BTM 382 Database Management - Assignment 1: Database Integrity
BTM 382
Database Management
Assignment 1
Use the database shown in Figure 1 to answer Problems 1-4.
1. For each table, identify the primary key and the foreign key(s). If a table does not have a
foreign key, write None.
2. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer.
3. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer.
Write NA (Not Applicable) if the table does not have a foreign key.
4. Identify all the relationships between the tables and describe their type.
BTM 382 Database Management
Use the database shown in Figure 2 to work Problems 5−7. Note that the database is composed of four tables that reflect these relationships:
-
An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEEs.
-
An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEEs.
5. For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None.
6. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer.
7. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.
BTM 382 Database Management
Use the database shown in Figure 3 to answer Problems 8-11.
8. Do the tables exhibit entity integrity? Answer yes or no, and then explain your answer.
9. Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer.
Write NA (Not Applicable) if the table does not have a foreign key.
10. Identify the TRUCK table’s candidate key(s).
11. For each table, identify a superkey and a secondary key.
Use the database shown in Figures 4 & 5 to answer Problems 12−15.
ROBCOR is an aircraft charter company that supplies on-demand charter flight services using a fleet of four aircraft. Aircrafts are identified by a unique registration number. Therefore, the aircraft registration number is an appropriate primary key for the AIRCRAFT table.
BTM 382 Database Management
BTM 382 Database Management
The nulls in the CHARTER table’s CHAR_COPILOT column indicate that a copilot is not required for some charter trips or for some aircraft. Federal Aviation Administration (FAA) rules require a copilot on jet aircraft and on aircraft having a gross take-off weight over 12,500 pounds. None of the aircraft in the AIRCRAFT table are governed by this requirement; however, some customers may require the presence of a copilot for insurance reasons. All charter trips are recorded in the CHARTER table.
12. For each table, identify the primary key and foreign key(s) when possible.
You want to see data on charters flown by either Robert Williams (employee number105) or Elizabeth Travis (employee number 109) as pilot or copilot, but not charters flown by both of them. Complete Problems 13–15 to find this information.
13. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by either employee 105 or employee 109 as pilot or copilot.
14. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by both employee 105 and employee 109.
15. Create the table that would result from applying a DIFFERENCE relational operator of your result from Problem 13 to your result from Problem 14.
