Month: March 2021

Overview

This assignment will help you get more practice with PHP syntax, form variables, and conditional statements

Sample

See working sample here: http://303.itpwebdev.com/~ta303/samples/lab-php-form/form.php

Requirements

1. Download starter file.
2. Form Page (form.php)
   1. Fill out <form> tag’s action and method attributes appropriately. 
   2. When this form is submitted, it will run submit_form.php.
3. Form Output / Submission Page (submit_form.php)
   1. Perform all validation as needed.
      1. If any of the fields are not filled out (except Flavor), display “Not provided” message in red.
      2. Tip: The provided HTML pages are using Bootstrap. Therefore, you can use CSS class text-danger for red text and the class text-success for green text.
   2. Show a message with current date & time in following format:
      1. This form was submitted on [weekday], [month] [day], [year] at [time].
         1. Example: This form was submitted on Sunday, September 17, 2017 at 03:15:14 PM.
         2. For the hour, use 12-hour format (not 24) with leading zeros.
      2. Use Los Angeles timezone.
      3. Refer to the PHP official documentation and search for the date function to see how to get the formatting exactly right.
   3. Display following values in submit_form.php after the form is submitted:
      
   4. Submission
      1. When completed, you will need to upload this page to the itpwebdev server via FileZilla (like you have done for labs/assignments earlier in the semester).
      2. Connect to the itpwebdev server on FileZilla. Create a folder named assignment08 and upload all files to this folder.
      3. In your browser, go to http://303.itpwebdev.com/~yourusername/assignment08/form.php to check that the assignment file has been uploaded correctly.
      4. One last thing. In your computer, open up student_page.html that you created in Lab 1. Add a link to this assignment to student_page.html under the heading “Assignments” so that the TAs can easily access your completed assignment.
      5. Upload the updated student_page.html to the itpwebdev server via FileZilla inside the public_html folder. If it asks you want to overwrite the previous file, click Yes.

 

To-do for today’s class

• Open up MySQL Workbench to write some more SQL statements.

Slides

Link to download PDF version.

Completed Files

• 12pm: https://github.com/nayeonk/itp303-spring-2021-12pm/tree/main/lect15-more-sql
• 4pm: https://github.com/nayeonk/itp303-spring-2021-4pm/tree/main/lect15-more-sql

To-do for today’s class

• Log in to cPanel (same credentials as FileZilla): http://303.itpwebdev.com/cpanel
• Download MySQL Workbench (version 8.x): https://dev.mysql.com/downloads/workbench/

Slides

Link to download PDF version of slides.

Completed Files

Completed files is a .mwb file that we created together in class. However, seeing it won’t be very helpful. If you are missing something, I recommend re-watching the last ~30 minutes of the lecture or looking at the Workbench Guide pdf.

• Coming soon…

 

 

To-do for today’s class

• Download song.sql.
• Login to cPanel and create a new database named username_song_db
• Download and install the latest MySQL Workbench if you haven’t already: https://dev.mysql.com/downloads/workbench/.
• Refer to below EER diagram of Song Database.

Slides

Link to download PDF version of slides.

Completed Files

• 12pm: https://github.com/nayeonk/itp303-spring-2021-12pm/tree/main/lect14-select
• 4pm: https://github.com/nayeonk/itp303-spring-2021-4pm/tree/main/lect14-select

Overview

This assignment consists of two parts: importing DVD database (needed for future assignments) and writing SELECT SQL statements against the DVD and song databases.

Requirements

Part 1 – DVD Database Import

1. Download dvd.sql file.
2. Create a new database in cPanel → MySQL Databases.
   1. Name it username_dvd_db (cPanel will prefix your username for you automatically).
   2. For example: ttrojan_dvd_db.
3. Import dvd.sqlfile to the newly created database.
   1. cPanel → PHPMyAdmin → [Click on username_dvd_db Database] → Import.
4. Verify all tables exist and are populated with data.
5. All tables and relationships should match the figure below.

Part 2 – SELECT SQL Statements

Open up MySQL Workbench and create one .sql file. In this file, you will write seven SQL statements – four statements against the song database and three statements against the DVD database you just imported in Part 1. Above each statement, write a comment with the below prompt.

Important: Use JOIN statements when querying from multiple tables as taught in lecture. Do NOT select multiple tables on FROM statement. See this stackoverflow link for more info and also this piazza post.

Song Database SELECT statements (4)

1. Display albums that have the letters “on” somewhere in the album title. Sort results in alphabetical order by album title.
   Sample results:
   
2. Same as #1, but only show album title and artist name (no artist_id) columns.
   Sample results:
   
   
3. Display tracks that have AAC audio file format. Only show track name (alias: track_name), composer, media type name (alias: media_type), and unit price columns.
   1. Use media_type_id column for comparison instead of media type name.
      Sample results:
      
      
4. Display R&B/Soul and Jazz tracks that have a composer (not NULL). Sort results in reverse-alphabetical order by track name. Only show track ID, track name (track_name), composer, milliseconds, and genre name (genre_name) columns.
   1. Use genre_id column for comparison instead of genre name.
      Sample results:
      

DVD Database SELECT statements (3)

1. Display drama (genre) DVDs that won awards. Sort results by year of when the DVD won an award. Show dvd title, award, genre, label, and rating.
   Sample results:
   
2. Display DVDs made by Universal (a label) and have DTS sound. Show dvd title, sound, label, genre, and rating.
   Sample results:
   
3. Display R-rated Sci-Fi DVDs that have a release date (not NULL). Order results from newest to oldest released DVD. Show dvd title, release date, rating, genre, sound, and label.
   Sample results:
   

Submission

When complete, you must upload the completed .sql file on Blackboard. Go to Assignments -> Assignment 7: SELECT Statements and upload the file.

Overview

This lab helps ensure you understand this week’s material and have all software working for upcoming lectures and assignments. You will design, upload, and populate a simple film database.

Requirements

1. Create a database in cPanel -> MySQL Databases
   1. Name it yourusername_film_db (your DB will be prefixed with your username by default).
2. Open MySQL Workbench. Create a new model.
3. Use the same name from step #1 for your schema name in MySQL Workbench.
4. Design a database to store data from Highest Grossing Films table below.
5. Normalize the database.
6. Establish table relationships.
7. Use appropriate data types.
   1. Hint: Range of INT data type is too small to fit Worldwide Gross field.
      1. https://dev.mysql.com/doc/refman/5.7/en/integer-types.html
8. Column names must be lowercase and separated by underscores for consistency.
   1. Example: worldwide__gross.
9. Populate all tables with correct data.
10. Save the model (saves as a .mwb file) and upload on Blackboard.
    1. Go to Blackboard->Assignments->Lab 7: Simple Film Database Design
    2. If you are unsure of your design choice (how you normalized tables), you can add comments on Blackboard rationalizing your decision.
11. Forward Engineer your model to 303.itpwebdev.com server.
12. You can check that the database is successfully on the server by going to cPanel -> Databases -> phpMyAdmin.

Sample

No sample.

Midterm Exam Information

When: Exam will open on Wednesday, 03/17 at 2:00pm Pacific Time and close on Thursday, 03/18 at 11:59pm Pacific time.

Length: 60 minutes to complete the exam.

Where: Blackboard.

Types of questions:

• True/False
• Multiple Choice
• Fill in the blank
• Short Coding

Topics: Topics covered in lecture from Week 1, Intro to HTML to Week 8. Meta Tags and Responsive Web Design

What to do BEFORE the exam:

1. Download Respondus LockDown Browser. You will be taking the exam through this tool. Instructions about Respondus and how to download it are here: https://keepteaching.usc.edu/students/student-toolkit/classroom/respondus/
   1. While you take the exam, the browser will “lock down” and will not allow you to access other applications or websites.
2. Plan ahead to do your best to be in a quiet place during the time you choose to take the exam.
3. Take the practice exam to get familiar with taking an exam on Blackboard. The practice exam is about half the length of the actual exam.
   1. Practice exam does not count toward your grade, it is purely for practice.
   2. When you complete the practice exam, you will see the correct answers.
   3. You may take the practice exam more than once.

During the exam:

1. Open up the Lockdown Browser application on your computer. It will direct you to Blackboard and you will need to log in.
2. Click on ITP 104’s Blackboard section –> click on “Midterm Exam” on the left sidebar.
3. Respondus will not allow you to access other online sources or software on your desktop. This is a CLOSED BOOK exam.
4. When you finish the exam, you can exit. Make sure to click submit.

Important Notes:

• This is a CLOSED BOOK exam: no accessing notes, no online resources, no collaboration with other human beings.
• You WILL need to have memorized the common HTML tags and their attributes. Any HTML tags discussed in lecture or in lab/assignment is fair game.
• You WILL need to have memorized the common CSS properties and values. Any CSS properties discussed in lecture or in lab/assignment is fair game.
• Blackboard “auto saves” your questions as you go, although it’s not 100% reliable so save as you go.

What you should know for the exam

Note: this is not meant to be an exhaustive list. Anything discussed in lecture can appear on the midterm exam. Refer to your lecture notes and assignments for a comprehensive review.

HTML basics

• The basic HTML skeleton
• Concept of opening and closing tags
• What is a tag and what is an attribute? And what do they look like?
• Common HTML tags and their function
  • <p>, <div>, <span>, <strong>, <em>, <h1>, <h2>, <h3>, <ul>, <li>, <ol>, <br>, <img>, <a>, <hr>, <form>, <label>, <select>, <option>, <input>, <textarea> and any other tags discussed in lecture

CSS Style Properties and Stylesheets

• What is CSS? vs HTML?
• Common CSS properties and their values
  • color, font-size, text-align, margin, padding, border, background-color, background-image and any other properties discussed in lecture
• Inline styles vs Stylesheets
  • syntax on inline styling vs using a stylesheet
  • class vs id
  • how to create a stylesheet – <style>
• Centering things horizontally
• Fonts

Create layouts and using div tags

• How to use <div> tags along with specific style properties such as margin, padding, float, width and height to create “boxes” that make up a page’s design and layout.
• Basic understanding of “float” that forces divs to stack horizontally, thus creating columns.
• Calculating column widths
• Basic understanding of CSS positions (e.g. relative vs absolute vs fixed)

Form objects

• HTML tags that create basic these form elements:
  • text
  • radio buttons
  • checkboxes
  • drop-down/select menu
  • textarea
• You need to know how to implement the above form elements as well as know each of their functionality
• How to “submit” a form
• You need to know how to code a form from scratch

Tables

• Basic understanding of tables
• Basic knowledge on how to style tables
• You need to know how to code a table from scratch.

Compound CSS Selectors

Fonts

Positions

Meta Tags and Responsive Web Design

Some terminology that would be helpful to know

• tags vs elements
• tag attributes
• CSS selectors (ID, class, tag)
• stylesheet vs inline style
• CSS property and values
• inline vs block elements (display property)

 

To-do for today’s class

• Save this Dropbox folder in the itp104 folder that you are saving all the lecture material in:
  https://www.dropbox.com/sh/1nvsxfpu15ou778/AAA-Pz3Un9b5bupZUfeR11g_a?dl=0
  Click Download -> Direct Download on top left to get a .zip of this folder.

Slides

View Slides
Link to download PDF version.

Completed Files

Completed files are stored in a GitHub repository. Links below will take you to a list of file(s) from today’s lecture. You can view the code via the browser through GitHub’s UI or clone/download the repo to open the code from your computer.

• https://github.com/nayeonk/itp104-spring-2021/tree/master/lect08/