The Mail-Order Database consists of the following tables and attributes. Please
ID: 3559042 • Letter: T
Question
The Mail-Order Database consists of the following tables and attributes. Please ensure that all constraints are created when creating the tables. All constraints other than NOT NULL constraints must be named.
1.Write a package called Process_Orders to process customer orders. This package should contain four procedures and a function, namely;
Add_order. This procedure takes as input customer number, employee number, and received date and tries to insert a new row in the Orders table. If the received date is null, the current date is used. The shipped date is left as null. If any errors occur, an entry is made in the Orders_errors table. A sequence called Order_number_seq should be used to populate the order number (ONO) column.
Add_order_details. This procedure receives as input an order number, part number, and quantity and attempts to add a row to the Odetails table. If the quantity on hand for the part is less than what is ordered, an error message is sent to the Odetails_errors table. Otherwise, the part is sold by subtracting the quantity ordered from the quantity on hand for this part. A check is also made for the reorder level. If the updated quantity for the part is below the reorder level, an entry is made to the Restock table.
Ship_order. This procedure takes as input an order number and a shipped date and tries to update the shipped value for the order. If the shipped date is null, the current date is used. If any errors occur, an entry is made in the Orders_errors table.
Delete_order. This procedure takes as input an order number and tries to delete records from both the Orders and Odetails tables that match this order number. If any errors occur or there is no record that matches this order number, an entry is made in the Orders_errors table.
Total_emp_sales. This function takes as input an employee number. It computes and returns the total sales for that employee.
2.Create triggers on the PARTS, ORDERS, and ODETAILS tables to populate the CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY columns when an insert or update is made. Use SYSDATE and the pseudo column USER to populate these columns.
3.Write a trigger that fires when a row in the Orders table is updated or deleted. The trigger should record the dropped order records in another table called deleted_orders. The deleted_orders table should also contain a date attribute that keeps track of the date and time the action (update or delete) was performed. This date is quite different from the CREATED_DATE and UPDATED_DATE from the Order table. Do not copy these dates to the deleted_order table. Please include the table creation script for the deleted_orders table in the script file.
4.Create a sequence called order_number_seq that will be used to populate the order number (ONO) column.
5.Write a PL/SQL anonymous block to test the above.
Explanation / Answer
--------------------------hope this will help you........please rate-------------------------------------
-- create_mail.sql
-- Mail Order Database; Create Tables Script
-- Chapter 2; Oracle 9i Programming -- A Primer
-- by R. Sunderraman
---------------------------------------------------------------
drop table zipcodes cascade constraints;
create table zipcodes (
zip number(5),
city varchar2(30),
primary key (zip));
drop table employees cascade constraints;
create table employees (
eno number(4) not null primary key,
ename varchar2(30),
zip number(5) references zipcodes,
hdate date);
drop table parts cascade constraints;
create table parts(
pno number(5) not null primary key,
pname varchar2(30),
qoh integer check(qoh>= 0),
price number(6,2) check(price >= 0.0),
olevel integer);
drop table customers cascade constraints;
create table customers (
cno number(5) not null primary key,
cname varchar2(30),
street varchar2(30),
zip number(5) references zipcodes,
phone char(12));
drop table orders cascade constraints;
create table orders (
ono number(5) not null primary key,
cno number(5) references customers,
eno number(4) references employees,
received date,
shipped date);
drop table odetails cascade constraints;
create table odetails (
ono number(5) not null references orders,
pno number(5) not null references parts,
qty integer check(qty> 0),
primary key (ono,pno));
---------------------------------------------------------------
-- insert_object.sql
-- Grade Book Database; Create Tables Script
-- Chapter 2; Oracle 9i Programming -- A Primer
-- by R. Sunderraman
---------------------------------------------------------------
insert into o_employees values
(1000,
person_type('Jones',
address_type('123 Main St','Wichita','KS',67226),
phones_varray_type('316-555-1212',null,null)),
'12-DEC-95');
insert into o_employees values
(1001,
person_type('Smith',
address_type('101 Elm St','Fort Dodge','KS',60606),
phones_varray_type('316-555-2121','316-555-2323',null)),
'01-JAN-92');
insert into o_employees values
(1002,
person_type('Brown',
address_type('100 Elm St','Kansas City','KS',50302),
phones_varray_type('780-555-1111',null,null)),
'01-SEP-94');
insert into o_parts values
(10506,'Land Before Time I',200,19.99,20);
insert into o_parts values
(10507,'Land Before Time II',156,19.99,20);
insert into o_parts values
(10508,'Land Before Time III',190,19.99,20);
insert into o_parts values
(10509,'Land Before Time IV',60,19.99,20);
insert into o_parts values
(10601,'Sleeping Beauty',300,24.99,20);
insert into o_parts values
(10701,'When Harry Met Sally',120,19.99,30);
insert into o_parts values
(10800,'Dirty Harry',140,14.99,30);
insert into o_parts values
(10900,'Dr. Zhivago',100,24.99,30);
insert into o_customers values
(1111,
person_type('Charles',
address_type('123 Main St','Wichita','KS',67226),
phones_varray_type('316-636-5555',null,null)));
insert into o_customers values
(2222,
person_type('Bertram',
address_type('237 Ash Avenue','Wichita','KS',67226),
phones_varray_type('316-689-5555','316-689-5556',null)));
insert into o_customers values
(3333,
person_type('Barbara',
address_type('111 InwoodSt','Fort Dodge','KS',60606),
phones_varray_type('316-111-1234','316-111-1235',null)));
insert into o_orders values
(1020,
odetails_ntable_type(odetails_type(10506,1),
odetails_type(10507,1),
odetails_type(10508,2),
odetails_type(10509,3)),
1111,1000,'10-DEC-94','12-DEC-94');
insert into o_orders values
(1021,
odetails_ntable_type(odetails_type(10601,4)),
1111,1000,'12-JAN-95','15-JAN-95');
insert into o_orders values
(1022,
odetails_ntable_type(odetails_type(10601,1),
odetails_type(10701,1)),
2222,1001,'13-FEB-95','20-FEB-95');
insert into o_orders values
(1023,
odetails_ntable_type(odetails_type(10800,1),
odetails_type(10900,1)),
3333,1000,'20-JUN-97',null);
Appendix B;
---------------------------------------------------------------
-- create_grade.sql
-- Grade Book Database; Create Tables Script
-- Chapter 2; Oracle 9i Programming -- A Primer
-- by R. Sunderraman
---------------------------------------------------------------
drop table catalog cascade constraints;
create table catalog (
cno varchar2(7) not null,
ctitle varchar2(50),
primary key (cno));
drop table students cascade constraints;
create table students (
sid varchar2(5) not null,
fname varchar2(20),
lname varchar2(20) not null,
minit char,
primary key (sid));
drop table courses cascade constraints;
create table courses (
term varchar2(10) not null,
lineno number(4) not null,
cno varchar2(7) not null,
a number(2) check(a > 0),
b number(2) check(b > 0),
c number(2) check(c > 0),
d number(2) check(d > 0),
primary key (term,lineno),
foreign key (cno) references catalog);
drop table components cascade constraints;
create table components (
term varchar2(10) not null,
lineno number(4) not null check(lineno>= 1000),
compname varchar2(15) not null,
maxpoints number(4) check(maxpoints>= 0),
weight number(2) check(weight>=0),
primary key (term,lineno,compname),
foreign key (term,lineno) references courses);
drop table enrolls cascade constraints;
create table enrolls (
sid varchar2(5) not null,
term varchar2(10) not null,
lineno number(4) not null,
primary key (sid,term,lineno),
foreign key (sid) references students,
foreign key (term,lineno) references courses);
drop table scores cascade constraints;
create table scores (
sid varchar2(5) not null,
term varchar2(10) not null,
lineno number(4) not null,
compname varchar2(15) not null,
points number(4) check(points >= 0),
primary key (sid,term,lineno,compname),
foreign key (sid,term,lineno) references enrolls,
foreign key (term,lineno,compname) references components);
---------------------------------------------------------------
-- insert_grades.sql
-- Grade Book Database: Insert Rows
-- Chapter 2; Oracle 9i Programming -- A Primer
-- by R. Sunderraman
---------------------------------------------------------------
insert into catalog values
('csc226','Introduction to Programming I');
insert into catalog values
('csc227','Introduction to Programming II');
insert into catalog values
('csc343','Assembly Programming');
insert into catalog values
('csc481','Automata and Formal Languages');
insert into catalog values
('csc498','Introduction to Database Systems');
insert into catalog values
('csc880','Deductive Databases and Logic Programming');
insert into students values
('1111','Nandita','Rajshekhar','K');
insert into students values
('2222','Sydney','Corn','A');
insert into students values
('3333','Susan','Williams','B');
insert into students values
('4444','Naveen','Rajshekhar','B');
insert into students values
('5555','Elad','Yam','G');
insert into students values
('6666','Lincoln','Herring','F');
insert into courses values
('f96',1031,'csc226',90,80,65,50);
insert into courses values
('f96',1032,'csc226',90,80,65,50);
insert into courses values
('sp97',1031,'csc227',90,80,65,50);
insert into components values
('f96',1031,'exam1',100,30);
insert into components values
('f96',1031,'quizzes',80,20);
insert into components values
('f96',1031,'final',100,50);
insert into components values
('f96',1032,'programs',400,40);
insert into components values
('f96',1032,'midterm',100,20);
insert into components values
('f96',1032,'final',100,40);
insert into components values
('sp97',1031,'paper',100,50);
insert into components values
('sp97',1031,'project',100,50);
insert into enrolls values
('1111','f96',1031);
insert into enrolls values
('2222','f96',1031);
insert into enrolls values
('4444','f96',1031);
insert into enrolls values
('1111','f96',1032);
insert into enrolls values
('2222','f96',1032);
insert into enrolls values
('3333','f96',1032);
insert into enrolls values
('5555','sp97',1031);
insert into enrolls values
('6666','sp97',1031);
insert into scores values
('1111','f96',1031,'exam1',90);
insert into scores values
('1111','f96',1031,'quizzes',75);
insert into scores values
('1111','f96',1031,'final',95);
insert into scores values
('2222','f96',1031,'exam1',70);
insert into scores values
('2222','f96',1031,'quizzes',40);
insert into scores values
('2222','f96',1031,'final',82);
insert into scores values
('4444','f96',1031,'exam1',83);
insert into scores values
('4444','f96',1031,'quizzes',71);
insert into scores values
('4444','f96',1031,'final',74);
insert into scores values
('1111','f96',1032,'programs',400);
insert into scores values
('1111','f96',1032,'midterm',95);
insert into scores values
('1111','f96',1032,'final',99);
insert into scores values
('2222','f96',1032,'programs',340);
insert into scores values
('2222','f96',1032,'midterm',65);
insert into scores values
('2222','f96',1032,'final',95);
insert into scores values
('3333','f96',1032,'programs',380);
insert into scores values
('3333','f96',1032,'midterm',75);
insert into scores values
('3333','f96',1032,'final',88);
insert into scores values
('5555','sp97',1031,'paper',80);
insert into scores values
('5555','sp97',1031,'project',90);
insert into scores values
('6666','sp97',1031,'paper',80);
insert into scores values
('6666','sp97',1031,'project',85);
commit;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.