Back to Syllabus

Database FINAL EXAM sample DO 10 OUT OF 12

SQL QUESTIONS ARE BASED ON THE "50K CARS" MODEL

You may use the "50K CARS" E-R diagram

1. Write a SELECT command to get the number of bookings made for each customer (Custome-no).

2. Write a SELECT command to retrieve registration, model

and rate_per_mile for those cars whose daily rate is less

than $50.

3. Write a SELECT statement to find the car which has the

most expensive daily rate.

4. Select the bookings made in January 1987.

5. Write a grant all command so that a customer can view only her/his bookings records. Assume that customer_no is also the user name.

6. An education database contains information about inhouse

company education training scheme. For each training

course, the database contains details of all prerequisite

courses for that course and all offerings for that course;

and for each offering it contains details of all teachers

and all student enrollments for that offering. The

relevant entities are as follows:

COURSE, PREREQ, OFFERING, TEACHER, ENROLLMENT.

Note: A course may have one or more prerequisites and a

prerequisite may be for one or more courses.

Design an ER scheme for the above application.

7. Map the ER schema in problem 6 into a relational schema.

Indicate the primary key and the foreign key(s) in each

table. You need not show the other attributes in your

schema.

8. Write all the referential integrity constraints for

the schema in problem 7.

9 Consider the STUDENT table, showing the student's ID, majors (one or more) and student activities.

The primary key is the combination of attribute (ID, Major, Activity). Does this table violate any of the first four normal forms? I so, then normalize.

10. Explain why a user or a process with a higher security

level than the security level of database object can not

write to that object.

11. Explain how a recovery algorithm decides which

transaction to UNDO and which transactions to REDO.

12. In concurrency control what is the difference between an

exclusive-lock and a share-lock. Can a share-lock ever be

promoted to an exclusive-lock, explain.

 Back to Syllabus

_