CSc4710/6710 Assignment 1

Description

Problem 1 (15 points)

For the following database,

1. list all the relationships among the records.

2. give some additional views that may be needed by other user groups.

3. give some examples of integrity constraints that you think should hold.

Problem 2 (20 points)

Which of the following plays an important role in representing information about the real world in a database? Explain briefly.

1. The data definition language.

2. The data manipulation language.

3. The buffer manager.

4. The data model.

Problem 3 (15 points)

What are the differences for external schemas, internal schemas, and conceptual schemas? How are these different schemas related to logical independence and physical independence?

Problem 4 (30 points)

Consider the following set of requirements for an airport database:

1. An airplane has a number and a specific model.

2. There are a number of airplane models, and each model is identified by a model number and has a capacity and a weight.

3. There are many technicians working at the airport. We keep track of each technician’s name, SSN, address, phone number, and salary. Each technician is an expert on one or more plane model(s), and his or her expertise may overlap with that of other technicians.

4. There are some traffic controllers working at the airport. Each traffic controller must take annual medical examinations and the date of the most recent exam is stored.

5. All airport employees (including technicians) belong to a union. We store the union membership number of each employee.

6. The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy. Each test has a Federal Aviation Administration (FAA) test number, a name, and a maximum possible score. The FAA requires the airport to keep track of each time that a given airplane is tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score that the airplane received on the test.

Design an ER diagram for the above. Specify key attributes of each entity type and structural constraints on each relationship type. Specify any additional assumptions.

Problem 5 (20 points)

Construct a relational model for the database described in Problem 4. For each relation, specify its primary key and foreign keys.