MySQL single table assignment

From Knowledge Kitchen
Jump to navigation Jump to search


In this assignment, you make a web page that displays data you have imported from a CSV text file into a MySQL database table.

Overview

What you will do:

  1. Find a CSV text file with data you find interesting
  2. Import this data into a MySQL table
  3. Make a web page that displays the data in this table
  4. Show the results of 10 different queries on this page


Requirements

Find a CSV text file with data you find interesting

About your data:

  • Pick a data source that interests you
  • The CSV you choose must contain more than 300 records
  • No two students can use the same data source

On your web page:

    • Include a heading for the entire page
    • Include two sentences describing what your data represents.
    • Include two sentences describing what you find interesting in this data
    • Provide a link to the original data source


Import this data into a MySQL table

About your MySQL table:

  • Include an 'id' field that holds a unique identifier for each record
    • An auto-incrementing unsigned int field
  • Include a 'created' field that holds the date and time at which every record was created in the table
    • A timestamp field that defaults to the current timestamp
  • Create other fields using data types that make sense for your data
    • If you expect any of the fields in the table to contain NULL values, be sure your field settings reflect that


Make a web page that displays the data in the table

Use good design: Follow one of the conventional web page layouts

Show the table structure:

  • Include a sub-heading for the "table structure" section of the page
  • Include a table showing all the fields in your MySQL table and their data types and any additional settings
  • Include a one-sentence justification of the data type and settings you used for each field

Show the results of the 10 queries:

    1. How many records there are in your table
    2. Logically group the data in your table and print out only the last (or highest) record in each category.
    3. Display three important fields; at least one should be in alphabetical order; show only the first 10 records in the table.
    4. Use GROUP BY to find the averages on a numerical field in a reasonable breakdown?
    5. Create a user-friendly listing of the first 15 records in alphabetical order.
    6. Write five more queries that are relevant to your data and and yield interesting results.
  • Display clear sub-headings above the results each query
  • Display the SQL query itself and the results of the query in well-formatted, well-designed HTML and CSS

Extra credit:


Submit your work

Submit the following:

  • the URL of your application on the web
  • a zip file of all the files used by your web page
  • A link to the original data source


What links here