Download 6 Solved Problems in Database Systems | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
Name: Student ID:
MIDTERM EXAM, March 11, 2004
CS 311 Introduction to Database Management Systems Department of Computer Science University of Illinois at Urbana-Champaign
Exam Rules:
1) Close book and notes, 75 minutes
2) Please write down your name and student ID number NOW.
3) Please wait until being told to start reading and working on the exam.
4) If you think a problem is ambiguous, write down your assumptions, argue
that they are reasonable, then work on the problem using those assumptions.
Scores:
Problem 1: out of 20 points
Problem 2: out of 20
Problem 3: out of 20
Problem 4: out of 20
Problem 5: out of 10
Problem 6: out of 10
Total: out of 100 points
1. (20 points) ER and Translation to Relational Model:
a. [15 points] Create a relational schema that captures this E/R diagram. To translate is-a hierarchies, use the ER style translation. For every relation in your schema, specify the key of that relation. b. [5 points] What is the key for entity " Contract_Emps "? And what is the key for “Hourly_Emps”? Briefly explain your answer. (Note: Part (a) asks for key of relation, part (b) asks for key of entities.)
3. (20 points, 10 points each) Relational Algebra & SQL Queries:
Consider a database schema with the following relations:
Student (ssn, name) Prof (ssn, name) Course (number, instructor-ssn, title, credits, room#) Enroll (student-ssn, course#) Room (number, capacity)
a. Write a relational algebra query that finds the names of all students who are enrolled in a class taught by “Jones”.
b. Write a SQL QUERY that finds the names of all students who are NOT enrolled in two classes held in the same room. (Note: if a student is enrolled in three classes held in the same room, you need to return the name of that student.)
4. (20 points, 10 points each) SQL Queries:
Using the same schema from Question 3, which is duplicated below:
Student (ssn, name) Prof (ssn, name) Course (number, instructor-ssn, title, credits, room#) Enroll (student-ssn, course#) Room (number, capacity)
a. Write an SQL query that lists the title of all courses that are either taught by “Smith” OR are taught in room number 444. Do not list duplicate titles.
b. Write an SQL query that considers all the courses that have ever been taught by “Brown” and are of 3 credits, and groups them according to title. For each course, the query should compute the average capacity of rooms in which the course has been offered, then return only courses for which this average is more than 20.
6. (10 points) Constraints and Triggers
Briefly discuss the tradeoffs among checks, assertions, and triggers.
