MIS3306 - Database Management Systems Part II

MIS3306 Database Management Systems

Exam 1: Part II

Instructions (READ):

1. Use Microsoft Visio or Draw.io to draw the diagram. Diagrams made by any other tools will not be graded.

2. Use IE (crow’s foot) notation. Diagrams with any other notations will not be graded.

3. The diagram should specify entities, keys, attributes, relationship types (identifying or non-identifying), and cardinalities (MAX and min on each side). Divide an attribute further into simple attributes when appropriate. All many-to-many relationships, if any, should be converted into associative entities (the answer should not have many to many relationship). Relationship labels are not needed. Use a specialization hierarchy when appropriate. Grading is based on the completeness of the diagram.

4. Add a textbox with your full name inside the diagram (no point without your name). The figure below is an example.

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?

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

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.

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.

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).