CSE444 5/6/99

HW4 Solutions

Exercise 8.3

  1. TRC {D | $ SÎ Supplier(S.name = D.name Ù $ CÎ Catalog(C.sid = S.sid Ù $ PÎ Parts(P.pid = C.pid Ù P.color = "red")))}
  2. DRC {<SN> | $ SI,SA(<SI, SN, SA> Î Suppliers Ù $ CI,CC(<SI, CI, CC>Î Catalog Ù $ PN,PC(<CI,PN,PC>Î Parts Ù PC = "red")))}

  3. TRC {D | $ SÎ Supplier(S.sid = D.sid Ù " PÎ Parts( $ CÎ Catalog(C.sid = S.sid Ù P.pid = C.pid )))}

DRC {<SI> | $ SN,SA(<SI,SN,SA>Î Suppliers Ù " <PI,PN,PC>Î Parts( $ CC(<SI, PI, CC>Î Catalog)))}

Exercise 15.3

  1. Key: ACE
  2. FD's we have are A->B, C->D, and ACE->ABCDE

    Relation is in 1NF. A->B violates BCNF, 3NF, and 2NF.

    Breakdown using the violator A->B as a basis. Our new relations are

    R2(A,B), R3(A,C,E,D,)

    R2 is fine, but we must check R3. C->D violates R3. Breakdown using violator into:

    R2(A,B), R4(A,C,E), R5(C,D)

    These are all in BCNF, so we are done.

  3. Key: AB

FD's we have (that apply) are B->F and A,B->A,B,F

Relation is in 1NF because B->F violates. Breakdown using violator.

R3(A,B), R4(B,F)

These are both in BCNF so we are done.

Exercise 15.4

  1. We can only say that (b) does not hold. 2,3->1 and 2,3->4 is the violation
  2. Dependencies are a relation level concept. We can not infer the dependencies that hold over the relation given just an instance of it (see page 400 bottom).

ER to relational model

BANK(Code, name, Addr)

BANK_BRANCH(BranchNo, Code, Addr)

ACCOUNT(AcctNo, BranchNo, Code, Balance, Type)

LOAN(LoanNo, BranchNo, Code, Amount, Type)

A-C(AcctNo, SSN)

L-C(LoanNo, SSN)

CUSTOMER(SSN, Phone, Name, Addr)

It's ok if you left 'Code' out of Account or Loan. Technically, since BANK_BRANCH is a weak_entity, you need 'Code' to distinguish accounts and loans. However, the book was not clear on how to translate weak entities to the relational model. So, if you treated it as a non-weak entity, that is fine.