Database Design and Development Project

This project provides a comprehensive guide to designing and developing a relational database system. It covers key aspects like entity relationship diagrams, user and system requirements, data validation, querying across multiple tables, and database testing. The project also includes technical and user documentation for the developed database. Desklib provides past papers and solved assignments for students.

Database
Design & Development

TABLE OF CONTENTS
INTRODUCTION......................................................................................................................3
P1 Design a relational database system and containing at least four interrelated tables, with
clear statements of user and system requirements......................................................................3
P 2 Develop the database system of user interface, output and data validations, and
querying across multiple tables.........................................................................................8
P.3 Implement a query language into the relational database system.............................18
Staff Queries:............................................................................................................................21
P.4 Database testing .......................................................................................................22
P.5 Technical and user documentation for the developed database ...............................25
CONCLUSION........................................................................................................................28
REFERENCES.........................................................................................................................29

INTRODUCTION
Database management system is a process or technique for managing the entire data
of company or organization. Database is a collection of information that organize, access,
manage and update in effective manner.
This report describes the relational database system by using specific tool and
techniques. This report also describe the different user and system required of database. It
also designs fully functional database system for performing different task effectively and
efficiently. This assignment will describe the different technique and tool that help in creating
and maintaining the database system (Alles and et.al., 2018). This assignment will discuss
about the test case, test plan by using testing procedure which help in managing the entire
database system effectively and efficiently.
P1 Design a relational database system and containing at least four interrelated tables, with
clear statements of user and system requirements
Fig.1.1

Fig: 1.1 shows entity relationship diagram which help in creating relationship between
different entities
Entity relationship diagram is graphical representation of entities that shows the relations in
database management system (Ayyavaraiah and Gopi, 2017). This diagram shows the entire
functionality of entities in database management system. ERD is a conceptual and model
representation of data that used for representing the entity and their infrastructure framework.
The important element of ERD:
 Entities
 relationships
 Attributes
Entity relationship diagram helps to increase a good and efficient database design and it can
use as high level logical data model for developing the conceptual design and their structure.
Entity is a real world object and having attribute and property.
For example- Entities:
vehicle
staff
booking
Learner
Lesson
Attributes:
Vehicle table-
Vehicle_id
vehicle_type
vehicle_make
vehicle_model
vehicle_registration
Staff table-
Staff_id

staff_first name
Staff_surname
Staff_nationality
Staff_DOB
Staff_password
Lesson table-
Lesson_id
Lesson_type
Lesson_no
Lesson_cost
User and system requirement-
User requirement specification describe the clear need and requirement of business. This is a
validation process for creating system. It cannot be based on documents and analysis but also
identify their actual required of database project (Bull and et.al., 2018). Each and every user
has different demand. It is required for designer to understand what they want. User
requirement is initial phase of project to analyse the entire requirement of business and their
resources.
Data modelling- Data modelling is a software engineering process for creating data model.
This model is analysing the requirement of project. Data modelling is mainly focused on the
database logical and physical models that required for developing an effective database
system. A logical data modelling is collection of entities and attributes (Coordinators, 2018).
It also composites with the relation between different entities. This process is creating
consistency, predictability, standardization in order to manage the entire resources that is
applicable in business organization. Data model provide framework for data used in the
information system.
The logical model consists of normalization model and having normal form that helps in
creating relationship between two tables. This model includes elements that applicable in
database system. Logical data model is useful for creating database system because it can
manage the entire data in effective manner.

On the other hand, decentralized process is important process in database system which helps
in physical database model. This process is performing different task and implementing the
queries in applications (Deneuvy and et.al., 2018).
For example- data modelling including tables and columns and also contain the table-spaces,
hardware and partitions.
System requirement-
System requirement is an essential for organization to design an effective and efficient
database system that is useful for user as well as company.
Software and hardware requirement-
Software and hardware requirement are necessary part for developing the relational database
system. These are main part of system creating an effective relationship between different
tables.
User interface:
In this system, database administrator provides the authority for user to access the data and
information of driving school and check the timing, schedule etc. All the details and
information available on their driving school database system. It also manages timing and
schedule that should be maintained by admin. Admin allows inserting, deleted, update data
on particular web system.
User:
Login
username
password

Fig :1.2

Software requirement-
Linux x86-64
 Asianux Server 3 SP2
 Oracle Linux 4 Update 7
 Oracle Linux 5 Update 2
 Red Hat Enterprise Linux 4 Update 7
 Red Hat Enterprise Linux 5 Update 2
 Red Hat Enterprise Linux 5 Update 5 (with the Oracle Unbreakable Enterprise Kernel
for Linux)
 SUSE Linux Enterprise Server 10 SP2
 SUSE Linux Enterprise Server 11
Hardware requirement-
database tool- MYSQL, oracle
hard disk space- 2 GB
Oracle home directory- 8GB
P 2 Develop the database system of user interface, output and data validations, and querying
across multiple tables
For designating the database table and fully functional system to store and
delete the information in the database table and columns.
Fig.2.1
This is the booking table and creating relationship with another database tables. For creating
table, it can be used SQL query for creating table according to the need and requirement of
case study. This table contains different information such as booking_id, booking_date,
booking_time etc. these are entities of booking table for showing the information of particular
staff and person or learner.

Fig.2.2
It is Learner table for creating relationship among different tables and columns. For creating
table, it can be used SQL query for creating table according to the need and requirement of
case study. This table contains different information such as learner_id, Learner_Firstname
Leaner_surname, Learner_phone no, Learner_Email address, Learner_date of birth,
Learner_nationality. Learner table showing the entities and their specific attributes.
Fig.2.3
Fig.2.4

Fig.2.5
This figure shows the Booking form for update, delete and save the information or data in
this form. There are different options available in this form such as add, Delete, find print and
save. These are important task that performed in database system. This form will represent all
important data effectively and efficiently.

Fig.2.6
Fig.2.7

Fig.2.8
This figure represents the staff form and allow user to update, delete and save the
information of staff member. There are different options available in this form such as add,
Delete, find print and save. These are important task that are performed in database system.
This form will represent all important data effectively and efficiently.

Learner form
This is the Learner table and creating relationship with another database tables. For creating
table, The SQL (structural query language) for creating table and adding some information
according to the requirement of case study. This table contains different information such as
learner_id, Learner_First name Leaner_surname, Learner_phone no, Learner_Email address,
Learner_date of birth, Learner_nationality. These are entities of Learner table for showing the
learner who learn to drive. This form is useful for user to easily identify the information and
also check the status of person who learn for driving.
Fig: 2.9

Vehicle form
Fig: 3.0
This is the vehicle table and creating relationship with another database tables. For creating
table, it can be used SQL query for creating table according to the need and requirement of
case study. This table contains different information such as vehicle_id, vehicle_make
vehicle_model, vehicle_registration number. These are entities of vehicle table for showing
vehicle information.

Lesson form
Fig: 3.1
This is the Lesson table and creating relationship with another database tables. For creating
table, This table show the different information of lesson that would be teach in the driving
schools. This table contains different information such as learner_id, Lesson_type,
lesson_number, Lesson_cost. Lesson form shows the cost and type of particular lesson. It is
also shows the attributes.

Database tables:
Booking table
Fig : 3.2
Learner Table-
Fig :33
Staff Table-
Fig : 3.4
Lesson table-
Fig : 3.5
Database table-
Lesson_table, learner_table, Staff_table, Booking _table
All the table create relationship between columns and also represent their attributes.
Relational database is applicable in driving school which are already mention in case study.
This database is based on the driving school for providing the effective services. This
organization is using an efficient database system for managing the information in proper
manner.

Relationship between database tables:
Fig: 3.5
This diagram shows the relationship between different entities and attributes by using
primary and foreign key.
Primary key- This key is also known as primary keyword because this key is used in
relational database system that has unique value and each table has their own primary key.
For example- In Booking table, Booking_id is a primary key because it contains unique value
in column. Each and every table have own primary key such as staff_id, Booking_id,
lesson_id, Learner_id.
Database must have one or two primary key in relational database tables. This key
will depend upon on the preference and it is possible to change primary key in the given
database system (Doll and et.al., 2018).
Foreign key- Foreign key is group of column in the relational database that connect
and link two different database tables. In most cases, this key is applicable for establishing
relationship between tables. For example- In figure: 2, staff table have their own primary key
is staff_id that show another table. It means staff_id is a primary key in staff table but in

booking table and represented as a foreign key because tables create a relationship between
each other.
P.3 Implement a query language into the relational database system.
Booking table
Booking Queries:

Learner table

Leaner Queries:

Lesson table
Lesson Queries:
Staff table
Staff Queries:

P.4 Database testing
Testing is the main concept in the software development process because it can
identify the need and requirement (Vasarhelyi and Halper, 2018.). Testing help for removing
all the threats, malware, virus that generated at the time of development. For designing the
database system, it is required for testing to test their cases and remove the bugs and threats
in the entire system. For evaluating the performance of designed database it is necessary to
conduct database testing. For this purpose, various test models can be used (Groomer and
Murthy, 2018). Various elements of system such as security aspects, functionality, reliability,
data validation and verification are verified by the testing principles. Along with these
attributes the following elements of database are tested.
 The transactions involved in database must not be interdependent and data must be
protected from sudden losses (Horne and et.al., 2018). Thus, durability and isolation
are key attributes which are tested along with validation and functionality. Testing is
the important factor that would test the entire functionality of operations. This process
will help for transaction to remove the error and providing the security to their
consumer. For Example- online transaction is possible to provide the security because
entire process is tested by testing tools and platforms. These tools and platform are
used for providing security in proper manner. The driving school verifies that online
payment of its system is easy and less time consuming. It will be helpful for its
consumers to use the service.
 The organization of data must be checked after implementation. It is one of the
essential task because this database schema testing provides the accurate details
regarding primary and foreign keys as well as the attributes and characteristics of
different fields. (Le and et.al., 2018) Data schema is the testing the entire details of
system or database. DBMS used primary and foreign key to create relationship
between tables and columns. This is useful for identifying the characteristic of entities
or attributes. For example the cost, type and id of lesson column are related to the
learner attribute as well. It is essential for the driving school to ensure that primary
and foreign keys accurately describe the relation between these various parameters.
 The triggering must be regulated for accurate functioning of database. Thus, the
developers made specific test schemes to test this parameter. For example, if a new

entity is added to learner field then the system must automatically trigger the lesson
and booking fields.
The structured testing of database is performed by tests such as triggering, schema testing,
and procedures for storing data. On the other hand, in order to test the functionality of
database following methods are used (Naveen, Sanjay and Abid, 2018). There are two
important testing that help for testor check the entire database system in effective manner.
Black box testing:
For checking the database functionality this approach verifies the system integration.
The methods such as graphing methods, boundary value analysis and equivalence partitioning
are the most common tools used in black box testing. With this approach it is not mandatory
for the tester to have detailed knowledge of database architecture or internal components.
Thus, it is simple, cost effective and can find errors related to functionality and performance
(Smith and et.al., 2017). However, sometimes it fails to detect certain errors and it also acts
as redundant. Black box testing is required for checking the external mechanism of software
and database system. This testing will useful for determine the internal process and their
mechanism (Pongratz, J. and et.al., 2018). Driving school organization is also using black
box testing and test their process at every stage. It is step by step process for checking the
entire stage of database implementation. Black box testing is the best approach for testing
only external system (Samaan, 2017). This testing will depend on the input and generated the
actual output.
White box testing:
In white box approach it is required that testers must have detailed knowledge of
internal architecture. It mainly tests the logical concepts and triggers which are responsible
for supporting database. White box testing also verifies the referential integrity, tables and
database schema. White box testing can easily eliminate the internal bugs. However, it does
not cover SQL statements and developers must have adequate knowledge of programming
skills (Singh, Shukla and Mishra, 2018.). White box testing is a method for test the internal
structure and design of database system.
The test plan for the system testing provides systematic guidance of steps to achieve testing
goals. Test plan is an effective and efficient strategy for fulfilling the target of user and
organization. This concept is useful for identifying the actual need and requirement in the

database system. There are important stages that help for performing operations in step by
step.
Test plan:
The database testing consists of following stages:
Test environment preparation: In the first stage test environment is prepared so that tests
can be performed. In the developed project SQL queries are used for testing purpose.
Test execution: The test procedures can be performed by different test modal such as black
box testing or white box. Each testing modal analyse the different elements of testing. The
testing execution of the driving school has used both approaches of testing so that
functionality and integrity can be tested.
Test results and validation: Once the test is performed, outputs of tests are evaluated. In
order to check the validation of test results and functionality of database developers use
various types of test data. It helps them to analyse the validation of outputs. The test plan
will help developers to conclude that the database is designed as per the requirements of
school. It compares the test results with the expected outcomes.
Documentation: The test results are documented in this last stage and these are compared
with the system and user requirements. It helps to evaluate that whether performance of
database is satisfactory or it requires modifications (McDermott, tephan and Gibson, 2018).
The following test specifications are planned to be tested under test plan.
Test description Data type used for testing Expected outcome
Enter details of new lesson
type
Duration, cost and type of
lesson along with unique ID
The new lesson field must
also be updated in the
attributes of booking so that
users can get its details as
well.
Protection for authorised
access
Passwords which provides
data privacy.
When user who has access to
only booking section must
not be able to modify the
lesson cost or attributes of

vehicle.
Backup ability All data types The user must be able to take
back up with easy without
system hanging.
Multiple accessing Two different users are asked
to access the same attributes
or record at the same time
from remote or same location.
Both of the users must be
able to access the data
without any error.
Error message Wrong type of data is entered
in the field. For example a
numerical value is added in
the name attribute.
An error message must notify
the mistake so that user can
correct it for proceeding
further.
Security checking Suspicious data is sent on the
system to identify the security
measurements
The antivirus must notify
regarding the vulnerability.
For testing the data validation, a wide range of test data is used. These data can be generated
by using automation tools or directly by testers. The type and amount of test data which can
be used for testing is influenced by the method of their generation and cost factors.
 For ensuring the accuracy bot valid and invalid set of test data is used so that outputs
can be compared and evaluated (Wenand et.al., 2018).
 Blank data are used to test that if database generates error signal or it gives false
results.
 Illegal type of test data is also effective so that only valid data is processed and data
validity can be tested for the developed database. For example, if any learner feeds
numerical values in his name attribute then system will notify regarding error or
mistake.
 For monitoring the performance and stress handling capacity the huge volumes of
data are generated so that it can be analysed that system does not crash due to large
volume of data flow.
The various testing approaches and test data are used to ensure that the database
system performs in similar manner as per the system and user requirements. The security
concern is also addressed by applying illegal test data and huge volume test data. Apart from

functional performance, test methods are also used to evaluate the effectiveness of system
requirements (Yunus and et.al., 2017). The data triggering and schema testing ensures that as
soon as staff members of driving school update their records the same should be reflected
immediately in other attributes. For example, if certain details of any lesson is changed then it
must be updated simultaneously in the records of learners as well. It will avoid error
possibilities and there will be no mismatch in records. The testing drawbacks will help to
drive school to identify attributes which need to be corrected by developers. Test plan and
test case are important concept in testing for identifying the actual need and requirement of
business. Database system is the best part of organization to manage their information
regarding the learner, staff in their database system. This is the best system for managing
their details in effective manner.
Testing approaches-
Testing approaches is the best strategy for implementing new concept in the database system
or project. Testing approach is applicable in DBMS for creating an effective design and
approach in proper manner. There are two important techniques applicable in database design
such as:
 Proactive- this approach is the best technique to test the process in order to find the
unwanted virus, malware the entire system. It is also important for fix the bug in the
database system. Proactive approach is using database system for controlling the bugs
in system so it is important for organization to manage their data.
 Reactive: this approach is applicable for entire design and coding because it will test
the entire part of database system.
There are some strategies that help for designing an effective quality of project and database:
 Dynamic approaches
 consultative approaches
 model based approaches
P.5 Technical and user documentation for the developed database
Technical documentation: This documentation for the reservation database system provides
the brief guidelines regarding its architecture and functional behaviour.

Description of process flow: The database will manage various records such as booking,
lessons, staff and vehicles. Each field has its own set of attributes and different access
related rules. Certain attributes such as staff details are not available for remote access
whereas the records such as booking and vehicles can be accessed from remote locations as
well. The database must have ability to make decision and update its columns. For example
when users will book a lesson than automatically details of vehicles must be provided to him
so that he can get the details of his trainer and vehicle.
Classification of data flow: The data flow is strictly monitored by administration of the
driving school. The staff members are primary users. They can modify details and access
majority of records such as lessons and vehicles details. The consumers are given authority
to modify only one column of booking and can access to lessons. Internet is also used so
various test methods are applied to ensure that huge volume of internet traffic does not
restrict the functional activities.
Business logic flow path and restrictions: For considering the security concerns of business
every record has a predefined access control and data flow. For example, consumers are not
liable to read or view the staff details.
Expected input parameter: Each attribute of the field has given specific parameter. For
example, the name of any entity must not include more than 50 characters. In case if some
fields are characterised by it then error message will notify that user cannot make this
attempt. Certain information is mandatory to be filled such as contact details and names
while user can opt to choose details such as their preference in choosing the driver or
vehicle. When users of driving school will not feed accurate information then, system will
not allow it to proceed further. For instance if user will leave name attribute empty then he
or she will not be allowed to view or modify the data. It is essential from the security
perspective.
Output and error handling: When user do not enter the data values as per the definition of
the column then an error message is highlighted on the screen. The same has been tested by
invalid test data during database testing. It also includes the case instances when null or
empty data is provided by user in mandatory field.

User documentation: The following guide will provide outline to users about attributes and
structure of database.
Booking tab: This option will allow customers to register in the driving school. It is
provided with remote access facility so that consumers can access it with the help of remote
devices as well. It will include booking details as well as some attributes of other columns
such as lesson id and vehicle number.
Learner section: It is required for the driving school to maintain a separate tab in the system
for learners. It consists of all personal details of learner such as name, contact number as
well as details of the booking they have registered for. This section is accessible by staff
members as well.
Lesson: It consists of details of all lessons provided by the school. The cost and types of
lessons are provided in different attribute so that staff members and customers both can
access these details any time. However, consumers can only read these details but only staff
members can modify these records.
Add staff: The driving school keeps the details of all of its employees. The regular
recruitment process makes it essential to provide a quick method to add staff member option
in the database. These details are highly sensitive and thus only top management of the
driving school can access and modify these field parameters.
Vehicle: Though vehicle details are not highly sensitive to threats of unauthorised access but
in order to provide complete details to customers regarding vehicles this section is created
separately. This is easily accessible to all users. However, modifications can be made by
authorise staff members only.
Settings: The settings option will allow users to change the settings related to access
permission, security check, user interface modifications such as change in font size or
background colour and brightness. It can change the appearance of database according to
comfort of each individual user.

CONCLUSION
As above report, a successful design an effective database system which help for
managing the entire business operations and important details of organization. It also
represents the relationship between different tables and columns by using foreign key.
Database management system is a process or technique for managing the entire data
of company or organization. This system is collecting the data that organize and it is easily
access, manage and update the information.
As per discuss , it concludes that the relational database system by using specific tool
and technique. This report design fully functional database system for performing different
task effectively and efficiently. Techniques and tools are helpful for creating and maintaining
the database system. Testing is the best method for creating test case, test plan and for
managing the entire database system effectively and efficiently.

REFERENCES
Books and Journals:
Alles, M. and et.al., 2018. Continuous monitoring of business process controls: A pilot
implementation of a continuous auditing system at Siemens. In Continuous Auditing:
Theory and Application (pp. 219-246). Emerald Publishing Limited.
Ayyavaraiah, M. and Gopi, A., 2017. Database Management System. Horizon Books (A
Division of Ignited Minds Edutech P Ltd).
Bull, J.W. and et.al., 2018. Data transparency regarding the implementation of European ‘no
net loss’ biodiversity policies. Biological Conservation, 218, pp.64-72.
Coordinators, N.R., 2018. Database resources of the national center for biotechnology
information. Nucleic acids research, 46(Database issue), p.D8.
Deneuvy, A. and et.al., 2018. Implementation of enhanced recovery programs for bariatric
surgery. Results from the Francophone large-scale database. Surgery for Obesity and
Related Diseases, 14(1), pp.99-105.
Doll, M. and et.al., 2018. Linking HIV-negative youth to prevention services in 12 US cities:
barriers and facilitators to implementing the HIV prevention continuum. Journal of
Adolescent Health, 62(4), pp.424-433.
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.
Horne, A.C. and et.al., 2018. Informing environmental water management decisions: using
conditional probability networks to address the information needs of planning and
implementation cycles. Environmental management, 61(3), pp.347-357.
Le, K.K. and et.al., Spfy: an integrated graph database for real-time prediction of bacterial
phenotypes and downstream comparative analyses. Database, 2018, pp.1-10.
McDermott, T.E., Stephan, E.G. and Gibson, T.D., 2018, April. Alternative database designs
for the distribution common information model. In 2018 IEEE/PES Transmission and
Distribution Conference and Exposition (T&D) (pp. 1-9). IEEE.
Naveen, K., Sanjay, K. and Abid, H., 2018. Facilitating lean manufacturing systems
implementation: role of top management. IJAME.

Pongratz, J. and et.al., 2018. Models meet data: Challenges and opportunities in
implementing land management in Earth system models. Global change
biology, 24(4), pp.1470-1487.
Samaan, S.S., 2017. Design and Implementation of a Pharmaceutical Inventory Database
Management System. Al-Khwarizmi Engineering Journal, 13(1), pp.118-128.
Singh, A., Shukla, N. and Mishra, N., 2018. Social media data analytics to improve supply
chain management in food industries. Transportation Research Part E: Logistics and
Transportation Review, 114, pp.398-415.
Smith, J.P and et.al., 2017. An Implementation of a Database Management System for Real-
Time Large-Lake Observations. Marine Technology Society Journal, 51(6), pp.5-9.
Vasarhelyi, M.A. and Halper, F.B., 2018. The continuous audit of online systems.
In Continuous Auditing: Theory and Application (pp. 87-104). Emerald Publishing
Limited.
Wen, Z. and et.al., 2018. Design, implementation, and evaluation of an Internet of Things
(IoT) network system for restaurant food waste management. Waste Management, 73,
pp.26-38.
Yunus, M.A.M. and et.al., 2017. Study on Database Management System Security
Issues. JOIV: International Journal on Informatics Visualization, 1(4-2), pp.192-194.