Project #3: Due => Dec. 5, BEFORE Class Begins Dec. 9, 6PM (Fri)

In project #3, each team needs to create a web-based interface to their SQL Server database. As we discussed in the beginning of the course, a huge number of Web sites are based largely on a relational database system. The HTML pages that users browse are often dynamically generated from databases, and user actions and inputs result in behind-the-scenes SQL queries and updates. Although the database-backed site you will create is not likely to be as snazzy as a typical Web shopping or auction site, the basic idea is the same.

Part 1: SQL and Web Interface Implementation

• For your finalized schema, populate data accordingly. Populate at least 10 tuples per table.
• Your Web interface should offer the user several different types of interactions that involve the database. You may base your Web interface on the same SQL queries that you implemented in project #2, or you may use new or additional SQL queries.
• For PHP parts, you can start from the examples from the tutorial that TA gave. Furthermore, if you want, you are free to use other tools that can generate PHP codes for you.
• Your interface should support operations that involve data retrieval as well as operations that involve data modification (i.e., INSERT/DELETE/UPDATE). That is, you have to implement, for instance:

     INPUT: Take some inputs from users (e.g., Book title and year)
     OUTPUT: Display a table of answers matching users' selection
  

  or

     INPUT: Take some inputs from users (e.g., employee's old and new addresses)
     OUTPUT: Display a table of answers after the updates (from old to new address)
  

• Implement 10 queries from projects #1 and #2 (you are free to replace old queries by new ones). Make sure your 10 queries contain interesting features. For instance, you should have queries that use features like:
  1. Sub-queries
  2. Join
  3. Aggregates
  4. Set operators
  5. Group-By
  6. Insert/Delete/Update
• Sophisticated error handling is not necessary, however your Web site definitely should not "lock up" regardless of how the user chooses to interact with it. For instance, even if users enters totally invalid input values (thus resulting in SQL error internally), never show the SQL error message returned from Database directly like:

      Warning: SQL error: SQL1086C An operating system error "" occurred. 
      SQL state S1000 in SQLExecDirect in /.../dce.psu.edu/fs/users/d/u/dul13/www/phpdul13/db.php on line 21
      Empty answers were returned from DB2
  

  Instead, your system must be able to prevent such an error, or present a nice and non-cryptic error message to users like

      Error: "Year" must be 4 digit numbers.
  

• Note that this is Database class, not a programming nor a web interface class. Therefore, you don't have to spend a lot of time and efforts to just make your web interface "pretty" or "PHP-rich." Instead, spend time on Database-related features. For instance, if you are doing e-Shopping project, you may want to implement e-Cart to keep track "what products a customer has purchased, etc". If you implement this feature using tables and SQL queries, then that's great. However, if you implement the same feature using JavaScript or PHP module, then I'm less impressed.
• Be C.R.E.A.T.I.V.E -- project #3 is open-ended. Add whatever you can think of to make your applications more interesting and appealing.
• Here are a few web-based database interfaces that may be of interest to you:

  PSU	Amazon.com	Student Project (2004)

Part 2: Presentation Slides

1. At cover page, write down URL address to your web interface (we need this URL so that we can visit the web site to grade. Please make sure to have this information)
2. Brief description about projects #1-3 (e.g., ER, schema, relational tables, SQL, interface, etc).
3. Demonstration. If your database and web-based interface are not functioning correctly during the demonstration, you team may lose points.

Part 3: FINAL Report

1. Cover page: List (1) project title, (2) section number, (3) project team name (i.e., A-H), and (4) member names
2. Part 1: FINAL highlevel description of your project
3. Part 2: FINAL E/R diagram
4. Part 3: FINAL assumptions that you made
5. Part 4: FINAL 10 queries and their SQL representations. For instance, if your query #1 was "Retrieve name and address of employee whose salary is great than 50K", then its SQL representation would be something like:

       SELECT name, address
       FROM   employee
       WHERE  salary > 50000
   

6. Part 5: For each 10 query, run it in SQL Server (either in Enterprise Manager or Query Analyzer) and show answers captured as a screen-shot to prove that your queries were executed well.
7. Part 6: FINAL Relational schema of all tables -- for each table, mention attribute name and type, then primary and foreign keys. Also, mention what referential integrity policy is used on what pair of attributes (delete-cascade or delete-restrict). If you optimized any tables, describe about those, and show the resultant tables.
8. Part 7: Description about your web interface (show a few screen-shots). Explain if there are interesting features that you would like to show off.
9. Part 8: Short description of WHO did WHAT role in your team (all members must participate in the project)

Part 4: Turn-In

1. Drop both report and presentation slide at ANGEL.
2. Also, bring one hard-copy of both report and presentation to class.
3. Like before, the leader of each team drops the copy at ANGEL, and the rest of members drops a "blank" one at ANGEL.