CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database

CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database

Overview:
Use SQL, PL/SQL, and Triggers to design and create a Mail-Order Database System. Please create your own data for testing purpose. Use the attached file "Project 2 Tables" as a guide to creating the tables. You should pre-populate the PARTS, CUSTOMERS, EMPLOYEE and ZIPCODES tables.

Due Date:
Check the due date in Syllabus for the exact date for this assignment. No project will be accepted after the due date.

Deliverables:
Turn in all SQL scripts in the form of a SQL script files. The script files should include,

1. A script file containing all the DML and DDL statements. That is, the SQL used to create the tables and sequence and the SQL to pre-populate or insert records in the tables. Name this file XXX_PROJ2.sql, where XXX are you intials.
2. A file containing the PL/SQL package (Specification and Body) that provides the functionality listed in the requirements below. Name this file XXX_PROJ2.pkg, where XXX are you initials.
3. A file containing the database triggers. Name this file XXX_PROJ2.trg, where XXX are you initials.
4. Finally, provide a test SQL*PLUS routine (PL/SQL anonymous block) that will test the PL/SQL functionality developed. Name this file XXX_PROJ2_tst.sql, where XXX are your initials.

You should submit your assignment through WebTycho as you did for previous assignments.
Use winzip or any zip software to package the four (4) files into one file called XXX_project2.zip, where XXX are your initials.

Requirements:
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. EMPLOYEE(ENO, ENAME, ZIP, HDATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
2. PARTS(PNO, PNAME, QOH, PRICE, REORDER_LEVEL, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
3. CUSTOMERS(CNO, CNAME, STREET, ZIP, PHONE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
4. ORDERS(ONO, CNO, ENO, RECEIVED, SHIPPED, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
5. ODETAILS(ONO, PNO, QTY, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
6. ZIPCODES(ZIP, CITY)
7. ORDERS_ERRORS(TRANSACTION_DATE, ONO, MESSAGE)
8. ODETAILS_ERRORS(TRANSACTION_DATE, ONO, PNO, MESSAGE)
9. RESTOCK(TRANSACTION_DATE, PNO)

• The EMPLOYEE table contains information about the employees of the company. The ENO (Employee Number) attribute is the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
• The PARTS table keeps a record of the inventory of the company. The record for each part includes its number (PNO) and name (PNAME) as well as the quantity on hand (QOH), the unit price (PRICE) and the reorder level (REORDER_LEVEL). PNO is the primary key for this table.
• The CUSTOMERS table contains information about the customers of the mail-order company. Each customer is assigned a customer number (CNO), which serves as the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
• The ORDERS table contains information about the orders placed by customers, the employee who took the orders, and the dates the orders were received and shipped. Order number (ONO) is the primary key. The Customer number (CNO) attribute is a foreign key referring to the CUSTOMERS table, and the ENO attribute is a foreign key referring to the EMPLOYEES table.
• The ODETAILS table contains information about the various parts order by the customer within a particular order. The combination of ONO and PNO attributes forms the primary key. The ONO attribute is a foreign key referring to the ORDERS table, and the PNO attribute is a foreign key referring to the PARTS relation.
• The ZIPCODES table maintains information about the zip codes for various cities. ZIP is the primary key.
• The ORDERS_ERRORS table contains information about any errors that occurred when an order is processed. Transaction date is the date of the transaction.
• The ODETAILS_ERRORS table contains information about all errors that occur when processing an order detail. Transaction date is the date of the transaction.
• The RESTOCK table contains information about all parts (PNO) that are below the reorder level. Transaction date is the date of the transaction.

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.