information system design
Answer the following questions
(please copy the question’s text before each of your answers) and submit them
to your professor for grading.
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 “a”.
5. Consider the following 3NF
relations about a sorority or fraternity:
MEMBER(Member_ID, Name,
Address, Dues_Owed)
OFFICE(Office_Name,
Officer_ID, Term_Start_Date, Budget)
EXPENSE(Ledger_Number,
Office_Name, Expense_Date, Amt_Owed)
PAYMENT(Check_Number,
Expense_Ledger_Number, Amt_Paid)
RECEIPT(Member_ID, Receipt_Date,
Dues_Received)
COMMITTEE(Committee_ID,
Officer_in_Charge)
WORKERS(Committee_ID,
Member_ID)
(a) Foreign keys are not indicated
in these relations. Decide which attributes are foreign keys and justify your
decisions. (b) Draw an E-R diagram for these relations, using your answer to
part “a”. (c) Explain the assumptions you made about cardinalities in your
answer to part “b”. Explain why it is said that the E-R data model is more
expressive or more semantically rich than the relational data model.
6. Consider the following functional
dependencies:
Applicant_ID --> Applicant_Name
Applicant_ID -->
Applicant_Address
Position_ID --> Position_Title
Position_ID -->
Date_Position_Opens
Position_ID --> Department
Applicant_ID + Position_ID -->
Date_Applied
Applicant_ID + Position_ID +
Date_Interviewed --> (Nothing)
(a) Represent these attributes with
3NF relations. Provide meaningful relation names. (b) Represent these
attributes using an E-R diagram. Provide meaningful entity and relationship
names.
7. Suppose you were designing a file
of student records for your university’s placement office. One of the fields
that would likely be in this file is the student’s major. Develop a coding
scheme for this field that achieves the objectives outlined in Chapter 10 for
field coding.
8. Suppose you created a file for
each relation in your answer to question “3” above. If the following queries
represented the complete set of accesses to this database, suggest and justify
what primary and secondary key indices you would build.
(a) For each PART in Item_Number
order list in Vendor_ID, sequence all the vendors and their associated prices
for that part. (b) List all PART RECEIPTs, including related PART fields for
all the parts received on a particular day. (c) For a particular VENDOR, list
all the PARTs and their associated prices that VENDOR can supply.
