EER Modelling and SQL Project for Database Systems - IMAT5103

This project focuses on EER modeling and SQL implementation for a retailer database named ABC Fashion. It covers case selection, enhanced entity-relationship diagram creation, logical database design, table creation with constraints, index creation, data population, and SQL query examples. The database design includes tables for address types, addresses, brands, customers, customer orders, departments, employees, inventory, job types, order lines, payslips, products, stores, and store departments. It also uses sequences and triggers for automatic ID generation and includes SQL statements for creating tables, indexes, and populating data, demonstrating a comprehensive approach to database design and implementation. Desklib provides this and many other solved assignments for students.

Running head: EER MODELLING AND SQL
EER Modelling and SQL
Name of the Student
Name of the University
Author’s Note:

1EER MODELLING AND SQL
Table of Contents
Stage 1:.......................................................................................................................................2
Task 1.1: Case Selection........................................................................................................2
Task 1.2 Enhanced Entity Relationship Diagram:.................................................................3
Stage 2:.......................................................................................................................................4
Task 2.1 Logical Database Design:........................................................................................4
Task 2.2 Table Creation:........................................................................................................4
Task 2.3 Index Creation:......................................................................................................18
Task 2.4 Data Population:....................................................................................................19
Task 2.5 SQL Query:...........................................................................................................25
Bibliography:............................................................................................................................27

2EER MODELLING AND SQL
Stage 1:
Task 1.1: Case Selection
The database is proposed for a retailer organization named ABC Fashion. The
organization has headquarter which controls all the activities such as internal operation and
sales. The case study not only describes the sales of the products but also provide a detailed
insight of internal processes. The case study is mainly based on the employee management,
branch maintenance and product stock handling. The sales of the products is for supporting
overall idea of the organization. The employee will be working in a branch which has a super
visor. To make the internal processes more clear the organization has chosen the supervisor
from the employees. This categorizes the employees in two categories such as regular and
supervisor. The organization wants to store the address of each user such as employee and
customer separately. The organization sales fashion products like shirts, pants and many
more.
The organizations executes its processes based on the stores in various locations. Each
of the stores has its own inventory. The products are sent to the customers from each of these
stores. The organization wants to store the details of the store from which the products are
sent to customers. The organizations also has different types of jobs. ABC fashion wants to
record new job types, find the stored job types and modify those job types through the
system. The organization wants to have only one supervisor of a department at a time. ABC
is not concerned with finding the past information regarding who was supervisor. The
organization wants only the information on who is currently supervising a store. Regarding
the sales, ABC wants to store the customer orders and the products ordered in the same order.
The inventory related data will be stored for each individual store. This is because the
organization stores product individually.

3EER MODELLING AND SQL
Task 1.2 Enhanced Entity Relationship Diagram:
The Enhanced Entity Relationship diagram or EER diagram is considered to be the
extended version of the logical database model named Entity Relationship diagram. The EER
models assists in designing the database that has high-level requirements. The EER diagram
shows the generalization and specialization.
Figure 1: Enhanced Entity Relationship Diagram
(Source: Created by Author)

4EER MODELLING AND SQL
Stage 2:
Task 2.1 Logical Database Design:
Figure 2: Logical Database Design of Retail Store Wide Database
(Source: Created by Author)
Task 2.2 Table Creation:
CREATE DATABASE bigm;
CREATE TABLE p17244121_addresstype (
AddType_ID char(1) NOT NULL,
Add_Type varchar(15) NOT NULL,
PRIMARY KEY (AddType_ID)
);

5EER MODELLING AND SQL
CREATE TABLE p17244121_address (
Address_ID number(10) NOT NULL,
Address_St varchar2(50) NOT NULL,
Address_City varchar2(30) NOT NULL,
Address_State varchar2(3) NOT NULL,
Address_Postcode char(4) NOT NULL,
Add_TypeID char(1) NOT NULL,
PRIMARY KEY (Address_ID),
FOREIGN KEY (Add_TypeID) REFERENCES p17244121_addresstype (AddType_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_address_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_address_seq_tr
BEFORE INSERT ON p17244121_address FOR EACH ROW
WHEN (NEW.Address_ID IS NULL)
BEGIN
SELECT p17244121_address_seq.NEXTVAL INTO :NEW.Address_ID FROM DUAL;
END;

6EER MODELLING AND SQL
/
CREATE TABLE p17244121_brand (
brand_ID number(10) NOT NULL,
brand_Name varchar2(30) NOT NULL,
PRIMARY KEY (brand_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_brand_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_brand_seq_tr
BEFORE INSERT ON p17244121_brand FOR EACH ROW
WHEN (NEW.brand_ID IS NULL)
BEGIN
SELECT p17244121_brand_seq.NEXTVAL INTO :NEW.brand_ID FROM DUAL;
END;
/
CREATE TABLE p17244121_customer (
Cust_Number number(10) NOT NULL,
Cust_FName varchar2(30) NOT NULL,

7EER MODELLING AND SQL
Cust_LName varchar2(30) NOT NULL,
Cust_Phone char(10) NOT NULL,
Address_ID number(10) NOT NULL,
PRIMARY KEY (Cust_Number),
FOREIGN KEY (Address_ID) REFERENCES p17244121_address (Address_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_customer_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_customer_seq_tr
BEFORE INSERT ON p17244121_customer FOR EACH ROW
WHEN (NEW.Cust_Number IS NULL)
BEGIN
SELECT p17244121_customer_seq.NEXTVAL INTO :NEW.Cust_Number FROM DUAL;
END;
/
CREATE TABLE p17244121_customerorder (
CustOrd_ID number(10) NOT NULL,
CustOrd_Date date NOT NULL,

8EER MODELLING AND SQL
Cust_Number number(10) NOT NULL,
Str_Num number(10) NOT NULL,
PRIMARY KEY (CustOrd_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_customerorder_seq START WITH 1 INCREMENT BY
1;
CREATE OR REPLACE TRIGGER p17244121_customerorder_seq_tr
BEFORE INSERT ON p17244121_customerorder FOR EACH ROW
WHEN (NEW.CustOrd_ID IS NULL)
BEGIN
SELECT p17244121_customerorder_seq.NEXTVAL INTO :NEW.CustOrd_ID FROM
DUAL;
END;
/
CREATE TABLE p17244121_departments (
Dept_ID number(10) NOT NULL,
Dept_Name varchar2(40) NOT NULL,

9EER MODELLING AND SQL
PRIMARY KEY (Dept_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_departments_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_departments_seq_tr
BEFORE INSERT ON p17244121_departments FOR EACH ROW
WHEN (NEW.Dept_ID IS NULL)
BEGIN
SELECT p17244121_departments_seq.NEXTVAL INTO :NEW.Dept_ID FROM DUAL;
END;
/
CREATE TABLE p17244121_employee (
Emp_ID number(10) NOT NULL,
Emp_FName varchar2(30) NOT NULL,
Emp_LName varchar2(30) NOT NULL,
Emp_Phone char(10) NOT NULL,
Emp_DoB date NOT NULL,
Emp_StartDate date NOT NULL,

10EER MODELLING AND SQL
Emp_TaxFNum char(12) NOT NULL,
Emp_HourlySalary binary_double NOT NULL,
StrDept_ID number(10) NOT NULL,
SupvisorID number(10) NOT NULL,
Address_ID_Postal number(10) NOT NULL,
Address_ID_Resident number(10) NOT NULL,
Emp_jobtypeID number(10) NOT NULL,
PRIMARY KEY (Emp_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_employee_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_employee_seq_tr
BEFORE INSERT ON p17244121_employee FOR EACH ROW
WHEN (NEW.Emp_ID IS NULL)
BEGIN
SELECT p17244121_employee_seq.NEXTVAL INTO :NEW.Emp_ID FROM DUAL;
END;
/

11EER MODELLING AND SQL
CREATE TABLE p17244121_inventory (
productNum number(10) NOT NULL,
Str_Num number(10) NOT NULL,
Inv_QntyOnHand number(10) NOT NULL,
Inv_QtyOrdered number(10)) NOT NULL,
PRIMARY KEY (productNum,Str_Num)
);
CREATE TABLE p17244121_jobtype (
jobtype_ID number(10) NOT NULL,
jobtype_Name varchar2(30) NOT NULL,
PRIMARY KEY (jobtype_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_jobtype_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_jobtype_seq_tr
BEFORE INSERT ON p17244121_jobtype FOR EACH ROW
WHEN (NEW.jobtype_ID IS NULL)
BEGIN