Category: 20213_itp303

Overview

This assignment helps you practice more SQL statements that manipulate the database, such as insert, update, and deleting records. You will write eleven SQL statements: five for Song database and six for DVD database.

Requirements

Create one .sql file for all eleven SQL statements. Use comments to write the prompt above each SQL statement.

Part 1 – Song Database

1. Create a view mpeg_tracks that displays all tracks with MPEG audio file format. Display track name (track_name) artist name (artist_name), composer, album title (album_title), and media type (media_type). Sort results in alphabetical order by track name.
   

   

2. Add a track below to the database:
   1. Track Title: The Ocean
   2. Album: The Song Remains The Same (Disc 1)
   3. Artist: Led Zeppelin
   4. Media Type: MPEG audio file
   5. Genre: Rock
   6. Composer: John Bonham/John Paul Jones/Robert Plant
   7. Milliseconds: 248000
   8. Bytes: 7990000
   9. Unit Price: 0.99
3. Make the following changes to the track added above:
   New Bytes: 8998765
   New Unit Price: 1.99
4. Delete track “20 Flight Rock” by BackBeat from the database.
5. Display how many tracks there are for each album. Show album ID, album title (album_title), and track count (track_count).
   1. Note: some of your counts might be different due to added or deleted records during in-class exercises.
      

      

Part 2 – DVD Database

1. Create a view dramas that displays all drama DVDs with release date not set to NULL. Show DVD ID, DVD title, release date, award, format, genre, label, rating, and sound.
   
2. Add a new DVD with the below information:
   1. Title: The Godfather
   2. Release Date: March 24, 1972
   3. Award: 45th Academy Award for Best Picture
   4. Format: Fullscreen, Widescreen
   5. Genre: Drama
   6. Label: Paramount
   7. Rating: R
   8. Sound: DTS
3. Make the following changes to the DVD titled “Zero Effect.” This DVD already exists in the database.
   1. New Label: Columbia TriStar
   2. New Genre: Comedy
   3. New Format: Fullscreen
4. Delete the DVD titled “Major League 3:Back to the Minors” from the database. This DVD already exists in the database.
5. Display the number of characters for the longest and shortest title in the database. Name columns longest_title and shortest_title respectively. Use aggregate functions.
   
6. Display all genres and number of DVDs belonging to each genre as dvd_count column. Show genre ID, genre name, and DVD count. Use an aggregate function.
   

Submission

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

Overview

This lab consists of two parts: importing DVD database (needed for Assignment 7) and writing SELECT SQL statements against the song database. Submit the .sql file on BlackBoard when you finish.

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 – Song Database SELECT SQL Statements

Open up MySQL Workbench and create one .sql file. In this file, you will write four SQL statements. 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.

1. Display albums that have ‘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 (use alias: track_name), composer, media type name (use 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 (use alias: track_name), composer, milliseconds, and genre name (use alias: genre_name) columns.
   1. Use genre_id column for comparison instead of genre name.
      Sample results:
      

Submission

When complete, you must upload the completed .sql file on Blackboard. Go to Assignments/Labs -> Lab 8: SELECT Statements and upload the file.

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-fall-2021-12pm/tree/main/lect14/
• 4pm: https://github.com/nayeonk/itp303-fall-2021-4pm/tree/main/lect14

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.

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.

• https://github.com/nayeonk/itp303-fall-2021-12pm/tree/main/lect13/
• https://github.com/nayeonk/itp303-fall-2021-4pm/tree/main/lect13/

To-do for today’s class

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

Slides

No slides

Helpful Readings

• Using the Web Storage API (MDN)

Completed Files

• 12pm: https://github.com/nayeonk/itp303-fall-2021-12pm/tree/main/lect12-storage/
• 4pm: https://github.com/nayeonk/itp303-fall-2021-4pm/tree/main/lect12-storage/

Midterm: Client-Side Information

When & Where: Online, through Gradescope.com can start the exam between Thu, 10/7 12:00 pm PST to Fri, 10/8 6:00 pm PST.

Length & Format: 80 minutes, online exam.

Topics: Everything taught from Lecture 1 to Lecture 12 (covered on 9/30)

Types of Questions: 

• Multiple choice – select one answer
• Multiple choice – select all that apply
• True/False
• Fill in the blank
• Short Code

How to take the exam:

• Log in to gradescope.com. You will need to create an account if you haven’t already with your USC email address.
• Once you have logged in, you should see two “Online Assignments” for ITP 303.
• Take the “Practice Gradescope Exam” first. It is a very short and simple practice exam just so you can get familiar with taking an exam on Gradescope.
• Then take the “Midterm: Client-side Exam” — this is the real exam.
• For the short code questions, write your code on Sublime Text or another text editor first. Then copy-paste the answers to gradescope. This will help maintain the format of the code (e.g. indentation) and also you can run your code before submitting it.
• Have questions during the exam? Because you are allowed to take the exam at any time, we will be unable to answer questions you have during the exam like you would in a physical setting. Make your best-educated guess and for really uncertain questions make a note about it separately and email it to the instructor after the exam.

Important Notes:

• This is an open-book exam. You may use notes, slides, and online resources. The only thing you CANNOT do is collaborate with any human beings on this exam. Do not discuss with anyone about the exam. Your exam must be completed on your own, by yourself.
  • Any suspicion of collaboration will be reported to SJACS.
• I highly recommend you create one single reference sheet instead of scouring your notes/slides/etc during the exam. This is a timed test so you want to limit time wasted on looking stuff up.
• No questions about any specific third-party API will be asked (e.g. What’s the endpoint used to get the latest playing movie from the Movie DB API?). You do not need to know a specific third-party API’s methods, endpoints, etc.
• You’ll be notified via Piazza when a study guide and practice exam is available.

Tips:

• Anything that appeared on the lecture or the slides is fair game. The best way to study for this exam is to review the code from lectures and your labs and assignments and review what each line of the code does. Rewriting some of the code is very helpful.
• Review the links under Helpful Reading under each lecture notes to get a deeper understanding of topic(s) taught in that lecture. You do not need to know everything from the links included in Resources, but the resources will help you understand the core material better.

Overview

This lab is intended to help start your Assignment 6. Refer to the assignment requirements while you are working on this lab.

Sample

Requirements

1. Code set-up
   1. Create the following new files:
      1.  todolist.html file,
      2. style.css file, and
      3. main.js file.
   2. Include the jQuery library in your .html file.
   3. Write all your CSS in the .css file.
      1. Link your .css file into the .html file.
   4. Write all your JavaScript in the .js file.
      1. You can add your .js file in your .html file by using <script src="name_of_file.js"></script>
      2. Use jQuery for all your DOM manipulation and event listening. You will write very little pure JS.
      3. Note you can still use JavaScript along with jQuery. For example:

         $("button").on("click", function() {
             let isOn = true; // JS way of creating variables
             if( isOn == true) { // JS way of if/else statements
                 $("input").val("The app is on");
             }
         });

2. Simple HTML structure
   1. Before working on adding/deleting items or styling the app, create a basic HTML structure that will help you start the assignment.
   2. Create an area at the top of the page with some text about today’s weather. A placeholder is fine for now.
   3. Below the weather information, create a container that will hold all your to do list items. Make this container somewhere between 400 and 600px wide.
   4. At the top of the to do list, create a heading that displays “To Do List.”
   5. Below the heading, add an input area that allows users to type in a to do item here.
   6. Below the input, use the <ul> tag to create a list of at least three to do items.
   7. Within the list item, add a placeholder for the “square” icon. This can simply be a character for now (In the sample, it is the X). Later, you can replace this character with the icon from Font Awesome library.
3. Simple CSS
   1. In your newly created .css file, add some basic CSS to help you get started.
   2. Add a border around the weather and to do list.
   3. Set a width to the weather and to do list.
   4. Center the weather and to do list to the browser.
   5. That’s sufficient for the lab. Feel free to add more CSS if you’d like.
4. Weather API information is displayed at the top of the page.
   1. Get Los Angeles’s weather information from the Weatherbit.io’s API. Sign up for a free account to obtain an API key.
   2. Read their documentation to find out how to get Los Angeles’ current temperature (in Fahrenheit), a short weather description and the apparent temperature (what it “feels like”).
      1. Start by looking for the endpoint of the API. What’s the URL you need to hit to communicate with this service?
   3. Use jQuery’s ajax method get the information from Weatherbit.

Submission/Upload to the server

Please follow the submission requirements below carefully. You will be deducted points for not following submission requirements to the teeth.

1. Open FileZilla and connect to the itpwebdev server as you have done in Lab 1 (click File -> Site Manager). If you forgot how to connect, follow this guide on how to connect and upload files to the itpwebdev web server.
2. After you have successfully connected to the itpwebdev server, created a folder (aka directory) named lab6 inside the public_html folder. Then upload this assignment HTML file and images to the lab6 folder. Refer to the guide if you’re not sure how to upload the assignment file.
3. In your browser, go to http://303.itpwebdev.com/~yourusername/lab6/todolist.html 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.
6. (Optional) If you are having trouble uploading to the server and cannot upload on time, add all files for this assignment in a folder, compress it as a .zip file and upload it to Blackboard. On Blackboard, go to Assignments -> Lab 6: To Do List Starter. You will get some points deducted for not uploading correctly but at least you will not get a zero.

To-do for today’s class

• Save this Dropbox folder in the itp303 folder that you are saving all the lecture material in:
  https://www.dropbox.com/sh/6j1nqdyhlptbdel/AACu6dT4RQy_9Wq-HlioFFtqa?dl=0
  Click Download -> Direct Download on top left to get a .zip of this folder.
• Refer to the iTunes API documentation: https://affiliate.itunes.apple.com/resources/documentation/itunes-store-web-service-search-api/

Helpful Readings

• • Introduction to web APIs (MDN)
  • Third-party APIs (MDN)

• Understanding and Using REST APIs (Smashing) – https://www.smashingmagazine.com/2018/01/understanding-using-rest-api/
• AJAX – Getting Started (MDN) – https://developer.mozilla.org/en-US/docs/Web/Guide/AJAX/Getting_Started
• Using XMLHttpRequest (MDN) – https://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest/Using_XMLHttpRequest

Slides

Link to download PDF version.

Completed Files

• 12pm: https://github.com/nayeonk/itp303-fall-2021-12pm/tree/main/lect10-api/
• 4pm:  https://github.com/nayeonk/itp303-fall-2021-4pm/tree/main/lect10-api/

To-do for today’s class

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

Helpful Readings

• Reference to createElement() (MDN)
• JS Object Basics (MDN)
• JS Working with Objects (MDN)

Slides

Link to download PDF version.

Completed Files

• 12pm: https://github.com/nayeonk/itp303-fall-2021-12pm/tree/main/lect09-dom-objects/
• 4pm: https://github.com/nayeonk/itp303-fall-2021-4pm/tree/main/lect09-dom-objects/