CS2623: Server-Side Programming
SSP08: MySQL Tables

Objective:

• Create and populate databases and tables using localhost
• Execute simple queries
• Export and import tables

Points

• Full Credit: 40 Points
• Late/Resubmit Credit: 36 Points

Situation

This week's assignment does not involves any PHP coding. It focuses on creating and populating the databases that will be used with the Chinese Zodiac web site. First, some background information. All work must be done using PHPMyAdmin NOT using the command line as described in the text.

On Apollo1, students cannot create new databases. Therefore, four generic databases have been created and are available for use. The names may be something similar to user#####a, user#####b, user#####c, and user######d. The database named user#####a will be used as the ChineseZodiac MySQL database. So all references (except p. 443, items 1-3) will be replaced by this more generic name.

As part of the experience, the database named ChineseZodiac is to be created on localhost either on your personal computer or on one of the lab computers. All of the exercises from the Chapter 7 Discovery Projects must be done locally using PHPMyAdmin. Upon completion of the exercises, the four tables are to exported. They are then to be imported into the user#####a database on Apollo1. If you are working in the Student Computer Center,allow enough time so the assignment can be completed in one sitting. Also, be sure to delete the database when you have completed the assignment so other students will not have access to it.

To save time, use the following .... right-click and select Save Link As (FireFox) or Save Target As (IE):

• Discovery Project 7-4: zodiac_years.txt
• Discovery Project 7-5: proverb_load.txt

Be sure to read the text above each image below. It provides specific information about the assignment.

Specifications

• Discovery Project 7-1: Follow the instructions in the text creating the new database (chinese_zodiac - I used ChineseZodiac as the name.) on localhost. Then within ChineseZodiac, create a table named zodiacsigns. Designate a primary key which will be the Sign field.When completed table will be populated as shown below.
  
  
  
  
• Discovery Project 7-2: Following the instructions, the result should be the following table: (Note: The structure of this table does not really allow for a primary key. Although this is not a good practice, it will be used "as is" for now.
  
  
  
  
  
• In Discovery Project 7-3: zodiacyears will have two fields.
  • Year which is the primary key, smallint, and 4 characters
  • Sign which is varchar and 7 characters
    
    
    
    
    
• Discovery Project 7-4: You may create the text file or use the following: zodiac_years.txt. The resulting query should appear as follows keeping in mind that there are actually about 130 records...only the first few are shown here. It may be easier to use the Import tab in PHPMyAdmin.
  • Select Import
  • Click on the Browse... button
  • Use UTF-8
  • Under Format: select "CSV using LOAD DATA"
  • Under Format-Specific Options, change columns will be separated by from ; to \t  ( \t represents tab)
  • Uncheck "Use LOCAL keyword".
  • Click on Go
    
    

Discovery Project 7-5:

Use the following:

proverb_number - INT, Primary Key, auto increment the field

proverb -VARCHAR, 400 chars (book says at least 100, change it to 400)


display_count, INT

You may create the textfile or use the following: proverb_load.txt



• 
  

Importing the tables to Apollo1

Once the work on ChineseZodiac has been completed locally, the tables must be imported into your user####a database on Apollo1

Follow the instructions in this weeks materials of use this link: MySQL Export and Import

Your Apollo1 PHPMyAdmin account will be checked as part of the grading process

Recurring Specifications:

None for this assignment since there are no web pages being created.

Assignment Submission

• Create a new folder entitled Chapter7. Inside the folder, create another folder named either assets or images.
• Create screenshots for the following items. The screenshots must be in a size and resolution that are easily readable. The URL must be shown on all screen shots so it can be verified that the work was done on localhost and not Apollo1. (See image below).
  • Discovery 7-1: Results of SELECT query in item 8.
  • Discovery 7-2: Structure of zodiacfeedback table.
  • Discovery 7-3: Upload DP7-3.jpg | gif | png into the ChineseZodiac/Images folder as instructed. Also include it in the Chapter7/images|assets folder. The one in the Chapter7 folder will be used in the web page.
  • Discovery 7-4: Results of the SELECT query that includes results that have been sorted by sign and then by year.
  • Discovery 7-5: Results of a SELECT query that includes all of the fields which have been sorted by proverb_number.
• Then create a very basic web page (index.htm/l) that will label and display each one of these images. This is only being used to grade your MySQL work. It does not have to be fancy.
• Upload the Chapter7 folder to apollo1. DO NOT place it inside of the ChineseZodiac folder.
• In the image below, note that the URL indicates localhost and that the name of the database is ChineseZodiac.
  
  
  

In the LMS assignment

• enter the URL: http://apollo1.occc.edu/user#####/Chapter7
• enter your Apollo1 login and password so I can check the user#####a database on the server to verify that the tables were exported/imported correctly.
  
  
• Submission links must open web page in a new window (not inside of the LMS) and display the page (and not a directory)
• Assignments not opening in a new window cannot be graded. If it is still within the late/resubmit period, they can be resubmitted (10% penalty). If it is after the final deadline, the work can no longer be submitted.