Video Game Competition - Score Page (SQL)

Objectives

• Students will develop an SQL query and PHP page that shows information from several different database tables in the form of a pivot table.
• Students will demonstrate the ability to execute advanced SQL queries including the following:
  • LEFT JOIN
  • INNER JOIN Table Aliases

  • SUM(IF(....)<value>,<alternate>)

  • WHERE
  • GROUP BY

  • LIMIT <number>,<step>

• show how to interface with a database using a scripting language (PHP, Python, Perl,etc)
• Students will demonstrate the ability to implement the Big6 skills to solve problems they meet.

Materials

The following materials are needed and should be prepared before beginning the lesson.

• A computer for each student
• A MySQL account for each student on the DB server
• A MySQL Client on each computer
• Big6 Worksheet
• Worksheet with Lesson resources (resources.pdf)
  • Organized (indexed) by topic
  • JOINS
  • MySQL Syntax
  • SELECT Syntax
  • MySQL Functions
    • SUM()

    • IF(),<true>,<false>

    • Control Sequences
  • PHP
    • mysql_connect($host,$user,$password) mysql_select_db($db) mysql_query($query)
    • mysql_fetch_assoc($results)
    • mysql_fetch_array($results,MYSQL_NUM)
  • Connection Information
  • Sample UML Chart
    • Instructions for how to read a UML chart

Audience

• Students in a web development class

TimeFrame

Due to the technical nature of this lesson, approximately two weeks should be set aside for this lesson.

Previous Knowledge

Students will have been studying SQL for several weeks now. The should now have the following skills:

• Mastery of simple SELECT statements
• Mastery of simple filtering using WHERE
• Familiarity with GROUP BY and ORDER BY

Previous lessons have taught the Big6 problem solving skills. Students should be familiar with the 6 steps and will be using them in this lesson as well.

Anticipatory Set

Play some fun music and perhaps a few videos of video games being played while students enter the room. Announce that the students will be working to organize the website for a video game competition.

Lesson Outline

Present students with the scenario they will be working in. "You are a developer in a group organizing a video game competition. The group wants to develop a page on their website that displays a table combining information about the video games, the players, and the scores in the competition. It is not your responsibility to develop the data entry part; another team is in charge of this. Your job is to display the information collected in a fast loading page."

"What you know about the information saved in the competition is this:

• There can be any number of players in the competition.
• There can be a number of different games played in each competition.
• The number of games is not not known and may fluctuate during the competition.
• Each player plays every game.
• Only the top score of each player in each game in a competition is recorded."

"Here is an example of what the table should look like. The format is what many people call a Pivot Table."

Show on the overhead the following table. Explain that the "..." in the 6th column of row 1 means there is an unknown number of games in the competition. The "..." in the 1st column of row 5 means there is an unknown number of players in the competition.

Distribute to the students an UML chart that was prepared to help them understand the data and how the different pieces are related. Show the sample UML on the overhead.

"Your job is to (1) design the SQL queries, limit of 2, that will return the needed information, (2) use PHP to both execute the queries and calculate the average/high/low scores needed, and (3) display the information to the end user. While the teacher will not explicitly teach all of the skills necessary to complete this assignment, he will be available to answer questions and offer advice"

"Since you already have experience using the Big6 to solve simple problems, you will be expected to document how you use it to accomplish this new task before you. Worksheets are available in the front of the room that you will use to summarize how you used the Big6."

Distribute the document "resources.pdf".

"You have in front of you some materials that will help you learn what you might need to know. The booklet contains tutorials, articles, URLs, and some screenshots. As you advance in the project, you may refer to this booklet as well as any other resource found online or in the course textbook."

Assignment

Due Date: Two weeks from now

Deliverables:

• PHP script that displays the desired information. Email a copy of the script and a URL to where it is posted online to the instructor by the due date.
• At least 6 completed Big6 worksheets to show Big6 implementation. Turn these in to the instructor as they are completed.

Requirements/Restrictions on the table:

• No more than 2 SQL queries can be used
• The page must render in under 5 seconds
• All game players listed in the left column.
• All games listed across the top of the table.
• Last column contains the player's total score
• Bottom row contains average/high/low score for each game

Links

• http://php.net

• http://dev.mysql.com/doc/refman/5.0/en/tutorial.html

• http://dev.mysql.com/tech-resources/articles/mysql_intro.html

• http://www.tizag.com/mysqlTutorial/

• http://www.freewebmasterhelp.com/tutorials/phpmysql/

• http://www.devshed.com/c/b/MySQL/

• http://forge.mysql.com/wiki/MySQL_Tutorials

• http://en.wikibooks.org/wiki/MySQL/Pivot_table

• http://dev.mysql.com/tech-resources/articles/wizard/index.html

• http://www.tizag.com/mysqlTutorial/mysqlsum.php

Case-Base Learning Justification

CategoryInst3265,CategoryLesson