Question 2 Flashcards
Consider an update for the AIRLINE database to enter a reservation on a particular flight or flight leg on a given date:
Give the operations for this update
One possible set of operations for the following update is the following:
INSERT into SEAT_RESERVATION; MODIFY the LEG_INSTANCE tuple with the condition:
( FLIGHT_NUMBER=FNO AND LEG_NUMBER=LNO AND DATE=DT) by setting
NUMBER_OF_AVAILABLE_SEATS = NUMBER_OF_AVAILABLE_SEATS - 1;
These operations should be repeated for each LEG of the flight on which a reservation is made. This assumes that the reservation has only one seat. More complex operations will be needed for a more realistic reservation that may reserve several seats at once.
Consider an update for the AIRLINE database to enter a reservation on a particular flight or flight leg on a given date:
What types of constraints would you expect to check?
We would check that NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of the flight is greater than 1 before doing any reservation (unless overbooking is permitted), and that the SEAT_NUMBER being reserved in SEAT_RESERVATION is available
Consider an update for the AIRLINE database to enter a reservation on a particular flight or flight leg on a given date:
Which of these constraints are key, entity integrity, and referential integrity constraints and which are not?
The INSERT operation into SEAT_RESERVATION will check all the key, entity integrity, and referential integrity constraints for the relation. The check that NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of the flight is greater than 1 does not fall into any of the above types of constraints (it is a general semantic integrity constraint)
Consider an update for the AIRLINE database to enter a reservation on a particular flight or flight leg on a given date:
Specify all the referential integrity constraints on Figure 3.8
We will write a referential integrity constraint as R.A –> S (or R.(X) –> T)
whenever attribute A (or the set of attributes X) of relation R form a foreign key that
references the primary key of relation S (or T). FLIGHT_LEG.FLIGHT_NUMBER –> FLIGHT
FLIGHT_LEG.DEPARTURE_AIRPORT_CODE –> AIRPORT FLIGHT_LEG.ARRIVAL_AIRPORT_CODE –> AIRPORT
LEG_INSTANCE.(FLIGHT_NUMBER,LEG_NUMBER) –> FLIGHT_LEG
LEG_INSTANCE.DEPARTURE_AIRPORT_CODE –> AIRPORT
LEG_INSTANCE.ARRIVAL_AIRPORT_CODE –> AIRPORT
LEG_INSTANCE.AIRPLANE_ID –> AIRPLANE
FARES.FLIGHT_NUMBER –> FLIGHT
CAN_LAND.AIRPLANE_TYPE_NAME –> AIRPLANE_TYPE
CAN_LAND.AIRPORT_CODE –> AIRPORT
AIRPLANE.AIRPLANE_TYPE –> AIRPLANE_TYPE
SEAT_RESERVATION.(FLIGHT_NUMBER,LEG_NUMBER,DATE) –> LEG_INSTANCE
Consider the relation CLASS(Course#, Univ_Section#, InstructorName, Semester, BuildingCode, Room#, TimePeriod, Weekdays, CreditHours). This represents classes taught in a university with unique Univ_Section#. Give what you think should be various candidate keys and write in your own words under what constraints each candidate key would be valid
Possible candidate keys include the following (Note: We assume that the values of the Semester attribute include the year; for example “Spring/94” or “Fall/93” could be
values for Semester):
1. {Semester, BuildingCode, Room#, TimePeriod, Weekdays} if the same room cannot be used at the same time by more than one course during a particular semester.
2. {Univ_Section#} if it is unique across all semesters.
3. {InstructorName, Semester} if an instructor can teach at most one course during each semester.
4. If Univ_Section# is not unique, which is the case in many universities, we have to examine the rules that the university uses for section numbering. For example, if the
sections of a particular course during a particular semester are numbered 1, 2, 3, …, then a candidate key would be {Course#, Univ_Section#, Semester}. If, on the other hand, all sections (of any course) have unique numbers during a particular semester only, then the candidate key would be {Univ_Section#, Semester}
Database design often involves decisions about the storage of attributes. For example a Social Security Number can be stored as a one attribute or split into three attributes (one for each of the three hyphen-deliniated groups of numbers in a Social Security Number—XXX-XX-XXXX). However, Social Security Number is usually stored in one attribute. The decision is usually based on how the database will be used. This exercise asks you to think about specific situations where dividing the SSN is useful
a. We need the area code (also know as city code in some countries) and perhaps the country code (for dialing international phone numbers).
b. I would recommend storing the numbers in a separate attribute as they have their own independent existence. For example, if an area code region were split into two regions, it would change the area code associated with certain numbers, and having area code in a separate attribute will make it is easier to update the area code attribute by itself.
c. I would recommend splitting first name, middle name, and last name into different attributes as it is likely that the names may be sorted and/or retrieved by the last name, etc.
d. In general, if the each attribute has an independent logical existence based on the application, it would make sense to store it in a separate column otherwise there is no clear advantage. For example, SSN need not be split into its component unless we are using the subsequences to make deductions about validity, geography, etc. In the two cases above, it made logical and business sense to split the attributes.
