Consider the following tables: DEPARTMENT (DepartmentName, BudgetCode, OfficeNum
ID: 3879655 • Letter: C
Question
Consider the following tables:
DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)
EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)
PROJECT (ProjectID, Name, Department, MaxHours, StartDate, EndDate)
ASSIGNMENT (ProjectID, EmployeeNumber, HoursWorked)
1)
Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to these relations.
2)
Produce some sample tables for these relations that observe the relational integrity rules. Suggest some general constraints that would be appropriate for this schema.
Explanation / Answer
The tables are
DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)
EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)
PROJECT (ProjectID, Name, Department, MaxHours, StartDate, EndDate)
ASSIGNMENT (ProjectID, EmployeeNumber, HoursWorked)
First identify the primary keys of each table. Primary key is an attribute or a combination of attributes which uniquely identifies each row in a table. The primary key of DEPARTMENT table is DepartmentName. The primary key of EMPLOYEE table is EmployeeNumber. The primary key of PROJECT table is ProjectID. The primary key of ASSIGNMENT table is combination of attributes ProjectID, EmployeeNumber.
The primary keys in these tables are shown below.
DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)
EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)
PROJECT (ProjectID, Name, Department, MaxHours, StartDate, EndDate)
ASSIGNMENT (ProjectID, EmployeeNumber, HoursWorked)
Next identify the foreign keys in each table. An attribute in a table is a foreign key when it refers to a primary key in another table. The Department attribute in table EMPLOYEE refers to DepartmentName attribute of table DEPARTMENT. Hence foreign key of table EMPLOYEE is Department. Also the Department attribute in table PROJECT refers to DepartmentName attribute of table DEPARTMENT. Hence foreign key of table PROJECT is Department.The ProjectID attribute in table ASSIGNMENT refers to ProjectID attribute of table PROJECT. The EmployeeNumber attribute in table ASSIGNMENT refers to EmployeeNumber attribute of table EMPLOYEE. Hence EmployeeNumber and ProjectID are foreign keys.
Entity Integrity Constraints
Entity Integrity Constraints states that primary key cannot be null. This is because primary key is used to uniquely identify a row in a table. If it is null, we cannot identify a row in a table.
Hence in these tables value of attributes DepartmentName in table DEPARTMENT, EmployeeNumber in table EMPLOYEE, ProjectID in table PROJECT and ProjectID, EmployeeNumber in table ASSIGNMENT cannot be null.
Referential Integrity Constraints
Referential Integrity constraints is specified between two tables and is used to maintain the consistency between tables. The referential integrity constraint states that a tuple in one table that refers to another table must refer to an existing tuple in that table.
Referential Integrity constraints in above tables are