Report: Database Design, Implementation, and Querying for Sweet Shop

This report provides a comprehensive overview of the database design and implementation for an online sweet shop. It begins with an introduction outlining the project's aims and objectives, followed by a class diagram and a discussion of constraints and assumptions. The report delves into design decisions, including the creation of a relational model and the importance of avoiding data redundancy and anomalies. It details the normalization process, progressing through 1NF, 2NF, and 3NF. A relational model is presented, along with a data dictionary. The implementation section includes the actual SQL code for creating tables, defining relationships, and populating the database with data. Finally, the report demonstrates the implementation of queries to meet the database's initial requirements and concludes with a critical evaluation of the design and implementation process. The report covers all the essential aspects of database design, from conceptual modeling to physical implementation, providing a solid foundation for understanding and applying database principles.

Contents
Chapter 1: Introduction....................................................................................................................3
Class diagram...................................................................................................................................4
Constraints and Assumptions.......................................................................................................5
Chapter 3: Design Decisions...........................................................................................................5
Chapter 4: Relational model............................................................................................................6
Chapter 5: Implementation..............................................................................................................8
Chapter 6: Queries.........................................................................................................................16
Chapter 7: Conclusion...................................................................................................................19
References......................................................................................................................................19

Chapter 1: Introduction
Online Sweet Shop is an online shop that specializes in selling of sweets to its customers. The
shop operates online and does not have any retail centers. The shop is in need of a database that
will be used to support its e-commerce thus this report discusses the design process of the
database. The aims and objectives of this project are;
 To design a class diagram based on the case study and provide a list of constraints and
assumptions used to design the class diagram.
 To discuss the design decisions made while constructing the class diagram and the
resulting design decisions needed to convert the class diagram to a relational model.
 To present a relational model that can be used for the implementation of the database
using a relational database management system.
 To show an implementation of the database. This will include showing the actual SQL
code for creating tables and implementing relationships between the tables as well as data
population codes that insert data into the tables making up the database
 To show an implementation of queries that demonstrate the implemented database meets
all the initial requirements of the database.
 To provide a critical evaluation of the design and implementation process of the database

Class diagram
Figure 1: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . Class
diagram

Constraints and Assumptions
 Each region has one warehouse. A region has many distribution centers and each
distribution center is associated with only one warehouse which operates in the region.
The distribution centers get their stock from the warehouse in the region that they fall in.
 There are two types of orders that can be prepared by a distribution center. The first type
of order is a standing order and the second is a special order. Both orders differ on the
basis of the items that are currently in stock.
 Each distribution center has its own stock. When a customer makes an order, the order is
processed based on the items that are currently available in stock in the distribution
center.
 A customer maintains a list of items that he or she would like to purchase. The list is
maintained as a wish list. When the customer makes an order and the item is transferred
from the wish list to the shopping list. Once an item is added to the shopping list, the item
is removed after 90 days if the customer does not buy it. The shopping list only maintains
one type of item at a time.
 Each order has one or more items that are currently available in stock. An order cannot
have items that are not in stock.
 An order results to one and only one payment which is done using a credit card.
Chapter 3: Design Decisions
The following design decisions were made while constructing the class diagram;
 A warehouse has one or more centers.
 A distribution center can receive none or many standing orders.
 A distribution center can receive none or many special orders.
 A standing order has one or more products.
 A special order has one or more products.
 A distribution center has a stock of one or more items where each item has a quantity that
is available in stock.
 A customer can have none or one shopping list.
 A shopping list has one or more items.
 A customer can make or more orders and an order can have one or more items.
 An order results to and only one payment.
Database schema
Schema is the design representation of tables, it contains the attributes of data and how they will
be arranged in the table. Below is an example of table schema
Table customer
customerID username password address.
Table shoppinglist
customerID sweetEntry date

Pitfalls We Wish to Avoid
Redundancy and loose of data
When designing our schema, we want to do so in such a way that we minimize redundancy of
data without losing any data. By redundancy, I mean data that is repeated in different rows of a
table or in different tables in the database.
Anomalies
These are problems caused on the data because of the flaws in the database. Anomalies can occur
in three ways. We will discuss how they occur using the table schema used above.
Insertion Anomalies
This anomalies occur when we try to insert data from a referenced table which does not exist in
that table. For example, it is not possible to insert data to the shopping list using a customer Id
that does not exist in the customer table. The customer Id in the shopping list table reference the
customer id in the customer table. To avoid this anomalies we need to add data to customer table
before inserting to shopping list table.
Deletion anomalies
Imagine if we could have a table called departmentEmployee table that’s hold the employee and
the department details.
Employee ID Employee Name Department Id Department Id
Imagine if we have a deparment with the an ID 200 if we decide to delete the department using
its departmentID we will delete all employees on that department causing loss of data. To avoid
this anomalies we can create two tables department Table and Employee table.
Update anomalies
Occurs when we try to delete columns in the database that has design flaws. Using the above
schema of employeedepartment table. If we want to update the name of employee with employee
Id 23 and belongs to department id 200. The employees from the department 200 data will be
updated causing the loss of data.
Null values
When designing database its good to avoid using attributes with a null value. Imagine if we want
to add a new attribute with a null value in the customer table. If the table has 20 columns it mean
that row will have null values for all the columns.
Functional Dependencies
We need to understand the functional dependency before we understand about normalization
which we will discuss next. Afunctional dependency occurs between two columns in a given
table. If functional dependency occurs between two columns that is column X and column Y
written as X  Y the values of col X will determine the value of col Y. For example in the

customer table customerID will functionally determines the username and other attributes in the
table.
The design decisions outlined above are used as business rules in order to convert the class
diagram into a relational model (Milamila, 2014). Each relation will have to undergo
normalization up to 3NF before the final relational model is constructed (Kaula, 2007).
The normalization steps that will be performed in order to convert the class diagram to a
relational model are;
 Normalization to 1NF – This is the first step normalization step that will be carried on the
entities derived from the class diagram. This step will involve identification of repeating
groups and removing them to make sure there are no repeating groups.
 Normalization to 2NF – After finishing up on normalization to 1NF, the next step will be
taking all the relations achieved after normalizing to 1NF and identifying and removing
any partial dependencies in the relation such that each relation will have only one
candidate key.
 Normalization to 3NF – After finishing up on normalization to 2NF, the next step will
taking all the relations achieved after normalizing to 2NF and identifying and removing
any transitive dependency such that each entity will have one and only one primary key
that will functionally determine all the other attributes.
Normalization to 3NF will be the last step of normalization and the relations achieved will be
used to create the relational model.

Chapter 4: Relational model
Figure 2: Relational model
The relational model shown in figure 2 above can be converted to a data dictionary that will be
used in database script creation.
Table Attribute Data type Constraint
Warehouse warehouseID integer Primary key
Name Varchar(50)
Address Varchar(50)
Location Varchar(50)

Region Varchar(50)
tel Varchar(25)
Center centerCode Integer Primary key
Name Varchar(50)
Address Varchar(50)
Tel Varchar(50)
warehouseID integer Foreign key references
warehouse (warehouseID)
Customer Username Varchar(25) Primary key
Name Varchar(50)
Password Varchar(250)
address Varchar(100)
Product sweetCode Integer Primary key
Name Varchar(50)
Description Varchar(500)
Size Integer
noPerMultiPack Integer
costPerPack decimal
costPerIndividual decimal
Stock centerID Integer Primary key
Foreign key references Center
(centerID)
sweetCode Integer Primary key
Foreign key references
product (sweetCode)
quantityOnHand Integer
StandingOrder orderNO Integer Primary key
centerCode Integer Foreign key references Center
(centerID)
dateRevised Date
StandingOrderProduct
s
OrderNO Integer Primary key
Foreign key references
StandingOrder (orderNO)
sweetCode Integer Primary key,
Foreign key references
product (sweetcode)
Quantity Integer
frequency Varchar(20)
SpecialOrder orderNO Integer Primary key
centerCode Integer Foreign key references Center
(centerID)
orderDate Date
SpecialOrderProducts orderNO integer Primary key
Foreign key references

SpecialOrder (orderNO)
sweetCode Integer Primary key
Foreign key references
Product (sweetCode)
Quantity Integer
deliveryDate Date
ShoppingList customerID Varchar(20) Primary key,
Foreign key references
customer (username)
sweetCode Integer Primary key,
Foreign key references
product (sweetCode)
dateEntered Date
Order orderNO Integer Primary key
orderDate Date
deliveryAddress Varchar(100)
customerID Varchar(20) Foreign key references
customer (username)
OrderLine OrderNO integer Primary key,
Foreign key references Order
(orderNO)
sweetCode Integer Primary key,
Foreign key references
Product (sweetCode
quantity Integer
Payment OrderNO Integer Primary key,
Foreign key references order
(orderNO)
cardType Varchar(50)
cardNO Integer
Expiry Char(5)
Amount Decimal
debitDate Date
Chapter 5: Implementation
The following is the SQL code used to implement the database;
create table warehouse (
warehouseID integer primary key,
name varchar(50) not null,
address varchar(50) not null,
loocation varchar(50) not null,

region varchar(50) not null,
tel varchar(50) not null
);
create table center (
centerCode integer primary key,
name varchar(50) not null,
address varchar(50) not null,
tel varchar(25) not null,
warehouseID integer not null,
foreign key (warehouseID) references warehouse (warehouseID)
);
create table customer (
username varchar(20) primary key,
name varchar(50) not null,
password varchar(250) not null,
address varchar(100) not null
);
create table product (
sweetCode integer primary key,
name varchar(50) not null,
description varchar(500) not null,
productsize integer not null,
noPerMultipack integer not null,
costPerPack decimal not null,
costPerIndividual decimal not null

);
create table stock (
centerCode integer not null,
sweetCode integer not null,
quantityOnHand integer not null,
primary key (centerCode,sweetCode),
foreign key (centerCode) references center (centerCode),
foreign key (sweetCode) references product (sweetCode)
);
create table standingOrder (
orderNO integer primary key,
centerCode integer not null,
dateRevised date not null,
foreign key (centerCode) references center (centerCode)
);
create table standingOrderProducts (
orderNO integer not null,
sweetCode integer not null,
quantity integer not null,
frequency varchar(20) not null,
primary key (orderNO,sweetCode),
foreign key (orderNO) references standingOrder (orderNO),
foreign key (sweetCode) references product (sweetCode)
);
Create table specialOrder (

orderNO integer primary key,
centerCode integer not null,
orderDate date not null,
foreign key (centerCode) references center (centerCode)
);
create table specialOrderProducts (
orderNO integer not null,
sweetCode integer not null,
quantity integer not null,
deliveryDate date not null,
primary key (orderNO,sweetCode),
foreign key (orderNO) references specialOrder (orderNO),
foreign key (sweetCode) references product (sweetCode)
);
create table shoppingList (
customerID varchar(20) null,
sweetCode integer not null,
dateEntered date not null,
primary key (customerID,sweetcode),
foreign key (customerID) references customer (username),
foreign key (sweetCode) references product (sweetCode)
);
create table customerorder (
orderNO integer primary key,
orderDate date not null,

deliveryAddress varchar(100) not null,
customerID varchar(20) not null,
foreign key (customerID) references customer (username)
);
create table orderLine (
orderNO integer not null,
sweetcode integer not null,
quantity integer not null,
primary key (orderNO, sweetCode),
foreign key (orderNO) references customerorder (orderNO),
foreign key (sweetCode) references product (sweetCode)
);
create table payment (
orderNO integer primary key,
cardType varchar(50) not null,
cardNO integer not null,
expiry char(5) not null,
amount decimal not null,
debitDate date not null,
foreign key (orderNO) references customerorder (orderNO)
);
insert into warehouse (warehouseID, name, address, loocation,region,tel) values
(1001,'MaidStone','Kent MD23 7TY','Unit 7, Thame industial estate','Maidstone','01622
654234');
insert into center (centerCode,name,address,tel,warehouseID) values

('1001','Canterbury','Ahsford Rd Kent Ct4 7HF','01227 453728','1001');
insert into center (centerCode,name,address,tel,warehouseID) values
('1002','Canterbury','Stour Valley Business park Kent Ct4 7HF','01227 453728','1001');
insert into customer (username,name,password,address) values
('fred','Fred Smith','abcd1234','11 Bermoondseye drive london');
insert into customer (username,name,password,address) values
('tony','Tony Stark','abcd1234','12 Bermoondseye drive london');
insert into customer (username,name,password,address) values
('peter','Peter Grifffin','abcd1234','13 Bermoondseye drive london');
insert into customer (username,name,password,address) values
('meg','Meg Griffin','abcd1234','14 Bermoondseye drive london');
insert into customer (username,name,password,address) values
('stewie','Stewie Griffin','abcd1234','15 Bermoondseye drive london');
insert into product
(sweetCode,name,description,productsize,noPerMultipack,costPerPack,costPerIndividual) values
(1001,'Cadburys Wispa','Cadbury wispa mix',150,5,50,5);
insert into product
(sweetCode,name,description,productsize,noPerMultipack,costPerPack,costPerIndividual) values
(1002,'Cadburys Whole nut','Cadbury whole nut mix',50,10,30,5);
insert into product
(sweetCode,name,description,productsize,noPerMultipack,costPerPack,costPerIndividual) values
(1003,'Haribos milk bottles','Canned haribo milk in a bottle',100,5,200,40);
insert into product
(sweetCode,name,description,productsize,noPerMultipack,costPerPack,costPerIndividual) values
(1004,'Cadburys Cholocate Eclairs','Cadbury packaaged chocolate Eclairs',500,5,20,4);
insert into stock (centerCode, sweetCode, quantityOnHand) values

(1001,1001,100);
insert into stock (centerCode, sweetCode, quantityOnHand) values
(1001,1002,200);
insert into stock (centerCode, sweetCode, quantityOnHand) values
(1002,1003,400);
insert into stock (centerCode, sweetCode, quantityOnHand) values
(1002,1004,100);
insert into stock (centerCode, sweetCode, quantityOnHand) values
(1001,1003,100);
insert into stock (centerCode, sweetCode, quantityOnHand) values
(1001,1004,200);
insert into standingOrder (orderNO, centerCode, dateRevised)
values (1001,1001,'02-Mar-2018');
insert into standingOrder (orderNO, centerCode, dateRevised)
values (1002,1002,'20-Jan-2019');
insert into standingOrder (orderNO, centerCode, dateRevised)
values (1003,1001,'02-May-2019');
insert into standingOrder (orderNO, centerCode, dateRevised)
values (1004,1001,'02-Jan-2020');
insert into specialOrder (orderNO, centerCode, orderDate)
values (1001,1001,'02-Mar-2018');
insert into specialOrder (orderNO, centerCode, orderDate)
values (1002,1002,'20-Jan-2019');
insert into specialOrder (orderNO, centerCode, orderDate)
values (1003,1001,'02-May-2019');
insert into specialOrder (orderNO, centerCode, orderDate)

values (1004,1001,'02-Jan-2020');
insert into standingorderproducts (orderNO,sweetCode,quantity,frequency)
values (1001,1001,5,2);
insert into standingorderproducts (orderNO,sweetCode,quantity,frequency)
values (1002,1002,15,2);
insert into standingorderproducts (orderNO,sweetCode,quantity,frequency)
values (1003,1003,25,2);
insert into standingorderproducts (orderNO,sweetCode,quantity,frequency)
values (1004,1004,55,2);
insert into specialorderproducts (orderNO,sweetCode,quantity,deliveryDate)
values (1001,1001,5,'31-Dec-2019');
insert into specialorderproducts (orderNO,sweetCode,quantity,deliveryDate)
values (1002,1002,15,'31-Dec-2019');
insert into specialorderproducts (orderNO,sweetCode,quantity,deliveryDate)
values (1003,1003,25,'01-Jan-2020');
insert into specialorderproducts (orderNO,sweetCode,quantity,deliveryDate)
values (1004,1004,55,'02-Jan-2020');
insert into shoppingList (customerid,sweetCode,deliveryDate)
values ('fred',1001,'31-Dec-2019');
insert into shoppingList (customerID,sweetCode,dateEntered)
values ('meg',1002,'31-Dec-2019');
insert into shoppingList (customerID,sweetCode,dateEntered)
values ('peter',1003,'01-Jan-2020');
insert into shoppingList (customerID,sweetCode,dateEntered)
values ('fred',1004,'02-Jan-2020');

insert into customerorder (customerid,orderNO,orderDate,deliveryaddress)
values ('fred',1001,'31-Dec-2019','personal');
insert into customerorder (customerid,orderNO,orderDate,deliveryaddress)
values ('meg',1002,'31-Dec-2019','Personal');
insert into customerorder (customerid,orderNO,orderDate,deliveryaddress)
values ('peter',1003,'01-Jan-2020','Personal');
insert into customerorder (customerid,orderNO,orderDate,deliveryaddress)
values ('fred',1004,'02-Jan-2020','Personal');
insert into orderline (orderNO,sweetCode,quantity)
values (1001,1001,5);
insert into orderline (orderNO,sweetCode,quantity)
values (1002,1002,15);
insert into orderline (orderNO,sweetCode,quantity)
values (1003,1003,25);
insert into orderline (orderNO,sweetCode,quantity)
values (1004,1004,55);
insert into payment (orderNO,cardType,cardNO,expiry,amount,debitDate) values
(1001,'MasterCard','55643576345323','12/22',345,'05-Jan-2020');
insert into payment (orderNO,cardType,cardNO,expiry,amount,debitDate) values
(1002,'Visa','55643576345323','12/22',345,'05-Jan-2020');
Chapter 6: Queries
The following queries demonstrate the database meets the requirements;
 Query 1
select so.*,sop.* from standingOrder so inner join standingOrderProducts sop
on so.orderNO=sop.orderNO;

 Query 2
select so.*,sop.* from specialOrder so inner join specialOrderProducts sop
on so.orderNO=sop.orderNO;
 Query 3
select customer.*, shoppingList.* from customer inner join shoppingList
on customer.username=shoppingList.customerID;
 Query 4
select * from customer where username in (select customerID from customerorder);
 Query 5
select p.sweetCode,p.name,sum(ol.quantity) from product p
inner join orderLine ol on ol.sweetCode=p.sweetCode
group by p.sweetCode,p.name;

 Query 6
select c.centerCode,c.name,count(s.sweetCode) from center c
inner join stock s on s.centerCode=c.centerCode
group by c.centerCode,c.name
order by count(s.sweetCode);

Chapter 7: Conclusion
The process of designing a database by following the standard database design process helped
achieve quality design of the online sweet shop database. Each step in the design process is
important. Normalization of tables achieved in the relational model helps to ensure database
consistency and integrity (Guru99, 2018). There are three forms of normalization, first normal
form, second normal form and the third normal form. The integrity of data is mainly achieved at
the third normal form.
A database with consistent data means that all operations performed on the database including
inserting, updating or deletion leaves the data without any errors and inconsistencies. Data
integrity and consistency is important for every business when a database is deployed in a
production environment. This is because overtime, the business might rely on the data to make
important business decisions thus inconsistent data can alter or affect the final decisions that are
made based on the analysis of the data.
After normalizing the all the tables in the database and achieving the integrity of the database it
is good idea to come up with the data dictionary. The dictionary it is important as it gives the
user the information contained in the database, the roles and the users allowed to access it and
the physical location where the database is stored (Meador, 2018). It also define the table
constraint (primary keys and foreign keys attributes) and the visible views on the database.
Using the data dictionary we can finally create all the tables required using the Data definition
language. The language is used to create, alter or drop tables. Next the tables are manipulated
with data using the data manipulation language. The language allows user to insert, select update
or delete data. Inserting data is crucial as there is a systematic way to insert new record. First, we
need to insert data to those tables that don’t have a foreign key or to tables that do not depend on
other tables. This will create data integrity and reduce errors when querying the database.
The Online Sweet Shop database has undergone all the standard steps for a good database
design. Every step in the design process and should not be skipped as it makes validation and
verification of the database easy as well as documenting the database creation process as
demonstrated in the document.

eferences
Guru99. (2018). What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples. [online]
Available at: https://www.guru99.com/database-normalization.html [Accessed 1 Jan. 2020].
Kaula, R. (2007). Normalizing with Entity Relationship Diagramming. [online] The Data
Administration Newsletter. Available at: http://tdan.com/normalizing-with-entity-relationship-
diagramming/4583 [Accessed 1 Jan. 2020].
Millamila, M. (2014). Entity Relationship Diagrams and Normalization. [online] prezi.
Available at: https://prezi.com/tunrndt0nin4/entity-relationship-diagrams-and-normalization/
[Accessed 1 Jan. 2020].
Meador, D. (2018). What is Data Dictionary. [online] Tutorialspoint.com. Available at:
https://www.tutorialspoint.com/What-is-Data-Dictionary [Accessed 10 Jan. 2020].