Part A (2 points) Create 4 tables and the corresponding relationships to implement the ERD below in your own database. P

[answerhappygod]

Part A (2 points) Create 4 tables and the correspondingrelationships to implement the ERD below in your own database.

Part A 2 Points Create 4 Tables And The Corresponding Relationships To Implement The Erd Below In Your Own Database P 1 (280.73 KiB) Viewed 6 times

Part B – 1 (2 points) /* Use the content of AdventureWorks andwrite a query to list the top 3 products included in an order forall orders. The top 3 products have the 3 highest order quantities.If there is a tie, it needs to be retrieved. The report needs tohave the following format. Sort the returned data by the salesorder column.
SalesOrderID Products
43659 709, 711, 777, 714
43660 762, 758
43661 708, 776, 712, 715
43662 758, 770, 762
43663 760
*/
Part B – 2 (2 points) /* Using AdventureWorks2008R2, write aquery to retrieve the top 2 salespersons, based on the total sales,for each year. Use TotalDue of SalesOrderHeader to calculate thetotal sales. The top 2 salespersons have the two highest total saleamounts. Exclude orders which don't have a salesperson specifiedfor this query.
Also calculate the top two salespersons' sales as a percentageof the total yearly sale.
Return the data in the following format. Sort the returned databy the year. The name is a salesperson's name.
Year % of Total Sale Top2Salespersons
2005 23.32 279 Tsvi Reiter, 277 Jillian Carson
2006 23.03 277 Jillian Carson, 276 Linda Mitchell
2007 19.78 289 Jae Pak, 276 Linda Mitchell
2008 14.41 276 Linda Mitchell, 289 Jae Pak */
Part C (2 points) /* Bill of Materials - Recursive */ /* Thefollowing code retrieves the components required for manufacturing"Mountain-500 Black, 48" (Product 992). Modify the code to retrievethe most expensive component(s) at each component level. Use thelist price of a component to determine the most expensive componentfor each level. Exclude the components which have a list price of0. Sort the returned data by the component level. */
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate,ComponentLevel) AS (
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
b.EndDate, 0 AS ComponentLevel
FROM Production.BillOfMaterials AS b WHERE b.
ProductAssemblyID = 992 AND b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID,p.PerAssemblyQty,
bom.EndDate, ComponentLevel + 1
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty,ComponentLevel
FROM Parts AS p
INNER JOIN Production.Product AS pr
ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
PK Person PersonID LastName FirstName DateOfBirth HE PK PK Volunteering FK FK VolunteeringID PersonlD OrganizationID FK SpecialtyID Organization OrganizationID Name Main Phone PK Specialty SpecialtyID Name Description