Given this aviation database, named CH2_AVIA, work the ...
Given this aviation database, named CH2_AVIA, work the following problems:
30. For each table, identify, where possible,
a. The primary key
Table CHARTER: CHAR_TRIP
Table AIRCRAFT: AC_NUM
Table MODEL: MOD_CODE
Table PILOT: EMP_NUM
Table EMPLOYEE: EMP_NUM
Table CUSTOMER: CUS_CODE
b. A superkey
Note: The super key and secondary key compositions were described in the note that accompanied Problem 27. The same arguments may be pursued with reference to parts b, c and e of this problem.
Table CHARTER: CHAR_TRIP + CHAR_DATE
Table AIRCRAFT: AC_NUM + MOD-CODE
Table MODEL: MOD_CODE + MOD_NAME
Table PILOT: EMP_NUM + PIL_LICENSE
Table EMPLOYEE: EMP_NUM + EMP_DOB
Table CUSTOMER: CUS_CODE + CUS_LNAME
c. a candidate key
Table CHARTER: No practical candidate key is available. For example,
CHAR_DATE + CHAR_DESTINATION + AC_NUMBER + CHAR_PILOT + CHAR_COPILOT
will still not necessarily yield unique matches, because it is possible to fly an aircraft to the same destination twice on one date with the same pilot and copilot. You could, of course, present the argument that the combination of all the attributes would yield a unique outcome.
Table AIRCRAFT: See the previous discussion
Table MODEL: See the previous discussion
Table PILOT: See the previous discussion
Table EMPLOYEE: See the previous discussion. But Perhaps the combination of
EMP_LNAME + EMP_FNAME + EMP_INITIAL + EMP_DOB
will yield an acceptable candidate key.
Table CUSTOMER: See the previous discussion
d. The foreign key(s)
Table CHARTER: CHAR_PILOT (references PILOT)
CHAR_COPILOT (references PILOT)
AC_NUMBER (references AIRCRAFT)
CUS_CODE (references CUSTOMER)
Table AIRCRAFT: MOD_CODE
Table MODEL: NONE
Table PILOT: EMP_NUM (references EMPLOYEE)
Table EMPLOYEE: NONE
Table CUSTOMER: NONE
e. a secondary key.
Table CHARTER: CHAR_DATE + AC_NUMBER + CHAR_DESTINATION
Table AIRCRAFT: MOD_CODE
Table MODEL: MOD_MANUFACTURER + MOD_NAME
Table PILOT: PIL_LICENSE + PIL_MED_DATE
Table EMPLOYEE: EMP_LNAME + EMP_FNAME + EMP_DOB
Table CUSTOMER: CUS_LNAME + CUS_FNAME + CUS_PHONE
31. Create the Entity Relationship diagram. Hint: Take a look at the table contents. You will discover that an AIRCRAFT can be used to fly many CHARTER trips, but that each CHARTER trip is flown by one AIRCRAFT. Similarly, you will discover that a MODEL references many AIRCRAFT, but each AIRCRAFT references a single MODEL, and so on.
Note: Database design properly precedes the creation of table structures and contents. However, we have found that our students generally find it much easier to understand design concepts and requirements after they have examined some data. By observing actual attribute values, the abstract notion of relationships becomes "real" and, therefore, more easily understood. So, throughout this chapter and in the chapter's question and problem sets, we have shown the table structures and contents first and then the E-R diagram is constructed to reflect the available information. Actually, this process is too often typical of real world projects in which "documentation after the fact" and "reverse engineering" are an unfortunate reality. Do stress that proper design always begins at the conceptual level and that table structures and contents are done at the implementation stage... after the design is judged to be correct. We will follow the appropriate procedures when we examine design issues in greater detail in Chapter 3.
After carefully exploring the CH2_AVIA database's contents, note the following relationships:
• a CUSTOMER can request many CHARTER trips, but each CHARTER trip is requested by one CUSTOMER.
• each CHARTER trip requires one AIRCRAFT, but an AIRCRAFT may be used to fly many different CHARTER trips.
• each AIRCRAFT is of a certain MODEL and, because the charter company may have many aircraft of a given model, each MODEL may reference many AIRCRAFT.
• a PILOT may pilot many CHARTER trips, but each CHARTER trip is piloted by one PILOT.
• a PILOT may serve as a copilot on a CHARTER trip.
• each PILOT is an EMPLOYEE (but not all EMPLOYEEs are PILOTs!).
The E-R diagram reflects this list of relationships:
The Chen ERD
The Crow’s Foot ERD
Remind the students that the orientation of the E-R diagram's components is immaterial. Show the students that the E-R diagram is just as valid if the entities are re-arranged. The key is the proper identification of all the relationships. However, emphasize these points:
• It is considered to be good form to avoid crossing relationship lines
• The dual relationships between CHARTER and PILOT are a typical indication of the existence of synonyms. Such synonyms are undesirable. (They will be removed in problem 33.)
• Relationships are always read from the 1 to the M side in a 1:M relationship. Therefore, AIRCRAFT flies CHARTER and MODEL references AIRCRAFT.
• If a 1:1 relationship exists, that relationship is read from the entity that contains the common attributes to the entity that contains the unique attributes. Therefore, the EMPLOYEE is a PILOT. (The EMPLOYEE entity contains the common attributes such as names and addresses, while the PILOT entity contains only those attributes – such as medical certificates and pilot licenses that are unique to pilots.)
32. Create the relational schema.
Problem 31's E-R diagram, when implemented, yields problem 32's Relational Schema.
33. Modify the ERD you created in problem 31 to eliminate the problems created by the use of synonyms. Hint: Modify the CHARTER table structure by eliminating the CHAR_PILOT and CHAR_COPILOT attributes, and then create a composite table named CREW to link the CHARTER and EMPLOYEE tables. (Some crewmembers, such as flight attendants, may not be pilots. That’s why the EMPLOYEE table enters into this relationship.)
The following ERD shows the results of the modification:
The Chen ERD
The Crow’s Foot ERD
As you review this solution, explain that the modification requires action at the implementation level. First, you would have to delete the original relationship between CHARTER and PILOT, then create the CREW table to contain the CHAR_TRIP and EMP_NUM attributes, then create the relationship between CHARTER and EMPLOYEE through CREW. The new database relationships are illustrated in the following relational schema. (Use the CH2-AVIA_CO_2 database on the Instructor’s CD.)
The CREW table contents are shown next.
Note that the table no longer contains nulls and that a separate entry is made for each crewmember. This structure makes it possible to add as many crewmembers as necessary to any charter flight.
Compare the contents of this CREW table to the CHAR_PILOT and CHAR_COPILOT entries in the original CHARTER table and note that no information has been lost as a consequence of the structural change.
-----------------------
[pic]
[pic]
[pic]
[pic]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- interagency aviation mishap response plan
- alamance burlington school system
- priority queues
- given this aviation database named ch2 avia work the
- international commission on zoological nomenclature
- introduction
- women veterans health v1 0 user manual
- e m e r g e n c y m a n a g e m e n t a n d t h e l a w
- department of veterans affairs pharmacy data management
- general zoology introduction 1
Related searches
- given the fact that synonym
- find the word given the letters
- find the polynomial given the zeros
- find the determinant of the given matrix
- aviation database websites
- ntsb aviation database aircraft accidents
- social work database software
- spirits named in the bible
- who named the planets
- describe the shape of the given histogram
- who named the planet saturn
- who named the planet earth