Database Design and Development

This report covers topics such as relational database management system, 3 tier client server application architecture, normalisation, physical data model using crow foot notation and SQL queries for adding dummy records and returning fixtures. It includes a detailed discussion on the database system for nursing home and provides a physical data model using crow foot notation. The report also includes SQL queries for adding dummy records and returning fixtures. Course code, course name and college/university are not mentioned.

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

denormalisation that helps in enhancing the specific queries also uses the referential integrity to
process the relationship types.
Crow foot notation, it is use for representing the relationship between the different entities.
Where the entities tables are connected with the different lines and other symbols that are offered
by this crow foot application. In this relationship line have the two features which mainly
explains the cardinality and the way of relationship. Where cardinality shows the total numbers
where the entity is linked with the other entity and modality or way refer as the minimum time
which the entity is connected with other entity. It represents entities in the boxes and with the
help of lines it shows the relationship (Repnikova and Loshkareva,2020). It is simpler notation so
that anyone can easily shows the relationship between the entities by simply using its notation.
13

TASK 3
1 SQL code for adding 5 suitable dummy records
15

Above solution can be evaluated as development of the table club where all ehtries have been
included. To find the solution for the footbal club database, firstly club table is creted by using
the SQl query. It uses the create table command to develop the table and then adds the values
like club id, club name, home team, away team and club details. It further adds the values that
has been shown in the above table. Above table has included the data as teams such as England,
ireland, scotland, evrton, liverpool etc. so that the further testing can be done efficiently (Yu
Yuan, and et.al., 2018). To add the dummy table insert query is selected as it is efficient and
provide a simpler execution. Thus for solving the error insert query has been implemented. In
this SQL databse table 5 entries have been included to show the every details. Where the dummy
records have been developed by inserting the values which is shown above. Dummy records in
SQL server are helpful in fixing the error. As the dummy data usually do not contains the actual
data but can be used as to reserve the space in which the actual data can be present nominally. As
to implement the SQL command is beneficial with this it offers the great speed, portability and
also provide the multiple data view approach. Thus above SQL queries also shown the result
which shows the team details where it involves the data for both home team and away team. So
that with the help of this dummy data front end developer can easily fix the error in tables by
testing.

2 SQL statements that returns the fixtures
This SQL statement has returns the table which is created as a dummy records to fix the eroors.
In this SQL statement or query language has been utilised to returns the fixtures. It also returns
the home team first and then away team in a alphabetical order. Thus SQl query utilise the order
by function to return the result in alphabetical order. As the order by function helps in providing
the data in ascending as well as in descending order. By default this function usually returns the
values in ascending order (Ma Zhang, and et.al., 2018). In this SQL query ASC keyword is used
to lay out the result in ascending order while to return the result in descending order then it
requires to write the DESC keyword. Therefore above query result has shown the list of the
home team and away team in a ascending order, where the home team comes first and then away
team comes. Data carries in both home team and away team alsso representd in a ascending
order as the team name which starts from the A comes first and so on. For using the order by
command it is also important to use the indexing as it helps in sorting or arranging data so that
the request which is made can give fast result. Order by command is good for smaller dataset as
17

it produce the result with accuracy when the data in the table are small, as it can affect the
performance when the tables are of large size.
3 SQL query
In this statement it is shown that the database system of the local football club accept the entries
of the players without their date of birth data. But the club’s insurance policy required this details
so that they can cover the medical coverage for any injuries which the player can gets during the
match. Thus for their policies it is important to have the date of birth entries as well so that
medical compensation can be given to the injured player. This SQL query statement have entered

the date of birth entry so that it will help the local club to manage the insurance policies for their
players (Goelman and Dietrich, 2018). Above query has evaluated the description about the
player table where the required entries have been made such like date of joining when the player
has join it also involves the date of left when the player has left the club. Thus it further adds the
Dob column where each players Dob has included so it will be easier for the club to provide the
insurance to the candidate who injured during the match.
4 SQL statement to search the player by the club name
As search function is the SQL query is required to performing the search operation as to search
out the particular details instead of whole details. Thus this SQL query is used to lay out the
player name by searching the club name. thus in this SQL query is executed where the details has
been given. Thus for searching the name club it gives the data in return as the player’s name.
Search command in the SQL is used to search the specific details as most of the time it is found
that by fetching the details it gives all information which can confuse the real query (Kraleva,
Kralev, and et.al., 2018). For an example is the user is looking for the name detail but in return
they are also getting the other information such as id, address and so on which can confuse the
actual detail. Thus with the help of the search command it can be easier to fetch the details, so
that the relevant output can be carried out. Thus above query command is giving the relevant
output as the player result by searching it for other details.
19

5 Altering the records for resolving the errors
It is found that many time altering the table gives many errors which imapact the system
performance. thus above query shows the Altering of the table to change the table name, as it
shows that the table name has changed and returns the name as abc. It uses the update command
to alter or update the details. As the update command is mainly used for modifying the values
sometimes it become hectic for the user to made changes. But with the help of the update
command user can easily alter the details as which are stored in the field or it is also beneficial in
updating the details when it is prersent in more than one field which can be stored in a single
table. Thus there are three type of the update command are found in the SQL server which are
DELETE, INSERT and UPDATE. Thus above SQL query has implemented the Update
command to alter the tournament table which changes its name. thus with the help of this
command local football club can easily made changes and to modify the details in their database
system, so that they can easily fetch the details whenever it requires.
CONCLUSION
From the above report it is concluded that having a database system helps in managing or
storing the data. With this business can easily retrieve the information. Designing of the database
provides the better arrangement of the data. Above report have illustrated the description about

the relational database management system, where the data is stored in a table form and by
performing Structure query language for easy retrieve. It also discuss the characteristics of the
RDBMS as how it is widely used system which gives the better data security as wells as consist
of simpler mechanism to install. It further describes the 3 tier architecture which describes the
application, data and presentation layer that can be optimised on separate platform. Also describe
the role of DBMS in architecture. Moving on further it explains the database system which
covers the data dictionary that are about having the table name with the possible attributes. It
also discuss the normalisation form which illustrates the 1,2 and 3 normal form. It finally
concluded with the discussion over the physical data model and crow foot notation. It also
discuss the SQL query which helps in providing the solution for the database system of local
club. It covers the different keywords as order by to give the result in appropriate manner, it also
describe the description about the query command which have been utilised.
21

REFERENCES
Books and journals
Fachada, N., 2018. Teaching database concepts to video game design and development students.
Markson, C., 2019. IS 331-451: Database Design, Management and Applications.
Chiusano, M., 2019. IS 331-101: Database Design, Management and Applications.
Liu, Y., 2021. Research on the integration of ideological and political elements in the course
“database technology and application”. In E3S Web of Conferences. (Vol. 245, p.
03038). EDP Sciences.
Soffian, N.S.M., and et.al., 2021. Development of Tourism Database Management System:
Creating ER Model.
Chao, W., 2020. Design and Analysis of Software Application Framework Based on Web and
Database Algorithm. International Journal of New Developments in Education. 2(4).
Repnikova, E.A. and Loshkareva, D.A., 2020. DATABASE SYSTEM DEVELOPMENT
PRINCIPLES FOR ORGANIZING CONFERENCE WORK. In X Всероссийский
фестиваль науки (pp. 1081-1084).
Ma, W., Zhang, and et.al., 2018. Design and establishment of quality model of fundamental
geographic information database. Int. Arch. Photogramm. Remote Sens. Spat. Inf.
Sci. 42(3).
Kraleva, R., Kralev, and et.al., 2018. Design and Analysis of a Relational Database for
Behavioral Experiments Data Processing. International Journal of Online
Engineering. 14(2).
Goelman, D. and Dietrich, S.W., 2018, February. A Visual introduction to conceptual database
design for all. In Proceedings of the 49th ACM Technical Symposium on Computer
Science Education. (pp. 320-325).
Yu, Z., Yuan, and et.al., 2018, May. A university fixed asset database information management
system based on internet of things. In 2018 2nd IEEE advanced information management,
communicates, electronic and automation control conference. (IMCEC) (pp. 2488-2491).
IEEE.
Chen, Huang, and et.al., 2018, August. Online virtual experiment teaching platform for database
technology and application. In 2018 13th International Conference on Computer Science
& Education. (ICCSE). (pp. 1-5). IEEE.

Davis, K.C., 2018, October. Teaching physical database design. In International Conference on
Conceptual Modeling. (pp. 165-175). Springer, Cham.
Rimsha, A.S. and Rimsha, K.S., 2019, September. Database Design for Threat Modeling and
Risk Assessment Tool of Automated Control Systems. In 2019 International Russian
Automation Conference. (RusAutoCon). (pp. 1-6).
Cai, X., Shen,and et.al, 2020, February. Application Research of Project-based Teaching Based
on Working Process in Oracle Database Teaching. In IOP Conference Series: Materials
Science and Engineering. (Vol. 750, No. 1, p. 012003). IOP Publishing.
23