Undergrad Computer Programming Assignment that involves SQL and Java
Final assignment: putting it all together Due: June 25, 11:59pm You will need to submit this assignment through Gradescope. Please check Moodle for Gradescope signup instructions. The assignment will be autograded, and you can submit as many times as you wish before the deadline. Assume the following schema for a database of a video rental store: RentalPlans(pid, name, max_movies, fee) Customers(cid, login, password, fname, lname, pid) MovieRentals(mid, cid, status) RentalPlans contains information on the available plans the Video Store offers to their customers; they have a plan id (pid), a name for the plan, a maximum number of movies a customer with that plan can rent (max movies), and the monthly cost of the plan (fee). Customers contains information on customers; customer id, login and password informa- tion, first and last names, and finally, the rental plan the customer is signed up for (pid). MovieRentals records the movie ids (mid) that a customer (cid) has rented, and their status, which can be open or closed. Open means the movie is currently rented out to the customer, whereas closed means it has been returned. 1 CMPSCI 345 Putting it all together 1. Your goal is to implement the fastsearch functionality for the provided Query.java. The provided starter code already contains placeholders for where your code should go. Make sure that you compile and test your code locally; Gradescope is not a development or debugging environment. The autograder will test both the output of your method and its speed. Make sure that your fastsearch function formats its output information in the same manner as the regular search function. It is normal for the autograder to take a few minutes to complete its evaluation. If it is taking much longer, likely your fastsearch is not fast. There are many guidelines, steps, and suggestions included in this topic’s slides and videos, all on moodle. Please take advantage of them. Submit your revised Query.java. Here are some more hints and guidelines, prepared by Professor Alexandra Meliou, who originally prepared this exercise: You may have noticed that the search function of the Video Store is often fast when you provide very specific titles (e.g., Harry Potter and the Chamber of Secrets), but it can get much slower when you provide less specific keywords (e.g., Harry Potter). This is because the existing search function performs a lot of the data processing logic within the Java code: it first retrieves movies that contain the provided string in the title, and then it iterates over this result and issues, for each movie, separate queries to retrieve the directors and actors. So, when many movies contain the provided string or keyword in the title, the application issues many separate queries and things can get slow. Your task is to implement the fastsearch functionality, by pushing more of the data processing logic into the database engine. The fastsearch command in the interface invokes transaction fast search from Query.java, so that is the method that you should modify. Your fastsearch should return only (1) the movie information (id, title, year), (2) its actors, and (3) its director(s). It does not need to return the rental status. (This is the same information as the search function returns, except the rental status.) Your fast search function should only issue 2 or 3 SQL queries, in contrast with regular search, which issues many queries, depending on how many movies match the search. Note: Fastsearch may end up not beating (and may even be slower than) regular search for some searches. This is normal. However, fastsearch should be significantly faster than search for less specific searches (e.g., Nixon, nowhere, etc.). Hint: You can do it with three queries: One query finds all movies matching the keyword; one query finds all directors of all movies matching the keyword; one query finds all the actors of all the movies matching the keyword. Execute each of these three queries separately. You then need to merge the results of the three queries in the Java code. The merge will be easier if your SQL queries sort the answers by the movie id. (There is also a way to write fastsearch with only two, or even only one single SQL query, but it gets messy with questionable benefits.) Important note: The autograder times the performance of your code to evaluate Q5. Occasionally, some steps may run a bit slower than normal for unknown reasons, which Page 2 CMPSCI 345 Putting it all together could potentially cause an autograder test to fail when it shouldn’t. If you find that your code passes at least one test for Q5 (especially test 1 or test 3, which are the hardest), but fails in others, it may be worthwhile to simply re-run the autograder. When in doubt, please contact us through Piazza. Page 3 dbconn.config # PostgreSQL is case sensitive for database names; # be sure to use proper capitalization. imdbUrl = jdbc:postgresql:imdb2015 customerUrl = jdbc:postgresql:customer postgreSQLDriver = org.postgresql.Driver postgreSQLUser = dbadmin postgreSQLPassword = postgresql-42.2.20.jar META-INF/MANIFEST.MF Manifest-Version: 1.0 Automatic-Module-Name: org.postgresql.jdbc Bundle-Activator: org.postgresql.osgi.PGBundleActivator Bundle-Copyright: Copyright (c) 2003-2020, PostgreSQL Global Developme nt Group Bundle-Descriptiona: Java JDBC driver for PostgreSQL database Bundle-DocURL: https://jdbc.postgresql.org/ Bundle-License: BSD-2-Clause Bundle-ManifestVersion: 2 Bundle-Name: PostgreSQL JDBC Driver Bundle-SymbolicName: org.postgresql.jdbc Bundle-Vendor: PostgreSQL Global Development Group Bundle-Version: 42.2.20 Export-Package: org.postgresql;uses:="org.checkerframework.checker.nul lness.qual,org.postgresql.copy,org.postgresql.fastpath,org.postgresql .jdbc,org.postgresql.largeobject,org.postgresql.replication,org.postg resql.util";version="42.2.20",org.postgresql.copy;uses:="org.checkerf ramework.checker.nullness.qual,org.postgresql,org.postgresql.core,org .postgresql.util";version="42.2.20",org.postgresql.core;uses:="javax. net,javax.net.ssl,org.checkerframework.checker.index.qual,org.checker framework.checker.nullness.qual,org.checkerframework.dataflow.qual,or g.ietf.jgss,org.postgresql,org.postgresql.copy,org.postgresql.core.v3 ,org.postgresql.jdbc,org.postgresql.replication,org.postgresql.replic ation.fluent.logical,org.postgresql.replication.fluent.physical,org.p ostgresql.util,org.postgresql.xml";version="42.2.20",org.postgresql.c ore.v3;uses:="org.checkerframework.checker.nullness.qual,org.postgres ql.copy,org.postgresql.core,org.postgresql.jdbc,org.postgresql.util"; version="42.2.20",org.postgresql.core.v3.replication;uses:="org.check erframework.checker.nullness.qual,org.postgresql.copy,org.postgresql. core,org.postgresql.replication,org.postgresql.replication.fluent.log ical,org.postgresql.replication.fluent.physical";version="42.2.20",or g.postgresql.ds;uses:="javax.naming,javax.sql,org.checkerframework.ch ecker.nullness.qual,org.postgresql.ds.common";version="42.2.20",org.p ostgresql.ds.common;uses:="javax.naming,javax.naming.spi,javax.sql,or g.checkerframework.checker.nullness.qual,org.postgresql,org.postgresq l.jdbc";version="42.2.20",org.postgresql.fastpath;uses:="org.checkerf ramework.checker.nullness.qual,org.postgresql.core";version="42.2.20" ,org.postgresql.geometric;uses:="org.checkerframework.checker.nullnes s.qual,org.postgresql.util";version="42.2.20",org.postgresql.gss;uses :="javax.security.auth.callback,org.checkerframework.checker.nullness .qual,org.ietf.jgss,org.postgresql.core,org.postgresql.util";version= "42.2.20",org.postgresql.hostchooser;uses:="org.checkerframework.chec ker.nullness.qual,org.postgresql.util";version="42.2.20",org.postgres ql.jdbc;uses:="javax.xml.transform,org.checkerframework.checker.index .qual,org.checkerframework.checker.lock.qual,org.checkerframework.che cker.nullness.qual,org.checkerframework.dataflow.qual,org.postgresql, org.postgresql.copy,org.postgresql.core,org.postgresql.fastpath,org.p ostgresql.jdbc2,org.postgresql.largeobject,org.postgresql.replication ,org.postgresql.util,org.postgresql.xml";version="42.2.20",org.postgr esql.jdbc2;uses:="org.checkerframework.checker.nullness.qual";version ="42.2.20",org.postgresql.jdbc2.optional;uses:="org.postgresql.ds";ve rsion="42.2.20",org.postgresql.jdbc3;uses:="org.postgresql.ds";versio n="42.2.20",org.postgresql.jre7.sasl;uses:="org.postgresql.core,org.p ostgresql.util";version="42.2.20",org.postgresql.largeobject;uses:="o rg.checkerframework.checker.nullness.qual,org.postgresql.core,org.pos tgresql.fastpath";version="42.2.20",org.postgresql.osgi;uses:="javax. sql,org.osgi.framework,org.osgi.service.jdbc";version="42.2.20",org.p ostgresql.replication;uses:="org.checkerframework.checker.nullness.qu al,org.postgresql.core,org.postgresql.replication.fluent";version="42 .2.20",org.postgresql.replication.fluent;uses:="org.checkerframework. checker.nullness.qual,org.postgresql.core,org.postgresql.replication, org.postgresql.replication.fluent.logical,org.postgresql.replication. fluent.physical";version="42.2.20",org.postgresql.replication.fluent. logical;uses:="org.checkerframework.checker.nullness.qual,org.postgre sql.core,org.postgresql.replication,org.postgresql.replication.fluent ";version="42.2.20",org.postgresql.replication.fluent.physical;uses:= "org.checkerframework.checker.nullness.qual,org.postgresql.core,org.p ostgresql.replication,org.postgresql.replication.fluent";version="42. 2.20",org.postgresql.ssl;uses:="javax.net.ssl,javax.security.auth.cal lback,org.checkerframework.checker.nullness.qual,org.postgresql.core, org.postgresql.util";version="42.2.20",org.postgresql.ssl.jdbc4;uses: ="javax.net.ssl,org.postgresql.ssl,org.postgresql.util";version="42.2 .20",org.postgresql.sspi;uses:="com.sun.jna,org.checkerframework.chec ker.nullness.qual,org.postgresql.core";version="42.2.20",org.postgres ql.translation;version="42.2.20",org.postgresql.util;uses:="org.check erframework.checker.nullness.qual,org.checkerframework.dataflow.qual, org.postgresql.core";version="42.2.20",org.postgresql.util.internal;u ses:="org.checkerframework.checker.nullness.qual,org.checkerframework .dataflow.qual";version="42.2.20",org.postgresql.xa;uses:="javax.nami ng,javax.sql,javax.transaction.xa,org.checkerframework.checker.nullne ss.qual,org.postgresql.core,org.postgresql.ds,org.postgresql.ds.commo n";version="42.2.20",org.postgresql.xml;uses:="javax.xml.parsers,java x.xml.stream,javax.xml.transform,javax.xml.transform.sax,org.checkerf ramework.checker.nullness.qual,org.xml.sax";version="42.2.20" Implementation-Title: PostgreSQL JDBC Driver Implementation-Vendor: PostgreSQL Global Development Group Implementation-Vendor-Id: org.postgresql Implementation-Version: 42.2.20 Import-Package: javax.sql,javax.transaction.xa,javax.naming,javax.secu rity.sasl;resolution:=optional,com.sun.jna;resolution:=optional,com.s un.jna.platform.win32;resolution:=optional,com.sun.jna.ptr;resolution :=optional,com.sun.jna.win32;resolution:=optional,javax.crypto;resolu tion:=optional,javax.crypto.spec;resolution:=optional,javax.naming.ld ap;resolution:=optional,javax.naming.spi;resolution:=optional,javax.n et;resolution:=optional,javax.net.ssl;resolution:=optional,javax.secu rity.auth;resolution:=optional,javax.security.auth.callback;resolutio n:=optional,javax.security.auth.login;resolution:=optional,javax.secu rity.auth.x500;resolution:=optional,javax.xml.parsers;resolution:=opt ional,javax.xml.stream;resolution:=optional,javax.xml.transform;resol ution:=optional,javax.xml.transform.dom;resolution:=optional,javax.xm l.transform.sax;resolution:=optional,javax.xml.transform.stax;resolut ion:=optional,javax.xml.transform.stream;resolution:=optional,org.che ckerframework.checker.index.qual;resolution:=optional;version="[3.5,4 )",org.checkerframework.checker.initialization.qual;resolution:=optio nal;version="[3.5,4)",org.checkerframework.checker.lock.qual;resoluti on:=optional;version="[3.5,4)",org.checkerframework.checker.nullness. qual;resolution:=optional;version="[3.5,4)",org.checkerframework.chec ker.regex.qual;resolution:=optional;version="[3.5,4)",org.checkerfram ework.common.value.qual;resolution:=optional;version="[3.5,4)",org.ch eckerframework.dataflow.qual;resolution:=optional;version="[3.5,4)",o rg.checkerframework.framework.qual;resolution:=optional;version="[3.5 ,4)",org.ietf.jgss;resolution:=optional,org.osgi.framework;resolution :=optional;version="[1.6,2)",org.osgi.service.jdbc;resolution:=option al;version="[1.0,2)",org.postgresql;resolution:=optional,org.postgres ql.copy;resolution:=optional,org.postgresql.core;resolution:=optional ,org.postgresql.core.v3;resolution:=optional,org.postgresql.core.v3.r eplication;resolution:=optional,org.postgresql.ds;resolution:=optiona l,org.postgresql.ds.common;resolution:=optional,org.postgresql.fastpa th;resolution:=optional,org.postgresql.geometric;resolution:=optional ,org.postgresql.gss;resolution:=optional,org.postgresql.hostchooser;r esolution:=optional,org.postgresql.jdbc;resolution:=optional