1. The CS department needs to store information about instructors (identified by ssn, with salary and officenumber as attributes), courses (identified by cno, with cname and time as attributes), and TAs of instructors (with name and age as attributes). Instructors perform lectures of courses; each course is associated with one and only one instructor; a TA is associated with one and only one instructor. We are not interested in what courses the TA was associated with once the instructor no longer is teaching courses. Draw an ER diagram that captures this information. 30 pts

2. CS Department frequent fliers have been complaining to Dane 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, maintained, and controlled at the airport. The relevant information is as follows:

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

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

c. A number of technicians work for the airport. You need to store the name, SSN, address, phone number, and salary of each technician.

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

e. A number of traffic controllers work for the airport. Traffic controllers must have an annual medical examination. For each traffic controller, you must store the date of the most recent exam. Each controller is responsible to control some set of planes. Each plane is assigned to exact one controller.

f. 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 identified by a social security number.

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

h. 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. 30 pts

2. The FAA passes a regulation that tests on a plane must be conducted by a technician who is an expert on that model. How would you express this constraint in the ER diagram? If you cannot express it, explain briefly. 10 pts

3. You set up a database company that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works by Picasso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that persons unique name, address, total amount of dollars spent in the gallery (very important!), and the artists that the customer tends to like. Draw the ER diagram for the database. 30 pts