{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Homework_4_Solution_with_breakup_points

# Homework_4_Solution_with_breakup_points - Homework 4...

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

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 )

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

View Full Document
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
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### 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
Ask a homework question - tutors are online