Question

You are given the following schemas for a corporation database. The corporation owns several subsidiary companies (e.g. Disney Corporation owns Lucasfilm Ltd). Managers are also Employees. Answer the following questions using the schemas.

Employee(ssn, name, street, city)

Company(company_name, asset)

Company_Branches(company_name, branch_num, city)

Works(ssn, company_name, branch_num, salary)

Managed_By(ssn, manager_ssn)

6) Does the current design allow us to enforce the constraint that each employee must have a manager (without using external check or triggers)? Why or why not?

7) If the answer to the above is “no,” what changes do we need to make on the schema(s) to allow enforcement of such a constraint?

Answer 1

Given that,

Employee(ssn, name, street, city)

Company(company_name, asset)

Company_Branches(company_name, branch_num, city)

Works(ssn, company_name, branch_num, salary)

Managed_By(ssn, manager_ssn)

6) Does the current design allow us to enforce the constraint that each employee must have a manager (without using external check or triggers)? Why or why not?

The given set of schema cannot enforce the constraint that an employee has a manager, since the schemas

Managed_By(ssn, manager_ssn)

does not link an employee with a manager and the schema Company_Branches does not mandate that a branch should have a manager.

Company_Branches(company_name, branch_num, city)

7) If the answer to the above is “no,” what changes do we need to make on the schema(s) to allow enforcement of such a constraint?

If the schema Managed_By and Company_Branches are modified as

Managed_By(branch_num, manager_ssn)

Company_Branches(company_name, branch_num, city, manger_ssn)

The constraint that an employee must have a manager can be enforced, provided the attribute branch_num in the schema Managed_by is a foreign key referencing the attribute branch_num of the schema Company_Branches and the attribute manger_ssn in the schema Company_Branches is a foreign key referencing the attribute manger_ssn in the Managed_By schema.