The Computer Science Department frequent fliers have been complaining to Hayward

Question

The Computer Science Department frequent fliers have been complaining to

Hayward County Airport officials about the poor organization at the airport. As a

result, the officials decided that all information related to the airport should be

organized using a DBMS, and you have been hired to design the database.

Your first task is to organize the information about all the airplanes stationed

and maintained at the airport. The relevant information is as follows:

Every airplane has a registration number, and each airplane is of a

specific model.

The airport accommodates a number of airplane models, and each

model is identified by a model number (e.g., DC-10) and has a capacity

and a weight.

A number of technicians work at the airport. You need to store the

name, SSN, address, phone number, and salary of each technician.

Each technician is an expert on one or more plane model(s), and

his or her expertise may overlap with that of other technicians. This

information about technicians must also be recorded.

Traffic controllers must have an annual medical examination. For each

traffic controller, you must store the date of the most recent exam.

All airport employees (including technicians) belong to a union. You must

store the union membership number of each employee. You can assume

that a social security number uniquely identifies each employee.

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 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 the airplane received on the test.

Please answer the following questions:

Draw an ER diagram for this database. Make sure to indicate primary   

keys, cardinality constraints, weak entities (if any) and participation

constraints. List any assumptions you make in the process.

Translate the ER diagram into relational database tables (i.e. give the

SQL DDL statements). Make sure that the translation captures key

constraints (primary keys and foreign keys if applicable) and participation

Explanation / Answer

http://postimg.org/image/ex8phc38v/

CREATE TABLE `Airplane` (
`registration_number` varchar(255),
`model_number` varchar(255),
PRIMARY KEY (`registration_number`),
CONSTRAINT `class_ibfk_11` FOREIGN KEY (`model_number`) REFERENCES `AirplaneModel` (`model_number`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `AirplaneModel` (
`model_number` varchar(255),
`capacity` varchar(255) DEFAULT NULL,
`weight` varchar(255) DEFAULT NULL,
PRIMARY KEY (`model_number`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `techcian` (
`SSN` varchar(255),
`name` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`phone_number` varchar(255) DEFAULT NULL,
`salary` varchar(255) DEFAULT NULL,
`UMN` varchar(255),
PRIMARY KEY (`SSN`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `technician_model` (
`SSN` varchar(255),
`model_number` varchar(255),
CONSTRAINT `class_ibfk_12` FOREIGN KEY (`SSN`) REFERENCES `techcian` (`SSN`),
CONSTRAINT `class_ibfk_13` FOREIGN KEY (`model_number`) REFERENCES `AirplaneModel` (`model_number`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `test_parts` (
`SSN` varchar(255),
`FAATestNo` varchar(255),
`hours` varchar(255) ,
`model_number` varchar(255),
`name` varchar(255) DEFAULT NULL,
`score` int(4),PRIMARY KEY (`FAATestNo`),
CONSTRAINT `class_ibfk_123` FOREIGN KEY (`SSN`) REFERENCES `techcian` (`SSN`),
CONSTRAINT `class_ibfk_133` FOREIGN KEY (`model_number`) REFERENCES `AirplaneModel` (`model_number`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

let me know if you need any clarification.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.