Homework 4 Solution
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,
, but not
[NOTE: This anticipates question F below]
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
(4) One ORDER has many ItemTypes, but each ItemType occurs only once in any
(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.
LIST THE MULTIVALUED DEPENDENCIES
There is one multivalued dependency:
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)