Database Systems assignment 1 _SOLUTION

Problem 1. [40pts] Consider a lab animal database, where we want to keep track of their genealogy. For every animal, we need to record, its unique id (a-id), its birthdate, its gender, body height and body weight. For male animals, the users also want to know the testosterone level, while for female ones they want to know the percent of body-fat. Moreover, our database should be able to store or deduce the biological mother, father and children of each animal. Finally, there are lab tests (with unique names, like ’red-blood-cellcount’, ’cholesterole-level’), which may be administered zero or more times on each animal. For each administered test, our users need to know the name of the test, the date, the animal and the (always numerical) result of the test. Complete the following tasks: 1. (20 pts) create the E-R diagram, indicate keys and weak entities (if any). 2. (20 pts) turn the E-R diagram into tables (relational model schema), and then write the SQL statement to create these relations (with primary key and foreign key constraint). Problem 2. [40pts] Suppose that you need to design a database for an airport. The relevant information that must be stored is: • Every airplane has a registration number, and each airplane is of 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 experience may overlap with that of other technicians. This information 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 each employee is uniquely identified by a SSN. • 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 giving 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. (20 pts) Give an E/R diagram for this database. Very briefly explain the intuitive meaning of any entity and relationship sets. Make sure to list the keys and weak entities (if any). 2. (20 pts) Turn the E-R diagram into tables (relational model schema). Indicate primary and candidate keys. Give the SQL statements to create the corresponding relations in a relational DBMS. The primary key and foreign key (if any) must be specified. Problem 3. [20 points] a) (10 points) Draw a (simple) E-R diagram that results in a primary key/foreign key constraint to be created between tables. Show the SQL statements that create the tables including the foreign key and primary key indications. b) (5 points) For the relational tables you generated in question 1(a), Describe which insert and delete operations in this database must be checked to ensure that referential integrity is not violated for that foreign key. Please state specifically which operations on which relations can cause problems. c) (5 points) Consider a database of employees in which we need to record information about employees’ addresses. Name one condition which would cause you to make ”address” an entity set of its own rather than an attribute of the employee entity set.