EER Modelling and SQL Project for Database Systems - IMAT5103
VerifiedAdded on 2023/04/21
|28
|4628
|89
Project
AI Summary
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:
EER Modelling and SQL
Name of the Student
Name of the University
Author’s Note:
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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
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.
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)
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)
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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)
);
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;
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,
/
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,
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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,
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,
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,
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,
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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;
/
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
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
12EER MODELLING AND SQL
SELECT p17244121_jobtype_seq.NEXTVAL INTO :NEW.jobtype_ID FROM DUAL;
END;
/
CREATE TABLE p17244121_orderline (
CustOrd_ID number(10) NOT NULL,
Prod_Num number(10) NOT NULL,
OrdLn_DateArrived Date NOT NULL,
OrdLn_DatePicked Date NOT NULL,
OrdLn_Qnty number(10) NOT NULL,
PRIMARY KEY (CustOrd_ID, Prod_Num)
);
CREATE TABLE p17244121_payslip (
Pay_ID number(10) NOT NULL,
Pay_date date NOT NULL,
Pay_num_of_hours binary_double NOT NULL,
Pay_amount_gross binary_double NOT NULL,
Emp_ID number(10) NOT NULL,
Str_Num number(10) NOT NULL,
PRIMARY KEY (Pay_ID)
) ;
SELECT p17244121_jobtype_seq.NEXTVAL INTO :NEW.jobtype_ID FROM DUAL;
END;
/
CREATE TABLE p17244121_orderline (
CustOrd_ID number(10) NOT NULL,
Prod_Num number(10) NOT NULL,
OrdLn_DateArrived Date NOT NULL,
OrdLn_DatePicked Date NOT NULL,
OrdLn_Qnty number(10) NOT NULL,
PRIMARY KEY (CustOrd_ID, Prod_Num)
);
CREATE TABLE p17244121_payslip (
Pay_ID number(10) NOT NULL,
Pay_date date NOT NULL,
Pay_num_of_hours binary_double NOT NULL,
Pay_amount_gross binary_double NOT NULL,
Emp_ID number(10) NOT NULL,
Str_Num number(10) NOT NULL,
PRIMARY KEY (Pay_ID)
) ;
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
13EER MODELLING AND SQL
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_payslip_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_payslip_seq_tr
BEFORE INSERT ON p17244121_payslip FOR EACH ROW
WHEN (NEW.Pay_ID IS NULL)
BEGIN
SELECT p17244121_payslip_seq.NEXTVAL INTO :NEW.Pay_ID FROM DUAL;
END;
/
CREATE TABLE p17244121_product (
Prod_Num number(10) NOT NULL,
Prod_Desc varchar2(50) NOT NULL,
Prod_Size varchar2(10) NOT NULL,
Prod_Price binary_double NOT NULL,
Prod_brandID number(10) NOT NULL,
PRIMARY KEY (Prod_Num),
FOREIGN KEY (Prod_brandID) REFERENCES p17244121_brand (brand_ID)
) ;
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_payslip_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_payslip_seq_tr
BEFORE INSERT ON p17244121_payslip FOR EACH ROW
WHEN (NEW.Pay_ID IS NULL)
BEGIN
SELECT p17244121_payslip_seq.NEXTVAL INTO :NEW.Pay_ID FROM DUAL;
END;
/
CREATE TABLE p17244121_product (
Prod_Num number(10) NOT NULL,
Prod_Desc varchar2(50) NOT NULL,
Prod_Size varchar2(10) NOT NULL,
Prod_Price binary_double NOT NULL,
Prod_brandID number(10) NOT NULL,
PRIMARY KEY (Prod_Num),
FOREIGN KEY (Prod_brandID) REFERENCES p17244121_brand (brand_ID)
) ;
14EER MODELLING AND SQL
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_product_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_product_seq_tr
BEFORE INSERT ON p17244121_product FOR EACH ROW
WHEN (NEW.Prod_Num IS NULL)
BEGIN
SELECT p17244121_product_seq.NEXTVAL INTO :NEW.Prod_Num FROM DUAL;
END;
/
CREATE TABLE p17244121_store (
Str_Num number(10) NOT NULL,
Str_Name varchar2(50) NOT NULL,
Str_Phone char(10) NOT NULL,
Str_Fax char(10) NOT NULL,
Str_Email varchar2(40) NOT NULL,
storeManagerID number(10) NOT NULL,
Supp_store_Num number(10) NOT NULL,
Address_ID number(10) NOT NULL,
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_product_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_product_seq_tr
BEFORE INSERT ON p17244121_product FOR EACH ROW
WHEN (NEW.Prod_Num IS NULL)
BEGIN
SELECT p17244121_product_seq.NEXTVAL INTO :NEW.Prod_Num FROM DUAL;
END;
/
CREATE TABLE p17244121_store (
Str_Num number(10) NOT NULL,
Str_Name varchar2(50) NOT NULL,
Str_Phone char(10) NOT NULL,
Str_Fax char(10) NOT NULL,
Str_Email varchar2(40) NOT NULL,
storeManagerID number(10) NOT NULL,
Supp_store_Num number(10) NOT NULL,
Address_ID number(10) NOT NULL,
15EER MODELLING AND SQL
PRIMARY KEY (Str_Num)
) ;
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_store_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_store_seq_tr
BEFORE INSERT ON p17244121_store FOR EACH ROW
WHEN (NEW.Str_Num IS NULL)
BEGIN
SELECT p17244121_store_seq.NEXTVAL INTO :NEW.Str_Num FROM DUAL;
END;
/
CREATE TABLE p17244121_storeDepartment (
StrDept_ID number(10) NOT NULL,
StrDept_Phone char(10) NOT NULL,
StrDept_Email varchar2(40) NOT NULL,
DeptSupervisorID number(10) NOT NULL,
Str_Num number(10) NOT NULL,
Dept_ID number(10) NOT NULL,
PRIMARY KEY (Str_Num)
) ;
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_store_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER p17244121_store_seq_tr
BEFORE INSERT ON p17244121_store FOR EACH ROW
WHEN (NEW.Str_Num IS NULL)
BEGIN
SELECT p17244121_store_seq.NEXTVAL INTO :NEW.Str_Num FROM DUAL;
END;
/
CREATE TABLE p17244121_storeDepartment (
StrDept_ID number(10) NOT NULL,
StrDept_Phone char(10) NOT NULL,
StrDept_Email varchar2(40) NOT NULL,
DeptSupervisorID number(10) NOT NULL,
Str_Num number(10) NOT NULL,
Dept_ID number(10) NOT NULL,
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
16EER MODELLING AND SQL
PRIMARY KEY (StrDept_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_storeDepartment_seq START WITH 1 INCREMENT
BY 1;
CREATE OR REPLACE TRIGGER p17244121_sD_seq_tr
BEFORE INSERT ON p17244121_storeDepartment FOR EACH ROW
WHEN (NEW.StrDept_ID IS NULL)
BEGIN
SELECT p17244121_storeDepartment_seq.NEXTVAL INTO :NEW.StrDept_ID FROM
DUAL;
END;
ALTER TABLE p17244121_customerorder
ADD CONSTRAINT cufk_1 FOREIGN KEY (Cust_Number) REFERENCES
p17244121_customer (Cust_Number),
ADD CONSTRAINT cufk_2 FOREIGN KEY (Str_Num) REFERENCES p17244121_store
(Str_Num);
ALTER TABLE p17244121_employee
PRIMARY KEY (StrDept_ID)
);
-- Generate ID using sequence and trigger
CREATE SEQUENCE p17244121_storeDepartment_seq START WITH 1 INCREMENT
BY 1;
CREATE OR REPLACE TRIGGER p17244121_sD_seq_tr
BEFORE INSERT ON p17244121_storeDepartment FOR EACH ROW
WHEN (NEW.StrDept_ID IS NULL)
BEGIN
SELECT p17244121_storeDepartment_seq.NEXTVAL INTO :NEW.StrDept_ID FROM
DUAL;
END;
ALTER TABLE p17244121_customerorder
ADD CONSTRAINT cufk_1 FOREIGN KEY (Cust_Number) REFERENCES
p17244121_customer (Cust_Number),
ADD CONSTRAINT cufk_2 FOREIGN KEY (Str_Num) REFERENCES p17244121_store
(Str_Num);
ALTER TABLE p17244121_employee
17EER MODELLING AND SQL
ADD CONSTRAINT empfk_1 FOREIGN KEY (StrDept_ID) REFERENCES
p17244121_storeDepartment (StrDept_ID),
ADD CONSTRAINT empfk_2 FOREIGN KEY (SupvisorID) REFERENCES
p17244121_employee (Emp_ID),
ADD CONSTRAINT empfk_3 FOREIGN KEY (Address_ID_Postal) REFERENCES
p17244121_address (Address_ID),
ADD CONSTRAINT empfk_4 FOREIGN KEY (Address_ID_Resident) REFERENCES
p17244121_address (Address_ID),
ADD CONSTRAINT empfk_5 FOREIGN KEY (Emp_jobtypeID) REFERENCES
p17244121_jobtype (jobtype_ID);
ALTER TABLE p17244121_orderline
ADD CONSTRAINT ofk_1 FOREIGN KEY (CustOrd_ID) REFERENCES
p17244121_customerorder (CustOrd_ID),
ADD CONSTRAINT ofk_2 FOREIGN KEY (Prod_Num) REFERENCES
p17244121_product (Prod_Num);
ALTER TABLE p17244121_payslip
ADD CONSTRAINT pfk_1 FOREIGN KEY (Emp_ID) REFERENCES
p17244121_employee (Emp_ID),
ADD CONSTRAINT pfk_2 FOREIGN KEY (Str_Num) REFERENCES p17244121_store
(Str_Num);
ALTER TABLE p17244121_store
ADD CONSTRAINT empfk_1 FOREIGN KEY (StrDept_ID) REFERENCES
p17244121_storeDepartment (StrDept_ID),
ADD CONSTRAINT empfk_2 FOREIGN KEY (SupvisorID) REFERENCES
p17244121_employee (Emp_ID),
ADD CONSTRAINT empfk_3 FOREIGN KEY (Address_ID_Postal) REFERENCES
p17244121_address (Address_ID),
ADD CONSTRAINT empfk_4 FOREIGN KEY (Address_ID_Resident) REFERENCES
p17244121_address (Address_ID),
ADD CONSTRAINT empfk_5 FOREIGN KEY (Emp_jobtypeID) REFERENCES
p17244121_jobtype (jobtype_ID);
ALTER TABLE p17244121_orderline
ADD CONSTRAINT ofk_1 FOREIGN KEY (CustOrd_ID) REFERENCES
p17244121_customerorder (CustOrd_ID),
ADD CONSTRAINT ofk_2 FOREIGN KEY (Prod_Num) REFERENCES
p17244121_product (Prod_Num);
ALTER TABLE p17244121_payslip
ADD CONSTRAINT pfk_1 FOREIGN KEY (Emp_ID) REFERENCES
p17244121_employee (Emp_ID),
ADD CONSTRAINT pfk_2 FOREIGN KEY (Str_Num) REFERENCES p17244121_store
(Str_Num);
ALTER TABLE p17244121_store
18EER MODELLING AND SQL
ADD CONSTRAINT sfk_1 FOREIGN KEY (storeManagerID) REFERENCES
p17244121_employee (Emp_ID),
ADD CONSTRAINT sfk_2 FOREIGN KEY (Supp_store_Num) REFERENCES
p17244121_store (Str_Num),
ADD CONSTRAINT sfk_3 FOREIGN KEY (Address_ID) REFERENCES
p17244121_address (Address_ID);
ALTER TABLE p17244121_storeDepartments
ADD CONSTRAINT stfk_1 FOREIGN KEY (DeptSupervisorID) REFERENCES
p17244121_employee (Emp_ID),
ADD CONSTRAINT stfk_2 FOREIGN KEY (Str_Num) REFERENCES p17244121_store
(Str_Num),
ADD CONSTRAINT stfk_3 FOREIGN KEY (Dept_ID) REFERENCES
p17244121_department (Dept_ID);
ALTER TABLE p17244121_inventory
ADD CONSTRAINT infk_1 FOREIGN KEY (productNum) REFERENCES
p17244121_product (Prod_Num),
ADD CONSTRAINT infk_2 FOREIGN KEY (Str_Num) REFERENCES p17244121_store
(Str_Num);
/
Task 2.3 Index Creation:
CREATE INDEX idx_employee on p17244121_employee (Emp_FName, Emp_LName)
TABLESPACE Users;
ADD CONSTRAINT sfk_1 FOREIGN KEY (storeManagerID) REFERENCES
p17244121_employee (Emp_ID),
ADD CONSTRAINT sfk_2 FOREIGN KEY (Supp_store_Num) REFERENCES
p17244121_store (Str_Num),
ADD CONSTRAINT sfk_3 FOREIGN KEY (Address_ID) REFERENCES
p17244121_address (Address_ID);
ALTER TABLE p17244121_storeDepartments
ADD CONSTRAINT stfk_1 FOREIGN KEY (DeptSupervisorID) REFERENCES
p17244121_employee (Emp_ID),
ADD CONSTRAINT stfk_2 FOREIGN KEY (Str_Num) REFERENCES p17244121_store
(Str_Num),
ADD CONSTRAINT stfk_3 FOREIGN KEY (Dept_ID) REFERENCES
p17244121_department (Dept_ID);
ALTER TABLE p17244121_inventory
ADD CONSTRAINT infk_1 FOREIGN KEY (productNum) REFERENCES
p17244121_product (Prod_Num),
ADD CONSTRAINT infk_2 FOREIGN KEY (Str_Num) REFERENCES p17244121_store
(Str_Num);
/
Task 2.3 Index Creation:
CREATE INDEX idx_employee on p17244121_employee (Emp_FName, Emp_LName)
TABLESPACE Users;
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
19EER MODELLING AND SQL
CREATE INDEX idx_address on p17244121_address (Address_St, Address_City,
Address_State, Address_Postcode) TABLESPACE Users;
CREATE INDEX idx_product on p17244121_product (Prod_Price, Prod_Desc)
TABLESPACE Users;
CREATE INDEX idx_customer on p17244121_customer (Cust_FName, Cust_LName)
TABLESPACE Users;
Task 2.4 Data Population:
INSERT INTO p17244121_addresstype VALUES ('R', 'Residential');
INSERT INTO p17244121_addresstype VALUES ('P', 'Postal');
INSERT INTO p17244121_addresstype VALUES ('B', 'Both');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('73 Kaesler Road', 'WEPAR', 'SA', '6207', 'P');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('43 Sydney Road', 'WINDEYER', 'SA', '2850',
'R');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('8 Gaggin Street', 'WINDERMERE', 'NSW',
'2321', 'B');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('69 Boorie Road', 'WIGTON', 'SA', '4612', 'B');
CREATE INDEX idx_address on p17244121_address (Address_St, Address_City,
Address_State, Address_Postcode) TABLESPACE Users;
CREATE INDEX idx_product on p17244121_product (Prod_Price, Prod_Desc)
TABLESPACE Users;
CREATE INDEX idx_customer on p17244121_customer (Cust_FName, Cust_LName)
TABLESPACE Users;
Task 2.4 Data Population:
INSERT INTO p17244121_addresstype VALUES ('R', 'Residential');
INSERT INTO p17244121_addresstype VALUES ('P', 'Postal');
INSERT INTO p17244121_addresstype VALUES ('B', 'Both');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('73 Kaesler Road', 'WEPAR', 'SA', '6207', 'P');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('43 Sydney Road', 'WINDEYER', 'SA', '2850',
'R');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('8 Gaggin Street', 'WINDERMERE', 'NSW',
'2321', 'B');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('69 Boorie Road', 'WIGTON', 'SA', '4612', 'B');
20EER MODELLING AND SQL
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('64 Whitehaven Crescent', 'REDBANK', 'SA',
'4865', 'R');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Burberry');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Hermes');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Prada');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Louis Vuitton');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Ralph Lauren');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Xavier', 'Whinham', '0249595440', '2');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Hannah', 'Loder', '0253208566', '4');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Alexis', 'Bracegirdle', '0582252056', '1');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Ebony', 'Boehm', '0361908874', '3');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Scarlett', 'Angel', '0245204058', '5');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Management');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Accounts');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Maintenance');
INSERT INTO p17244121_address (Address_St, Address_City, Address_State,
Address_Postcode, Add_TypeID) VALUES ('64 Whitehaven Crescent', 'REDBANK', 'SA',
'4865', 'R');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Burberry');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Hermes');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Prada');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Louis Vuitton');
INSERT INTO p17244121_brand (Brand_Name) VALUES ('Ralph Lauren');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Xavier', 'Whinham', '0249595440', '2');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Hannah', 'Loder', '0253208566', '4');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Alexis', 'Bracegirdle', '0582252056', '1');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Ebony', 'Boehm', '0361908874', '3');
INSERT INTO p17244121_customer (Cust_FName, Cust_LName, Cust_Phone,
Address_ID) VALUES ('Scarlett', 'Angel', '0245204058', '5');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Management');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Accounts');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Maintenance');
21EER MODELLING AND SQL
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Warehouse');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Sales');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('Formal Shirt', 'XXL', '50.50', '5');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('Casual Shirt', 'L', '75.75', '3');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('T-Shirt', '44', '80.00', '2');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('Shirt', 'XL', '78.80', '3');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('Jacket', 'Normal', '50.45', '4');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('ABC', '0362268320',
'2123796407', 'LucyEdgerton@dayrep.com', '1', '1', '2');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('DEF', '0745333277',
'7758123696', 'JakeMountford@dayrep.com', '1', '1', '5');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('GHI', '0267868042',
'7966069271', 'AlicaSchaw@jourrapide.com', '2', '1', '2');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Warehouse');
INSERT INTO p17244121_departments (Dept_Name) VALUES ('Sales');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('Formal Shirt', 'XXL', '50.50', '5');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('Casual Shirt', 'L', '75.75', '3');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('T-Shirt', '44', '80.00', '2');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('Shirt', 'XL', '78.80', '3');
INSERT INTO p17244121_product (Prod_Desc, Prod_Size, Prod_Price, Prod_BrandID)
VALUES ('Jacket', 'Normal', '50.45', '4');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('ABC', '0362268320',
'2123796407', 'LucyEdgerton@dayrep.com', '1', '1', '2');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('DEF', '0745333277',
'7758123696', 'JakeMountford@dayrep.com', '1', '1', '5');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('GHI', '0267868042',
'7966069271', 'AlicaSchaw@jourrapide.com', '2', '1', '2');
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
22EER MODELLING AND SQL
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('JKL', '0887334122',
'1777786201', 'JaxonPackham@teleworm.us', '2', '1', '5');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('MNO', '0353013493',
'0814523328', 'TaylaFehon@rhyta.com', '2', '1', '4');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Manager');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Accountant');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Janitor');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Warehouse Staff');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Sales Person');
INSERT INTO p17244121_storeDepartment (StrDept_Phone, StrDept_Email,
DeptSupervisorID, Str_Num, Dept_ID) VALUES ('0353013493',
'TaylahWetherspoon@armyspy.com', '1', '1', '1');
INSERT INTO p17244121_storeDepartment (StrDept_Phone, StrDept_Email,
DeptSupervisorID, Str_Num, Dept_ID) VALUES ('0249340051', 'KaylaLouat@dayrep.com',
'2', '5', '2');
INSERT INTO p17244121_storeDepartment (StrDept_Phone, StrDept_Email,
DeptSupervisorID, Str_Num, Dept_ID) VALUES ('0240801182',
'BethanyGooseberry@dayrep.com', '2', '2', '3');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('JKL', '0887334122',
'1777786201', 'JaxonPackham@teleworm.us', '2', '1', '5');
INSERT INTO p17244121_store (Str_Name, Str_Phone, Str_Fax, Str_Email,
StoreManagerID, Supp_store_Num, Address_ID) VALUES ('MNO', '0353013493',
'0814523328', 'TaylaFehon@rhyta.com', '2', '1', '4');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Manager');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Accountant');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Janitor');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Warehouse Staff');
INSERT INTO p17244121_jobtype (JobType_Name) VALUES ('Sales Person');
INSERT INTO p17244121_storeDepartment (StrDept_Phone, StrDept_Email,
DeptSupervisorID, Str_Num, Dept_ID) VALUES ('0353013493',
'TaylahWetherspoon@armyspy.com', '1', '1', '1');
INSERT INTO p17244121_storeDepartment (StrDept_Phone, StrDept_Email,
DeptSupervisorID, Str_Num, Dept_ID) VALUES ('0249340051', 'KaylaLouat@dayrep.com',
'2', '5', '2');
INSERT INTO p17244121_storeDepartment (StrDept_Phone, StrDept_Email,
DeptSupervisorID, Str_Num, Dept_ID) VALUES ('0240801182',
'BethanyGooseberry@dayrep.com', '2', '2', '3');
23EER MODELLING AND SQL
INSERT INTO p17244121_storeDepartment (StrDept_Phone, StrDept_Email,
DeptSupervisorID, Str_Num, Dept_ID) VALUES ('0283946076', 'BlakeDuffy@rhyta.com',
'2', '1', '4');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Hugo', 'Sticht',
'0553158030', '1991-10-25', '2017-02-14', '1547353495', '40.00', '1', '1', '2', '1', '1');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Lara', 'Pelensky',
'0539560648', '1984-07-14', '2015-12-10', '8535111801', '39.50', '1', '2', '5', '3', '3');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Makayla',
'Hartigan', '0291999635', '1988-1-15', '1998-1-15', '8230489342', '17.00', '2', '1', '2', '3', '2');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Lincoln',
'Bruntnell', '0253967501', '1981-11-23', '2009-02-28', '738989342', '16.00', '1', '2', '2', '3', '2');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Lola', 'Boucaut',
'0253859928', '1976-09-12', '2010-03-19', '9353859928', '21.00', '1', '2', '3', '4', '1');
INSERT INTO p17244121_storeDepartment (StrDept_Phone, StrDept_Email,
DeptSupervisorID, Str_Num, Dept_ID) VALUES ('0283946076', 'BlakeDuffy@rhyta.com',
'2', '1', '4');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Hugo', 'Sticht',
'0553158030', '1991-10-25', '2017-02-14', '1547353495', '40.00', '1', '1', '2', '1', '1');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Lara', 'Pelensky',
'0539560648', '1984-07-14', '2015-12-10', '8535111801', '39.50', '1', '2', '5', '3', '3');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Makayla',
'Hartigan', '0291999635', '1988-1-15', '1998-1-15', '8230489342', '17.00', '2', '1', '2', '3', '2');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Lincoln',
'Bruntnell', '0253967501', '1981-11-23', '2009-02-28', '738989342', '16.00', '1', '2', '2', '3', '2');
INSERT INTO p17244121_employee (Emp_FName, Emp_LName, Emp_Phone, Emp_DoB,
Emp_StartDate, Emp_TaxFNum, Emp_HourlySalary, StrDept_ID, SupvisorID,
Address_ID_Postal, Address_ID_Resident, Emp_JobTypeID) VALUES ('Lola', 'Boucaut',
'0253859928', '1976-09-12', '2010-03-19', '9353859928', '21.00', '1', '2', '3', '4', '1');
24EER MODELLING AND SQL
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-03-18', '1', '1');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-05-08', '2', '2');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-09-18', '3', '3');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-11-27', '2', '4');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-12-11', '1', '5');
INSERT INTO p17244121_orderline VALUES ('1', '1', '2018-03-19', '2018-03-21', '1');
INSERT INTO p17244121_orderline VALUES ('2', '2', '2018-05-11', '2018-05-14', '2');
INSERT INTO p17244121_orderline VALUES ('3', '3', '2018-09-23', '2018-09-25', '2');
INSERT INTO p17244121_orderline VALUES ('4', '4', '2018-11-28', '2018-11-29', '3');
INSERT INTO p17244121_orderline VALUES ('5', '5', '2018-12-14', '2018-12-15', '1');
INSERT INTO p17244121_inventory VALUES ('1', '1', '11', '1');
INSERT INTO p17244121_inventory VALUES ('2', '2', '16', '2');
INSERT INTO p17244121_inventory VALUES ('3', '3', '8', '2');
INSERT INTO p17244121_inventory VALUES ('4', '4', '17', '3');
INSERT INTO p17244121_inventory VALUES ('5', '5', '10', '1');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-03-18', '1', '1');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-05-08', '2', '2');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-09-18', '3', '3');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-11-27', '2', '4');
INSERT INTO p17244121_customerorder (CustOrd_Date, Cust_Number, Str_Num)
VALUES ('2018-12-11', '1', '5');
INSERT INTO p17244121_orderline VALUES ('1', '1', '2018-03-19', '2018-03-21', '1');
INSERT INTO p17244121_orderline VALUES ('2', '2', '2018-05-11', '2018-05-14', '2');
INSERT INTO p17244121_orderline VALUES ('3', '3', '2018-09-23', '2018-09-25', '2');
INSERT INTO p17244121_orderline VALUES ('4', '4', '2018-11-28', '2018-11-29', '3');
INSERT INTO p17244121_orderline VALUES ('5', '5', '2018-12-14', '2018-12-15', '1');
INSERT INTO p17244121_inventory VALUES ('1', '1', '11', '1');
INSERT INTO p17244121_inventory VALUES ('2', '2', '16', '2');
INSERT INTO p17244121_inventory VALUES ('3', '3', '8', '2');
INSERT INTO p17244121_inventory VALUES ('4', '4', '17', '3');
INSERT INTO p17244121_inventory VALUES ('5', '5', '10', '1');
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
25EER MODELLING AND SQL
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-05-01', '40', '1000.40', '1', '1');
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-05-01', '40', '1134.00', '2', '2');
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-05-01', '42', '1045.00', '3', '2');
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-06-01', '35', '977.50', '1', '3');
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-06-01', '21', '677.00', '2', '4');
Task 2.5 SQL Query:
select CONCAT(ep.Emp_FName, ' ', ep.Emp_LName) AS Name, CONCAT(ad.Address_St,
' ', ad.Address_City, ' ', ad.Address_State, ' ', ad.Address_Postcode) AS Address From
p17244121_employee ep inner join p17244121_address ad on ep.Address_ID_Postal =
ad.Address_ID Order By Emp_FName;
Select CONCAT(cust.Cust_FName, ' ', cust.Cust_LName) AS Name, Max(co.CustOrd_Date)
AS 'Most Recent Order Date' from p17244121_customer cust left outer join
p17244121_customerorder co on cust.Cust_Number = co.Cust_Number where
cust.Cust_FName like '%a%';
select CONCAT(cust.Cust_FName, ' ', cust.Cust_LName) AS Name, cust.Cust_Phone,
CONCAT(ad.Address_St, ' ', ad.Address_City, ' ', ad.Address_State, ' ',
ad.Address_Postcode) AS Address from p17244121_customer cust inner join
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-05-01', '40', '1000.40', '1', '1');
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-05-01', '40', '1134.00', '2', '2');
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-05-01', '42', '1045.00', '3', '2');
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-06-01', '35', '977.50', '1', '3');
INSERT INTO p17244121_payslip (Pay_date, Pay_num_of_hours, Pay_amount_gross,
Emp_ID, Str_Num) VALUES ('2016-06-01', '21', '677.00', '2', '4');
Task 2.5 SQL Query:
select CONCAT(ep.Emp_FName, ' ', ep.Emp_LName) AS Name, CONCAT(ad.Address_St,
' ', ad.Address_City, ' ', ad.Address_State, ' ', ad.Address_Postcode) AS Address From
p17244121_employee ep inner join p17244121_address ad on ep.Address_ID_Postal =
ad.Address_ID Order By Emp_FName;
Select CONCAT(cust.Cust_FName, ' ', cust.Cust_LName) AS Name, Max(co.CustOrd_Date)
AS 'Most Recent Order Date' from p17244121_customer cust left outer join
p17244121_customerorder co on cust.Cust_Number = co.Cust_Number where
cust.Cust_FName like '%a%';
select CONCAT(cust.Cust_FName, ' ', cust.Cust_LName) AS Name, cust.Cust_Phone,
CONCAT(ad.Address_St, ' ', ad.Address_City, ' ', ad.Address_State, ' ',
ad.Address_Postcode) AS Address from p17244121_customer cust inner join
26EER MODELLING AND SQL
p17244121_address ad on cust.Address_ID = ad.Address_ID where cust.Cust_Number NOT
IN (Select cu.Cust_Number from p17244121_customerorder cu);
select prd.Prod_Num, prd.Prod_Desc, SUM(i.Inv_QntyOnHand) AS Available_Quantity
from p17244121_inventory i left outer join p17244121_product prd on i.ProductNum =
prd.Prod_Num GROUP BY prd.Prod_Num ORDER BY prd.Prod_Num;
select prd.Prod_Num, prd.Prod_Desc, CONCAT(prd.Prod_Price, ' ', '$') AS Price from
p17244121_product prd where prd.Prod_Price <= (select AVG(p.Prod_Price) from product
p);
select CONCAT(ep.Emp_FName, ' ', ep.Emp_LName) AS Name, psl.Pay_num_of_hours,
CONCAT(psl.Pay_amount_gross, ' ', '$') AS 'Salary Received' from p17244121_payslip psl
inner join p17244121_employee ep on psl.Emp_ID = ep.Emp_ID where psl.Pay_date =
(select MAX(pay.Pay_date) from p17244121_payslip pay);
p17244121_address ad on cust.Address_ID = ad.Address_ID where cust.Cust_Number NOT
IN (Select cu.Cust_Number from p17244121_customerorder cu);
select prd.Prod_Num, prd.Prod_Desc, SUM(i.Inv_QntyOnHand) AS Available_Quantity
from p17244121_inventory i left outer join p17244121_product prd on i.ProductNum =
prd.Prod_Num GROUP BY prd.Prod_Num ORDER BY prd.Prod_Num;
select prd.Prod_Num, prd.Prod_Desc, CONCAT(prd.Prod_Price, ' ', '$') AS Price from
p17244121_product prd where prd.Prod_Price <= (select AVG(p.Prod_Price) from product
p);
select CONCAT(ep.Emp_FName, ' ', ep.Emp_LName) AS Name, psl.Pay_num_of_hours,
CONCAT(psl.Pay_amount_gross, ' ', '$') AS 'Salary Received' from p17244121_payslip psl
inner join p17244121_employee ep on psl.Emp_ID = ep.Emp_ID where psl.Pay_date =
(select MAX(pay.Pay_date) from p17244121_payslip pay);
27EER MODELLING AND SQL
Bibliography:
Dhingra, A. and Singh, I., 2017. Big Data Analysis Model for Transformation and
Effectiveness in the e-Governance Service from Relational Database.
Narasayya, V., Menache, I., Singh, M., Li, F., Syamala, M. and Chaudhuri, S., 2015. Sharing
buffer pool memory in multi-tenant relational database-as-a-service. Proceedings of the
VLDB Endowment, 8(7), pp.726-737.
Nather, M. and Theuvsen, L., 2015. Economic evaluation of animal disease control strategies:
Application of a relational database system. AGRÁRINFORMATIKA/JOURNAL OF
AGRICULTURAL INFORMATICS, 6(4), pp.30-38.
Solihin, W., Eastman, C. and Lee, Y.C., 2017. Multiple representation approach to achieve
high-performance spatial queries of 3D BIM data using a relational database. Automation in
Construction, 81, pp.369-388.
Bibliography:
Dhingra, A. and Singh, I., 2017. Big Data Analysis Model for Transformation and
Effectiveness in the e-Governance Service from Relational Database.
Narasayya, V., Menache, I., Singh, M., Li, F., Syamala, M. and Chaudhuri, S., 2015. Sharing
buffer pool memory in multi-tenant relational database-as-a-service. Proceedings of the
VLDB Endowment, 8(7), pp.726-737.
Nather, M. and Theuvsen, L., 2015. Economic evaluation of animal disease control strategies:
Application of a relational database system. AGRÁRINFORMATIKA/JOURNAL OF
AGRICULTURAL INFORMATICS, 6(4), pp.30-38.
Solihin, W., Eastman, C. and Lee, Y.C., 2017. Multiple representation approach to achieve
high-performance spatial queries of 3D BIM data using a relational database. Automation in
Construction, 81, pp.369-388.
1 out of 28
Related Documents
Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
![[object Object]](/_next/static/media/star-bottom.7253800d.svg)
Unlock your academic potential
© 2024 | Zucol Services PVT LTD | All rights reserved.