N Tier Database Project

Project Name: N Tier Database Project
Format: Hands-on using Amazon Web Services (aws)
Length: Two weeks
Due: By start of class on Wed Nov 11th
Teams: 2 students per project team, students select their partner, must be different from previous project(s)

Summary

In this project, you'll build a multi-tier system that utilizes a MySQL database. You'll complete the project using a package of software called "LAMP" where each letter corresponds to a technology component:

  • "L" for Linux Operating System
  • "A" for Apache Web Server
  • "M" for MySQL
  • "P" for PHP Scripting Language

Instead of building a "LAMP" environment, you'll use one provided by Amazon Web Services (aws). On aws, you can "provision" a computer with software already installed. Thus, you'll be using a computer "on the cloud" to host your LAMP environment. After building a database app on that computer, you'll provision another computer with LAMP. You'll use the first computer to access the database on the second computer creating multiple tiers:

Tier 1 Tier 2 Tier 3
Your laptop LAMP1 with PHP Script LAMP2 with MySQL Database

N Tier Database Project (instructions at the bottom of the page)

Resources in the Cloud

One of the many forms of cloud computing is to use "resources" in the cloud. A common example of this is data storage "on the cloud" with a site like xdrive.com. For this project, we're going to use Amazon's Elastic Compute Cloud (EC2). As a first step, sign up for EC2:

http://aws.amazon.com/ec2/

Read about EC2 on this page. As a class, we'll discuss the features and functionality of EC2. In particular, pay close attention to the section titled "Operating Systems and Software".

AWS Management Console

As described previously, you are going to "provision" computers for use in this project. Each computer you provision has an hourly fee associated with it. To offset this cost, Amazon has graciously given us a $100 credit per student. I recommend you use one Amazon account for this whole project. To apply the credit to your account, go to:

https://aws-portal.amazon.com/gp/aws/developer/account/index.html?ie=UTF8&action=credits

When you are ready, let me know and I will give you the code for the credit.

Each machine that you provision costs roughly $34 to run continuously for 2 weeks. Your $100 credit will easily cover this. Be sure, however, to terminate your machines when you are finished with this project so you don't continue to accrue charges. Update: On Nov 1, pricing dropped from 10 cents per hour to 8.5 cents per hour dropping the two week cost to roughly $30 per machine.

Now that you have money to pay for your machine, let's go provision one. You'll use the AWS Management Console to create and manage your "instances". You can access the console here:

http://aws.amazon.com/console/

You can get to this at any time by logging into aws. Under "Your Account" is a link to the "AWS Management Console".

To finish signing into the Console, click on "sign into the AWS Console" for "EC2".

Security and Key Pairs

One of the most confusing elements of this process is the security approach. You'll be accessing your computer from your laptop. Amazon needs to be able to authenticate you — to make sure you are allowed to log onto the Linux computer residing at their site. Amazon uses "Key Pairs" to do this. You'll need to generate a key pair for this process. On the Console, you'll see a section called "My Resources". In this section, you'll see "0 Key Pairs".

  • Click on "0 Key Pairs"
  • Click on "Create Key Pair"
  • Provide a name for your Key Pair. You can use any name you wish.
  • Click "Create"

VERY IMPORTANT You will be presented with a window for you to save your key. The format is a PEM file. You must put this file somewhere where you can easily access it. I recommend your G drive or a USB drive. You cannot create this file a second time. You will be starting your "instance" with this PEM file. If you lose it, you will not be able to access your remote computer.

  • Save the PEM file

Creating your Amazon Machine Instance (AMI)

Now you are ready to provision a computer for your use. Amazon has hundreds of configurations available for use. You can view them all here:

[http://developer.amazonwebservices.com/connect/kbcategory.jspa?categoryID=171]

To launch your own AMI, go back to the "EC2 Dashboard" on the Console and click on "Launch Instances". You'll be presented with a list of AMI's. We're going to use:

  • Click "Select" next to LAMP Web Starter (AMI Id: ami-2cb05345)
  • In the "Launch Instance Wizard"

1 instance
small
select the keypair you created earlier
select all 3 security groups (basic, default, and webserver)
launch
close

  • Click "Instances" on Console
  • Instance should be starting (it usually takes a few minutes to start)
  • Once the Instance is started, right click on it. You'll see information about the instance. The "Public DNS" is the most important of these details.
  • Copy "Public DNS"
  • Open your web browser and paste in this URL
  • What do you see? This page was part of the configuration. It's a default PHP page (html page with special scripting) that was provided as part of this AMI. If you see the PHP page, you are all set.

Connecting to your AMI

The next part will be confusing. Please read carefully and stay with me.

To connect to your AMI, you need a piece of software on your computer (in this case, the KSB lab computer) that will allow you to connect using a "Secure Shell" referred to as SSH. A "Shell" in Linux is the command line environment for you to interact with the operating system. It's a bit like the CMD interface on Windows (if you do start->run->cmd on windows, you'll see what I mean.) A "Secure Shell" means that you are going to go through some security steps to access the shell.

First, we'll download a free SSH program called Tunnelier. You can download it here:

http://www.bitvise.com/tunnelier-download

Tunnelier is very robust and more than we need for this project. But the interface is one of the best and the easiest to use (in my opinion.) Download and install Tunnelier and launch the program.

  • On the login tab, cut/paste the "Public DNS" for your AMI into the box called "Host"
  • Leave SSH as port 22. When you created your AMI, you told Amazon which doors to leave open. You left door 22 open.
  • In username, put "root". This is the standard login name for Linux (and MySQL, if you recall.) "root" is equivalent to an administrator account on Windows.
  • The tricky part is next. We need to tell Tunnelier about our PEM key.
  • Click on "User Keypair Manager"
  • Click on "Import"
  • Locate your PEM file on your computer (G drive or USB)
  • Click "Import"
  • Close the KeyPair window
  • Back on the login tab, for the "Initial Method" change the pull down to "public key - slot 1"
  • Click "Login" and hold your breath
  • If you see some windows come up (xterm, etc.), you are connected!

A Short Introduction to Unix (Linux)

Complete the following tutorials for an introduction to Linux. You'll use the window that says "xterm" while in Tunnelier.

Note that you'll use this same window for the project when you want to access mysql. At the prompt, type "mysql" and you'll be logged into MySQL as root.

http://www.ee.surrey.ac.uk/Teaching/Unix/unix1.html
http://www.ee.surrey.ac.uk/Teaching/Unix/unix5.html (start at section 5.3)

MySQL, Apache, and PHP

Jump to this page to continue. You'll return here for the actual project assignment which is below.

N Tier Project Assignment

Create an "N tier" system with two separate AMI's. Both AMI's will run Fedora LAMP.

AMI #1: php scripts to access MySQL database on AMI#2; html forms
AMI #2: MySQL database

Your project must have the following:

MySQL database (on AMI #2)

  • Once again, we'll use the CRUISE database.

http://itec470.wikidot.com/local--files/analytics/AUCruise.xls
Your MySQL database must have all six tables (VOYAGE, CRUISE, PASSENGER, PORT, SHIP, VISIT) with all data.

Note: You can create separate PHP scripts for each of the tables and their data OR you can use a PHP function called fgetcsv to read a csv file (comma delimited), put the data into an array, and use it to populate each table. You get 10 extra points on this assignment if you create and populate your tables using fgetcsv.

PHP Scripts (on AMI #1)

  • Create tables
  • Populate tables (insert data)
  • Write SQL to complete the items below and display your results. (Note: You must Issue the SQL and return the results using PHP.)
  1. Find the passenger name and phone number for the passenger with SSN “356787494”
  2. Find the total number of docks available at all ports
  3. Find the ship name and ship builder of the heaviest ship by weight
  4. Find the cruise start and end date (not the port arrival and departure dates) for all cruises into ports in Venezuela
  5. Find the fare paid by the passenger with the last name “Brown” where the cruise director was “Lee”

This section was modified on Nov 4th to make the project deliverables clear.

To turn in your project, turn in the following:

  1. PHP to create tables
  2. PHP to insert data into tables
  3. PHP for 5 SQL statements
  4. Data (presented in PHP) from issuing 5 SQL statements

(Note: All PHP scripts run on AMI #1 and access the database on AMI #2)


Note: You can use internet resources for SQL syntax. You may not share your SQL or PHP with other project teams. Each team must work independently.

VERY IMPORTANT Be sure to terminate your machines (instances) when you are finished with this project so you don't continue to accrue charges. You can see how much of your credit you used by going back to the credits page:
https://aws-portal.amazon.com/gp/aws/developer/account/index.html?ie=UTF8&action=credits

Project Grading

The project will be graded based on two criteria:

  • Completeness (all requirements)
  • Correctness (functionally accurate)
  • Creativity (where applicable, creative approach)
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License