This is being done in Microsoft SQL sever management studio

The Emi-Trev company acts as a distributer of unique products to discerning customers. Using the unnormalized data on the attached spreadsheet, construct a normalized relational database that will allow the company to keep records on their customers (and potential customers), the products they have for sale and sold, and the companies that are the sources for these products.

Assumptions and requirements: - Each product is unique - Since they are unique, if they are sold to someone, they are no longer for sale (they are still maintained in the records) - Any row on the unnormalized spreadsheet with just customer information is a potential customer - Do not alter any of the data or the way the data is structured (e.g. the area code of the business phone number) within each cell - Do not include data specific to the records as part of your queries. For example, if you are asked to give me a query that provides the name of the customer with the largest single purchase, you could easily scan the spreadsheet and craft an answer like select CustomerName from Customer where SellingPrice = 1829.95 (yes, this is incomplete (and incorrect) but you see my point). Such a query would be penalized.

Queries to perform on this database: 1) Give me the name of the customer with the largest single purchase (bet you didnt see that one coming).

2) Give me the name and address of the customer with the largest sum of purchases and the total amount of those purchases.

3) Give me the names and addresses of all our supplying companies organized by area code.

4) Give me the average price for all items that were sold.

5) Give me the total price for all items remaining to be sold.

6) Give me the name and address of everyone in our customer table, indicate whether they have purchased something, and if they have, provide the name of the most expensive product they bought.

7) Give me the name of every customer that lives in the same state as one of our supplying businesses.

8) Given the high price of the products provided by Torchwood (and the fact that they are not selling), reduce their price in the database by 15%.

9) As a companion to Query 7, we want to offer those individuals a special 10% discount for all products manufactured by the business in that same state. Provide the customer name(s), the corresponding business name(s), the names of all available products, and their discounted price.

BusinessNameACMEACMEACMEACMEWizardsWizardsWizardsWizardsTorchwoodTorchwoodBusinessAddress123Racine,Billings,MT123Racine,Billings,MT123Racine,Billings,MT123Racine,Billings,MT1990Beholder,Seattle,WA1990Beholder,Seattle,WA1990Beholder,Seattle,WA1990Beholder,Seattle,WA222Underhill,Ocala,FL222Underhill,Ocala,FLBusinessPhoneNumber8822882(406)8822882(406)8822882(406)8822882(406)2048069(425)2048069(425)2048069(425)2048069(425)8675309(352)8675309(352)ProductNameHitch-hikersThumbHi-SpeedTonicJetPropelledUnicycleUltimatumDispatcherPortableHoleBugbearRepellentVorpalBlade+2BackscratcherTARDISMk5(nonfunctional)SonicSocketWrench ProductSellingPriceCustomerName$199.95RichardCheney$749.95$1,829.95LanceArmstrong$1,449.95RichardCheney$1,249.95$499.95RichardCheney$9,979.95$379.95MichaelHass$269,499.95$149,999.95CustomerAddress666South,Dallas,TX42Cherywood,Oakland,CA666South,Dallas,TX666South,Dallas,TX322Dillon,Tulsa,OK Fil Guinn 478 First, Okmulgee, OK Danny Claborn 73 Stonebrook, Okmulgee, OK David Tsehai 45 Crabtree, Dillon, MT BusinessNameACMEACMEACMEACMEWizardsWizardsWizardsWizardsTorchwoodTorchwoodBusinessAddress123Racine,Billings,MT123Racine,Billings,MT123Racine,Billings,MT123Racine,Billings,MT1990Beholder,Seattle,WA1990Beholder,Seattle,WA1990Beholder,Seattle,WA1990Beholder,Seattle,WA222Underhill,Ocala,FL222Underhill,Ocala,FLBusinessPhoneNumber8822882(406)8822882(406)8822882(406)8822882(406)2048069(425)2048069(425)2048069(425)2048069(425)8675309(352)8675309(352)ProductNameHitch-hikersThumbHi-SpeedTonicJetPropelledUnicycleUltimatumDispatcherPortableHoleBugbearRepellentVorpalBlade+2BackscratcherTARDISMk5(nonfunctional)SonicSocketWrench ProductSellingPriceCustomerName$199.95RichardCheney$749.95$1,829.95LanceArmstrong$1,449.95RichardCheney$1,249.95$499.95RichardCheney$9,979.95$379.95MichaelHass$269,499.95$149,999.95CustomerAddress666South,Dallas,TX42Cherywood,Oakland,CA666South,Dallas,TX666South,Dallas,TX322Dillon,Tulsa,OK Fil Guinn 478 First, Okmulgee, OK Danny Claborn 73 Stonebrook, Okmulgee, OK David Tsehai 45 Crabtree, Dillon, MT