Requirements (please save the answer to each question in a separate file).
Part-A: TRIGGERS
Q1. The “doctorID” column in the DOCTORS table needs to be generated automatically. Each ID is sequentially numbered for a new tuple being added to the table.
Notes:
a) The IDs take up the full column width.
b) The ID must begin with the prefix letter: D
c) The doctorID value generated by the trigger should also overwrite any value provided through an INSERT statement (don’t worry about rows already in the table before the trigger is in place).
d) For this question assume the first new entry will start at D1001 and IDs will go up to D9999
Create a sequence and a trigger to take care of these requirements.
Name the sequence for Doctors doctors_seq (the name is important as we use it in our grading scripts; do NOT name it anything else). Name the trigger anything you like.
The “logID” column in the ARCHIVE_LOG table needs to be generated automatically. Each ID is sequentially numbered for a new tuple being added to the table.
Notes:
a) The IDs take up the full column width.
b) The ID must begin with the prefix letter: A
c) The logID value generated by the trigger should also overwrite any value provided through an INSERT statement (don’t worry about rows already in the table before the trigger is in place).
d) For this question assume the first new entry will start at A50001 and IDs will go up to A89999
Create a sequence and a trigger to take care of these requirements.
Name the sequence for the archive log table alog_seq (the name is important). Name the trigger anything you choose.
Q2. Create a trigger for the following requirements (optional: you can create one trigger each for the insert, delete and update cases). When an order detail line (i.e., order-medicine entry) is added / deleted / updated:
a) For inserts & updates: The subtotal (lineSubtotal) for that entry should be updated
b) The current ORDERS (amount) should be updated based on the dollar value of the corresponding order line (quantity * unit price). Note: if it’s a deletion, the ORDERS(amount) should be reduced.
Notes: If the only order detail entry for an order is deleted, the ORDERS(amount) should drop to 0. You can assume that an update to order details will only change: (qty, unitprice).
Part-B: PROCEDURES
Q3. Create a procedure called RECEIVE_ORDER which takes one parameter as the input:
(1) param_orderID (e.g., OD1023)
The procedure should change the ORDERS status from Not Received (‘0’) to Received (‘1’) for the order specified in the parameter. Please read the notes in the table creation script to better understand the ORDERS and ORDER_DETAILS tables.
Thereafter, the procedure should increase the corresponding Quantity on Hand in the MEDICINES table appropriately.
For example, suppose the status for OD1024 changes from 0 to 1. Then the quantityOnHand (MEDICINES table) for the corresponding medicines (e.g., the ORDER_DETAILS table lists M005 and M001) should increase by the qty value in ORDER_DETAILS. In this case, quantityOnHand for M001 should go up by 3; quantityOnHand for M005 should go up by 4. If an existing quantityOnHand is NULL, assume it means 0.
Notes:
• If the orderID is invalid (does not exist in ORDERS): raise an application error. Give it an error number of -20050. The error number is important (do not use a different one). You can choose a suitable error-message explaining the error.
• If the order status for the order is already at Received (‘1’), once again raise an application error. Give it an error number of -20051. The error number is important (do not use a different one). Choose a suitable error-message explaining the error.
Q4. Create a procedure called ARCHIVE_ORDERS which takes no parameters.
Note: the name of the procedure is important (do not change it).
The procedure looks at the current date. Any orders that are > 90 days old, are moved into the ARCHIVED_ORDERS table. Correspondingly, the order details entries are moved into ARCHIVED_ORDER_DETAILS.
Make an entry in the ARCHIVE_LOG table that contains the date the procedure was run, the table from which data was archived and the number of rows archived. Make a separate entry in ARCHIVE_LOG for each table (and corresponding number of rows archived). On screen, display for each table: the table name and number of rows archived.
Hint: carefully review the tutorial on D2L T4: SQL Tables so you are familiar with inserting and deleting rows. The procedure is a relatively straightforward combination of SQL queries (to count rows), insert and delete statements. Attention to detail is important
TABLES
—– To drop previously created tables.
DROP TABLE ARCHIVE_LOG;
DROP TABLE ARCHIVED_ORDER_DETAILS;
DROP TABLE ARCHIVED_ORDERS;
DROP TABLE ORDER_DETAILS;
DROP TABLE MEDICINES;
DROP TABLE ORDERS cascade constraints;
DROP TABLE DOCTORS;
DROP TABLE SPECIALIZATIONLEVELS;
–Specialization levels table
create table SPECIALIZATIONLEVELS(
specializationLevel char(1) primary key ,
specialization varchar(20)
);
–create table doctors
CREATE TABLE DOCTORS (
doctorID char(5) constraint doctorid_pk PRIMARY KEY,
dName varchar2(15) CONSTRAINT dname_nn NOT NULL,
dAge number(3),
dDesignation varchar2(15),
dSalary number(10,2),
dSpecialization char(1),
constraint DSpl_fk foreign key (dSpecialization) references SPECIALIZATIONLEVELS
);
–create table medicines
CREATE TABLE MEDICINES (
medicineID char(4) constraint medicineid_pk PRIMARY KEY,
quantityOnHand number(3), — quantity on hand / in stock
standardPrice number(10,2) — a standard list price determined by the clinic
);
–create table orders
CREATE TABLE ORDERS (
orderID char(6) constraint orderid_pk PRIMARY KEY,
orderDate DATE,
authDoctorID char(5),
amount number(14,2), — dollar value, i.e., a summation of order detail (quantity * unit price) for this order
status char(1) CONSTRAINT status_nn NOT NULL, — Status of 0: Not received (i.e., we have placed the order, but have not received it), Status of 1: Received (i.e., the order was received)
constraint orders_doc_fk foreign key (authDoctorID) references DOCTORS
);
–create table orders details
CREATE TABLE ORDER_DETAILS(
orderID char(6),
medicineId char(4),
qty number(3), — quantity
unitPrice number(8,2), — for this order, the unit price paid for the medicine
lineSubtotal number(12,2), — dollar value, i.e., (quantity * unit price) for this line
constraint ORDER_DETAILS_pk PRIMARY KEY (orderId, medicineID),
constraint order_fk foreign key (orderId) references ORDERS,
constraint medicines_fk foreign key (medicineId) references MEDICINES
);
–create table archive log
create table ARCHIVE_LOG (
logID char(6) constraint alog_pk primary key,
daterun date,
tablearchived varchar2(128),
rowsarchived number(10)
);
–create table archived orders
CREATE TABLE ARCHIVED_ORDERS (
orderID char(6) constraint a_orderid_pk PRIMARY KEY,
orderDate DATE,
authDoctorID char(5),
amount number(14,2),
status char(1)
);
–create table archived orders details
CREATE TABLE ARCHIVED_ORDER_DETAILS(
orderID char(6),
medicineId char(4),
qty number(3),
unitPrice number(8,2),
lineSubtotal number(12,2),
constraint a_order_details_pk PRIMARY KEY (orderId, medicineID),
constraint a_order_fk foreign key (OrderId) references ARCHIVED_ORDERS
);
—inserting values into the tables
–inserting into specialization levels table
INSERT INTO SPECIALIZATIONLEVELS VALUES (‘A’, ‘Pathology’);
INSERT INTO SPECIALIZATIONLEVELS VALUES (‘B’, ‘Neurology’);
INSERT INTO SPECIALIZATIONLEVELS VALUES (‘C’, ‘Surgery’);
INSERT INTO SPECIALIZATIONLEVELS VALUES (‘D’, ‘ENT’);
INSERT INTO SPECIALIZATIONLEVELS VALUES (‘E’, ‘Dermatology’);
–inserting values into doctors
INSERT INTO DOCTORS VALUES (‘D0001’, ‘Jose M.’,’50’,’Associate’,’100000′, ‘A’);
INSERT INTO DOCTORS VALUES (‘D0002’, ‘Zachary Abbot’,’65’,’Specialist’,’150000′, ‘B’);
INSERT INTO DOCTORS VALUES (‘D0003’, ‘Jonathan A.’,’45’,’Assistant’,’60000′, ‘C’);
INSERT INTO DOCTORS VALUES (‘D0004’, ‘Bob Abedi’,’54’,’Senior’,’120000′, ‘D’);
INSERT INTO DOCTORS VALUES (‘D0005’, ‘John Lee’,’48’,’Associate’,’68000′,’E’);
— select * from doctors;
–insert values into medicines
INSERT INTO MEDICINES VALUES (‘M001’,5, 100);
INSERT INTO MEDICINES VALUES (‘M002’,26, 50);
INSERT INTO MEDICINES VALUES (‘M003’,10, 150);
INSERT INTO MEDICINES VALUES (‘M004’,35, 90);
INSERT INTO MEDICINES VALUES (‘M005’,40, 80);
INSERT INTO MEDICINES VALUES (‘M006’,70, 50);
–insert into orders
INSERT INTO ORDERS VALUES (‘OD0002’, sysdate – 700, ‘D0004’, 300, ‘1’);
INSERT INTO ORDERS VALUES (‘OD1001’, sysdate – 100, ‘D0001’,1650, ‘1’);
INSERT INTO ORDERS VALUES (‘OD1003’, sysdate – 20, ‘D0001’, 1200, ‘0’);
INSERT INTO ORDERS VALUES (‘OD1024’, sysdate – 10, ‘D0002’, 540, ‘0’);
–insert into order_details
INSERT INTO ORDER_DETAILS VALUES (‘OD0002’, ‘M004’, 3, 100, 300);
INSERT INTO ORDER_DETAILS VALUES (‘OD1001’, ‘M002’, 5, 60, 300);
INSERT INTO ORDER_DETAILS VALUES (‘OD1001’, ‘M003’, 9, 150, 1350);
INSERT INTO ORDER_DETAILS VALUES (‘OD1003’, ‘M001’, 10, 120, 1200);
INSERT INTO ORDER_DETAILS VALUES (‘OD1024’, ‘M005’, 3, 80, 240);
INSERT INTO ORDER_DETAILS VALUES (‘OD1024’, ‘M001’, 3, 100, 300);
commit;
