数据库代考: CS430/630 – Final Exam Practice

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:

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

   any course.

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

   course.

4. (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:

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

   with age greater or equal than 25.

4. (d)  Find the names of students who took every 4-credits course.
5. (e)  Find for each course identifier (cid) the sid(s) of the student(s) who got the highest score.

1

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.

1. (a)  Draw the E/R diagram for this database, assuming no constraints hold other than what results from the schema.
2. (b)  Modify the E/R diagram from (a) to reflect the constraint that each customer must have at least one account.
3. (c)  Modify the E/R diagram from (a) to reflect the constraint that an account must have only one customer.
4. (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?

2