Database Design & Development

DATABASE DESIGN &
DEVELOPMENT

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
LO 1.................................................................................................................................................1
P 1 Relational database system....................................................................................................1
LO 2.................................................................................................................................................5
P 2 Database development...........................................................................................................5
P 3 Query language......................................................................................................................9
LO 3...............................................................................................................................................13
P 4 System Testing....................................................................................................................13
LO 4...............................................................................................................................................15
P 5 Technical and user documentation......................................................................................15
CONCLUSION..............................................................................................................................15
REFERENCES..............................................................................................................................16

INTRODUCTION
Database can be defined as a collection of organized data which is stored in computer
system that can be accessed easily from the system (Stonebraker and et.al., 2018). Database
management system or DBMS is a software which is used to store, manipulate and manage data
which is stored into a database. There are various kinds of tools available that can be for database
design and development. One of the most common tools used for database design and
development is Microsoft Access. Microsoft Access is database management system of
Microsoft that was released in 1992 (Lambert, Holladay and Drum, 2017). This software stores
data in its own format based on Access Jet Database Engine. In this assignment Microsoft
Access DBMS system will be used for designing and development of a small database of a small
ABC LTD. Company that consist of five database tables that are: customers, product, deliveries,
routes and drivers’ details. All the tables are made up of rows and columns that consist of
attributes and values. In order to explain relationship between all the tables Entity relationship
diagram and logical diagram will be drawn and explained. In order to ensure that all the tables of
database are fit for the purpose, database will be queried and tested. Other than this, for the fully
functional database a technical and user documentation will also be created.
LO 1
P 1 Relational database system
Database requirements:
Overall requirements of the database will be categorized into two types of requirements:
First is user requirements and second is system requirements.
User Requirements:
User can access the database and data stored in it (Van Aken and et.al., 2017).
User can update or edit the database.
User can query the database easily.
User can view all the results, reports created easily.
System Requirements:
Customers table: This table will consist of all the details related to the customer.
Product table: This table will consist of all the details of type of products.
Deliveries table: This table will consist of details of all the deliveries that are required to be done.
1

Routes table: This table will consist of details of routes though which deliveries are required to
be done.
Drivers details table: This table will consist of all the details of drivers who will be doing the
product deliveries (Zhang and et.al., 2018).
Effectiveness or importance of DBMS
DBMS helps in reducing chances of any kind of human error. While developing database for
ABC Ltd. There was no scope of human errors as any kind of errors used to display error
message.
It helps the users to perform multiple tasks simultaneously which helps in increasing efficiency
and effectiveness of the users and allows then to store any amount of data within database easily
and effectively (Ghosh and et.al., 2018).
One of the main importance of DBMS is that it allows the users to relate different tables with one
another. This feature helped the user in relating all the tables of ABC organization database with
one another in a clear and appropriate manner.
It also helps in increasing decision making for the user as it helps the user in providing correct
and high-quality information in a much better manner which further helps in increasing decision
making done by the organization as with the help of DBMS any kind of information can be
extracted in a much easy manner (Naumann, 2018).
Data Dictionary:
Tables Attributes
Customers Customer ID (PK)
Customer_name
Customer_address
Customer_contact
Product_ID (FK)
Product Product ID (PK)
product_name
Product_size
Price
Deliveries Order number (PK)
Customer_ID (FK)
2

Customer_contact
Delivery_postcode
Product_ID (FK)
Delivery_address
Driver_ID (FK)
Routes Route number (PK)
Driver_ID (FK)
Driver_Contact
Region
Scheduled_Day
Order_Number (FK)
Drivers details Driver ID (PK)
Driver_name
Driver_contact
Entity relationship diagram
ER diagram is a relationship diagram that shows relationship between entities stored in a
database (Hoffer, Ramesh and Topi, 2016). This diagram helps in specifying needs of business
that are required to be remembered to perform the business process in a proper manner. Below
Entity relationship diagram helps in explaining relationship between five main entities that are:
customers, deliveries, routes, product and driver details.
3

Figure 1 ER diagram
Logical diagram
Logical data diagram can be defined as a kind of graphical view or representation of
overall structure of the database or information system (Patel, Han and Jain, 2016). this diagram
helps in analysing overall structure of the database with the help of entities and relationships.
Below logical diagram helps in explaining relationship between all the entities where all the
foreign keys of the tables are easily visible.
Figure 2 Logical data diagram
4