Database Design & Development

This document provides an introduction to database design and development, including the use of Microsoft Access as a database management system. It covers topics such as relational database systems, database development, query language, system testing, and technical and user documentation.

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

Figure 24 Output of SQL statement
Display customer id, order number and driver id of deliveries that are of East Region
Figure 25 SQL statement of East region delivery
Figure 26 Output of East region delivery SQL query
Show SQL query that uses another SQL query
Figure 27 SQL Statement
Figure 28 Output of SQL query
Report:
Here are some reports that were generated either from tables or from query statements:
11

Customer report
Figure 29 Customer Details report
Customer invoice report
Figure 30 Report generated from customer invoice query
Report that shows delivery details
Figure 31 Delivery report
Report generated from scheduled day query
12

Figure 32 Deliveries after 3rd February report
LO 3
P 4 System Testing
Database security
Database security can be defined as a kind of security measures that helps in enhancing
overall security or protection of database against integrity, confidentiality and availability of the
data (Van Aken and et al., 2017). It is important to maintain database security in order to protect
database, its data, DBMS system from various kinds of internal or external threats. Database
testing helps in identifying loopholes with the database so that such issues or loopholes can be
removed and database security can be enhanced.
Testing
Testing can be defined as a way through which it can be determined that whether main
objectives of a system have been met or not, whether there are any kind of issues or errors in the
system or not (Gallinucci, Golfarelli and Rizzi, 2018). There are different types of software
testing that helps in testing the software and also helps in providing in-depth knowledge about
the software. Different types of testing are mainly divided into two categories: manual testing
and automation testing. Here manual testing of the database of ABC LTD. Company will be
done with the help of a test plan. Test plan is a detailed documentation that describe objective,
expected outcome of the test, actual output of the rest, results of the test and test success or
failure identification and any kind of improvement which is required to be made to remove
issues that have been identified.
Test Case Test Description Expected
Outcome
Actual Outcome Comments
13

Test
1/#1/No1/
Test Case
1
Insertion Test:
Check if values
are getting
inserted into any
tables or not
Drivers details
table: Values
should be easily
inserted into
Drivers details
table
Test is
successful and
driver details
were
successfully
added into the
table
Test 2 Primary key
testing:
Check if duplicate
values can be
inserted into
primary key or not
If trying to insert
same value into
primary key error
message should be
displayed
Test is
successful as
customer id is a
primary key in
customer table
error message
was displayed
while entering
same duplicate
value
Test 3 Query Testing
using a Single
Table: Find out
details of
customer with
Customer_ID=401
Details of the
customer with
Customer_ID=
401 should be
displayed
Test is
successful:
Details of
customer with
Customer id
with 401 was
displayed
successfully
Test 4 Relationship
testing: Check
whether data types
of primary keys
that are used as
foreign key in
Error message
should be
displayed when
data type of
primary keys is
tried to be
Test is
successful: Error
message was
displayed when
data type of
primary key was
14

another table can
be changed or not
changed tried to be
changed
Test 5 Query testing
using multiple
tables: Check
whether involve
of all the
customers with
their prices and
scheduled day are
displayed or not
Price and
scheduled day of
the products with
customer id should
be displayed
without any error.
Test is
successful:
Customer
involve details
was successfully
displayed
Future recommendation to develop better database: Database developed should be tested
and used by the real user of the database so that any kind of future improvement or issuers can be
identified (Van Aken and et al., 2017). Requirements of the database should be approved or
accepted by the final database user so that proper database can be designed and developed that
can be used by the final user. which fulfil all of their requirements.
LO 4
P 5 Technical and user documentation
Technical documentation
Purpose: the main purpose of this technical documentation is to lay emphasis on ways in which
database systems are used, ways through which Tables, forms, reports and queries within MS
Access are created and focus on information that can help in database back-up (Krzysiak and et
al., 2018).
Ways to create tables, reports and queries:
Table creation: explained with the help of diagrams
 In order to create a table in MS Access:
 User need to go to create menu and click on table design.
15

 After selecting table design, design view of a table will be opened where user need to fill
field names, their data types and save the table with a particular name.
 In order to create primary key within a table they need to select that filed and click on
Design -> Primary key.
 After creation of all the tables user can create relationship between all the tables with the
help of foreign keys and joins. In order to create relationship between tables click on
Database Tools -> Relationships.
 After this all the tables created will be shown and user can create relationship between
two tables by creating foreign keys and joining tables.
 Queries creation: In order to create queries in MS Access
 User need to select queries option from create menu. User can create a query through
query wizard and query design as well.
 After selecting query design user need to select tables through which query is required to
be created
 Then select the fields on which criteria is required to be applied.
 After this process query is run and required output is created.
 Report creation:
 Report creation is one of the easiest things to be created in MS Access.
16

 Simply select the table or query of which report is required to be created and click on
Report from Create menu (Krzysiak and et al., 2018).
 After creation of report save it and edit the report according to the user requirement
though design view in home menu.
Minimum hardware and software requirements:
Minimum hardware requirements to run MS access database application are: Min. OS required: Microsoft Windows (32 bit), Microsoft Windows (64 bit)
 Processor Speed: 1 Hz
 Min RAM Size: 1 GB
 Min Hard Drive Space: 3 GB
Minimum hardware requirements to run MS access database application are: Microsoft Family: MS Access required Microsoft Version: At least 2016
Way to back-up a database system: It is quite easy to create and save back up file of the database
in MS Access.
 Go to File Menu
 In Save As menu save database as option and click on back up database.
 Click on save as button and choose place where backup file is required to be saved.
 Choose a name of back up file and save back up.
User Documentation
17

Purpose of Guide: the main purpose of this User documentation is to lay emphasis on ways in
which database systems can be operated, types of error messages that user can face and many
more (Krzysiak and et al., 2018).
How to operate database system:
 How to add/ edit/ delete records:
Edit or new record:
o Open table in Datasheet view from Home menu.
o In home tab click on new to enter a new record or user can directly click on new
record blank.
o After adding new record or doing some changes within the tables click on save option
present in home menu -> save option in records.
Delete a record:
o Open table in Datasheet view from Home menu.
o Select record which is required to be deleted
o Press DELETE, select Home > Records > Delete, or press Ctrl+Minus Sign (-).
 How to run a query:
o In navigation Pane locate the query
o Either double click on the query user want to run or click on the query and then press
enter to run.
o Query will be opened in design view
 How to run a report:
o In navigation Pane locate the query
o Either double click on the report user want to run or click on the report and then press
enter to run.
 How to print Reports:
o Open the report which is required to be printed by double clicking on it.
18

o When the report gets opened print preview.
o When print preview menu opens, set page size, layout and then click on print.
 How to save records:
o Open table in Datasheet view from Home menu.
o Select home menu and click on Save record option. Record will be saved.
Error messages: there are various types of error messages that database user of MS access can
face (Krzysiak and et al., 2018). But most commonly faced error messages faced by the users
are:
 Syntax error
 Invalid use of '.', '!', or '()'.
 AddNew or Edit already used
 Not a valid file name.
 Not a valid table name.
 More then one relationship associated with the table. Field data type cannot be changed.
CONCLUSION
From the above assignment is has been concluded that there are various kinds of DBMS
tools that can be used for designing and developing a database. It has been analysed that before
designing and developing a database it is important to identify both system and user
requirements for the database as it helps in clarifying main requirements of the database. These
requirements further help in identifying main entities of the database. In order to explain
relationship between these entities entity relationship diagram can be used. This diagram not
only helps in explaining relationship between entities but also helps in identifying primary key
and foreign keys in a better manner. It has also been analysed that it is extremely important to
maintain the security of a database and it can be done by protecting the database from both
internal and external threats. One of the most common way to check the security of database is
through database testing through test plan and another way is by limiting privileges of all the
users who would be using the database through grant and revoke privileges. In order to extract or
update required information it is important to perform or use SQL queries so that desired output
can be obtained that can further be used for effective decision making.
19

REFERENCES
Books and Journals
Gallinucci, E., Golfarelli, M. and Rizzi, S., 2018. Schema profiling of document-oriented
databases. Information Systems. 75. pp.13-25.
Ghosh, S., and et.al., 2018, December. Automated Generation of ER Diagram from a Given Text
in Natural Language. In 2018 International Conference on Machine Learning and Data
Engineering (iCMLDE) (pp. 91-96). IEEE.
Hindmarsh, A.C., Serban, R. and Reynolds, D.R., 2019. User Documentation for cvode v5. 0.0-
dev. 2 (sundials v5. 0.0-dev. 2).
Hoffer, J.A., Ramesh, V. and Topi, H., 2016. Modern database management (p. 600). Pearson.
Krzysiak, Z., and et al., 2018. Technical documentation management with the use of the Access
program. Mechanik. 91(7). pp.552-554.
Lambert, S.L., Holladay, J. and Drum, D.M., 2017. International AC: An education case on
continuous monitoring SQL server data with ODBC-linked tables in microsoft access.
Journal of Emerging Technologies in Accounting Teaching Notes. 13(2). pp.5-30.
Naumann, F., 2018. The relational database management systems genealogy. In Making
Databases Work: the Pragmatic Wisdom of Michael Stonebraker (pp. 173-179).
Patel, K., Han, H. and Jain, A.K., 2016, October. Cross-database face antispoofing with robust
feature representation. In Chinese Conference on Biometric Recognition (pp. 611-619).
Springer, Cham.
Pizzi, G., and et.al., 2016. AiiDA: automated interactive infrastructure and database for
computational science. Computational Materials Science. 111. pp.218-230.
Stonebraker, M., and et.al., 2018. C-store: a column-oriented DBMS. In Making Databases
Work: the Pragmatic Wisdom of Michael Stonebraker (pp. 491-518).
Van Aken, D., and et.al., 2017, May. Automatic database management system tuning through
large-scale machine learning. In Proceedings of the 2017 ACM International Conference
on Management of Data (pp. 1009-1024).
Zhang, B., and et.al., 2018. A demonstration of the ottertune automatic database management
system tuning service. Proceedings of the VLDB Endowment. 11(12). pp.1910-1913.
20