Database Design and Implementation: A Case Study Using MS Access

This report explores the design and implementation of a relational database using MS Access. It covers fundamental database concepts, including normalization, entity-relationship modeling, and SQL queries. The report details the creation of a database for an art gallery, demonstrating the application of database design principles and techniques. It includes a comprehensive analysis of the database's effectiveness, validation methods, and user and technical documentation.

Introduction
Relation database is a collection of the information and records. It stored the data in the tabular form
which can be an update, access, insert, delete, and selected the database. Database supports for data
verification and validations, and data normalization. Here, I have created the database on the MS Access.
Database report divides into three parts.
In the first section, I will define database concept, database security, advantage and distance. The
database will explain organisational issues. I will also explain Database design methods and
methodology. In the second section, Logical design, requirement specification, and development area will
be explained for the database. I will also design the normalization and ER diagram for the database. In the
third section, I will design the database and performed the query on the database. Further, I will define
the data verification and validations techniques and designed a user and technical documentation.
1

LO2 Understand database design techniques
2.2 discuss entity-relationship modelling and normalization
Normalization:
The database is a collection of information. Database use normalization to reduce the data redundant in
the database table and improved the data integrity, efficiency, and scalability. Relation methods and
model explained the efficiency of the database which is classified in the normal form. Normalization spilt
the database table into a normal form that must be re-joined the table according to the query (Memon,
2014).
First Normal Form
It referred as 1 NF which contained following specification
 Each table attribute should be a unique name which contained a single value.
 The table cannot contain row duplicated value.
 In the 1NF does not allow the repeating groups.
Here, I have shown 1 NF for database
Table 1: First Normal Form
First Normal Form
Person Table Art Table
Customer_ID Artwork_name
Customer_name Type
Contact_number Location
Address Branch
Artist_ID Gallery_name
Name Group_name
Contact_number address
Address Booking_id
Assistant_ID Artwork_name
Name Art_style
Gallery Event_name
2

Staff_name Date
work Time
salary
Second Normal Form
It referred as 2 NF which contained following specification
 2 NF table must be in the 1NF form.
 2NF remove the partial dependencies and designed another table.
 It is fully dependent on the table primary key
Here, I have shown 2NF for database
Table 2: Second Normal Form
Second Normal Form
Customer Art
Customer_ID Artwork_name
Customer_name Type
Contact_number Location
Address Branch
Gallery_name
Staff of gallery Art_style
Artist_ID Group_name
Name address
Contact_number Booking_id
Address Artwork_name
Assistant_ID
Name Booking details
Gallery Booking_id
Customer_ID Artwork_name
Staff_name Event_name
work Location
salary Date
3

Time
Third Normal Form
It referred as 3 NF which contained following specification
 3 NF table must be in the 2NF form.
 It removed the transitive dependencies in the table which means table Non-primary attributes
independent from other Non-primary attributes of the table (Saokosal, 2015).
Here, I have shown 3NF for database
Table 3: Third Normal Form
Third Normal Form
Gallery Artist Artwork Artwork_Group
Gallery_name Artist_ID Artwork_name Group_name
Location Name Type address
Manger_ID Contact_number Location Customer_ID
Customer_ID Address Branch Booking_id
Staff_ID Artwork_name Gallery_name
Staff
Upcoming_Event Booking Staff_id Customer
Event_name Booking_id Staff_name Customer_ID
Location Customer_id work Customer_name
Date Artwork_name salary Contact_number
Time Event_name Address
Assistant_ID Art_type
Assistant Artwork_name
Assistant_ID Art_style
Name
Gallery
Customer_ID
4

Entity-relationship modelling
ER modelling is creating a graphically representing the object and their logical relationships in the
database. ER diagram design with objects and their relationships. Objects or Entities represent by the
ovals, polygons, points and circles. The relationship represents a line which connected the objects. It has
an equivalent relational database table and vice-versa. It used for the design of the database, debugging,
and optimization. It defined one to one, one to many, many to one and many to many relationships in the
database (Inf, 2000).
Figure 1: ER diagram for Database
5

LO3 Be able to design, create and document databases
3.1 apply the database developmental cycle to a given data set
Relational Database
A relational database is a collection of data which represented in the tuples or table form. It commonly
used the database.RD is contained a number of table in the database and each table has a primary key.
Data access form RDMS easy compare than other the database table. The relational database supports
MYSQL, SQL, and So-on another query language that used extract the information or data from the
database.
A feature of the relational database:
 Data is stored in the tabular form which contained the primary key in each table.
 It defined the relationship between the table.
 ACID property support by the Relational database. ACID means Atomicity, Consistency,
Isolation and Durability.
 It is a centralized database which supports the normalization
 RD objects contained complex relationship.
 A relational database is supported the transaction and update tool (Aslam, 2016).
Table view of the database:
Gallery Table:
 Design View:
Figure 2: Design View of the Gallery Table
 Data In table:
6

Figure 3: Gallery table datasheet view after entering the data
Artist Table:
 Design View:
Figure 4: Design View of the Artist Table
 Data In table:
Figure 5: Artist table datasheet view after entering the data
Artwork Table:
 Design View:
7

Figure 6: Design View of the Artwork Table
 Data In table:
Figure 7: Artwork table datasheet view after entering the data
Artwork Group Table:
 Design View:
Figure 8: Design View of the Artwork Group Table
 Data In table:
Figure 9: Artwork Group table datasheet view after entering the data
Upcoming Event Table:
8

 Design View:
Figure 10: Design View of the Upcoming event Table
 Data In table:
Figure 11: Upcoming event table datasheet view after entering the data
Booking Table:
 Design View:
Figure 12: Design View of the Booking Table
 Data In table:
9

Figure 13: Booking table datasheet view after entering the data
Staff Table:
 Design View:
Figure 14: Design View of the Staff Table
 Data In table:
Figure 15: Staff table datasheet view after entering the data
Customer Table:
 Design View:
Figure 16: Design View of the Customer Table
10

 Data In table:

Figure 17: Customer table datasheet view after entering the data
Gallery Assistant Table:
 Design View:
Figure 18: Design View of the Gallery Assistant Table
 Data In table:
Figure 19: Gallery Assistant table datasheet view after entering the data
Art type Table:
 Design View:
11

Figure 20: Design View of the Art Type Table
 Data In table:
Figure 21: Art Type table datasheet view after entering the data
12

3.2 design a fully functional database (containing at least four inter-relational
tables) including user interface
In the database, the query used to extract the information from the database. Database support MySQL,
SQL and another query language. SQL is a special-purpose programming language that is designed for
the RDMS. it gave the instructions for communicate with the database and performed specified database
task, functions, work, and queries.
RDMS Query is four types
 Data Definition Language: DDL support Alter (add into the database), Create (New database
create), Drop (delete from the table), Truncate command, and select (extracted the information
from a database). It mostly uses for modifies data into the database.
 Data Control Language: It contained control command of the database. Grant (allowed the user
to access the database) and Revokes (privileged the database access) command.
 Data Manipulation Language: DML Manage the database information. It contained Insert (data
inserted into the database), deletes (data deleted from the database), update (data updated into the
database) and select command (Black, 2015).
 Commits and Rollback command are Transaction Control command which managed the
transactions.
Here, I have designed a number of queries with SQL view, query design view, and output.
Query 1
SQL View:
Figure 22: Query 1 SQL View
OUTPUT:
Design View
13

Figure 23: Query 1 Design View
Table View
Figure 24: Query 1 Table View
Query 2
SQL View:
Figure 25: Query 2 SQL View
OUTPUT:
Design View:
14

Figure 26: Query 2 Design View
Table View
Figure 27: Query 2 Table View
Query 3
SQL View:
Figure 28: Query 3 SQL View
OUTPUT:
Design View
15

Figure 29: Query 3 Design View
Table View
Figure 30: Query 3 Table View
Query 4
SQL View:
Figure 31: Query 4 SQL View
OUTPUT:
Design View
16

Figure 32: Query 4 Design View
Table View
Figure 33: Query 4 Table View
Query 5
SQL View:
Figure 34: Query 5 SQL View
OUTPUT:
Design View
Figure 35: Query 5 Design View
17

Table View
Figure 36: Query 5 Table View
Query 6
SQL View:
Figure 37: Query 6 SQL View
OUTPUT:
Design View
Figure 38: Query 6 Design View
Table View
18

Figure 39: Query 6 Table View
Query 7
SQL View:
Figure 40: Query 7 SQL View
OUTPUT:
Design View
Figure 41: Query 7 Design View
Table View
Figure 42: Query 7 Table View
19

Query 8
SQL View:
Figure 43: Query 8 SQL View
OUTPUT:
Design View
Figure 44: Query 8 Design View
Table View
Figure 45: Query 8 Table View
Query 9
SQL View:
Figure 46: Query 9 SQL View
20

OUTPUT:
Design View
Figure 47: Query 9 Design View
Table View
Figure 48: Query 9 Table View
Query 10
SQL View:
Figure 49: Query 10 SQL View
OUTPUT:
Design View
21

Figure 50: Query 10 Design View
Table View
Figure 51: Query 10 Table View
22

3.3 evaluate the effectiveness of the database solution and suggest methods of
improvement
Database Validation
It checked that database data are feasible and sensible but does not check the data accurate. Rang check,
Type check, Length check, drop down check and format check is a method of the database Validation
Computer Science GCSE GURU, n.d.).
Data verification
It is a process which checked the database data accuracy and inconsistencies after completion of the data
Migration. Data verification checks the data accuracy during data transferred from one source to another
(Teach-ict, 2011).
Table 4: Test Case Table
S.no Test case Excepted Result Actual Test Remark
1. The user does not enter
data in Gallery table one
field?
Data should not store
into the database
Data does not store
in the database.
Successful
2. The user does not enter
the primary key the
Artist table?
reference integrity
Error should be raised
in the database?
the integrity of
Reference error is
created
Successful
3. The user does not enter
date given format in the
Upcoming Event table?
The error message
should be created in the
former format?
Error message
display on the
screen.
Successful
4. Staff Name is entered
into a text format.
Staff name should be
Saved the database
Staff name saved
the database.
Successful
5. The user does not enter
Time given format in the
Upcoming Event table?
The error message
should be created in the
former format?
Error message
display on the
screen.
Successful
6. The user enters the
contact number text
A contact number
should be entered
Error message and
showed a message
Successful
23

format in the customer
table?
numerical format. enters the data
numerical form.
7. The user enters the
customer name in the
text.
Customer name should
be stored in the
database.
Customer name
stored in the
database.
Successful
8. The user enters
Customer_id in Booking
table which does not
exist in the customer
table.
An error message
should be created
which tell there is
reference integrity
Reference integrity
error message is
created.
Successful
Figure 52: Test Case 1
Figure 53: Test Case 2
24

Figure 54: Test Case 3
Figure 55: Test Case 4 Enter the data
Figure 56: Test Case 4 Actual result
25

Figure 57: Test Case 5
Figure 58: Test Case 6
Figure 59: Test Case 7 enter data
26

Figure 60: Test Case 7 Actual result
Figure 61: Test Case 8
27

3.4 provide supporting user and technical documentation
User Documentation:
Database user documentation includes the guidelines for database software and offers the basic process
which needs the start software. It is also included user interface and designed fundamentals. Here, I have
created user documentation for the database which offered the detail of MS access open and closed steps
with their working (Sdle, n.d.).
User documentation for MS Access Database:
Step 1: User double clicked on the MS access database menu.
Figure 62: Step 1
Step 2: Open the MS access database windows which are shown below:
Figure 63: Step 2 for MS Access
28

Step 3: User selected the one database type and clicked on the create button.
Figure 64: Step 3 for MS Access database
Step 4: After the clicked on to create, an MS Access database is created which are shown below. The
table view is the default page for the MS Access. The user can be added number of tables according to
their needs (Support, 2016).
Figure 65: Step 4 for MS Access Database
29

Form View of Database Table
Here I have explained the form view of a database table which tells the user what information are a need
in the database table.
Figure 66: Gallery Table Form View
Figure 67: Gallery Assistant Table Form View
30
Give the
details of
Gallery
Assistant
form
Give the
details
of
Gallery
form

Figure 68: Customer Table Form View
Figure 69: Booking Table Form View
31
Give the
details of
Customer
form
Give the
details of
booking
form

Figure 70: Artwork Table Form View
Figure 71: Artwork group Table Form View
32
Give the
details of
Artwork
form
Give the
details of
Artwork
Group
form

Figure 72: Art Type Table Form View
Figure 73: Artist Table Form View
33
Give the
details of
Art Type
form
Give the
details of
Artist
form

Figure 74: Upcoming event Table Form View
Figure 75: Staff Table Form View
Technical Documentation:
Here, I have created a Technical documentation for the database. It is included the Database Documenter
Glossary, Validation of database table, and forms properties.
Database Documenter Glossary
34
Give the
details of
Upcoming
event form
Give the
details of
Staff form

Table 5: Database Documenter Glossary
S.NO Term Full form
1 SQL SQL is a query language which is used in the database
2 DB It is a database which stored the information
3 Data Modeling Define the relationships between objects
4. Normalization Designed an organized table without ambiguity
5. View Designed temporary table with a specific perspective.
.
Validation of database table
Table 6: Validation of database table
S.no Validation Of the database
1. Each table contained the data in fields
2. The table must be contained the primary key that is Unique
3 Contact number field must be a numerical value.
Figure 76: Validation case 1
35

Figure 77: Validation case 2
Figure 78: Validation case 3
Forms properties
Figure 79: Property sheet of Upcoming Event Form
36

Figure 80: Property sheet of Staff Form
Figure 81: Property sheet of Gallery Form
37

Figure 82: Property sheet of Gallery Assistant Form
Figure 83: Property sheet of Customer Form
38

Figure 84: Property sheet of Booking Form
Figure 85: Property sheet of Artwork Form
39

Figure 86: Property sheet of Artwork Group Form
Figure 87: Property sheet of Artist Form
40

Figure 88: Property sheet of Art Type Form
ER Diagram:
ER diagram design for the data which contained the entity and their relationship. Here, I have created an
ER diagram for given database which given information about attributes, primary key and their
relationship. It makes database efficient and helpful in the searching.
Figure 89: ER diagram for the Database
41

Conclusion
In the report, I have created a database which is successfully implemented and designed using MS access
database. I have learned the concepts and issues of the database and also developed the skills design and
creation of complex databases.
In the report, I have included different type of database and their management system. Database issue and
its pros and cons have also explained the report. I have designed a database according to database logical
and physical requirements and performed the normalization and designed the ER diagram. In the next
section, I have explained query language which used by MS Access database. At the end report, data
verification and validations techniques, Technical and User documentation are defined.
42

References
 Aslam, A. (2016). Relational Database Management System. [online] Slideshare. Available at:
https://www.slideshare.net/AdilAslam4/relational-database-management-system-69290451
[Accessed 17 Mar. 2018].
 Black, C. (2015). Relational Database Management System(RDBMS) Structured Query
Language(SQL) - ppt download. [online] Slideplayer. Available at:
http://slideplayer.com/slide/9584396/ [Accessed 17 Mar. 2018].
 Computer Science GCSE GURU. (n.d.). Database Validation - Computer Science GCSE GURU.
[online] Available at: https://www.computerscience.gcse.guru/theory/database-validation
[Accessed 17 Mar. 2018].
 Inf. (2000). Introduction to Entity-relationship modelling. [online] Available at:
http://www.inf.unibz.it/~franconi/teaching/2000/ct481/er-modelling/ [Accessed 17 Mar. 2018].
 Memon, s. (2014). Normalization. [online] Slideshare. Available at:
https://www.slideshare.net/salmanmemon77964/normalization-41738255 [Accessed 17 Mar.
2018].
 Saokosal,, O. (2015). Database Concept - Normalization (1NF, 2NF, 3NF). [online] Slideshare.
Available at: https://www.slideshare.net/kosalgeek/database-concept-normalization-1nf-2nf-3nf
[Accessed 17 Mar. 2018].
 Sdle. (n.d.). Power source database. [online] Available at:
http://www.sdle.co.il/AllSites/810/Assets/user%20manuel%20ver2.pdf [Accessed 17 Mar. 2018].
 Support. (2016). Getting started with Access 2007. [online] Available at:
https://support.office.com/en-us/article/getting-started-with-access-2007-23a9abb4-2d41-451a-
b81c-bbdc00623aa0 [Accessed 17 Mar. 2018].
 Teach-ict. (2011). Teach ICT - GCSE ICT - database validation and verification. [online]
Available at: http://www.teach-ict.com/gcse_new/databases/validation_verification/miniweb/
pg11.htm [Accessed 17 Mar. 2018].
43