Figure below contains sample data for parts and for suppliers who supply those p

Question

Figure below contains sample data for parts and for suppliers who supply those parts. In discussing the data with users, you find that part numbers - but not descriptions -uniquely identify parts, and that supplier names uniquely identify suppliers. Multiple suppliers can supply the same part. For example, air deflectors can be purchased from either Asterman or JMZ Technologies. Convert this relation into an equivalent set of relations in third normal form (3NF). In word document write out all transitions from the first normal form to the third normal form following format in the chapter 11, pp.AC696-AC705.

Explanation / Answer

Item table

1NF ---atomic values

. Functional Dependencies :

2NF

Decompose relation into two tables based on the functional dependencies

PARTSUPPLIER(Item Number,Supplier Name,Description,Unit Cost) . Here Item Number and Supplier Name is the composite primary key

SUPPLIER(Supplier Name,Address). Here Supplier Name is the primary key.

3NF

The Unit Cost is functionally Dependent on both Item Number and Supplier Name . But Description is functionally dependent on Item Number Only.

So decompose PARTSUPPLIER table further to:

PART(Item Number,Description)

PARTSUPPLIER(Item Number,Supplier Name,Unit Cost)

SUPPLIER(Supplier Name,Address)

The tables are in 3NF now .

Item Number Description Supplier name Address Unit Cost 1234 Air Deflector Asterman Georgetown 5.00 1234 Air Deflector JMZ Technologies Buda 4.50 5678 Energy saving Kit Scryps Distributors San Rita 3.00 5678 Energy saving Kit Asterman Georgetown 2.00 5678 Energy Saving Kit JMZ Technologies Buda 3.75

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.