Your Perfect Assignment is Just a Click Away

Starting at $8.00 per Page

100% Original, Plagiarism Free, Customized to Your instructions!


DBST651 UMUC MYSQL Entry Database Designing For A Company

DBST651 UMUC MYSQL Entry Database Designing For A Company

Question Description

Question 1:

The diagram below (also attached in DBST651_final_part2_ERD.pdf ) is an ERD for an order entry database for a company. A customer orders products through company employee.

Study the ERD and answer the following questions:

Identify all relationships and specify cardinality and business rules. For example: 1:M between Customer and Orders: a customer can place many orders; an order will be placed by one and only one customer.

For each entity, identify primary key and foreign key if any. For foreign key, also specify parent entity and matching attribute in parent entity.

Write SQL DDL statements to create tables in Oracle and also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type. This step is important as in Question 2 you will run INSERT statements to populate the tables you created and then write SELECT statement to query those tables.

Write SQL statement for the following scenario:

Add a new customer John Smith with custono 1 and custzip 23456.

Save changes permanently.

Changer customer John Smith zip from 23456 to 20001.

Cancel the change made in step c, restore data to its original status prior to step c.

Delete customer John Smith.

Save changes permanently.

For SQL code, submit both source statements and results of running your statements.

Question 2:

Continue Question 1 above. It is important for you to create those tables with the exact table/column name and exact column data type/length as shown in the ERD.

Run the DBST651_final_Part2_q2.sql script attached and then respond the following SQL problems:

Create SQL statements for the following scenarios. Your response should include SQL statement, output and any other assumptions you have made to arrive at the solution. Please provide a single SQL query for each problem.

a. For Seattle, WA customers compute the average amount of their orders and the number of orders placed. The result should include the customer number, customer last name, average order amount and the number of orders placed. Show results in the order of average order amount from high to low.

b. For Seattle, WA customers compute the number of unique products ordered. If a product is purchased on multiple orders, it should be counted only one time. The result should include customer number, customer last name and the number of unique products ordered. Show results in the order of customer’s last name.

c. For each employee with a commission less than 0.05, compute the number of orders taken and the average product total quantity per order. The results should include the employee number, employee last name, number of orders taken and the average product total quantity per order. Show results in the order of number of orders taken from low to high.

d. For each Connex product compute the number of unique customers who ordered the product in Jan 2007. The results should include the product number, product name and the number of unique customers. Show results in the order of product name.

"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you A results."

Order Solution Now

Our Service Charter

1. Professional & Expert Writers: Eminence Papers only hires the best. Our writers are specially selected and recruited, after which they undergo further training to perfect their skills for specialization purposes. Moreover, our writers are holders of masters and Ph.D. degrees. They have impressive academic records, besides being native English speakers.

2. Top Quality Papers: Our customers are always guaranteed of papers that exceed their expectations. All our writers have +5 years of experience. This implies that all papers are written by individuals who are experts in their fields. In addition, the quality team reviews all the papers before sending them to the customers.

3. Plagiarism-Free Papers: All papers provided by Eminence Papers are written from scratch. Appropriate referencing and citation of key information are followed. Plagiarism checkers are used by the Quality assurance team and our editors just to double-check that there are no instances of plagiarism.

4. Timely Delivery: Time wasted is equivalent to a failed dedication and commitment. Eminence Papers are known for the timely delivery of any pending customer orders. Customers are well informed of the progress of their papers to ensure they keep track of what the writer is providing before the final draft is sent for grading.

5. Affordable Prices: Our prices are fairly structured to fit in all groups. Any customer willing to place their assignments with us can do so at very affordable prices. In addition, our customers enjoy regular discounts and bonuses.

6. 24/7 Customer Support: At Eminence Papers, we have put in place a team of experts who answer all customer inquiries promptly. The best part is the ever-availability of the team. Customers can make inquiries anytime.