# Homework 2

Question 1: 10pts Consider a relation with schema $R(a,b,c,d)$ and functional dependencies $S=\{ab\rightarrow c, c\rightarrow d, d\rightarrow a\}$

a. What are all the non-trivial FDs that follow from the given FDs?

There are many: you should restrict yourself to FDs with single attributes on the right side.
Hint – My solution has 14 nontrivial FDs (including the original 3) with single attributes on the RHS.

b. What are the superkeys of R?

c. What are candidate keys of R?

Question 2: 10pts  For each of the following relation schemas and sets of FDs:

a. $R(a,b,c,d)$ with FDs $S= ab\rightarrow c, c\rightarrow d, d\rightarrow a\}$
b. $R(a,b,c,d)$ with FDs $S= b\rightarrow c, c\rightarrow d\}$
c. $R(a,b,c,d)$ with FDs $S= ab\rightarrow c, bc\rightarrow d, cd\rightarrow a, ad\rightarrow b\}$

Assume $R$ is in 1NF.

Do the following:

1. Indicate all the 3NF violations. Describe how the FD violates 3NF.
2. Decompose the relations, as necessary, into collections of relations that are all in 3NF.

Hint – Remember to indicate the keys of the new relations

Question 3: 10pts  For each of the relation schemas and sets of FDs from Question 2:

1. Indicate all BCNF violations. Describe how the FD violates BCNF.
2. Decompose the relations, as necessary, into collections of relations that are all in BCNF.

Hint – Remember to indicate the keys of the new relations

Question 4: 25pts  This exercise builds upon the Products schema from the last homework. Recall that the database schema consists of four relations, whose schemas are:

Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

Some sample data for the relations are shown below.

Product

PC

Laptop

Printer

Write expressions of relational algebra to answer the following queries. You may use the sequence of operators notation if you wish. Show the result of your query. However, your answer should work for arbitrary data, not just this data.

a. What PC models have a speed of at most 3.00?

b. What manufacturers make laptops with a hard disk of at least 160GB?

c. Find the model number and price of all products (of any type) made my manufacturer B

d. Find the model numbers of all color laser printers

e. Find those manufacturers that sell Laptops, but not PCs.

f. Find those hard-disk sizes that occur in two or more PCs.

g. Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only once; e.g., list $(i,j)$ but not $(j,i)$.

Question 5: 5pts  Draw expression trees for each of your expressions of Q4 (only a-e)

Question 6: 25pts  Denote answers to Q4 (only a-e) in tuple relational calculus.

Question 7: 10pts  Suppose relations $R$ and $S$ have $m$ tuples and $n$ tuples respectively. Give the minimum and maximum numbers of tuples that the results of the following expressions can have:

a. $R\cup S$

b. $R\bowtie S$

c. $\sigma_C (R)\times S$, for some condition $C$

d. $\Pi_L (R)-S$, for some list of attributes $L$

Question 8: 05pts Using the Laptop relation from Q4, suppose we compute the projection $\Pi_{\textrm{speed}} (\textrm{Laptop})$. What are the values of this expression as a set? As a bag? What is the average value of tuples in this projection, when treated as a set? As a bag?

Some exercises are from Garcia-Molina, Ullman, Widom. Database Systems. 2nd Edition.

This is an individual assignment. Discussion is allowed, but students may not write submitted solutions in the presence of a group.