Database Design and Implementation Report - DATA 200, ACU 2018

This report details the database design and implementation for an Emergency Room case study, focusing on data storage and management techniques. It begins with the design of an Entity Relationship Diagram (ERD) that includes entities such as doctors, patients, nurses, receptionists, beds, medications, and appointments, highlighting the relationships between these entities using cross-foot notation. The report also discusses the implementation of the database in Microsoft Access, emphasizing its capabilities for linking and importing data, along with its template-based database support. The ER diagram is normalized up to the third normal form, with justifications provided for each table. The database includes sample data and five queries to demonstrate its functionality. Business rules and assumptions, particularly regarding patient appointments and bed assignments, are outlined. The report concludes with a bibliography of relevant sources.

Database Concepts
Name of the Students:
Name of the University:
Author Note

Reflection
This report contains the information about the database design for the Emergency Room
case study. Data is one of the most prominent factor of the current era and hence, storing them
has emerged as one of the topmost priority for the data owners. There are different options
available for proper data storage and one of them is the database. The database has cited
prominence adoption ratio. The reason behind the discussed adoption is the introduction of IT
and electrification of data. However, the development of database, migration of data from one
database to other and some other key aspects has emerged as one of the prominent concerning
factor. For the above discussed factors different tools and techniques have been availed such as
DBMS, ERD and other prominent tools and techniques. Hence to start with the design of the
database I have started with design of ER Diagram for the database. Also it should be noted that
for the assignment we were asked to implement the database on Microsoft access. The discussed
paper has focused in the linking procedure of the ERD and the Microsoft Access Database.
Hence, the following sections have offered an insight into the subject before discussing the
attempts and steps used for linking.
ERD abbreviation for Entity relationship diagram is a relationship diagram that cites
witness to the relationship between the noun (objects/entities) stored within a database. In simple
terms, it can be quoted as a component of the information/data which defines the logical
structure of a specific database. It was first introduced in 1976 by peter Chen which was latter
modified to ensure an enhanced diagram format. The ER diagram for the database that is to be
implemented contains the doctors, patients, nurses, receptionists, bed, medication and
appointment entities.

The core components of an ERD are the entities and attributes. Hence we have included a
number attributes in the above discussed entities. The connection between them are shown with
assistance of lines that are draw between the entities, attributes and the relationships. The
discussed diagram has proven to be of great vitality in database descriptions. It can also be used
in developing a system. The system development can take consideration of multiple scenarios
with assistance of the diagram before concluding with a finalized format of database. The
diagram is also used for understanding the operationalize of the database and manipulate the
functionality if needed. The ERD can be designed in different styles. The most prominent are the
IES (Information Engineering style), Chen Style, Bachman Style and Martin Style. We have
used the cross foot notation for the development for the ER diagrams.
We have implemented the database on Microsoft Access, and hence it is important that
we discuss the importance of Microsoft Access in this report. One of the globally adopted DBMS
(database management system) is Microsoft Access which is the co-subject of the discussed
paper. The offering by Microsoft is an integrated form of MJDE (Microsoft Jet Database
Engine), GUI (graphics user interface) and SDK (software development kit). The discussed
database management system offers prominent benefits and one of them is the capability to link
or import data and information that is stored in non-MS Access tools or database.
The database capabilities of MS access are not the limit for the discussed database
system. It can also act as a front-end program and allow other tools to operate in the background.
Additionally, there are template based database support from the discussed system which makes
it more significand and attractive for the users with limited knowledge of the system. These
templates are pre-installed in the system and for different options they can be downloaded from
the Microsoft’s official website. Another prominent feature of the discussed system is that it

grants access to the predefined, queries, tables, reports, macros and forms. It also allows the user
to use the above discussed components to enhance their database. The number of users that can
use the data have its association with multiple factors such as the level of use of the system,
design of the application, data size and the tasks that are being performed by or on the system.
The above discussed statement could be understood by the example that for the data size of less
than 1 GB the system will offer proper functionality for up to a 100 number of users. The
database is filled with the data as per the requirement of the assignment and five queries are also
present in the database. This paper has helped us in gaining knowledge on the database design
and implementation techniques.
ER Diagram (Old)
Please Fill
ER Diagram (New)

Normalization
The above designed ER Diagram is normalized up till third normal from. For the
justification the following discussion should be taken into account:
Firstly, for the Doctors, Patients, Nurses and receptionists, it should be noted that there is
only a single candidate key namely doctorID, patientID, nurseID and receptionistID and these
attributes are indexed and hence, they are also the primary key field in these tables. This proves
that the tables are in first normal form. Additionally, all the fields in the table functionally
depend on the primary key and hence the tables are in second normal from. But it should be
noted that there are no additional functional dependencies in the table and as a result the
transitive dependencies are eliminated and this proves that the tables are in third normal form.
As for the other three tables the bed, medication and appointments they have a single
primary key which is indexed and hence are already in first normal form. Additionally, all the
fields are dependent on the primary key. However, it is to be noted that there are foreign keys in
the table although there are no transitive dependencies within the table and hence, these tables
are also in third normal from. Hence, this justifies the normalization of the database.
Queries
Query Query Description
qrydoctor List first name, last name, email address and mobile number of all
doctors.
qryAppoinment List the details of the appointment with the appointment id.
qryBed Details of the patients who are allocated in the beds.
qryNurse Details of all the Nurses who are in charge of the beds.

qryScheduler List of patients and the receptionists who schedule the appointment.
Business Rules/Assumptions
Along with the provided information the Bed table and appointment table have been
assumed. It is assumed that at first a patient needs to have an appointment with the doctor to
discuss the issues and the appointment would be set by the receptionist. Additionally, it has been
assumed that the patients would be admitted in bed and a nurse would be in charge of the bed.

Bibliography
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Hingorani, K., Gittens, D., & Edwards, N. (2017). REINFORCING DATABASE CONCEPTS
BY USING ENTITY RELATIONSHIPS DIAGRAMS (ERD) AND NORMALIZATION
TOGETHER FOR DESIGNING ROBUST DATABASES. Issues in Information Systems, 18(1).