9. The Office. Dunder Mifflin Paper Company, Inc. hired us to build their database system. The...

Transcribed Image Text:

9. The Office. Dunder Mifflin Paper Company, Inc. hired us to build their database system. The company wants to keep the information of the employees (name, email, hire date, salary, phone numbers). An employee may have multiple phone numbers, but a given phone number can only belong to a single employee. Employees work for a branch, which is located in a given address and city. Branches are administrated by one manager, which must be one of the employees. No manager can administrate more than one branch. Moreover, an employee may be the supervisor for other employees, this way reflecting the hierarchy of the roles in the company. The office is full of many types of employees, but we want to keep additional information for salesmen (commission percentage they take in each sale) and warehouse staff. This is necessary since we warehouse staff require to fulfill a number of safety certifications, and we must also store the date for which the warehouse employee obtained each of the certifications. a) Draw an ERD to represent the given problem. Do not forget about notation, relationships, roles, cardinality, and participation. b) Translate the ERD into the relational model, identify primary keys and foreign keys for each table, enforce not null, unique, or any other constraints when needed. c) Write the SQL scripts to create the tables, constraints, and keys. 9. The Office. Dunder Mifflin Paper Company, Inc. hired us to build their database system. The company wants to keep the information of the employees (name, email, hire date, salary, phone numbers). An employee may have multiple phone numbers, but a given phone number can only belong to a single employee. Employees work for a branch, which is located in a given address and city. Branches are administrated by one manager, which must be one of the employees. No manager can administrate more than one branch. Moreover, an employee may be the supervisor for other employees, this way reflecting the hierarchy of the roles in the company. The office is full of many types of employees, but we want to keep additional information for salesmen (commission percentage they take in each sale) and warehouse staff. This is necessary since we warehouse staff require to fulfill a number of safety certifications, and we must also store the date for which the warehouse employee obtained each of the certifications. a) Draw an ERD to represent the given problem. Do not forget about notation, relationships, roles, cardinality, and participation. b) Translate the ERD into the relational model, identify primary keys and foreign keys for each table, enforce not null, unique, or any other constraints when needed. c) Write the SQL scripts to create the tables, constraints, and keys.

Answer rating: 100% (QA)

Solution Answer a To make the ERDiagram we must considered the constraints given in the question Her... View the full answer