[Solved] 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 specic model. The airport accommodates a number of airplane models, and each model is identied 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. Trac controllers must have an annual medical examination. For each trac 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 identied 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 briey 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 specied. 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 specically 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.