Comprehensive Database Design and Report for UShop Online Retail

This document presents a comprehensive database design project for UShop, an online retail company. The project begins with an introduction to the need for a Relational Database Management System (RDBMS) to manage customer, product, employee, and order data. It details the understanding of databases, explaining the advantages of RDBMS, and then delves into conceptual modeling by identifying key entities like CUSTOMER, EMPLOYEE, ORDER, and PRODUCT. The project includes an Entity Relationship (ER) model, a logical model, and a thorough normalization process to optimize data organization and eliminate redundancy, including 1NF, 2NF, and 3NF, and BCNF. An enhanced ERD is also provided. The relational schema, physical model, and creation scripts for the database are detailed, along with ER diagrams created in phpMyAdmin and table structures. Insertion scripts are also included. The document covers all aspects of database design and implementation, making it a valuable resource for students studying database management and data modeling.

Data Modelling & SQL Language
DATABASE DESIGN AND REPORT
[NAME]
[STUDENT NUMBER]

Table of Contents
Introduction................................................................................................................................2
Understanding the Database.......................................................................................................2
Conceptual modelling................................................................................................................3
Possible entities..............................................................................................................................................3
ER Model..........................................................................................................................................4
Logical model............................................................................................................................4
Normalization...................................................................................................................................4
Enhanced ERD..................................................................................................................................7
Relational schema.............................................................................................................................7
Physical model...........................................................................................................................8
Creation script...................................................................................................................................8
ER diagram in phpMyAdmin..........................................................................................................11
Table structure................................................................................................................................12
Insertion script................................................................................................................................12
Conclusion...............................................................................................................................16
References................................................................................................................................17
Figure 1: Conceptual data model...............................................................................................5
Figure 2: Normalization process................................................................................................7
Figure 3: Enhanced ERD...........................................................................................................8
Figure 4: Table structure in PhpMyAdmin..............................................................................12
1

Introduction
UShop is an online retail company and the business has grown immensely over the years. In
order to manage the data and information sets, the company is looking to Relational Database
Management System (RDBMS). The database needs to be developed for the organization and
there are certain parameters that must be met. The database must store the information of the
customers, products, employees, and orders associated with the company. There are specific
attributes associated with the four major categories of information. There are also certain
conditions that need to be met while developing the database. For instance, an employee of
UShop can server multiple orders at the same time while one order shall be served by a single
employee only. Similarly, an order may comprise of one or more products and one product
can be ordered in multiple orders. The development team will be able to adequately develop
and implement the database with the aid of the database details, such as normalization, entity
relationship diagrams, details of the attribute, and likewise.
Understanding the Database
A database is a container in which is used in order to store and organize the information sets.
There are manipulations that can be made to the database as per the user role that is assigned.
In order to implement all of these capabilities, it is required to implement a specialized engine
called a Database Management System (DBMS). The database engine is composed of the
software, hardware, and the information sets. There are different types of database
management systems that have been designed. File management system is one of the types of
database management systems in which file-based processing is carried out.
The database management system that is to be implemented for UShop is Relational
Database Management System (RDBMS). It is the database that is designed on the basis of
the relational model and was developed by E.F. Codd in the year 1970. The basic format
which may be used to explain the database is the tables that are made up of rows and
columns. The software development process can be broadly categorized and carried out
through two major approaches. One of these approaches is to break the problem in smaller
pieces and add further details to each sub-level (Yamamoto and Koizumi, 2013). It is termed
as the top-down or decomposition approach. The second approach is to develop the structure
using a defined coding process and to carry out the testing of each of these steps. It is termed
as the bottom-up approach. Code reusability is one of the prime benefits that are provided
with the use of this approach.
2

The implementation of RDBMS will offer several advantages to the organization, such as:
 The table format that is used and supported by the RDBMSs is easy to use and
understand. The users of the database will be the employees of UShop and they will
get hold of the same in no time. It will lead to the enhancement of their respective
productivity and efficiency levels (Seo and Kim, 2011).
 The database will allow multiple users to access the database at the same time. The
database will have transaction management facility implemented in it along with the
build-in locking mechanism. This will ensure that the reliability of the information is
maintained at every instance and there are no performance issues during simultaneous
database access by more than one user.
 There will be several users that will be provided with the access to the database.
However, the access rights and permissions will vary from one user to the other. For
example, the rights of an operational staff members and the director of UShop will be
different. RDBMS will allow the database administrator to easily grant and control
the data access which will lead to enhanced security and protection of the data sets
(Hussain and Khan, 2014).
 The database administrator will be able to easily handle and maintain the database
with the aid of automated maintenance tools. The backing up of the data sets will also
be done with ease which will enhance the security of the data sets.
 Generic language as Structured Query Language (SQL) will be supported by the
database. The syntax used in the database will be simple and it will allow easy
execution of data handling and data storage operations.
Conceptual modelling
Possible entities
On the basis of given scenario, following entities are identified:
 CITY
 CUSTOMER
 EMPLOYEE
 ORDER
 ORDER_PRODUCT
 POTCODE
 PRODUCT
 QUALIFICATION
3

 STATE
ER Model
An Entity Relationship (ER) model or diagram is the representation of the database entities
and their relationship in a graphical format. There are four major entities that will be involved
in the database of UShop and these will be Customers, Employees, Orders, and Products.
Each of these entities will have their own set of attributes that will be represented in the
model along with the relationship between the attributes.
Figure 1: Conceptual data model
Logical model
Normalization
Normalization is a design technique that is used in the databases that allows easier and
efficient organization of the data sets to remove the redundancy and dependency present
among the sets. The methodology that is followed in the technique is to divide the larger
tables in to smaller ones to achieve the goals and then link the smaller tables with the aid of
the relationships.
Edgar Codd came up with this technique and introduced the first normal form for avoiding
and removing the redundancy in the data sets. Soon after, second and third normal forms
were developed. Codd along with Boyce introduced another normal form which is referred as
4

Boyce-Codd normal form. There are certain conditions that need to be fulfilled in order to
make sure that the normal forms of the database are achieved (Sharmila and Anusya, 2012).
For the first normal form, it is essential that each table cell has a single value and every
record is unique in nature. In the databases, there are keys that are used to uniquely identify a
specific cell or record. Primary keys are used in the process of normalizing the database and
these refer to a single column value that must be unique for every record. Composite keys are
also used in the process and these are made up of multiple columns. In the second normal
form, it is necessary that the table is already present in the first normal form and single
columns primary key is defined and is present (Attallah, 2017). Foreign keys are also used in
the process of normalization. These are the keys that reference the primary key from another
table. Transitive functional dependency is defined as a condition wherein the changes in a
non-key column may change or have an impact on other non-key column. The database is
considered to be in its third normal form when it is already present in its second normal form
and there are no transitive functional dependencies present (Demba, 2013). There are chances
that the database may be in its third normal form and there may be certain anomalies present
in the database. Boyce Codd Normal Form (BCNF) is the normal form which is then applied
to deal with the anomalies.
All the entities shown in figure 2 are in UNF. I started with splitting all entities into smaller
tables and then established relationships among them.
 1NF – In order to convert UNF into 1NF, I followed the rule that the data should
atomic i.e. each cell should contain only single value and identified primary key
(attribute that uniquely identifies other attribute values. It is always Non-NULL). In
this process, foreign keys are also identified which are used for connecting other
tables by referencing primary key of another table.
 2NF – In this process, repeating groups are eliminated using primary key. This was
done by creating a separate table with unique primary key.
 3NF – In this process, transitive dependency is removed.
5

Figure 2: Normalization process
6

Enhanced ERD
Figure 3: Enhanced ERD
Relational schema
CUSTOMER – {customerID, customerFirstName, customerLastName,
customerOtherName, houseNumber, streetName, cityID, postCodeID, contactNumber}
STATE – {stateID, stateName}
CITY – {cityID, cityName, stateID}
POSTCODE – {postCodeID, postcode, cityID}
QUALIFICATION – {qualificationID, qualificationName}
EMPLOYEE – {employeeID, employeeFirstName, employeeLastName, houseNumber,
streetName, cityID, postCodeID, salary, jobTitle, contactNumber}
ORDERS – {orderID, orderDate, totalQuantity, customer, employee}
PRODUCT – {productID, productName, productPrice, productStock}
ORDER_PRODUCT – {orderID, productID, quantity}
7

Physical model
Creation script
CREATE TABLE STATE
(stateID INT AUTO_INCREMENT
, stateName VARCHAR(20) NOT NULL
, PRIMARY KEY (stateID)
) ;
CREATE TABLE CITY
(cityID INT AUTO_INCREMENT
, cityName VARCHAR(20) NOT NULL
, state INT NOT NULL
, PRIMARY KEY (cityID)
) ;
ALTER TABLE CITY ADD CONSTRAINT city_state_fk FOREIGN KEY (state)
REFERENCES STATE (stateID);
CREATE TABLE POSTCODE
(postCodeID INT AUTO_INCREMENT
, postCode INT NOT NULL
, city INT NOT NULL
, PRIMARY KEY (postCodeID)
) ;
ALTER TABLE POSTCODE ADD CONSTRAINT postCode_city_fk FOREIGN KEY
(city) REFERENCES CITY (cityID);
CREATE TABLE QUALIFICATION
(qualificationID INT AUTO_INCREMENT
, qualificationName VARCHAR(30) NOT NULL
, PRIMARY KEY (qualificationID)
) ;
8

CREATE TABLE CUSTOMER (
customerID INT AUTO_INCREMENT
, customerFirstName CHAR(30) NOT NULL
, customerLastName CHAR(30) NOT NULL
, customerOtherName CHAR(30) NOT NULL
, houseNumber INT NOT NULL
, streetName VARCHAR(20)
, city INT
, postCode INT
, contactNumber INT
, PRIMARY KEY (customerID)
);
ALTER TABLE CUSTOMER ADD CONSTRAINT customer_city_fk FOREIGN KEY
(city) REFERENCES CITY (cityID);
ALTER TABLE CUSTOMER ADD CONSTRAINT customer_postCode_fk FOREIGN
KEY (postCode) REFERENCES POSTCODE (postCodeID);
CREATE TABLE EMPLOYEE (
employeeID INT AUTO_INCREMENT
, employeeFirstName CHAR(30) NOT NULL
, employeeLastName CHAR(30) NOT NULL
, houseNumber INT NOT NULL
, streetName VARCHAR(20)
, city INT
, postCode INT
, salary int
, jobTitle VARCHAR(20)
, contactNumber INT
, PRIMARY KEY (employeeID)
);
ALTER TABLE EMPLOYEE
9

ADD qual INT;
ALTER TABLE EMPLOYEE ADD CONSTRAINT employee_qual_fk FOREIGN KEY
(qual) REFERENCES QUALIFICATION (qualificationID);
ALTER TABLE EMPLOYEE ADD CONSTRAINT employee_city_fk FOREIGN KEY
(city) REFERENCES CITY (cityID);
ALTER TABLE EMPLOYEE ADD CONSTRAINT employee_postCode_fk FOREIGN
KEY (postCode) REFERENCES POSTCODE (postCodeID);
CREATE TABLE ORDERS (
orderID INT AUTO_INCREMENT
, orderDate DATE NOT NULL
, totalQuantity INT NOT NULL
, customer INT NOT NULL
, employee INT NOT NULL
, PRIMARY KEY (orderID)
);
ALTER TABLE ORDERS ADD CONSTRAINT orders_customer_fk FOREIGN KEY
(customer) REFERENCES CUSTOMER (customerID);
ALTER TABLE ORDERS ADD CONSTRAINT orders_employee_fk FOREIGN KEY
(employee) REFERENCES EMPLOYEE (employeeID);
CREATE TABLE PRODUCT (
productID INT AUTO_INCREMENT
, productName VARCHAR(30) NOT NULL
, productPrice INT NOT NULL
, productStock INT NOT NULL
, PRIMARY KEY (productID)
);
CREATE TABLE ORDER_PRODUCT (
10

productID INT NOT NULL
, orderID INT NOT NULL
, quantity INT NOT NULL
, PRIMARY KEY (productID, orderID)
);
ALTER TABLE ORDER_PRODUCT ADD CONSTRAINT orderP_product_fk FOREIGN
KEY (productID) REFERENCES PRODUCT (productID);
ALTER TABLE ORDER_PRODUCT ADD CONSTRAINT orderP_order_fk FOREIGN
KEY (orderID) REFERENCES ORDERS (orderID);
ER diagram in phpMyAdmin
Figure 4: Table structure in PhpMyAdmin
11