Homework 2

Question 1: 10pts Consider a relation with schema $R(a,b,c,d)$ and functional dependencies $S=\{a,b\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= \{a,b\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= \{a,b\rightarrow c; ~~ b,c\rightarrow d; ~~ c,d\rightarrow a; ~~ a,d\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 job board and hiring system from the last homework. Recall that the database schema consists of several relations. Let’s pick four main ones:

• Posting(company, id, category)
• FullTime(id, salary, hours, bonus)
• Internship(id, type, duration, pay_per_mo)
• Contract(id, type, location, pay)

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 FullTime jobs have a salary of more than $130,000?

b. What companies have FullTime jobs requiring fewer than 30 hours?

c. Find the ID and compensation of all job posts (of any category) posted by IBM.

d. Find the IDs of all Internships with a duration of 1 month.

e. Find the companies that post FullTime jobs, but do not post Internships

f. Find those Internships that have the same pay_per_mo

g. Find those FullTime jobs with the same salary and number of hours. A pair of jobs 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 FullTime relation from Q4, suppose we compute the projection $\Pi_{\textrm{salary}} (\textrm{FullTime})$. What are the values of this expression as a set? As a bag? What is the mean-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.