Download Midterm Exam Solutions - Fall 2006 - Database Systems | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
Name: NetID:
MIDTERM EXAM, March 9, 2006
CS 411 Database Systems Department of Computer Science University of Illinois at Urbana-Champaign
Exam Rules:
1) Close book and notes, 75 minutes , scratch papers are allowed.
2) Please write down your name and NetID number NOW.
3) Please wait until being told to start reading and working on the exam.
4) No question can be asked during the exam (due to departmental regulations, to
be fair to off-campus students). If you think a problem is ambiguous, write
down your assumptions, argue that they are reasonable, then work on the
problem using those assumptions.
5) No electronic devices are permitted.
Scores:
Problem 1: out of 15 points
Problem 2: out of 15
Problem 3: out of 20
Problem 4: out of 30
Problem 5: out of 20
Total: out of 100 points
Problem 1. (15 points) ER and translation to relation model
Translate the following ER diagram into a relational schema. For each relation in your schema, specify the key of that relation. In translating a subclass hierarchy, use the ER style translation.
customer(customer-id, name, street, city) loan(loan-number, amount) payment(p-number,loan-number, p-date, p-amount) borrows(customer-id, loan-number) account(account-number, balance) owns(customer-id,account-number, start-date) Savings-account(account-number, Interest-rate) Checking-account(account-number, Overdraft-limit)
Grading Scheme:
- 4pts for the “payment” relation
- 3pts for the “Savings-account” relation
- 3pts for the “Checking-account” relation
- 1pt for each other relation
customer
name city
loan
loan-number amount
payment
p-number p-amount
account
balance
borrows pays
owns
IS-A
account-number
street
p-date
start-date
customer-id
Checking-account
Overdraft-limit
Savings-account
Interest-rate
Problem 3. (20 points) Relational Algebra
This problem has 2 parts.
Consider the following database schema:
course (course#, dept-name) enroll (studentID, course#, status)
(a) (10 points) Write a relational algebra expression to find the course# of all the courses offered by CS department.
Project_{course#}(Select_{dept-name==”CS”}(Course))
Grading Scheme:
(b) (10 points) Write a relational algebra expression to find the studentIDs of all the students who are not enrolled in course# 411.
Project_{studentID}(enroll) – Project_{studentID}(Select_{course# == “411”}(Enroll))
Some students answered: Project_{studentID}(Select_{course# <> “411”}(Enroll))
This answer is incorrect. Suppose there is a student who does not register for 411, however, the above query would still return the studentID for that student if he/she is registered for any course other than 411.
And a few students answered: Project_{studentID}(enroll – Select_{course# == “411”}(Enroll))
This was a good attempt at removing above problem, however, still suffers from that problem.
A good way to verify your relational algebra expression or sql query is to try on a small example.
Grading Scheme:
- For the two incorrect answers, we award 5/10 points.
Problem 4. (30 points) SQL
This problem has 3 parts.
Again consider the following database schema:
course (course#, dept-name) enroll (studentID, course#, status)
(a) (10 points) Write a SQL query that finds the studentID of all the students who are enrolled in at least one course offered by CS department and are not enrolled in any courses offered by EE department.
{SELECT studentID FROM course, enroll WHERE course.course# = enroll.course# AND dept-name = ‘CS’} EXCEPT {SELECT studentID FROM course, enroll WHERE course.course# = enroll.course# AND dept-name = ‘EE’}
(b) (10 points) Write a SQL query that lists the course# of all the courses for which more than 50 students are enrolled.
SELECT course# FROM enroll GROUP BY course# HAVING count(studentID)>
(c) (10 points) Write a SQL query that finds the studentID of all the students who are enrolled in the maximum number of courses. (For example, suppose there are 3 students, each is enrolled in 5 courses, and all other students are enrolled in fewer than 5 courses. Then you are expected to return the studentID of these 3 students.)
CREATE VIEW v AS SELECT student ID, count(course#) as NumOfCourses FROM enroll GROUP BY studentID
SELECT studentID FROM v WHERE NumOfCourses=max(NumOfCourses)
Name: NetID:
MIDTERM EXAM, March 9, 2006
CS 411 Database Systems Department of Computer Science University of Illinois at Urbana-Champaign
Exam Rules:
1) Close book and notes, 75 minutes , scratch papers are allowed.
2) Please write down your name and NetID number NOW.
3) Please wait until being told to start reading and working on the exam.
4) No question can be asked during the exam (due to departmental regulations, to
be fair to off-campus students). If you think a problem is ambiguous, write
down your assumptions, argue that they are reasonable, then work on the
problem using those assumptions.
5) No electronic devices are permitted.
Scores:
Problem 1: out of 15 points
Problem 2: out of 15
Problem 3: out of 20
Problem 4: out of 30
Problem 5: out of 20
Total: out of 100 points
Problem 1. (15 points) ER and translation to relation model
Translate the following ER diagram into a relational schema. For each relation in your schema, specify the key of that relation. In translating a subclass hierarchy, use the ER style translation.
customer(customer-id, name, street, city) loan(loan-number, amount) payment(p-number,loan-number, p-date, p-amount) borrows(customer-id, loan-number) account(account-number, balance) owns(customer-id,account-number, start-date) Savings-account(account-number, Interest-rate) Checking-account(account-number, Overdraft-limit)
Grading Scheme:
- 4pts for the “payment” relation
- 3pts for the “Savings-account” relation
- 3pts for the “Checking-account” relation
- 1pt for each other relation
customer
name city
loan
loan-number amount
payment
p-number p-amount
account
balance
borrows pays
owns
IS-A
account-number
street
p-date
start-date
customer-id
Checking-account
Overdraft-limit
Savings-account
Interest-rate
Problem 3. (20 points) Relational Algebra
This problem has 2 parts.
Consider the following database schema:
course (course#, dept-name) enroll (studentID, course#, status)
(a) (10 points) Write a relational algebra expression to find the course# of all the courses offered by CS department.
Project_{course#}(Select_{dept-name==”CS”}(Course))
Grading Scheme:
(b) (10 points) Write a relational algebra expression to find the studentIDs of all the students who are not enrolled in course# 411.
Project_{studentID}(enroll) – Project_{studentID}(Select_{course# == “411”}(Enroll))
Some students answered: Project_{studentID}(Select_{course# <> “411”}(Enroll))
This answer is incorrect. Suppose there is a student who does not register for 411, however, the above query would still return the studentID for that student if he/she is registered for any course other than 411.
And a few students answered: Project_{studentID}(enroll – Select_{course# == “411”}(Enroll))
This was a good attempt at removing above problem, however, still suffers from that problem.
A good way to verify your relational algebra expression or sql query is to try on a small example.
Grading Scheme:
- For the two incorrect answers, we award 5/10 points.
Problem 4. (30 points) SQL
This problem has 3 parts.
Again consider the following database schema:
course (course#, dept-name) enroll (studentID, course#, status)
(a) (10 points) Write a SQL query that finds the studentID of all the students who are enrolled in at least one course offered by CS department and are not enrolled in any courses offered by EE department.
{SELECT studentID FROM course, enroll WHERE course.course# = enroll.course# AND dept-name = ‘CS’} EXCEPT {SELECT studentID FROM course, enroll WHERE course.course# = enroll.course# AND dept-name = ‘EE’}
(b) (10 points) Write a SQL query that lists the course# of all the courses for which more than 50 students are enrolled.
SELECT course# FROM enroll GROUP BY course# HAVING count(studentID)>
(c) (10 points) Write a SQL query that finds the studentID of all the students who are enrolled in the maximum number of courses. (For example, suppose there are 3 students, each is enrolled in 5 courses, and all other students are enrolled in fewer than 5 courses. Then you are expected to return the studentID of these 3 students.)
CREATE VIEW v AS SELECT student ID, count(course#) as NumOfCourses FROM enroll GROUP BY studentID
SELECT studentID FROM v WHERE NumOfCourses=max(NumOfCourses)
