Introduction to Databases: Database Design and Development

This report covers the designing of the database for Nursing home, which also covers the data dictionary. It describes the formation of physical diagram by using the crow foot database notation. Report will further lay out the normalization that covers 1st , 2nd and 3rd normal form.

INTRODUCTION TO
DATABASES DATABASE
DESIGN AND
DEVELOPMENT

TABLE OF CONTENT
INTRODUCTION...........................................................................................................................3
MAIN BODY..................................................................................................................................3
TASK 1............................................................................................................................................3
A) RDBMS..................................................................................................................................3
B) 3 Tier client server application architecture...........................................................................4
TASK 2............................................................................................................................................7
A) Database for nursing home.....................................................................................................7
B) Physical diagram using crow foot database notation............................................................12
CONCLUSION..............................................................................................................................14
REFERENCES..............................................................................................................................15

INTRODUCTION
Database refer as systematic collection of arranged information, which are gathered
electronically in a system. Database management system allows the user to perform several
operations as to create, update, delete data in system. An accurately database design gives the up
to date information and easy access to information. It involves the categorization of data and help
to identify the interrelationship (Kamatkar and et.al 2018). Database design is the whole process
of developing a comprehensive data model, it entails the all required logical and physical
configuration that are needed for developing design. Through which the firm will easily analyse
what data need to be stored on their system and which need to be modified or delete. It will be
beneficial in retrieving the data in short time. This report will lay out the designing of the
database for Nursing home, which also covers the data dictionary. It describes the formation of
physical diagram by using the crow foot database notation. Report will further lay out the
normalization that covers 1st , 2nd and 3rd normal form.
MAIN BODY
TASK 1
A) RDBMS
RDBMS stands for Relational database management system refer as program which
enable the user to perform several operations which are update, create, delete. It is the DBMS

that collect and retrieve the information which comes into the tabular format. It is considered as
the subset of the DBMS, it maintains the data integrity by making system efficient. This system
stores the data in tables form where the information is present in row or column (Ivezic and et.al
2019). These tables are used to create the SQL code so that, user can easily access or retrieve the
data.
Characteristics of RDBMS
 Data stored in table and every row called as record or tuple, collection of the tuple termed
as table cardinality.
 Every column known as the attribute field, that stores the information about every entity.
RDBMS follow some rules which implies that no two columns are identical as to avoid
the duplicate data it uses the candidate key which is used to analyse the every record
uniquely.
My SQL, Oracle, IBM DB2 are the common RDBMS that are using now a days. MYSQL is a
open source RDBMS, it mainly concentrates on robustness, stability, it is widely used database
system in a web applications. MYSQL is beneficial in providing the high throughput with a few
latency. Other than this Oracle is also a popular RDBMS, in this data is considered as the unit, it
helps in solving the information management issues. Oracle also known as enterprise grid
system, it is cost-efficient as well as use singular database for every data type and helps in
creating the hybrid cloud environment.
B) 3 Tier client server application architecture
It is the client server architecture in which the modules are treated independently which
are data access, processor logic and user interface. In this tier known as the layer. Most often
every module are developed and maintained on different or separate platform. Every tier are data
layer, application layer and presentation layer (Gupta, Saxena and Gill, 2018). Every layer plays
their crucial role as data layer collects the information and application layer manage all logical
process while presentation layer work as a graphical user interface the helps in communication
with other two layers. main benefit of this system as every tier can created separately so that
operations like update, create can done separately, so changes in other cannot affect the
efficiency of other layer.

As for the DBMS role designing of system depends on its architecture, that can be
centralised. in this DBMS can be consider as either 1 tier or multiple tier, for the single tier
system treated as a single unit where the user can easily access the information or if there is any
changes then it will be directly done by the system. In database tier information received by the
application layer are processed it can be a MYSQL, oracle RDBMS. It uses the query processed
language that can be use in accessing or updating the useful information.

Illustration 1: 3 tier client server
architecture

TASK 2
A) Database for nursing home
Database system enables to store the information in such an organised manner, so that user can
easily lay out the needful operation. Here below is the database for the nursing home, which also
covers the data dictionary.

Data dictionary involves the Metdata of the database, it is important as it stored all the
information at one location so that data can be retrieved in short time. It is collection of data
which are attributes, data definition which are captured in system.
Data dictionary
Entities Attributes
Doctor doc_id (PK), doc_name doc_gender,
doc_address, doc_phoneNo, ssn_id(FK),
pcs_id(FK)

Patient ssn_id(Pk), pat_name, pat_age, pat_gender,
pat_address, pcs_id(FK)
Nurse nurse_id(Pk), nurse_name, nurse_gender,
nurse_phoneNo, ssn_id
Medication medi_id, medi_name, medi_descr
Prescriber pcs_id (PK), dose, date, medi_id(FK),
doc_id(Fk)
Normalisation:
It is the efficient process of eliminating the redundancy from the system. redundancy in
system cause many problem as it affects the insert, delete and update database operation, so it
tries to reduce the issue for easy data retrieval. there are some normalisation form which are as
follows:
1st normal form: If there is a composite valued attribute than it violates the 1NF arrangement, a
system can be called as in 1NF when each attribute in the system have one valued attribute
(Prettyman, 2020). It means that table does not consist of the multiple values otherwise relation
does not in a formal form.
1NF
ssn_id Pat_name Pat_age Pat_phoneNo
1 abc 22 1225425
1254564
2 def 25 3256485
3 ghi 36 1232546

2nd normal form: A relation said to be 2 NF when it is in 1Normal form. In this there should be
no partial dependency means no attribute that are piece of candidate key.
Doc_id Doc_duty Doc-age
101 diagnose 25
101 educate 25
102 treat 23
103 tests 35

3rd normal form: This normal form is used to eliminate the transitive redundancy, it lowers the
duplication in data also helps in reducing the data anomalies. it mainly ensures the referential
integrity. Below is the example of 3rd normal form that eliminates the dependencies.
Pat_id Pat_name Ssn-code Pat_gender Pat_phone
201 john 1 male 1254
202 arry 2 female 6352
203 wilmy 3 male 2587

B) Physical diagram using crow foot database notation
Physical representation shows how data model is developing in the database system, it shows all
structure which involve the details regarding every tuple that contains the records in table. it
shows the data types keys as primary and foreign key in the entity. It mainly used for creating the
DDL (data definition language) statement that are delivered to the server. With this user can
easily proceed to calculate the storage estimates and can also involve the allocation details that
are present in database system (Alseelawi and et.al 2020). Physical model shows the data in a
data layer as provide the table specifications, so that it can be easily understood. This data model
is relying on the RDBMS it also varies according to this database system. For an example if the
system has different datatype in both oracle and SQL server in such the data diagram appears to
be different instead of carrying same details, there might changes in primary and foreign key.
Crow foot notation usually represent the graphical notation that shows the relationship side, it is
used to design the entity relationship diagram. It helps in arranging the data so that user can
easily understand the database structure (Arini,Arifudi and Aris,2019). Crow foot uses the
unique symbols that present the database elements. It follows some rules which are- every entity
is present in a boxes and relationship are shown by lines which represents the cardinality. This
notation is also used because of its simplicity; user can easily use this notation by using Visio
software. Below is the physical diagram which is design using the crow foot symbols, it entails
the three entities which are song, artist and playlist (Hidalgo Lorenzo, 2020). diagram also
shows the datatype for every attribute, where song_id, Play_id and Art-id are the primary key
and they are connected with their respective foreign key table.

Figure 1Physical diagram using Crow foot symbols
CONCLUSION
From the above report it is concluded that designing of Database layout many processes,
Having a database allows to organised the information in a systematic manner, so it could
retrieved anytime irrespective of other factors. Above report illustrated the description about the
database system, it covered the RDBMS topic, which described as how it plays an crucial role in

performing suitable operations. it also carries out the characteristics of RDBMS and its uses in
modern era which covers its example that are oracle, MYSQL server which are popularly used
RDBMS. Moving on further report described the 3 tier client server, that evaluate its three layers
working, along with this it also described how DBMS plays an role in this system. it further
details the database system for nursing which covers the normalisation steps and data dictionary.
it lastly concluded with the physical diagram by using the crow foot notations.

REFERENCES
Books and Journals
Alseelawi, N.S. and et.al 2020. Design and implementation of an e-learning platform using N-
TIER architecture.
Arini, F.Y., Arifudin, R. and Aris, M., 2019, October. Applied structured database in a small
project. In Journal of Physics: Conference Series (Vol. 1321, No. 3, p. 032130). IOP
Publishing.
Gupta, A., Saxena, M. and Gill, R., 2018, April. Performance Analysis of RDBMS and Hadoop
Components with their File Formats for the development of Recommender Systems.
In 2018 3rd International Conference for Convergence in Technology (I2CT) (pp. 1-6).
IEEE.
Hidalgo Lorenzo, S., 2021. Performance Evaluation in SQL Server. Computer Science Program;.
Ivezic, N. and et.al 2019. A Two-Tiered Database Design Based on Core Components
Methodology.
Kamatkar, S.J. and et.al 2018, June. Database performance tuning and query optimization.
In International Conference on Data Mining and Big Data (pp. 3-11). Springer, Cham.
Prettyman, S., 2020. Interfaces, Platforms, and Three-Tier Programming. In Learn PHP 8 (pp.
47-87). Apress, Berkeley, CA.