CSE 305 -- Principles of Database Systems
Fall 2023
Second Assignment for the Database Programming Project
Out: 10 October 2023
Due: 6 November 2023, 4:00pm, one per team
In this assignment, you are to create and test all the SQL DML statements that
you will need to implement your online movie rental system. You should
therefore once again carefully read the
Requirements Specification document available on the
course web site. In Section 3 of that document, all of the transactions that
the system is required to support are detailed. For each of these transactions,
you should create an SQL statement (SELECT, INSERT, DELETE, UPDATE). For some,
you may need several different statements or a sequence of statements, if the
transaction is complex or takes in-parameters. For parameterized transactions,
you should clearly document the parameters.
You should write your SQL statements based on the relational database you
designed in the first project assignment. You should,
however, modify your design to correct any problems we found while grading it.
For this purpose, you can consult the E-R and SQL models we have provided in
the solution to the first project assignment (soon to be available in the Project
Assignments folder on Brightspace).
In your next project assignment,
you will be asked to write the JDBC code
that will
allow users of your system to interact with the database server in order to
reent movies, etc.
You must test your SQL statements by executing them against the test
database, or Demo Data, that is provided. This
means you must load your schema with the information described in the Demo Data. For parameterized transactions, choose
"typical values" for the parameters from that Demo Data for your testing. You
should provide the input, parameters, SQL definition, and the output of running
it against the Demo Data, for each transaction in Section 3 of the Requirements
Specification document.
Pleased submit your assignment on BrightSpace.
You must hand in a document that includes:
- The name of your team (i.e. this should also be the name of your
online movie rental system).
- The names and e-mail addresses of the team members.
- A dump (listing) of all your tables with the initial Demo Data loaded
into them.
- For each transaction in Section 3 of the
Requirements Specification, you should provide:
- The definition of the transaction (can be taken directly from
Section 3, extended and/or modified as you see fit).
- The type and definition of any in-parameters to the SQL
statement(s).
- The SQL statements that implement the transaction (with
parameters as ?'s, if necessary).
- An execution of the SQL statements of the transaction
against your database containing the Demo Data, using "typical" values
for the parameters, showing the SQL statement used and the output.
- A short description, if necessary, of any particular issues
concerning this transaction.