SOLUTIONS TO MIDTERM EXAM QUESTIONS

QUESTION 1.a

CREATE TABLE Flights
(
    flight# INT NOT NULL,
    from varchar(100) NOT NULL,
    to varchar(100) NOT NULL,
    distance INT NOT NULL,
    departureTime datetime NOT NULL,
    arrivalTime datetime NOT NULL,
    primary key (flight#)
)

CREATE TABLE Aircraft
(
    aircraft# INT NOT NULL,
    aircraftName varchar(100) NOT NULL,
    flyingCapability INT,
    primary key(aircraft#)
)

CREATE TABLE CertifiedBy
(
    employeeId INT NOT NULL references Employees(employeeId),
    aircraft# INT NOT NULL references Aircraft(aircraft#),
    primary key(employeeId, aircraft#)
)

CREATE TABLE Employees
(
    employeeId INT NOT NULL,
    employeeName VARCHAR(100),
    salary float,
    primary key(employeeId)
)

QUESTION 1.b.

TWO CORRECT SOLUTIONS: (a) Just say that the query cannot be expressed without using cross product; (b) Say that, and use it anyway.

Given that this could have confused some students, we will not mark wrong answers. Good answers, however, will get bonus marks.
 
S: selection
P: projection

Pdistance = Pdistance S((from="Toronto" ^ to="London") v (from="London" ^ to="Toronto") Flights))

Answer = P aircraftName S flyingCapability>distance (Aircraft x Pdistance)




QUESTION 2. See figure.

QUESTION 3.a.

For example, in the CREATE TABLE statement,..

CREATE TABLE R7(
                D1 int Primary Key,
                ....
                FOREIGN KEY D1 References R2(B1) ON DELETE CASCADE)

Same for R5.


QUESTION 3.b.

You specify alternate (v.g. candidate) key with the keyword UNIQUE. However, you cannot declare a foreign  key over an attribute that is not a primary key. In this case, you must create a trigger or a stored procedure for verifying referential integrity.
 

CREATE TABLE R2(
               B1 int Primary Key,
               B2 int UNIQUE
               .....)

QUESTION 3.c.

On insertion of "a" in A, "a" must be also inserted either in B or C (or in both), because of the "cover" constraint.
On deletion of "b" in B, nothing must be done, because deleting a child does not mean that you must delete its parent. Although, if "b" is NOT in C, deleting "b" from B without deleting it from A will violate the cover condition. Thus, in the latter case, "b" must also be deleted from A.