Database design and development Assignment Sample

DATABASE DESIGN AND
DEVELOPMENT

Table of Contents
INTRODUCTION...........................................................................................................................1
PART 1............................................................................................................................................1
1. Requirements of system and users ....................................................................................1
P1 ...........................................................................................................................................2
2. Design of database .............................................................................................................2
3. Understanding user and system requirements....................................................................3
4. Analysing effectiveness of database wrt system and user requirements............................4
PART 2............................................................................................................................................4
Completely functional database system.................................................................................4
P2............................................................................................................................................4
Effectiveness of database solution and suggestions for future improvement .................................7
Database effectiveness in relation with the prescribed requirements....................................7
Database effectiveness in relation with requirements of user................................................8
Future improvement strategies for sustaining effectiveness..................................................8
PART 3............................................................................................................................................9
Effectiveness of database testing............................................................................................9
P4..........................................................................................................................................10
Test data choice in effectiveness of testing..........................................................................11
Test plan ..............................................................................................................................13
PART 4..........................................................................................................................................13
P5..........................................................................................................................................13
Technical and user documentation for the developed database system ..............................13
CONCLUSION..............................................................................................................................16
REFERENCES..............................................................................................................................17

INTRODUCTION
Designing of database refers to the fact of involving every essential feature that is
required by the organisation. Elements are to be verified carefully at the time of designing of the
database as it should be in an order to achieve the specific requirements of a firm (Jagadish and
et.al, 2016). Also, after the database has been successfully designed, its implementation takes
place when the specified database is being implemented in the company so that it can help them
to attain their objectives at a much faster time. The report is about analysing the user and system
requirements of the organisation. It also involves the effectiveness of design of the database
covering system and user requirements. A well and fully functional database has also been
designed in the report. Also, the report involves testing of the requirements and a proper
documentation for user as well as technical measures.
PART 1
1. Requirements of system and users
There are some factors that are causing issues with the manual processing of routine
functions of hospital So, in order to overcome the issue, the business has decided to choose a
well designed database through which they can record necessary information and tracks of their
activities. This will help them to focus on achieving a great productivity with minimum error and
trust of their patients. Therefore, there must be some user as well as system requirements for this
database as well. Some well known example of user requirement can be that hospital deals with a
variety of information management such as keeping track of details related to their patients,
schedules and information of doctors, purchasing slips related to medicines and other
equipments. So, recording a proper set of information about all case studies of their facility
seekers and service providers is required to be maintained. These should be integrated with terms
such as parameters like blood groups and other related parameters used in hospitals. Therefore,
these should be kept in mind while the designing of database. Also, system requirements can be
that the systems on which they will implement database should be up to date, whether it is in
terms of software or of hardware, both of these should be well developed so that the database
may not create any issue on the system such as heating up of system, hanging etc. These users
and system requirements are necessary to be ensured while the designing as well as
implementation of the database so that they can help them to achieve their objectives in a most
efficient manner.

P1
2. Design of database

3. Understanding user and system requirements
A clear view of system and user requirements of hospital is as under :
User requirements
 Hospital manages the details of its patients, employee's and schedules of doctors
manually. The purchasing of medicines and other clinical equipments is also
accomplished with manual records. So in this case, they may face losing data. It is
because a manual record of above functionalities can get lost and cause complications for
hospital to analyse their expected and achieved outcomes.
 Also, their tracking of health status of patients and visiting time table of doctors is being
done manually, which can also get difficult at the time of making schedule or providing
treatment guidelines to patients when they are discharged from the hospital (Clifford and
et.al, 2016). So, as per this, they require a database that is capable of recording all this
information and data, i.e. of employees, health status of patients, doctors and their
availability etc. so that they can be even able to evaluate their performance as compare to
other clinics and hospitals. Therefore, these can be considered as the user requirements of
hospital for an efficient designing and implementation of database.
System requirements
 One of the major factor that is required for an efficient implementation of database is that
the system should be up to date with the latest hardware and software's.
 The hardware of the system on which database has to be implemented should have
efficient amount of space and memory so that it can cope with the requirements and data
of database. Also, if there will be an appropriate hardware on the system, then there are
very minor chances of occurring any issues such as heating of system, hanging up etc.
 Along with the hardware, ensuring all applications required and appropriate software's is
important because the software should be up to date in the system in which database has
to be implemented. It is important because it helps in easing the operations, thus not
making them complex that it can have an influence on the outputs of the system. So, the
software and the hardware, both should be up to date and as per the requirement so that

the database can be able to function properly and efficiently thus recording all the
necessary data and information of the hospital.
4. Analysing effectiveness of database wrt system and user requirements
The database designed can be termed as effective enough because the requirements of
hospital staff members have been kept in mind while designing it. The database is designed in
such a manner that hospital can maintain all of its information which is processed on daily basis
and along with that, managing the record of total number of convalescents (Harrington, 2016).
Also, the software's and hardware's have been updated well so that the database cannot throw
any pressure on the system because when any of them are not installed properly, there are
chances that the system will heat up and this can result in shutting down of system as well. So,
the database has been made in accordance with all system and user requirements so that it can be
able to cope with the basic requirements of hospital.
PART 2
Completely functional database system
P2

Effectiveness of database solution and suggestions for future improvement
For evaluation of the quality of designed database system, it is essential to evaluate
performance of various criteria such as security features, complexity of user interface, easiness
of information extraction and flow along with above mentioned requirements.
Database effectiveness in relation with the prescribed requirements
The development of designed database system must be able to fulfil the system
requirements. The effective database system provides feature of flexibility thus designed system
must be compatible with existing hardware and software of hospital. The database system must
support concurrent data processing.
Database system must essentially consider the concepts of data validation. The database
processes the information only when user enters sensible and valid data. For instance, extraction
of employee's record or details of any patient is possible only when valid employee id or patient
id is fed into database. In case of invalid or null id, database will not be able to provide results to
the user.
Data validation tools regularly validate consistency and sensibility of inserted data. Data
validation in database ensures that data type and range are valid values, which can be processed.
The system is effective to check presence, length and format of the data inserted into database.
For evaluating effectiveness of database in terms of extracting useful information, a database
system is analysed as multi component system. The data is allocated and managed in distributed
form so that effectiveness of database dynamics can be evaluated. For evaluation of installation
and operational aspects of the designed database, benchmarking evaluation techniques must be
performed.
The extent up to which hospital will share the data is also an important factor in
evaluation of effectiveness. The effectiveness of designed database system is guaranteed by
designers only after analysing the utilization of disks during processing of various queries across
multiple tables of database. It requires critical evaluation of different levels of central processing
unit. The queries, which are used to extract data from designed system must be effective to
minimize the time delay in processing as well as occupies the least space in disk. The system and
user interface must not enhance complexity of the database. The increased complexity and
unnecessary queries will not only reduce effectiveness of database system but will also make it
difficult for users of hospital to implement it. In that case, objectives of database may not be

fulfilled. The designed system must be effective so that it can integrate with all types of security
features. The support and integration with other external application will enable the system to
enhance its functionality and range of operational activities. Data base system can provide
effective outputs only if regular maintenance actions are performed properly. The system must
provide support to implement features, which are necessary to sustain the effectiveness of
database system.
Database effectiveness in relation with requirements of user
The major requirement of hospital is to manage its useful and sensitive information via
database system instead of manual data handling. Thus, the database must have sufficient
capacity for storing and processing the entire data. It must not be ineffective in handling huge
data, otherwise system failure may occur. Database must be effective in terms of processing. The
considerably large delay in processing queries or operation can degrade the effectiveness of the
system and may not provide effective results hospital in its operational activities. The parameters
like storage capacity and corresponding time delay in processing data are key factors for hospital
to evaluate the effectiveness of database system. Organisation will also operate some additional
application software in integration with database system.
Thus, database must be able to simulate with such external applications. The user
interface of designed system must be simple so that users of the health service provider can
easily interact with system and do not find it difficult or complex as compared to manual system.
Database system effectiveness is significantly dependent on attached security measurements. The
developed system must provide guarantee of the security of processed data against unauthorised
access and network security threats. These requirements by an organisation act as key factor in
evaluating the performance and effectiveness of the database system.
Future improvement strategies for sustaining effectiveness
Hospital requires implementation and guidance of certain strategies, which are necessary
for maintaining effectiveness of database system. They must perform regular cleaning of waste
data from its database. Unnecessary and useless data is generally not required by organisation
any more must be removed. This unused data not only affects the speed of operational activities
but also acts as a vulnerable threat for the security of entire system. Organisation must update its

database periodically so that effectiveness is maintained. In order to avoid information loss data
stored in the database must be back up on an essential routine. The data backup will not only
protect data from destruction but will also be helpful for future requirements.
Data encryption and regular monitoring of all data transfers and activities can reduce the
risk of security threats to a great extent. Hospital must also keep its data free from unauthorised
accessing and corruption related activities. This approach will increase data integrity and
effectiveness of database system is sustained for long term. The network security strategies must
be implemented strictly in order to avoid data security concerns. For instance, strong passwords
for authorized accessing, regular update of licensed antivirus software's is beneficial for ensuring
the security of system. Hospital must give authorised access to only selected individuals who can
modify and insert the data into database. It will greatly reduce the risk of unauthorised access
and database effectiveness will be maintained for longer term.
PART 3
Effectiveness of database testing
In order to ensure consistency and effectiveness of designed database system, various
components and functionality of database can be tested.
The effective database testing analyses and considers the following aspects:
 An effective Database testing ensures that all transactions included in database
processing holds a valid and sensible output. These outputs must be durable in nature;
this means they must be stored safely in database without vulnerability of data loss due to
system crash or power loss.
 Data base testing provides assurance that the designed database is completely able to
reflect changes made in input values. The immediate reflection of changes made into
output provides confirmation that functional requirements of user and system are
successfully implemented.
 The database testing will analyse the effectiveness of validity and integrity of data. It will
evaluate the performance factor of database such as time delay in data processing, storage
capacity and load handling ability. The database testing also aims at evaluating the
performance of various functions and triggering activities of the designed database.

P4
The database testing will provide feedback regarding drawbacks in the design and development
process. The feedback can be used to improve the functionality of the designed database. In
order to gain the effective results for database testing organisation must apply an in-memory
approach. It will increase the speed of database testing. The testing must be performed by skilled
team of designers. The selection of test data type plays crucial role in determining the
effectiveness of database testing. The organisation can use following database testing methods
which can provide effective testing results:
Black box testing:
This testing approach is considered as high level testing and it usually emphasis on
software behaviour instead of internal coding and working of the developed system. It prioritizes
the expectations and requirements of user as compare to the design approach of the database.
Black box testing does not require the knowledge of programming and implementation and is
difficult to be performed in automation mode. This type of testing use the concept of trial and
detecting error method for evaluating the functionality of database. It is less time consuming
testing procedure as compare to white box testing but this type of testing is suitable for large
segments of code.
The designed database system can be tested by using different methods of black box
testing. For evaluating the data validity and integration the most common black box method is
equivalence partitioning method. In this method of database testing inputs values are divided into
two categories of valid and invalid inputs and corresponding tests are performed from each set of
values. For analysing the test results of database in context of boundary value or range of data
values boundary value analysis method for testing is used.
White box testing:
White box testing is considered as low level testing and it requires the internal structure
and coding of the designed database. This type of testing evaluate the effectiveness of internal
functioning of database. Since this type of testing requires the detail knowledge of internal
coding it is essential for the tester to have great understanding of programming and detailed
knowledge about testing procedure. The main purpose of this type of testing is to evaluate the
code quality. Some of the most common method used in white box testing are statement and
branch coverage and path coverage. These testing methods ensures that every statement and

branch of the code is executed. It performs the testing of all paths involved in the coding of
designed database system. This testing method is very useful in elimination of internal errors.
For error handling and performance evaluation of the database white box database testing
technique creates test cases.
Gray box testing:
In this type of testing the internal structure and working of the database are partially
known to the testing team. It concentrates on various layers of designed database system to
enhance the coverage of testing procedures. This type of testing is helpful in identification of
web system related errors. The Gray box testing can be visualise as the combination of both
white and black box testing. For improving the overall performance and effectiveness of testing
results it uses various test inputs from developers and testers. The biggest advantage of this type
of testing techniques is that developers get enough time for fixing the defects and they perform
the testing from the view point of users instead of performing it as developers. This approach is
useful in performing testing in a way that it clearly follows the system and user requirement
guideline. Gray box testing methods such as matrix testing define all program variable for
achieving effective test results.
Test data choice in effectiveness of testing
The data which is used for testing the database is crucial in providing effective results.
Thus, test data must wisely select the data for testing. The developed database system can be
tested by using following test data types:
Security test data: A suspicious test data is used to ensure that database is provided with security
tools. This type of test data ensures that confidentiality and authenticity of the developed
structure.
Performance test data: These test data are similar to real time data which is used by organisation
so that functionality can be tested. This type of testing provides the accurate evaluation of load
handling capacity and system failure possibilities of the database system (Just, Jalali and Ernst,
2014).
Black box test data: These type of test data are designed in a way that they suit the developed
coding of the database. Initially it performs the test without data fetching and later on it is
performed with different sets of test data.

White box data: For effective testing certain test data is extracted from various paths and
branches of code. It also consists of invalid arguments and parameters for testing negative
application programming interface.
Null test data: Testers use empty or absent values of data so that they can figure out the response
of database in case when data is not entered by user (Patel, Han and Jain, 2016).
Invalid and wrong format test data: To evaluate the performance of designed database system in
extreme conditions when user has provided invalid and wrong data. This test data is used to
ensure that database do not process inaccurate data and instead it takes appropriate step to ask
user to feed accurate and desired data type.
Valid and boundary test data: To evaluate the functionality and performance of designed
database testers also test the system with valid values and extreme range. This is useful to judge
the extreme boundary limits in which database can function effectively.

Test plan
The objective, resources and tools of the database testing can be documented by test plan.
The test plan for the designed database system is as follows:
Test coverage: During the various stages of database testing it is necessary to define the
sequence of testing parameters in which they will be tested. It is also essential to document the
expected results of test and how they meet the system and user requirements.
Defining test environment: The designed database can be tested only within technical
environment with well defined scope. The defined testing environment reduces the technical
errors which may affect the effectiveness of testing and performance.
Test tools and methods: The essential test data are created with which system is tested to test all
parameters of the database. The iterative approaches of database rebuilding and reinitialization
of test data can give effective results. For this purpose various testing tools such as test data
generative and management tools, privacy tools and system load testing tools can be used by the
testers.
Testing inspection and results: The inspection tools are used to analyse and detect the errors in
the performance of database system and provides feedback. This feedback can be used to
evaluate the performance in relation with the user requirements.
PART 4
P5
Technical and user documentation for the developed database system
Technical documentation:
The technical documentation of the database will provide the following aspects of the
database.
Objective and purpose: The developed database will serve as an effective tool for data
management. The systematic approach provides the more secure and efficient way in terms of
processing and storage.
Architecture: The user and system requirements are implemented by the means of relational

database model. Each requirement is framed as logical and then physical implementation. The
system architecture also consider the memory and server requirements of organisation. The
restricted data type are provided for each table attribute to avoid data invalidity.
Risks: The organisation must ensure the boundary values and limits of the storage. The excess
to these limits may cause system crash. The network security tools must be installed and update
regularly and essentially.
Quality management: The performance and quality of data can be analysed by using various
profiling tools which makes comparison between data and the table attributes. Data validation
and integrity are crucial parameters for quality verification.
User documentation:
The following documentation provides the guidelines to the users regarding the
developed database system and its related attributes of user interface.
Authorised accessing: The developed database provides only authorised access to each
component of database. The employee section cannot be accessed by doctors without authority.
Doctors have authority to access only details of their patients. This approach provides
systematic management of all departments within hospital.
Sectional segmentation: Each department will have different preview of the database. The
details of patients and doctors will have tabs like name, contact number, id and email. Similarly,
store department will have tabs like names of medicines and other equipments, quantity and
description of each equipment and its id. The staff members can access the details of other
department attributes such as details of doctors and patients as well as availability of medicines
in stock but only with the authorised permission in the form of passwords or permission.
Settings: To make the user interface more easy and convenient the setting option is provided in
the database. Every individual user can change the interface related settings such as font size
and colour, brightness, background theme, multitasking status, memory and disk storage details.
This feature helps organisation to review and monitor the statistics so that storage can be freed
from useless information and useful information can be back up.
Individual segments: Each department of organisation is provided with feature that they can
access the details related to all stakeholders associated with them. For instance doctors and staff

members can manage the patients related records in this section. The account's department can
use this tab to analyse the transactions of the year for assessing the profit of hospital.
Employees: The entire records related to performance and personal details of the employees of
the organisation are provided in an individual section. In this tab employees such as doctors,
nursing staff and other management staff can view their attendance records, performance and
benefits provided to them. Organisation can use these records to provide incentives or
management of salaries.
Admin: The certain attributes are accessible and can be modified by administration only. This
feature can be used by the organisation to provide interaction facility with the employees.

Future improvements:
In order to maintain the quality performance of database organisation must use various
strategies which minimizes the performance degrading factors. For avoiding the system failure
the data validation and implementation of restricted data type to the table attribute can serve as
the most appropriate strategy. Auto fill facility to specific fields can make user interface more
convenient. To monitor the quality performance from CPU and disk space organisation must
normalise its data. Data normalisation is essential for eliminating the data redundancy and from
preventing system to perform unnecessary repeated operational activities (Ge and et.al., 2016).
To enhance the speed of the database in memory approach can be very effective. With
increasing load on processing capacity of the data base organisation can integrate the cloud
services and virtualisation with developed database. It will make database more efficient in terms
of load handling. The regular and strict monitoring of the disk space, performance of central
processor and login activities of every team member can give rise to the performance and
security of the database. The integration of highly advance features such as biometric system and
face recognition features can strict the security arrangements of the database.
CONCLUSION
It can be concluded from the report that designing and implementation of database in
hospital was essential as to help them in order to remove the manual processing of operations,
thus making them digital. A completely functional database system has also been designed which
compiles all the user and system requirements. It is because it helped in functional activities of
hospital by coping with the different challenges and minimization the errors. The report also
included the testing of requirements of user as well as system. Also, it has discussed about a well
and efficient user and technical documentation.

REFERENCES
Books and Journals
Apanowicz, C.K., Eastwood, V.K., and et.al., 2014. Method and system for data compression in
a relational database. U.S. Patent 8,700,579.
Clifford, A., Emanowicz, K. and et.al. International Business Machines Corp, 2016. Test data
generation and scale up for database testing using unique common factor sequencing.
U.S. Patent Application 14/859,253.
Ge, F., Idicula, S., and et.al., 2016. Efficient processing of path related operations on data
organized hierarchically in an RDBMS. U.S. Patent 9,229,967.
Groomer, S.M. and Murthy, U.S., 2018. Continuous auditing of database applications: An
embedded audit module approach. In Continuous Auditing: Theory and Application (pp.
105-124). Emerald Publishing Limited.
Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.
Hogan, R., 2018. A Practical Guide to Database Design. Chapman and Hall/CRC.
Jagadish, H.V., Zhou, A.and et.al., 2016. Contemporary RDBMS-based systems for visualization
of high-volume numerical data have difficulty to cope with the hard latency requirements
and high ingestion rates of interactive visualizations. Existing solutions for lowering the
volume of large data sets disregard the spatial properties of visualizations, resulting in
visualization errors. In this work, we introduce VDDA, a visualization-driven data
aggregation... The VLDB Journal.25(1). pp.103-123.Cleve, A., Gobert, M., and et.al.
2015. Understanding database schema evolution: A case study. Science of Computer
Programming.97.pp.113-121.
Just, R., Jalali, D. and Ernst, M.D., 2014, July. Defects4J: A database of existing faults to enable
controlled testing studies for Java programs. In Proceedings of the 2014 International
Symposium on Software Testing and Analysis (pp. 437-440). ACM.
Kimura, H., Narasayya, V. and Syamala, M., Microsoft Technology Licensing LLC,
2015. Compression aware physical database design. U.S. Patent 9,009,124.
Klochkov, Y., Klochkova, E., and et.al.,2016, September. Model of database design in the
conditions of limited resources. InReliability, Infocom Technologies and Optimization
(Trends and Future Directions)(ICRITO), 2016 5th International Conference on (pp. 64-
66). IEEE.
Lawande, S., Rasin, A., and et.al., 2018. Automatic vertical-database design. U.S. Patent
Application 10/007,686.
Mcminn, P., Wright, C.J. and Kapfhammer, G.M., 2015. The effectiveness of test coverage
criteria for relational database schema integrity constraints. ACM Transactions on
Software Engineering and Methodology (TOSEM). 25(1). p.8.
McMinn, P., Wright, C.J., and et.al., 2016, October. SchemaAnalyst: Search-based test data
generation for relational database schemas. In Software Maintenance and Evolution
(ICSME), 2016 IEEE International Conference on(pp. 586-590). IEEE.
Myint, Y.L., Washizaki, H., and et.al., 2018, April. Test case reduction based on the join
condition in pairwise coverage-based database testing. In2018 IEEE International
Conference on Software Testing, Verification and Validation Workshops (ICSTW). IEEE.
Patel, K., Han, H. and Jain, A.K., 2016, October. Cross-database face antispoofing with robust
feature representation. In Chinese Conference on Biometric Recognition (pp. 611-619).
Springer, Cham.