Managing Database Storage Structure

TABLESPACE AND USER
Task 1
Table Space 1
a)CREATE USER 2222_publisherDB
IDENTIFIED BY pwd4publisherDB
DEFAULT TABLESPACE 2222_publisherDB
TEMPORARY TABLESPACE 2222_publisherDB
QUOTA 20M on 2222_publisherDB;
/*
*The above CREATE USER statement would create a new user called
*2222_publisherDB in the Oracle database whose password is
*pwd4publisherDB, the default tablespace would be 2222_publisherDB
*with a quota of 20MB, and the temporary tablespace would be
*2222_publisherDB.
*/
Table Space 2
CREATE USER 2222_publisherDB
IDENTIFIED BY pwd4publisherDB
DEFAULT TABLESPACE 3333_publisherDB
TEMPORARY TABLESPACE 3333_publisherDB
QUOTA 20M on 3333_publisherDB;
/*
*The USER 2222_publisherDB in the Oracle database whose password is
*pwd4publisherDB, has access the default tablespace would be
*3333_publisherDB with a quota of 20MB, and the temporary tablespace
*would be 3333_publisherDB.
*/
GRANT create session TO publisherDB;
GRANT create table TO publisherDB;
GRANT create view TO publisherDB;
GRANT create any trigger TO publisherDB;
GRANT create any procedure TO publisherDB;
GRANT create sequence TO publisherDB;
GRANT create synonym TO publisherDB;
/* the above gave full pemission to the user created the statement*/
b) /*PUBLISHER TABLE*/

CREATE TABLE Publisher
( publisher_name varchar2(50) NOT NULL,
city varchar2(50),
street varchar2(25),
post_code varchar2(10),
phone_no varchar2(12) NOT NULL,
fax_no varchar(20) NOT NULL ,
CONSTRAINT Publisher_unique UNIQUE (phone_no, fax_no),
CONSTRAINT Publisher_pk PRIMARY KEY (publisher_name)
);
/*
*the created publisher table with publiher_name as primary key,
*city ,street, postcode, phoneno and faxno, has been populated with
*values inserted to it respectively.
*/
INSERT ALL
INTO Publisher (publisher_name, city, street, post_code, phone_no, fax_no)
VALUES ('john new','glasgow','15','213123','124124124124','124124124134')
INTO Publisher (publisher_name, city, street, post_code, phone_no, fax_no)
VALUES ('Mathew smith','edinburg','14','213121','124124124121','124124124131')
INTO Publisher (publisher_name, city, street, post_code, phone_no, fax_no)
VALUES ('Marcus ali','paisley','13','213120','124124124662','124124124132')
INTO Publisher (publisher_name, city, street, post_code, phone_no,
fax_no)VALUES('umer farooq','paisley','11','213170','124124124122','12
4124124152')
SELECT * FROM dual;
/*PRINT JOB TABLE*/
CREATE TABLE PrintJob
( job_no number(10) NOT NULL,
start_date date(10),
completion_date date),
printing_cost number(10),
publisher_name varchar2(50)
FOREIGN KEY (publisher_name)
REFERENCES Publisher(publisher_name),
CONSTRAINT PrintJob_pk PRIMARY KEY (job_no)
);
/*
*the created PrintJob table with Job_no as primary key, start_date,
*completion_date, printing_cost and publisher_name, has been populated
*with values inserted to it respectively.
*/

INSERT ALL
INTO PrintJob (job_no,start_date, completion_date,
printing_cost,publisher_name) VALUES ('J10','12-1-2015','12-1-
2016',11233,'john new')
INTO PrintJob (job_no,start_date, completion_date,
printing_cost,publisher_name) VALUES ('J12','11-5-2015','11-1-
2017',1164,'Mathew smith')
INTO PrintJob (job_no,start_date, completion_date,
printing_cost,publisher_name) VALUES (''J11','12-1-2017','12-1-
2018',1000,'Marcus ali')
INTO PrintJob (job_no,start_date, completion_date,
printing_cost,publisher_name)VALUES('J19','11-5-2016','11-1-
2018',1777333,'umer farooq')
SELECT * FROM dual;
/*PURCHASE ORDER TABLE*/
CREATE TABLE PurchaseOrder
( order_no number(10) NOT NULL,
order_date date,
job_no number(10),
FOREIGN KEY (job_no)
REFERENCES PrintJob(job_no)
CONSTRAINT PurchaseOrder_pk PRIMARY KEY (order_no)
);
/*
*the created PurchaseOrder table with ‘orderno’ as primary key,
*orderdate, jobno has values assigned to it respectively.
*/
INSERT ALL
INTO PurchaseOrder (order_no,order_date, job_no) VALUES ('O10','12-1-
2015','J10')
INTO PurchaseOrder (order_no,order_date, job_no) VALUES ('O11','6-2-
2016','J11')
INTO PurchaseOrder (order_no,order_date, job_no) VALUES ('O12','2-10-
2016','J12')
SELECT * FROM dual;
/*PRINT ITEM TABLE*/
CREATE TABLE PrintItem
( item_Code number(10) NOT NULL,
type varchar2(50),
price varchar2(50),
CONSTRAINT PrintItem_pk PRIMARY KEY (item_Code)

);
/*
*created a new printitem table with ‘item_Code’ as primary key,
*type, price has values assigned to it respectively.
*/
INSERT ALL
INTO PrintItem (item_Code, type, price) VALUES ('I10','part',1500)
INTO PrintItem (item_Code, type, price) VALUES ('I11',' vehicle
part',2500)
INTO PrintItem (item_Code, type, price) VALUES ('I12',' motor
part',3500)
SELECT * FROM dual;
/*REQUEST ITEM TABLE*/
CREATE TABLE RequestItem
( item_Code number(10) NOT NULL,
order_no number(10) NOT NULL,
quantity varchar2(50),
FOREIGN KEY (order_no)
REFERENCES PrintJob(order_no),
FOREIGN KEY (item_Code)
REFERENCES PrintItem(item_Code)
CONSTRAINT RequestItem_pk PRIMARY KEY (item_Code, order_no)
);
/*
*the created RequestItem table with ‘orderno, itemcode as
*primary key and quantity has values inserted respectively, hence.
*/
INSERT ALL
INTO RequestItem (item_Code,order_no,quantity) VALUES ('O10','I10',2)
INTO RequestItem (item_Code,order_no,quantity) VALUES ('O11','I11',3)
INTO RequestItem (item_Code,order_no,quantity) VALUES ('O12','I12',6)
SELECT * FROM dual;
/*SUPERVISOR EMPLOYEE TABLE*/
CREATE TABLE SuperEmployee
( super_emp_no number(50) NOT NULL,