For Table 1 ORDER(order#, customer#, name, address, orderdate)

31a. List all functional dependencies

31b What is the key? (Note, to analyze or normalize a table, you MUST figure out the key of the table first)

31c. What Normal Form is this table in? Why? Use the definition of the normal form that applies

? 31d. Describe the modification anormaly this table may have (Note, describe insertion anormaly, deletion anormaly and update anormaly respectively)

? 31e. Normalize the table into a set of tables that are at least 3NF.

For table 2: ORDER_LINE(order#, product#, description, quantity, unitprice)

32a. List all functional dependencies

32b What is the key? (Note, to analyze or normalize a table, you MUST figure out the key of the table first)

32c. What Normal Form is this table in? Why? Use the definition of the normal form that applies

32d. Normalize the table into a set of tables that are at least 3NF.

4.

Suppose that the data of above table shows all possible functional dependencies.

41. Develop a set of functional dependencies that describe the information in above table.
42. What normal form above table is in (Note, to answer this question you must figure out the key of the table)? Why?
43. What modification anomalies this table suffers (be as specific as possible)
44. Develop a set of third normal form (3NF) tables from these functional dependencies. Justify/explain that this set of tables are actually in third normal form.

??

5. Given the following table and FD and MVD

EQUIPMENT (Manufacturer, Model, AcquisitionDate, BuyerName, BuyerPhone, PlantLocation, City, State, ZIP)

Functional dependencies (FD):

BuyerName ? PlantLocation, City, State, Zip;

? ?Zip ? City, State;

(Manufacturer, Model, BuyerName) ? AcqDate

BuyerName -->> BuyerPhone (Multivalued Dependency)

5a. What is the KEY of EQUIPMENT?

5b. What normal form this table is in? (Note, only if you have all FDs and know the key , you can then answer tis question).

5c Why this is a ill-designed table. Describe one anomaly (insert, delete or update anomaly).

5d. DO you need to normalize this table? why? and how ? (show the result of normaliztion) Note. The resulting tables must have KEY and FK so that the information carried in original table is kept by the resulting tables (Namely normalization MUST be "loseless decomposition").

Order number: 1234 Customer number: 9876 Customer name: Billy Customer address: 456 HighTower Street City-Country: Hong Kong, China ProductNo A123 B234 C345 Order Form Desscription Pencil Eraser Date: Sharpener Quantity 100 200 LO 5 11/04/98 Unit Price $3.00 $1.50 $8.00