Problem

The materials manager at Pine Valley Furniture Company maintains a list of suppliers for...

The materials manager at Pine Valley Furniture Company maintains a list of suppliers for each of the material items purchased by the company from outside vendors. Table 4-7 shows the essential data required for this application.

a. Draw a dependency diagram for this data. You may assume the following:

• Each material item has one or more suppliers. Each supplier may supply one or more items or may not supply any items.

• The unit price for a material item may vary from one vendor to another.

• The terms code for a supplier uniquely identifies the terms of the sale (e.g., code 2 means 10 percent net 30 days). The terms for a supplier are the same for all material items ordered from that supplier.

b. Decompose this diagram into a set of diagrams in 3NF.

c. Draw an E-R diagram for this situation.

Reference table 4-7

Step-by-Step Solution

Solution 1

Step #1 of 5

a) Functional dependency diagram of the relation:

• The relational schema is shown along with the PVFC (Pine Valley Furniture Company) functional dependency in the relation.

• It describes the details about the material id, material name, units, standard cost. It also describes vendor id, name, unit, terms code.

• It also gives correlation among all.

The diagram to show functional dependency is as given below:

$\\Acer-pc\file status\8.VIRENDRA\12-4-2014\10943-4-14PE_a.jpg$

Functional dependency:

• An association between two attributes or two sets of attributes in a same relational database table which is having some constraints is known as functional dependency.

• In a table one, attribute is functionally dependent on another attribute to take one value.

Step #2 of 5

Functional dependencies for the relation are illustrated below:

Functional dependency #1:

• MaterialName, UnitsOfMeasure, StandardCost are functionally dependent on MaterialID.

Functional dependency #2:

• VendorName, TermsCode, Terms are functionally dependent on VendorID.

Functional dependency #3:

• Terms is functionally dependent on TermsCode.

Functional dependency #4:

• UnitPrice is functionally dependent on MaterialID and VendorID.

Step #3 of 5

b) Decompose the relation into its Third Normal Form (3NF):

Third normal form:

• For the third normal form, the first and basic requirement is that the model should be in the first and second normal form.

• The value of any non-primary key attributes will not depend on any other non-primary key attributes.

o If any non-primary key attributes are depend on any other non-primary key attribute then it should moved or deleted.

o It is termed as transitive dependency.

The diagram given below describes the relation between the materials and the suppliers. They are associated with accurate details.

$C:\Users\300722\Desktop\a.jpg$

Step #4 of 5

Explanation:

The above diagram is transformed to four relations MATERIAL, SUPPLIEDBY, VENDOR, and TERMS.

• In the MATERIAL relation,

o MaterialID, MaterialName, UnitOfMeasure and StandardCost are its attributes.

o MaterialID is the primary key attribute.

• In the SUPPLIEDBY relation,

o MaterialID, VendorID, and UnitPrice are its attributes.

o MaterialID and VendorID is the foreign key attributes to refer MATERIAL and VENDOR tables.

• In the VENDOR relation,

o VendorID, VendorName and TermsCode are its attributes.

o VendorID is the primary key attribute.

o TermsCode is the foreign key attribute to refer TERMS table.

• In the TERMS relation,

o TermsCode and Terms are its attributes.

o TermsCode is the primary key attribute.

Step #5 of 5

c) ER diagram for the relations

The ER diagram makes transformation of the relations. The changes in the diagram show the inter relation between the material, units, vendor and terms which is as given below:

$\\Acer-pc\file status\8.VIRENDRA\12-4-2014\10943-4-14PE_c.jpg$

Explanation:

• In the MATERIAL entity,

o MaterialID, MaterialName, UnitOfMeasure and StandardCost are its attributes.

o MaterialID is the primary key attribute.

• In the SUPPLIEDBY entity,

o MaterialName is the attribute.

• In the VENDOR entity,

o VendorID, and VendorName and TermsCode are its attributes.

o VendorID is the primary key attribute.

• In the TERMS relation,

o TermsCode and Terms are its attributes.

o TermsCode is the primary key attribute.

Uses relationship:

• The cardinality of the relationship is “Mandatory one to optional many”.

• This is because, certain Terms may or may not be used by the vendors.

Add your Solution

Textbook Solutions and Answers Search

Solutions For Problems in Chapter 4

Free Homework Help App

Download From Google Play

Scan Your Homework
to Get Instant Free Answers

Need Online Homework Help?

Ask a Question

Get Answers For Free
Most questions answered within 3 hours.

Recent Solutions