Homework 2 Relational schema

1) (10 pts) Consider the following relational schema: worker(person-name, street, city ) works(person-name, company-name, salary) company(company-name, city) manages(person-name, manager-name)
Write each of the following queries using Query-by-Example (QBE): Assume that all workers work for at most one company.
i. Find all workers who earn more than the average salary of all workers of their company. ii. Find the company that has the most workers. iii. Find the companies whose workers earn a higher salary, on average, than the average salary at ”NY Corporation”.
2) (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) i. Using SQL, define a table constraint on worker that will ensure that every worker’s salary is at least $5000. ii. Define a table constraint on company that will ensure that all managers have age 40. iii. Define an assertion on company that will ensure that all managers have age 40. iv. Compare this assertion (iii) with the equivalent table constraint (ii). Explain which is better.
3) (10 pts) Given the following Relation R and the set of Functional Dependencies (FD) S that hold on R, find all the candidate keys for R. Show your work. R(A,B,C,D,E,F)
1
S: DF → C BC → F E → A ABC → E 4) (10 pts) Given the following Relation R and the set of Functional Dependencies (FD) S that hold on R, what is the highest possible normal form of R(1NF,3NF,orBCNF) you can decompose R into with respect to S? Show your work. R(A,B,C,D,E,F)
S: A → D B → C AB → E E → F 5) (15 pts) Suppose that Relation R of the previous question has been decomposed into the following relations R1,R2 and R3. Now, identify the highest normal form (1NF,3NF,orBCNF) for each of the relations in the decomposition. Show your work. R1(A,B,E,F) R2(A,B,C) R3(A,D)
6) (10 pts) Given the following decomposition of Relation R and the set of functional dependencies S, is the decomposition dependency-preserving? Explain your answer. R(K,L,M,N,O,P,Q,R,S,T)
S: 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)
7) (15 pts) Given a relation R and the set of functional dependencies S on R, find out a decomposition of R into dependency-preserving lossless-join 3NF relations. Show your work.
2
R(K,L,M,N,O,P,Q,R,S,T)
S: KL → M LN → OP KN → QR K → S R → T 8) (15 pts) Given a relation R and the set of functional dependencies S that hold for R, (i) Identify the candidate key(s) for R. (ii) Identify the best normal form that R satisfies (1NF,3NF,orBCNF). (iii) If R is not in BCNF, decompose R into a set of BCNF relations that preserve the dependencies. Otherwise, explain why there is no BCNF decomposition for R. R(A,B,C,D) S: ABC → D D → A
3