CSCE 4350 Database Design

Assignment # 1

Introduction/ER

Total: 120 points

Issued: 09/04/2007

Due : 09/18/2007

................................

1. (20 points) List four major advantages of a database system and two major disadvantages of a database system compared with a file system.

2. (10 points) List at least two reasons why database system support data manipulation using a declarative query languages such as SQL, instead of just providing a library of C or C++ functions to carry out data manipulation.

3. (10 points) Explain the distinctions among the terms primary key, candidate key, and a super key. Give an example Entity set to show what the primary key is and what are the candidate keys and super keys?

4. (30 points) Consider an ER diagram in Figure 1 which models an online bookstore.

a) List the entity sets and their primary keys.

b) Suppose the bookstore adds music cassettes and compact disks to its collection. The same music item may be present in cassette or compact disk format, with differing prices. Extend the ER diagram to model this addition, ignoring the effect on shopping baskets.

c) Now extend the ER diagram, using generalization, to model a case where a shopping basket may contain any combination of books, music cassettes, or compact disks.

5. (50 points) Design an ER diagram for the following application. Clearly State the assumptions you make and justify your assumptions.

1. The system needs to keep track of aircrafts, flight crews, customers and their itineraries, and flights.

2. Each aircraft has a unique number, maker, model, and capacity.

3. A crew member is either a flight attendant or a pilot. Each crew member has his/her employee ID, name, job title, and base city. Each crew member serves several flights and each flight has two co-pilots and several flight attendants.

4. Each customer has a profile (you will need to decide what information to keep) and may have several itineraries. Each itinerary consists of one or more flights.

5. Each flight has a unique flight number, a departing city, an arriving city, a departing time, an arriving time, and number of available seats. Several aircrafts may fly under the same flight number on different days.

Identify the main entities first, and then construct their relationships. Draw an ER diagram for the system using a graph drawing tool.

Submission instructions:

- give answers to problems 1-5 and submit a hard copy of the answers before class on the due date

Figure 1