Database Management System: Hospital Database Design and Normalization

This report provides a comprehensive overview of database management systems (DBMS), starting with the definition of a database and DBMS, followed by an explanation of relational databases and their advantages over conventional data storage methods. It emphasizes the importance of Entity Relationship (ER) diagrams in the database design process and discusses database anomalies, along with normalization techniques (1NF, 2NF, 3NF) to address these issues. The report includes a practical scenario of designing a hospital database, complete with ER diagrams illustrating relationships between patients and doctors, conversion to a relational model, identification and elimination of anomalies through normalization, and relevant SQL commands for table creation, data insertion, updates, and deletion. The document concludes with a bibliography of cited sources.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of Student-
Name of University-
Author’s Note-

1DATABASE MANAGEMENT SYSTEM
Answer of Question 1:
Collecting information in an organized way is generally known as database. In a
database, the data can be easily accessed, updated, and managed. The data that are put in
database are put in rows, tables, and columns and the data are indexed so that the accessing is
easier to find the relevant information. The data that are stored in the database can be deleted,
expanded, and updated when new information are added. The workloads are created and can also
be updated by the processes involved in database and querying of data is also possible with the
data in the database.
Answer of Question 2:
DBMS commonly known as Data Base Management System is a system software used
for managing and creating a database. Programmers and users are provided by DBMS in a
scheduled way to create the project, retrieve and update the data and also manage the data.
The end users gets the ability to delete, creates, update, and read data in database using
DBMS. DBMS works as an interface in between the end user and the database or in between the
application program so that the data can be organized consistently and can also be accessed
easily. Mainly three important things are managed by DBMS. They are: data, the engine of
database which helps in data accessible and database schema, in which logical structure of
database is defined.

2DATABASE MANAGEMENT SYSTEM
Answer of Question 3:
A scientist E.F.Codd in IBM first invented relational database in the year 1970.
Relational database is mainly collection of data that is accessed or reassembled in different ways
without organizing the table sin the database. The interface between the application program and
the standard user is the Structured Query Language (SQL). Statements that are involved in SQL
have interactive queries from the relational database and gather the data for the reports. Set of
tables, which has the data fit in the predefined categories of the database, is the relational
database. Example: for data entry of a business, a table describes a customer with all the columns
for customer name, customer address, phone number and many more details.
Answer of Question 4:
The advantages of database DBMS over conventional storing system of data are:
 The flexibility of DBMS is much more than the convectional way of database. The
programs and the data that are independent on each other and there is no need of
modification of the program when different kinds of unrelated data are generally added or
is deleted from database. The physical storage does not change with the alteration of data.
 The DBMS has a fast response to the information request that is given by the user where
as in the conventional process, the response to any request is generally slow.
 DBMS can have multiple access but the conventional method of data storing does not
support multiple access. The software of database allows the data to be accessed in
various ways.
 The training cost of DBMS is comparatively less than giving training to users who are
involved in conventional method.

3DATABASE MANAGEMENT SYSTEM
Answer of Question 5:
The importance of Entity relationship diagrams are much more better than any other
diagram because:
 Provides Visual representation of design that is being done
 ERD make the communication effective as it states all the entities and attributes involved
in the design.
 The ERD is a very simple diagram and it is very easy to understand even if the user is not
trained at al.
Answer of Question 6:
The issues that arise in the database are generally known as the database anomaly, which
mainly occurs because of storing the data in flat database and poor planning. This anomaly of the
database can be removed by normalization process, which is splitting the tables into simplified
form.
Answer of Question 7:
1NF (First Normal Form)- I First Normal Form, the columns in a table does not have
multiple value. It will only have anatomic value.
2NF (Second Normal Form)- A table is considered to be in 2NF if:
 The table should be in 1NF.
 All the non-prime attributes should be dependent on proper subset on the
candidate key of the table.

4DATABASE MANAGEMENT SYSTEM
3NF (Third Normal Form)- Third Normal Form has to satisfy the following conditions:
 Table should be in 2NF.
 If there is transitive functional dependency of the non-prime attributes on super
key, that should be removed.
Answer to Question 8:
 Entity Relationship Diagram of a hospital
Figure: ERD Diagram for the hospital

5DATABASE MANAGEMENT SYSTEM
 ERD shows the relationships, primary key, and foreign key in 1NF. This ERD is in 2NF
Figure: ERD in 1NF

6DATABASE MANAGEMENT SYSTEM
 ERD after removing the anomalies and normalizing the ERD in 3NF
Figure: 3NF Diagram of the hospital removing the anomalies
SQL commands
 CREATE TABLES
DOCTOR
SQL>CREATE TABLE DOCTOR(D_ID NUMBER(3) PRIMARY KEY, D_NAME
VARCHAR(30) NOT NULL, D_CONTACT_NO NUMBER(10), D_TYPE VARCHAR(10),
D_HOURS NUMBER, SPECIALIZATION VARCHAR(20));

7DATABASE MANAGEMENT SYSTEM
PATIENT
SQL> CREATE TABLE PATIENT(P_ID NUMBER(5) PRIMARY KEY, P_NAME
VARCHAR(30) NOT NULL,GENDER VARCHAR(1) NOT NULL,P_ADDRESS
VARCHAR(50) NOT NULL, CONTACT_NO NUMBER(10),D_ID NUMBER(3), FOREIGN
KEY(D_ID) REFERENCES DOCTOR(D_ID), DOB DATE NOT NULL);
ROOM
SQL> CREATE TABLE ROOM (ROOM_ID NUMBER(3) PRIMARY KEY,
ROOM_TYPE VARCHAR(10) NOT NULL);
PATIENTS_STATUS
SQL>CREATE TABLE PATIENTS_STATUS (P_ID
NUMBER(5),DATE_ADMITTED DATE NOT NULL, DATE_DISCHARGED DATE NOT
NULL, ROOM_ID NUMBER(3), FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID),
FOREIGN KEY(ROOM_ID) REFERENCES ROOM(ROOM_ID));
EMPLOYEE
SQL> CREATE TABLE EMPLOYEE(E_ID NUMBER(4) PRIMARY KEY, E_NAME
VARCHAR(30) NOT NULL, E_GENDER VARCHAR(1), E_CONTACT_NO NUMBER(10)
NOT NULL, E_ADDRESS VARCHAR(50) NOT NULL, E_TYPE VARCHAR(10), SALARY
NUMBER NOT NULL);
NURSE

8DATABASE MANAGEMENT SYSTEM
SQL> CREATE TABLE NURSE (EMP_ID NUMBER(4) PRIMARY KEY,
NURSE_NAME VARCHAR(30) NOT NULL, NURSE_TYPE VARCHAR(10),
SHIFT_START_TIME VARCHAR(5), END_SHIFT_TIME VARCHAR(5), FOREIGN
KEY(EMP_ID) REFERENCES EMPLOYEE(E_ID));
NURSE_STATUS
SQL> CREATE TABLE NURSE_STATUS( EMP_ID NUMBER(4) NOT NULL, D_ID
NUMBER(3), P_ID NUMBER(5), FOREIGN KEY(EMP_ID) REFERENCES
NURSE(EMP_ID),FOREIGN KEY(P_ID) REFERENCES PATIENT(P_ID),FOREIGN
KEY(D_ID) REFERENCES DOCTOR(D_ID));
 INSERT INTO TABLES
Doctor table
SQL> INSERT INTO DOCTOR
VALUES(012,'JASON',3214569870,'SURGEON',9,'HEART');
SQL> INSERT INTO DOCTOR
VALUES(101,'NANCY',8972564870,'REGULAR',8,'AURTHOPEDDIC');
SQL> INSERT INTO DOCTOR
VALUES(136,'ROB',5645987802,'SURGEON',9,'DENTAL');

9DATABASE MANAGEMENT SYSTEM
Patient Table
SQL> INSERT INTO PATIENT VALUES(00001, 'JOHN', 'M', 'SYDNEY,
AUSTRALIA',1019654789,101,'05-OCT-1992');
SQL> INSERT INTO PATIENT VALUES(00002, 'RACHEL', 'F', 'MELBOURNE,
AUSTRALIA',1035103579,136,'16-OCT-1991');
SQL> INSERT INTO PATIENT VALUES(00003, 'RICKY', 'M', 'BRISBANE,
AUSTRALIA',1045698701,101,'31-AUG-1985');

10DATABASE MANAGEMENT SYSTEM
Room Table
SQL> INSERT INTO ROOM VALUES (106,'ICU');
SQL> INSERT INTO ROOM VALUES(135,'ICCU');
SQL> INSERT INTO ROOM VALUES(021,'OT');
SQL> INSERT INTO ROOM VALUES(005,'CHECK-UP');

11DATABASE MANAGEMENT SYSTEM
Patient Status
SQL> INSERT INTO PATIENTS_STATUS VALUES (001,'12-JAN-2018','20-JAN-2018',21);
SQL> INSERT INTO PATIENTS_STATUS VALUES (002,'26-DEC-2017','05-JAN-2018',5);
SQL> INSERT INTO PATIENTS_STATUS VALUES (003,'02-FEB-2018','05-FEB-2018',106);