Movie Theater Database pSQL
This is a project from my database management system course where we went through the process of creating an entire movie theater database all the way through. I will explain the code by order of completion and the things I learn in each part but if you want to see the complete code click here.
The first part includes given data which will be used to fill out tables that need to be created. There are 6 tables that were created: Movies, Theaters, Theater Seats, Showings, Customers, and Tickets. Each table has specifications that needed to be followed. These specifications include using the proper data types for certain attributes and using primary and foreign keys to maintain referential integrity. To check out the code to the first part click here.
The second part starts with the data and tables from the first part. Restrictions are applied to certain attributes in the tables, specifically NOT NULL and UNIQUE. After applying the restrictions, I used the data in the tables to query the database and find specific information using SELECT statements. The keyword DISTINCT in the SELECT statements had to be used properly in order to output the correct data. To check out the code for the second part click here.
The third part uses the data and tables from part 2 and demonstrates how to update and maintain a database system. Part 3 starts by using SQL to combine data from 2 tables. Next I altered tables by adding foreign keys and constraints to the existing database. Then I created unit tests in order to test the added constraints to make sure they all work properly. Next I created a view of various table attributes then I queried that view I had just created. Lastly, I created an index in order to improve the performance of the SQL statements. To check out the code for the third part click here.
The fourth and final part is putting the database I created to practical use. I implemented an application front-end to the database using JDBC. There are 3 methods in the MovieTheaterApplication.java that were created for this application. The first method is getShowingsCount and it returns the number of showings whose priceCode equals an attribute called thePriceCode. The second method is updateMovieName and it returns the number of tuples that were updated, which will always be 1 or 0. The last method is a reduceSomeTicketPrices which reduces the prices of some tickets but not all of them. The method invokes a stored function reduceSomeTicketPricesFunction that even calls the first method in the function, getShowingCount. To check out the code for fourth part click here.
This an example of a relational database with referential integrity
