You are a database developer on an instance of SQL Server 2008. Your company is using a database to record its purchasing transactions. You have an Inventory table and a PurchaseOrder table defined as follows:
Correct Answer: C
Explanation/Reference:
To accomplish the desired objectives, you should perform the following actions:
Drop the existing FOREIGN KEY constraint.
Remove the InvID and Quantity columns from the PurchaseOrder table.
Create a POLines table that includes the PONumber, InvID, and Quantity columns.
Create a FOREIGN KEY constraint on the InvID column of the POLines table that references the InvID column in the Inventory table.
Create a FOREIGN KEY constraint on the PONumber column of the POLines table that references the PONumber column of the PurchaseOrder table.
To record multiple line items on a single purchase order, you should create a separate table to store the lines of each purchase order. Each row in the PurchaseOrder table will represent a single purchase, and each row of the new POLines table will represent the purchase of a specific inventory item on a purchase order. After dropping the existing FOREIGN KEY constraint, you should move the InvID and Quantity columns from the PurchaseOrder table to the POLines table. Next, you should create a FOREIGN KEY constraint on the InvID column of the POLines table that references the InvID column in the Inventory table. This will ensure that purchase order lines can only be added for items that exist in the Inventory table. Finally, you should create a FOREIGN KEY constraint on the PONumber column of the POLines table to associate a purchase order with its respective line items. In this scenario, the revised data model would resemble the following:
You should not drop the existing FOREIGN KEY constraint, create a POLines table including the InvID and Quantity columns, and create a FOREIGN KEY constraint on the InvID column that references InvID column of the Inventory table. This would only accomplish a portion of the desired objectives. The resulting data model would not include a relationship to associate each purchase order with its respective line items. To do so, you would also have to include the PONumber column in the POLines table and create a relationship between the PurchaseOrder and POLines tables.
You should not denormalize the data model by combining the Inventory and PurchaseOrder tables into a single table because this data model would store redundant data. If you combined the Inventory and PurchaseOrder tables, the same inventory item information would have to be stored in multiple rows, once for each purchase order line containing that item. Multiple rows in the combined table would also contain the same purchase order number and date. You should only denormalize your data model when it will enhance performance and simplify queries, or if you need to capture required historical information that would not otherwise be captured. When a database is denormalized, extra steps must be taken to ensure data integrity. In most cases, normalization is preferred.
You should not add multiple columns for InvID and Quantity values to the PurchaseOrder table and create a FOREIGN KEY constraint from each InvID column that references the InvID column in the Inventory table. You should not add multiple columns for the same attribute within an entity because this makes the data model less flexible and more difficult to query and maintain. For example, in this scenario, if you chose to add columns for each purchased item, each purchase order could only contain the number of items for which you added additional columns. If a purchase for more items was made, the table structure would have to be modified to include additional columns. If fewer items were purchased than there were columns for purchased items, some of the columns would be empty, thus unnecessarily increasing the size of the database. This data model would also make writing queries more difficult because you would have to query each purchaserelated column.
