You handle administrative support for CyberToys, a small chain that sells computer hardware and software and specializes in personal service. The company has four stores located at malls and is planning more. Each store has a manager, a technician, and between one and four sales reps. The owners want to create a personnel records database, and they asked you to review a table that they had designed. They suggested fields for store number, location, store telephone, manager name, and manager home telephone. They also want fields for technician name and technician home telephone and fields for up to four sales rep names and sales rep home telephones. Draw their suggested design and analyze it using the normalization concepts you learned in the chapter. What do you think of their design and why? What would you propose?

You handle administrative support for CyberToys, a small chain that sells computer hardware and software and specializes in personal service. The company has four stores located at malls and is planning more. Each store has a manager, a technician, and between one and four sales reps. The owners want to create a personnel records database, and they asked you to review a table that they had designed. They suggested fields for store number, location, store telephone, manager name, and manager home telephone. They also want fields for technician name and technician home telephone and fields for up to four sales rep names and sales rep home telephones. Draw their suggested design and analyze it using the normalization concepts you learned in the chapter. What do you think of their design and why? What would you propose?

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter1: Overview Of Database Concepts

Section: Chapter Questions

Problem 5HOA: Access path A database table is composed of records and fields hold data. Data is stored in records....

See similar textbooks

Similar questions

You are to design a Hospital database. So before creating the database, your task is to draw the ER diagram based on the following data collected and analyzed: A hospital will have a name, unique id, multiple helpline numbers (one for doctor's appointment, one for ambulance, one for bill information and one for other services' info) and locations. A hospital may have more than one location as it may have different branches and each of the locations will have a building no., street address, road no. and city name. A patient will have a name, unique ID, may have more than one phone number, age, height, weight and symptoms. A patient takes an appointment in a hospital and the appointment date is recorded. A patient may have an attendant. An attendant will have his/her name, contact numbers and relationship with the patient as a record in the database. Due to the covid situation, the hospital authorities will only allow 1 attendant with 1 patient.
You are asked to create a database for a busing company. You need to keep track of the differ- ent buses, each having a plate number, make year, and number of seats. You also need to keep track of drivers that work in the company. The information kept on each driver should include the license number, age, and name. Finally, you need to keep information on which drivers are allowed to drive different buses. Drivers cannot drive any bus, but rather each driver is assigned with specific buses in the fleet he/she is allowed to drive. Accordingly, a driver may be allowed drive many different buses, and a bus can be driven by many different drivers. 2. Convert the ER diagram that is found in the next page to a logical relational data model. Submit in your file the relational model you arrived at (i.e. the list of tables, their columns, indicating their primary and foreign keys). Note: You are NOT asked to create an Access Database, but rather only provide the list of tables you would have in…
John liked your work so much that he would like you to create a database for the comic book shop that you worked with in the first project. Create a relational database with at least two tables. Comic book and company would be two logical ones
Your task is to develop a database, in order to keep information on all sales at a motorcycle dealership. The database will include information on motorcycles, salespeople, sales, and customers. We assume for simplicity that the dealership sells only new motorcycles and that all motorcycles are produced by the same manufacturer. Each kind of motorcycle is called a model, and it has a unique model number. For each model, you want to store also its name, engine size in cubic centimeters (cc) which is an integer number, and the date when this particular model was introduced. For each salesperson, you want to store their employee ID number, first name, middle initial, last name, and the date when the employee was hired. For each customer, you want to store a customer ID number, first name, middle initial, last name, address information (city, state, and zipcode), phone number (given in standard US format), and e‐mail address. Assume that you do not have a zip code table available. Each…
Design a survey form of your own. Fill it out as it might have been completed during the database design for Henry Books. For any questions you have too little information to answer, make a reasonable guess. (Ray Henry, the owner of a bookstore chain named Henry Books, gathers and organizes information about branches, publishers, authors, and books. Each local branch of the bookstore has a number that uniquely identifies the branch. In addition, Ray tracks the branch’s name, location, and number of employees. Each publisher has a code that uniquely identifies the publisher. In addition, Ray tracks the publisher’s name and city. The only user of the Book database is Ray, but you do not want to treat the entire project as a single user view. Ray has provided you with all the reports the system must produce, and you will treat each report as a user view. Ray has given you the following requirements: User View 1 Requirements: For each publisher, list the publisher code, publisher name, and…
StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.
The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.
You handle administrative support for CyberToys, a small chain that sells computer hardware and software and specializes in personal service. The company has four stores located at malls and is planning more. Each store has a manager, a technician, and between one and four sales reps. Bruce and Marcia Berns, the owners, want to create a personnel records database, and they asked you to review a table that Marcia designed. She suggested fields for store number, location, store telephone, manager name, and manager home telephone. She also wants fields for technician name and technician home telephone and fields for up to four sales rep names and sales rep home telephones. Draw Marcia’s suggested design and analyze it using the normalization concepts you learned in the chapter. What do you think of Marcia’s design and why? What would you propose?
The marketing team wants to celebrate the success of StayWell with a party. The team wants a table with the names of all residents and owners combined into single column named PARTICIPANT. You need to combine this information from the tables and send it back to the team. You do not need to create a new table in the database schema. Task Return a list of all the residents and owners
You are required to develop a simple database application for a small recruiting firm that wishes to keep track of all the employees at the firm; storing details about their name, phone numbers, hiredate, Gender, Date of Birth, Salary, and Commission. The firm has many departments and there are 5 to 20 employees in each department. The department information includes department name, description and total number of employees in that department. The company also provides vehicles for some of its employees. An employee maybe allocated one car. To ensure timely maintenance of vehicle, the company would like to store the following details of the vehicle: make, model, next_maintenance_date. Each employee has a position (manager, accountant, administrator, clerk, HR employee, etc.). Various allowances are allocated to each position. For example, the managers have fuel allowance, house allowance, social allowance and managers allowance. On the other hand, the administrators have house…
You have a list of all your contacts details that you would like to store in a database. Your goal is that when a record is inserted in contacts table, you also want this record to be stored (inserted) in a backup table. Assume you have the two tables created: CONTACTS and CONTACTS_BACKUP. These two (2) tables have the same records. The CONTACTS table have the following columns: CONTACT_ID (Primary_Key), FIRSTNAME, LASTNAME and MOBILE_NO. CONTACTS_ID FIRSTNAME LASTNAME MOBILE_NO 1 100 Princess Kim 813023895 2 101 Henry Packer 811257891 3 102 Petrus Johannes 855698712 4 103 Lukas Shilongo 832584691 The CONTACTS_BACKUP table have the same columns and records as the CONTACTS table. CONTACTS_ID FIRSTNAME LASTNAME MOBILE_NO 100 Princess Kim 813023895 2 101 Henry Packer 811257891 3 102 Petrus Johannes 855698712 4 103 Lukas Shilongo 832584691 You now need to keep both tables in sync so that when a record is inserted into the CONTACTS table it should automatically be inserted into…
You are asked to design a database for an University. This particular university has multiple departments. Each department in that university offers one or more courses to the students. All the students are part of a certain department and they take courses in that department. A department has a name, unique number and chairman. The courses have a name, a course number such as ‘311’ , a credit point value, and the year it commenced. A course cannot be identified uniquely with the course number, but the course number is unique for each department. Students have given names, surname, unique Student ID, Date of Birth and the year they enrolled. When a student takes a course the year and the semester he or she took that particular course is recorded. When he finishes the course , the grade and total marks are recorded. A student can take multiple courses but they are also allowed to drop a semester in that university by taking no courses. Construct an ER diagram using the requirements…