Homework_4_Solution_with_breakup_points - Homework 4...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Homework 4 Solution ISE 382 Spring 2006 E. Transform the following table into two or more tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints. Make and state assumptions as necessary. ORDER (CustomerNumber, FirstName, LastName, Phone, OrderNumber, DateIn, DateOut, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) ASSUMPTIONS: (1) CustomerNumber (FirstName, LastName) , but not CustomerNumber  (FirstName, LastName) [NOTE: This anticipates question F below] (2) CustomerNumber  Phone . This means that there may be more than one phone number for each customer. (3) One CUSTOMER has many ORDERS, but each ORDER is associated with only one CUSTOMER. (4) One ORDER has many ItemTypes, but each ItemType occurs only once in any ORDER. (5) ItemType - > ItemPrice (6) SpecialInstructions is associated with each item type, since different instructions may be given for different items. (7) Order number is a number assigned to the orders themselves, without association with any particular customer. Thus we have such numbers as “123454”, 123455”, etc, rather than “Customer 101, Order 1”, Customer 101, Order 2”, Customer 102, Order 1”, etc. STEP ONE: LIST THE MULTIVALUED DEPENDENCIES There is one multivalued dependency: CustomerNumber  Phone To simplify the other steps, we’ll break this out into it’s own table now ORDER_2 (CustomerNumber, FirstName, LastName, OrderNumber, DateIn, DateOut, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) CUSTOMER_PHONE ( CustomerNumber , Phone )
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
The foreign key will be CustomerNumber, but we still have normalization to BCNF to do, so we’ll add the referential integrity constraint for this relation at the end of the process when we have all the normalized relations. BCNF Algorithm
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 04/24/2008 for the course ISE 382 taught by Professor Kale during the Spring '08 term at USC.

Page1 / 7

Homework_4_Solution_with_breakup_points - Homework 4...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online