Database assignment SLP

rick07

SLP4.docx

Home >Computer Science homework help >Database assignment SLP

1 | Page

For this assignment use the bookstore database assignment below to generate reports for the output specified:

1. Current inventory status of the books and CDs

2. Sales Transactions List

3. Monthly Profit Report

4. Inventory Status Query by Product

5. Sales Transaction that each employee has processed

6. Any other output you think appropriate

Write a 2- to 5-page paper that includes your SQL statements and screenshots of the result tables in DBMS.

EMPLOYEE TABLE

Social Security Number	First Name	Last Name	Address	Zip Code	Date of Birth	Part Time Status	Salary	Date Of Hire
10000001	Joseph	Disuza	Z/11, C Block, MA	446001	3-12-1980	Yes	$30	3-1-2015
10000002	Ana	Fernades	M-6, 3 Avenue,NA	446001	2-3-1978	Yes	$15	6-8-2015
10000003	Lipe	Kem	C60, Luise Park, HH	456221	4-9-1990	Yes	$22	5-8-2015
10000004	Dan	Polo	B34, 32 Gale Road, JJ	470001	12-7-1981	No	$4200	1-8-2010
10000005	Mike	Hudson	9th Park, City Face, SA	446001	13-10-1982	No	$6200	1-5-2000
10000006	John	Tope	C45, Club C, North GH	470001	10-10-1975	Yes	$23	7-10-2015
10000007	Lipsa	Das	P13, Bhaskar Col, NA	456221	13-12-1973	Yes	$18	6-9-2015
10000008	Tote	Aluma	F16, C Block, South Aven	470001	1-3-1990	Yes	$32	3-1-2016
10000009	Willium	Dik	T1, B Block, Gate Church	446001	11-9-1980	Yes	$30	5-2-2016
10000010	Charlse	Wilson	R10, A Block, CC Avenue	446001	12-1-1970	No	$9000	17-11-1993

CITY TABLE

Zip Code	City
446001	Leads
456221	Peets
470001	Hemp
400001	London
460024	Peets
450002	Leeds
470003	Hemp

Customer Table

Customer Number	First Name	Last Name	Address	Zip Code	Contact Number	Email
100001	Lui	Kings	M1, South Avenue, SD	400001	11243520	[email protected]
100002	Sant	Martin	C12, 12/22, West Lane	400001	22019382	[email protected]
100003	Sri	Venkat	M12, South City park	460024	54009221	[email protected]
100004	Ven	Jones	C11, 12/22, East Lane	450002	33000132	[email protected]
100005	Charlse	Dikense	B13, Facility Center, East	400001	44002322	[email protected]
100006	Mike	Tison	Z11, Marg Zim, Kanta P	460024	11002833	[email protected]
100007	Hero	Hito	V19, Silver Green, Tee B	450002	44002928	[email protected]
100008	Zole	Fernades	K10, Otto Mi, South C	470003	44023282	[email protected]

Sales Order

Sales Order Number	Customer Number	Item Name	Date Of Sale	Amount	Payment Status	Social Security Number
1230000001	100001	Transistor	3-5-2016	$80	Paid	10000001
1230000002	100002	Diodes	4-7-2015	$42	Paid	10000004
1230000003	100003	Valve	12-12-2015	$102	Paid	10000003
1230000004	100004	Transistor Circuit	17-1-2016	$231	Paid	10000001
1230000005	100005	Television Set	18-2-2016	$400	Paid	10000003
1230000006	100006	Microwave	12-1-2015	$250	Paid	10000006
1230000007	100007	Heating System	13-2-2016	$180	Paid	10000007
1230000008	100008	Television Set	5-4-2016	$402	Paid	10000003

SQL Statement

Drop table sales_order;

Drop table customer;

Drop table employee;

Drop table city;

CREATE TABLE "CITY" (

"ZIPCODE" NUMBER(10) PRIMARY KEY,

"CITY" VARCHAR2(1000 CHAR) NOT NULL

);

CREATE SEQUENCE "CITY_SEQ" NOCACHE;

CREATE TRIGGER "CITY_BI"

BEFORE INSERT ON "CITY"

FOR EACH ROW

BEGIN

IF :NEW."ZIPCODE" IS NULL THEN

SELECT "CITY_SEQ".NEXTVAL INTO :NEW."ZIPCODE" FROM DUAL;

END IF;

END;;

CREATE TABLE "CUSTOMER" (

"CUSTOMERNUMBER" NUMBER(10) PRIMARY KEY,

"FNAME" VARCHAR2(1000 CHAR) NOT NULL,

"LNAME" VARCHAR2(1000 CHAR) NOT NULL,

"ADDRESS" VARCHAR2(1000 CHAR) NOT NULL,

"CONTACTNUMBER" VARCHAR2(1000 CHAR) NOT NULL,

"EMAIL" VARCHAR2(1000 CHAR) NOT NULL,

"ZIPCODE" NUMBER(10) NOT NULL

);

CREATE INDEX "IDX_CUSTOMER__CITY" ON "CUSTOMER" ("CITY");

ALTER TABLE "CUSTOMER" ADD CONSTRAINT "FK_CUSTOMER__CITY" FOREIGN KEY ("CITY") REFERENCES "CITY" ("ZIPCODE");

CREATE SEQUENCE "CUSTOMER_SEQ" NOCACHE;

CREATE TRIGGER "CUSTOMER_BI"

BEFORE INSERT ON "CUSTOMER"

FOR EACH ROW

BEGIN

IF :NEW."CUSTOMERNUMBER" IS NULL THEN

SELECT "CUSTOMER_SEQ".NEXTVAL INTO :NEW."CUSTOMERNUMBER" FROM DUAL;

END IF;

END;;

CREATE TABLE "EMPLOYEE" (

"SSN" NUMBER(10) PRIMARY KEY,

"FNAME" VARCHAR2(1000 CHAR) NOT NULL,

"LNAME" VARCHAR2(1000 CHAR) NOT NULL,

"ADDRESS" VARCHAR2(1000 CHAR) NOT NULL,

"DATEOFBIRTH" VARCHAR2(1000 CHAR) NOT NULL,

"PARTTIMESTATUS" VARCHAR2(1000 CHAR) NOT NULL,

"SALARY" NUMBER(10) NOT NULL,

"HIRE_DATE" DATE NOT NULL,

"CITY" NUMBER(10) NOT NULL

);

CREATE INDEX "IDX_EMPLOYEE__CITY" ON "EMPLOYEE" ("CITY");

ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "FK_EMPLOYEE__CITY" FOREIGN KEY ("CITY") REFERENCES "CITY" ("ZIPCODE");

CREATE SEQUENCE "EMPLOYEE_SEQ" NOCACHE;

CREATE TRIGGER "EMPLOYEE_BI"

BEFORE INSERT ON "EMPLOYEE"

FOR EACH ROW

BEGIN

IF :NEW."SSN" IS NULL THEN

SELECT "EMPLOYEE_SEQ".NEXTVAL INTO :NEW."SSN" FROM DUAL;

END IF;

END;;

CREATE TABLE "SALES_ORDER" (

"ORDER_NUMBER" NUMBER(10) PRIMARY KEY,

"ITEM_NAME" VARCHAR2(1000 CHAR) NOT NULL,

"SALE_DATE" DATE NOT NULL,

"AMOUNT" NUMBER(10) NOT NULL,

"PAYMENTSTATUS" VARCHAR2(1000 CHAR) NOT NULL,

"CUSTOMERNUMBER" NUMBER(10) NOT NULL,

"SSN" NUMBER(10) NOT NULL

);

CREATE INDEX "IDX_SALES_ORDER__CUSTOMERNUMBE" ON "SALES_ORDER" ("CUSTOMERNUMBER");

CREATE INDEX "IDX_SALES_ORDER__SSN" ON "SALES_ORDER" ("SSN");

ALTER TABLE "SALES_ORDER" ADD CONSTRAINT "FK_SALES_ORDER__CUSTOMERNUMBER" FOREIGN KEY ("CUSTOMERNUMBER") REFERENCES "CUSTOMER" ("CUSTOMERNUMBER");

ALTER TABLE "SALES_ORDER" ADD CONSTRAINT "FK_SALES_ORDER__SSN" FOREIGN KEY ("SSN") REFERENCES "EMPLOYEE" ("SSN");

CREATE SEQUENCE "SALES_ORDER_SEQ" NOCACHE;

CREATE TRIGGER "SALES_ORDER_BI"

BEFORE INSERT ON "SALES_ORDER"

FOR EACH ROW

BEGIN

IF :NEW."ORDER_NUMBER" IS NULL THEN

SELECT "SALES_ORDER_SEQ".NEXTVAL INTO :NEW."ORDER_NUMBER" FROM DUAL;

END IF;

END;

ERD Diagram

Screen Shots

Select * from Employee;

Social Security Number	First Name	Last Name	Address	Zip Code	Date of Birth	Part Time Status	Salary	Date Of Hire
10000001	Joseph	Disuza	Z/11, C Block, MA	446001	3-12-1980	Yes	$30	3-1-2015
10000002	Ana	Fernades	M-6, 3 Avenue,NA	446001	2-3-1978	Yes	$15	6-8-2015
10000003	Lipe	Kem	C60, Luise Park, HH	456221	4-9-1990	Yes	$22	5-8-2015
10000004	Dan	Polo	B34, 32 Gale Road, JJ	470001	12-7-1981	No	$4200	1-8-2010
10000005	Mike	Hudson	9th Park, City Face, SA	446001	13-10-1982	No	$6200	1-5-2000
10000006	John	Tope	C45, Club C, North GH	470001	10-10-1975	Yes	$23	7-10-2015
10000007	Lipsa	Das	P13, Bhaskar Col, NA	456221	13-12-1973	Yes	$18	6-9-2015
10000008	Tote	Aluma	F16, C Block, South Aven	470001	1-3-1990	Yes	$32	3-1-2016
10000009	Willium	Dik	T1, B Block, Gate Church	446001	11-9-1980	Yes	$30	5-2-2016
10000010	Charlse	Wilson	R10, A Block, CC Avenue	446001	12-1-1970	No	$9000	17-11-1993

Select * from City;

Zip Code	City
446001	Leads
456221	Peets
470001	Hemp
400001	London
460024	Peets
450002	Leeds
470003	Hemp

Select * from Customer;

Customer Number	First Name	Last Name	Address	Zip Code	Contact Number	Email
100001	Lui	Kings	M1, South Avenue, SD	400001	11243520	[email protected]
100002	Sant	Martin	C12, 12/22, West Lane	400001	22019382	[email protected]
100003	Sri	Venkat	M12, South City park	460024	54009221	[email protected]
100004	Ven	Jones	C11, 12/22, East Lane	450002	33000132	[email protected]
100005	Charlse	Dikense	B13, Facility Center, East	400001	44002322	[email protected]
100006	Mike	Tison	Z11, Marg Zim, Kanta P	460024	11002833	[email protected]
100007	Hero	Hito	V19, Silver Green, Tee B	450002	44002928	[email protected]
100008	Zole	Fernades	K10, Otto Mi, South C	470003	44023282	[email protected]

Select * from Sales_order;

Sales Order Number	Customer Number	Item Name	Date Of Sale	Amount	Payment Status	Social Security Number
1230000001	100001	Transistor	3-5-2016	$80	Paid	10000001
1230000002	100002	Diodes	4-7-2015	$42	Paid	10000004
1230000003	100003	Valve	12-12-2015	$102	Paid	10000003
1230000004	100004	Transistor Circuit	17-1-2016	$231	Paid	10000001
1230000005	100005	Television Set	18-2-2016	$400	Paid	10000003
1230000006	100006	Microwave	12-1-2015	$250	Paid	10000006
1230000007	100007	Heating System	13-2-2016	$180	Paid	10000007
1230000008	100008	Television Set	5-4-2016	$402	Paid	10000003