INTN 2201: Lab Assignment 7:
SQL Database Basics:

Due Date and Marking

Lab 7 is due by March 27th 2009, by midnight. You should have run your SQL scripts against your lastnamefirstinitial_db and uploaded your new PHP files. Notify your instructor that your work is ready to be marked by whichever method instructed in class.

General

Complete the database assignment questions found at this link. You are to create a file named lab7_autos.sql text file that implements/demonstrates all of the commands required in the assignment document (YOU MUST RUN THE SQL FILE AGAINST YOUR DATABASE TO MAKE IT WORK). You are going to create a lab7_auto_info.php file that will retrieve and display your db content in a web page.

Download the files found in the lab7_example.zip This is a working example of the James Bond films discussed in the lecture period. You must run the sql script (if you have not done so already) provided in order to ensure that the database table exists. Work through the comments found in the "lab7_bond_info.php" page. If your page works you should see something very similiar to this page.

For the class demo your instructor has provided the SQL script to create the James Bond database. It will be used to demonstrate running a script against your PostGreSQL database. You are to modify it to account for the latest James Bond movies with Daniel Craig. Place this file in the sql folder with the auto_info.sql file created above. Provide a link to this file on the lab7.php page.

Specific Requirements

• You are to create a PostGreSQL database on the opentech server, this can be achieved by logging into the opentech server through PuTTY and typing: CREATEDB userid_db (where userid is YOUR userid). When you are prompted for a password your student number is the default. You are to run this CREATEDB command only once this semester (you only have ONE database, but you will CREATE multiple tables in the database).
• Create a PHP page named lab7.php (that implements your header and footer files and is XHTML valid), that has (in addition to your banner and a paragraph explaining the purpose of the assignment) has links to your two (2) SQL files created for this assignment, and links to your modified James Bond table page, and your created auto info page.
• Add a link in your nav bar that connects to this new lab7.php page.
• You are to create a sql sub-directory in your working directory on the opentech server. This is where you are to place your modified (see below) lab7_bond_movies.sql and the new auto_info.sql files.
• You are to take the provided lab7_bond_movies.sql file and modify it to account for the latest James Bond movies with Daniel Craig (INSERT a record into the actor table) and two records in the movies table (for "Casino Royale" and "Quantum of Solace"). Be sure to run this script against your userid_db database (otherwise the script is just a text file, and you will not receive full marks). The provided James Bond page will still work but it will now have two (2) extra rows at the bottom of the table.
• You are to use the lab7_bond_info.php file as a starting point for your lab7_auto_info.php page. Change the SQL statement and PHP code so that it retreives and displays in a table your automobile records in your auto_info table.
• As always, your pages are to have HTML comments <!-- --> , PHP comments /* */ with info about the pages, plus a banner and description paragraph.

Handing In

Publish your pages on the opentech server and submit the URL of your index page to YOUR lab instructor. The preferred method/location for the various instructor is:

Don't mail your files themselves or save them into WebCT; they are useless away from the server.

To publish your pages, upload them to the server in the directory /var/www/users/intn2201/userid (this is the only folder that will work because that's what the Apache Web server software is configured to look in for your files).
Keep your files on-line until you are sure they have been marked!
Directory and file names on the server are case-sensitive, so be sure to put everything in lowercase letters. The default page name is index.html. Don't use spaces, apostrophes, uppercase letters or quotation marks in your file name (keep it simple, using letters and digits, otherwise people won't find your pages).

Linking file names to URLs

Let's say your login ID is speedy, then your file name on the server is:
 /var/www/user/intn2201/speedy/index.html
The corresponding URL is:
 http://opentech.durhamcollege.ca/~intn2201/speedy/
Don't forget the ~ (tilde) symbol. It means "speedy's home directory".

For the rest of the files in your site, you need to give the name in the URL. The file named:
 /var/www/users/intn2201/speedy/page2.html
has the corresponding URL is:
 http://opentech.durhamcollege.ca/~intn2201/speedy/page2.html