You work as the Enterprise application developer at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers in the domain run Windows Server 2003. The design of applications forms part of your responsibilities at Domain.com. Domain.com operates as a vehicle tracking organization in the vehicle insurance industry. You are currently developing a vehicle inventory management solution for Domain.com. Following are the existing facts:
1. A vehicle is identified by its vehicle identification number (VIN).
2. A vehicle has one or more options.
3. Different vehicles may have the same options.
4. Each option can be standard or nonstandard.
The exhibit below illustrates the logical database design.
You have been tasked to assess the Domain.com design in terms of data integrity.
What conclusion can you draw?
A.
The design is correct.
B.
The design is incorrect. Option table should have a foreign key that references the VIN column in the Vehicle table.
C.
The design is incorrect. VehicleOption table should not have the OptionID column.
D.
The design is incorrect. Vehicle table should have a foreign key that references the OptionID column in the Option table.
Explanation:
The Vehicle table has a primary key named VIN. This indicates that a VIN uniquely identifies a row in the Vehicle table.
An Option table contains rows that represent the possible options for all vehicles. A VehicleOption table contains rows that identify options for particular vehicles. This is a necessary table since there is a many-to-many relationship that exists between vehicles and options.
Thus this design is correct in terms of data integrity.
Incorrect answers:
B: The Option table should not have a foreign key that references the VIN column in the Vehicle table. This would only be required in a case of a many-to-one relationship between vehicles and options.
C: The VehicleOption table should not be removed from the VehicleOption table as this will result in a disconnection in the relationship between vehicles and options.
D: The Vehicle table should not have a foreign key that references the OptionID column in the Option table as this would only be required in a case where there is a one-to-many relationship between vehicles and options.