# CSE 444 Homework 2

Objectives:
To be able to translate from E/R diagrams to a relational database, to understand functional dependencies, Boyce-Codd Normal Form,  Third Normal Form, and XML representation of data.
3.1,(3.2 recommended), 3.3-3.7
Number of points:
100
Due date:
October 20
Assignment Tools
1. [10 points]
1. Create a relational schema that captures this E/R diagram.
2. Which relation in your relational schema represents the relationship "insures" in the E/R diagram, and why is that your representation?
3. Compare the representation of the relationships "drives" and "operates" in your schema, and explain why they are different.
4. What are the keys for each relation in your relational schema?
2. [20 points] Consider a relation with the schema R(A,B,C,D) and functional dependencies AB -> C, A -> D, BD -> A
1. Compute A+, AB+, and AC+.
2. What are all of the keys of R? (Hint: read on page 133.)
3. What are all the nontrivial functional dependencies that follow from the given dependencies? (Hint: read on page 131.)
3. [10 points] Consider the relational schema R(A,B,C,D) and the following set of functional dependencies: S = {A -> B, BC -> A, D -> C}.  For each of the functional dependencies X -> Y below indicate whether it logically follows from S.  If your answer is "yes", you should compute X+ showing that it contains Y.  If your answer is "no" you should give an example of a relational instance for R that satisfies S but does not satisfy X -> Y:
1. A -> C
2. BD -> A
3. CD -> A
4. BCD -> A
5. ABC -> D
4. [15 points] For the following relations and functional dependencies
1. Decompose the relations, as necessary, into collections of relations that are in BCNF. Show all of your work and explain which dependency violations you are correcting by your decompositions.
2. Decompose the relations into a collection that follows 3NF instead.
The relations:
1. R(A,B,C,D,E) with functional dependencies BD -> E,  C -> A.
2. S(A,B,C,D,E) with functional dependencies AB -> E, CD -> A, E -> D.
5. [20 points] (This is Exercise 3.6.8 from the book)
We say a set of attributes X is closed (with respect to a given set of functional dependencies) if X+=X. Given the closed attribute sets, this gives us some information on the underlying functional dependencies.

Consider a relation with schema R(A,B,C,D) and an unknown set of functional dependencies. In each of the cases below, give a set of functional dependencies that is consistent with the conditions given.

1. All sets attributes are closed.
2. The only closed sets are {} and {A,B,C,D}.
3. The only closed sets are {}, {A,B}, and {A,B,C,D}.
6. [20 points] Consider the following database schema about students and courses:

Student(sid, name, email), Course(cid, name, instructor, room), Enrolls(sid, cid, grade)

Underlined attributes denote keys. In Enrolls, sid and cid are foreign keys in Student and Course respectively. The following two constraints hold: each student is enrolled in some course, and each course has at least one student enrolled.
1. Design a DTD for exporting this data in XML to the dean's office.  The dean wants to see the data grouped by courses and needs to have access to all the information in the database.
2. Design a DTD for exporting this data to a student's association.  Group the data now by students, and hide the grade.
3. Give an example of a database instance for your schema and show both its XML views according to the DTD at the previous two points.