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: 15pts  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: 15pts  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: 35pts  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.

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 Printers have a price of at most $300?

b. What manufacturers make laptops with a screen of at most 15 in?

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 PCs, but not Laptops.

f. Find the speeds that occur in two or more Laptops.

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. 2^nd Edition.

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

Cite your sources.

Submissions must be uploaded to GradeScope and marked by the due date.