Data Analysis and Design Report: Relational Database for FTHC

This report provides a detailed analysis of data analysis and database design principles, focusing on the case of Fast Track Health Centre (FTHC). It begins with a critical comparison of different data models (hierarchical, network, and relational), discussing their data structures, manipulation, and integrity aspects. The report then explores the benefits and limitations of database technologies compared to traditional file systems, highlighting the importance of reducing data redundancy. Different approaches to database design, including top-down and bottom-up methods, are examined, along with the use of data flow diagrams and entity relationship diagrams. The core of the report involves designing a relational database system to meet FTHC's requirements, including conceptual, logical, and physical database designs, normalization, and the use of tools and techniques. The report also covers the building of a relational database system, implementation of query languages, and a critical evaluation of query tools. Finally, the report reviews and tests the relational database system, providing documentation, user guides, verification, validation, and control mechanisms, to improve the present system.

Data analysis and design

Table of Contents
INTRODUCTION...........................................................................................................................1
TASK 1............................................................................................................................................1
1.1 Critical comparison between different data models and schemas.........................................1
1.2 Critical discussion of benefits and limitations of different database technologies................3
1.3 Analyze different approaches to database design..................................................................4
1.4 Explain top-down and bottom-up approaches.......................................................................6
TASK 2............................................................................................................................................7
2.1 Design a relational database system to meet a given requirement........................................7
2.2 Building a relational database system based on a prepared design.......................................4
2.3 A range of database tools and techniques to enhance the user interface...............................4
2.4 Crow’s foot notation..............................................................................................................4
2.5 Critical reflection...................................................................................................................6
TASK 3............................................................................................................................................6
3.1 Benefits of using manipulation and query tools....................................................................6
3.2 Implementation of a query language into the relational database system.............................7
3.3 Critically evaluation of query tools used in data extraction..................................................8
3.4 Advantages of query in database...........................................................................................8
TASK 4............................................................................................................................................8
4.1 Review and test a relational database system........................................................................8
4.2 Documentation to support the implementation and testing of a relational database.............9
4.3 User documentation for a developed relational database system..........................................9
4.4 Explain the ways through which verification and validation has been addressed.................9

4.5 Control mechanisms..............................................................................................................9
4.6 Testing and implementation of database system.................................................................10
4.7 Appropriate decisions to improve present system...............................................................10
CONCLUSION..............................................................................................................................11
REFERENCES..............................................................................................................................12
3

List of figures
Figure 1: File management system (data redundancy)....................................................................7
Figure 2: Database management (data redundancy)........................................................................7
Figure 3: Data flow diagram............................................................................................................8
Figure 4: Entity relationship diagram..............................................................................................9
Figure 5: Top-down approach.........................................................................................................9
Figure 6: Bottom-up approach.......................................................................................................10
Figure 7: 0-Level DFD..................................................................................................................13
Figure 8: 1-Level diagram.............................................................................................................13
Figure 9: Patient enquiry form.......................................................................................................14
Figure 10: Testing and implementation of database......................................................................20
List of tables
Table 1: Database schema..............................................................................................................6
Table 2: Conceptual data model....................................................................................................10
Table 3: Logical database design...................................................................................................11
Table 4: Crow’s Foot notation.......................................................................................................15
Table 5: Speciliast_details table....................................................................................................17
Table 6: Room_details...................................................................................................................17
Table 7: Patient_details table.........................................................................................................17
Table 8: Recpt and FTHC_details table.........................................................................................17
Table 9: Verification and Validation table....................................................................................19

INTRODUCTION
Data analysis and design is an important problem solving technique and it allows
database users to maintain records or details without any redundancy. As per the present case
scenario, Fast Track Health Centre (FTHC) has been taken into consideration which provides
quality of services to its patients and operated by health care professionals. Furthermore, report
provides details about different data models and approaches of database design with the help of
discussing some real world’s problems in a significant manner. In addition, relationship database
design and database system on the base of given case study have been discussed.
TASK 1
1.1 Critical comparison between different data models and schemas
Different data models can be used for the purpose of fetching data from database and
presenting it to users.. On the basis of data structures, data manipulation and data integrity,
critical comparison between data models can be done. In Hierarchal model, data presents to user
in hierarchal structure or a sort of inverted tree. Data structure: It has records, nodes and fields. In hierarchal structure, data is stored in
the form of family tree and nodes are arranged in the parent and child data segments. In
addition, one to many or one to one relationship exists in this model. On the other side, in
network model, multiple branches are emanating from one or more nodes and it can be
shown by an arrangement of the blocks (Wasson, 2006). In order to represent the
database of the organization, blocks, areas and arrows have been used. It is based on the
parent child relationship and supports many to many relationships. In relationship
database model, each record can have multiple parents and multiple child records
(Jonathan, 2008). Data manipulation: Independent stand alone query interface is not provided in the
hierarchical model. While in network model, CODASYL (Conference on Data Systems
Languages) is employed for the purpose of fetching information a. On the other side, in
relational database, SQL language and other query languages are used to manipulate data
stored in the database (Coronel and Morris, 2014). On the basis of critical comparison
between these data models, it can be said that retrieve algorithms are complex and
symmetric in the case of both network and hierarchal models.
1

 Data integrity: The main restriction which is associated with hierarchal model is that
database user cannot insert the information of a child who does not have any parent. On
the other hand, network model and relationship models are not suffered from any
insertion anomaly (Ramakrishnan, 2007). Deletion related constraints which include
deletion of parent results in deletion of child records is also attached with hierarchal
model but other network models not suffered from any kind of delete anomalies.
Table 1: Database schema
Factors Conceptual schema Logical schema Physical schema
Definition It represents that how
the business world sees
information. It is made
out of actually what
database user has plan.
It is concerned with the
views of real world
(Westman, 2001).
In this schema,
generalized formal
structure is used in which
the rules of information
science are defined.
Real time execution of data and
features are defined in this
schema.
Complexity
level
Less complex Moderate High complex (Ranjit, 2006)
Features Table, table space, view
and packages
Fully normalized entities,
logical domains and data
types.
Index, key, alternate key and
referential integrity , views and
constraints
Structure
2

1.2 Critical discussion of benefits and limitations of different database technologies
In earlier, most of the enterprises were used traditionalfile system for storing and
managing information. Less complexity and high security related advantages are associated with
the use of file system. However, data redundancy issue, high access time and data integrity
related challenges are also associated with the use of traditional file system. For example, in file
system, each application has its own private file and it leads to considerable redundancy (Ault,
2002.).By using centralized database this type of problem can be reduced but it can be possible
by reducing the all redundancy. From the below figure, it is clear that all information of college
is maintained in separate files or applications which takes high storage in database.
Figure 1: File management system (data redundancy)
(Source: Régnière and et.al., 2012)
On the other side, controlling redundancy, enforcing integrity, avoiding inconsistency and
restricting un-authorization access are the main benefits of using thedatabase system as compare
to file management system (Woodward, 2013). However, maturity, administration, expertise,
support and analytics related issues are the major constraints of database management system.
3

Figure 2: Database management (data redundancy)
(Source: Régnière and et.al., 2012)
From the above database management figure, it is clear that Name, Class, Father_Name,
Address and other records are stored separately. By joining of relations, information can be
retrieved easily and redundancy can be removed completely (Eriksson and et.al., 2013). From the
comparison of traditional and database system, it can be said that redundancy of data affects
greatly to the consistency of data. In such kind of situation, proper implementation of database is
beneficial for organizations and database users to reduce the chances of issues related to data
redundancy and data independence in a proper manner.
4

1.3 Analyze different approaches to database designFor developing any database, generally two
types of approaches including top-down method and the bottom-up are employed. Top-
down begins with the general as well as moves to the specific and it is used to create a new
database. However, a detailed understanding of the system and proper understanding
between the analyst and end-users are needed to produce satisfactory results (Ritchie and
et.al., 2013). On the other hand, bottom-up approach begins with specific details and
moves up to the general. In addition, the analyst will work backwards through the system
and information stored in the database. In the context of describing the boundaries of
system, data flow or Unified modeling diagram (UML) is framed. Data flow diagram
provides a detailed representation of system components to the technical and nontechnical
audiences in an effective manner. With the help of this diagram, process, data storage and
external entities are presented (Database Design Strategies, n.d.).
Figure 3: Data flow diagram
(Source: Ritchie and et.al., 2013)
Entity relationship diagram is used to present store data layout at higher level of
abstraction and it mainly concentrates on relationship between data stores. On the other side,
DFD emphasizes on the functions that system performs as well as process specification.
5

Figure 4: Entity relationship diagram
(Source: Ritchie and et.al., 2013)
The above entity relationship diagram shows the relationship between the sales, product and
warehouse activities. Customers place an order for products, then sales department collects
products from warehouse and serve to customers.
1.4 Explain top-down and bottom-up approaches
At a logical level, objects are defined and applied at physical database design. In this
approach, analyst will work backwards through the system and attributes are identified first then
group them to form entities. A payroll system in which information about human capital,
payment, dates of the pay period and other information are stored that can be considered as an
example of top-down approach.
Figure 5: Top-down approach
6

Figure 6: Bottom-up approach
(Source: Åström and Wittenmark, 2013)
In real time, it is used for capturing a database from one physical database to another and
minimizes the application coding changes. For example, doctors and physicians are used bottom
approach in which specific symptoms of disease is identified and then, infer the general disease
causes (Ogden, 2012). As per the given case scenario, bottom database design approach is used
because it allows database users to maintain the records of patients and verified documents
which are necessary for the registration process.
TASK 2
2.1 Design a relational database system to meet a given requirement
In order to meet requirement of Fast Track Health Centre (FTHC) database, relational
database system can be used. In this regard, logical and physical database design are prepared
that can be enlisted as follow.
Table 2: Conceptual data model
7
Specialist
detail
Administration department
Room booking at FTHS

Table 3: Logical database design
Physical database design: With the help of entity relationship diagram and data flow diagram,
physical design of database can be described.
1
Specialist details
D_Id (PK)
Full Name
Marital Status
Sex
Date of birth
Contact address
Contact number
Emergency contact details
Valid documents of address proof
Highest educations
Specialization area
Training certificates
Evidence of education and
specialization area
Photographs
Valid documents of address proof
Pay check-up charge
Room booking at FTHS
Booking_ID (PK)
D_Id (FK)
Booking time
Price
Availability
Administration department
Membership number (PK)
D_Id (FK)
Confirm the appointment
Checking the availability
Date
Time
Specialist
Collect amount
Hand over the amount to specialist

Entity Relationship Diagram
m m
1
n m
2
Patient
P_Id F-name
M_stat
us
DOB
Contact_
Add
Contact
Membershi
p
Receptionist and FTHC
Inquiry
Status Date S_Avail A_Dat
e
SA_tim
e
Patient
appointment
Administration department
Specialists
Fees
S_Id
Full-
name
Specialization
Photo
Exp_dat
Certificat
e
P_Id Reg-num
Consultatio
n
Valid_Doc
Room
confirm
Room booking
R_Avail
R_Date
R_time
Price
Collect money Hand over
money

Data flow diagram
Figure 7: 0-Level DFD
Current status Specialist Data
Pay price Amount
Verify data FTHC
Availability and time
Phone Call
Patient
Figure 8: 1-Level diagram
Data dictionary: It contains information about data definitions and entity relationship. Batch
process is required in passive data dictionary. On the other side, DBMS updates automatically
active data dictionary.
Normalization: In the context of eliminating redundancy, organizing data and decomposing
tables into sub-tables, this process is considered (Hsiao, 2014). By applying normalization rules,
Fast Track Health Centre (FTHC) will be able to organize information into tables and rows in a
systematic manner.
3
Specialist room booking
Patient appointment
Administration
department
Specialist room booking
1.0 Verify
informatio
n
2.0 Room
booking for
consultation
3.0Receptionist
Patient appointment

2.2 Building a relational database system based on a prepared design
Relationship database design can be applied by using entity relationship diagram and
structured query language. From the given case situation, it is clear that different tables including
patient, specialist, room information and receptionist are employed together to reduce the
chances of ambiguity (Régnière and et.al., 2012). With the help of considering ER model,
relationship database system can be developed.
2.3 A range of database tools and techniques to enhance the user interface
In order to increase the user interface, it is essential for front-end developer or
programmer to consider different user interface factors such as easy to use, reliability and
consistency (Eriksson and et.al., 2013). By applying normalization process, data redundancy
related issues can be solved as well as consistency of patients and room booking related
information can be enhanced.
Member ID Auto generated
Doctor_name
Specialist area
Membership
Availability
Date and time
Contact number
Figure 9: Patient enquiry form
From the above figure menu driven or button base design, it is cleared that patient can
search information about specialist doctors on the basis of their availability and time. In addition,
SQL query tool can be employed in order to maintain information about room booking and
specialist availability in a proper manner (Ritchie and et.al., 2013).
2.4 Crow’s foot notation
By applying Crow’s foot notation, entity relationship diagram can be described in an
appropriate manner. In order to represent relationship between entities, diamond symbol is used
4
Submit

(Ault, 2002). With the help of ERwin tool and cardinality terms Crow Foot notation for ER
diagram can be explored.
IE notations
Table 4: Crow’s Foot notation
Rectangular In order to represent entity, rectangular box is used.
Attributes For the purpose of containing detail information,
attribute is employed.
One to one relationship In the context of representing relationship between
two entities, One to One relationship can be used.
Many to many Relationships In the ER diagram, it is clear that patient is
associated with several instance of receptionist and
5
Member_Id
Full name
Specialization
Membership
Education
Certificate
Room details
Room _num (PK)
Member_Id (FK)
Availability
Date
Time

FTHC (Linoff, 2008).
Primary key and foreign key Member_Id works as primary key in specialist table
and foreign key in room detail table.
2.5 Critical reflection
On the basis of critical comparison of researcher own work and outcome produced by
database technologies, it can be said that proper disclose information about entity relationship
diagram and data flow diagram are the major strengths of researcher's work. On the other side,
absence of practical implementation of Crow’s Foot notation and structured query language are
the major constraints associated with present work (Linoff, 2008). Moreover, the current design
is able to present the database design of Fast Track Health Centre (FTHC) in the form of entities,
attributes and relationship between different entities by using arrow and cardinality concepts. In
addition, several concepts regarding to database analysis and design which include top-down and
bottom-up approach, normalization and database design are defined in a systematic manner.
However, by using SQL database and UML modeling tools, weaknesses of practical
implementation of database design can be reduced.
TASK 3
3.1 Benefits of using manipulation and query tools
Data manipulation language can be used for the purpose of retrieving, updating and
deleting the records of database. Selecting, inserting, deleting and updating records are the main
data manipulation tools. Record can insert into database by using "Insert command”. On the
other hand, update command can be employed to modify records of database (Hsiao, 2014).
Select * from Room_details
Insert into Room_details values (105,'Yes', 501, 4008534, ‘15/01/2015')
Delete from Room_details where Room_num = 105;
Update Room_details
Set= Availability
6

Where Room_num= 105
Different formulas and functions such as Now (to return current availability of room date and
time) and GETDATE (return current date and time) and aggregate functions like SUM and AVG
can also be used to execute database operation in a proper manner.
3.2 Implementation of a query language into the relational database system
With help of CREATE command, table in SQL database can be prepared.
Table 5: Speciliast_details table
Create table Speciliast_details (S_ID int primary key, Full_name varchar(50), Marital status varchar(3),
Sex (4), DOB date, Contact address varchar(100), Contact_num bigint, EContact_detail bigint, valid
documet varchar (100), Mobile bigint, education (30), Specialization_area varchar(50), Training
certificates varchar(50), Pay check-up charge int)
Insert into Speciliast_details values (105,'Rocky Denam', 'Single', ‘Male’, ‘15/01/1975’, ‘Bermigam near
loard stadium’, 400856428, ‘Passport and License’, 98256894231, ‘MBBS’, ‘Physician’, ‘NHS certified’,
£500 )
Table 6: Room_details
Create table Room_details (Room_num int primary key, S_ID foreign key, Availability varchar (4),
A_Dat date, Time varchar (30))
Insert into Room_details values (501,105, ‘Yes’, 15/01/1975’, ‘Morning session’)
Table 7: Patient_details table
Create table patient_details (P_ID int primary key, S_ID foreign key, F_name varchar (50), DOB date,
Contact_num bigint, Contact_Add varchar (100))
Insert into patient_details values (201,105, ‘John’, 18/05/1978’, 98256894231, ‘New South Wales)
Table 8: Recpt and FTHC_details table
Create table Recpt and FTHC_details (R_ID int primary key, S_ID foreign key, A_date date (50), Status
varchar (30), Collect_money varchar (30), handover_money varchar (30))
7

Insert into Recpt and FTHC_details values (801,105, 18/04/2015’, Available, ‘yes’, ‘yes’)
3.3 Critically evaluation of query tools used in data extraction
For retrieving the records from patient table, select query can be used. Effective use of data
extraction query by using SQL helps in retrieving data from the health care database. In
order to build and input queries to database, different types of query tools such as SQL, T-
SQL and PL/SQL can be employed. For the purpose of normal queries from database, SQL
tool has been used (Åström and Wittenmark, 2013). However, PL/SQL is a combination of
programming language and SQL. It is used to enhance the capabilities of structured query
language. By using “Select Command”, specialist will be able to gain knowledge regarding
room booking and patient can see records of specialist availability, time and date in a
systematic manner. Data manipulation language is used in the present case can be enlisted
as follow.
Select * from patient_details
3.4 Advantages of query in database
This task has been covered in PPT.
TASK 4
4.1 Review and test a relational database system
For the purpose of testing relational database, user can use SQL and other query
languages. On the basis of quality codes written in the query and meeting functional
requirements, performance of relational database can be checked. In order to test the validity of
database, regression and agile testing methodologies can be employed (Gelman and et.al., 2014).
Performance of RDMS can be measured, by checking the basic structures and database queries
which include tins entries, records updates and deletes..
4.2 Documentation to support the implementation and testing of a relational database
By preparing documentation, information about the usability of database functions and
tables can be mentioned. User manual is prepared to aware database users about the use of
technology and resources as well as make them familiar with query language. Besides that,
SQL tutorials and manuals have provided to specialists so that they can check room availability
8

and book that in a proper manner (Westman, 2001). Training can be provided to database users
in order to understand the system and get the proper outcome by applying query tools.
4.3 User documentation for a developed relational database system
In the context of aware database users about basic database terminology and itsr
execution, user documentation has been prepared. In this regard, usability of database functions
and tables are clearly defined so that user can understand database system effectively and use
them in a proper manner (Coronel and Morris, 2014). In order to attain better results on time and
increase user convenience, training can be provided to user at stipulated time.
4.4 Explain the ways through which verification and validation has been addressed
Table 9: Verification and Validation table
Verification Validation
By entering P_Id, it is checked that patient entry is
available in the database or not.
This works as a foreign key when one record
update then it creates update error.
Without applying SUM function, record was not
shown.
After improving result, proper results are get on
time.
By executing query, validation has been checked. By considering quality factors and validation
criteria, all test cases outcomes have been measured
(Westman, 2001).
4.5 Control mechanisms
Control mechanism including security, integrity and system availabilities that are
necessary to be followed during database design. In the context of accessing control,
administrative and physical controls are provided to database user (Jonathan, 2008). Besides that,
different types of security measurements have been applied such as protection form improper
usage and database integrity etc. for protecting sensitive data in an effective manner. In addition,
access rights are provided to patient, specialist, registrar and admin department to carry out
different operations as per their roles and responsibilities.
4.6 Testing and implementation of database system
Database of health care can be tested by ensuring that the level of data mapping between
different forms and entities. Testing of four properties including ‘Atomicity’, ‘Consistency’,
9

‘Isolation’ and ‘Durability’ have been done when database is distributed. In addition, tables,
queries and syntaxes are checked to test the working of entire database (Ramakrishnan, 2007).
By applying these steps, testing of health care database users can be done.
Figure 10: Testing and implementation of database
In addition, white box testing technique can be employed because it allows database
administrator to test DB before making the integration with the front end (UI). . On the other
side, black box testing is used in order to measure integration between UI and DB. By applying
these techniques, database user will be able to validate present outcome against the expected
values and validate the results of SQL query.
4.7 Appropriate decisions to improve present system
On the basis of critical evaluation of “Fast Track Health Centre (FTHC)” database, it is
cleared that database professional should concentrate on preparing user manual simpler for the
users,graphics and images that should be used for the betterment of present queries and its
results. In present case, only basis functions of SQL including create, insert, update and delete
has been described. But in further report, database administrator should concentrate on
explaining and using of different database terminologies such as view, store procedure and other
structured query language functions in a significant manner (Greenwals, Stackowiak and Stern,
2013). Store procedures and functions types of advance functionalities will help patient and
specialists to retrieve information from database in a quick manner. In addition, white box testing
and agile techniques can be applied to check validity of database by using various tools.
CONCLUSION
From the present report, it is cleared that by implementing “Fast Track Health Centre
(FTHC)” database, organization will be able to perform room booking and patient record
maintenance tasks in a proper manner. Specialist can be able to retrieve information about room
10

booking, accessibility, time and datae. On the other side, patients would be able to see
availability of specialist and taking consultation from doctors at right time.
11

REFERENCES
Books and Journals
Åström, K. J. and Wittenmark, B., 2013. Computer-controlled systems: theory and design.
Courier Corporation.
Ault, R., 2002. Oracle Administration and Management. John Wiley & Sons.
Coronel, C., and Morris, S., 2014. Database Systems: Design, Implementation, & Management.
Cengage learning.
Eriksson, L. and et.al., 2013. Multi-and megavariate data analysis basic principles and
applications. Umetrics Academy.
Gelman, A. and et.al., 2014. Bayesian data analysis. London: Chapman and Hall/CRC.
Greenwals, R., Stackowiak, R. and Stern, J., 2013. Oracle Essentials: Oracle Database 12c. O
Reilly Media Inc.
Hsiao, C., 2014. Analysis of panel data. Cambridge university press.
Jonathan, F., 2008. The UK's faster payment project: avoiding a bonanza for cybercrime
fraudsters. Journal of Financial Crime. 15 (2). pp.155 - 164.
Ogden, T., 2012. Essential wavelets for statistical applications and data analysis. Springer
Science and Business Media.
Ramakrishnan, R., 2007. Database management systems. McGraw-Hill.
Ranjit B., 2006. Understanding management data systems for enterprise performance
management. Industrial Management & Data Systems. 106(1). pp.43 – 59.
Régnière,J. and et.al., 2012. Effects of temperature on development, survival and reproduction of
insects: experimental design, data analysis and modeling. Journal of Insect Physiology.
58(5). pp. 634-647.
Ritchie, J. and et.al., 2013. Qualitative research practice: A guide for social science students and
researchers. Sage.
12