# CSE 444 Midterm

Autumn 2001

Name___________________________________

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
ABC

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:

 A B C D

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

File(name, size)

The file size is in bytes. The type of a Relationship is one of:

type=”owner”

type=”writePermission”.

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.

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

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)