Explore BrainMass

Relationships for any referential integrity violations.

Please answer the attached questions.

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?

Note: That's the record of Chris Smith with EmployeeID = 11.


Solution Preview


Answer 1 :

If there is a field in a table which is referring another field in another table, the data types should be same.
Here the data type of EmployeeId field in Device Table is int, and it is referring EmployeeId in Employee Table where the data type is LongInt.
So you have to change the data type of EmployeeId in ...

Solution Summary

This solution discusses relationships for any referential integrity violations.