[home page]

NAME$\underset{¯}{}$

 
CSE 670: Final Exam
 
Tu, Dec 9, 5:30–7:18
Open Notes, Open Books
The exam consists of $\underset{¯}{four}$ problems
Answers not appearing in designated spaces WILL NOT be graded

• Problem #1 (10 points) Consider the following table.

  

  1. Show the functional dependencies satisfied by the given data.

  2. Is the data in 3NF? Justify your reply.

  3. Is the data in BCNF? Justify your reply.

  4. Is the multivalued functional dependency $A\to \to B$ satisfied? Justify your reply.

  5. Is the multivalued functional dependency $B\to \to A$ satisfied? Justify your reply.

• Problem #2 (10 points)
  1. Consider the schema $\left\{A,B,C,D,E,F,G,H\right\}$ decomposition into $R_1=\left\{A,B,C\right\}$, $R_2=\left\{B,D,E,F\right\}$, and $R_3=\left\{A,G,H\right\}$. Which of the following functional dependencies is preserved, and which is not preserved, under the given decomposition? Justify your replies.

     $AB$	$\to$	$CH$
     $A$	$\to$	$DGH$
     $D$	$\to$	$AC$
     $G$	$\to$	$ACDH$

  2. Find two minimal covers for the following set of functional dependencies.

     $A$	$\to$	$BC$
     $B$	$\to$	$AC$
     $C$	$\to$	$AB$

• Problem #3 (10 points) Consider the schema $R=\left\{A,B,C,D,E\right\}$.
  1. Show that the decomposition $R_1=\left\{A,B\right\}$, $R_2=\left\{A,D\right\}$, $R_3=\left\{A,E\right\}$ $R_4=\left\{B,E\right\}$, $R_5=\left\{C,D,E\right\}$. is lossless join for the following functional dependencies.

     $A$	$\to$	$C$
     $B$	$\to$	$C$
     $C$	$\to$	$D$
     $DE$	$\to$	$C$
     $CE$	$\to$	$A$

  2. Find a lossless join functional-dependency preserving 3NF decomposition under the following functional dependencies. Justify your reply.

     $A$	$\to$	$C$
     $B$	$\to$	$C$
     $C$	$\to$	$D$
     $CE$	$\to$	$A$

• Problem #4 (10 points) Consider the following relations.

  EMPLOYEE

  NAME	SSN	CITY
  Anna	125-21-0987	Cincinnati
  Deborah	124-12-3456	Cleveland
  Doug	123-45-6789	Columbus
  Steve	251-21-9870	Cincinnati

  WORK_ON

  SSN	DNUM
  123-45-6789	1
  124-12-3456	2
  125-21-0987	1
  251-21-9870	1

  DEPARTMENT

  DNAME	DNUMBER	CITY
  compiler	1	Columbus
  database	2	Cincinnati

  1. Provide a relational calculus query that provides the names of all the employees that do not work alone in a department.

  2. Show the outcome of the following query.

     $\left\{\right.$ w.SSN, d.DNAME $|$ WORKS_ON(w) and DEPARTMENT(d) and ( $\exists$ e1, $\exists$ e2 ) ( EMPLOYEE(e1) and EMPLOYEE(e2) and (e1.SSN > w.SSN) and (e2.SSN < w.SSN) ) $\left.\right\}$

  3. Provide the translation of the following relational calculus query into SQL.

     $\left\{x.A,x.B|R_1\left(x\right)and\left(\exists y\right)\left(R_2\left(y\right)andy.C<>x.A\right)\right\}$