Referential Integrity Check
Question 1 - Given the following data types of some table fields and the simple schema of the relational database they are a part of, inspect its relationships for any referential integrity violations. If any exist, list what they are:
Note: You can assume that cascading updates and deletes are not enabled.
Field data types for each table, with (PK) indicating the primary key field:
Employee Table
Field Name Type
EmployeeID (PK) Long Int
First_Name Text
Last_Name Text
Phone Text
Vehicle Table
Field Name Type
Vehicle_ID (PK) Long Int
Driver_ID Long Int
Model Text
Device Table
Field Name Type
Device_ID (PK) Int
EmployeeID Int
Type Text
Part Table
Field Name Type
Part_ID (PK) Int
Device_ID Long Int
Type Text
Conceptual schema showing inter-table relationships:
The actual schema in MS Access would look as follows:
Question 2 - Given the following data for the Employee and Vehicle tables, identify any referential integrity violations, if any:
EmployeeID First_Name Last_Name Phone
11 Chris Smith 8134567690
12 Pat Malone 9145551234
Vehicle_ID Driver_ID Model
1234 11 Ford F 150
4567 12 Toyota Rav4
2233 11 Pontiac
4993 NULL Chevy
6457 13 VW Jetta
Question 3 - As a user, can you delete the first record in the Employee table above, without violating any referential integrity rules? Explain why?
Attachment:- Referential_Integrity_Check.doc