Data Model Development and Implementation Assignment 2

This assignment covers analyzing user requirements, entity identification, attribute addition, relationship outlining, functional dependencies, normalization to 3NF, ER diagram creation, SQL queries, and database implementation using MS Access and SQL Server.

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

Part D:
Determine the functional dependences.
This section presents the functional dependencies to show the relationship between the attributes
of same table. Here, I have presents the functional dependencies between the attributes of Knox
Private hospital database. [5].
 Pharmacy_NameLocation, Phone_Number, Drug_ID, Patient_ID
 Drug_IDPatient_ID, Trade_Name, Formula.
 Doctor_ID Doctor_Name, Doctor_specialty, Year_Of_Experiance, Contact_Number,
Patient_ID.
 Presciption_ID Doctor_ID, Patient_ID, pharmacy_Name.
 Patient_ID Patient_name, Patient_Address, Patient_Number, Patient_Age
6

Part E:
Then normalize these tables. Make the normalization to 3NF. State for every step in the
normalization, which functional dependency that causes it.
Normalization is the process which is used to reduce the data redundancy and remove the data
repetition in the database system. there are total three steps of the normalization in a database.
1NF:
In this all the data in the two tables, so it is difficult to understand the data. In this various attributes
are in the repeating mode. Below shows the table of the 1NF. In this 1NF there are only two tables
are created. With the two tables, it is not easy to understand the working of the database, and not
easy to maintain the databases in a proper manner. The table names are a patient table and Doctor
table [2].
7
Patient Table
Patient_ID
Patient_name
Patient_Address
Patient_Number
Patient_Age
presciption_ID
pharmacy_Name
Trade_Name
Doctor Table
Formula
Location
Cost
Doctor_Name
Doctor_specialty
Year_Of_Experiance
Contact_Number

2 NF:
It is the reduced form as compared to the 1NF, in the 2NF, the number of the table increases an
attribute of the tables will remain same. With the increase the table, it is easy to understand the
data and it reduced the data duplicity in this method. This is the 2nd method of normalization, in this
the table divided into one more part to remove the data redundancy. The new table with the name
of Pharmacy table [3].
Patient Table
Patient_ID
Patient_name
Patient_Address
Patient_Number
Patient_Age
presciption_ID
Pharmacy table
pharmacy_Name
Drug_ID
Patient_ID
Location
Cost
Phone_Number
8
Doctor table
Doctor_ID
Doctor_Name
Doctor_specialty
Year_Of_Experiance
Contact_Number
Patient_ID
pharmacy_Name

3 NF:
This is the most reduced form of the database system. In this, all the tables have the unique primary
key and do not have the repeating attributes. In this, the number of tables increases and reaches the
maximum possible tables for the database system. Below, the 3NF is shows for the same Knox
private hospital database system
In the 2 NF, there are three tables formed. But with that, there are some of the data reparations and
the data is not clear. So in the 3NF two more tables are created with the name of drugs and
Prescription [4].
9
Patient Table
Patient_ID
Patient_name
Patient_Address
Patient_Number
Patient_Age
Drug Table
Drug_ID
Patient_ID
Trade_Name
Formula
Doctor Table
Doctor_ID
Doctor_Name
Doctor_specialty
Year_Of_Experiance
Contact_Number
Patient_ID
Prescription Table
pharmacy_Name
Drug_ID
Patient_ID
Location
Prescription
Table
presciption_ID
Doctor_ID
Patient_ID
pharmacy_Name

Question2
Part A:
Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If
you make any assumptions about data that doesn’t show the problem, they must be described.
You are required to use Visio or any other software tool to create the ER diagram.
Assumptions: For developing the relational diagram, both conceptual and entity relational some of
the assumptions are taken, below shows all the assumptions.
 The patient can buy the drug from the pharmacy.
 The doctor can prescribe more than one drug to the patient.
 There will be more than one patient for a single doctor.
 The cost of the same drug will vary in different cities pharmacy [1].
Figure 2: Conceptual diagram
(Tool Used: Visual Studio)
The above figure shows the conceptual diagram of the database system. in that, the basic
relationship between the tables is created. all the attributes of the entities are defining in this
diagram.
10

Figure 3: ER Diagram
(Tool Used: Visual Studio)
The above figure shows the entity relationship diagram for the database system. this shows the
relation between the entities and attributes of the database system. in this relation shows with the
help of a primary and foreign key.
11

Question 3
Build this model using MS Access by creating these tables and Relationships. Populate these
tables with appropriate data, at least 3 records in each table.
Design view of Doctor Table:
Figure 4: Design view
Table view of Doctor table:
Figure 5: Table View
12

A form of Doctor table:
Figure 6: Form
Table view of Drug table:
Figure 7: Table view
13

Design view of Drug table:
Figure 8: Design view
A form of Drug table:
Figure 9: Form view
14

Table view of the patient table:
Figure 10: Table view
Design view of the Patient table:
Figure 11: Design view
15

A form of the Patient table:
Figure 12 Form
Table view of Pharmacy table:
Figure 13: Table view
16

Design view of Pharmacy table:
Figure 14: Design view
A form of Pharmacy table:
Figure 15: Form
17

Table view of Prescription table:
Figure 16: Table view
Design view of the prescription table:
Figure 17: Design view
A form of Prescription Table:
Figure 18: Form
18

Question4.
Write SQL queries for the following questions and execute them on the database you created in
Question 4.
Include screenshots of the outputs and all SQL statements you used to answer following
questions this model using MS SQL Server by creating these tables and Relationships. Populate
these tables with appropriate data, at least 3 records in each table.
Design view of Doctor table:
Figure 19: Design view
Table view of Doctor table:
Figure 20: Table view
19

Design view of Drug table:
Figure 21: Design view
Table view of Drug table:
Figure 22: Table view
Design view of the Patient table:
Figure 23: Design view
20

Table view of the Patient table:
Figure 24: Table view
Design view of Pharmacy table:
Figure 25: Design view
Table view of Pharmacy table:
Figure 26: Table view
21

Design view of Pharmacy table:
Figure 27: Design view
Table view of Pharmacy table:
Figure 28: Table view
22

Part A:
Details of doctors registered at Knox Private Hospital.
Below is the image shows the Query, that shows the details of the Doctors.
Select * from Doctor;
Figure 29: Query1
23

Part B:
The management is interested in knowing Name and address of patients who are older than 60
years.
Below shows the image that shows the output of the patient details, which have age is greater than
60.
Select Patient_Age, Patient_name
From patient
Where patient_Age > 60;
Figure 30: Query2
24

Part C:
Explain how to formulate a query to print Prescription of a patient.
Below image shows the details of the doctor name, medicine name and patient name as output.
Select Doctor.Doctor_ID, Drug.Trade_Name, Doctor.Doctor_Name, Patient.Patient_name
FROM (Patient INNER JOIN Drug ON Patient.Patient_ID = Drug.Patient_ID) INNER JOIN Doctor ON
Patient.Patient_ID = Doctor.Patient.ID;
Figure 31: Query 3
25

References
[1] jandrewsxu, "Assumptions, applicable measures, er diagram", Slideshare.net, 2011.
[Online]. Available: https://www.slideshare.net/jandrewsxu/assumptions-applicable-measures-er-
diagram. [Accessed: 23- Jan- 2018].
[2] O. Gabry, "Database — Normalization (Part 7) – OmarElGabry’s Blog – Medium", Medium,
2016. [Online]. Available: https://medium.com/omarelgabrys-blog/database-normalization-part-7-
ef7225150c7f. [Accessed: 23- Jan- 2018].
[3] R. Baram, "Database normalization in MySQL: Four quick and easy steps",
ComputerWeekly.com, 2015. [Online]. Available:
http://www.computerweekly.com/tutorial/Database-normalization-in-MySQL-Four-quick-and-easy-
steps. [Accessed: 23- Jan- 2018].
[4] Holowczak, "Database Normalization", Holowczak.com Tutorials, 2012. [Online]. Available:
http://holowczak.com/database-normalization/. [Accessed: 23- Jan- 2018].
[5] A. Kozubek, "On Functional Dependencies", vertabelo, 2014. [Online]. Available:
http://www.vertabelo.com/blog/technical-articles/functional-dependencies. [Accessed: 23- Jan-
2018].
26