COIT20247 Database Design and Development Assignment 2

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

COIT20247 Database Design and
Development (T12019)
Assignment2

Table of Contents
Part A. Database implementation using MS Access.....................................................................................2
Introduction.................................................................................................................................................2
Task1 Normalization....................................................................................................................................2
(a) Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF).
.................................................................................................................................................................2
(b) Select two relations from step1, a) and address following task for two relations..............................3
Task2 Relational database implementation.................................................................................................5
Part B. Database implementation using MySQL work bench......................................................................5
Conclusion...................................................................................................................................................5
References...................................................................................................................................................6
2

Part A. Database implementation using MS Access
Introduction
Database design and development is the most beneficial activities for an organization. Using a
database management system they can manage entire order management process, customer
detail, payment structure management and etc. It is a system which allows organization for the
management of data and retrieval of records in seconds only. A database implementation
strategy with MS Access can also provide a user interface to prepare a report for the activity of
order fulfillment or salary calculation. On the other hand, it may have some issue of data
redundancy and duplicate entries so the development of attribute should be address to the
third normalized form so that none of the records makes a duplicate entry in the system.
Task1 Normalization
(a) Map the ERD, from the sample solution, into a set of relations in at least
Third Normal Form (3NF).
Normalization is a special type database designing concept which enables to discover a database
solution as per proposal to develop a system with a minimized number of copied data. It strongly
separates the different type of data in different tables and by offering key terminology a
relationship can be built in between them. The primary concern to use normalization concept is
to update or add new records of data without affecting the whole table of solution.
Types of normalization:
 1Normalisation Form: within first normalization form database table hold different
types of data.
 2Normalisation Form: within second normalization form tables are worked a function
for other table fields.
3

3Normalisation Form: within third normalization form of database data duplication is strictly
prohibited. If the two consecutive tables would require same data records then it is separately
stores in two different tables.(Studytonight.com, 2019)
Normalized form for given database design is as follows:
Customer(CustomerId, CustomerName, CustomerStreetAddress, CustomerCity,
CustomerPostCode, CustomerContactNum)
Employee(EmployeeId, EmployeeName, EmployeeStartDate)
Parcel(ParcelId, CustomerId, EmployeeId, ParcelWeightInGrams, FromAddressStreetAddress,
FromAddressCity, FromAddressPostCode, ToAddressStreetAddress, ToAddressCity,
ToAddressPostCode, DateCollected, ParcelValue, Charges, status)
Delivered(DeliveredId, ParcelId, EmployeeId, DeliveredDate)
Returned(ReturnedId, ParcelId, EmployeeId, ReturnedDate)
Lost(LostId, ParcelId, EmployeeId, LostEntryDate)
Claim(ClaimId, LostId, ClaimLodgedDate, ClaimSettledAmount, ClaimPaidDate)
Invoice(InvoiceId, ParcelId, InvoiceDate, InvoiceAmount)
(b) Select two relations from step1, a) and address the following task for two relations
Listing of functional dependencies exist in relation
It is a set of relationship which exists in between two attributes when one attribute uniquely
depends on another attribute. For example, if R is a relation in between two attribute A and B
then functional dependency in between two attributes can be represented as A B. That mean
B functionally depends on A. (www.javatpoint.com, 2019)
4

From a given set of relations, there are two relations considered which are functionally
dependent.
1. First Relation
Customer(CustomerId, CustomerName, CustomerStreetAddress, CustomerCity,
CustomerPostCode, CustomerContactNum)
CustomeridCustomerName
CustomerNameCustomerStreetAddress
CustomeridCustomerPostCode
CustomeridCustomerContactNum
2. Second Relation
Delivered(DeliveredId, ParcelId, EmployeeId, DeliveredDate)
DeliveredIdEmployeeId
DeliveredIdParcelId(from Delivered table)ParcelId(from Parcel table)
DeliveredIdEmployeeId
DeliveredIdDeliveredDate
Demonstration of relations for 3NF
Demonstration of relations for 3NF
Customer(CustomerId, CustomerName, CustomerStreetAddress, CustomerCity,
CustomerPostCode, CustomerContactNum)
Primary Key is CustomerId which identified the customer. There will be more than one
customer with the same name so customerId is the only one that determines the record of
each customer uniquely. Moreover, a customer may several addresses so in this case study it is
5

required to map the address with CustomerId so that home address can only be available and
hence the customer would have only one address. There is only one date of birth for each
customer so there is no repeating term and hence the relation in 1NF.
Primary Key CustomerId is only a candidate key because the name and address of two
customers are could not be unique. This means CustomerId functionally determine every other
attribute and there is no partial dependency available. Hence the relationship is in 2NF.
Name of the customer cannot be used to determine another attribute in the table because two
different customers may have the same records. Therefore no transitive dependencies exist in
table and thus this relationship is in 3Normal Form.
Data Type Used for Development of Table
Claim
Customer
Delivered
6

Employee
Invoice
Lost
Parcel
7

Returned
Task2 Relational database implementation
Queries
1. Display the delivery status and the corresponding number of parcels
Query
Output
8

2. Display the number of parcels that have been lost but have not been claimed yet for any
compensation
Query
Output
3. Display the details of the customers who have contributed the maximum amount of charges
to CQPS through all of their parcel delivery orders
Output
9

4. List the details of employees who have handled with at least five parcels
5. Display the details of employee(s) whose name contains the word “wheel”.
Integrity constraints
Parcel Table consists three different types of attribute type. So integrity constraints over the
following attributes shown in below given table
Attribute Data type Integrity constraint
implemented
Error message
Parcelid Number NOT NULL Should not be
10

zero
ParcelValue Number CHECK(ParcelValue>=0) Should be greater
than 0
DateCollected Date/Time NOT NULL The date should
not be blank
Report
11

Part B. Database implementation using MySQL workbench
As per requirement, a simplified version of the ER model has been designed for the CQPS by
dealing with the three tables Customer, parcel and Employee. The design has been prepared
using MYSQL Workbench
12