SQL and JSDBC/title> </head> <body style='margin: 0.5cm 1cm; max-width: 800px'> <h2>CPSC 271, Spring 2009, Homework 9:<br> SQL, MySQL, and JDBC</h2> <p>This will be the last regular homework assignment for this course. Aside from this homework, the remaining work in the course consists of the final project and the research report for Homework 5.</p> <p>This homework is about using databases to store data for web applications. The first part of the lab consists of some SQL exercises using the MySQL console interface. <b>You should fill out the answers to the exercises on this handout, and turn in the handout as part of your homework.</b></p> <p>The second part of the homework asks you to work on a small web application that uses a database. The parts of the application that use the JDBC database API are missing. You need to write those parts. The application consists of a single JSP page, <i>index.jsp</i>. (It is an example of a single-page application, which is sort-of the polar opposite of the MVC pattern.) <b>To turn in your work for this part of the homework, you should copy your completed <i>index.jsp</i> file to your homework directory inside <i>/classes/s09/cs271/homework</i>.</b></p> <hr> <h3>Part 1: SQL Exercises</h3> <p>The file <i>/classes/s09/cs271/pollsdb.init</i> contains the same table-creation commands as the file, <a href="pollsdb_create_tables.txt" style="text-decoration:none; color:blue">pollsdb_create_tables.txt</a>, that was handed out in class, along with commands for inserting a lot of data into the tables.</p> <p>Start up the MySQL console, log onto the MySQL RDBMS server on mathb.hws.edu, and give the <i>use</i> command to select your database, as described in the <a href="march23.html" style="text-decoration:none; color:blue">previous handout</a>. Enter the command</p> <pre> source /classes/s09/cs271/pollsdb.init; </pre> <p>to execute the database initialization file. The command</p> <pre> show tables; </pre> <p>should then list the five tables that have been created. <b>Note: In the MySQL console, you should end every statement with a semicolon. You can type in statements that extend over more than one line. The semicolon marks the end of the statement.</b></p> <p>Note: You can ask MySQL to tell you the structure of a table with a command such as</p> <pre> describe answers; </pre> <p>which lists information about the table named <i>answers</i>. This can be useful for exploring a database, or if you forget the name of a column.</p> <hr align=center width="33%"> <p>For the rest of this section, you should use the MySQL console to answer the questions, or to check your answer when the question asks for an SQL statement, and enter your the answers in the spaces provided. <p><b>Ex. 1.</b> What values are output by the following command?</p> <pre> SELECT username FROM rolls WHERE roll="creator"; <b>Answer:</b> </pre> <p><b>Ex. 2.</b> What value is output by the following command, and <b>why?</b> <pre> SELECT count(*) FROM rolls WHERE roll="creator"; <b>Answer:</b> </pre> <p><b>Ex. 3.</b> You are listed as a user in the database, under your regular HWS username. What password is listed for you in the database? What SQL command did you use to determine this? <pre> <b>Answer:</b> </pre> <p><b>Ex. 4.</b> How many votes were cast in poll number 3 (the total for all possible answers)? What SQL command did you use to determine this? <pre> <b>Answer:</b> </pre> <p><b>Ex. 5.</b> Describe the table produced by the following command, and explain how the command works. As part of your answer, explain the statement begins <i>select polls.id</i> rather than simply <i>select id</i> <pre> SELECT polls.id, username, question FROM users, polls WHERE users.id = polls.creator_id; <b>Answer:</b> </pre> <p><b>Ex. 6.</b> According the data in the database, how many votes did <b>you</b> cast altogether? What SQL statement or statements did you use to determine the answer? <pre> <b>Answer:</b> </pre> <p><b>Ex. 7.</b> Describe the table produced by the following SQL command, and explain the purpose of the command: <pre> SELECT answer, count(*) FROM answers, responses WHERE answers.poll_id = responses.poll_id AND responses.answer_number = answers.answer_number AND answers.poll_id = 1 GROUP BY answers.answer_number; <b>Answer:</b> </pre> <p><b>Ex. 8</b> Give the two SQL commands that would be used to add a new user to the database, with username "chris", password "frazzled", and role "member". (Try it!) <pre> <b>Answer:</b> </pre> <p><b>Ex. 9</b> Give the SQL command that would be used to change <b>your</b> password in the database to "foo". (Try it!) <pre> <b>Answer:</b> </pre> <p><b>Ex. 10</b> Give the two SQL commands that would be used to delete from the database the answer number 6 for poll number 1, along with all the votes for that option. (Try it!) <pre> <b>Answer:</b> </pre> <p><b>Ex. 11</b> The web application in the second part of this lab requires three database tables. Here are descriptions of the tables. Translate these descriptions into SQL CREATE statements, and write the CREATE statements in the space at the right. <b>Use your statements to create the tables, so that you will be set to do Part 2.</b></p> <br> <table style="margin-left: 1cm" border=1 cellpadding=5 cellspacing=0> <tr><td colspan=2 align=center><b><big>Table Name: <i>userlist<i></big></b></td></tr> <tr align=center><td><b>Column Name</b></td><td><b>Type of data</b></td></tr> <tr><td>id</td><td>integer</td></tr> <tr><td>user</td><td>string</td></tr> <tr><td>password</td><td>string</td></tr> </table> <p style="margin-left: 1cm">The primary key for <i>userlist</i> is <i>id</i>;<br> <i>id</i> is an auto-incremented column.</p> <br> <br> <br> <table style="margin-left: 1cm" border=1 cellpadding=5 cellspacing=0> <tr><td colspan=2 align=center><b><big>Table Name: <i>movies<i></big></b></td></tr> <tr align=center><td><b>Column Name</b></td><td><b>Type of data</b></td></tr> <tr><td>id</td><td>integer</td></tr> <tr><td>name</td><td>string</td></tr> </table> <p style="margin-left: 1cm">The primary key for <i>movies</i> is <i>id</i>;<br> <i>id</i> is an auto-incremented column.<br> There is an index on the <i>name</i> column.</p> <br> <br> <br> <table style="margin-left: 1cm" border=1 cellpadding=5 cellspacing=0> <tr><td colspan=2 align=center><b><big>Table Name: <i>ratings<i></big></b></td></tr> <tr align=center><td><b>Column Name</b></td><td><b>Type of data</b></td></tr> <tr><td>id</td><td>integer</td></tr> <tr><td>user_id</td><td>integer</td></tr> <tr><td>movie_id</td><td>integer</td></tr> <tr><td>rating</td><td>integer</td></tr> </table> <p style="margin-left: 1cm">The primary key for <i>movies</i> is <i>id</i>;<br> <i>id</i> is an auto-incremented column.<br> there are indexes on <i>user_id</i> and <i>movie_id</i>.</p> <br> <br> <p><b>Ex. 12</b> Add at least three users to the <i>userlist</i> table that you created in the previous exercise. What SQL statement or statements did you use to add the users? </p> <pre> <b>Answer:</b> </pre> <p><b>Ex. 13</b> Make sure that your name is on this handout before you turn it in!</p> <br> <hr> <h3>Part 2: A Web Application Using JDBC</h3> <p>The web application that you will work on for Part 2 uses a database to store a list of movies and user ratings for the movies. Only registered users can access the application. (Users can only be added by directly manipulating the database; no web interface is provided for adding users.)</p> <p>The application is defined by one long file. I have written the application, then removed some of the code that uses the database. Your job is to restore the deleted functionality. You can find the sections that you have to write by searching for the string "*****".</p> <p>The file will be available no later than Monday, March 30. It will be stored on the file server as <i style="white-space:pre">/classes/s09/cs271/assg8/index.jsp</i>.</p> <p>You should start a new NetBeans Web project. Replace the contents of <i>index.jsp</i> in the project with the contents of the file <i style="white-space:pre">/classes/s09/cs271/assg8/index.jsp</i>. You need to start by creating a connection to <b>your</b> database on <i>mathb.hws.edu</i>, near the top of the file. Once that is done, you will be able to run the project, and you can start implementing the missing functionality -- starting with logging on.</p> <hr> <p align=center><b><big> Both parts of this homework are due on Friday, April 10.<br> (But an extension might be given.) </big></b></p> <hr> </body> </html>