DBMS Assign 2

Normalization Practice 1 Solution BIS 638 – Database Management for Business Systems

The Acme Company maintains a list of the parts they order from their vendors along with the prices that each vendor charges. The following table shows a portion of this data: PartCost

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

1. Use arrows to show the functional dependencies in the table. We’ll look for functional dependencies moving from left to right through the fields of the table. As we do, keep in mind that a functional dependency only exists when the relationship between the fields is one-to-one. PartNbr determines Desc. PartNbr does not determine VendorNbr or VendorAddress because a given part can come from multiple vendors (e.g., PartNbr 1234 comes from VendorNbr 117 and 623). PartNbr also does not determine UnitCost because a given part can have a different cost, depending on the vendor (e.g., the UnitCost for PartNbr 1234 is either $10.00 or $8.00, depending on the vendor).

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

Desc is free-form, descriptive text. As such, it should not be used as a determinant.

VendorNbr determines VendorAddress. VendorNbr does not determine PartNbr or Desc because a given vendor can supply multiple parts (e.g., VendorNbr 117 supplies PartNbr 1234 and PartNbr 5678). VendorNbr also does not determine UnitCost because the parts supplied by a vendor can have different costs (e.g., VendorNbr 117 supplies PartNbr 1234 and PartNbr 5678 with UnitCosts of $10.00 and $3.00, respectively).

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

Vendor Address is free-form, descriptive text. As such, it should not be used as a determinant. Since UnitCost is not determined by PartNbr or VendorNbr alone, it is determined by the two of them together.

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

2. Convert the table into 3NF relations. Using the three functional dependencies identified in Task 1, the following tables were created: Part (PartNbr, Desc) Vendor (VendorNbr, VendorAddress) Cost (PartNbr, VendorNbr, UnitCost) In each table, the determinant of the functional dependency becomes the primary key (e.g., PartNbr is the primary key in the Part table). Since UnitCost has two determinants (PartNbr and Vendor), the Cost table has a composite primary key.

Having created the tables based on the functional dependencies, we now need to review them to make sure each table is 3NF. Let’s review each table in turn. Part (PartNbr, Desc)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not composite so a partial functional dependency is not possible).

 3NF – Yes. There are not transitive functional dependencies. Vendor (VendorNbr, VendorAddress)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not composite so a partial functional dependency is not possible).

 3NF – Yes. There are not transitive functional dependencies. Cost (PartNbr, VendorNbr, UnitCost)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies. UnitCost is not determined by PartNbr or VendorNbr alone.

 3NF – Yes. There are not transitive functional dependencies. The 3NF tables are as follows: Part (PartNbr, Desc) Vendor (VendorNbr, VendorAddress) Cost (PartNbr, VendorNbr, UnitCost)