Logical and Physical Database Design and DBMS Functions

This report explains the importance of logical and physical database design, DBMS functions, SQL, ER diagram, queries, business rules and assumptions. It also suggests future features that can be added to the database.

Database Assignment
May 23
2019
Student ID:
Student Name:
Module Tutor:

Report
Reflection
Logical and Physical database design are most important to show the system requirement. Both
have different features as mentioned below-
Logical Database Design
Logical database design shows the business requirements in the form of model which is very
easy to understand. There are different ways to show system requirements in logical database
design. Some of them are as follows-
- Entity Relationship diagram
- Data flow diagram
- UML diagrams etc.
Entity Relationship diagram shows the entities and the relationship in between different
entities.
Data Flow Diagram shows the flow of data in the system.
UML Diagrams
There are different diagrams in UML diagrams like Activity diagram, state machine diagram,
sequence diagram etc. All UML diagrams show the flow of data in the system.
Physical Database Design
Physical database design is created after logical database design. After getting information from
logical database design, the physical database is created. The physical database is the actual way
of database. In physical database design the database is created in Relational Database Design
(RDBMS). In Relational Database Design the data is stored in the form of tables and columns.

DBMS Functions
There are so many functions of DBMS. Some of them are mentioned below-
- It is used to store data in the form of relations.
- It is used to fetch information in a very less time. It saves the time of users.
- It maintains the data integrity.
- It maintains the database security.
- It helps in easy data access.
(Studytonight 2014)
There are different approaches to make system designs e.g. Top-Down and Bottom-Up.
Top-Down Approach
In Top-Down approach, the whole system is taken as one single entity and later decomposed into
sub systems according to achievements and requirements.
Bottom-Up Approach
In Bottom-Up approach, the different basic systems are taken and work together to form the
required system. The sub systems gathered until the required one system is not generated.
The Bottom-Up approach is best for the Emergency Room because we have very clear vision to
make the single system that will fulfill the gathered requirements in logical database design.
SQL (Structured Query Language)
Structured Query Language is used to manage the data in the DBMS (Database Management
System). It performs operation on data in the Database Management System like create records,
edit records, delete records, create tables, edit tables, create views, edit views etc. It is used to
perform different operation on the database like below-
- It allows the users to create and manage the complete database.
- It allows the users to fetch specific data any time.
- It shows the simplified data in the form of reports.
- It allows the users to create stored procedures, functions etc.
- It allows the administrator to authenticate the users.
The Emergency Room system is created in MS Access. The SQL is used to manage the
Emergency Room database. Different queries are built in the database to show the summarized
result sets of data.

The database design is changed from earlier like the addresses are stored in separate tables
because a person like doctors, nurses, patients etc. may have any number of addresses. Similarly,
the emails and phone numbers are also stored separately because doctors, nurses, patients etc.
may have any number of emails and phone numbers.
In future we can add different more features into the database as mentioned below-
- We can add patient’s family member’s details.
- We can add patient’s medical history in separate table.
ER Diagram
Old ER Diagram

New ER Diagram

( Lucid Software Inc. 2015)
(Janalta Interactive Inc. 2015)
(teach-ict.com n.d.)
Queries
Query: qry_patient
SELECT * FROM Patients;
This query will show the complete detail of all patients.
Query: qry_Patient_Doctor
SELECT Doctors.FirstName+' '+Doctors.MiddleName+' '+Doctors.LastName AS DoctorName,
Patients.FirstName+' '+Patients.MiddleName+' '+Patients.LastName AS PatientName,
Medication.Prescription FROM Patients INNER JOIN (Doctors INNER JOIN Medication ON
Doctors.DoctorID=Medication.DoctorID) ON Patients.PatientID=Medication.PatientID;
This query will display the name of patients, their doctor name and prescription.

Query: qry_PatientPrescription
SELECT Patients.FirstName, Patients.MiddleName, Patients.LastName,
Medication.PrescribeDate, Medication.StartDate, Medication.EndDate, Medication.Prescription
FROM Patients INNER JOIN Medication ON Patients.PatientID = Medication.PatientID;
This query will display the complete detail of patients along with prescription.

Query: qry_Doctor_no_Prescription
SELECT Doctors.FirstName+' '+Doctors.MiddleName+' '+Doctors.LastName AS DoctorName,
count(Medication.DoctorID) AS NoOfPrescription FROM Doctors INNER JOIN Medication
ON Doctors.DoctorID=Medication.DoctorID GROUP BY Doctors.FirstName,
Doctors.MiddleName, Doctors.LastName;
This query will display the complete doctors and their number of prescriptions.
Query: qry_Doctor_Prescription
SELECT Doctors.FirstName+' '+Doctors.MiddleName+' '+Doctors.LastName AS ['Doctors who
did not Prescibed yet'] FROM Doctors WHERE DoctorID not in (Select DoctorID from
Medication);
This query will display the doctors who didn’t prescribe till yet.

Business Rules and Assumptions
Following business rules and assumptions are followed while implementing the database-
- There are four types of persons in the Emergency Room.
- A doctor, nurse or receptionist may also be the patient.
- A person may have zero or any number of addresses.
- A person may have zero or any number of emails.
- A person may have zero or any number of phone numbers.
- A doctor, nurse or receptionist may be involved in more than one consecutive shifts.
- Every shift will contain two nurses, two doctors, two receptionists etc.
- The patient may be relieved after medications.
- A patient may be admitted in the Emergency room for medications.
References
Studytonight. (2014). Database Technology. [online] Available at:
http://www.studytonight.com/dbms/er-diagram.php. [Accessed 24 May 2019]
Lucid Software Inc. (2015). ER Diagram Symbols and Meaning. [online] Available at:
http://www.sparxsystems.com/enterprisearchitectuserguide/modelinglanguages/
dataflowdiagrams.html. [Accessed 24 May 2019]
Janalta Interactive Inc. (2015), Functional Dependency, [online] Available
at:http://www.techopedia.com/definition/19504/functional-dependency. [Accessed 24 May
2019]
teach-ict.com (n.d.), Third Normal Form, [online] Available
at:http://www.teach-ict.com/as_a2_ict_new/ocr/AS_G061/315_database_concepts/
normalisation/miniweb/pg9.htm. [Accessed 24 May 2019]