1.A company database needs to store information about employees (iden-ti?ed by ssn, with salary and phone as attributes), departments (identi?ed by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identi?ed uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.
Draw an ER diagram that captures this information.
2.Computer Sciences Department frequent ?iers have been complaining to Dane County Airport o?cials about the poor organization at the airport. As a result, the o?cials decided that all information related to the airport should be organized using a DBMS, and you have been hired to design the database. Your ?rst 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 speci?c model.
The airport accommodates a number of airplane models, and each model is iden-ti?ed 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 exper-tise may overlap with that of other technicians. This information about technicians must also be recorded.
Tra?c controllers must have an annual medical examination. For each tra?c 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 each employee is uniquely identi?ed by a social security number.
The airport has a number of tests that are used periodically to ensure that air-planes 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.
1. Draw an ER diagram for the airport database. 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 (in English).
2. The FAA passes a regulation that tests on a plane must be conducted by a tech-nician who is an expert on that model. How would you express this constraint in the ER diagram? If you cannot express it, explain brie?y.