CPCC DB Phase 2

1. Translate the conceptual schema for Continental Palms Community College (CPCC) into a logical schema. Make sure all tables are in 3NF. Specify all the integrity constraints.

2. Write the following queries in relational algebra and tuple relational calculus.

• Get the name, location (campus, building), and phone number of every department.
• Get the name, building, and phone number of every department at the East campus.
• Get the list of programs (name and graduation requirements) available at the East campus.
• Get the list of students (name, and email) majoring in Information Technology Management at the East campus.

3. CPCC also hired another consulting group to design the database. In their design, there is the following big table:

Program (deptCode, deptName, campus, building, phone, fax, programName, minCredits, minGPA)

From the requirements, we have identified the following functional dependencies:

deptCode -> deptName, campus, building, phone, fax
deptName -> deptCode, campus, building, phone, fax
programName -> deptCode, minCredits, minGPA

Now you need to help them do the following:

• Give examples of data redundancies and data anomalies in their big table.
• Determine the primary key of their big table.
• Determine what normal form their big table is in.
• Rescue their design by normalizing their big table into multiple 3NF tables. Rename some of the attributes when appropriate. Indicate the primary key of each table. Specify all the foreign keys.