[SOLVED] database SQL 1

1

CS430/630 Final Exam Practice

40 points, 150 minutes

For questions 1-3, you are given the following schema:

Students (sid:integer, sname:string, age:integer)

Courses (cid:integer, cname:integer, credits:integer)

Grades(sid:integer, cid:integer, grade:string)

The meaning of attributes is as follows:

sid: unique student identifier, primary key in table Students
cid: unique course identifier, primary key in table Courses
sname: student name
age: student age
cname: course name

credits: number of credits for a course
grade: the grade obtained by student identified by sid for course identified by cid; sid and

cid are foreign keys referring to the sid and cid fields in the Students and Courses
tables, respectively.

Question 1

Write relational algebra expressions for the following queries given the schema above:

(a) Find the grades that students of age 20 obtained in courses with 4 credits.
(b) Find the names of students who took a course named Calculus and did not get a C grade in

any course.
(c) Find the ages of students who got an A in some course with 3 credits or who got a B in any

course.
(d) Find the maximum age among students who took Calculus.

Note: for Q1, you are NOT ALLOWED to use SQL, answers in SQL will not receive any marks. Derive
relational algebra expressions only.

Question 2

Write SQL queries for the following:

(a) Find distinct ages of students who took a course with name CS310.
(b) Find the names of students who took only 4-credits courses.
(c) Find the average grade over all students for those courses which enrolled at least 10 students

with age greater or equal than 25.
(d) Find the names of students who took every 4-credits course.
(e) Find for each course identifier (cid) the sid(s) of the student(s) who got the highest score.

2

Question 3

Using the schema above, and assuming that grade is of type integer, provide the SQL statement to
create a view TopStudents that lists the student ID, name and average grade (GPA) for students that
have GPA above 3.0.

Question 4

Design a database for a bank, including information about customers and their accounts. Information
about customers includes their name, address, phone and SSN. Accounts have numbers, types (e.g.,
savings/checking) and balances. Also record the customer(s) who own an account.

(a) Draw the E/R diagram for this database, assuming no constraints hold other than what results
from the schema.

(b) Modify the E/R diagram from (a) to reflect the constraint that each customer must have at least
one account.

(c) Modify the E/R diagram from (a) to reflect the constraint that an account must have only one
customer.

(d) Modify the diagram from (a) such that a customer can have a set of addresses (which are street-
city-state triples) and a set of phones. Recall that in the E/R model there can be only primitive
data types (no sets).

Question 5

Suppose you are given a relation R with four attributes ABCD and the following set of FDs: ABC,
BCD.

a. Identify the candidate key(s) for R

b. Determine if R is in BCNF, 3NF, or none of the above. If it is not in BCNF, decompose it into a set of
BCNF relations

Question 6

Show the grant diagrams after steps 4 and 5 of the sequence of actions below, where A owns the
relation on which the privilege p is assigned. Can C still exercise privilege p? What about E?

Step Executed by Action

1 A GRANT p TO B WITH GRANT OPTION

2 A GRANT p TO C

3 B GRANT p TO D WITH GRANT OPTION

4 D GRANT p TO E

5 B REVOKE p FROM D CASCADE