Problems (60 points):

1. Transform each of the following ER or EER diagrams to a relational schema that shows referential integrity constraints and functional dependencies [see the Mapping Example (Problem 4-33) for an example].

a. (5 points) Figure 2-9b (Flight), p.72

b. (5 points) Figure 2-11c [not part (a) or (b), which are more complicated], p.77

c. (5 points) Figure 3-6b (Vehicle), p.123

d. (5 points) Figure 3-7a (Patient), p.124

2. (8 points) A SCHEDULE table contains the following attributes: CourseID, SectionID, CourseTitle, ScheduledDayTime, PrerequisiteCourseID, and PrerequisiteCourseName. The primary key of this table includes CourseID and SectionID, which together determine all the other attributes. The additional functional dependencies are:

CourseID determines CourseTitle PrerequisiteCourseID determines PrerequisiteCourseName

Decompose this non-3NF table into 3NF relations. Show all functional dependencies and links on the decomposed relations.

Do Problems 4 and 5 using Microsoft Access.

4. (14 points) Problem 6-34, p.283,

This problem requires you to create a database with six tables based on Figure 6-11 on p.283 (see the paragraph before this problem). The 6 tables are linked as: FACULTY-QAULIFIED-COURSE-SECTION-REGISTRATION-STUDENT

Note that CourseID in the SECTION table should be a foreign key and thus dash-underlined in the table.

Turn in the SQL commands and the Relationship chart (press Alt + Print Screen key combination to copy-paste the chart into your Word file).

5. (8 points) From the PVF database find all orders placed by customers located in NJ and all orders that requested Product #8. Write a SQL UNION statement such that only one column, with heading OrderID, appears in the results. Turn in both the SQL commands and the Microsoft Access output results.

Problem 4-33, p.195, 196 (Figure 4-33) Customer ID Cust Address CARD ACCOUNT Account ID 1, 1110 Merch Addr Card Type Card Type- DEBIT CARD Bank No CREDIT CARD Cur Bal CHARGES Charge Time Amount Problem 4-33, p.195, 196 (Figure 4-33) Customer ID Cust Address CARD ACCOUNT Account ID 1, 1110 Merch Addr Card Type Card Type- DEBIT CARD Bank No CREDIT CARD Cur Bal CHARGES Charge Time Amount