CI7300: Data Management & Governance - AAF Database Design

This assignment details the design and implementation of a database for "All About Furniture (AAF)", a UK-based chain of stores selling household goods, expanding into e-commerce. The assignment begins with an introduction outlining the aims and objectives of creating a database for online shopping services, enabling customers to browse and purchase products. The core of the assignment involves designing a class diagram to represent the database entities like products, customers, baskets, and orders, and then translating this into a relational model. The relational model includes the creation of tables such as Category, Sub_Category, Product, Customer, Basket, Basket_Product, Shipping_Method, Orders, and Delivery_Item, along with the definition of primary and foreign keys. The document also includes SQL scripts for creating these tables and inserting sample data. The design emphasizes normalization up to 3NF to ensure data integrity and efficiency, and the database structure supports various queries to manage product information, customer orders, and delivery details. Finally, the document describes the major design decisions, including the separation of basket and order functionalities, and the storage of product quantities and delivery dates. This database design aims to provide a secure and efficient system for AAF's online shopping platform, enhancing customer experience and business operations.

1
Table of Contents
Table of Contents .................................................................................................................................... 1
Chapter 1: Introduction ............................................................................................................................ 2
• Aims: - ........................................................................................................................................ 2
• Objectives: - ................................................................................................................................ 2
Chapter 2: The Class Diagram ................................................................................................................. 4
Chapter 3:................................................................................................................................................ 6
The Relational Model .............................................................................................................................. 7
Chapter 4: Implementation .................................................................................................................... 11
Chapter 5: Queries ................................................................................................................................. 20
Conclusion ............................................................................................................................................ 23
References: - ......................................................................................................................................... 25

2
Chapter 1: Introduction
• Aims: -
In this assignment, we are creating a database for “All About Furniture (AAF)”. This is a chain
of stores that sell various household goods from store locations throughout the UK. This
database stores information related to products, customers, their basket and orders. The aim of
this database is to provide online shopping services to the customers. We will not need to go to
the market and visit any shop for getting anything. Just create an account on this site and then the
user can view all the available products. The user can go through the products and purchase their
favorite products. Then the user can pay online for the products or they can also pay after
product delivery.
This database will simplify the overall shopping and payment process. A customer can select and
add any number of products to the bucket. Then these products can be ordered from the bucket.
There are a number of product categories and each category can have a number of subcategories.
Each subcategory can have a number of products. An online shopping application can be easily
designed using the provided class diagram and database design.
• Objectives: -
- This database stores all the information related to household goods and provides
better services to the customers.
- It provides the facilities to the customers to shop online.

3
- This database does not store information related to customer payment methods due to
privacy reasons. This information is just collected temporarily for the payment
purpose.
- This database provides full detail of all the available products like product name, their
cost, how many products are available, etc.
- By using this database, we can save our money and time.
- It will help the client in increasing the overall business. The ease in use and the
simple design will helps in growing the sales of the company significantly.
- This helps in providing secured access to users. We are using a unique combination
of username and password for authenticating each user. It will help in to increase the
overall security of the database.

4
Chapter 2: The Class Diagram
(www.tutorialspoint.com, 2019)

5
We have various classes in our object-oriented design. All the attributes and operations of the
objects are grouped together in classes. Each category can have many subcategories and each
subcategory can have various products. A basket will store all the products added by the
customer in the cart. An order can have a number of products selected from the basket. Also,
each order will have a shipping method.
Constraints and assumptions:
1. Each product will have a unique reference number and it will act as a primary key for each
product.
2. There are a number of product categories.
3. Each product category can further have a number of subcategories.
4. Each subcategory can further have a number of products.
5. One or more products can be added to the customer basket at any given time.
6. A customer can have only one basket and each basket can have zero or many products stored
in it.
7. A customer can have one or many orders and each order further can have one or many
products.
8. There is a shipping method associated with each order and it is stored along with the order
details.
9. An order will have at least one product. An order cannot exist without a product.
10. All the attributes of each class are private. So these attributes cannot be accessed directly
outside these classes. The external classes can use the getters and setters provided with each
object to access these attributes.

6
Chapter 3:
• The major design decision taken in the construction of the class diagram is that basket
and the order will be different. Each of them can have a number of products.
• We have stored the quantities of the products, delivery Dates and the orders in three
parallel arrays in the Order class. The user will have to add the required data in all these
arrays.
• We have stored the quantities of the products, added Dates and the orders in three parallel
arrays in the Basket class. The user will have to add the required data in all these arrays.
• Each user will have a unique username and they will have to use their unique username
and password to login into the application.
• The delivery date of all the products in an order will be stored differently. Two orders in
the same order can have a different delivery date.
• The quantity of the order in basket and order cannot be negative or zero. It must be a
number greater than or equal to one.
• To convert the class diagram into a relational model, I analyzed the class diagram. Then I
converted all the classes into entities/tables and class attributes into table attribute with
their data type. Then I established the relationship between the tables according to the
constructed class diagram and normalized the tables up to 3NF by Decomposing the
tables to remove many to many relationships.

7
• We have prepared a normalized database from the class diagram and it is in the third
normal form. There are no repeating groups, no partial dependency and no functional
dependency in the implemented database design.
• Our database design allows us to implement all the required queries and all the queries
are giving us the required results.
The Relational Model
--1. Category Table
CREATE TABLE Category
(
Category_No VARCHAR2 (10) PRIMARY KEY,
CG_Name VARCHAR2 (50) NOT NULL,
Description LONG
);
--2. Sub_Category Table
CREATE TABLE Sub_Category
(
SC_No VARCHAR2 (10) PRIMARY KEY,
Category_No VARCHAR2 (10) NOT NULL,
SC_Name VARCHAR2 (50) NOT NULL,
Description LONG,

8
FOREIGN KEY (Category_No) REFERENCES Category (Category_No)
);
--3. Product Table
CREATE TABLE Product
(
PReference_No VARCHAR2 (10) PRIMARY KEY,
SC_No VARCHAR2 (10) NOT NULL,
PName VARCHAR2 (50) NOT NULL,
Description LONG,
Price NUMBER (10,2) NOT NULL,
Quantity NUMBER (10) NOT NULL,
FOREIGN KEY (SC_No) REFERENCES Sub_Category (SC_No)
);
--4. Customer Table
CREATE TABLE Customer
(
UserName VARCHAR2 (10) PRIMARY KEY,
Password VARCHAR2 (10) NOT NULL,
Title VARCHAR 2(5) CHECK (Title IN ('Mr','Mrs','Miss')),
Initials VARCHAR2 (20) NOT NULL,
Last_Name VARCHAR2 (20) NOT NULL,

9
HN_Street VARCHAR2 (20) NOT NULL,
Town VARCHAR2 (20) NOT NULL,
PostCode VARCHAR2 (10) NOT NULL,
TelePhone VARCHAR2 (12) NOT NULL,
Email VARCHAR2 (50) NOT NULL
);
--5. Basket Table
CREATE TABLE Basket
(
Basket_No VARCHAR2 (10) PRIMARY KEY,
UserName VARCHAR2 (10) NOT NULL,
Create_Date DATE NOT NULL,
Status VARCHAR2 (15) CHECK (Status IN ('Ordered','Not Ordered')) NOT NULL,
FOREIGN KEY (UserName) REFERENCES Customer (UserName)
);
--6. Basket_Product Table
CREATE TABLE Basket_Product
(
Basket_No VARCHAR2 (10) NOT NULL,
PReference_No VARCHAR2 (10) NOT NULL,
Quantity NUMBER (10) NOT NULL,

10
Add_Date DATE NOT NULL,
PRIMARY KEY (Basket_No, PReference_No),
FOREIGN KEY (Basket_No) REFERENCES Basket (Basket_No),
FOREIGN KEY (PReference_No) REFERENCES Product (PReference_No)
);
--7. Shipping_Method Table
CREATE TABLE Shipping_Method
(
SM_No VARCHAR2 (10) PRIMARY KEY,
SM_Name VARCHAR2 (50) NOT NULL,
Charges NUMBER (10,2) NOT NULL
);
--8. Orders Table
CREATE TABLE Orders
(
Order_No VARCHAR2 (10) PRIMARY KEY,
Order_Date DATE NOT NULL,
PostPack_Insurance NUMBER (10,2) NOT NULL,
Despatch_To VARCHAR2 (50) NOT NULL,
Total_Amount NUMBER (10,2) NOT NULL,
SM_No VARCHAR2 (10),

11
FOREIGN KEY (SM_No) REFERENCES Shipping_Method (SM_No),
FOREIGN KEY (Order_No) REFERENCES Basket (Basket_No)
);
--9. Delivery_Item Table
CREATE TABLE Delivery_Item
(
Basket_No VARCHAR2 (10) NOT NULL,
PReference_No VARCHAR2 (10) NOT NULL,
Expected_Date DATE NOT NULL,
Delivery_Date DATE,
PRIMARY KEY (Basket_No, PReference_No),
FOREIGN KEY (Basket_No, PReference_No) REFERENCES Basket_Product (Basket_No,
PReference_No)
);
Chapter 4: Implementation
-----------------CREATE TABLES ------------------
-----------------1. Category Table---------------
CREATE TABLE Category
(
Category_No VARCHAR2 (10) PRIMARY KEY,
CG_Name VARCHAR2 (50) NOT NULL,

12
Description LONG
);
----------------2. Sub_Category Table--------------
CREATE TABLE Sub_Category
(
SC_No VARCHAR2 (10) PRIMARY KEY,
Category_No VARCHAR2 (10) NOT NULL,
SC_Name VARCHAR2 (50) NOT NULL,
Description LONG,
FOREIGN KEY (Category_No) REFERENCES Category (Category_No)
);
----------------3. Product Table---------------
CREATE TABLE Product
(
PReference_No VARCHAR2 (10) PRIMARY KEY,
SC_No VARCHAR2 (10) NOT NULL,
PName VARCHAR2 (50) NOT NULL,
Description LONG,
Price NUMBER (10,2) NOT NULL,
Quantity NUMBER (10) NOT NULL,
FOREIGN KEY (SC_No) REFERENCES Sub_Category (SC_No)
);