The objective of this assignment is to assess your understanding of SQL queries, functional dependencies, and normalization. Academic Integrity Statement: This assignment must be completed independently. By submitting this assignment, I state that have completed this assignment independently. Type your name in the box below to affirm the above statement. NAME: Part 1: SQL(30%) Using SQLServer to create the queries below. - Use the Cape Codd database located on the SeeSaw2 SQL Server. - Write your code, then copy \& paste the SQL code, followed by the data onto this Word Document. If the data set is long, copy approximately 10 to 15 records. NOTE: It is easier if you use a clipping tool for the data set as it does not copy/paste well. - Be sure to use good indention form on coding. You will be graded on form in this assignment. - The sample that follows will give you an example of how your code and data should look. Sample: Write an SQL statement to select both the SKU and the SKU Description fields from the inventory. SELECT SKU, SKU,REsGridtion FROM INVENTORY NOTE: There were 32 records returned when executing this query but I only copy and pasted the first 10 records as required. Be sure you limit your data to only 10 records. 1. Write a SQL statement to list the total quantity of items on hand, grouped by department. Name the column output as "TotalltemsOnHand". Sort the list in descending order of items on hand. Code: Data Result: 2. The sales department is complaining because some items are out of stock and they are having trouble meeting their sales quotas. The sales manager asks you to provider her with a list all the items that are currently out of stock along with the buyer's name, so she can persuade the buyers to get off their tails and order more items to sell. Using a SUBQUERY, list the SKU, SKU RescriptipD. and BuxercName for items that are out of stock. Code: Data Result: 3. List the total SKUs (disregard availability), grouped by city the products are warehoused in and the department. Output should look like this, though there will be more rows: \begin{tabular}{|l|l|l|l|} \hline & WarehouseCity & Department & CountOfSKUs \\ 1 & Atlanta & Water Sports & 4 \\ \cline { 2 - 4 } 2 & Bangor & Water Sports & 4 \\ \hline \end{tabular} Name the aggregate column "CountOfSKUs". Sort the list in descending order by the total SKUs available. Code: Data Result: Part 2: Functional Dependencies (30%) - NOTES: Underlined attributes are either the primary key or part of the primary key. For example, R(A,B,C) indicates that A,B is a composite primary key. - Use the same notation as in the text. - For example, to take the following relation: R(X,Y,Z) with FD:(X,Y)Z and (X,Z)Y from 3NF to BCNF, you would recommend the following: R(X,Z) and R2(Z,Y), as described in the text on page 167 using the STUDENT_ADVISOR relation. 1. Given that relation (R) has the following structure: R(A,B,C,D) with the following functional dependencies (FD): (A,B)C and AD a. What Normal Form does this relation violate? b. Using the relational notation format above, modify the relation so that it is in Boyce-Codd Normal Form (BCNF). (Consider adding tables, moving attributes between tables, etc. Be sure to underline primary keys and italicize foreign keys.) 2. For Relation R(A,B,C,D,E) with functional dependencies: - (A,B)C - (A,B)D - (A,B)E - DE a. What normal form does this relation violate? b. Using the relational notation format above, modify the relation so that it is in Boyce-Codd Normal Form (BCNF). 3. Identify the functional dependency rule for each of the following items: a. If AB and BC, then AC What is the name of this type of functional dependency? b. If XZ and XY, then X(Z,Y) What rule of functional dependencies does this demonstrate? c. Use a formula like the above to show the decomposition rule. 4. Identify the functional dependencies in the table below. Functional Dependencies: Part 3: Normalization (40\%) Convert the form below into a list of tables, attributes, primary and foreign keys in the standard form of documentation. (For example, MERCHANDISE(MerchNumber, MerchDescription, CustomerNumber, etc...) where table names are capitalized, primary keys are underlined, and foreign keys are italicized. The tables should be in Boyce Codd Normal Form. List your relations below