Is 337

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.

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.

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 “a”.

PATIENT (PATIENT NO, ADDRESS, ROOM NO, ADMIT DATE, TREATMENT ID)

ROOM (ROOM NO, PHONE, DAILY RATE)

PHYSICIAN (PHYSICIAN ID, NAME, DEPARTMENT ID)

TREATMENT (TREATMENT ID, DESCRIPTION, COST)

SUPERVISOR (SUPERVISOR ID, DEPARTMENT ID)

DEPARTMENT (DEPARTMENT ID, SUPERVISOR ID)

5. Consider the following 3NF relations about a sorority or fraternity:

MEMBER(Member_ID, Name, Address, Dues_Owed)

OFFICE(Office_Name, Officer_ID,...