QuestionQuestion

Transcribed TextTranscribed Text

Database Design and Modeling Questions Disallowed structures for Q.1, Q.2, and Q.3 (DO NOT USE THESE STRUCTURES FOR Q1, Q2, and Q3): - category_id = x, where x is a PK value from the category table. - Hard coding calculated values such as sums and averages. Instead use appropriate functions and a “dynamic calculation” approach to calculate these values. Q.1) Write a SELECT statement that displays the number of products per category, the maximum discount percent, the average list price, and the number of items sold for each distinct category of products carried by the guitar shop. Include a row that gives the same information over all product categories (a single row that provides cumulative information from all your rows). Round the values on the average list price column, so that the values include only two decimal spaces. HINT: This involves the use of a subquery. Your result set should look like this, including results and formatting aspects: (4 rows returned) Q.2) Write a SELECT statement that displays the customer’s name, number of orders, number of items, price of the most expensive item they have ordered, and total from all of their orders. Only include customers who have ordered more than $300. Your result set should look like this, including results and formatting aspects: (6 rows returned) Q.3) (25 points) Write a SELECT statement that displays the product name, number of customers who ordered that product, number of items ordered, and order total for each product. Your result set should look like this, including results and formatting aspects: (9 rows returned) Q.4) Normalize the un-normalized table given below through 1NF, 2NF and 3NF. Show the normalization process in a file (e.g., creating tables and drawing lines in MS Word or MS Excel), save as a .pdf file, and submit it through the link available on Canvas. Name your file according to this scheme: yourlastname_a3_normalize.pdf. (Do not write, edit or print protect your .pdf file). The presentation of your normalization process should mimic the normalization class exercise and the example provided on Canvas. In order to complete this assignment successfully, please keep in mind the following points as you work on the normalization case. You may lose points if you do not follow these: - Make sure that you cover each normalization step (1NF, 2NF, 3NF) separately, explicitly, and in sequence. - Do not skip any normalization steps. - In each normalization step, you should only resolve the issue(s) that pertain to the given step.(For example, do not resolve transitive dependencies in 1NF or 2NF, as transitive dependencies are 3NF business). - In each step, list all of the tables, including those that do not change in the given step. - Identify and show primary keys, composite primary keys, and foreign keys in each step. - Show the relationships between the tables in each form, (by drawing lines/arrows between corresponding PKs and FKs). - The sample records are for your reference only to help you understand the nature of eachfield. You should not include them in your work. Hotel St. Marie Reservations “Database” Reservations Table Field Sample Record Reservation ID (PK) 72376 Check In Date 1/1/2020 Check Out Date 1/3/2020 Reservation Method Booking Website Room Floor 5 Room Number 512 Room Size Type Double Room Bed Type Queen Room View Street Guest 1 First Name John Guest 1 Last Name Doe Guest 1 Salutation Mr. Guest 1 Minor (Yes/No) No Guest 2 First Name Jane Guest 2 Last Name Doe Guest 2 Salutation Dr. Guest 2 Minor (Yes/No) No Guest 3 First Name Jim Guest 3 Last Name Doe Guest 3 Salutation Mr. Guest 3 Minor (Yes/No) Yes Payment Type Credit Card Payment Amount $480 Payment Date 12/10/2019

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

/*Answer 1*/
SELECT
    *
FROM
    (SELECT
       category_name,
            num_products,
            max_discount,
            avg_list_price,
            num_purchased
    FROM
       (SELECT
       p.category_id,
            c.category_name,
            COUNT(*) AS num_products,
            MAX(p.discount_percent) AS max_discount,
            ROUND(AVG(p.list_price), 2) AS avg_list_price
    FROM
       products AS p
    INNER JOIN categories AS c ON p.category_id = c.category_id
    GROUP BY c.category_name) AS pc
    INNER JOIN (SELECT
       p1.category_id, SUM(o.quantity) AS num_purchased
    FROM
       products AS p1
    INNER JOIN order_items AS o ON p1.product_id = o.product_id
    GROUP BY (p1.category_id)) AS po ON pc.category_id = po.category_id) AS t1
UNION SELECT
    NULL AS category_name,
    SUM(num_products) AS num_products,
    MAX(max_discount) AS max_discount,
    ROUND(SUM(avg_list_price) / SUM(num_products),
            2) AS avg_list_price,
    MAX(num_purchased) AS num_purchased
FROM
    (SELECT
       category_name,
            num_products,
            max_discount,
            avg_list_price,
            num_purchased
    FROM
       (SELECT
       p.category_id,
            c.category_name,
            COUNT(*) AS num_products,
            MAX(p.discount_percent) AS max_discount,
            ROUND(SUM(p.list_price), 2) AS avg_list_price
    FROM
       products AS p
    INNER JOIN categories AS c ON p.category_id = c.category_id
    GROUP BY c.category_name) AS pc
    INNER JOIN (SELECT
       p1.category_id, SUM(o.quantity) AS num_purchased
    FROM
       products AS p1
    INNER JOIN order_items AS o ON p1.product_id = o.product_id
    GROUP BY (p1.category_id)) AS po ON pc.category_id = po.category_id) AS cumm
ORDER BY num_purchased;...

By purchasing this solution you'll be able to access the following files:
Solution1.txt, Solution2.pdf and Solution3.zip.

$40.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Python Programming Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats