1. The table below contains sample data for parts and for suppliers who provide

Question

1. The table below contains sample data for parts and for suppliers who provide those parts. In discussing this data with users, we find that part ID, but not names, uniquely identify parts and that supplier names uniquely identify suppliers. (30 pts)

In what normal form is the table as it is currently presented? Why?

Convert the table into a relation PART_SUP. In what normal form is this relation and why? For the PART_SUP relation, give specific examples of insertion, deletion, and modification anomalies, based on the data.

Create the set of relations needed to represent the data in 3NF.

PartID

PartName

SupName

City

UnitCost

1234

Logic Chip

Intel

Cupertino

10.00

1234

Logic Chip

AMD

Phoenix

8.00

5678

Memory Chip

Intel

Cupertino

3.00

5678

Memory Chip

ChipsRUs

Tucson

2.00

5678

Memory Chip

AMD

Phoenix

5.00

2. For the following, (1) determine what normal form it is currently in, (2) explain why it's in that normal form (and not others), and (3) convert it into 3NF if it's not already there. (20 pts)

CLASS(CourseID, InstID, CourseName, ClassTime, InstName)

          FD: CourseID --> CourseName

          FD: InstID --> InstName

JOBS(EmployeeID, DeptNum, JobID, JobTitle, EmpName)

          FD: JobID --> JobTitle

                       

3. Transform the following E-R Diagram into a set of relations in 3NF. (50 pts) Be sure to list all foreign key constraints.

PartID

PartName

SupName

City

UnitCost

1234

Logic Chip

Intel

Cupertino

10.00

1234

Logic Chip

AMD

Phoenix

8.00

5678

Memory Chip

Intel

Cupertino

3.00

5678

Memory Chip

ChipsRUs

Tucson

2.00

5678

Memory Chip

AMD

Phoenix

5.00

Patient! D PatFnamePatLna me PatPhone PhysID PhysFname PhysLname)( Department PATIENTo TreatedBy PHYSICIAN

Explanation / Answer

1.

The table is in 1NF as all the attributes are atomic. But all the non key attributes are not fully functionally dependent on the primary key PartID

Anomalies

Insertion Anomaly ---- For inserting data of Part,the Supplier details should also be inserted. But it is not practical.

Deletion Anomaly --- If we delete the details of Part,the Supplier details also get deleted. If the Supplier supplies only that Part ,his details will be lost.

Modification Anomaly ---- There is redundancy in the table , if an attribute is updated,it has to be updated at all places otherwise inconsistent data exists.

Relations in 3NF

Functional Dependencies

PartID ->PartName

SupName ->City

PartID,SupName ->Cost

Part(PartID ,PartName)

Supplier(SupName ,City)

Part_Supplier(PartID,SupName ,Cost)

underlined are primary keys and italicised are foreign keys

2.

1 ,2 The relation is in 1NF only as all the non key attributes are not fully functionally dependent on the composite primary key (CourseID, InstID)

3

Tables in 3NF

Course(CourseID , CourseName)

Instructor( InstID , InstName)

CourseInstructor(CourseID,InstID,ClassTime)

Job(JobID , JobTitle)

Employee(EmployeeID,EmpName,DeptNum)

3

PATIENT(PatientID,PatFname,PatLname,PatPhone,PhysID)

PHYSICIAN(PhysID,PhyFname,PhyLname,Department,HospID)

TREATMENT(TreatID,TreatName,Price,PatientID)

HOSPITAL(HospID,HospName,HospStreet,HospState,HospCity,HospZip)

underlined are primary keys and italicised are foreign keys

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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