How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask TheDoctor Your Own Question

TheDoctor
TheDoctor, Professional w/Adv. Degree
Category: Homework
Satisfied Customers: 1757
Experience:  M.S. in Internet Information Systems
59032869
Type Your Homework Question Here...
TheDoctor is online now
A new question is answered every 9 seconds

CMIS420 – Advanced Relational Database PROJECT 2

Resolved Question:

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 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 exact date 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 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 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 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 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 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 part. A check is also made reorder level. If the updated quantity 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 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 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 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.

I. ZIPCODES Table

Column Name Data Type Null?
ZIP VARCHAR2(5) Not Null
CITY VARCHAR2(25) Not Null

II. EMPLOYEE Table

Column Name Data Type Null?
ENO NUMBER(6) Not Null
ENAME VARCHAR2(50) Not Null
ZIP VARCHAR2(5) Not Null
HDATE DATE Not Null
CREATION_DATE DATE Not Null
CREATED_BY VARCHAR2(10) Not Null
LAST_UPDATE_DATE DATE Not Null
LAST_UPDATE_BY VARCHAR2(10) Not Null

III. PARTS Table

Column Name Data Type Null?
PNO NUMBER Not Null
PNAME VARCHAR2(50) Not Null
QOH NUMBER Not Null
PRICE NUMBER(5,2) Null
REORDER_LEVEL NUMBER(2) Null
CREATION_DATE DATE Not Null
CREATED_BY VARCHAR2(10) Not Null
LAST_UPDATE_DATE DATE Not Null
LAST_UPDATE_BY VARCHAR2(10) Not Null

IV. CUSTOMERS Table

Column Name Data Type Null?
CNO NUMBER Not Null
CNAME VARCHAR2(50) Not Null
STREET VARCHAR2(50) Not Null
ZIP VARCHAR2(5) Not Null
PHONE VARCHAR2(15) Not Null
CREATION_DATE DATE Not Null
CREATED_BY VARCHAR2(10) Not Null
LAST_UPDATE_DATE DATE Not Null
LAST_UPDATE_BY VARCHAR2(10) Not Null

V. ORDERS Table

Column Name Data Type Null?
ONO NUMBER Not Null
CNO NUMBER Not Null
ENO NUMBER Not Null
RECEIVED DATE Null
SHIPPED DAE Null
CREATION_DATE DATE Not Null
CREATED_BY VARCHAR2(10) Not Null
LAST_UPDATE_DATE DATE Not Null
LAST_UPDATE_BY VARCHAR2(10) Not Null

VI. ODETAILS Table

Column Name Data Type Null?
ONO NUMBER Not Null
PNO NUMBER Not Null
QTY NUMBER(3) Not Null
CREATION_DATE DATE Not Null
CREATED_BY VARCHAR2(10) Not Null
LAST_UPDATE_DATE DATE Not Null
LAST_UPDATE_BY VARCHAR2(10) Not Null

VII. ORDER_ERRORS

Column Name Data Type Null?
ONO NUMBER Not Null
TRANSACTION_DATE DATE Not Null
MESSAGE VARCHAR2(100) Not Null

VIII. ODETAILS_ERRORS

Column Name Data Type Null?
ONO NUMBER Not Null
TRANSACTION_DATE DATE Not Null
PNO NUMBER Not Null
MESSAGE VARCHAR2(100) Not Null

XI. RESTOCK Table

Column Name Data Type Null?
PNO NUMBER Not Null
TRANSACTION_DATE DATE Not Null

Submitted: 2 years ago.
Category: Homework
Expert:  TheDoctor replied 2 years ago.
THIS ANSWER IS LOCKED!
You can view this answer by clicking here to Register or Login and paying $3.
If you've already paid for this answer, simply Login.
TheDoctor, Professional w/Adv. Degree
Category: Homework
Satisfied Customers: 1757
Experience: M.S. in Internet Information Systems
TheDoctor and 2 other Homework Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
< Previous | Next >
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
  • He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here. Diane Dallas, TX
 
 
 

Meet The Experts:

 
 
 
  • Manal Elkhoshkhany

    Tutor

    Satisfied Customers:

    4530
    More than 5000 online tutoring sessions.
< Last | Next >
  • http://ww2.justanswer.com/uploads/BU/BusinessTutor/2012-2-2_115741_Kouki2.64x64.jpg Manal Elkhoshkhany's Avatar

    Manal Elkhoshkhany

    Tutor

    Satisfied Customers:

    4530
    More than 5000 online tutoring sessions.
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Engineer

    Satisfied Customers:

    4395
    Expert in Java C++ C C# VB Javascript Design SQL HTML
  • http://ww2.justanswer.com/uploads/LI/lindaus/2012-6-10_04811_IMG20120609164157.64x64.jpg Linda_us's Avatar

    Linda_us

    Finance, Accounts & Homework Tutor

    Satisfied Customers:

    3132
    Post Graduate Diploma in Management (MBA)
  • http://ww2.justanswer.com/uploads/chooser77/2009-08-18_162025_Chris.jpg Chris M.'s Avatar

    Chris M.

    M.S.W. Social Work

    Satisfied Customers:

    2486
    Master's Degree, strong math and writing skills, experience in one-on-one tutoring (college English)
  • http://ww2.justanswer.com/uploads/JawaadAhmed/2009-6-27_12137_SIs_SHadi.jpg F. Naz's Avatar

    F. Naz

    Chartered Accountant

    Satisfied Customers:

    2055
    Experience with chartered accountancy
  • http://ww2.justanswer.com/uploads/JK/jkcpa/2011-1-16_182614_jkcpa.64x64.jpg Bizhelp's Avatar

    Bizhelp

    CPA

    Satisfied Customers:

    1884
    Bachelors Degree and CPA with Accounting work experience
  • http://ww2.justanswer.com/uploads/avremote/photoa.jpg Seanna's Avatar

    Seanna

    Tutor

    Satisfied Customers:

    1781
    3,000+ satisfied customers, all topics, A+ work