Question

Consider the below database schema for company ABC:

Employee(empNo, givename, famname, gender, DOB)

Supervises(super_empNo*, empNo*, description)

Department(deptNo, name, manager_empNo*)

Dependent(empNo*, name, relationship)

Project(projNo, description, deptNo*)

The relations Employee, Supervises, Department, Dependent and Project keep data for employees, supervision, departments, dependents of employees and projects of ABC.

For the database, primary keys, and parent and child relations for foreign keys are annotated. The meaning of most attributes is self-explanatory. Answer questions below.

Your answer to each question must be according to the given database schema and instance.

1.1. (2 points) Does the primary key of Supervises (super_empNo, empNo) ensure that each supervisor supervises a different employee? Explain your answer.

1.2. (5 points) Is it possible that a project has no department? Explain why/why not. If possible, identify any data integrity constraints that can be placed on the Project table to prevent this? Can a project have many departments? Explain using the data integrity constraints on the Project table.

1.3. (2 points) Is it possible that a supervisor does not supervise any employees? Explain your answer using the data constraints on the Supervises table.

1.4 (3 point) Assuming that every department is working on at least one project (and the Department table contains a record with department number 8), can the SQL statement below be successfully executed? Explain your answer. DELETE FROM Department WHERE deptNo = 8;

Answer 1

Answer 1.1: Yes. The primary key of Table Supervises (super_empNo, empNo) ensure
                    that each supervisor supervises a different employee.

Since the combination of Super_empNo and empNo is a primary key of  table Supervises. Primary key cannot be NULL. In addition, there is no repeated values exist in the primary key.

As the combination of super_empNo and empNo is a primary key. So, it cannot be Null and no repeated value exit. Therefore, it ensures each Supervisor supervises a different employee.

Answer 1.2:   No, it is not possible that a project has no department.

                       Since the dept no in the project table is a primary key. According to       
                       Entity integrity constraints, “primary key cannot be null”.

                       Therefore, they must be unique value in the dept no column, which is also,
                       not null. Therefore, each project has some unique and not null department.

                       Each project does not have many department. In that case, dept no of project table is not unique. Which violate
the rule of primary key constraint data integrity.

Answer 1.3: No, it is not possible that a supervisor does not supervise any employees.

             Since the combination of super_empNo* and empNo* in the Supervises table is a primary key. According to   Entity integrity constraints, “primary key cannot be null”. Therefore, a supervisor must supervise any employees.

Answer 1.4: DELETE FROM Department WHERE deptNo = 8;

                     The above query cannot be executed successfully. According to Referential Integrity Constraint- You can't delete a record from a primary table if matching records exist in a related table.

The deptNo in the department table is a primary key to the project table. So, we cannot delete it.