1804ICT Create Database Design

Str_Fax CHAR(10),

Str_Email VARCHAR(40),

StoreManagerID INT,

SupStore_Num INT,

Address_ID INT,

Foreign key(SupStore_Num) references STORE(Str_Num),

Foreign key(Address_ID) references ADDRESS (Address_ID)

/*--------------------7. STOREDEPARTMENT TABLE--------------------------------*/

create table STOREDEPARTMENT

StrDept_ID INT AUTO_INCREMENT Primary Key,

StrDept_Phone CHAR(10),

StrDept_Email VARCHAR(40),

DeptSupervisorID INT,

Str_Num INT,

Dept_ID INT,

Foreign key(Str_Num) references STORE(Str_Num),

Foreign key(Dept_ID) references DEPARTMENT (Dept_ID)

/*--------------------8. EMPLOYEE TABLE--------------------------------*/

CREATE TABLE EMPLOYEE

Emp_ID INT AUTO_INCREMENT Primary Key,

Emp_FName VARCHAR(30),

Emp_LName VARCHAR(30),

Emp_Phone CHAR(10),

Emp_DoB DATE,

Emp_StartDate DATE,

Emp_TaxFNum CHAR(12),

Emp_HourlySalary DOUBLE(10,2),

StrDept_ID INT,

SupvisorID INT,

Address_ID_Postal INT,

Address_ID_Resident INT,

Emp_JobTypeID INT,

Foreign key(StrDept_ID) references STOREDEPARTMENT (StrDept_ID),

Foreign key(SupvisorID) references EMPLOYEE(Emp_ID),

Foreign key(Address_ID_Postal) references ADDRESS (Address_ID),

Foreign key (Address_ID_Resident) references ADDRESS (Address_ID),

Foreign key (Emp_JobTypeID) references JOBTYPE (JobType_ID)

/*------------------ALTER TABLE STORE AND STOREDEPARTMENT --------------------------------*/

ALTER TABLE STORE ADD CONSTRAINT STOREFK

Foreign key(StoreManagerID) references EMPLOYEE(Emp_ID);

ALTER TABLE STOREDEPARTMENT ADD CONSTRAINT SD_FK

Foreign key(DeptSupervisorID) references EMPLOYEE(Emp_ID);

/*---------------------9. BRAND TABLE--------------------------------*/

CREATE TABLE BRAND(

Brand_ID INT AUTO_INCREMENT Primary Key,

Brand_Name VARCHAR(30)

/*------------------10.PAYSLIP TABLE--------------------------------*/

CREATE TABLE PAYSLIP(

Pay_ID INT AUTO_INCREMENT Primary Key,

Pay_date DATE,

Pay_num_of_hours DOUBLE(4,2),

Pay_amount_gross DOUBLE(6,2),

Emp_ID INT,

Str_Num INT,

Foreign key(Emp_ID ) references EMPLOYEE(Emp_ID),

Foreign key(Str_Num) references STORE(Str_Num)

/*----------------11. PRODUCT TABLE--------------------------------*/

CREATE TABLE PRODUCT(

Prod_Num INT AUTO_INCREMENT Primary Key,

Prod_Desc VARCHAR(50),

Prod_Size VARCHAR(10),

Prod_Price DOUBLE(4,2),

Prod_BrandID INT,

Foreign key(Prod_BrandID) references BRAND (Brand_ID)

/*-----------------12. INVENTORY TABLE--------------------------------*/

CREATE TABLE INVENTORY

ProductNum INT,

Str_Num INT,

Inv_QntyOnHand INT,

Inv_QtyOrdered INT,

primary key (ProductNum, Str_Num),

Foreign key(ProductNum) references PRODUCT (Prod_Num),

Foreign key(Str_Num) references STORE (Str_Num)

/*--------------13. CUSTOMERORDER TABLE--------------------------------*/

CREATE TABLE CUSTOMERORDER(

CustOrd_ID INT AUTO_INCREMENT Primary Key,

CustOrd_Date DATE,

Cust_Number INT,

Str_Num INT,

Foreign key(Cust_Number) references CUSTOMER (Cust_Number),

Foreign key(Str_Num) references STORE (Str_Num)

/*------------------14. ORDERLINE TABLE--------------------------------*/

CREATE TABLE ORDERLINE

CustOrd_ID INT,

Prod_Num INT,

OrdLn_DateArrived DATE,

OrdLn_DatePicked DATE,

OrdLn_Qnty INT,

primary key (CustOrd_ID, Prod_Num),

Foreign key(CustOrd_ID) references CUSTOMERORDER(CustOrd_ID),

Foreign key(Prod_Num) references PRODUCT (Prod_Num)

Task 2: -

/*-------------------------------------------------------------------------

-----------------------INSERT DATA INTO TABLES-----------------------------

---------------------------------------------------------------------------*/

/*-----------------------1. ADDRESSTYPE table------------------------*/

insert into ADDRESSTYPE VALUES ('R','Residential'),

('P','Postal'),

('B','Both');

/*------------------------2. ADDRESS TABLE--------------------------------*/

insert into ADDRESS(Address_St, Address_City, Address_State, Address_Postcode, Add_TypeID) VALUES

('152 Temple St','Redbank','VIC','3350','R'),

('153 Temple St','Redbank','NSW','2001','P'),

('154 Temple St','Redbank','NSW','2001','B'),

('155 Temple St','Redbank','QLD','4132','R'),

('156 Temple St','Redbank','QLD','4132','P');

/*--------------------3. CUSTOMER TABLE--------------------------------*/

INSERT INTO CUSTOMER(Cust_FName, Cust_LName, Cust_Phone, Address_ID) VALUES

('mikaya','Caan','1236567456',5),

('Mohammad','Awrangjeb','5676567876',1),

('John,','Smith','5676565676',2),

('Robin','Caan','5676561234',3),

('Mika','Smith','9876567876',4),

('Veena','Caan','1236567876',5);

/*-----------------------4. JOBTYPE TABLE--------------------------------*/

insert into JOBTYPE(JobType_Name) VALUES

('Accountant'),

('CA'),

('Manger'),

('Admin'),

('Programmer');

/*-----------------------5. DEPARTMENT TABLE--------------------------------*/

insert into DEPARTMENT(Dept_Name) VALUES

('Sales'),

('Training'),

('Payroll'),

('Customer Service'),

('Billing');

/*---------------------6. STORE TABLE--------------------------------*/

insert into STORE (Str_Name, Str_Phone, Str_Fax,Str_Email, SupStore_Num,Address_ID) Values

('BigM Underwood','0989098781','1565456765','[email protected]',1,1),

('BigM1 Underwood','0989098782','2565456765','[email protected]',2,2),

('BigM2 Underwood','0989098783','3565456765','[email protected]',3,3),

('BigM3 Underwood','0989098784','4565456765','[email protected]',4,4),

('BigM4 Underwood','0989098785','5565456765','[email protected]',5,5);

/*--------------------7. STOREDEPARTMENT TABLE--------------------------------*/

insert into STOREDEPARTMENT(StrDept_Phone, StrDept_Email, Str_Num, Dept_ID) VALUES

('9861686178','[email protected]',1,1),

('9862686278','[email protected]',2,2),

('9863686378','[email protected]',3,3),

('9864686478','[email protected]',4,4),

('9865686578','[email protected]',5,5);

/*--------------------8. EMPLOYEE TABLE--------------------------------*/

INSERT INTO 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

('Gaani','Caan','0987656781','1994-01-01','2016-01-01','098789878912',23.09,1,1,1,1,1),

('Yanni','Raan','0987656782','1994-02-01','2016-02-01','098789878913',23.09,2,2,2,2,2),

('Cabin','Raan','0987656783','1994-03-01','2016-03-01','098789878914',23.09,3,3,3,3,3),

('Roon','Caan','0987656784','1994-04-01','2016-04-01','098789878915',23.09,4,4,4,4,4),

('John','Raan','0987656785','1994-05-01','2016-05-01','098789878916',23.09,5,5,5,5,5);

/*------------------UPDATE STORE TABLE AND STOREDEPARTMENT --------------------------------*/

update STORE set StoreManagerID=(1) where Str_Num=(1);

update STORE set StoreManagerID=(2) where Str_Num=(2);

update STORE set StoreManagerID=(3) where Str_Num=(3);

update STORE set StoreManagerID=(4) where Str_Num=(4);

update STORE set StoreManagerID=(5) where Str_Num=(5);

/*------------------UPDATE STOREDEPARTMENT TABLE--------------------------------*/

UPDATE STOREDEPARTMENT SET DeptSupervisorID =(1) WHERE StrDept_ID=(1);

UPDATE STOREDEPARTMENT SET DeptSupervisorID =(2) WHERE StrDept_ID=(2);

UPDATE STOREDEPARTMENT SET DeptSupervisorID =(3) WHERE StrDept_ID=(3);

UPDATE STOREDEPARTMENT SET DeptSupervisorID =(4) WHERE StrDept_ID=(4);

UPDATE STOREDEPARTMENT SET DeptSupervisorID =(5) WHERE StrDept_ID=(5);

/*---------------------9. BRAND TABLE--------------------------------*/

INSERT INTO BRAND(Brand_Name) VALUES

('Century'),

('Armani'),

('lakme'),

('abc'),

('xyz');

/*------------------10.PAYSLIP TABLE--------------------------------*/

INSERT INTO PAYSLIP(Pay_date,Pay_num_of_hours,Pay_amount_gross, Emp_ID, Str_Num) VALUES

('2017-01-01',2.4,34.09,1,1),

('2017-02-01',3.4,44.09,2,2),

('2017-03-01',4.4,54.09,3,3),

('2017-04-01',5.4,64.09,4,4),

('2017-05-01',6.4,74.09,5,5);

/*----------------11. PRODUCT TABLE--------------------------------*/

INSERT INTO PRODUCT(Prod_Desc,Prod_Size,Prod_Price,Prod_BrandID) VALUES

('Pant for man','Large',67.09,1),

('Pant for man','XL',17.09,2),

('skirt for girl','Large',27.09,3),

('skirt for girl','Medium',37.09,4),

('Pant for man,','Medium',47.09,5);

/*-----------------12. INVENTORY TABLE--------------------------------*/

insert into INVENTORY VALUES

(1,2,500,5),

(1,1,100,5),

(2,2,200,10),

(3,3,300,15),

(4,4,400,20),

(5,5,500,25);

/*--------------13. CUSTOMERORDER TABLE--------------------------------*/

INSERT INTO CUSTOMERORDER(CustOrd_Date, Cust_Number,Str_Num) VALUES

('2018-01-01',1,1),

('2018-02-01',2,2),

('2018-03-01',3,3),

('2018-04-01',4,4),

('2018-05-01',5,5);

/*------------------14. ORDERLINE TABLE--------------------------------*/

INSERT INTO ORDERLINE VALUES

(1,2,'2018-02-01','2018-05-20',8),

(1,3,'2018-02-01','2018-05-20',8),

(1,1,'2018-02-01','2018-02-02',8),

(2,2,'2018-03-02','2018-03-04',8),

(3,3,'2018-04-03','2018-04-05',8),

(4,4,'2018-05-04','2018-05-06',8),

(5,5,'2018-06-05','2018-06-07',8);

Task 3: -

/*I. List of names and complete postal address of all employees sorted by their salary.*/

Select concat(Employee.Emp_FName,' ' ,Employee.Emp_LName) as 'Employee Name',

Concat(Address.Address_ST,' ', Address.Address_City,' ',

Address.Address_State,' ', Address.Address_Postcode) as 'Postal Address',

concat('$',Employee.Emp_HourlySalary) as 'Salary'

from Employee, Address

where Employee.Address_ID_Postal=Address.Address_ID

order by Employee.Emp_HourlySalary;

II. The date on which the most recent customer order has been made. The customer name and date of the order will be sufficient.*/

select concat(Customer.Cust_FName,' ', Customer.Cust_LName) as 'Customer Name',

Customerorder.CustOrd_Date as 'Order Date'

from customer, customerorder

where Customer.Cust_Number=CustomerOrder.Cust_Number

Order By CustOrd_Date DESC

LIMIT 1;

III. List of all the store names and their addresses, sorted in dictionary order of the store name.*/

SELECT Store.Str_Name as 'Store Name',

Concat(Address.Address_ST,' ', Address.Address_City,' ',

Address.Address_State,' ', Address.Address_Postcode) as 'Address'

from store, address

where store.Address_ID= Address.Address_ID

Order By Store.Str_Name;

IV. A list of all customers that have not placed an order yet. Displaying customer number and name will be sufficient.*/

select Customer.Cust_Number as 'Customer Number',

Concat(Customer.Cust_FName,' ', Customer.Cust_LName) as 'Name'

from Customer where Customer.Cust_Number not in (select Cust_Number from Customerorder);

V. A list containing the name of employees, which work as accountant.*/

SELECT concat(Employee.Emp_FName,' ', Employee.Emp_LName) as 'Employee Name',

JobType.jobtype_Name as 'Work'

from Employee, JobType

where Employee.Emp_JobTypeID=JobType.JobType_ID

and JobType.jobtype_Name='Accountant';

VI. A list containing the total quantity on hand for each product (product number and description) regardless of stores.*/

select Product.Prod_Num as 'Product Number',

Product.Prod_Desc as 'Description' ,

concat('$',Cast((Sum(Inventory.Inv_Qntyonhand))As char)) as 'Total Quantity on hand'

from Product, inventory

where Product.Prod_Num=Inventory.ProductNum

group by Product.Prod_Num, Product.Prod_Desc;

VII. A list showing each product sold (picked) on or before May 20, 2018. Show product number, name and quantity sold, sorted by product number and then quantity sold.*/

select Product.Prod_Num as 'Product Number',

Product.Prod_Desc as 'Description' ,

concat('$',Cast((Sum(Inventory.Inv_Qntyonhand))As char))as 'Total Quantity on hand'

from Product, inventory, orderline

where Product.Prod_Num=Inventory.ProductNum

and orderline.Prod_Num=Product.Prod_Num

and Orderline.OrdLn_DatePicked='2018-05-20'

group by Product.Prod_Num, Product.Prod_Desc

order by Product.Prod_Num, Inventory.Inv_Qntyonhand ;

III. A list of products (show product number, description and price) whose price is less than or equal to the average product price.*/

select Prod_Num as 'Product number', Prod_Desc as 'Product Description',

concat('$',Prod_Price) As 'Price'

from Product

where Prod_Price<=(select avg(Prod_Price) from product);

IX. Increase each employee’s salary by 7.5% and show the updated salary of all employees (name and salary)*/

update employee set Emp_HourlySalary=Emp_HourlySalary+Emp_HourlySalary*0.075;

select Concat(Employee.Emp_FName,' ', Employee.Emp_LName) as 'Employee Name',

concat('$',Employee.Emp_HourlySalary) as 'Salary'

from Employee;

X. Show the pay information (employee name, hours paid, amount paid) of all employees in the most recent pay date.*/

select Concat(Employee.Emp_FName,' ', Employee.Emp_LName) as 'Employee Name',

concat('$',Employee.Emp_HourlySalary) as 'Hours Paid',

concat('$',Payslip.Pay_Amount_Gross) as 'Amount Paid'

from Employee, Payslip

where Employee.Emp_ID=Payslip.Emp_ID

order by Payslip.Pay_date DESC

LIMIT 1;

XI. Make a list of all products of brand “Armani” and their price in ascending order of price. Show the product description and price.*/

select Product.Prod_Desc as ' Product Description',

concat('$',Product.Prod_Price) as 'Price'

from Product

where Prod_BrandID=( select Brand_ID from brand

 where Brand.Brand_Name='Armani')

order by Product.Prod_Price;

II. A list of supervisors (employee id, name) and all of their subordinates (employee id, name).*/

SELECT S.SupvisorID as 'Supvisor ID',

Concat(S.Emp_FName,' ',S.Emp_LName) as 'Supvisor Name',

SO.Emp_ID as 'subordinate ID',

Concat(SO.Emp_FName,' ',SO.Emp_LName) as 'subordinate Name'

FROM EMPLOYEE S, EMPLOYEE SO

WHERE SO.Emp_ID= S.SupvisorID;

Task 4: -

/*A customer named Daniel Ortega, from 11 Fuller Road, Marsden QLD

4132 and having a mobile number 0431xxx668, orders two shirts of size

“XL” of brand “Prada” on 6 Sep 2018. The full order is ready on 8 Sep

2018 for pickup, but the customer picks up on 10 Sep 2018*/

insert into ADDRESS(Address_St, Address_City, Address_State, Address_Postcode, Add_TypeID) VALUES

('11 Fuller Road', 'Marsden', 'QLD','4132','R');

INSERT INTO CUSTOMER(Cust_FName, Cust_LName, Cust_Phone, Address_ID) VALUES

('Daniel', 'Ortega', '0431xxx668',6);

INSERT INTO BRAND(Brand_Name) VALUES

('Prada');

INSERT INTO PRODUCT(Prod_Desc,Prod_Size,Prod_Price,Prod_BrandID) VALUES

('shirts','XL',78.09,6);

INSERT INTO CUSTOMERORDER(CustOrd_Date, Cust_Number,Str_Num) VALUES

('2018-09-06',7,1);

INSERT INTO ORDERLINE VALUES

(7,6,'2018-09-08','2018-09-10',2);

Bibliography

Awad, E., & Gotterer, M. (1992). Database management. Danvers, Mass.: Boyd & Fraser Pub. Co.

Cram, C. (2012). Microsoft Word 2010. Australia: Course Technology/Cengage Learning.

Date, C., & White, C. (1993). A guide to DB2. Reading: Addison-Wesley.

DuBois, P. (2013). MySQL. Upper Saddle River, NJ: Addison-Wesley.

DuBois, P., & Go?mez Pastor, J. (2005). MySQL. Madrid: Anaya Multimedia.

Duffy, J. (2011). Microsoft Word 2010. Boston, MA: Course Technology.

Eliot, G. (2016). The mill on the Floss. New York: Open Road Integrated Media.

Huddleston, T., & Miller, M. (2011). Using Microsoft Word 2010. Indianapolis, Ind.: Que.

Johnson, S., & Johnson, S. (2011). Brilliant Microsoft Word 2010. Harlow: Prentice Hall.

Kofler, M., & Kramer, D. (2001). MySQL. Berkeley: Apress.

Obermair, W. (1995). Extending the object-oriented database management system VODAK with active capabilities. Sankt Augustin: GMD.

Pachev, S. (2012). MySQL. Helion.

PATHAK, N. (2011). DATABASE MANAGEMENT SYSTEM. [S.l.]: HIMALAYA PUBLISHING HOUSE.

Ramarkrishnan, R. (1997). Database management system. London: McGraw-Hill Pub. Co. (ISE Editions).

Rigollet, P. (2010). Microsoft Word 2010. St Herblain: E?ditions ENI.

Rutkosky, N., & Roggenkamp, A. (2011). Microsoft Word 2010. St. Paul, MN: Paradigm Pub.

Schneller, D., & Schwedt, U. (2010). MySQL admin cookbook. Birmingham, U.K.: Packt Pub.

Schwartz, B., Zaitsev, P., & Tkachenko, V. (2012). High performance MySQL. Beijing: O'Reilly Media.

Silberschatz, A., Korth, H., & Sudarshan, S. (2009). Database system concepts. Boston: McGraw-Hill.

Tahaghoghi, S., & Williams, H. (2007). Learning MySQL. Sebastopol, Calif.: O'Reilly.

Ullman, L. (2006). MySQL. Berkeley, CA: Peachpit.