COS 597A, Fall 2011

Solutions to Problem Set 1

Problem 1

Part 1.

The only "IS A" surely is not covering -- there must be more employees than technicians and traffic controllers.  It is probably also not overlapping, meaning the same person would not be hired as a technician and a traffic controller.

Part 2.

It’s hard to express that "tests on a plane must be conducted by a technician who is an expert on that model" because in the ER model, the relation “Expert_of” is between Models and Technicians, the relation “Model_of” is between Planes and Models. To enforce the new constraint that a technician who is an expert on that model must conduct tests on a plane, involves two relations, which is impossible in ER model.   We could try to aggregate and relate (model, technician) pairs to Airplanes, but then we would not be able to express that an airplane is exactly one model.  Instead, we could aggregate Airplanes and Models through "Model_of" and relate these (airplane, model) pairs to Technicians through "Expert_of".  We could aggregate again and relate (technician, (airplane, model)) "Expert-of" tuples to test Events through "Tested_by".  Then each event would relate to a technician and airplane and model where the airplane was of that model and the technician was an expert of the model.  Event should have a participation constraint and key constraint to "Tested_by".  Note that all this aggregation adds a lot of redundancy, like relating each technician to every plane of a model of which the technician is an expert.

Problem 3.   Using SQL definitions:

CREATE TABLE Models (

m_num INTEGER,

capacity INTEGER,

weight REAL,

PRIMARY KEY(m_num)

)

CREATE TABLE Airplanes (

reg_num INTEGER,

m_num INTEGER NOT NULL,

PRIMARY KEY(reg_num),

FOREIGN KEY (m_num) REFERENCES Models

)

CREATE TABLE Employees (

ssn CHAR(9),

union INTEGER NOT NULL,

PRIMARY KEY(ssn)

)

CREATE TABLE Technicians (

ssn CHAR(9),

name CHAR(20),

address CHAR(100),

phone CHAR(20),

salary REAL,

PRIMARY KEY(ssn),

FOREIGN KEY(ssn) REFERENCES Employees

(* you may add ON DELETE CASCADE *)

)

CREATE TABLE Traffic_Controllers (

ssn CHAR(9),

date_of_exam DATE,

PRIMARY KEY(ssn),

FOREIGN KEY(ssn) REFERENCES Employees

(* you may add ON DELETE CASCADE *)

)

CREATE TABLE Expert_of (

m_num INTEGER,

ssn CHAR(9),

PRIMARY KEY(m_num, ssn),

FOREIGN KEY(m_num) REFERENCES Models,

FOREIGN KEY(ssn) REFERENCES Technicians

)

CREATE TABLE Tests (

test_num INTEGER,

name CHAR(20),

max_score INTEGER,

PRIMARY KEY(test_num)

)

CREATE TABLE Tested_by (

event_id INTEGER,

test_num INTEGER NOT NULL,

reg_num INTEGER NOT NULL,

ssn CHAR(9) NOT NULL,

date DATE NOT NULL,

hours_taken INTEGER NOT NULL,

score INTEGER NOT NULL,

PRIMARY KEY(event_id),

FOREIGN KEY(test_num) REFERENCES Tests,

FOREIGN KEY(reg_num) REFERENCES Airplanes,

FOREIGN KEY(ssn) REFERENCES Technicians

)

Reasonable data types have been chosen;  others are possible. For example, Salary may be better defined as Number(10,2) because only two decimal are needed.

Not converting Model_of into a relation and putting m_num as a non-null foreign key of Airplanes enforces the key constraint and the full participation constraint of airplane-is-a-model-of relation. Similary, the full participation and key constraint of Event  in Tested_by is represented by representing entity Event only as the primary key of  Tested_by and using NOT NULL constraints on all fields (implicitly in the primary key field) of Tested_by.

The full participation constraint that a technician must be one or more models’ expert can’t be expressed.