Ace Manufacturing constructs products for sale. Each product is identified by a

Question

Ace Manufacturing constructs products for sale. Each product is identified by a serial number. Each product is constructed from many other parts which are purchased from a variety of vendors. Given the following relation, produce a normalized set of relations which are normalized up through 3rd normal form. Note: in the following relation the prefix Prod indicates Product, Comp indicates Component and Vend indicates Vendor.

Manufacture(ProdSerialNum, ProdName, ProdType, ProdTypeName, Component(CompSerialNum, CompType, CompName, Vendor(VendCode, VendName, VendAddress, CompPrice)* )*, ProdPrice)

Business Rules:

1. Ace Manufacturing constructs many products.

2. A product is classified as a certain type.

3. A product is made up of components.

4. Each component can be supplied by many different vendors.

5. A vendor’s components may be found in many different products.

6. Each vendor may charge a different price for the same component.

3a. List ALL the FDs for the table that hold based upon the descriptions of the constraints above.

3b. Is the table definition in 1NF? Why or why not? If not, convert the table to 1NF table(s).

3c. Is/are the table(s) in 2NF? Why or why not? If not, convert the table(s) to table(s) that are in 2NF. Explain why they are now in 2NF?

3d. Are the tables in 3NF? Why or why not? If not, convert the table to 3NF. Explain why they are now in 3NF?

3e. List all of the foreign keys in question 3d. Identify them by the table name and state which table(s) and corresponding attribute(s) they relate to.

Explanation / Answer

Answer:

1)

The functional dependencies (FD’s) for the provided table are:

{ProdSerialNum} -> {ProdName, ProdType, ProdTypeName}

{CompSerialNum} -> {ProdSerialNum, CompType, CompName, CompPrice, VendCode}

{VendCode} -> {VendName, VendAddress}

2)

Yes, the provided table is in 1NF.The provided table does not contain any repetitive groups in the table.

Therefore, the table can be written as,

Manufacture(ProdSerialNum , ProdName, ProdType, ProdTypeName, CompSerialNum, CompType, CompName, CompPrice, VendCode, VendName, VendAddress, ProdPrice)

3)

Yes, the provided table is in 2NF. In the provided table a non-key attributes are functionally dependent on the primary key.

Therefore, the table can be written as,

Manufacture(ProdSerialNum , ProdName, ProdType, ProdTypeName, CompSerialNum, CompType, CompName, CompPrice, VendCode, VendName, VendAddress, ProdPrice)

4)

No, the provided table is not in 3NF. In the provided table, there are no transitive dependencies.

Hence, the tables are given as,

Product (ProdSerialNum , ProdName, ProdType, ProdTypeName, CompSerialNum, ProdPrice)

Component(CompSerialNum, CompType, CompName, CompPrice, VendCode)

Vendor(VendCode, VendName, VendAddress)

5)

The foreign keys in the 3NF are,

In Product’s table CompSerialNum is the foreign key.

In Component’s table VendCode is the foreign key.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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