]
>
final exam
[home page]
NAME
CSE 670: Final Exam
Mo, March 16
Open Notes, Open Books
The exam consists of
problems
Answers not appearing in designated spaces WILL NOT be graded
- Problem #1 (10 points)
- Map the given ER-diagram into relational data schemas. The outcome should not include more than six
schemas.
- Provide a Java program with embedded SQL code which performs the following operations.
- Make a query to the database to get the table PEOPLE(Name,SSN).
- Find the person listed last in the table PEOPLE(Name,SSN).
- Make a query to the database to determine the number of times the name of the last listed person is mentioned in
the table.
- 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
and the following set
of functional dependencies.
- Find all the candidate keys in .
- Choose a primary key and determine whether the schema is in 2NF? Justify your answer.
3NF? Justify your answer.
BCNF? Justify your answer.
- Either use Armstrong inference rules to prove the functional dependency
or
provide a counter example to disprove the case.
- Provide two records to under which
the multivalued functional dependency
is satisfied, while the multivalued functional dependency
is not
satisfied.
- Problem #3 (10 points)
Consider the decomposition ,
,
of
.
- Does the decomposition satisfy the lossless join property under the functional dependency
? Justify
your answer.
- Does the decomposition satisfy the functional dependencies preservation property under the functional dependency
? Justify
your answer.
- 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.