# 程序代写代做代考 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: AB→C,

BC→D.

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