CSE444 5/6/99
HW4 Solutions
Exercise 8.3
DRC {<SN> | $ SI,SA(<SI, SN, SA> Î Suppliers Ù $ CI,CC(<SI, CI, CC>Î Catalog Ù $ PN,PC(<CI,PN,PC>Î Parts Ù PC = "red")))}
DRC {<SI> | $ SN,SA(<SI,SN,SA>Î Suppliers Ù " <PI,PN,PC>Î Parts( $ CC(<SI, PI, CC>Î Catalog)))}
Exercise 15.3
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.
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
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.