CSE 444 Midterm

Autumn 2001


Total number of points: 100 plus 10 bonus points 

1.      [25 points] Consider the E/R diagram below. (a) [10points] Design a relational schema for it, and underline all keys in every table. (b) [15points] Write the SQL statements for creating these tables, and include statements for primary keys, foreign keys, and unique attributes. Choose any reasonable domains for the attributes.  

2.      [15 points] Consider a relation R(A,B,C,D) with functional dependencies
à D, ADà C, BDà A. Answer the following questions:

a.      [5 points] Compute all keys.

b.      [5 points] Decompose the relation in BCNF.

c.      [5 points] Give an example of a database instance in which all given functional dependencies hold but ACDà B does not hold:












3. [40 points plus 10 bonus] Consider the following relational schema

                  User(login, name, office)
                  Relationship(uLogin, fName, type)  
                  File(name, size)
The file size is in bytes. The type of a Relationship is one of:




Their meaning is obvious.

a.      [10 points] Write a relational algebra expression equivalent to the query below. You may represent the expression as a tree.

FROM    File
WHERE name not in (SELECT File.name
                                    FROM User, Relationship, File
                                    WHERE User.name=”Smith” AND
                                                   User.login=Relationship.uLogin AND
                                                   Relationship.type=”owner” AND

b.      For each of the questions below write SQL queries that answers that question:

                                                        i.            [10 points] Write a SQL query that finds all user names that have write permissions to some file owned by “Mary” and share the same office with her.

                                                      ii.            [10 points] For each user that owns files whose total size is more than 5MB, return their names, total number of files owned, and total size of those files.

                                                    iii.            [10 points] We add the constraint that every user must have read permissions to all files to which she has write permissions.  Write SQL statement(s) to insert all needed read permissions to satisfy this constraint.

                                                     iv.            Bonus question [10 points] Retrieve all users who have read permissions only to files owned by “Smith”.

4.      [20 points] Consider the following relational schema about courses, homeworks for courses, students, and a relationship between students and homeworks:
                  Course(id, name)
                  HomeWorks(courseId, hwNumber, points)
                  Turnins(courseId, hwNumber, studentId, grade)
                  Students(id, name)
In each of the cases below, design a DTD for exporting the information to an XML format that includes information about course names, homeworks numbers and points, student names, and the grade each student got for every homework. It should not include information about course id’s and student id’s:

a.      [10points] The hierarchy is: courses (top level), homeworks, turnins, students

b.      [10points] The hierarchy is: students (top level), turnins, homeworks, courses.