Database Design & Development Report: Relational Database System

This report details the design and development of a relational database system, focusing on a driving school scenario. It begins with an introduction to database management systems and their importance, followed by a breakdown of user and system requirements. The report includes an Entity Relationship Diagram (ERD) to illustrate the relationships between entities such as vehicles, staff, bookings, and lessons. It then covers the development of the database system, including the user interface, output, data validations, and querying across multiple tables. SQL queries are implemented, and database tables (booking, learner, staff, vehicle, and lesson) are presented with their attributes. The report also discusses database testing procedures, emphasizing security, functionality, and data validation. Finally, the report includes technical and user documentation, providing a comprehensive overview of the entire database design and development process. The report concludes by highlighting the key aspects of the implemented database system.

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.