COS 425, Fall 2006

Solutions to Problem Set 1

 

(1)   There are many correct answers to these problems. The following pictures show one of them.

(a)

(b)

 

 

(The above two pictures are from Instructor’ Supplementary Material for “Database Management Systems,” 3rd Edition by Raghu Ramakrishnan and Johannes Gehrke.)

 

(2)


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.

 

(3)  Please see separate document .


(4)

Neither overlapping, nor covering: Members of the Computer Science department include students, faculty, and various kinds of staff.  Suppose we want to store attribute “Tenured-or-not” for faculty, and we want secretaries to participate in a “Secretary of” relationship with faculty. In this case, we may specialize the entity CSmembers into two subclasses: Faculty and Secretaries. In this case, the subclasses are neither overlapping, nor covering.

 

Overlapping and covering: Some students of the Computer Science department are taking classes, some are doing research.  There are students just taking classes, there are students just doing research (advanced graduate students), and there are students doing both.  We don't expect any student to be doing neither.  Therefore we may specialize the entity CSstudents into two overlapping and covering subclasses: Coursework_Students and Research_Students. 

 


 

(5)

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

)

 

You may use other data types of SQL as you desire as long as appropriate. 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.


(6) Please see separate document .