create a web page

CMSC 508 – Database Theory

Virginia Commonwealth University, Spring 2016

Database and the Web

Due date: May 8, 2016

1. Objective

The objective of this assignment is to develop a small dynamic website interacting with an Oracle SQL

database. The topic, theme, and programming languages of the website are open to the student

preferences (although PHP is strongly recommended). The student may choose any application

related to his/her own interests and background. Some examples may be: online bike store, DNA

database, University students’ records, music/movies rating web, etc. The only requisite is to satisfy

the functional requirements to show the student’s expertise and knowledge on the use of the

database management system and a web programming language. Use of 3rd-party code is allowed, as

long as it is referenced properly. However, the assignment will be evaluated according to the student’s

capability of developing his/her own code, and the completeness and functionality of the web. Use of

HTML5, AJAX, CSS, etc. technologies will be positively valued but it is not a requisite.

2. Required functionality

Whichever the website application is, it must allow the following functionality to interact with the SQL

database via forms, reports, and navigation:

 Insert new data into tables

 Update information in the tables

 Delete information in the tables

 Selection of information filtered by conditions, and sorting of the results

 Computation of statistics based on the data within the tables

 The database should also comprise triggers and views related to the web procedures

Example: online bike store. Catalog of bikes: insert/update/delete bike models, information, units.

Customers: insert/update/delete customers, allow registration of new customers. Shopping:

customers ordering bikes and parts to deliver in a given location. Store manager: statistics of best

customers, largest sales window time based on order statistics, etc.

All transactions, errors and constraint-checking must be carried out using the proper database

procedures, and the website should reflect the result of the operation. The consistency and integrity

of the information in the database is a priority. Example: website for movies/airplane tickets cannot

allow two people to choose concurrently the same seat. Abort the transaction and display error to

the user.

3. Development instructions and deliverables

The student must deliver:

1) A text document with the definition and constraints of the problem, the analysis of requisites and

the desired functionality.

2) A complete Entity-Relationship diagram reflecting the data model for the given problem.

3) SQL scripts for all the queries, creation of tables, views, triggers, etc.

4) The website fully working at jasmine.cs.vcu.edu. Should any user/password be required to access

user/administrator profiles in the web, they must be provided as well to test all the website

functionality and its correctness.

Instructions for accessing the web folder:

Connect to the host jasmine.cs.vcu.edu using a SFTP client such as Filezilla. The SFTP port is 20035,

the username is your VCU eID, the password is your V#. There is a folder named public_html where

you must place your website files. After uploading the files, you may access at the address

http://jasmine.cs.vcu.edu:20038/~username/myfile.php where username is your VCU eID. (20038 is

the Apache webserver port, while 20037 is the Oracle SQL port, and 20039 is the APEX web server).

The main page must be named index.html or index.php

Recommended tutorials for PHP + Oracle (installation prerequisites are obviously not required)

Tutorial: Developing a PHP Web Application with Oracle Database 11g http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/oow10/php_webapp/php_webapp.htm

Tutorial: Web 2.0 Applications with PHP and Oracle Database 11g http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/opensrclang/php/php.htm

Example of connection setup:

<?php

// Create connection to Oracle

$conn = oci_connect('oracleusername', 'oraclepassword', 'localhost:20037/xe'); // this is localhost, i.e., jasmine.cs.vcu.edu

if (!$conn) {

$m = oci_error();

echo $m['message'], "\n";

exit;

}

else {

print "Connected to Oracle!";

}

// Close the Oracle connection

oci_close($conn);

?>