Dynamic Content Management - Beta

1.1 - Organize Data

Create a spreadseet with the following:

15 total products

Price, name, and cateogry columns

Create only 3 possibly categories

Each product must be assigned to one of the three categories

Email the spreadsheet to your instructor. This activity is worth 10 points.

Activity 1.2 - Normalize Data

Using the spreadsheet create a viso drawing of normalized data.

All data should still be present in the tables when you're finished; none of the data should be discarded.

Hint: You should end up with 4 tables.

Email a .jpg of your finished viso drawing as well as the .xls to your instructor. This activity is worth 10 points.

Activity 1.3 - Normalize More Data

Using the spreadsheetcreate a viso drawing of normalized data.

All data should still be present in the tables when you're finished; none of the data should be discarded.

Hint: One column could be mathematically calculated and thus does not need to be stored in the database..

Email a .jpg of your finished viso drawing as well as the .xls to your instructor. This activity is worth 10 points.

Activity 2.1 - Customers

Create the following empty table in a MySQL text file. For each column you create in the tables, choose proper data types, limit their sizes, and include primary key and other appropriate column attributes.

Table Name

Customers

Columns in that table

Customer ID (index)

Username

Password

Last Name (index)

First Name

Street address

City (index)

State (index)

Zip

Phone

Phone 2

Gender – Must have the value M or F

BIlling address

BIlling City

BIlling State

BIlling Zip

Birthdate

Don't forget to index the column which users will commonly search (don't index the PK; it's already an index).

Then include the queries to:

Show databases

tables

indexes

columns

Submit your .sql as an email attachment to your instructor. This activity is worth 10 points.

2.2 - Area 51

Create the following empty tables in a MySQL text file. For each column you create in the tables, choose proper data types, limit their sizes, and include primary key and other appropriate column attributes.

Table Names

Employees

Columns in that table

Employee ID – Auto Incremented

Last Name (index)

First Name

Middle Initial

Clearance – Must be the value "Confidential", "Secret" or "Top Secret"

Jobs

Job ID – Auto Incremented

Job Title(index)

Job Description

Wage Per Hour

Aliens

Alien ID – Auto Incremented

Species Name (index)

Home Planet (index)

Galaxy (index)

Tech Level - Must be "1" "2" "3" or "4"

Disposition - Must be "Hostile" or "Friendly"

Encounters

Sighting ID – Auto Incremented

Alien ID

Description

Kind - Must be "1" "2" "3" or "4"

Witness ID

Investigation ID

Date

Time

Witnesses

Witness ID – Auto Incremented

First Name

Last Name (index)

Street Address

City

State

Zip

Phone

Investigations

Investigation ID – Auto Incremented

Project Codename (index)

Supervisor ID

Clearance Required – Must be the value "Confidential", "Secret" or "Top Secret"

Assignments

Assignment ID – Auto Incremented

Investigation ID

Employee ID

Job ID

Hours Worked

Date Begun

Date Completed

Artifacts

Artifact ID – Auto Incremented

Artifact Name (index)

Purpose - limit to three values of your choice

Alien ID

Storage Location (index)

Don't forget to index the columns which users will commonly search.

Show databases, tables, indexes and columns

Submit your .sql as an email attachment to your instructor. This activity is worth 20 points.

Final Project Data

Submit the first round of the data you're collecting for your final project.

This activity is worth 10 points.

Activity 3.1 - Shows

Download the starting file. 3.1 - Starting File (.sql)

In a new sql file set up a new database and do the following:

Create a TV show table that only has 3 columns, for show name, network, and catch phrase. Each column should allow 25 characters maximum.

Below that, add statements to:

Add a column called 'Number of Seasons'.

Delete the Catch Phrase column.

Change the name of the Network column to 'nw_1'.

Add a primary key column called 'Show ID'.

Change the Show Name column to allow 75 characters maximum.

Give the Show Name column index status.

Add a column of your choice so that it comes first.

Change the name of the table to "Totally Awesome Shows".

Display the column information for the table.

Submit your finished .sql via email. This activity is worth 10 points

Activity 3.2 - Employee Info

Start MySQL and run the Activity3.2 start file.

The above file has code to make a database and tables. Open it in a text editor and start making YOUR code at the end of its code. Run the file with YOUR code and MY code. Do not change the CREATE TABLE STATEMENTS.

Download the starting file. 3.2 - Starting File (.sql)

Your code should alter the database as follows, in this order:

Manager_Table:

Add a column called 'Last Name'.

Change the name of the Manager Name column to 'First Name'.

Add a timestamp column called 'lastupdate' after the Manager Id column.

Add an index that uses the Last Name column.

Change the name of the table to 'Managers'.

Change table type to InnoDB.

Employee_Table:

Remove the index status from the Fname column.

Remove the primary key status from the Employee_Id column.

Change table type to InnoDB.

Dependant_Table:

Delete the table.

Department_Table:

Delete the primary key column.

In one statement, make the Dept Code column be the last column in the table, and make it the Primary Key with a numeric datatype.

Change the Dept Sector column to only allow the values 'First Floor' and 'Second Floor'.

Change the name of the table to Depts.

Switch the placement of Dept_Name and Dept_Sector columns.

Rename the Dept Code column to D_Code with auto_increment.

Displays:

Display the table status.

Then show a simple list of all tables.

Then show the column information for each table.

Then show indexes for each table.

Submit your .sql as an email attachment to your instructor. This activity is worth 20 points.

4.1 - Add and Delete Data - Candy

Save this start file. It starts you off with code that creates a database, tables, and some row data. Do not tamper with the existing code. At the bottom of the file, add queries that do what the instructions say.

Submit your .sql as an email attachment to your instructor. This activity is worth 10 points.

4.2 - Add and Delete Data - Your Choice

In a new, blank sql text file, create a database and 3 tables of your choice. Think of 3 tables that might logically be related in some way.

Each table must have 5 fields (columns), a primary key, and an auto increment.

Then add 10 records (rows) to each table. Information should be original, creative, and detailed for a higher grade.

Show database, tables, columns and records. Look things over to make sure all your record data got into the tables.

Submit your .sql as an email attachment to your instructor. This activity is worth 10 points.

5.1 - Update Data

Run the starting file at bottom left. WIthout altering the starting file code, make it a sql file and follow instructions at the bottom of the file.

Submit your .sql as an email attachment to your instructor. This activity is worth 10 points.

5.2 - Update Data

Run the starting file at bottom left. WIthout altering the starting file code, make it a sql file and follow instructions at the bottom of the file.

Submit your .sql as an email attachment to your instructor. This activity is worth 20 points.

6.1 - Update Data

Run the starting file at bottom left. Without altering the starting file code, make it a sql file and follow instructions at the bottom of the file.

Submit your .sql as an email attachment to your instructor. This activity is worth 10 points.

6.2 - Update Data

Examine the starting Excel spreadsheet. In PHP MyAdmin build the two tables like they appear in the Excel file. Be sure to fill in the tables with the row data, too.

Export the data into a plain old SQL file. Then add code to perform the tasks listed below:

1. Display the advertiser number and name for all the records in the Advertiser table.
2. Display the advertiser number and name for all advertisers who are represented by Ad Rep number 2.
3. Display the advertiser number, name, and balance for all advertisers who owe our company more than $1000 (balance).
4. Display the advertiser number, name, and amount paid for all advertisers where the Advertiser's Rep is number 3.
5. Display the advertiser name, amount paid, and repNo for all advertisers where the Advertiser's Rep is number 5 and the amount paid is less than $100,000.
6. Display the name of all advertisers whose name contains a letter "R".
7. Display the Advertiser name, balance, and repNo for all advertisers where the Advertiser's Rep is number 4 or the Balance is greater than $10,000.
8. Display the Advertiser names, sorted in ascending order by name.
9. Display the Advertiser name and state, sorted by state, and displaying each state only once.
10. Using an INNER JOIN, display the Advertiser number and name from the advertiser table along with their Rep's first name and last name from the other table.
11. For every advertiser whether they have a Rep or not, display the Advertiser number and name along with their Rep's last name from the other table.
12. For every Rep whether they have an advertiser or not, display the Rep's first name and last name along with their Advertiser's name(s) from the other table, sorted by Rep's last name in descending alphabetical order (Z-A).
13. For only the Rep Jan Brady, display the Rep's first name and last name along with her Advertiser's name(s) from the other table.
14. For only Advertisers from NY, display their Rep's first name and last name along with the Advertiser's name(s) from the other table.

Submit your .sql as an email attachment to your instructor. This activity is worth 20 points.

8.1 - Import

1. Download the Microsoft Excel .xls file and export the row data to two text files: "adrep.txt" and "advertiser.txt".
2. Open the starting Microsoft Access .mdb file. Notice the two tables. Add one table of your choice; it must contain 5 columns and 7 records of original data of your choice (no gibberish, please).
3. Export the row data to three text files, one for each table of the Access .mdb file. You should end up with three txt files from Access. Careful exporting the dates!
4. Put copies of all 5 text files onto the D: drive.
5. Create an activity8 sql file where you create the following queries:
   • The usual database and USE statements.
   • 5 CREATE TABLE queries, one for each table.
   • Use LOAD DATA queries to import the row data from the text files on the D drive into mysql, filling all 5 tables.
   • Show all records from all 5 tables.
   • When you source your file, check all the data carefully to ensure it was imported correctly!

Submit your .sql as an email attachment to your instructor. This activity is worth 10 points.

Please include the following

1. your activity8.sql file
2. one text file for each of your 5 tables

9.1 - XML

Create a XML document tracking typefaces

Name of the typeface

Who designed it

When it was designed

Type of font face

Make at least 5 nodes

Upload your xml file to your web server and email the instructor a link. This activity is worth 10 points.

9.2 - XML + JavaScript

Output the XML Document via JavaScript + HTML + CSS

The page must display ALL of the data

The data must be acompany a label or heading identify what the data is

Style the document

Must follow web standards

Upload your all necessary files to your web server and email the instructor a link. This activity is worth 30 points.

10.1 - PHP + MySQL

Create a Database with at least 5 columns

Create at least 10 records.

Output the data via PHP into a html document.

Upload the index.php to the student web server.

Make sure to style the document

Be Creative! Use images and backgrounds

Zip all necessary files and email them to your instructor. This activity is worth 20 points.

Final Project

Using data collected from the beginning of the term you will create a visualization.

Project rubric

Use any of the following formats. It could be any of the following

Vector Illustration

Code Generated

Painting

Scuplture

Animation

Simulation

Interactive

Requirements

You must visually answer a question, prove a point, or demonstrate data findings

You may mix images, type, charts, graphs, and other visuals to demonstrate your findings

You must present the question somewhere on the piece

The piece can not be larger than 1024 X 768

You will submit an Electronic version (working files, photographs, etc) and a physical version (printout, sculpture, installation)

Examples -visual.ly

Present your physical and electronic version to the instructor for submission. This project is worth 50 points.

Final Exam Review

Please go through this practice exam. It will be very similar to the final exam

• Final Exam Review

Final Exam

Rules and Procedures

Don't sit next to anyone from our class

Open notes, open homework, open web NOT open to digital communicaitons.

No break til done

get stuck? move on try everything

try for partial credit

finish by EOC

when done, give back instructions, backup to U:, clear off D:, take break.