Q1: Develop a verification report for to show your database meets the following requirements
1. At least 50 customers
2. These customers by average have 3 orders per year ranging from ZERO to 6 orders.
3. Orders should be spread over 4 years and cover all 12 months. This means your orders should be distributed over 4 years and not all in one month.
4. Every order should have by average 4 products ranging from ZERO to all products.
5. Some orders should have all products
6. Employee have position and salary.
a. Every year each employee receives a salary increase between 1% and 3%.
b. Salary increase will happen at the beginning of the year.
c. Your database should be able to keep track of historical salaries.
7. Commission should be paid by the end of each month.
8. 10% of your products sold to customers are returned by customer for some reasons.
9. Reason for returning could be
a. Wrong product
b. Damaged product
c. Wrong quantity
• Include the data model of your design
• Include your database script (database name_v2)- any error in your script will result to ZERO for this question
• Develop queries (enough to show the above requirements are me) to verify your database
Q2: Database Expansions
2.1 Add employee commission to the database
Expand your database to support paying commission to employees. Your expansion should support the following requirements:
1. Only employees with position title related to sales will receive commission (sales manager, sales)
2. Any time there is sale, a trigger should insert the commission amount to sales commission table along with order number, product number, date, and employee number.
3. Any time there is return on sale, a trigger should insert the return commission amount to return commission table along with order number, product number, date, and employee number.
4. Report 1: Use these two sales and return commission tables to calculate the monthly commission. This report should contain: employee number, month, year, sales commission, return commission, and net commission.
5. Report 2: create a monthly commission report to show the net monthly commission and final total for the year. Include your revised database script. The format should be as follows
Emp emp Jan Feb Mar ... year
Num Name comm comm comm ... Total
2.2 ADD the following capabilities to your database. If you already have it, then develop a report to show them already exist
• Sales are only done by employees with the following positions: Sales Manager, Sales Representative, Customer Service, and Customer Service Manager. Develop a report to identify if any discrepancy exist.
• Employee termination date and reason for termination should be maintained. This is similar to individual assignment. Develop a report to verify its existence in your database.
• Your orders should support shipping method. You could use similar shipping methods in OES2. Develop a report to verify its existence and correctness.
• Revise your order table to include the following columns
- expected_ship_date(add 5 days to order date to automatically creates this date)
- ship_date(order date <= ship date <= expected ship date or and ship date>expected ship date)
o make sure you have orders satisfying all cases.
o Write a report show all cases exist
- expected_receive_date (based on shipping method add day (s) to the ship date to get this date
o 1Day delivery (add one day) → this method cost 10% of gross sale
o 2Day delivery (add two days)→ this method cost 5% of gross sale
o Ground delivery (add 5 days) → this method cost 2% of gross sale
It is okay if you havedifferent percentages or methods BUT you have to have something
- Actual_receive_date(ship date <= actual receive date<= expected_receive_date;actual receive date> expected receive date)
o make sure you have orders satisfying all cases.
o Write a report show all cases exist
- Your database should support calculation of tax based on customer state
o Check whether the customer is supposed to pay taxes or not, if tax status is 'Y' (default) then the right tax is calculated depending on the STATE tax rate, otherwise no tax (zero).
o Develop a report to show the calculation is correct
Create the new version of your database. The new script will be called "your_databaseName_yourLastName.v2".It should be in one file to create and populate the database with NO ERRORS
Q3 Develop the Base SALES report of your database
Using your database in question 1, develop a report to contain the following fields and requirements
1. Customer data: customer number, last name, full name (concatenation of custNo, first name, andand last name), gender, city, state
2. Customer orders: order number, date, product number, product name, quantity, unit price, sub-total, tax (use customer city/state as the base for tax - you can use the tax table in OES database)
3. Employee data: employee number, last name, full name (concatenation of first name, and last name), gender, city, state, position title, commission
4. Your report should include all customers that have no orders as well
5. Analyze the report using Pivot table and capture the result
Q4: Develop the Base Purchase report of your database
Using your database in question 1, develop a report to contain the following fields and requirements
1. Product data: product number, name, product category, product brand, unit price
2. Vendor purchase: vendor number, product number, qty purchased, unit price, purchase
3. Vendor data: Vendor number, name, city, state
4. Employee data: employee number, last name, full name (concatenation of first name, and last name), gender, city, state, position title
6. Your report should include all vendors have no orders as wellAnalyze the report using Pivot table and capture the result
7. Analyze the report using Pivot table and capture the result
Q5: Develop the Base Return of SALES report of your database
Using your database in question 1, develop a report to contain the following fields and requirements
1. Customer data: customer number, last name, full name (concatenation of first name, and and last name), gender, city, state
2. Customer Return:return_ID, customer number, order number, product number, date of return, returned_qty, retrun category
3. Product data: product number, name, product category, product brand, unit price
4. Employee data: employee number, last name, full name (concatenation of first name, and last name), gender, city, state, position title, commission
5. Your report should include all customers that have no orders as well
6. Analyze the report using Pivot table and capture the result
Q6: User Defined Functions, Using the latest version of your database, do the followings
• Develop a UDF to receive a product number and returns the total number product sold
• Develop a UDF to receive a product number and year, and returns the total number product sold for that year
• Develop a UDF that will receive a product number, a month, a year, and returns the sales for that year
Develop a SQL program to call these UDF and produce the following report
Product#, product_name, year, Number_Product_sold, total_sale
• Develop a UDF to calculate
o Sub-total (Gross sale)
o Tax (if customer is supposed to pay tax)
o Shipping charge based on shipping method
Develop a SQL to show above calculations for each order.
Compare your results with actual values stored in each order gross sales amount, tax amount, and shipping charge amount
Q7: Stored Procedures, Using the latest version of your database, do the followings
• Develop a stored procedure to receive a product number and returns the total number product sold
• Develop a storedprocedure to receive a product number andyear, and returns the total number product sold for that year
• Develop a procedure that will receive a product number, a month, a year, and returns the sales for that year
Develop a driver program in PL/SQL to call the above procedures and produce the following reports
Product#, product_name, year, Number_Product_sold, total_sale
Q8: Advanced Query Processing: Using your database, answer the following queries
Note: For every query, you should have the following format: query in English, sql code, and result.
Result should be readable, so you may need to use SQL plus commands to format it.
If the result is more than 20 lines, then show only 20 rows (selecting 20 rows should be part of your SQL program). Hint think about ROWNUM
8.a: List the top 25% percent of customers in terms of total number of orders
8.b: For each product (product number and product description),
List the top three customers purchased in term of net sales
8.c: For each product brand, List the top three states which customers have purchased the product brand. This should be done in term of gross sales (don't include tax or shipping charges)
8.d: List those customers that their orders included all of our products
8.e: List those employees that have sold all products
8..f: List every states with their highest total sale
Q9: Active databases: Using your database version
Sales requirements
If any product is sold to a customer and there is enough product on hand
Then the QOH column in product table should be updated.(You should think about creating a trigger).
else customer should be informed (email) that you are out of stock and products will be shipped on this date (to be calculated)
the quantity ordered should be placed on back orders
Order to vendor should be placed
- All our vendor has 3 days no charge delivery and 5% charge for ‘1 Day' delivery
- Order date, expected receiving date, actual received date, shipper (similar shippers that we use for our sales
- Orders rec'd from vendor should increase the QOH
- Validate your development with your own test data and make sure it is posted in your report then rollback your changes
- All orders should be shipped 5 days after order date.
- Be careful, this should be done for the new orders not the old transaction.
- Validate your development with your own test data and make sure it is posted in your report then rollback your changes
Requirements and deliverables
You need to carefully use the following requirements in each of your question (if it applies)
- Every file should have the question number followed by lastname_databasename
- SQL or PL/SQL code should be indented and followed by formatted report
- Report should be readable with a professional report like format (SQLPLus commands) using courier font size9. You may have to use different page orientations (portrait or landscape) for different questions. This is very important for grading your work.
- If your database does not have the right data and/or tables THEN you should expand your database to include what is needed to answer the question.
Deliverables:
• All programs and script should be submitted as one ZIP file
• Within the zip file, you should have a folder for each question
• Every folder should have the question number followed by your_lastname_databasename
o A given question, should have the database script, executable program, and a report
- Database script:If the database script changes, the new version or appropriate version should be used for the question
• Drop commands
• Create commands
• Insert commands
- Program format guideline
• Clear all formatting commands
• Formatting commands using SQLPlus
• Query number and description in English as a comment
• SQL or PL/SQL indented program
• Formatted result surrounded in block comment
- Report for each question should follow the report format guideline (sample is used during the semester and it is included in Test 3 folder as a reminder
Attachment:- Test.zip