Introduction:

This assignment will design a database that manages the hospital management system. This will define the Royal Rundle Hospital. This assignment is related to design a database management system for Royal Rundle Hospital. THE First E-R diagram will necessary for the design database using SQL. Royal Rundle Hospital manages many departments, doctor information, nurse data, and compounder information, billing, and patient data. In Royal Rundle Hospital the patient has some kind of ailment touch with the hospital and will be solved by the doctor. This assignment understands the data required for a given case study for Royal Rundle Hospital. This will capture the key entities and their relationship with other entities. Also define the benefit, assumption, constraint, and advantage of Royal Rundle Hospital. At last, this report will include effective communication with Royal Rundle Hospital.

 

Understanding of data requirements:

This paragraph will include a very good understanding of data that will be needed for Royal Rundle Hospital. Royal Rundle Hospital will have some data related to doctors, nurses, compounder, treatments, billing, patients, and other staff. Royal Rundle Hospital has some actors which act inside a hospital. Hospital has many benefits for patients. There are many types of department which handled by this hospital. Some information related to the department will be correlated to the doctor. There are many types of specialists who work inside a hospital. Some data requirements are related to patients. Below the list of data required is showing for this hospital—

• Patients — All information about patients
• Department — All information about departments in RRH
• Doctor- Information related to doctor
• Treatment — data information related to past and future treatment and other
• Room — requirement of room data
• Billing — Information of billing
• Nurse — Information related to nurse
• Compounder  –   containing information related to the compounder

These data requirements are needed in this system. Some operation work done by these hospitals showing below—

5. It will manage the staff working in this hospital.
5. This will keep all records for patient death, patient treatment, and patient information.
5. Stored the workers' information which are working for this hospital.
5. Stored all information of doctors.
5. Stored and generate the bill of patients, billing information, payment information.
5. This will track all data of each department and worked with a particular skill. 

 

This system will need doctor information. In  Royal Rundle Hospital the main actor is Doctor. There are two types of the doctors in the hospital, namely, regular doctors and call-on doctors. Regular doctors are those doctors who come to the hospital daily. Call-on doctors are those doctors who are called by the hospital if the relevant regular doctor is not available. So doctor information is needed for this system, this is the main data requirement for Royal Rundle Hospital. Some data information related to a doctor is like, doctor type which has two types either Call-on or Regular, doctor address, doctor id, doctor's name, and doctor contact as well as doctor specialist. This information is related to doctors who need to store in the database.

Another data requirement is related to the Patient. The Patient will contact the doctor and the doctor will make a decision about the patient disease and admitted it into the hospital as required. So here Royal Rundle Hospital will save the data requirement related to patients. So some data information regards patients are patient name, patient id, patient number, patient address, patient disease, and patient age. So this information will need for this system.

In RRH, there are many departments like Orthopedic, Pathology, Emergency, Dental, Gynecology, Anesthetics, I.C.U., Blood Bank, Operation Theater, Laboratory, M.R.I., Neurology, Cardiology, Cancer Department, Corpse, etc. There is an OPD where patients come and get a card (that is, the entry card of the patient) for check-up from the relevant doctor. So Department information very important when doctors, nurse and compounder will need to interact with each other. Some data information related to department required for the system which are department name, department type, and department unique id etc.

This system will need the data requirement related to nursing. Nurse is the primary helper for all patients as well as doctor. Nurse related information like nurse name, nurse age, nurse address, nurse salary and nurse contact are required for this database. This database has full information about nurses and doctors. Same thing will be happen for Compounder which will be the part of helper communities. Compounder has main purpose for help out the patient as well as emergency services.  This system need information related to compounder. Some information will be stored into the database related to compounder are compounder name, compounder address, compounder salary, compounder age, compounder number etc. Both information regards compounder and nurse will be associated with a particular department.

Billing information or we can say payment information will be needed for this database. In Royal Rundle Hospital this is necessary to store billing information. Some information related to billing like billing amount, payment mode either online or offline, the discharge date of the patient, payment type, and unique payment id.  So this information is required for Royal Rundle Hospital.

When a patient is treated by the doctor then the doctor will give them the perception which has full detailed information about the treatments of particular patients. This system will need the treatment information put up by Royal Rundle Hospital doctor. The treatment has some information which needs for Royal Rundle Hospital are, treatment type, treatment date, treatment patient name etc. this information is required for Royal Rundle Hospital.

Now we have discussed some data requirements for this hospital but one information is related to patient calls which is room information. When the patient will be admitted by the doctor's report then the room service will be needed for these patients. There are many rooms in the Royal Rundle Hospital. The room will be available in zero stars to 5-star rating. Some information related to rooms is room id, room type, room capacity, room number, and room availability. 

These are all data required for this hospital.

 

 

Capture a key entity and their relationship:

As we discussed above the data requirements applied to this hospital. These are also data requirements as we discuss for Royal Rundle Hospital. These requirements have a direct relation to the entity and its attributes. The entity is an object in the database. An entity can be a single thing, person, piece, and place or we can say object. The attributes are the data component related to the particular entity. In this section, the discussion is going for making a entity and its relationship to Royal Rundle Hospital.

Below is all entity showing into the table form—

These are all entity which will be used for making Royal Rundle Hospital database design. Here patient_RRH entity holds the information related to the patient which admitted in RRH, doctor_RRH entity holds the information related to doctors who are working in a hospital either they are Call-on or regular. Same Nurse_RRH and Compounder_RRH store all information related to the Nurses and compounder for every department in which they work. Department_RRH entity has information about the department of royal rundle hospital. There are many types of departments hold by this hospital. This may include some departments like surgical, maternity, obstetric care, dialysis, emergency, mental health, aged and palliative care, allied health services, and a 24-hour emergency department. So this type of all information related to department hold by department_rrh entity. Room_RRH entity has related information of rooms and they may include all data which define rooms information be located to particular patients. Payment_rrh entity has billing information and treatment_rrh table has treatment information of patients. The staff will be allocated to the department's entity.

Below is an E-R diagram of Royal Rundle Hospital database—

This  E-R diagram will be allocated all tables that will be used and managed by the database.  There are eight entities in this E-R  diagram. All Entity is related to each other. They have some cardinality and constraints that is used in relation for each other. Some cardinality of the relation of this database used listed below—

5. many to many: In this cardinality, the multiple records in one table is related to multiple records in another tables. Like for example in our database, multiple doctors can treat multiple patients.
5. one to many:  A record in a table will be related to many records in another table, like for example a department can have more than one nurse.
5. One to one: One table is related to another table but only one relation is existing at one time. For example doctor to patient relationship.

The constraint which used in the database are primary key constraint and foreign key constraint. Both constraints will be used in database. Some information related to this constraint showing below—

5. Primary key constrain:

The primary key will be used in any table but only one time. There is only one primary key in one table but the foreign key can be more than one. The primary key has two functions, it is unique and it is not null. It uniquely identify the whole table records. It will not accept the null values. It is a clustered index and defines all sequences of cluster index. In our database the primary key used in all tables or all entities. In the E-R diagram, the doctor_rrh entity has a primary key which is doctor_id. Same for all entities like patient_rrh has patient_id primary key and treatment_rrh has treatment_id primary key. These primary key will be used to define records of the table as uniquely.

 

5. Foreign key constraint:

A foreign key constraint is a field in a table that is the primary key in another table. This will refer a column in a table which is the primary key of another table. This constraint is used for making a relationship to another table. We have more than one foreign key as compare to the primary key. Foreign keys do not create an index automatically. Also, it is not able to create a cluster and noncluster. So if a user wants to create an index in the table then he needs to create an index manually using some command. Some foreign keys used in the E-R diagram are department_id in doctor_rrh entity and patient id in room_rrh entity.

All eight entity have some attributes which are showing below—

patient_rrh	Patient _ name , patient _ number ,  patient _ id ,  patient _ age , patient _ address , doctor _ id , patient _ disease.
doctor_rrh	Doctor _ id , doctor _ name , doctor _ contact , doctor _ type , doctor _ address , doctor _ specialist , department _ id
Nurse_rrh	Nurse _ id , nurse _ age , nurse _ name , nurse _ contact , nurse _ salary , nurse _ address , department _ id
treatment_rrh	Treat _ id, treat _ type , treat _ date , patient _ id
Compounder_rrh	compounder _ id , compounder _ age , compounder _ name , compounder _ contact , compounder _ salary , compounder _ address , department _ id
room_rrh	room _ id , room _ number , room_ capacity, room_availablity, room_type, patient_id
Payment_rrh	Payement_id, payement_mode, payement_type, payment_date, discharge_date, patient_id
department_rrh	Deparment_id, department_ name, department_type, doctor_id

 

All eight entities will be related to each other as related information. These relationships are:

Entities	Relationship	Description
patient_rrh and doctor_rrh	one to one	One patient will be allocated to one doctor
patient_rrh and payment_rrh	one to one	One patient have one specific bill.
doctor_rrh and department_rrh	many to many	Many doctor have many department which they are work at a time.
patient_rrh and room_rrh	one to one	One room will be allocated to one patient at a time.
department_rrh  and compounder_rrh	One to many	One Department have many compounders which they have worked.
department_rrh  and nurse_rrh	One to many	Department have many nurses which they have worked.
patient_rrh and treatment_rrh	one to many	One patient will have more than one treatment.

 

These are all relationship between all entities with each other. For doctor and patient one to one relationship will applied. 

patient_rrh:

 

Attribute	Attribute_description	Data type	Constraints
Patient _ id	This attribute store patient id	Int	The primary key
Patient _ name	This attribute store patient name	Varchar(255)	-
Patient _ number	This attribute store patient contact	long	-
Patient _ address	This attribute store patient address	Varchar(255)	-
Patient _ age	This attribute store patient disease	int	-
Patient _ disease	This attribute store patient disease.	Varchar(255)
Doctor _ id	This attribute store doctor id	Int	Foreign key

 

Nurse _ RRH entity—

Attribute	Attribute_description	Data type	Constraints
nurse _id	This attribute store nurse id	Int	The primary key
nurse_ name	This attribute store nurse name	Varchar(255)	-
nurse _ contact	This attribute store nurse contact	long	-
nurse _ address	This attribute store nurse address	Varchar(255)	-
nurse _ age	This attribute store nurse age	int	-
nurse _ salary	This attribute store nurse salary.	int
department _ id	This attribute store department id	Int	Foreign key

 

Compounder_RRH entity—

Attribute	Attribute_description	Data type	Constraints
compounder _id	This attribute store compounder id	Int	The primary key
compounder _ name	This attribute store compounder name	Varchar(255)	-
compounder _ contact	This attribute store compounder contact	long	-
compounder _ address	This attribute store compounder address	Varchar(255)	-
compounder _ age	This attribute store compounder age	int	-
compounder _ salary	This attribute store compounder salary.	int
compounder _ id	This attribute store department id	Int	Foreign key

 

 

Doctor_RRH entity—

Attribute	Attribute_description	Data type	Constraints
doctor _id	This attribute store doctor id	Int	The primary key
doctor _ name	This attribute store doctor name	Varchar(255)	-
doctor _ contact	This attribute store doctor contact	long	-
doctor _ type	This attribute store doctor type	Varchar(255)	-
doctor _ address	This attribute store doctor address	Varchar(255)	-
doctor _ specialization	This attribute store doctor specialization.	Varchar(255)
department _ id	This attribute store department id	Int	Foreign key

 

Room_RRH entity—

Attribute	Attribute_description	Data type	Constraints
Room  _ id	This attribute store room id	Int	The primary key
room _ number	This attribute store room number	int	-
room _ capacity	This attribute store room capacity	int	-
room _ availability	This attribute store room availability	int	-
room _ type	This attribute store room type	Varchar(255)	-
patient _ id	This attribute store patient id	Int	Foreign key

 

Payment_RRH entity—

Attribute	Attribute_description	Data type	Constraints
payment  _ id	This attribute store payment id	Int	The primary key
Payment  _ mode	This attribute store payment mode	Varchar(255)	-
Payment  _ date	This attribute store payment date	date	-
Payment  _ type	This attribute store payment type	Varchar(255)	-
discharge _ date	This attribute store discharge date	date	-
patient _ id	This attribute store patient id	Int	Foreign key

 

Treatment_RRH:

Attribute	Attribute_description	Data type	Constraints
treat  _ id	This attribute store treat id	Int	The primary key
treat  _ type	This attribute store treat  type	Varchar(255)	-
treat  _ date	This attribute store treat date	date	-
patient _ id	This attribute store patient id	Int	Foreign key

 

Department_RRH entity—

Attribute	Attribute_description	Data type	Constraints
deparment  _ id	This attribute store deparment id	Int	The primary key
deparment _ type	This attribute store deparment  type	Varchar(255)	-
deparment  _ name	This attribute store deparment name	Varchar(255)	-
doctor _ id	This attribute store doctor id	Int	Foreign key

 

 

Effective communication:

This information system can be treated on the basis of time and cost for processing the data manually and automatically as differed to one of the automated systems. From given point, it was found, management paid large amount of cost on papers and folders to store data. They store the extra requirement for staff and maintenance of the hospital. So the benefit of this database design will reduce the paper-pen work and free from a bundle of folders and papers.

Some advantage of royal rundle hospital is showing below—

5. No one can hack this database system, this is protected and safe for royal rundle hospital.
5. The database of royal rundle hospital has high efficiency.
5.  This system will store the all data of patients either they have gone or they have admitted.
5. All departments don’t need to interact with a mediator which needs to call the department vise doctor. This system will automatically define the particular doctor with a particular department need.
5. This system will reduce the time complexity and reduce the unnecessary work done by those who carry this management manually.
5. This system will trace debts automatically.
5. This system will calculate the overall doctor's performance and the radio of the doctor works.

Some challenges in Royal Rundle hospital—

Design challenge:

This will be hard to design real-time calculation of patients and doctors. This will be complex for design the database using more foreign keys. For solution divide the table into two parts with a different foreign key.

Human Challenge:

Humans will need to understand the data requirements and knowledge of database design in computer applications. For solving this challenge, this hospital will provide some training programs.

Technical Challenge:

Data entry and data design are hard to worthy. There are many types of maintenance problems faced by personal computers. For solving this approach, hardware and technical support can be available for solving maintenance issues.

 

Feasibility of Information system:

 

The feasibility solution every constraint will be satisfied by the set of value of a variable. There are some feasibility showing below—

Economic feasibility:

 These will analysis the project costs and revenues in an effort to determine whether or not it is logical and possible to complete. So these will initiated the cost and revenues of royal rundle hospital.

Operational Feasibility:

Operational feasibility is the measure of how well a proposed system solves the problems, how it satisfies the requirements identified in the requirements analysis phase of system development, and takes advantage of the opportunities identified during scope definition. Royal rundle hospital has many departments. Some department is handled by staff. This will train some member of staff who work all time and provide 24 hour facilities.

 

Reference:

Jiang, L. and Naumann, F., 2019. Holistic primary key and foreign key detection. Journal of Intelligent Information Systems, pp.1-23.

Khaire, A.V. and Mali, P.B., Towards Automated Generation of ER-Diagram using a Web Based Approach.

Cheng, L.C. and Chu, H.C., 2019. An innovative consensus map-embedded collaborative learning system for ER diagram learning: sequential analysis of students’ learning achievements. Interactive Learning Environments, 27(3), pp.410-425.

Darmanto, E., 2016. ANALISA PERBANDINGAN PEMODELAN BASIS DATA MENGGUNAKAN ER-DIAGRAM DAN EER-DIAGRAM PADA KASUS SISTEM ASISTENSI PERKULIAHAN PRAKTIKUM. Simetris: Jurnal Teknik Mesin, Elektro dan Ilmu Komputer, 7(1), pp.405-414.

Trang, N.T.T., 2018. Differences from the anchor modelingand an entity relationship database. International Journal of Advanced Research in Computer Engineering & Technology (IJARCET), 7(8).

Kilias, T., Löser, A., Gers, F.A., Koopmanschap, R., Zhang, Y. and Kersten, M., 2018. Idel: In-database entity linking with neural embeddings. arXiv preprint arXiv:1803.04884.