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 an Internet Service Provider (ISP).
You are currently developing an enterprise application for Domain.com. Domain.com currently offers its customers three services: Web site hosting, Internet connectivity and voice over Internet Protocol (VoIP). The choice of purchasing one or more of these services is up to each customer. Furthermore, multiple customers can purchase the same services. In the event of a customer purchasing a service, a service commencement date, as well as service termination data is set for that customer. The exhibit below illustrates the logical database design for the application.
You now need to normalize the database.
What should you do?
A.
Move all columns from the Services table to the Customers table except the CustomerID column.
Change the primary key on the Customers table to include the ServiceID column.
Delete the Services table.
B.
Move all columns from the Customers table to the Services table except the CustomerID column.
Change the primary key on the Services table to include the CustomerID column.
Delete the Customers table.
C.
Add a table named CustomerServices.
Add a Foreign Key to the CustomerServices table that references the CustomerID column of the Customer table.
Add a Foreign Key to the CustomerServices table that references the ServiceID column in the Services table.
D.
Add a table named ServiceType and add two columns named ServiceTypeID and Description respectively.
Add a foreign key to the Services table that references the ServiceTypeID column of the ServiceType table.
Remove the ServiceType column from the Services table.
Explanation:
When you normalize a database, you in essence separate the data into multiple tables to reduce duplicate data. In this case, the Services table contains data related to customer services which includes the service commencement date, the service termination data as well as the service type. Say 2000 customer purchase a service named VoIP, it will result in the VoIP value being duplicated in 2000 rows. Thus to eliminate this duplication of data, you should create a third table named ServiceType to store the service type information. Then you should add a foreign key to the Services table that references the primary key in the ServiceType table.
Incorrect answers:
A, B: Combining columns into one table will result in de-normalizing.
C: Adding an extra table with two foreign keys that references the appropriate columns in other tables should only be done in many-to-many relationships. An intersection table joins two entities that are involved in many-to-many relationships. In this scenario there is a one-to-many relationship between customers and purchased services, a many-to-many relationship between customers and service types, thus you should rather add a ServiceType table that can be used as the intersection table between customers and service types.