Assignment: Data Management

Part 1. Data Model for a Medical Clinic

1A. Draw an ERD from the following description.

• In the ERD, put primary keys and foreign keys. You do not have to place attributes other than primary keys (PKs) and foreign keys (FKs).
• There must be no multi-valued attributes in the ERD.
• There must be no many-to-many relationship in the ERD.

A medical clinic database must satisfy the following requirements:

1. A patient (with Pat_ID as the PK) can make many appointments (with Appt_No as the PK) with one or more doctors (with Doc_ID as the PK) in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made by exactly one patient for exactly one doctor.

2. If kept, an appointment yields a visit (with Visit_No as the PK). A visit is for only one appointment.

3. The visit yields one or more visit details (with Visit_No and Detail_No as the PK). There are two kinds of visit details: diagnosis and treatment. A treatment is performed by exactly one medical staff (Staff_ID as the PK) member; a medical staff member may perform many treatments.

4. Each visit creates one (or no) bill (with Bill_No as the PK); a bill may cover many visits.

5. A bill may be paid by many (or no) insurances (with Insur_ID as the PK); an insurance may cover many bills.

6. An insurance insures exactly one patient; a patient may have many insurances.

1B. Convert the above ERD into a relational model (i.e., tables with primary key and foreign keys).

Part 2. Data Model for Shipping Tickets

2A. The following figure shows a shipping ticket that is sent to the customer together with products they ordered. Prepare an entity relationship diagram (ERD) reflecting the data contained in the shipping ticket.

Note:

• The ERD must have Customer (Customer ID as the PK), Manufacturer (Manufacturer Name as the PK), Carrier (Carrier Name as the PK), and many other entities.

• Assume that there is only one customer and one carrier for each order.

The ERD must include not only entities and relationships with cardinalities but also all attributes. Underline the primary key. There must be no many-to-many relationships and no multi-valued attributes. Enter all foreign keys in the ERD.

2B. Convert the above ERD into a relational model (i.e., tables with primary key and foreign keys).

Part 3. The National Collegiate Athletic Association (NCAA) Football Database

3A. Draw an entity-relationship diagram for an NCAA football database that must contain data of:

• School: (1) school name, (2) conference that the school belongs to, (3) average cost of a game ticket in each year (since the introduction of the team), (4) number of in-state players in each year (since the introduction of the team), (5) number of out-state players in each year (since the introduction of the team), (6) number of scholarships that the school offers in each year (since the introduction of the team), (7) number of players who graduated in each year (since the introduction of the team), and (8) number of recruiting incidents (usually violations) in each year (since the introduction of the team).

• Player: (1) player id number, (2) player name, (3) birth date, (4) school that the player represents, (5) year when the player joined the school, (6) high school from which the player graduated.

- A player plays for one school. In a school, there are many players.

• Coach: (1) coach id number, (2) coach name, (3) birth date, (4) schools for which the coach worked and is working, (5) dates during which the coach worked for schools, and (6) positions at schools.

- A coach may have worked for more than one school.
- But a coach cannot work for more than one school at one time.
- Further, a coach cannot have more than one position at one time.

• Stadium: (1) stadium name, (2) stadium size (i.e., no. of seats), and (3) owner

- The owner of a stadium can be either a school or a non-school owner.
- Every stadium is owned by exactly one school or non-school owner.
- A school may own one or more stadiums.

• Game: (1) location (e.g., stadium), (2) date, (3) two schools, (4) scores, and (5) number of injuries for each school.

• Bowl Game: as a subtype entity of the game entity. (1) organizer and (2) bowl description.

The ERD must include not only entities and relationships with cardinalities but also all attributes. Underline the primary key. There must be no many-to-many relationships and no multi-valued attributes. Enter all foreign keys in the ERD.

3B. Convert the above ERD into a relational model (i.e., tables with primary key and foreign keys).

Attachment:- Assignment-Data-Management.rar