ERD and SQL assignment:
AutoLube company is in the quick oil change and autorepair business. Jack Black owns and operates it in a Detroitneighborhood. Customers typically bring in their cars for “quickoil change” service or for other auto repairs. AutoLube alsoreplaces windshield wipers, oil filters, and air filters, etc.subject to customer approval. The invoice contains the charges foreach of the services performed. When the invoice is presented tocustomers, they pay cash, use a credit card, or write a check.AutoLube does not extend credit.
Given the high parts usage of the business operations,AutoLube must maintain careful control of its parts (oil, wipers,oil filters, and air filters, auto parts) inventory. Therefore ifparts reach their minimum on board quantity, the parts in lowsupply must be reordered from an appropriate vendor by placing apurchase order. Typically a Purchase order placed on a Vendor couldinclude multiple parts. AutoLube maintains a vendor list, whichcontains vendors actually used and potential vendors.
Periodically (say, at the end of each week), Jackwould like to have reports generated of parts that are below theirminimum levels to plan for placing orders on vendors for theirreplenishment. In addition, Jack would like to query the system andgenerate reports. Jack Black would like to have an order processingsystem to handle daily operational transactions of the business.Among other things, he plans to track customer’s car mileage andsend service alerts to customers.
List of Entities: All required entities should beidentified including any associative entities identified forresolving M:N relationships
Business Rules: Rules that involve the abovementioned entities(two for each relationship on the ERD)
ER Diagram: This diagram should resolve all M: Nrelationships. This should clearly identify the major attributes ofvarious entity sets along with Primary/Foreign keys. Showappropriate connectivity. Use MS Visio for creating ERD.
Implementation in SQL Server: Create 10 tables andInsert at least five rows into each of the created tables using SQLstatements (You can make up the data).Turn in the SQL script usedboth for creating tables and adding data.
SQL Queries: At least 10 queries using joins(involving at least two tables in each query and three tables inthree of the queries), aggregate functions, “group by” functionsetc. should be turned in along with screenshot printout of yourresults.
Visio and a code editor, Visual Studio or VSCetc
Answer to ERD and SQL assignment: AutoLube company is in the quick oil change and auto repair business. Jack Black owns and operat…