Instructions 1. Using the ER Diagram, create an SQL script the implements the design in a text file. a. Use the create_databases.sql script as a template or guide. b. In your script create the database and tables, then add the records, and finally the views. Everything should be Database Description The database support operations at the MTC Bookstore. Here is a list of the entities: Customers the people that purchase items from the book store Purchases each time a customer makes a purchase, a record of the purchase is created. PurchasesItems these are the actual item purchased by the customer. Note that a purchase will have at least on purchased item, but it can have several items in a single purchase. Products this is a list of the items that can be purchased Courses a list of the courses offered by MTC RequiredMaterials are the products that are required per class. Bookstore ER Diagram Customers CustomerID, int, NN, PK, AI FirstName, Varchar 20,NN LastName , Varchar 25, NN Address , Varchar 30 City , Varchar 20 State , Varchar 2 Zip , Varchar 5 Phone , Varchar 14 Email , Varchar 40, NN, unique Products ProductID, int, NN, PK, AI ProductName, Varchar ,NN, 20, Description , Varchar, 200 OnHand, int, NN UnitPrice, decimal(6,2), NN Purchases PurchaseNumber, int, NN, PK, AI CustomerID, int, NN, FK PurchaseDate, Date, NN Courses CourseID, Varchar 7, NN, PK CourseName, Varchar 30, NN PurchasedItems ItemID , int, NN, PK, AI PurchaseNumber, int, NN, FK ProductID, int, NN, FK Quantity, int, NN Price, decimal(6,2), NN RequiredMaterials CourseID, Varchar 7, NN, PK ProductID, int, NN, PK 1 M 1 1 1 1 M M M M Key PK Primary Key FK Foreign Key NN Not Null AI Auto Increment Implementation Instructions Create a databases, name it Bookstore, and use the new database Create the six tables as shown on the previous slides Make sure you follow the specifications in the ER diagram Implement all primary and foreign key constraints at the table level Using insert statements, add records to each table. To avoid errors, add records in the following sequence. You need to need to make up the records 5 customer records 5 product records 4 purchases records. Pick a customer and assign to purchases to them. Pick two more customer and assign them one purchase each. Two of the customers should not have a purchase. 6 PurchasedItems records. Make sure each purchase has at least one item Implementation Instructions Continued 4 Course records RequiredMaterials create enough records so that each course has at least one product assigned to it. Create 5 views CustomerList Show the name, phone number, and email address of every customer. Sort the list by first name and last name CustomerPurchases Show each Customer name, date of purchase, product name, price NoPurchases Show the name of the customers that do not have a purchase. CourseMaterials Show CourseID, Course Name, Product Name for all products are required NumberOfPurchases Show the customers name and number of purchases for each customer. Include customers with purchases and customers with no purchases. In other words include all of the customers.