Instructions 1. Using the ER Diagram, create an SQL script the implements the de
ID: 3920608 • Letter: I
Question
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.
Explanation / Answer
CREATE DATABASE Bookstore;
USE Bookstore;
CREATE TABLE Customers (
CustomerID INT NOT NULL AUTO_INCREMENT,FirstName Varchar(20) NOT NULL,LastName Varchar(20) NOT NULL,
Address Varchar(30) ,City Varchar(20) ,State Varchar(2),
Zip Varchar(5) ,Phone Varchar(14),Email Varchar(40) unique NOT NULL , PRIMARY KEY(CustomerID));
CREATE TABLE Products (ProductID INT NOT NULL AUTO_INCREMENT,ProductName Varchar(20) NOT NULL,
Description Varchar(200) NOT NULL,OnHand INT NOT NULL,UNITPRICE decimal(6,2) NOT NULL,
PRIMARY KEY(ProductID));
CREATE TABLE Purchases (PurchaseNumber INT NOT NULL AUTO_INCREMENT,CustomerID INT NOT NULL,
PurchaseDate Date NOT NULL,PRIMARY KEY(PurchaseNumber),FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID));
CREATE TABLE Courses (CourseID Varchar(7)NOT NULL,CourseName Varchar(30)NOT NULL,
PRIMARY KEY(CourseID));
CREATE TABLE PurchasedItems (ItemID INT NOT NULL AUTO_INCREMENT,PurchaseNumber INT NOT NULL,
ProductID INT NOT NULL,Quantity INT NOT NULL,PRice decimal(6,2) NOT NULL, PRIMARY KEY(ItemID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
FOREIGN KEY (PurchaseNumber) REFERENCES Purchases(PurchaseNumber));
CREATE TABLE RequiredMaterials(CourseID INT NOT NULL AUTO_INCREMENT,ProductID INT NOT NULL,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ,PRIMARY KEY(CourseID));
INSERT INTO RequiredMaterials (ProductID) VALUES (1)
INSERT INTO RequiredMaterials (ProductID) VALUES (2)
CREATE DATABASE `bookstore` /*!40100 DEFAULT CHARACTER SET latin1 */;
INSERT INTO CUSTOMERS (FirstName,LastName,Address,City,State,Zip,Phone,Email)
VALUES ('abc','et','12 abc','Leavesan','UK','2345','1234-1234','abc.a@gma.com');
INSERT INTO CUSTOMERS (FirstName,LastName,Address,City,State,Zip,Phone,Email)
VALUES ('tch','et','12 abc','Leavesan','UK','2345','1234-1234','abc.abc@gma.com');
INSERT INTO CUSTOMERS (FirstName,LastName,Address,City,State,Zip,Phone,Email)
VALUES ('abctc','et','12 abc','Leavesan','UK','2345','1234-1234','ac.a@gma.com');
INSERT INTO CUSTOMERS (FirstName,LastName,Address,City,State,Zip,Phone,Email)
VALUES ('abkwjc','et','12 abc','Leavesan','UK','2345','1234-1234','abc.aljka@gma.com');
INSERT INTO CUSTOMERS (FirstName,LastName,Address,City,State,Zip,Phone,Email)
VALUES ('askbc','et','12 abc','Leavesan','UK','2345','1234-1234','abc.awjlpa@gma.com');
INSERT INTO Products (ProductName,Description,OnHand,UNITPRICE) VALUES
('tttt','ahwlahwlih',20,10.21);
INSERT INTO Products (ProductName,Description,OnHand,UNITPRICE) VALUES
('ajhlkw','WKHJLjwhlihLJ',40,5.21);
INSERT INTO Products (ProductName,Description,OnHand,UNITPRICE) VALUES
('IKHJLA','ahaihwjlwlahwlih',1,100.21);
INSERT INTO Products (ProductName,Description,OnHand,UNITPRICE) VALUES
('aengla','aljJWOJAW;K',100,1.21);
insert into Purchases (CustomerID,PurchaseDate) values (1,'2018-06-06');
insert into Purchases (CustomerID,PurchaseDate) values (3,'2018-04-06');
insert into Purchases (CustomerID,PurchaseDate) values (5,'2018-01-16');
INSERT INTO Courses VALUES ('abc123','CS')
INSERT INTO Courses VALUES ('avb123','ME')
INSERT INTO Courses VALUES ('jh234','EEE')
INSERT INTO PurchasedItems (PurchaseNumber,ProductID,Quantity,PRice) VALUES
(1,1,2,12.34)
INSERT INTO PurchasedItems (PurchaseNumber,ProductID,Quantity,PRice) VALUES
(2,3,4,12.34)
INSERT INTO PurchasedItems (PurchaseNumber,ProductID,Quantity,PRice) VALUES
(3,3,10,1204.34)
INSERT INTO Courses VALUES ('ajk','628')
NOTE:As per chegg rules we can only solve 1st 4 sub question