Report on Database Design and Development for Nursing Home System

This report provides a detailed exploration of database design and development principles, focusing on a practical application within a nursing home context. It begins by defining Relational Database Management Systems (RDBMS) and explaining the 3-tier client-server architecture, highlighting the role of DBMS within this structure. The report then delves into the design of a database for a nursing home, covering essential aspects such as table structures, relationships, and data normalization techniques, including 1NF, 2NF, and 3NF. A physical data model using crow's foot notation is presented to visually represent the database schema. Furthermore, the report includes SQL code examples for adding dummy records, retrieving specific data, and altering records to resolve errors. The SQL queries demonstrate practical applications of database manipulation, including ordering results and updating records. The overall aim is to illustrate the process of designing and implementing a relational database system for managing patient information and related operations within a nursing home environment. Desklib is a valuable resource for students seeking similar solved assignments and study tools.

DATABASE DESIGN AND
DEVELOPMENT
1

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................3
MAIN BODY...................................................................................................................................4
TASK 1............................................................................................................................................4
A) Relational database management system (RDBMS)..............................................................4
B) 3 Tier client server application architecture and role of DBMS in this architecture..............5
TASK 2............................................................................................................................................6
A Database for nursing Home.....................................................................................................6
Normalisation...............................................................................................................................9
B) Physical data model using crow foot notation......................................................................12
TASK 3..........................................................................................................................................15
1 SQL code for adding 5 suitable dummy records....................................................................15
2 SQL statements that returns the fixtures.................................................................................17
3 SQL query...............................................................................................................................18
4 SQL statement to search the player by the club name............................................................19
5 Altering the records for resolving the errors...........................................................................20
CONCLUSION..............................................................................................................................20
REFERENCES..............................................................................................................................22

INTRODUCTION
Database system refer as the organised system where all the data is arranged in a order so
that user can easily access the information without any problem. It is mainly managed by the
database management system; every business has their own DBMS to store their data so that
they can used for laying out their business functionalities (Fachada, 2018). Database design is the
arrangement of the data on the basis of database model, in which the designer examine the
processes as how the data is stored and can be interrelated with the other elements. Thus with
this information designer initiates to fits the data into the database model which is mainly
manage by the data base management system. In relational data base system where the data is
stored in a table format so that data can be managed and also easily accessed from tables.
Following report will cover the tasks in which it lay out the description about the RDBMS and 3
tier client server application architecture. Report will also discuss the normal form which
involves 1 NF, 2 NF and 3 NF. It also covers the description about the database system of
nursing and physical model diagram by using crow’s foot notation. It also discusses the SQL
queries along with its description, as SQL queries also involves the keywords such as order by
and update for making changes accordingly.
3

MAIN BODY
TASK 1
A) Relational database management system (RDBMS)
It is the DBMS that stores the data in a table based structure that connects the elements with each
other to show the relationship. RDBMS includes the functions that manages the security,
reliability and accuracy or integrity of the data which is been stored. It has following elements
which are tables that involves the rows and columns where the data is stored. In this the data is
mainly stored in a tuple which are rows (Davis, 2018). It is most commonly used database
system which is having number of tables and all have primary key. Every table have its unique
primary key, but there might be more than one foreign key that is used to develop the
relationship between tables.
Characteristics of the RDBMS system
 Main feature of the RDBMS as it represents the data in logical and in organised form. In
every table corresponds to independent entity. Data must be presented in table form
where it stores in tuples.
 Data in the table are specified with the table name and column name which also include
the primary key row.
 It also helps in providing the feature to create the indexes for rapid retrieval of the data.
 It also offers the creation of the virtual table where the sensitive data can be stocked and
on which the simplified queries can be performed (Rimsha and Rimsha, 2019).
 Its main characteristics as it provides the multi user accessibility which can be easily
controlled or managed by the individual user.
 RDBMS provides the better security to the data, it secures the data from any misuse.
 Also RDBMS is simple database to begin with it do not consists of the complex feature to
setup.
Its examples are MySQL, oracle, SQL server and IBM. Most widely used RDBMS are
MySQL and oracle are widely used RDBMS because of their properties which can be easily
manage by the user. They also offer the reliability and accuracy.

B) 3 Tier client server application architecture and role of DBMS in this architecture
3 Tier architecture refer as the client side architecture where the data interface, data access
and other logical functional processes are stocked and managed by evaluating on individual
platform. In this tier can also be termed as layers. In this application is mainly spread to the three
independent computing layers. In this it involves the three layers which are as follows:
Presentation layer: It works as to sends the information to the browser in the hypertext mark-up
language (HTML) and also in cascading style sheet(CSS) and java script form. It also leverages
the other frameworks which are angular and react etc (Chen, Huang, and et.al., 2018).
Application layer: It utilise the application server where the business logic are processed for the
application it can be written in the programming language which can be C, C++ and python or
etc.
Database layer: It is the data base management system which gives the better access to the
application data. It can be a MySQL, SQL server. Its role in 3 tier is to create and manage the
functional logic processes also provide the better facilities for the data storage. As the changes
can be done on three layers by using separate platform, thus it do not affects the elements of
different layers.
This architecture will provide the better facility for updating the technology of individual
tier and its main benefit is it do not affect the functionality of the other tier. Also with this users
can easily work on different tiers according to their knowledge (Markson, 2019). Main benefit of
3 tier is that it offers the reliability and better independence to lay out the services.
5

Figure 1 Three tier architecture
TASK 2
A Database for nursing Home
Database for the nursing involves the different tables where the data is stored so that it can offer
better retrieval of the data. It involves the tables of prescriber, doctor, patient, nurse, room, block,
stay and medication. Each table carries the different entities and they are connected with the
foreign key (Chiusano, 2019). With the help of this they can easily manage the treatment for
their patient with the DBMS they can easily store the details of the patient’s activity.

7

Normalisation
Normalisation is the process in order to reduce the redundancy, anomalies and inaccuracy from
the data base. It also aims to eliminate the undesirable elements from the function as insertion,
update. It also helps in splitting the table into the small tables and it associates it with the
relationship (Liu, 2022). It is of various types which are 1st normal form, 2nd normal form, 3rd
normal form.
1NF: Relation is said to be in first normal form when it has the atomic values. As in this it is
important that the attribute of the table do not hold the various values, there should be only single
value. It does not involve the composite values.
Doc_id Doc_Name Doc_mobileNO
1 John 12546
56425
2 Harry 12785
3 Deana 36442
9

Doc_id Doc_mobileNO
1 12546
1 56425
2 12785
3 36442
2NF: When the relation follows the 1st normal form then it is also considering as in 2nd normal
form. In this the non key attributes are completely rely on the primary key (Cai Shen,and et.al,
2022). It discards the partial dependencies. It helps in managing the data efficiently also keeps
the data consistent also improvise the organisation of the data into the database management
system.
Nurse_id Nurse age Duty shift
101 45 temporary
102 30 day
102 30 night
103 24 day
103 24 night
Nurse_id Nurse age
101 45
102 30
103 24

Nurse_id Duty shift
101 temp
102 Day
102 Night
103 Day
103 night
3NF: If the relation is in 2NF then it is said to be in 3rd normal form it does not involves the
transitive dependency (partial). It is mainly used for decreasing the data delicacy and also used
for accomplishing the data integrity.
Main benefit of the normalisation as it makes the database design more flexible and also
gives the better and high security which also aims to improvise the functionality of the database
management system (Soffian and et.al., 2021).
Patient_id Hospital_name Room_floor
201 Jyc First
202 Jyc First
203 kpf Fourth
204 kpf Fourth
Patient_id Hospital_name
201 Jyc
202 Jyc
203 Kpf
204 kpf
11

Hospital name Room_floor
Jyc first
Kpf fourth
Data dictionary
It refers as to collects the data attributes, elements that are being used in the data base
system. It gives the clear information as what the system is having so that user can easily
understand what are the data that are being stored in the system.
Entity Attribute
Doctor Doc_id, doc_name, doc_address, doc_mobileNo
Patient Ssn_id pat_name, pat_address, pat_phoneNo
Nurse Nurse_id, nurse_name, nurse_address, nurse_gender, nurse_mobileNo
Medication Medi_id, medi_name, medi_descr
Prescriber Prsc_id, dose, date
Room Room_id, room_no, block floor, block_code
Block Block_id Block_floor, block_code
Stay Stay_id, patient, room_id, start-time, end-time
B) Physical data model using crow foot notation
Physical data model refers as the specific diagram which represents the relational data elements
which are table, tuples, columns, primary and the foreign key which connects the tables which
other. It mainly utilised to develop the DDL (data descriptive language) statements that can be
used in the database server (Chao, 2020). It actually describes how the database can be
developed. It represents the structure of the database table as the table name, column name, data
type, primary and foreign key, also describe the relationship or associativity between the tables.
Well-developed physical data model gives the better quality in data also helps in processing and
better maintenance. It provides the visualise structure of the database, it mainly supports the