Relational Database Design Report for KameelPerd Hydraulic Equipment

This report details the process of relational database design for KameelPerd Hydraulic Equipment Company, beginning with an analysis of entities and attributes. The report guides through the normalization process, transforming data into 1NF, 2NF, and 3NF forms to eliminate redundancy. An Entity Relationship Diagram (ERD) is created to visually represent the database structure, including entities like Customer, Shipment, Delivery_Company, Supplier, Product, Invoice, and SalesPerson. A data dictionary is provided, detailing each attribute, its type, and constraints. The report concludes that normalization is key to efficient database organization, and the ERD and data dictionary are valuable for storing and managing information for the company. The report also includes references to relevant literature on database design and normalization.

Running head: Relational Database Design
RELATIONAL DATABASE DESIGN
Name of the Student
Name of the University
Author Note

1Relational Database Design
Table of Contents
Introduction:...............................................................................................................................2
Discussion:.................................................................................................................................2
Task 1:....................................................................................................................................2
Task 2:....................................................................................................................................5
Conclusion:..............................................................................................................................11
Bibliography:............................................................................................................................12

2Relational Database Design
Introduction:
After analyzing the provided documentation, every entity can be identified. This
report provides the step by step process of normalization. After completing the normalization
process an ERD can be produced. Every entity and its attributes and keys can be identified in
the ERD. After identified the ERD a data dictionary is attached in this report. This data
dictionary provides every attribute detail that is identified in the ERD.
Discussion:
Task 1:
Before starting the normalization process every entities and attributes must be in UNF
structure. The UNF structure is providing below.
Customer (Customer_number, Customer_Name, Salesperson_Number, Salesperson_Name,
Salesperson_Area, Delivery_Company_Number, Delivery_Company_Location,
product_number, product_description, quantity, price, supplier_number, supplier_name
invoice_number, Shipping_number)
Now, most of the important attributes are identified but it is not properly normalized
in order to organize the data normalization is required (Amin et al. 2019). Normalization is a
special type of technique to organize every data with the database. Above entity and
attributes only dependent on one key and data is redundant. Decomposing table in various
form is the best way to eliminate the data redundancy problem (Eessaar. 2016). There are
many rules are available such as 1NF, 2NF, 3NF BCNF and fourth normal form. In this
report, we are going to the decomposing table in 1NF, 2NF and 3NF only.
1NF 2NF 3NF
Identifying entities and Identifying entities with partial No key dependencies

3Relational Database Design
attributes in 1NF form dependencies
(Product)
product_number,
product_description, quantity,
price, supplier_number,
supplier_name
invoice_number, Date
Shipping_number
(Customer)
Customer_number,
product_number,
Customer_Name,
Salesperson_Number,
Salesperson_Name,
Salesperson_Area,
Delivery_Company_Number,
Delivery_Company_Location,
(Product)
product_number,
product_description, quantity,
price,
(supplier)
supplier_number,
product_number,
supplier_name
(Customer)
Customer_number,
invoice_number,
Shipping_number
Salesperson_Number,
Date
Customer_Name,
Salesperson_Name,
Salesperson_Area,
Delivery_Company_Number,
Delivery_Company_Location
(Customer)
Customer_number,
Customer_Name,
Address,
Contact_number
(Salesperson)
Salesperson_Number,
Salesperson_Name,
Salesperson_Area,
Contact_Number
(Delivery_Company)
Delivery_Company_Number,
Delivery_Company_Location,
Contact_Number
(supplier)
supplier_number,
supplier_name
contact_number
(Product)

4Relational Database Design
product_number,
supplier_number,
product_description, price,
(Shipment)
Shipping_number,
Delivery_Company_Number,
Quantity_Shipped
Date
(Invoice)
Invoice_Number,
Customer_Number,
Salesperson_Number,
Product_number,
Shipping_number,
Date,
Quantity, Sales_Price
Assumptions:
Assume that,
 Apart from customer name and number, customer table also record their address and
contact number.
 Shipment entity store delivery company number, quantity sold and date.

5Relational Database Design
 Delivery company entity also store every contact detail.
 Supplier table also stores every contact number of suppliers.
 Apart from product number and description, product table store supplier number and
unit price.
 Invoice table store every necessary detail. Customer number, salesperson number,
product number, shipping number, sales price and total quantity sold information.
 Salesperson table store their number, name, area and contact number
Business Rules:
 Shipment table contains one or more delivery company number.
 One supplier can provide one or more products.
 One Invoice can have only one Customer number, salesperson number, product
number, shipping number.
Task 2:
Entity Relationship Diagram:

6Relational Database Design
Figure 1: ERD of KameelPerd Hydraulic Equipment Company
(Source: Created by Author)
Entity Description Form:
Entity Description Form
Database Name KameelPerd Hydraulic Equipment Database
Entity Name: Customer
Description: All information related to customers stored in this table

7Relational Database Design
Attribute
Name Type Required Default Constraints
P
K
Customer_Number
Customer_Name
Address
Contact
INT
TEXT
TEXT
INT
NOT NULL
NOT NULL
NOT NULL
NOT NULL
N/A
N/A
N/A
N/A
UNIQUE
N/A
N/A
N/A
Entity Description Form
Database Name KameelPerd Hydraulic Equipment Database
Entity Name: Shipment
Description: All information related to shipping stored in this table
Attribute
Name Type Required Default Constraints
P
K
F
K
Shipping_Number
Delivery_Company_Number
Quantity_Shipped
INT
INT
INT
NOT NULL
NOT NULL
NOT NULL
N/A
N/A
N/A
UNIQUE
N/A
N/A

8Relational Database Design
Date Date NOT NULL N/A N/A
Entity Description Form
Database Name KameelPerd Hydraulic Equipment Database
Entity Name: Delivery_Company
Description: All information related to Delivery_Company stored in this
table
Attribute
Name Type Required Default Constraints
P
K
Delivery_Company_Number
Delivery_Company_Location
Contact_Number
INT
TEXT
INT
NOT NULL
NOT NULL
NOT NULL
N/A
N/A
N/A
UNIQUE
N/A
N/A
Entity Description Form
Database Name KameelPerd Hydraulic Equipment Database

9Relational Database Design
Entity Name: Supplier
Description: All information related to Supplier stored in this table
Attribute
Name Type Required Default Constraints
P
K
Supplier_number
Supplier_name
Contact_Number
INT
TEXT
INT
NOT NULL
NOT NULL
NOT NULL
N/A
N/A
N/A
UNIQUE
N/A
N/A
Entity Description Form
Database Name KameelPerd Hydraulic Equipment Database
Entity Name: Product
Description: All information related to Products stored in this table
Attribute
Name Type Required Default Constraints
P
K
F
Product_Number
Supplier_number
Product_Description
INT
INT
TEXT
NOT NULL
NOT NULL
NOT NULL
N/A
N/A
N/A
UNIQUE
N/A
N/A

10Relational Database Design
K Price DECIMAL NOT NULL N/A N/A
Entity Description Form
Database Name KameelPerd Hydraulic Equipment Database
Entity Name: Invoice
Description: All information related to Products stored in this table
Attribute
Name Type Required Default Constraints
P
K
F
K
F
K
F
K
F
Invoice_Number
Customer_number
Salesperson_Number
Product_number
Shipping_Number
Date
Quantity_Sold
Sales_Price
INT
INT
INT
INT
INT
DATE
INT
DECIMAL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
UNIQUE
N/A
N/A
N/A
N/A
N/A
N/A
N/A

11Relational Database Design
K
Entity Description Form
Database Name KameelPerd Hydraulic Equipment Database
Entity Name: SalesPerson
Description: All information related to SalesPerson stored in this table
Attribute
Name Type Required Default Constraints
P
K
SalesPerson_Number
SalesPerson_Name
SalesPerson_Area
Contact_Number
INT
TEXT
TEXT
INT
NOT NULL
NOT NULL
NOT NULL
NOT NULL
N/A
N/A
N/A
N/A
UNIQUE
N/A
N/A
N/A
Conclusion:
After the above analysis and research, it can be concluded that normalization is one of
the best ways to organize the database. After completing the normalization process, Figure 1
ERD can be used for KameelPerd Hydraulic Equipment Company to store every information.
Data dictionary is provided in this report. It can produce a detailed overview of every entity
and its attributes along with the primary and foreign key.

12Relational Database Design

13Relational Database Design
Bibliography:
Amin, M., Romney, G.W., Dey, P. and Sinha, B., 2019. Teaching Relational Database
Normalization in an Innovative Way. The Journal of Computing Sciences in Colleges, p.48.
Date, C.J., 2019. Database design and relational theory: normal forms and all that jazz.
Apress.
Eessaar, E., 2016. The database normalization theory and the theory of normalized systems:
finding a common ground. Baltic Journal of Modern Computing, 4(1), p.5.
Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.
Kumar, K. and Azad, S.K., 2017, October. Database normalization design pattern. In 2017
4th IEEE Uttar Pradesh Section International Conference on Electrical, Computer and
Electronics (UPCON) (pp. 318-322). IEEE.
Noh, H.N., Bahari, M. and Zakaria, N.H., 2018. A Conceptual Model of Database
Normalization Courseware Using Gamification Approach. In PROCEEDINGS OF NEW
ACADEMIA LEARNING INNOVATION (NALI) SYMPOSIUM 2018 (p. 23).