Database Design and Implementation: A Case Study of MRA Database

This report provides a comprehensive analysis of database design concepts and implementation methodologies, focusing on the design of a relational database for a mobile repair agency (MRA). It begins by examining the key issues and applications of databases within organizations, including scalability, data security, and increasing data volume. The report then delves into database developmental methodologies, such as conceptual, logical, and physical ER modeling, along with normalization techniques. It details the implementation of a database using SQL, covering table creation, query execution, and data validation. The report also evaluates the effectiveness of the database solution and suggests methods for improvement. Furthermore, the report demonstrates the database design and provides user and technical documentation, including table designs, data dictionaries, and design views. Desklib offers a wealth of resources, including past papers and solved assignments, to aid students in their academic pursuits.

Database Design Concepts

Contents
Introduction:....................................................................................................................................5
LO1 Ba able to analyses the function of database and data management systems.........................6
1.1 Analyses the key issues and application of database within organizations...........................6
1.2 identify the functions of the database and database management system:............................9
LO2 Be able to analyses database design techniques and methodologies.....................................10
2.1 Analyze database developmental methodologies................................................................10
2.2 Discuss the role of entity-relationship modeling and normalization in database design:....13
LO3 Be able to design and create a database................................................................................18
3.1 Apply a database developmental cycle to a data set.......................................................18
3.2 Design a fully functional database containing inter-relational tables and a user interface..19
3.3 Evaluate the effectiveness of a database solution and suggest methods of improvement...26
LO4 Be able to demonstrate the functionality of a database........................................................28
4.1 Demonstrate the database design to assessors.....................................................................28
4.2 Create a supporting user and technical document for a database........................................29
Conclusion:....................................................................................................................................33
References......................................................................................................................................34

Table of Figures
Figure 1: Railway reservation system..............................................................................................7
Figure 2: Library Management System...........................................................................................8
Figure 3: Banking............................................................................................................................9
Figure 4: Entity-Relationship Diagram.........................................................................................13
Figure 5: Relational Diagram........................................................................................................15
Figure 6: Design of Company table...............................................................................................20
Figure 7: Table of company table..................................................................................................20
Figure 8: Output of company table................................................................................................20
Figure 9: Design of customer table................................................................................................21
Figure 10: Table of customer table................................................................................................21
Figure 11: Output of customer table..............................................................................................21
Figure 12: Design of Device table.................................................................................................22
Figure 13: Table of device table....................................................................................................22
Figure 14: Output of Device table.................................................................................................22
Figure 15: Design of engineer table...............................................................................................23
Figure 16: Table of engineer table.................................................................................................23
Figure 17: Output of engineer table...............................................................................................23
Figure 18: Design of payment details table...................................................................................24
Figure 19: Table of payment details table.....................................................................................24
Figure 20: Output of payment details............................................................................................24
Figure 21: Design of repair details table........................................................................................25
Figure 22: Table of repair details table..........................................................................................25
Figure 23: Output of repair details table........................................................................................25
Figure 24: Design of supplier table...............................................................................................26
Figure 25: Table of supplier table..................................................................................................26
Figure 26: Output of supplier table................................................................................................26
Figure 27: Database Design...........................................................................................................29
Figure 28: Company Table............................................................................................................30
Figure 29: Customer Table............................................................................................................31
Figure 30: Device Table................................................................................................................31

Figure 31: Engineer Table.............................................................................................................31
Figure 32: Payment Details Table.................................................................................................31
Figure 33: Repair Detail Table......................................................................................................31
Figure 34: Supplier Table..............................................................................................................32
Figure 35: Company Table Design View.....................................................................................32
Figure 36: Customer Table Design View......................................................................................32
Figure 37: Device Table Design View..........................................................................................33
Figure 38: Engineer Table Design View.......................................................................................33
Figure 39: Payment Table Design View........................................................................................33
Figure 40: Repair Table Design View...........................................................................................33
Figure 41: Supplier Table Design View........................................................................................34
Table of Tables
Table 1: User Documentation........................................................................................................30

Introduction:
Mobile repair agency (MRA), is a growing mobile repair company in London. It requires a
relational database to maintain the various data of the company. This database will contain
various details like products details, its category, customer details, mobile repair details and
many more. This report will contain the relationship diagram of the following company. The part
first of the report will include the function of the database with data management and key issues.
The second part will include the database design and various methodologies to develop the
database. The entity relationship term with normalization will also discuss in this part. Next part
is practical implementation part of the report which has the database development cycle with the
user interface and various methods to improvements. The last section of the report will contain
the database design and user and technical documentation for a database.

LO1 Ba able to analyses the function of database and data management
systems
1.1 Analyses the key issues and application of database within organizations
The database is the collection of various types of information which is easy to organize and
access and database management is the software which used to create and manage this database.
There are various key issues of the database which is required to remind for an organization
before creating a database. The key issues are like,
Scalability: The scalability found in almost every application and services which are based on the
web. The scalability is affected by the cataloguing components, operating systems, database
architecture and hardware configuration.
Changing landscape: The developers and organizers are constantly changing the landscape from
SQL to Oracle and then Oracle to DB2. There are so many versions of each database which
make it complex and impact the skills, policies and other areas.
Increasing the data volume: Increasing the data volume is affect the query performance, back and
recovery, increase CPU requirement and maintenance timeframes.
Data security: The big amount of data facing this problem mostly. This issues can be maintained
by its original data source with API and web services methods (bain, 2008).
Database application: database applications are computer software and programs which used to
manage and collect the information from a database system. Almost every business has its own
database to manage the records on platforms like Oracle, Microsoft Access, SQL server and
many more. There is the various application of database like banking, education, transport and in
many more. Some other applications are like query process and management, user-friendly,
integrity and security of data with data sharing. The other applications where use the database are
like railway reservation system, banking system education and library in the education sector
(Singh, 2018).

Railway reservation system: it keeps the record of ticket booking record of the passenger as well
as train status with its arrival and departure time.
Figure 1: Railway reservation system
Source: (Roberthempsall, 2011)
Library management system: it keeps the records of books with issues date, the name of the book
with its author name and availability.

Figure 2: Library Management System
Source: (Dykstra, 2014)
Banking: the records of thousands transaction with bank customer details and record of money
send and receive.
Figure 3: Banking

Source: (Shelton, 2017)
1.2 identify the functions of the database and database management system:
The database management has three elements which are:
 Physical database: the collection of files which makes data.
 Database engine: it is used to access and modify the database content.
 Database scheme: the logical structure specification of stored data in the database.
The file system is also a term of the database which includes spreadsheets, word documents,
photographs and presentation files. These all structure can be created by the user as required.
This types of system are known as file system and used for the individual system user.
There are various operations can perform in a database system like, create, edit the database,
update and delete the database. Save and retrieve operations can be performed after creating the
database. Database management system provides some other function of a database:
 Concurrency: a database can be accessed by multiple users at the same time.
 Data manipulation: this process is defined to creating the database and perform the
operations like, insert, edit and delete. These three functions handle the data manipulation
needs, which types are plashed and unlashed.
 Back up & Recovery: for maintaining the data of the organization it is required to take
backup process.
 Security: require to know the security guidelines for user rights.
 Data description: data dictionary is used for access the data description.
 Integrity: the structure of the database and its guidelines support to improve the data
integrity (Study, 2018).
Advantages and disadvantages of the database:
Advantages:
 The term centralized, control the data with avoiding duplication of data and reduce total
data storage. Also, ignore the extra process of and required only necessary data to store.

 The sharing of data is allowed for control of any application program. Control the data
integrity to contain consistent and accurate data.
 Data security is important for an organization to prevent an access by any unauthorized
person. It includes proper authentication process to access the database and check the
user before permitting to access the sensitive data. Various level of security implemented
for making secure the confidential data.
 Data independence has two types in a database, logical data independence and physical
data independence. A physical term used to change in the physical storage device. The
logical term is used in the application program to not change its existing fields by add or
delete any item from it. Changes can apply to a conceptual schema with the effect the
existing schema.
Disadvantages:
 The database management system is costly to purchase and develop in terms of software
and update in terms of hardware programs.
 It is required to process continuously of some terms like sharing of data, security of data
with integrity.
 The database might fail if duplication process is not working and backup-recovery
operation are costly and complex for every database (Tiwari, 2016).

LO2 Be able to analyses database design techniques and methodologies
2.1 Analyze database developmental methodologies
The database development methodologies used to build the database by using the client
requirement. The methodology has various phases to from requirement to design method, and
implementation, testing and documentation. The database methodology is divided into some
types like, conceptual, logical and physical database design. Here we discuss the steps of the
creating the database which starts requirement to the user and technical documentation.
Requirement analysis: the requirement analyses of mobile repair agency is to create a relational
database to maintain the company records of customers, categories, repair and services. The
customers register themselves to the database with using information like first name, last name,
date of birth, email, contact number and address. Some entities also need to create like, suppliers,
mechanics for repair mobile and create a relationship between the attributes of the relationship.
Design methodology: the methodology for the database includes some process like techniques,
tool, procedure and documentation to help in design methodology. It includes various stages
which help the designer to design the database as well as to manage and control the database.
Here are some types of design methodology:
 Conceptual ER: conceptual entity relationship diagram is the combination of entities,
relationship and attributes. The conceptual includes construction process of a model and
its deals with the task and with the user. During the process of data modelling, it follows
a prearranged methodology. Data models are represented by the figures and create a data
dictionary (W3schools, 2018).
 Logical ER: the logical entity relationship method includes the relation in the designs.
The logical methodology obtains and authorizes the relation between using normalization
process. It validates the relations and checks integrity control with its limits (W3schools,
2018).
 Physical ER: the physical entity relationship methodology is used to implemented logical
design into physical design. The terms of logical design like entity, attributes, constraints
and relationship translate into physical database design. Physical design includes the

description of execution which defines the base relation. The step of implement physical
methodology is designed base relation with the representation of data and design general
constraints (W3schools, 2018).
Implementation: there are so many tools and software to create and implement a database for an
organization like MS Access, SQL, Oracle and many more. Here I used SQL for creating and
implement the database for mobile repair agency. In the process of implementation, the tables
and queries are going to create. The table is created in the SQL database as per user requirement
and creation of entities and their attributes. These table’s data hold the primary key and foreign
key as the representation of the relationship between attributes. Joins are used to combine two or
more than two tables in SQL. Another terms query are used in the creation of SQL database. The
query is a code in SQL which used as a command form in the SQL to perform any operation or
task. The most used queries in SQL are, Select, Update, Delete and Insert.
Testing: database testing is the layering process which includes layers like, user interface, data
access layer, business layer and many more. There are various terms which include the testing of
database like, black box, white box, validation, referential integrity and others.
 Validation: validation process is used to check the information which enters by the user
or developer into the database. There are some validation methods which used for
validation of data like, range check, length check, format check, type check, drop down
check and presence check (computerscience, 2018).
Referential integrity: This term is used to define the relationship between tables. Each table of
database holds a primary key which used in another table to create a relationship. The primary
key which appears in another table of the database, known as a foreign key. A foreign key is
used as join to merge two tables and create dependencies between the tables (ibm, 2018).
Maintenance: Maintenance required for the database of the mobile repair agency and provide
tanning of database to the staff members of the organization and do regular maintenance of the
hardware and update the software tools. Download the latest version software and use the latest
technology hardware system for the database as well as provide training to the staff.