Corresponding to problem and exercise 3 on page 359

This preview shows page 2 - 4 out of 8 pages.

2.Corresponding to Problem and Exercise 3 on page 359.Consider the E-R diagram in Figure 9-21. Worth 20% of the assignment.a.Transform the E-R diagram into a set of 3NF relations.b.State and justify all referential integrity rules for the 3NF relations from part a.Underlineall primary keys. Italicizeall foreign keys.VENDOR(Vendor_ID, Address)PART(Item_Number, Description)PRICE QUOTE(Quote_Number, Vendor_ID, Item_Number, Quote_Quantity, Price)PART RECEIPT(Order_Number, Quote_Number, Vendor_ID, Item_Number, Date, Order_Quantity)
ITEC 3600, Fall 2011, Unit 4 HW VENDOR and PART are in 3NF since there are no functional dependencies involving any of their attributes. Quote_Number , Vendor_ID, and Item_Number make up the combination primary key in PRICE QUOTE. Quote_Quantity is now a standard attribute as is Price and they are both determined by a combination of Item_Number, Vendor_ID, and Quote_Number, since you need an Item_Number to generate a quantity of something and a price for it, and a price could vary based on who the vendor is. Vendor_ID and Item_Number are also foreign keys that reference matching values in their home entities. For the PART RECEIPT entity, Order_Number is part of the primary key. If I understand the book right, Quote_Number, Vendor_ID, and Item_Number are also part of the primary key for PART RECEIPT. Following the rule for 1:N relationships, Quote_Number, Vendor_ID, and Item_Number become foreign keys for this entity since primary key(s) on the one side become foreign key(s) onthe many side. Date and Order_Quantity are nonprimary key attributes. Since there are no repeating groups, no partial dependencies on the primary key, and there are no functional dependencies between two or more nonprimary key attributes, all relations are in third normal form.3.Given the following relations, explain what it will take to reduce them to 2NF and then to 3NF. UNDERLINEall primary keysin the resulting relation. ITALICIZEall foreign keysin the resulting relation. If a key is both, then underlineit and italicizeit. Notice that you might be prompted to create a new table, if necessary. Identify the name of this table and fill in the data appropriately. Worth 30% of the assignment.a.Change this SALESPERSON/ORDERtable to FIRST NORMAL FORM (1NF)Use the same data and rearrange it properly in the blank table below..

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture