Project Deliverables

The following summarizes the expectations for the final project deliverables:

In-class presentation

You will present a 3-minute demonstration of your project during Lesson 40.

You are not expected to demonstrate all aspects of your project within this limited time frame: focus on the why you chose the project and the aspect(s) of which you’re most proud, found most interesting, etc. Given the number of teams, the time limit will be strictly enforced.

Project submission

You must submit an archive of your web application and a file that contains all the SQL commands to (re)create your database. The latter file may be “hand written” or a “dump” created by the database management system (DBMS), but must be readable by someone familiar with SQL. (If readable by you, then this criterion is likely satisfied.) See instructions for an example of such a file.

Instructor demonstration

You must schedule a half-hour block to demonstrate your web application to the instructor. This demonstration should be no later than next Wednesday, 12 May. Expect to exercise all aspects of the application’s functionality during this presentation.

Instructions to create a MySQL database

As part of the schema design, you wrote SQL queries to create a relational schema and to populate it with representative data. These SQL queries should be used – possibly with modifications – to create the database in MySQL and allow others to (re)create the database, including representative data.

The following steps outline how to create the database in MySQL:

1. Create an SQL script (e.g., database.sql) that defines the database schema and populates it with representative data.

   For example, the following queries create a simple database with two relations:

   -- drop existing tables (if they exist)
   
   DROP TABLE IF EXISTS ExampleWithForeignKey;
   DROP TABLE IF EXISTS Example;
   
   
   -- create the tables
   
   CREATE TABLE Example (
     id INTEGER AUTO_INCREMENT NOT NULL, -- unique identifier (automatic)
     name CHARACTER VARYING(24) NOT NULL UNIQUE,
     description CHARACTER VARYING(128),
   
     PRIMARY KEY (id)
   );
   
   CREATE TABLE ExampleWithForeignKey (
     exampleId INTEGER NOT NULL,
     value CHARACTER VARYING(32) NOT NULL,
   
     PRIMARY KEY (exampleId, value),
     FOREIGN KEY (exampleId) REFERENCES Example (id)
         ON UPDATE CASCADE ON DELETE RESTRICT
   );
   
   
   -- populate the database with representative data
   
   INSERT INTO Example (name, description) VALUES
   ('one', 'un, uno, eins, ...'),
   ('two', 'deux, dos, zwei, ...'),
   ('three', 'trois, tres, drei, ...');
   
   INSERT INTO ExampleWithForeignKey (exampleId, value) VALUES
   ((SELECT id FROM Example WHERE name = 'one'), '1'),
   ((SELECT id FROM Example WHERE name = 'one'), '0b00000001'),
   ((SELECT id FROM Example WHERE name = 'one'), '0o00000001'),
   ((SELECT id FROM Example WHERE name = 'one'), '0x00000001'),
   ((SELECT id FROM Example WHERE name = 'two'), '2');
   

   This file is the one that you should submit with an archive of your web application so that the instructor can test your application.

   Your submission for the schema design might contain PostgreSQL-specific commands. Consequently, some changes might be required before using the file to create a MySQL database.

2. Create the database in MySQL using the following command in a terminal window:

   mysql --user=student --password student < database.sql
   

   This command must be executed in the same directory as your database script (database.sql).

   If you receive any errors, then you must fix them and run the script again. (Lack of output indicates the absence of errors – i.e., the script executed correctly.)

3. Use the MySQL client to verify that the database was created correctly:

   mysql --user=student --password student
   

   The MySQL client is essentially a terminal for entering SQL commands. For example,

   SELECT * FROM Example;
   

   should return something like

   id	name	description
   1	one	un, uno, eins, …
   2	two	deux, dos, zwei, …
   3	three	trois, tres, drei, …

At this point, you should be ready to integrate the project website with the MySQL database. For example,

<?php
$connection = new mysqli("localhost", "student", "CompSci364", "student");

$query = "SELECT id, description FROM Example WHERE name = ?";
$statement = $connection->prepare($query);
$statement->bind_param("s", $_GET["name"]);
$statement->execute();
$statement->bind_result($id, $description);
if ($statement->fetch()) {
 // do something with the result
}
 ?>