Relational Database Implementation using MS Access

Desklib provides solved assignments and past papers to help students succeed.

Contents
Part A- Database implementation using MS Access..................................................................2
Normalisation.........................................................................................................................2
Relational database implementation......................................................................................4
Implementation Report...........................................................................................................5
References..................................................................................................................................6
List of Figures
Figure 1: ERD............................................................................................................................3

Part A- Database implementation using MS Access
Normalization
Normalization is a process of removing the inconsistency, redundancy, and anomalies from
the present database (Kumar, K. and Azad, S.K., 2017). The process of Normalisation
includes three different forms named as 1NF, 2NF, and 3NF. 1st Normal Form helps in
removing the atomic values, 2nd Normal Form helps in removing the Partial Dependency
present in the database and 3rd Normal Form helps in removing the Transitive Dependency.
Partial Dependency is defined as when a non-prime attribute depends on one of the two
present primary keys in a table. Transitive Dependency is defined as when a non-prime
attribute depends on the other non-prime attribute.
a). The given ERD is normalized into 3NF and shown as:
Claim (ClaimId, ClaimLodgedDate, ClaimSettledAmount, ClaimPaidDate)
Customer (CustomerId, CustomerName, CustomerAddress, CustomerContactNum)
Parcel (ParcelId, ParcelWeightInGrams, FromAddress, ToAddress, DateCollected,
ParcelValues, Charges, Status, CustomerId, EmployeeId, ClaimId)
Invoice (InvoiceId, InvoiceDate, InvoiceAmount)
Employee (EmployeeId, EmployeeName, EmployeeStartDate)

Figure 1: ERD
b). Functional Dependencies:
Functional Dependencies can be defined as the relationship between the two of the attributes,
mainly among the Primary keys and the non-prime attributes inside the particular table or
entity (Coronel, C. and Morris, S., 2016). It can be understood as for any of the relation R, If
Y is functionally dependent upon the X that is considered as the Primary Key, the X value
can uniquely determine the Y value.
Here we are taking two relations where we have to find out their functional
dependencies.
 Customer Relation
Customer (CustomerId, CustomerName, CustomerAddress, CustomerContactNum)
Here,
CustomerId -> CustomerName
CustomerId -> CustomerAddress
CustomerId -> CustomerContactNum

Primary Key here is the CustomerId and none of the entities are dependent on each other
whereas each of the attributes has their relation with the CustomerId. So, none of the
dependencies occur.
 Employee Relation
Employee (EmployeeId, EmployeeName, EmployeeStartDate)
Here,
EmployeeId -> EmployeeName
EmployeeId -> EmployeeStartDate
Primary Key defined here is the EmployeeId and none of the entities are dependent on each
other whereas each of the attributes has their relation with the EmployeeId. So, none of the
dependencies occur.
Demonstration
The above two relations mentioned are present and normalized in the form of 3NF. 3NF is
the normalization form where the data is present in the 1NF, 2NF and is free of Transitive
dependency.
 Customer Relation
Customer (CustomerId, CustomerName, CustomerAddress, CustomerContactNum)
The above-mentioned relation is in 1NF as the data present in this table contains the atomic
values as there are no repetitions present in the data. The data is also present in the 2NF as
there is no Partial Dependency present and the Partial Dependency is considered as when a
non-prime attribute depends on one of the two present primary keys in a table. The next step
of the Normalisation is the 3NF, the above relation is already normalized into the 3NF as
there is no Transitive Dependency present and it is defined as when a non-prime attribute
depends on the other non-prime attribute.
 Employee Relation
Employee (EmployeeId, EmployeeName, EmployeeStartDate)
The above-mentioned relation is also present in 1NF as the data present in this table contains
the atomic or dissimilar values as there are no repetitions present in the data. The data is also
present in the 2NF as there is no Partial Dependency encountered. The next step of the
Normalisation is the 3NF, the above relation is already normalized into the 3NF as there is no
Transitive Dependency present.
Relational database implementation
Data Integrity
Attribute Data Type Integrity Constraint
Implemented
Error Message
ParcelId AutoNumber Primary Key Duplicate Values
ParcelWeightInGrams Number
FromAddress Text
ToAddress Text

DateCollected Date/Time <=Now()
Charges Number
ParcelValue Number
Status Text
CustomerId Number Foreign Key Can’t update values
EmployeeId Number Foreign Key Can’t update values
ClaimId Number Foreign Key Can’t update values
Implementation Report
While completing this assignment, I have learned a few things that are:
 The first thing I have learned is the normalization process that actually helps in providing
consistent data without any redundancy and the anomalies.
 The second thing that I have learned is the proper implementation of the queries and the
query optimization process that helps in providing the required data from a large set of
data (Mahajan, D. and Zong, Z., 2017).
The complex task that has been completed:
The task that was a bit difficult for me to perform was the joining of the tables for performing
the given queries. Also, in one of the query, the nested query is used that was a bit difficult to
perform. The things were found difficult at first but later on, got successfully implemented.

References
 Mahajan, D. and Zong, Z., 2017, October. Energy efficiency analysis of query
optimizations on MongoDB and Cassandra. In 2017 Eighth International Green and
Sustainable Computing Conference (IGSC) (pp. 1-6). IEEE.
 Kumar, K. and Azad, S.K., 2017. Relational Database Normalization under Tabular
Approach: A Design Methodology. International Journal of Advanced Research in
Computer Science, 8(5).
 Coronel, C. and Morris, S., 2016. Database systems: design, implementation, &
management. Cengage Learning.