[home page]

NAME$\underset{¯}{}$

 
CSE 670: Final Exam
 
Mo, March 16
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)
  1. Map the given ER-diagram into relational data schemas. The outcome should not include more than six schemas.
     

  2. Provide a Java program with embedded SQL code which performs the following operations.
     1. Make a query to the database to get the table PEOPLE(Name,SSN).
     2. Find the person listed last in the table PEOPLE(Name,SSN).
     3. Make a query to the database to determine the number of times the name of the last listed person is mentioned in the table.
     4. Print the name and its count

     Don’t include the code that establishes the connection with the database.

• Problem #2 (10 points) Consider the schema $R=\left\{A,B,C,D,E,G,H\right\}$ and the following set $F$ of functional dependencies.

  $\begin{array}{cc}AB& \to C\\ B& \to D\\ CD& \to E\\ CE& \to GH\\ G& \to A\\ \end{array}$

  1. Find all the candidate keys in $F$.

  2. Choose a primary key and determine whether the schema is in 2NF? Justify your answer.

     3NF? Justify your answer.

     BCNF? Justify your answer.

  3. Either use Armstrong inference rules to prove the functional dependency $CD\to AG$ or provide a counter example to disprove the case.

  4. Provide two records to $R$ under which the multivalued functional dependency $AB\to \to CDE$ is satisfied, while the multivalued functional dependency $AB\to \to CD$ is not satisfied.

     

• Problem #3 (10 points) Consider the decomposition $R_1=\left\{A,C,E\right\}$, $R_2=\left\{B,C,F\right\}$, $R_3=\left\{D,E,F\right\}$ of $R=\left\{A,B,C,D,E,F\right\}$.
  1. Does the decomposition satisfy the lossless join property under the functional dependency $AB\to CDEF$? Justify your answer.

  2. Does the decomposition satisfy the functional dependencies preservation property under the functional dependency $AB\to CDEF$? Justify your answer.

  3. Provide a single functional dependency under which the decomposition is lossless join, but not functional dependencies preserving.

• Problem #4 (10 points) Consider a database having the following relations.
      EMPLOYEE(NAME, SSN, CITY) 
      WORK_ON(SSN, DNUM) 
      DEPARTMENT(DNAME, DNUMBER, CITY) 
  
  1. Translate the following relational calculus query into SQL.

     $\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 + 1) ) $\left.\right\}$

  2. What changes should be made in the data

     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

     so that the query

     $\left\{\right.$ w.DNUM, 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 + 1) ) $\left.\right\}$

     will produce the following output.

        DNUM        DNAME 
      ----------- ---------- 
         2        compiler 
         2        database 
         1        compiler 
         1        database 
     

  3. Provide a relational calculus query whose outcome is the names of all the employees that work in more than one department.