Explore BrainMass
Share

Explore BrainMass

    Relationships for any referential integrity violations.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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.

    © BrainMass Inc. brainmass.com October 9, 2019, 9:02 pm ad1c9bdddf
    https://brainmass.com/computer-science/databases/inspect-its-relationships-for-any-referential-integrity-violations-171129

    Attachments

    Solution Preview

    Hi,

    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.

    $2.19