Data Model Development and Implementation Assignment 2

DATA MODEL DEVELOPMENT
AND IMPLEMENTATION
Assignment 2

Contents
Question 1:............................................................................................................................................3
Part A:................................................................................................................................................3
Part B:................................................................................................................................................4
Part C:................................................................................................................................................5
Part D:................................................................................................................................................6
Part E:................................................................................................................................................7
Question2............................................................................................................................................10
Part A:..............................................................................................................................................10
Question 3...........................................................................................................................................12
Question4............................................................................................................................................19
Part A:..............................................................................................................................................23
Part B:..............................................................................................................................................24
Part C:...............................................................................................................................................25
References...........................................................................................................................................26
1

List of figure
Figure 1: Relationship diagram..............................................................................................................5
Figure 3: Conceptual diagram..............................................................................................................10
Figure 2: ER Diagram...........................................................................................................................11
Figure 4: Design view...........................................................................................................................12
Figure 5: Table View............................................................................................................................12
Figure 6: Form.....................................................................................................................................13
Figure 7: Table view.............................................................................................................................13
Figure 8: Design view...........................................................................................................................14
Figure 9: Form view.............................................................................................................................14
Figure 10: Table view...........................................................................................................................15
Figure 11: Design view.........................................................................................................................15
Figure 12 Form....................................................................................................................................16
Figure 13: Table view...........................................................................................................................16
Figure 14: Design view.........................................................................................................................17
Figure 15: Form...................................................................................................................................17
Figure 16: Table view...........................................................................................................................18
Figure 17: Design view.........................................................................................................................18
Figure 18: Form...................................................................................................................................18
Figure 19: Design view.........................................................................................................................19
Figure 20: Table view...........................................................................................................................19
Figure 21: Design view.........................................................................................................................20
Figure 22: Table view...........................................................................................................................20
Figure 23: Design view.........................................................................................................................20
Figure 24: Table view...........................................................................................................................21
Figure 25: Design view.........................................................................................................................21
Figure 26: Table view...........................................................................................................................21
Figure 27: Design view.........................................................................................................................22
Figure 28: Table view...........................................................................................................................22
Figure 29: Query1................................................................................................................................23
Figure 30: Query2................................................................................................................................24
Figure 31: Query 3...............................................................................................................................25
2

Question 1:
Part A:
Analyze all user requirements given above, identify and list all entities described in each user
requirement.
For the given case study (Knox private Hospital) there are total five entities are created. These five
entities are complete all the requirement of Knox Private Hospital.
 To complete the user view 1 requirement, one entity is created with the name of Patient.
 To complete the user view 2 requirement, entity is created by the name Doctor.
 To complete the user view 3 requirements, Doctor table has at least one apientent or each
doctor.
 To complete the user view 4 requirements, an entity is created with the name prescribe. The
where doctor gives one and more prescription to several patient and patients also take more
than one prescription from the doctors.
 To complete the user view 5 requirements, an entity is created with the name Drug.
 To complete the user view 6 requirements, an entity is created with the name pharmacy.
 To complete the user view 7 requirements, designed unique name, phone number and
address in the pharmacy entity.
Final entities in the model are:
3
Entities for Knox Private
hospital
Patient
Doctor
Prescribe
Drug
Pharmacy

Part B:
Add attributes to these entities and represent these entities (or Tables) and attributes as a
collection of Tables and attributes
In the below shows the attributes of the various tables that are created in the Knox private hospital
database system.
1. Patient (Patient_ID (PK), Patient_name, Patient_Address, Patient_Number, Patient_Age)
2. Doctor (Doctor_ID (PK), Doctor_Name, Doctor_specialty, Year_Of_Experiance,
Contact_Number, Patient_ID)
3. Pharmacy (pharmacy_Name (PK), Drug_ID, Patient_ID, Location, Cost, Phone_Number)
4. Drug (Drug_ID (PK), Patient_ID, Trade_Name, Formula)
5. Prescription (Presciption_ID (PK), Doctor_ID, Patient_ID, pharmacy_Name)
4

Part C:
Outline all relationships between entities.
In the below figure shows the relationship diagram of all the entities that are present in the database
system.
Figure 1: Relationship diagram
Table 1: Relationship Table
Table name 1 Table name 2 Relationship
Pharmacy Prescribe One to many
Drug Pharmacy One to many
Patient Pharmacy One to many
Patient Prescribe One to many
Patient Doctor One to many
Patient Drug One to many
Doctor Prescribe One to many
5