Database Design and Development Report: ABC LTD Database Project

This report details the design and development of a database for ABC LTD, a small company. The assignment utilizes Microsoft Access to create a relational database with tables for customers, products, deliveries, routes, and drivers. The report covers user and system requirements, the importance of DBMS, and a data dictionary defining table attributes. It includes Entity-Relationship (ER) and logical diagrams illustrating table relationships. The development phase describes the creation of each table, including field properties and sample data. SQL queries are used to insert, display, and filter data, with examples provided. The report also covers report generation based on queries. System testing, including a test plan, is performed to ensure database security and functionality. The report concludes with technical and user documentation, emphasizing table creation, and offers recommendations for future database 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

LO 2
P 2 Database development
Database application development can be defined as a kind of process which is used to
obtain real world requirements, analyse all the requirements, design and implement it into the
database or system (Hindmarsh, Serban and Reynolds, 2019). it helps in putting all the values
within the database in a well-planned manner. For this database five tables have been created and
each table have at least 5 records stored in it.
Customers Table:
Figure 3 Design view of customers tables
Figure 4 field properties of customer_ID (Primary key)
Deliveries table:
5

Figure 5 Design view of Deliveries table
Figure 6 field properties of Order_number (primary key)
Drivers details table:
Figure 7 Design view of Drivers details table
6

Figure 8 Field properties of Driver_ID (primary key)
Product table:
Figure 9 Design view of Product table
Figure 10 Field properties of product_ID (primary key)
Routes table:
Figure 11 Design view of Routes table
7

Figure 12 Field properties of Route_number (Primary key)
Values inserted in all the tables:
Customers Table:
Figure 13 Values inserted into Customers table
Deliveries table:
Figure 14 Values inserted into Deliveries table
Drivers details table:
Figure 15 Values inserted into Drivers details table
8

Product table:
Figure 16 Values inserted into Product table
Routes table:
Figure 17 Values inserted into Routes table
P 3 Query language
SQL stands for Structured Query language is a kind of way to communicate with a
database (Pizzi and et.al., 2016). This query language is used for manipulating, updating or
storing data into database. There are various kinds of SQL commands that can be used to update,
store or delete the data into database as per the requirements of the user. Some of the SQL
queries applied to ABC LTD. Database are as follows:
Insert new values into product table
Figure 18 SQL statement of insert query
Figure 19 Value inserted from insert statement
9

Figure 20 Product table after value insertion through insert SQL query
Display customer invoice of all the customers that displays customer id, customer name, their
order number, product id, price and scheduled day of delivery.
Figure 21 SQL statement of the query
Figure 22 Output of SQL statement
Display details of the customers whose scheduled day of delivery is 3rd February or after 3rd
February.
Figure 23 SQL statement of the query
10