Solution for problems I.e and II on exam #3:

A partial answer to this question notes that the only essential structure in a relational database is the table. A full answer notes that a table is a relation, gives a definition of a relation (a subset of a Cartesian product of a series of sets) and makes the connection between relations and tables: that a table can be viewed as a subset of the Cartesian product of underlying domains (the domains that underly the attributes of the table). This question may reappear on the final.

 Problem II:

a. (5 pts.) Write the table as an unnormalized relation, identifying repeating fields. What is the key field of this unnormalized relation?

The form includes the following fields. Repeating fields are identified by an asterisk:

• OrderNo
• Date
• TotalCost
• CustNo
• CustName
• CustAddr
• CustTele
• ItemCode (*)
• Description (*)
• Qty (*)
• UnitCost (*)

We have the following functional dependencies:

• OrderNo => Date, TotalCost, CustNo.
• CustNo => CustName, CustAddr, CustTele
• ItemCode => Description, Unitcost
• (OrderNo, ItemCode) => Qty

Therefore (OrderNo, ItemCode) is the key.

b. (5 pts.) Normalize the relation of part (a) by removing the repeating fields, and give the resulting tables.

When we do this (the second step in the process of bottom-up design), we get the following two tables:

• ORDER(OrdNo, Date, TotalCost, Custno, CustName, CustAddr, CustTele)
• ITEMS(OrdNo, ItemCode, Description, UnitCost, Qty)

Note that we retain the key for the repeated items (otherwise, we lose the connection between an order and the items ordered).

c. (10 pts.) Identify partial key and transitive dependencies in the tables of part (b), and arrive at a final relational schema for this form. Do you recognize it from an earlier assignment?

• There is a partial key dependency (2NF violation) in ITEMS. The key is (OrdNo, ItemCode), and Description and UnitCost depend only on ItemCode. Splitting this out we get two tables from ITEM:
  • LINES(OrdNo, ItemCode, Qty)
  • CATALOG(ItemCode, Description, UnitCost)
• There is also a transitive dependency OrdNo => CustNo => CustName, etc. in the ORDER table (violation of 3NF). Splitting this apart, we get two more tables:
  • ORDER(OrdNo, Date, TotalCost, CustNo)
  • CUSTOMER(CustNo, CustName, CustAddr)
• Which appears very much like the E-R exercise of some weeks ago, now done bottom-up.