Question

1 Approved Answer

Posted on Sep 25, 2024

How do you create indexes for these queries? What are the correct statements that will help the queries index correctly. We already created queries but

How do you create indexes for these queries? What are the correct statements that will help the queries index correctly. We already created queries but we need help with indexing thanks!

CREATE DATABASE Project; USE Project;

CREATE TABLE Customers( cID CHAR(9), fName VARCHAR(20) NOT NULL, lName VARCHAR(20) NOT NULL, mInitial CHAR(1)NOT NULL, gender CHAR(2), address VARCHAR (30), city VARCHAR (20), state CHAR(2), zip CHAR(5), userName VARCHAR(20), password VARCHAR(16)NOT NULL, PRIMARY KEY(cID), UNIQUE (userName));

CREATE TABLE ProvidesFeedbacks( fID CHAR(10), cID CHAR(9)NOT NULL, rating VARCHAR(12), fDate DATE, PRIMARY KEY (fID), FOREIGN KEY (cID) REFERENCES Customers(cID));

CREATE TABLE PlaceOrders( oID CHAR(10), cID CHAR(9) NOT NULL, ordDate DATE NOT NULL, status VARCHAR(20), billingAddress VARCHAR(45), cardNo CHAR(16) NOT NULL, cardType VARCHAR(15) NOT NULL, expDate DATE NOT NULL, amount FLOAT(8,2), PRIMARY KEY (oID), FOREIGN KEY (cID) REFERENCES Customers(cID));

CREATE TABLE ShipShipments( sID CHAR(12), oID CHAR(10) NOT NULL, receiver VARCHAR(30) NOT NULL, address VARCHAR (45) NOT NULL, city VARCHAR (20) NOT NULL, state CHAR(2) NOT NULL, zip CHAR(5)NOT NULL, type VARCHAR(10), shippingDate DATE, receivedDate DATE, PRIMARY KEY(sID), FOREIGN KEY (oID) REFERENCES PlaceOrders(oID));

CREATE TABLE Employees( eID CHAR(5), fname VARCHAR(20) NOT NULL, minitial CHAR(1)NOT NULL, lname VARCHAR(20)NOT NULL, gender CHAR(1), phone CHAR(12)NOT NULL, dob DATE NOT NULL, age TINYINT, ssn CHAR(12), street VARCHAR(45), city VARCHAR(20), state CHAR (2), zip CHAR (5), email VARCHAR(30), username VARCHAR(30), password VARCHAR(20), salary FLOAT(12,2), PRIMARY KEY (eID), UNIQUE(ssn), UNIQUE(email), UNIQUE(username));

CREATE TABLE Departments( dName VARCHAR(20), location1 VARCHAR(45), phone1 CHAR(12), location2 VARCHAR(45), phone2 CHAR(12), budget FLOAT(10,2), PRIMARY KEY (dName));

CREATE TABLE WorksIn( dName VARCHAR(20), eID CHAR(9), wFrom DATE, position VARCHAR(30), PRIMARY KEY (dName,eid), FOREIGN KEY(dName) REFERENCES Departments(dName), FOREIGN KEY(eID) REFERENCES Employees(eID));

CREATE TABLE Advertisements( aID CHAR(5), cost float(10,2), provider VARCHAR(30), type VARCHAR (30), media VARCHAR (20), PRIMARY KEY (aID));

CREATE TABLE products( prodID CHAR (9), prodName VARCHAR(20), maker VARCHAR(30), unitCost FLOAT(8,2), salePrice float(10,2), qtyInStock INT, PRIMARY KEY(prodID));

CREATE TABLE Games( prodID CHAR (9), title VARCHAR (30), class VARCHAR (15), releaseDate date, PRIMARY KEY(prodID ), FOREIGN KEY(prodID ) REFERENCES Products(prodID));

CREATE TABLE Movies( prodID CHAR (9), title VARCHAR(40), releaseDate date, rating VARCHAR(5), leadStar VARCHAR (30), genre VARCHAR(20), PRIMARY KEY (prodID), FOREIGN KEY(prodID) REFERENCES Products(prodID));

CREATE TABLE Marketing( dName VARCHAR(20), PRIMARY KEY (dName), FOREIGN KEY (dName) REFERENCES Departments(dName));

INSERT INTO Marketing VALUES('Marketing');

CREATE TABLE CustServices( dName VARCHAR(20), PRIMARY KEY (dName), FOREIGN KEY (dName) REFERENCES Departments(dName));

INSERT INTO CustServices VALUES('Customer Service');

CREATE TABLE Advertises( dName VARCHAR(20), aID CHAR(5), prodID CHAR(9), sDate DATE, eDate DATE, PRIMARY KEY (dName, aID, prodID), FOREIGN KEY (dName) REFERENCES Marketing(dName), FOREIGN KEY(aID) REFERENCES Advertisements(aID), FOREIGN KEY (prodID) REFERENCES Products (prodID));

CREATE TABLE Serves( cID CHAR(9), dName VARCHAR(20), PRIMARY KEY (cID,dName), FOREIGN KEY (cID) REFERENCES Customers (cID), FOREIGN KEY (dName) REFERENCES CustServices (dName));

CREATE TABLE Includes( prodID CHAR(9), oID CHAR(10), qty SMALLINT, salePrice DECIMAL(5, 2), amount DECIMAL(8, 2), PRIMARY KEY(prodID, oID), FOREIGN KEY(prodID) REFERENCES Products(prodID), FOREIGN KEY(oID) REFERENCES PlaceOrders(oID));

-- Business Functions for customers: -- 1. Registration for creating a new user account INSERT INTO customers VALUES ( 'CU0000017','Boy', 'George','D', 'MF', '123 Harmon Ln.','Kansas City', 'MO', '12345', 'BoyGeorge80s4Lyfe','n0dix4me' );

-- 2. User login SELECT username, password FROM customers WHERE username = 'wCameroun' && password = 'waca1997';

Create Index idx_customers_username_password On customers (username, password)

-- 3. User can update his/her address, password, etc. UPDATE customers SET address = '1234 New Address Ct.' WHERE suerName = 'wCameroun'

-- 4. Product search by game title, movie title, genre, producer, release date, or combinations SELECT title, releaseDate, rating, genre FROM movies WHERE genre = 'action' AND title = 'Le Village Natal';

-- 5. Add/delete products from shopping cart INSERT INTO shopping_cart VALUES (prodID, prodName, salePrice, qty) ('MOV000001','Movies', 11.86, 1 );

DELETE FROM shopping_cart WHERE prodID = 'MOV000001';

-- 6. Change shopping carts as orders are added (please note that once a shopping cart is changed to order, all of this shopping carts info should be removed from the database). DELETE FROM shopping_cart;

-- 7. Place the order INSERT INTO placeorders VALUES( '1611041234','CU0000001', '2016-11-04','shipping','1123 Harris Blvd. Charlotte, NC 22870','0215587445893645', 'Debit', '2017-11-30', 15.00 );

-- 8. Order trace SELECT oID AS 'Order ID', cID AS 'Customer ID', ordDate AS 'Date Ordered', status AS 'Current Status' FROM placeorders WHERE oID = '1604150004';

-- Functions for employees: -- 9. List product information (e.g., title, producer, price) and quantity-in-stock of some chosen products SELECT games.title AS Title FROM products RIGHT JOIN games ON products.prodID = games.prodID RIGHT JOIN movies ON products.prodID = movies.prodID;

-- 10. Update order status UPDATE PlaceOrders SET stauts = 'delivered' WHERE oID = 1601140001;

-- 11. Insert new products INSERT INTO Products VALUES ('MOV000021','Movies', 'Sony',7.66,10.86,25), ('GAM000016','Games', 'Microsoft', 24.00,59.00,15);

-- Functions for marking analysts: -- 12. Which products are better sold in the second quarter than the first quarter? SELECT Q2.prodID FROM (SELECT P1.prodID, PO1.ordDate, SUM(I1.salePrice) AS TotalSales FROM Products P1 JOIN Includes I1 ON P1.prodID = I1.prodID JOIN PlaceOrders PO1 ON PO1.oID = I1.oID WHERE QUARTER(PO1.ordDate) = 1 ) as Q1 RIGHT JOIN (SELECT P2.prodID, PO2.ordDate, SUM(I2.salePrice) AS TotalSales FROM Products P2 JOIN Includes I2 ON P2.prodID = I2.prodID JOIN PlaceOrders PO2 ON PO2.oID = I2.oID WHERE QUARTER(PO2.ordDate) = 2 ) as Q2 ON Q1.prodID = Q2.prodID WHERE Q2.TotalSales > Q1.TotalSales GROUP BY prodID;

-- 13. Which categories of Movies are the most profitable ones? SELECT genre, SUM(salePrice) FROM Movies M JOIN Includes I ON M.prodID = I.prodID GROUP BY genre ORDER BY SUM(salePrice) DESC LIMIT 2;

-- 14. What is the average time between the order placed and shipped? SELECT CONCAT('The average shipping time is ', AVG(ordDate - shippingDate), ' day(s)' ) as 'Average Shipping Time' FROM placeorders LEFT JOIN shipshipments ON placeorders.oID = shipshipments.oID;

image text in transcribed

4 CREATE TABLE Customers 5 CID CHAR (9), 6 fName VARCHAR (20) NOT NULL, 7 Name VARCHAR (20) NOT NULL 8 mInitial CHAR (1)NOT NULL, 9 gender CHAR(2), 10 address VARCHAR (30), 11 city VARCHAR (20), 12 state CHAR(2), 13 zip CHAR(5), 14 userName VARCHAR (20), 15 password VARCHAR (16)NOT NULL, 16 PRIMARY KEY(cID), 17 UNIQUE (userName)); 18 19 20 CREATE TABLE ProvidesFeedbacks( 1 fID CHAR(10), 22 CID CHAR(9)NOT NULL, 23 rating VARCHAR(12), 24 fDate DATE, 25 PRIMARY KEY (fID), 26 FOREIGN KEY (cID) REFERENCES Customers (cID)) 27 28 29 CREATE TABLE PlaceOrders 30 oID CHAR (10), 31 cID CHAR (9) NOT NULL, 32 ordDate DATE NOT NULL, 33 status VARCHAR (20), 34 billingAddress VARCHAR (45), 35 cardNo CHAR (16) NOT NULL, 36 cardType VARCHAR (15) NOT NULL, 37 expDate DATE NOT NULL, 8 amount FLOAT (8,2), 39 PRIMARY KEY (OID), 0 FOREIGN KEY (cID) REFERENCES Customers(cID)); 41 42 43 CREATE TABLE ShipShipments( 44 SID CHAR(12), 5 oID CHAR(10) NOT NULL, 46 receiver VARCHAR (30) NOT NULL, 47 address VARCHAR (45) NOT NULL, 48 city VARCHAR (20) NOT NULL, 49 state CHAR (2) NOT NULL, 50 zip CHAR(5)NOT NULL, 4 CREATE TABLE Customers 5 CID CHAR (9), 6 fName VARCHAR (20) NOT NULL, 7 Name VARCHAR (20) NOT NULL 8 mInitial CHAR (1)NOT NULL, 9 gender CHAR(2), 10 address VARCHAR (30), 11 city VARCHAR (20), 12 state CHAR(2), 13 zip CHAR(5), 14 userName VARCHAR (20), 15 password VARCHAR (16)NOT NULL, 16 PRIMARY KEY(cID), 17 UNIQUE (userName)); 18 19 20 CREATE TABLE ProvidesFeedbacks( 1 fID CHAR(10), 22 CID CHAR(9)NOT NULL, 23 rating VARCHAR(12), 24 fDate DATE, 25 PRIMARY KEY (fID), 26 FOREIGN KEY (cID) REFERENCES Customers (cID)) 27 28 29 CREATE TABLE PlaceOrders 30 oID CHAR (10), 31 cID CHAR (9) NOT NULL, 32 ordDate DATE NOT NULL, 33 status VARCHAR (20), 34 billingAddress VARCHAR (45), 35 cardNo CHAR (16) NOT NULL, 36 cardType VARCHAR (15) NOT NULL, 37 expDate DATE NOT NULL, 8 amount FLOAT (8,2), 39 PRIMARY KEY (OID), 0 FOREIGN KEY (cID) REFERENCES Customers(cID)); 41 42 43 CREATE TABLE ShipShipments( 44 SID CHAR(12), 5 oID CHAR(10) NOT NULL, 46 receiver VARCHAR (30) NOT NULL, 47 address VARCHAR (45) NOT NULL, 48 city VARCHAR (20) NOT NULL, 49 state CHAR (2) NOT NULL, 50 zip CHAR(5)NOT NULL