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 MovieTheaterApplication.java

import java.sql.*; import java.util.*;

/** * The class implements methods of the MovieTheater database interface. * * All methods of the class receive a Connection object through which all * communication to the database should be performed. Note: the * Connection object should not be closed by any method. * * Also, no method should throw any exceptions. In particular, in case * an error occurs in the database, then the method should print an * error message and call System.exit(-1); */

public class MovieTheaterApplication {

private Connection connection;

/* * Constructor */ public MovieTheaterApplication(Connection connection) { this.connection = connection; }

public Connection getConnection() { return connection; }

/** * getShowingsCount has a string argument called thePriceCode, and returns the number of * Showings whose priceCode equals thePriceCode. * A value of thePriceCode thats not A, B or C is an error. */

public List getShowingsCount(int thePriceCode) { List result = new ArrayList(); // your code here

// end of your code return result; }

/** * updateMovieName method has two arguments, an integer argument theMovieID, and a string * argument, newMovieName. For the tuple in the Movies table (if any) whose movieID equals * theMovieID, updateMovieName should update its name to be newMovieName. (Note that there * might not be any tuples whose movieID matches theMovieID.) updateMovieName should return * the number of tuples that were updated, which will always be 0 or 1. */

public int updateMovieName(int theMovieID, String newMovieName) { // your code here; return 0 appears for now to allow this skeleton to compile. return 0;

// end of your code }

/** * reduceSomeTicketPrices has an integer parameter, maxTicketCount. It invokes a stored * function reduceSomeTicketPricesFunction that you will need to implement and store in the * database according to the description in Section 5. reduceSomeTicketPricesFunction should * have the same parameter, maxTicketCount. A value of maxTicketCount thats not positive is * an error. * * The Tickets table has a ticketPrice attribute, which gives the price (in dollars and cents) * of each ticket. reduceSomeTicketPricesFunction will reduce the ticketPrice for some (but * not necessarily all) tickets; Section 5 explains which tickets should have their * ticketPrice reduced, and also tells you how much they should be reduced. The * reduceSomeTicketPrices method should return the same integer result that the * reduceSomeTicketPricesFunction stored function returns. * * The reduceSomeTicketPrices method must only invoke the stored function * reduceSomeTicketPricesFunction, which does all of the assignment work; do not implement * the reduceSomeTicketPrices method using a bunch of SQL statements through JDBC. */

public int reduceSomeTicketPrices (int maxTicketCount) { // There's nothing special about the name storedFunctionResult int storedFunctionResult = 0;

// your code here

// end of your code return storedFunctionResult;

}

};

4. Description of methods in the Movie TheaterApplication class Movie Theater Application.java contains a skeleton for the Movie Theater Application class, which has methods that interact with the database using JDBC. The methods in the Movie Theater Application class are the following: gerShowingsCount: This method has a string argument called the PriceCode, and returns the number of Showings whose priceCode equals the PriceCode. A value of the Price Code that's not 'A', 'B' or 'C' is an error. updateMovieName: Sometimes a movie changes name. The updateMovieName method has two arguments, an integer argument the Movie ID, and a string argument, new Movie Name. For the tuple in the Movies table (if any) whose movie ID equals theMovieID, update MovieName should update its name to be newMovieName. (Note that there might not be any tuples whose movieID matches the MovieID.) updateMovie Name should return the number of tuples that were updated, which will always be 0 or 1. reduce Some Ticket Prices: This method has an integer parameter, maxTicketCount. It invokes a stored function reduce Some Ticket Prices Function that you will need to implement and store in the database according to the description in Section 5. reduce Some Ticket Prices Function should have the same parameter, maxTickerCount. A value of maxTickerCount that's not positive is an error. The Tickets table has a ticketPrice attribute, which gives the price in dollars and cents) of each ticket. reduceSome Ticket PricesFunction will reduce the ticket Price for some (but not necessarily all) tickets; Section 5 explains which tickets should have their ticket Price reduced, and also tells you how much they should be reduced. The reduce Some Ticket Prices method should return the same integer result that the reduce Some Ticket Prices Function stored function returns. The reduceSome Ticket Prices method must only invoke the stored function reduce Some Ticket Prices Function, which does all of the assignment work; do not implement the reduce SomeTicketPrices method using a bunch of SQL statements through JDBC Each method is annotated with comments in the Movie Theater Application.java file with a description indicating what it is supposed to do (repeating most of the descriptions above). Your task is to implement methods that match the descriptions. The default constructor is already implemented. 4. Description of methods in the Movie TheaterApplication class Movie Theater Application.java contains a skeleton for the Movie Theater Application class, which has methods that interact with the database using JDBC. The methods in the Movie Theater Application class are the following: gerShowingsCount: This method has a string argument called the PriceCode, and returns the number of Showings whose priceCode equals the PriceCode. A value of the Price Code that's not 'A', 'B' or 'C' is an error. updateMovieName: Sometimes a movie changes name. The updateMovieName method has two arguments, an integer argument the Movie ID, and a string argument, new Movie Name. For the tuple in the Movies table (if any) whose movie ID equals theMovieID, update MovieName should update its name to be newMovieName. (Note that there might not be any tuples whose movieID matches the MovieID.) updateMovie Name should return the number of tuples that were updated, which will always be 0 or 1. reduce Some Ticket Prices: This method has an integer parameter, maxTicketCount. It invokes a stored function reduce Some Ticket Prices Function that you will need to implement and store in the database according to the description in Section 5. reduce Some Ticket Prices Function should have the same parameter, maxTickerCount. A value of maxTickerCount that's not positive is an error. The Tickets table has a ticketPrice attribute, which gives the price in dollars and cents) of each ticket. reduceSome Ticket PricesFunction will reduce the ticket Price for some (but not necessarily all) tickets; Section 5 explains which tickets should have their ticket Price reduced, and also tells you how much they should be reduced. The reduce Some Ticket Prices method should return the same integer result that the reduce Some Ticket Prices Function stored function returns. The reduceSome Ticket Prices method must only invoke the stored function reduce Some Ticket Prices Function, which does all of the assignment work; do not implement the reduce SomeTicketPrices method using a bunch of SQL statements through JDBC Each method is annotated with comments in the Movie Theater Application.java file with a description indicating what it is supposed to do (repeating most of the descriptions above). Your task is to implement methods that match the descriptions. The default constructor is already implemented