Question
Given the database above, assume that the CUSTOMER table has a million records, the ORDERS table has 10 million records, the EVENT table has 250,000
Given the database above, assume that the CUSTOMER table has a million records, the ORDERS table has 10 million records, the EVENT table has 250,000 records, the ARTIST table has 25,000 records and the VENUE table has 10,000 records as you answer the following (12pts total, 2pts each):
a. For the following query, provide the pseudocode for the following SQL SELECT C.CustomerId, C.FirstName, C.LastName, O.TicketPrice, E.EventName FROM CUSTOMER as C, ORDERS as O, EVENT as E WHERE C.CustomerId = O.CustomerId AND E.EventId=O.EventId;
b. Determine the number of operations it would take to perform this query if there are no indexes at all on the tables above:
c. For the query given in part (a), propose a set of indexes that would improve the speed of this query. List these indexes in TABLENAME.(COL1, COL2, COLN) format where (COL1,COL2, COLN) is a list of columns the table is indexed on. Assume worst-case performance for your indexes (hash indexes have a constant time C lookup time, tree indexes have a lookup time O(log(n))). You should also assume that orders are evenly distributed among customers and events (so each customer has on average 10 order records with him/her, and each event has on average 40 order records associated with it.)
CUSTOMER Customerld FirstName LastName BillingAddress ORDERS Customerld Eventld TicketPrice Quantit EVENT Eventld Artistld Venueld EventName EventDate EventLength VENUE Venueld VenueName Capacity VenueAddress ARTIST Artistld FirstName LastName Categoryld ARTIST_TYPES Categoryld CategoryName
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started