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.