EXTRA CREDITS

7.19. Consider the ER diagram in Figure 7.20, which shows a simplified schema for an airline reservations system. Extract from the ER diagram the requirements and constraints that produced this schema. Try to be as precise as possible in your requirements and constraints specification.

Entity Types:

Airport

AirportCode.

1. Name, City, and State for each airport.

Airplane Type

1. Each airplane type is identified by TypeName.

2. We need to keep track of the Max-seats for each airplane type.

Airplane

1. Each airplane is identified by AirplaneId.

2. We need to keep track of the Total-no-of-seats for each airplane.

Flight Leg

1. We need to track of the LegNo for each flight leg.

Flight

1. Each flight is identified by Number.

2. We need to keep track of the Weekdays and Airline for each flight.

Leg Instance

1. We need to keep track of the Date and No-of-avail-seats for each leg instance.

2. Each leg instance must be assigned to at least one airplane.

3. Each leg instance must be an instance of a flight leg.

Relationships, attributes, and constraints:

Relationship between FLIGHT and FARES

a. Each instance of FLIGHT may offer multiple instances of FARE.

b. Instances of FARE must be sold for a specific instance of FLIGHT.

Relationship between FLIGHT and FLIGHT LEG

c. Each instance of FLIGHT may consist of multiple instances of FLIGHT LEG.

d. Instances of FLIGHT LEG must belong to a specific instance of FLIGHT

Relationship between FLIGHT LEG and AIRPORT (ScheduledDepTime)

e. Each instance of AIRPORT may be scheduled for the departure of multiple instances of FLIGHT LEG.

f. Instances of FLIGHT LEG must be scheduled for departure from a specific instance of AIRPORT.

Relationship between FLIGHT LEG and AIRPORT (ScheduledArrTime)

a. Each instance of AIRPORT may be scheduled for the arrival of multiple instances of FLIGHT LEG.

b. Instances of FLIGHT LEG must be scheduled for arrival at a specific instance of AIRPORT.

Relationship between FLIGHT LEG and LEG INSTANCE

g. Each instance of FLIGHT LEG may consist of instances of LEG INSTANCE.

h. Instances of LEG INSTANCE must be instances of FLIGHT LEG.

Relationship between LEG INSTANCE and AIRPORT (ArrTime)

a. Each instance of AIRPORT may accommodate the arrival of multiple instances of LEG INSTANCE.

b. Instances of LEG INSTANCE must arrive at a specific instance of AIRPORT.

Relationship between LEG INSTANCE and AIRPORT (DepTime)

c. Each instance of AIRPORT may accommodate the departure of multiple instances of LEG INSTANCE.

d. Instances of LEG INSTANCE must depart from a specific instance of AIRPORT.

Relationship between LEG INSTANCE and SEAT (CustomerName, CPhone)

e. Each instance of LEG INSTANCE may have multiple instances of SEAT for reservation.

f. Instances of SEAT must be reserved for a specific instance of LEG INSTANCE.

Relationship between AIRPLANE and LEG INSTANCE

g. Each instance of AIRPLANE may be assigned to multiple instances of LEG INSTANCE.

h. Instances of LEG INSTANCE must have a specific instance of AIRPLANE assigned to them.

Relationship between AIRPLANE TYPE and AIRPLANE

i. Each instance of AIRPLANE TYPE may consist of multiple instances of AIRPLANE.

j. Instances of AIRPLANE must belong to a specific instance of AIRPLANE TYPE.

Relationship between AIRPORT and AIRPLANE TYPE

k. Multiple instances of AIRPORT may be available for multiple instances of AIRPLANE TYPE for landing.

l. Multiple instances of AIRPLANE TYPE may land at multiple instances of AIRPORT.

26.34. Consider the COMPANY database described in Figure 3.6. Using the syntax of Oracle triggers, write active rules to do the following:

a. Whenever an employee’s project assignments are changed, check if the total hours per week spent on the employee’s projects are less than 30 or greater than 40; if so, notify the employee’s direct supervisor.

b. Whenever an employee is deleted, delete the PROJECT tuples and

DEPENDENT tuples related to that employee, and if the employee manages a department or supervises employees, set the Mgr_ssn for that department to NULL and set the Super_ssn for those employees to NULL.

Answers:

This procedure looks for an employee whose SSN matches the procedure’s AGRSSN

argument and it notifies the supervisor of that employee.

CREATE TRIGGER INFORM_SUPERVISOR_ABOUT_HOURS
AFTER UPDATE OF HOURS ON WORKS_ON
FOR EACH ROW
WHEN ((SELECT SUM(HOURS)
FROM WORKS_ON
WHERE ESSN = NEW.ESSN) < 30
OR
(SELECT SUM(HOURS)
FROM WORKS_ON
WHERE ESSN = NEW.ESSN) > 40)
TELL_SUPERVISOR (NEW.ESSN);
(b) CREATE TRIGGER DELETE_IN_CASCADE
AFTER DELETE ON EMPLOYEE
FOR EACH ROW
BEGIN
DELETE FROM WORKS_ON
WHERE ESSN = OLD.SSN;
DELETE FROM DEPENDENT
WHERE ESSN = OLD.SSN;
UPDATE EMPLOYE
SET SUPERSSN = ‘null’
WHERE SUPERSSN = OLD.SSN;
END;

15.28. Consider the relation R, which has attributes that hold schedules of courses and sections at a university; 
R = {Course_no, Sec_no, Offering_dept,


Credit_hours, Course_level, Instructor_ssn, Semester, Year, Days_hours, Room_no,


No_of_students}. 


Suppose that the following functional dependencies hold on R:


{Course_no} → {Offering_dept, Credit_hours, Course_level}


{Course_no, Sec_no, Semester, Year} → {Days_hours, Room_no,


No_of_students, Instructor_ssn}


{Room_no, Days_hours, Semester, Year} → {Instructor_ssn, Course_no,



Sec_no}


Try to determine which sets of attributes form keys of R. How would you normalize this relation?


Answer

C = CourseNo, SN = SecNo, OD = OfferingDept, CH = CreditHours, CL = CourseLevel,
I = InstructorSSN, S = Semester, Y = Year, D = Days_Hours, RM = RoomNo,
NS = NoOfStudents
Hence, R = {C, SN, OD, CH, CL, I, S, Y, D, RM, NS}, and the following functional dependencies hold:
{C} -> {OD, CH, CL}
{C, SN, S, Y} -> {D, RM, NS, I}
{RM, D, S, Y} -> {I, C, SN}

First, we can calculate the closures for each left hand side of a functional dependency, since these sets of attributes are the candidates to be keys:
(1) {C}+ = {C, OD, CH, CL}
(2) Since {C, SN, S, Y} -> {D, RM, NS, I}, and {C}+ = {C, OD, CH, CL}, we get:
{C, SN, S, Y}+ = {C, SN, S, Y, D, RM, NS, I, OD, CH, CL} = R
(3) Since {RM, D, S, Y} -> {I, C, SN}, we know that {RM, D, S, Y}+ contains {RM, D, S,Y, I, C, SN}. But {C}+ contains {OD, CH, CL} so these are also contained in {RM, D, S,Y}+ since C is already there. Finally, since {C, SN, S, Y} are now all in {RM, D, S, Y} + and {C, SN, S, Y} + contains {NS} (from (2) above), we get:
{RM, D, S, Y}+ = {RM, D, S, Y, I, C, SN, OD, CH, CL, NS} = R

Hence, both K1 = {C, SN, S, Y} and K2 = {RM, D, S, Y} are (candidate) keys of R. By applying the general definition of 2NF, we find that the functional dependency {C} ->{OD, CH, CL} is a partial dependency for K1 (since C is included in K1). Hence, R is normalized into R1 and R2 as follows:
R1 = {C, OD, CH, CL}
R2 = {RM, D, S, Y, I, C, SN, NS} with candidate keys K1 and K2

Since neither R1 nor R2 have transitive dependencies on either of the candidate keys, R1 and R2 are in 3NF also. They also both satisfy the definition of BCNF.