create.sql

DROP SCHEMA Lab4 CASCADE; CREATE SCHEMA Lab4;

CREATE TABLE Movies( movieID INT, name VARCHAR(30) NOT NULL, year INT, rating CHAR(1), length INT, totalEarned NUMERIC(7,2), PRIMARY KEY(movieID), UNIQUE(name, year) );

CREATE TABLE Theaters( theaterID INT, address VARCHAR(40), numSeats INT NOT NULL, PRIMARY KEY(theaterID), UNIQUE(address) );

CREATE TABLE TheaterSeats( theaterID INT, seatNum INT, brokenSeat BOOLEAN NOT NULL, PRIMARY KEY(theaterID, seatNum), FOREIGN KEY(theaterID) REFERENCES Theaters );

CREATE TABLE Showings( theaterID INT, showingDate DATE, startTime TIME, movieID INT, priceCode CHAR(1), PRIMARY KEY(theaterID, showingDate, startTime), FOREIGN KEY(theaterID) REFERENCES Theaters, FOREIGN KEY(movieID) REFERENCES Movies );

CREATE TABLE Customers( customerID INT, name VARCHAR(30), address VARCHAR(40), joinDate DATE, status CHAR(1), PRIMARY KEY(customerID), UNIQUE(name, address) );

CREATE TABLE Tickets( theaterID INT, seatNum INT, showingDate DATE, startTime TIME, customerID INT, ticketPrice NUMERIC(4,2), PRIMARY KEY(theaterID, seatNum, showingDate, startTime), FOREIGN KEY(customerID) REFERENCES Customers, FOREIGN KEY(theaterID, seatNum) REFERENCES TheaterSeats, FOREIGN KEY(theaterID, showingDate, startTime) REFERENCES Showings );

Need help with reduceSomeTicketPricesFunction.sql

As Section 4 mentioned, you should write a stored function called reduceSome Ticket Prices Function that has an integer parameter, maxTicketCount. reduceSome Ticket Prices Function will change the ticketPrice attribute for some (but not necessarily all) tuples in Tickets. But it never reduces the ticketPrice of more than maxTicketCount tuples in Tickets. One of the attributes in the Showings table is priceCode. We say that a showing is an 'A' showing if its priceCode is 'A', and similarly for 'B' showings and 'C' showings. Every ticket has a corresponding showing, because Tickets has a Foreign Key referencing the Primary Key of Showings. We're going to reduce the ticket Price for some Tickets based on the priceCode of the corresponding Showing. . We'll reduce the ticketPrice for 'A' showings by $3. We'll reduce the ticketPrice for 'B' showings by $2. We'll reduce the ticketPrice for 'C' showings by $1. But we won't reduce the ticket Price for all tuples in Tickets; we'll only reduce the ticketPrice for at most maxTicketCount tuples in Tickets. How do we decide which to reduce? First, we process the tickets for A' showings, ordered by increasing customerID of the ticket. Then we process the tickets for 'B' showings, ordered by increasing customerlD. Then we process the visits for 'C' showings, ordered by increasing customerID. ("Processing" involves doing the reduction described above, which may be $3, $2 or $1.) But as soon as we have processed maxTicketCount tickets, we are done; no additional tickets are processed. The value that reduceSome Ticket Prices Function returns is the total of all the ticketPrice reductions made. To see how this works, suppose that there are 4 tickets to 'A' showings, 5 tickets to 'B' showings, and 10 tickets to 'C' showings. If maxTicketCount is 19 or more, then all 19 of these tickets will have their ticket Prices reduced, and reduceSomeTicketPrices Function returns the value 32 = 4*3 + 5*2 + 10* 1), even if maxTicketCount was 25. If max TicketCount is 9, then the ticket Prices for the tickets to 'A' and 'B' showings will be reduced, and reduceSomeTicke1Prices Function returns the value 22 (-43 + 5*2). If maxTicketCount is 6, then the ticketPrices for the 4 tickets to 'A' will be reduced, and the ticket Prices for 2 of the 5 'B' showings will be reduced, and reduce Some Ticket Prices Function returns the value 16 =(43+2*2). Which 'B' showing tickets receive the reduction? The ones that have the lowest customerID. (Don't worry about multiple 'B' showing tickets that have the same customer ID; if there are 3 'B' showing tickets that have the same customerID, and you're only allowed to reduce ticketPrice for 2 of them, then any 2 of them are okay.) What if there is a tuple in Tickets whose ticket Price is NULL? ticketPrice for that ticket doesn't get reduced, and that ticket does not count towards maxTicketCount. So if maxTicketCount is 18, you'll try to process 18 tickets whose ticket Price isn't NULL, not including any tickets whose ticket Price is NULL in your count. Write the code to create the stored function, and save it to a text file named reduceSomeTicket PricesFunction.pgsql. To create the stored function reduceSome Ticket PricesFunction, issue the psql command: \i reduceSome Ticket Prices Function.pgsql at the server prompt. If the creation goes through successfully, then the server should respond with the message "CREATE FUNCTION". You will need to call the stored function within the reduceSomeTicket Prices method through JDBC, as described in the previous section, so you'll need to create the stored function before you run your program. You should include the reduceSomeTicket PricesFunction.pgsql source file in the zip file of your submission, together with your versions of the Java source files Movie Theater Application.java and Run Movie Theater Application.java that were described in Section 4. As Section 4 mentioned, you should write a stored function called reduceSome Ticket Prices Function that has an integer parameter, maxTicketCount. reduceSome Ticket Prices Function will change the ticketPrice attribute for some (but not necessarily all) tuples in Tickets. But it never reduces the ticketPrice of more than maxTicketCount tuples in Tickets. One of the attributes in the Showings table is priceCode. We say that a showing is an 'A' showing if its priceCode is 'A', and similarly for 'B' showings and 'C' showings. Every ticket has a corresponding showing, because Tickets has a Foreign Key referencing the Primary Key of Showings. We're going to reduce the ticket Price for some Tickets based on the priceCode of the corresponding Showing. . We'll reduce the ticketPrice for 'A' showings by $3. We'll reduce the ticketPrice for 'B' showings by $2. We'll reduce the ticketPrice for 'C' showings by $1. But we won't reduce the ticket Price for all tuples in Tickets; we'll only reduce the ticketPrice for at most maxTicketCount tuples in Tickets. How do we decide which to reduce? First, we process the tickets for A' showings, ordered by increasing customerID of the ticket. Then we process the tickets for 'B' showings, ordered by increasing customerlD. Then we process the visits for 'C' showings, ordered by increasing customerID. ("Processing" involves doing the reduction described above, which may be $3, $2 or $1.) But as soon as we have processed maxTicketCount tickets, we are done; no additional tickets are processed. The value that reduceSome Ticket Prices Function returns is the total of all the ticketPrice reductions made. To see how this works, suppose that there are 4 tickets to 'A' showings, 5 tickets to 'B' showings, and 10 tickets to 'C' showings. If maxTicketCount is 19 or more, then all 19 of these tickets will have their ticket Prices reduced, and reduceSomeTicketPrices Function returns the value 32 = 4*3 + 5*2 + 10* 1), even if maxTicketCount was 25. If max TicketCount is 9, then the ticket Prices for the tickets to 'A' and 'B' showings will be reduced, and reduceSomeTicke1Prices Function returns the value 22 (-43 + 5*2). If maxTicketCount is 6, then the ticketPrices for the 4 tickets to 'A' will be reduced, and the ticket Prices for 2 of the 5 'B' showings will be reduced, and reduce Some Ticket Prices Function returns the value 16 =(43+2*2). Which 'B' showing tickets receive the reduction? The ones that have the lowest customerID. (Don't worry about multiple 'B' showing tickets that have the same customer ID; if there are 3 'B' showing tickets that have the same customerID, and you're only allowed to reduce ticketPrice for 2 of them, then any 2 of them are okay.) What if there is a tuple in Tickets whose ticket Price is NULL? ticketPrice for that ticket doesn't get reduced, and that ticket does not count towards maxTicketCount. So if maxTicketCount is 18, you'll try to process 18 tickets whose ticket Price isn't NULL, not including any tickets whose ticket Price is NULL in your count. Write the code to create the stored function, and save it to a text file named reduceSomeTicket PricesFunction.pgsql. To create the stored function reduceSome Ticket PricesFunction, issue the psql command: \i reduceSome Ticket Prices Function.pgsql at the server prompt. If the creation goes through successfully, then the server should respond with the message "CREATE FUNCTION". You will need to call the stored function within the reduceSomeTicket Prices method through JDBC, as described in the previous section, so you'll need to create the stored function before you run your program. You should include the reduceSomeTicket PricesFunction.pgsql source file in the zip file of your submission, together with your versions of the Java source files Movie Theater Application.java and Run Movie Theater Application.java that were described in Section 4