Note: Answer the queries as completely as possible.  Partial credit will be given.
You must answer the questions in the order given – i.e, A thru H.

Q1. RELATIONAL LANGUAGES

All queries below refer to the following database:

    STUDENT (Stud#, Bdate, Major, S_name, S_phone)
    COURSE (Course#, C_name, Dept)
    OFFERING (Section#, Course#, Semester, Instructor_name)
    INSTRUCTOR (InstructorName, I_phone, I_rank)
    TEXT (Section#, BookISBN)
    BOOK (BookISBN, AuthorName, BookTitle, Publisher, Year, Price)
    ENROLL (Student#, Section#)

Note: BookISBN  is an identifier for a book.
Section# is like the CRN on OSCAR – it is unique for each offered section.

A. RELATIONAL ALGEBRA (21 points)

A1 For courses offered in semester = ‘Spring 2000’ produce a listing:
 (course#, no._of_sections_offered)
 (Hint: this requires the function notation proposed in book.)

A2. Consider sections offered in semester = ‘Fall 99’ and books published by Publisher = ‘Addison Wesley’.  Make a listing: (Section#, Course#, BookISBN) where the section is from Fall 99, Book is from Addison Wesley, and BookISBN has a null value if no Addison Wesley book is used by that section.( Be careful with how you set up the operations of algebra.).

A3. List BookISBN for books that are used by both departments: Dept = ‘EE’ and Dept = ‘CS’.  (No restriction on semesters – maybe used in any semester).
 

B. SQL (22 points)

B1. Produce a listing: (Section#, InstructorName, BookISBN) for those sections in which an instructor uses his/her own book.

B2. Create a view called “CSBOOKS” for the CS department for the “Spring 2000” semester containing: (Section#, Course#, InstructorName, BookISBN). Note: This view includes only courses that use textbooks.
- Is this view  updatable? Why?

B3. Produce a listing: (Stud#, no._of_sections) for students with Major = ‘ISYE’ who are enrolled for at least 5 sections in the Spring 2000 semester.
 

C. QBE (13 points)

C1. List (InstructorName, I_phone) for instructors with rank = ‘Asst Prof’ who are teaching in Spring 2000 a course belonging to the ‘CS’ department.

C2. Produce a result table: (Section#, BookTitle) for all books of Publisher = ‘Addison Wesley’ that have been adopted in sections in the entire database.
 

D. TUPLE CALCULUS (8 points)

D1. Write in tuple calculus: List coursename and department for all courses that have at least one section offered in Spring 2000 semester.  Be precise in your notation.

D2. What type of quantification did you use above?  What are the free variables?
 

E. DOMAIN CALCULUS (6 points)

E1. Write in English an interpretation of the following query:

Result = { d e q  |  E (a b c p) STUDENT (a b c d e) and ENROLL (p q) and a = p}

E2. Do the above in QBE.
 

NORMALIZATION (PARTS F, G, H)

F. Consider the following relation: (5 points)

Employee (EmpID, Bdate, Dependent_name, Car_lic_tag#)

Assuming that Dependent-name and Car_lic_tag# are both multivalued, this relation is not in 1NF.  Convert it into a set of 1NF relations.  Show the keys in resulting relations clearly.
 

G. Consider a bank database: (7 points)

(Acct#, Cust_name, Acct_type, Cust_phone#, Monthly_fee)

Assume that accounts can be held jointly by multiple customers.

Dependencies:  Acct# --> Acct_type, Monthly_fee
                        Cust_name --> Cust_phone#

G1. What is the key of this relation from the given interpretation?

G2. Does it meet the 3NF general test?  Explain clearly why or why not.

G3. Using the  key in G1, normalize successively and show its decomposed normalized result.
 

H. Consider the relation: (8 points)

FLIGHT_RESERVATION (Flight#, Date, Cust_name, Plane_type, Seat#, Plane_capacity)

Assume that each reservation is for one customer and assigns a unique Seat# to the customer.

     Flight# --> Plane_type
     Plane_type --> Plane_capacity

- Normalize this relation successively into 2NF and 3NF.  Clearly show intermediate relations at each level of normalization.
- Is the result in BCNF?