Database Design and Implementation for Fashion Outlet

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,