Quiz 2

This quiz is to be turned in at 2:05 PM.

Question 1 (25 points)

Draw an entity-relationship diagram that would be useful keeping up with all homework assignments in all courses you here taken at UNCA. Your ER-diagram should have about three entities for useful objects such as courses and faculty.

Question 2 (75 points)

Consider the following table, which is rather like the university purchase order system:

PO# LINE# DEPT REQUESTOR R-OFFICE ITEM QUANT COST-EACH ACCNT-NUM
P93444 1 CSCI NATION, CARY RBH221A DESK 3 300.00 205555
P93444 2 CSCI NATION, CARY RBH221A COIN 14 1.00 205555
P83777 1 CSCI ANTHONY, SUSAN RBH035 COIN 133 1.00 205556
P45456 1 PHYS EINSTEIN, AL RBH100 MASS 10 13335.67 307777
P45457 1 PHYS EINSTEIN, AL RBH100 ENERGY 1 3.67 307777
P67321 1 MUSC PRESLEY, ELVIS HIP175 STAMP 1356 .29 406777

After interviewing the appropriate university officials you find out the following facts:

  1. All items on a single PO must be paid from the same account number.
  2. Each item of PO is identified by a line number.
  3. Only certain people within a department are allowed to authorize PO's.
  4. All items are purchased under a state contract that specifies one vendor and one price for that item.

Subquestion 2.A

What are the possible candidate keys for the table?

Subquestion 2.B

Is the table in First Normal Form? If so, why? If not, how could it be put into First Normal Form?

Subquestion 2.C

What are the dependencies in the table? You will undoubtedly need to make some assumptions in answering this question. Please state your assumptions clearly.

Subquestion 2.D

Is the table now (after possible processing in subquestion 2.B) in Second Normal Form? If so, why. If not, how could it be put into Second Normal Form?

Subquestion 2.E

Is the table now in Third Normal Form? If so, why. If not, how could it be put into Third Normal Form?

Subquestion 2.F

Is the table now in Boyce-Codd Normal Form? If so, why. If not, how could it be put into Boyce-Codd Normal Form?

Back to the CSCI 363 home page