Question

Given the following Schema :

Employee (empNo, fName, lName, address, DOB, sex, position, deptNo) Department (deptNo, deptName, mgrEmpNo)
Project (projNo, projName, deptNo)
WorksOn (empNo, projNo, dateWorked, hoursWorked)

Where:

• Employee contains employee details and empNo is the key.

• Department contains department details and deptNo is the key.

• mgrEmpNo identifies the employee who is the manager of the department. There is only

  one manager for each department.

• Project contains details of the projects in each department and the key is projNo (no two

  departments can run the same project).

• WorksOn contains details of the hours worked by employees on each project, and

  empNo/projNo/dateWorked form the key.

1. Draw the E-R diagram that matches this schema.

2. Using relational algebra list all the department managers including department name, employee full-name and address.

3. Using relational algebra list all the projects (only project number) employee ‘Charles Merkel’ has been part of.

Answer 1

E-R diagram

the ER diagram was created using DIA.the ER diagram shows the coneptual relationships between the different objects of the database. the ER diagram shoes all tables in the database and how those tables relate to each other through primary and foreign keys.primary and foreign key is discussed further under the relationship properties and referential integrity.heading.