Comp 353/453: Database Programming,  homework 1
Due Friday, Feb 10, 2012    
Peter Dordal, Loyola University Chicago CS Dept

Elmasri & Navathe 4.7, 4.10, 4.12
 
[I originally wrote 4.8, but the correct problem is 4.7]

4.7. Consider the LIBRARY relational database schema [shown here]. Choose the appropriate action (reject, cascasde, set to NULL, set to default) for each referential integrity constraint, both for the deletion of a referenced tuple and for the update of a primary key attribute value in the referenced tuple. Justify your choices.

The DB consists of seven tables, with the following attributes. The primary key is underlined.

BOOK:  
    Book_id, Title, Publisher_name

BOOK_AUTHORS:
    Book_id, Author_name

PUBLISHER
    Name, Address, Phone

BOOK_COPIES
    Book_id, Branch_at, No_of_copies

BOOK_LOANS
    Book_id, Branch_id, Card_no, Date_out, Due_date

LIBRARY_BRANCH
    Branch_id, Branch_name, Address

BORROWER
    Card_no, Name, Address, Phone

The seven referential integrity constraints are the following. The book diagram shows an arrow from the lefthand table/field to the righthand table/field.

1.	BOOK.Publisher_name	⟶	PUBLISHER.Name
2.	BOOK_AUTHORS.Book_id	⟶	BOOK.Book_id
3.	BOOK_COPIES.Book_id	⟶	BOOK.Book_id
4.	BOOK_COPIES.Branch_id	⟶	LIBRARY_BRANCH.Branch_id
5.	BOOK_LOANS.Book_id	⟶	BOOK.Book_id
6.	BOOK_LOANS.Branch_id	⟶	LIBRARY_BRANCH.Branch_id
7.	BOOK_LOANS.Card_no	⟶	BORROWER.Card_no

4.10  Specify the following queries in SQL on the COMPANY relational database schema shown in Figure 3.5 [and in class]. Show the results of each query if it is applied to the COMPANY database in Figure 3.6 [same as the data I provided -- pld].

(a) Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.

(b). List the names of all employees who have a dependent with the same first name as themselves.

(c). Find the names of all employees who are directly supervised by Franklin Wong [fname='Franklin', lname='Wong'].


4.12 Specify the following queries in SQL on the database schema of Figure 1.2 [the UNIVERSITY database].

(a) retrieve the names of all senior students majoring in 'CS' (computer science).

(b) Retrieve the names of all courses taught by Professor King in 2007 and 2008.

(c) For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section.

To do 4.12(c), you must use the  COUNT() grouping function, not actually covered in Chapter 4.

(d) Retrieve the name and transcript of each senior student (Class = 4) majoring in CS. A transcript includes course name, course number, credit hours, semester, year and grade for each course completed by the student.