Database Design and Implementation: A Case Study Using MS Access

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