computer science relational schema

CS34800: Homework 2

Spring 2018

Due: Thursday April 12, 2018 11:59PM on Blackboard (There will be a 10% penalty for each late calendar-day. After five calendar

days, the homework will not be accepted.)

1) (15 pts) Consider the following relational schema: worker(wid: integer, wname: string, age: integer, salary: real) works(wid: integer, cid: integer, time: integer) company(cid: integer, budget: real, managerid: integer)

Here, every manager is also a worker. Now, answer the following questions:

i. Define a table constraint on worker that will ensure that every worker’s salary is at least $4000.

ii. Define a table constraint on company that will ensure that all managers have age > 50.

iii. If you replace the above table constraint in (ii) with an equivalent asser- tion, explain which will be better in this particular scenario.

2) (15 pts) Given the following Relation R and the set of Functional Depen- dencies (FD) FD that hold on R, find a minimum cover of FD. Show your work.

a) R(K, L, M, N, O, P, Q, R) FD: K → L KLMN → O OP → Q OP → R KMNP → O KMNP → R

b) R(P, Q, R, S, T, U, V, W ) FD: Q → U U → V PQ → WST SU → TR V T → RW

1

R → W

3) (20 pts) Given the following Relation R and the set of Functional Depen- dencies (FD) FD that hold on R, Find the following: a) Identify the candidate key(s) for R. b) what is the highest normal form of R(1NF, 3NF, orBCNF ).

Show your work for all the above. R(K, L, M, N)

FD: KL → M KL → N M → K N → L

4) (10 pts) Given the following decomposition of the relation R and the set of functional dependencies FD, is the decomposition dependency-preserving? Justify your answer (with explanation).

R(K, L, M, N, O, P, Q, R, S, T )

FD: KL → M K → NO L → P P → QR N → ST

Decomposition: R1(K, L, M, N, O) R2(L, P, Q, R) R3(N, S, T )

5) (20 pts) Given a relation R and the set of functional dependencies FD on R, find out a decomposition of R into dependency-preserving lossless-join 3NF relations. Show your work.

R(K, L, M, N, O, P, Q, R, S, T )

FD: KL → M LN → OP KN → QR K → S R → T

6) (20 pts) Given a relation R and the set of functional dependencies FD that hold for R,

2

(i) Identify the best normal form that R satisfies (1NF, 3NF, orBCNF ). (ii) If R is not in BCNF , decompose it into a set of BCNF relations that

preserve the dependencies. Otherwise explain why there is no BCNF decom- position for R.

Show your work for all the above.

R(K, L, M, N) FD:

KLM → N N → K

3