Exam1—Solution Sketches
COSC 3480
October 11, 2005
Your Name:
Your SSN:
Problem 1 [18]:
Problem 2 [13]:
Problem 3 [13]:
Problem 4 [11]:
Problem 5 [4]:
Problem 5 [6]:
S [65]:
Grade:
The exam is “open books” and you have 75 minutes to complete the exam.
1) Designing E/R Schemas [18]
Design a “good” entity-relationship diagram that describes the following objects in a university application: students, departments, sections taught in the present and future, and courses. Departments have a name that uniquely identifies the department. Students are identified by a unique social security number, zero, one or multiple e-mail addresses, and an optional gpa (new students do not have a gpa yet). Courses have a unique course number and a course title. Courses are offered in one or more sections at a particular time. Sections are identified by the time they are offered (e.g. 10:30-noon TUTH) and by the course they are associated with. Additionally sections are characterized by the class room the section is taught in. Only information concerning sections that are taught in the present or in the future is stored in the database. Students take a course in a particular semester and receive a grade for their performance. Sometimes students take the same course again in a different semester. There are no limits on how many courses a student can complete, and on how many students completed a particular course. Each student is associated with a least one department. Some students are graduate students that are additionally characterized by their most recent GRE-score. Some graduate students work for a department and receive a salary for their services. Each department employs at most 75 graduate students; graduate students are not allowed to work for multiple departments.
Indicate min-max cardinalities for each relationship type; assign roles (role names) to each relationship if there are ambiguities! Use sub-types, if helpful to express constraints!
2) General Questions [13]
a) What is data independence? Why is data independence important? What do DBMS offer to accomplish data independence? [5]
1. Definition
2. Change IS/CS without having to change / with limited change of application programs
3. Hide Implementation Details from Programmers; create different views of databases,…
b) What is a database transaction? Why does a DBMS interleave the execution of different transactions instead of executing one transaction after the other? [5]
1. Atomic sequence of actions that is either is executed or not
2. Need not to wait for slow I/O operations to finish, keeping the CPU busy with work from other transaction while waiting for an I/O operation to complete.
c) What are the main differences between main memory and disk memory? [3]
1. main memory is more expensive
2. main memory is much faster
3. disk memory is much more reliable/non-volatile
3) Using Relational Algebra [13]
All the queries you have to write refer to the Sailor, Boat, and Reserves relations on page 101 (92 2nd edition) of the textbook. Give relational algebra expressions that correctly implement the following three queries:
a) “Give the sid of those sailors that have at least one reservation for a blue boat on 11/11/2003” [3]
psid(scolor = “blue”(Boat) lXl sday =“11/11/2003”(Reserves))
b) Give the sid of all sailors that have a reservation for a blue boat for 12/12/2003 and a reservation for a blue boat on 10/12/2003 [5]
(psid (sday =“12/12/2003”(Reserves) |X| (scolor = “blue”(Boat)) Ç psid (sday =“10/12/2003”(Reserves |X| (scolor = “blue”(Boat))))
c) Give the sids of those sailors that have reservations for all boats on 10/11/2005 [5]
psid,bid (sday =“10/11/2003” (Reserves)) / pbid (Boats))
4) Writing SQL Queries [11]
Write SQL queries to satisfy the following information requirements:
a) Give the ssn, sname of sailors that have a reservation of a green boat and a red boat on the same day [5]
SELECT S.sid,S.name
FROM Sailor S, Reservation R1, Reservation R2, Boat B1, Boat B2
WHERE S.sid=R1.sid and S.sid=R2.sid and R1.bid=B1.bid and R2.bid=B2.bid and
B1.color=red and B2.color=green and R1.day=R2.day;
b) Give bid and number of reservations for those boats that have more that 15 reservations for 10/10/2005 [6]
SELECT bid, count(*)
FROM Reserves R
WHERE R.day=’10/10/2005’
GROUP BY R.bid
HAVING Count(*)>15
1
5) FOREIGN KEY CONSTRAINTS [4]
Assume the following SQL syntax in given:
CREATE TABLE Enrolled
(sid CHAR(20),
cid CHAR(20),
grade CHAR(2),
FOREIGN KEY (sid)
REFERENCES Students
ON DELETE CASCADE
…
What is the meaning of the command ‘on delete cascade’? Be specific!
If a student gets deleted, all his/her enrollments into courses will also be deleted.
6) Mapping E/R to Relational Schemas [6]
Map the E/R diagram given below to a relational schema using the Dr. Eick’s default mapping that was discussed in class. Giving the graphical representation for the relational schema is sufficient (just identify all foreign and primary keys
Person(ssn,name)
Person (ssn, name)
Student(ssn, major) Department(name)
Employs(ssn, department, salary)
1