1. Assume that at Pine Valley Furniture products are composed of components, pro
ID: 3545988 • Letter: 1
Question
1. Assume that at Pine Valley Furniture products are composed of components, products are assigned to salespersons, and components are produced by vendors. Also assume that in the relation PRODUCT (Prodname, Salesperson, Compname, Vendor), Vendor is functionally dependent on Compname and Compname is functionally dependent on Prodname. Eliminate the transitive dependency in this relation and form 3NF (third normal form) relations. 2. Transform the E-R diagram of Figure 8-3 into a set of 3NF relations. Make up a primary key and one or more non-keys for each entity. 3. Transform the E-R diagram of Figure 9-21 into a set of 3NF relations. 4. Consider the list of individual 3NF relations below. These relations were developed from several separate normalization activities. PATIENT(Patient_ID, Room_Number, Admit_Date, Address) ROOM(Room_Number, Phone, Daily_Rate) PATIENT(Patient_Number, Treatment_Description, Address) TREATMENT(Treatment_ID, Description, Cost) PHYSICIAN(Physician_ID, Name, Department) PHYSICIAN(Physician_ID, Name, Supervisor_ID) (a) Merge these relations into a consolidated set of 3NF relations. State whatever assumptions you consider necessary to resolve any potential problems you identify in the merging process. (b) Draw an E-R diagram for your answer to part 1. Assume that at Pine Valley Furniture products are composed of components, products are assigned to salespersons, and components are produced by vendors. Also assume that in the relation PRODUCT (Prodname, Salesperson, Compname, Vendor), Vendor is functionally dependent on Compname and Compname is functionally dependent on Prodname. Eliminate the transitive dependency in this relation and form 3NF (third normal form) relations. 2. Transform the E-R diagram of Figure 8-3 into a set of 3NF relations. Make up a primary key and one or more non-keys for each entity. 3. Transform the E-R diagram of Figure 9-21 into a set of 3NF relations. 4. Consider the list of individual 3NF relations below. These relations were developed from several separate normalization activities. PATIENT(Patient_ID, Room_Number, Admit_Date, Address) ROOM(Room_Number, Phone, Daily_Rate) PATIENT(Patient_Number, Treatment_Description, Address) TREATMENT(Treatment_ID, Description, Cost) PHYSICIAN(Physician_ID, Name, Department) PHYSICIAN(Physician_ID, Name, Supervisor_ID) (a) Merge these relations into a consolidated set of 3NF relations. State whatever assumptions you consider necessary to resolve any potential problems you identify in the merging process. (b) Draw an E-R diagram for your answer to partExplanation / Answer
(1) We assume that the relation is already in 2NF, and elimination of the transitive dependency is sufficient. The relation PRODUCT can be replaced with 2 relations, PRODUCT and VENDOR, as follows:
PRODUCT has fields Prodname(Primary Key), Salesperson and Compname. Salesperson and Compname are functionally dependent Prodname.
VENDOR has fields Compname(Primary Key) and Vendor. Vendor is functionally dependent on Compname.
There are no transitive dependencies.
proceed as::
1NF is the most basic of normal forms - each cell in a table must contain only one piece of information, and there can be no duplicate rows.
2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. As Chris said in his response:
The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF] (so help meCodd).
2NF
Say you have a table containing courses that are taken in a certain semester, and you have the following data:
|-----Primary Key----| uh oh |
V
CourseID| Semester | #Places | Course Name |
-------------------------------------------------|
IT101 | 2009-1 | 100 | Programming |
IT101 | 2009-2 | 100 | Programming |
IT102 | 2009-1 | 200 | Databases |
IT102 | 2010-1 | 150 | Databases |
IT103 | 2009-2 | 120 | Web Design |
This is not in 2NF, because the fourth column does not rely upon the entire key - but only a part of it. The course name is dependent on the Course's ID, but has nothing to do with which semester it's taken in. Thus, as you can see, we have duplicate information - several rows telling us that IT101 is programming, and IT102 is Databases. So we fix that by putting the course name into another table, where CourseID is the ENTIRE key.
Primary Key |
CourseID | Course Name |
---------------------------|
IT101 | Programming |
IT102 | Databases |
IT103 | Web Design |
No redundancy!
3NF
Okay, so let's say we also add the name of the teacher of the course, and some details about them, into the RDBMS:
|-----Primary Key----| uh oh |
V
Course | Semester | #Places | TeacherID | TeacherName |
---------------------------------------------------------------|
IT101 | 2009-1 | 100 | 332 | Mr Jones |
IT101 | 2009-2 | 100 | 332 | Mr Jones |
IT102 | 2009-1 | 200 | 495 | Mr Bentley |
IT102 | 2010-1 | 150 | 332 | Mr Jones |
IT103 | 2009-2 | 120 | 242 | Mrs Smith |
Now it should be obvious that TeacherName is dependent on TeacherID - so this is not in 3NF. To fix this, we do much the same as we did in 2NF - take TeacherName out of this table, and put it in its own, which has TeacherID as the key.
Primary Key |
TeacherID | TeacherName |
---------------------------|
332 | Mr Jones |
495 | Mr Bentley |
242 | Mrs Smith |
No redundancy!!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.