Homework 1
This first homework is due on Tuesday October 8th. You will work alone
on this homework -- You may however exchange your ideas with
other students. You are encourage to work on this homework before the
test (Tuesday October 1st).
Instructions: Follow the 'guidelines for assignments'. Be clear,
understandable and precise in your explanations. The SQL code must be
tested on matrix.
1. Mathematics Foundations
Let S ={a,b,c} and T={{},a}. Compute S x T.
Let S be the powerset of E and |E| = n. How many relations on S are
there? Explain.
2. SQL
Exercises 2.4 and 2.5 on page 26.
Exercise 2.11 page 27.
3. Entity Relationship Diagram
Computer Sciences Department frequent fliers have been complaining to
Dane County Airport Officials about the poor organization at the
airport. As a result, the officials have 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 that are stationed and maintained
at the airport. The relevant information is as follows:
* Every airplane has a registration number, and each airplane is of a
specific model.
* The airport accomodates 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 airplane model(s), and
his/her expertise may overlap with that of the other technicians. This
information about technicians must also be recorded.
* Traffic controllers must have an annual medical examination. For
each traffic controller, you must record the date of most recent
medical exam.
* All airport employees (including technicians) belong to a union. You
must store the union membership number for each employee. Each
employee is uniquely identified by the 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 each time that 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 that airplane
received on the test.
Draw an ER diagram for the airport database. Be sure to indicate
the various attributes of each entity and their domains and
relationship set; also specify the key and cardinality constraints for
each relationship set. Specify any necessary overlap and covering
constraints as well (in English).
Notes: - Please write your assumptions clearly, if any.
- You can draw your ERD by hand or use a tool. For example you
can use MS word to draw ER diagrams using flowchart symbols. You can
as well use SmartDraw to draw them. SmartDraw is free and easy to use
(Please look on the web). If you use any other tool please provide the
name and URL (if any).
- Be very clear when you add the cardinality constraints and show your
work. Use the Chen notation.