Fashion Outlet Database Design and Implementation Report

This report provides a comprehensive overview of the database design and implementation for a Fashion Outlet system. It begins with an introduction outlining the report's focus on both logical and physical database designs. The report covers the Entity-Relationship (ER) diagram, user and system requirements, and a detailed data dictionary. It then assesses these requirements, ensuring they align with the system's functionality. The implementation section details the creation of the database, including SQL insert statements and various queries to retrieve and manipulate data. The report also addresses database system testing, highlighting different types of testing methodologies such as structural, functional, and nonfunctional testing. Finally, the report concludes with technical and user documentation, including functional and non-functional requirements, table structures, a data flow diagram, and an explanation of the system's activity flow. The report is designed to provide a thorough understanding of database design principles and their practical application in a real-world scenario.

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'),