Database Design and Implementation for Fashion Outlet

This report explains the logical and physical database design and implementation for Fashion Outlet. It includes an ER diagram, user/system requirements, data dictionary, and assessment of requirements.

HND Assignment
May 16
2019
Student ID:
Student Name:

Report
Contents
Report..........................................................................................................................................................2
Introduction.................................................................................................................................................3
LO1: Logical Database Design....................................................................................................................3
ER Diagram..............................................................................................................................................3
User/System Requirements......................................................................................................................4
Data Dictionary.......................................................................................................................................4
Assessment of User/System Requirements..............................................................................................6
LO2: Database Implementation...................................................................................................................7
Create Database.......................................................................................................................................7
Insert Statements...................................................................................................................................12
Queries..................................................................................................................................................15
LO3: Database System Testing.................................................................................................................19
LO4: Technical and User Document.........................................................................................................21
Introduction...........................................................................................................................................21
Functional Requirements.......................................................................................................................21
Non-Functional Requirements...............................................................................................................22
Tables Structure.....................................................................................................................................22
Data Flow Diagram...................................................................................................................................28
Flow of Activity in the System..............................................................................................................29
Conclusion.............................................................................................................................................32
References.................................................................................................................................................33

Introduction
The report is based upon the database design and implementation of Fashion Outlet. The logical
and physical both database designs are being shown in the report. the report is capable of
explaining everything about the Fashion Outlet system and Employee database which is storing
the entire detail of Fashion System.
LO1: Logical Database Design
ER Diagram

User/System Requirements
1. An employee can join more than one department.
2. An employee will work as a manager.
3. A manager can manage more than one department.
4. An employee may have different salary from different dates.
5. An employee may have different titles from different dates.
6. A customer can order any number of orders.
7. There may be any number of items in an order.
8. An item will be categorized in two categories- Rent and Sale Items.
9. The payment detail of customers is necessary to be stored into the database like card
number etc.
Data Dictionary
Entity Name Attributes Data Type Nulls Key
Customer Customer_id int NO Primary Key
Customer_name varchar NO
address varchar NO
Phone Integer NO
email Integer NO
Items item_id int NO Primary Key
item_name varchar NO
description varchar NO
In_stock int NO
Rent_items item_id int NO Primary Key
Foreign key
references Items
(item_id)
Rent_per_day int NO
Sale_items item_id int NO Primary Key
Foreign key
references Items
(item_id)
Rent_per_day int NO
Orders Order_id varchar NO Primary Key
Customer_id int NO Foreign key
references Items
(customer_id)

item_id int NO Foreign key
references Items
(item_id)
amount int NO
Order_date date NO
Payment Order_id varchar NO Primary Key
Card_number int NO
Card_type varchar NO
Expiry_date date NO
Employees Emp_no int NO Primary Key
Birth_date datetime NO
First_name varchar NO
Last_name varchar NO
Hire_date datetime NO
Department Dept_no Char NO Primary Key
Dept_name varchar NO
Dept_Manager Emp_no int NO Primary Key
Foreign key
references
Employees
(Emp_no)
Dept_no Char NO Primary Key
Foreign key
references
Department
(Dept_no)
From_date datetime
To_date datetime
Dept_Emp Emp_no int NO Primary Key
Foreign key
references
Employees
(Emp_no)
Dept_no Char NO Primary Key
Foreign key
references
Department
(Dept_no)
From_date datetime
To_date datetime
Salaries Emp_no int NO Primary Key
Foreign key
references
Employees
(Emp_no)
Salary int NO

From_date datetime NO Primary Key
To_date datetime
Titles Emp_no int NO Primary Key
Foreign key
references
Employees
(Emp_no)
Title varchar NO Primary Key
From_date datetime NO Primary Key
To_date datetime
Assessment of User/System Requirements
An employee can join more than one department: an employee can join more than one
department because the emp_dept table contains the primary key (emp_no, dept_no).
An employee will work as a manager: an employee will work as a manager because the foreign
key referencing
A manager can manage more than one department: a manager can manage more than one
department because the dept_manager table contains the primary key (emp_no, dept_no).
An employee may have different salary from different dates: An employee may have
different salary from different dates because the salaries table contains the primary key (emp_no,
start_date)
An employee may have different titles from different dates: An employee may have different
titles from different dates because the titles table contains the primary key (emp_no, title,
start_date)
A customer can order any number of orders: A customer can order any number of orders
because there is 1:M relationship in between customers and orders tables.
There may be any number of items in an order: There may be any number of items in an
order because there is 1:M relationship in between items and orders tables.
An item will be categorized in two categories- Rent and Sale Items.
The payment detail of customers is necessary to be stored into the database like card number etc.

LO2: Database Implementation
Create Database
DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;
SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
DROP TABLE IF EXISTS dept_emp,
dept_manager,
titles,
salaries,
employees,
departments,
customers,
items,
rent_items,
sale_items,
orders,
payment;
CREATE TABLE employees (
emp_no INT NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,

gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
);
CREATE TABLE departments (
dept_no CHAR(4) NOT NULL,
dept_name VARCHAR(40) NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE KEY (dept_name)
);
CREATE TABLE dept_manager (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,dept_no)
);
CREATE TABLE dept_emp (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,dept_no)
);
CREATE TABLE titles (
emp_no INT NOT NULL,
title VARCHAR(50) NOT NULL,
from_date DATE NOT NULL,
to_date DATE,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,title, from_date)
)
;
CREATE TABLE customers (
customer_id INT NOT NULL,
customer_name varchar(50) NOT NULL,
address varchar(50) NOT NULL,
email varchar(50) NOT NULL,
phone varchar(20) NOT NULL,
PRIMARY KEY (customer_id)
);
CREATE TABLE items (
item_id INT NOT NULL,
item_name varchar(20) NOT NULL,
description varchar(50) NOT NULL,

in_stock int NOT NULL,
PRIMARY KEY (item_id)
);
CREATE TABLE rent_items (
item_id INT NOT NULL,
rent_per_day INT NOT NULL,
FOREIGN KEY (item_id) REFERENCES items (item_id) ON DELETE CASCADE,
PRIMARY KEY (item_id)
);
CREATE TABLE sale_items (
item_id INT NOT NULL,
sale_price INT NOT NULL,
FOREIGN KEY (item_id) REFERENCES items (item_id) ON DELETE CASCADE,
PRIMARY KEY (item_id)
);
CREATE TABLE orders (
order_id INT NOT NULL,
item_id INT NOT NULL,
customer_id INT NOT NULL,
amount INT NOT NULL,
order_date date NOT NULL,
FOREIGN KEY (item_id) REFERENCES items (item_id) ON DELETE CASCADE,

FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE CASCADE,
PRIMARY KEY (order_id)
);
CREATE TABLE payment (
order_id INT NOT NULL,
card_number INT NOT NULL,
card_type varchar(20) NOT NULL,
expiry_date date NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE,
PRIMARY KEY (order_id)
);

Insert Statements
USE employees;
INSERT INTO `departments` VALUES ('d001','Marketing'),
('d002','Finance'),
('d003','Human Resources'),
('d004','Production'),('d005','Development'), ('d006','Quality Management'),('d007','Sales'),
('d008','Research'),('d009','Customer Service');
INSERT INTO `employees` VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24'),
(10011,'1953-11-07','Mary','Sluis','F','1990-01-22'),
(10012,'1960-10-04','Patricio','Bridgland','M','1992-12-18'),
(10013,'1963-06-07','Eberhardt','Terkki','M','1985-10-20'),
(10014,'1956-02-12','Berni','Genin','M','1987-03-11');
INSERT INTO `dept_emp` VALUES (10001,'d005','1986-06-26','9999-01-01'),
(10002,'d007','1996-08-03','9999-01-01'),

(10003,'d004','1995-12-03','9999-01-01'),
(10004,'d004','1986-12-01','9999-01-01'),
(10005,'d003','1989-09-12','9999-01-01'),
(10006,'d005','1990-08-05','9999-01-01'),
(10014,'d005','1993-12-29','9999-01-01');
INSERT INTO `dept_manager` VALUES (10013,'d001','1985-01-01','1991-10-01'),
(10001,'d001','1991-10-01','9999-01-01'),
(10002,'d002','1985-01-01','1989-12-17'),
(10008,'d002','1989-12-17','9999-01-01'),
(10012,'d003','1985-01-01','1992-03-21'),
(10011,'d003','1992-03-21','9999-01-01'),
(10014,'d004','1985-01-01','1988-09-09'),
(10003,'d004','1988-09-09','1992-08-02');
INSERT INTO `salaries` VALUES (10001,60117,'1986-06-26','1987-06-26'),
(10001,62102,'1987-06-26','1988-06-25'),
(10002,66074,'1988-06-25','1989-06-25'),
(10003,66596,'1989-06-25','1990-06-25'),
(10004,66961,'1990-06-25','1991-06-25'),
(10005,71046,'1991-06-25','1992-06-24'),
(10006,74333,'1992-06-24','1993-06-24'),
(10007,75286,'1993-06-24','1994-06-24'),
(10008,75994,'1994-06-24','1995-06-24');

INSERT INTO `titles` VALUES (10001,'Senior Engineer','1986-06-26','9999-01-01'),
(10002,'Staff','1996-08-03','9999-01-01'),
(10003,'Senior Engineer','1995-12-03','9999-01-01'),
(10004,'Engineer','1986-12-01','1995-12-01'),
(10004,'Senior Engineer','1995-12-01','9999-01-01'),
(10005,'Senior Staff','1996-09-12','9999-01-01'),
(10005,'Staff','1989-09-12','1996-09-12'),
(10006,'Senior Engineer','1990-08-05','9999-01-01'),
(10007,'Senior Staff','1996-02-11','9999-01-01'),
(10007,'Staff','1989-02-10','1996-02-11'),
(10008,'Assistant Engineer','1998-03-11','2000-07-31');
insert into `customers` values (10001,'Nick Jonas','NJ','[e-mail]','3462245116'),
(10002,'Max White','NJ','[e-mail]','8934245116'),
(10003,'Jack Brown','NJ','[e-mail]','9067245116'),
(10004,'Rose Smith','NJ','[e-mail]','7824245116');
insert into items values (10001,'Jeans', 'White in colour', 15),
(10002,'Vase', 'Blue in colour', 10),
(10003,'Bed', 'Brown in colour', 2),
(10004,'Jacket', 'White in colour', 10);
insert into rent_items values (10001,10),
(10004, 20);

insert into sale_items values (10002,18),
(10003, 100);
insert into orders values (10001, 10001, 10001, 15, '2019-03-03'),
(10002, 10002, 10002, 25, '2019-03-04'),
(10003, 10003, 10003, 15, '2019-03-05'),
(10004, 10004, 10004, 15, '2019-03-06');
insert into payment values (10001, 3456, 'Master', '2019-12-30'),
(10002, 3456, 'Master', '2019-12-30'),
(10003, 3456, 'Visa', '2020-12-30'),
(10004, 3456, 'Master', '2019-12-30');
Queries
Query 1
select concat(first_name, ' ' , last_name) as 'emp_name', gender, salary from employees inner join
salaries on employees.emp_no=salaries.emp_no;

Query 2
select employees.emp_no, title, dept_name from titles inner join employees on
titles.emp_no=employees.emp_no inner join dept_emp on employees.emp_no=dept_emp.emp_no
inner join departments on dept_emp.dept_no=departments.dept_no;
Query 3
select concat(first_name, ' ' , last_name) as 'emp_name', gender from employees inner join dept_emp
on employees.emp_no=dept_emp.emp_no where dept_emp.dept_no='d004';
Query 4
select dept_name, emp_no as manager from departments inner join dept_manager on
departments.dept_no=dept_manager.dept_no;

Query 5
select departments.dept_name, dept_manager.emp_no, concat(first_name, ' ' , last_name) as manager
from departments inner join dept_manager on departments.dept_no=dept_manager.dept_no inner join
employees on dept_manager.emp_no=employees.emp_no where year(employees.hire_date)>1986;
Query 6
SET SQL_SAFE_UPDATES=0;
update employees set birth_date='1987-11-21' where last_name='Simmel';
Query 7
SET SQL_SAFE_UPDATES=0;
delete from employees where employees.emp_no='10003';

Query 8
create view v_emp as
select employees.emp_no, concat(first_name, ' ' , last_name) as employee_name,
departments.dept_no, departments.dept_name, salary from departments inner join dept_emp on
departments.dept_no=dept_emp.dept_no inner join employees on
dept_emp.emp_no=employees.emp_no inner join salaries on employees.emp_no=salaries.emp_no;
select * from v_emp;

LO3: Database System Testing
Database system testing is the most important part of database design and development. There are
different types of database testing as mentioned below-
- Structural Database Testing
- Functional Testing
- Nonfunctional Testing
Structural Database Testing
Structural database testing is used to test tables and columns, database schema, views, triggers, stored
procedures, functions etc.
Functional Testing
Functional testing is used to test the functionality of the database from the user point of view. It is of two
types- white box testing and black box testing.
Nonfunctional Testing
Nonfunctional testing is used to test the risks in the database system. It deals with the stress testing, test
system performance, check minimum requirements etc. (Tutorialspoint.com 2019)
In our database structural database testing is performed. All database objects are tested in it like below-
Verify database and Devices
1. Verify the database name.
2. Verify log device, backup device etc.
3. Verify required space.

4. Verify all database settings like authorized users etc.
Verify Tables and Columns
1. Verify all tables and their names. All table names should follow the same naming
standard.
2. Verify all attributes. All table attributes should follow the same naming standard.
3. Check all null values in the tables.
4. Check all default values in the tables.
5. Verify all indexes on all tables.
6. Verify primary key and foreign keys on the tables.
Verify Views
1. Verify all views and their names. All views names should follow the same naming
standard.
2. Al views should return the exact results.
Setup Scripts
1. Setup database from scratch.
2. Setup existing database.
Integration test
1. Test whether all defined procedures are being called correctly or not.
2. Test whether all defined triggers are being called correctly or not.
3. Test whether all defined functions are being called correctly or not.

LO4: Technical and User Document
Introduction
The report is based upon the database design and implementation of a Fashion Outlet in London.
The database is implemented to store the details of customers, employees, items, orders,
payments, rented items, sale items etc.
There are different requirements of database of Fashion Outlet in London which can be
categorized into functional and non-functional requirements.
Functional Requirements
1. The employees table will have emp_no as primary key.
2. The department table will have dept_no as primary key.
3. The payment table will have order_id as primary key.
4. The payment table will have order_id as primary key.
5. The customer table will have customer_id as primary key.
6. The item table will have item_id as primary key.
7. The rent_items table will have item_id as primary key.
8. The sale_items table will have item_id as primary key.
9. The dept_manager table will have dept_no and emp_no as primary key.
10. The dept_emp table will have dept_no and emp_no as primary key.
11. The titles table will have emp_no, from_date and to_date as primary key.
12. The salaries table will have emp_no, from_date and to_date as primary key.

13. There should be feature that an employee can join more than one department.
14. The manager will be the employee also.
15. There should be feature that a manager can manage more than one department.
16. The salary of an employee can be changed from different dates.
17. An employee may have different titles from different dates.
18. There should be feature that a customer can order any number of orders.
19. There should be feature that any number of items may be included in an order.
20. There will be two types of items- Rent and Sale Items.
Non-Functional Requirements
1. The system will work automatically.
2. The system will be error free. There will be no bugs in the system.
3. There should be proper checks for data validation.
4. There should be proper security checks on the system.
5. The users should be authenticated before using the system.
6. Every user like employee, customers have different features provided in the system.
7. A customer cannot access the features of the employees.
Tables Structure
The table structures are being shown by the describe command below-
Employees Table Structure
describe employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | | |
| birth_date | date | NO | | | |

| first_name | varchar(14) | NO | | | |
| last_name | varchar(16) | NO | | | |
| gender | enum('M','F') | NO | | | |
| hire_date | date | NO | | | |
+------------+---------------+------+-----+---------+-------+
Departments Table Structure
describe departments;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no | char(4) | NO | PRI | | |
| dept_name | varchar(40) | NO | UNI | | |
+-----------+-------------+------+-----+---------+-------+
Dept_Emp Table Structure
describe dept_emp;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | | |
| dept_no | char(4) | NO | PRI | | |
| from_date | date | NO | | | |
| to_date | date | NO | | | |

+-----------+---------+------+-----+---------+-------+
Dept_Manager Table Structure
describe dept_manager;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | | |
| dept_no | char(4) | NO | PRI | | |
| from_date | date | NO | | | |
| to_date | date | NO | | | |
+-----------+---------+------+-----+---------+-------+
Titles Table Structure
describe titles;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | | |
| title | varchar(50) | NO | PRI | | |
| from_date | date | NO | PRI | | |
| to_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
Salaries Table Structure
describe salaries;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | | |
| salary | int(11) | NO | | | |

| from_date | date | NO | PRI | | |
| to_date | date | NO | | | |
+-----------+---------+------+-----+---------+-------+
Customers Table Structure
Describe Customers
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| customer_id | int(11) | NO | PRI | | |
| customer_name | varchar(50) | NO | | | |
| address | varchar(50) | NO | | | |
| email | varchar(50) | NO | | | |
| phone | varchar(20) | NO | | | |
+---------------+-------------+------+-----+---------+-------+
Items Table Structure
describe items;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| item_id | int(11) | NO | PRI | | |
| item_name | varchar(20) | NO | | | |
| description | varchar(50) | NO | | | |
| in_stock | int(11) | NO | | | |
+-------------+-------------+------+-----+---------+-------+
Rent_Items Table Structure
describe rent_items;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |

+--------------+---------+------+-----+---------+-------+
| item_id | int(11) | NO | PRI | | |
| rent_per_day | int(11) | NO | | | |
+--------------+---------+------+-----+---------+-------+
Sale_Items Table Structure
describe sale_items;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| item_id | int(11) | NO | PRI | | |
| sale_price | int(11) | NO | | | |
+------------+---------+------+-----+---------+-------+
Orders Table Structure
describe orders;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| order_id | int(11) | NO | PRI | | |
| item_id | int(11) | NO | MUL | | |
| customer_id | int(11) | NO | MUL | | |
| amount | int(11) | NO | | | |
| order_date | date | NO | | | |
+-------------+---------+------+-----+---------+-------+
Payment Table Structure
describe payment;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+

| order_id | int(11) | NO | PRI | | |
| card_number | int(11) | NO | | | |
| card_type | varchar(20) | NO | | | |
| expiry_date | date | NO | | | |
+-------------+-------------+------+-----+---------+-------+

Data Flow Diagram
(Lucid Software Inc. 2019)
(Smartdraw, LLC 2019)

There are different processes in the system. An employee can only add the items into the system
and he is the only person who can show the items on the website. A customer can view items on
the website and then can select those items and purchase them.
Flow of Activity in the System
Home Page
This is the storyboard of the home page of the website which is showing the main features of the
home page of the website. A customer can choose the items of the website by selecting the
specific categories.

Category Item Page
This is the storyboard of the page which is giving features to select items in specific category and
then add to cart those items.

Checkout Page
This is the storyboard of the checkout page of the website which is showing the important
features of the checkout page of the website. A customer has to fulfill all mandatory details
before payment.

Final Page
This is the storyboard of the final page of the website after checkout. The customer can go home
page, shopping menu page etc.
Conclusion
The technical document is showing the table structures of those tables which are define din the
employee database. The flow of activities is also being shown in the document. This document is
very beneficial to understand the entire Employee database and system.

References
Lucid Software Inc. (2019). What is a Data Flow Diagram. Retrieved from:
https://www.lucidchart.com/pages/data-flow-diagram.
Smartdraw, LLC (2019). Data Flow Diagram. [online]. Retrieved from:
https://www.smartdraw.com/data-flow-diagram/.
Tutorialspoint.com (2019). Data Testing - Types. [online]. Retrieved from:
https://www.tutorialspoint.com/database_testing/database_testing_types.htm