Database management system | Computer Science homework help

5. Paste your diagram in this document and
submit this document. Any submission other than this document will
not be graded.

1. (8 points) The following are two tables with data for a Customer-Order database. Answer the following questions based on these two tables.

a. Explain entity integrity. Do the tables exhibit entity integrity?

Answer:

b. Explain referential integrity. Do the tables exhibit referential integrity?

Answer:

2.
(7 points) Dormitory Administration System

You are designing a dormitory administration system. The university has several dorms, each has many rooms. A dorm is identified by its dorm ID. Besides, a dorm has a dorm name, street address, city, state, and zipcode. Each room is identified by a combination of dorm ID and room number. Besides, a room has number of beds, and monthly rent.

Answer:

[Paste the diagram here.]

3.
(20 points) Clinic Appointment System (Must Use Supertype-Subtype Entities)

You are asked to design a database system for a clinic appointment system. The requirements for the appointment system are summarized as the follows.

· There are three types of persons in the system: staff, physicians, and patients. Any data about a person in the system must be stored in
one of these three categories (and cannot be simply stored as “person”). In addition, a person cannot be two categories at the same time (e.g., a person cannot be a staff and a patient simultaneously).

· The system has data about the staff’ ID, first name, last name, phone, and address.

· The system should contain data about physicians, including their ID, first name, last name, phone, address, certificate, and specialty.

· The system has to track information about patients. A patient, identified by its ID, has information including first name, last name, phone, and address.

· A patient can make appointments with a physician through the system. A patient may make several appointments in the system. A physician may have several appointments in the system. For each appointment, only one (and at least one) patient is involved. In some occasions, there may exist an appointment without a physician on it. Data about an appointment includes appointment ID, time, date, reason, and the patient and the physician for the appointment.

· One appointment may have one or more diagnoses. Each diagnosis has its own diagnosis ID, description, and treatment.

Answer:

[Paste the diagram here.]

4. (15 points) Book publisher system. Draw an ER diagram for the following case.
The E-R diagram should include entities, attributes, identifiers, and relationships. In the diagram, place a textbox at the top-left corner of your diagram and type
Made by FirstName LastName in the textbox (5 points).

· A book is identified by its ISBN number, and it has a title, a price, and a date of publication. A book is published by a publisher, which has its own publisher ID number and a name. Each book has only one publisher, but one publisher typically publishes multiple books over time.

· A book is written by one or multiple authors. Each author is identified by an author number and has a name and date of birth. Each author has either one or multiple books. A book may have several authors.

· Additional information is needed regarding the relationship between a book and its authors. An author may have different royalty rates for different books that he/she wrote. Also keep in mind that a book may have several authors (hence several royalty rates).

Answer:

[Paste the diagram here.]

1

image1.emf

image2.png