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à 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:
|
A |
B |
C |
D |
|
|
|
|
|
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:
type=”owner”
type=”readPermission”
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
User.login=Relationship.uLogin 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)
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.