HOMEWORK $1$

Due: Sunday, $09/08/2024$

$1.(20$ pts$.)$ Using the ER notations discussed in class to do a conceptual design for a

$$research projects$$ database. This database records information about project

contracts. Each project has only one sponsor and a sponsor may fund several

projects. A professor may participate in multiple projects and each project can

involve several professors. When a professor is participating in a project, he$/$she is

given a budget for the work. Add some meaningful attributes to make your design

complete.

$2.(80$ pts$)$ Using ER notations discussed in class to design a database for an airport.

The relevant information is as follows:

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

model.

$$ Each airplane model is identified by a model number and has a capacity and

weight.

$$ A number of technicians work at the airport. You need to store their name,

SSN$,$ address, and salary.

$$ Each technician is an expert on one or more plan model$($s$),$ and his or her

expertise may overlap with that of other technicians.

$$ 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 technician$)$ belong to a union. You must store

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

employee is uniquely identified by a social security number.

$$ 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.

Be sure to indicate the various attributes of each entity and relationship set; also

specify the key and participation constraints for each relationship set. Specify any

necessary overlap and covering constraints as well.HOMEWORK $1$

Due: Sunday, $09/08/2024$

$1.(20$ pts$.)$ Using the ER notations discussed in class to do a conceptual design for a

$$research projects$$ database. This database records information about project

contracts. Each project has only one sponsor and a sponsor may fund several

projects. A professor may participate in multiple projects and each project can

involve several professors. When a professor is participating in a project, he$/$she is

given a budget for the work. Add some meaningful attributes to make your design

complete.

$2.(80$ pts$)$ Using ER notations discussed in class to design a database for an airport.

The relevant information is as follows:

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

model.

$$ Each airplane model is identified by a model number and has a capacity and

weight.

$$ A number of technicians work at the airport. You need to store their name,

SSN$,$ address, and salary.

$$ Each technician is an expert on one or more plan model$($s$),$ and his or her

expertise may overlap with that of other technicians.

$$ 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 technician$)$ belong to a union. You must store

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

employee is uniquely identified by a social security number.

$$ 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.

Be sure to indicate the various attributes of each entity and relationship set; also

specify the key and participation constraints for each relationship set. Specify any

necessary overlap and covering constraints as well.