Database System Design and Implementation: Knox Hospital Project

This assignment focuses on the design and implementation of a database system for Knox Private Hospital. The student explores entities such as doctors, patients, and drugs, defining their attributes and relationships. An entity-relationship diagram (ERD) is constructed to visualize these relationships, including many-to-many relationships between doctors and patients and one-to-many relationships between patients and drugs. The assignment also delves into functional dependencies and database normalization techniques (1NF, 2NF, 3NF, and BCNF). The database model is built using MS Access, including the creation of tables, relationships, and the population of these tables with relevant information. SQL queries are provided to demonstrate data retrieval and manipulation. The assignment concludes with a summary of the database system's role in managing and controlling hospital information.

Data model development and implementation
1

Contents
INTRODUCTION...........................................................................................................................3
PART B...........................................................................................................................................3
b). Discuss the attributes to the different entities and representing them as a collection of tables
and attributes................................................................................................................................3
c) Determine the two relationship between the entities...............................................................3
d). Discuss to identify the functional dependencies....................................................................4
e). Normalise the database tables.................................................................................................4
2) Construct the structure of database by using entity relationship diagram and make the
assumption about the data which are explicitly given as per problem........................................5
3). Build the model by using MS Access by creating the relationship between tables. Populate
these tables with the appropriate information..............................................................................7
Relationship.....................................................................................................................................8
CONCLUSION..............................................................................................................................11
2

INTRODUCTION
Database system is a software that can use for providing the interface to database for
managing and controlling the information in proper manner. This type of software is interested to
store large amount of data and information. This report will discuss about the Knox private
hospital contracted with the Green IT service to develop and design the effective database system
of hospital. Furthermore, this report will discuss about the entity relationship model to establish a
relationship between them. It will develop the database of hospital system by using MS Access
and also creating the tables and relationships.
PART B
b). Discuss the attributes to the different entities and representing them as a collection of tables
and attributes.
Entity can be described as a real world object which can be represented by the set of properties
and attributes. The Entity mainly represents the data in a Relational data base management
system. On the other hand, attributes can be termed as the instances of the entity which
represents the entity in an same or another table. An attribute of the entity is a particular property
that describes the entity. Entity relationship diagram shows the relationship of entity sets which
are stored within a database.
For example, according to the case of the Knox private hospital, Doctor, Patient and
Drugs are three different entities which has their own attributes such as Doctor_id, Patient_id,
Drug_id etc.
c) Determine the two relationship between the entities
As per the case study of the Knox private hospital, there are various different types of
entities are created in the ERD. All the entities represent different table within database.
For example, Doctor and patient entities within the ERD presented by the Entity
relationship diagram of the Knox private hospital. Both the doctor and patient entity within ERD
share many to many relationships with each other. As there can be many patients and therefore
a patient can have many doctors for different diseases.
Also, there is one more entity which maintains the relation with another entity that is drug
and patient. Patient entity maintains one to many relationships with drug entity. As one patient
can have many drugs for different diseases which represents one to many relationships between
both the entities
3

d). Discuss to identify the functional dependencies.
Functional dependency is related to a candidate key which uniquely identify the field
determine all other attributes that establishes the relation. In various cases, functionally
dependent sets are irreducible if right hand set of functional dependency holds only one attribute.
e). Normalise the database tables
Normalisation is a technique of organizing the data in the database table. It can be
considered as a systematic approach that helps in decomposing the tables to eliminate the data
redundancy and undesirable character sticks such as select, insert, delete, update etc.
Normalisation can be categorised into various forms such as 1NF, 2NF, 3NF and BNCF.
4

SQL Queries
5

Design View:
6

7

2) Construct the structure of database by using entity relationship diagram and make the
assumption about the data which are explicitly given as per problem
Entity relation diagram within the project as per the Scenario of the Knox private table
can be described below:
Figure 1 Entity relationship Diagram
Assumptions about the data which are explicitly given as per the case study of the Knox private
hospital as the patient must have a unique plus not NULL by which the patient can be easily
accessed or represented throughout the treatment journey. Along with this, it can be assumed that
doctors assigned according to the speciality to treat the disease of the patient. Also, Doctor's id is
a primary key within the entity named Doctor and doctor's Id can be referred as a foreign key
within other tables within the database.
8

3). Build the model by using MS Access by creating the relationship between tables. Populate
these tables with the appropriate information.
Figure 2ER Model by using MS Access
9

Relationship
SQL Statement
Output:
10

Relationship
SQL Statements
Output
11

CONCLUSION
As per discussion, it concluded that Database system is a type of system that help for
managing and controlling the information that can use for providing the interface to database for
managing and controlling the information in proper manner. This type of software is interested to
store large amount of data and information. It also summarised the Knox private hospital
contracted with the Green IT service to develop and design the effective database system of
hospital. Furthermore, it is also creating an entity relationship model to establish a relationship
between them. It is developing the database of hospital system by using MS Access and also
creating the tables and relationships.
12