I am a cont. ed student but I'm not presently enrolled. I was wondering if
anyone could assist me in answering these questions from my "self-study"
practice section. I'm stumped and with no professor, I don't have access
to solutions for the problems I don't understand. Thanks.

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’ve 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 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
expertise may overlap with that of other technicians. This information about
technicians 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
the social security number uniquely identifies each employee.

The airport has a number of tests that are used periodically to ensure that
airplane 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 that a given technician
using a given test tests a given airplane. For each testing event, the information
needed is the date, the number of hours the technician spent doing the test,
and the score that 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 a technician who is an expert on that
model must conduct tests on a plane. How would you express this constraint
in the ER diagram? If you cannot express it, explain briefly.


A company database needs to store information about employees (identified
by ssn, with salary and phone as attributes); departments (identified 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 identified 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.

1. Draw an ER diagram that captures this information.
2. Give the SQL to construct the database tables necessary for the ER diagram.


A common topic is the issue with 1-many relationships: the “one” entity can
be absorbed into the “many” entity, thereby reducing the number of tables.
Give an example database of when you would do this and another example of
when you wouldn’t, and explain why.


Assume 40 blocks of data. Describe how the data and any redundant data might
be spread out over a RAID 5 system with 10 disks. State how data and parity
is mapped onto the disks (what data is on each disk), and what blocks each
parity block is computed over, and what disk it is stored on.


Draw a picture of what a hash database would look like after inserting the
following data. Don’t do any splits. Assume we start with four buckets.
Each page can hold four keys.

40, 36, 17, 22, 25, 39, 84, 81, 68, 27, 95, 74, 24, 29,32, 55, 59, 96.

What does the hash table look like after one split? After the split, where
does 37 hash to?


Construct a B+ tree for the data above. Assume that leaf nodes and index
nodes both hold four items. Illustrate the 1st, 5th and last tree.

Given the data above, describe and picture what the sorting process will
look like. Assume that each data page holds just two items, that we have
8 pages of memory available, and that we’ll use double-buffering when that’s
useful.



Imagine you’re setting up a database for FunTime videos franchise (one store).
Your initial database is very simple: it has tables of customers, videos,
and rentals. The customers have a card number, a name, and an address.
Since the store will buy multiple copies of each movie, there will be one
record per tape, because we have to keep track of who has what tapes, so
that when one gets put in the drop box, the correct customers is credited
with the return. Assume that a title is unique as a title and a director
are unique. The rental relationship is many-to-many, since a customer can
rent many videos, and multiple customers will rent a video. When a video
is rented the “returned” date is null; it’s filled in when the video is returned.


Create table customers (cardno number,
name varchar2 (20) not null,
address varchar2 (30),
balance number (5, 2),
primary key (cardno));

Create table videos (vid number,
title varchar2 (20) not null,
director varchar2 (15),
genre varchar2 (10),
primary key (vid));

Create table rentals (vid number,
cardno number,
rented date not null,
returned date,
returnedp number,
primary key (cardno, vid),
foreign key (cardno) references customers,
foreign key (vid) references videos);

 The total and average balances among the customers.

 Names of all customers who currently have a video out

 The titles of videos that are completely out (all copies are rented).
Note that a title should appear only once in the listing.

 The boss wants to know how many tapes we have rented, broken down
by genre (how many dramas, comedies, westerns, etc.) so they can determine
what genres are the best money producers.


You’re designing a database for Delaney books. Explain how you would set
up the database for efficient querying in multiple ways. What keys will
you use? What indexes will you build? What alternatives for record storage
will you use? How will you organize the database and indexes? Discuss (regarding
probability and search costs) of various kinds of searches. Prepare to handle
at lease 3-4 different kinds.