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: 1281
Experience:  M.S. in Internet Information Systems
Type Your Homework Question Here...
TheDoctor is online now
A new question is answered every 9 seconds

CMIS420 – Advanced Relational Database PROJECT 2 – Mail-Order

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 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.


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: 11 months ago.
Category: Homework
Expert:  Rachel-Mod replied 11 months ago.
Hello,
I'm a moderator for this topic. I've been working hard to find a professionals to assist you with your Question , but sometimes finding the right professionals can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Thank you!
Customer: replied 11 months ago.

Yes, I need one

Expert:  Rachel-Mod replied 11 months ago.
Thank you for your patience. We will continue the search for a professional for you.
Rachel
Customer: replied 11 months ago.

I have never waited before, i need before, there is always some that helps, i never had to go through anyone, since when did just answer need a moderator.

Customer: replied 11 months ago.

who is TheDoctor41183.6690622801, he solved this same assignment already for someone, can you please release yourself from my post and give a chance to see my message

Expert:  Angela--Mod replied 11 months ago.

Hi, I am a Moderator for this topic. I sent your requested professional a message to follow up with you here, when they are back online. If I can help further, please let me know, otherwise, no need to reply. Thank you for your patience.

Expert:  TheDoctor replied 11 months 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: 1281
Experience: M.S. in Internet Information Systems
TheDoctor and 10 other Homework Specialists are ready to help you
Customer: replied 11 months ago.


Thanks, sorry about that i have 2 more questions, how do i send it you

Expert:  TheDoctor replied 11 months ago.
Hello again,

No problem. You can enter your new question here.

When entering a new question, please remember to start it with **For TheDoctor**.

Thank you again.
Customer: replied 11 months ago.

**For TheDoctor**


CMIS420 – Advanced Relational Database


Final Programming Project


In this project, you will design a secure database with several tables using good relational database design techniques.


Project Objectives:


After completing this Project, you should be able to:


Explain the purposes of advanced database objects including indexes, clusters, sequences, and viewsDevelop and implement functions, triggers, and stored procedures/packages


Project Requirements:


Using the Oracle database engine, design and develop a relational database to store user information to support a large online shipping company. Information that needs to be stored includes user name, detailed mailing address, and shipping information including number of packages, weight, cost, destination, origin, and tracking information allowing users to determine where a package is, and when and who signed for the package once it arrives. Other business data should be present showing how much the shipment costs and invoicing capability to send bills to customers. Your design should be normalized and well-designed for scalability for additional customers and shipment locations.
Your design should allow for inserting, updating, selecting and deleting information
A trigger should be used to generate invoices once a customer reaches $200 in shipping costs.
An instead-of trigger should be employed to update and/or populate multi-table views.
You should include packages, indexes, views and other helpful database components that will assist you in your design.
Your scripts should work perfectly allowing the creation of all of the tables, triggers, views and other database components and provide test scripts that demonstrate the use of the system.
You do not need a GUI component but your scripts should demonstrate all of the functionality of the system.


Deliverables:


All sql scripts and run scripts and instructions for successfully building your database and testing your database.


An ERD diagram of your database design showing primary keys, foreign keys and other constraints.


3. Well-written word document describing:


The database designHow to set up and test your databaseThe test data and results you used for your database,Your approach, lessons learned, design strengths, limitations and suggestions for future improvement and alternative approaches, the most challenging aspects of developing this application.


Your instructor's policy on late projects applies to this project.


Projects submitted with evidence of plagiarism will be handled in accordance with UMUC Policy 150.25 - Academic Dishonesty and Plagiarism.


Format:


Documentation format and length:


The word documentation describing and reflecting on your design and approach should be written using Microsoft Word and be no more than 5 pages in length and no less than 2 pages. The font size should be 12 point. The page margins should be 1 inch. The paragraphs should be with double line spacing. All figures, tables, equations and referenced should be properly labeled and formatted using APA style.


Code format:


Header comment block,


Appropriate comments within the code,


Appropriate variable and function names, and


Correct indentation.


Grading:


This activity is awarded 30% of the total grade in the course. In the gradebook, the total number of points will be set to 100. The project elements are assessed as follows:


Attributes


Value


Project Design


20 points


Project Functionality


40 points


Test Data


20 points


Approach documentation


15 points


Approach documentation grammar and spelling


5 points


Total


100 points


The following grading rubric will be used to assign points to your project.


Grading Rubric:


Attribute


Unacceptable


Acceptable


Distinguished


Points


Program Design


(0-8) Points


- Database is not normalized


(9-15) Points


- Database is in 1st or 2nd normal form


(15-20) Points


- Database is in 3rd normal form


Functionality


(0-20) Points


- Few requirements were fulfilled


(21-30) Points


- Meets some requirements


(31-40) Points
- Meets most requirements


Test Data


(0-10) Points
- Little or no test data suite included


(11-17) Points


- Some test data and results included but some vital functionality not tested


(18-20) Points
- Comprehensive test data suite included with results


Approach


documentation


(0-7) Points


- Offers unsupported or irrelevant opinions


- Does not consider alternatives


- Rarely identifies strengths and weaknesses of the experimental design and of the data


(8-12) Points


- Supports opinions with argument, evidence, or proof


- Considers alternatives but does not eliminate them


- Sometimes identifies strengths and weaknesses of the experimental design and of the data


(13-15) Points


- Conclusively argues/documents a point of view


- Considers alternatives and shows how and why they should/should not be accepted


- Identifies strengths and weaknesses of the experimental design and of the data


Approach


Documentation


grammar/spelling


(0-2) Points


-Grammatical errors detract from the readability of the paper


-Sentence structure (run-ons, fragments) needs improvement and impedes readability


-Numerous spelling errors


(3-4) Points


-Few grammatical errors


-Sentence structure needs improvement


- Some spelling errors


(5) Points


- No grammatical errors


-Sentence structure and diction are effective and clear


- No obvious spelling errors


 

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
< Last | 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's Avatar

    Manal Elkhoshkhany

    Tutor

    Satisfied Customers:

    4514
    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:

    4514
    More than 5000 online tutoring sessions.
  • 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:

    3099
    Post Graduate Diploma in Management (MBA)
  • http://ww2.justanswer.com/uploads/lanis/2009-4-1_233717_phput9xef_c1pm.jpg Lani S.'s Avatar

    Lani S.

    Tutor

    Satisfied Customers:

    2453
    Registered Nurse, Internet Researcher, Private Tutor
  • 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:

    2258
    Master's Degree, strong math and writing skills, experience in one-on-one tutoring (college English)
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Engineer

    Satisfied Customers:

    2193
    Expert in Java C++ C C# VB Javascript Design SQL HTML
  • http://ww2.justanswer.com/uploads/JawaadAhmed/2009-6-27_12137_SIs_SHadi.jpg F. Naz's Avatar

    F. Naz

    Chartered Accountant

    Satisfied Customers:

    1962
    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:

    1865
    Bachelors Degree and CPA with Accounting work experience