final exam

NAME

CSE 670: Final Exam

Mo, March 16
Open Notes, Open Books
The exam consists of 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 = {A,B,C,D,E,G,H} and the following set F of functional dependencies.
AB → C
B → D
CD → E
CE → GH
G → A
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 → AG or provide a counter example to disprove the case.
4. Provide two records to R under which the multivalued functional dependency AB →→ CDE is satisfied, while the multivalued functional dependency AB →→ CD is not satisfied.
Problem #3 (10 points) Consider the decomposition R₁ = {A,C,E}, R₂ = {B,C,F}, R₃ = {D,E,F} of R = {A,B,C,D,E,F}.
1. Does the decomposition satisfy the lossless join property under the functional dependency AB → CDEF? Justify your answer.
2. Does the decomposition satisfy the functional dependencies preservation property under the functional dependency AB → 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)

Translate the following relational calculus query into SQL.
w.SSN, d.DNAME | WORKS_ON(w) and DEPARTMENT(d) and ( ∃ e1, ∃ e2 ) ( EMPLOYEE(e1) and EMPLOYEE(e2) and (e1.SSN = w.SSN) and (e2.SSN = w.SSN + 1) )

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

w.DNUM, d.DNAME | WORKS_ON(w) and DEPARTMENT(d) and ( ∃ e1, ∃ e2 ) ( EMPLOYEE(e1) and EMPLOYEE(e2) and (e1.SSN = w.SSN) and (e2.SSN = w.SSN + 1) )

will produce the following output.

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

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

AB	→ C
B	→ D
CD	→ E
CE	→ GH
G	→ A