D. Nixon: CS 12 > Week 10

Home (CS 12) | Assignments | Tutorials | Examples | Quizzes & Exam | Other Info | Chat

Computer Science 12


Week 10




Sunday, April 19, 2020



Assignment 10 (due next class, unless otherwise specified)

  1. Excel assignment (due next week, Sunday, April 26)
  2. Uploading your HTML pages to a remote Unix machine (due next week, Sunday, April 26)
  3. Blog assignment (due next week, April 26)
  4. PowerPoint presentation, draft 2 (due next week, Sunday, April 26)
  5. Reminder: Excel extra-credit assignments (due Sunday, April 26)
  6. Reminder: Extra-credit project proposals
  1. Excel assignment (due next week)

    Practice using VLookup as in this week's Excel tutorials. Practice first by duplicating one or more of the examples in the tutorials.

    Then modify your old "invoice" file (one of the versions with both taxable and non-taxable items, preferably Assignment 7, but otherwise Assignment 4) so that it contains a separate "Inventory" table (located further down in the spreadsheet, below the invoice itself, with at least four or five blank rows between it and the invoice), listing products together with their unit prices, quantity in stock, and the column indicating whether a given item is taxable. See the second page of A-Invoice-blank.pdf in the example files.) The new inventory table should also contain at least a few more products besides the ones in the invoice. (The idea here is that the inventory table lists all the products in the entire store, whereas the invoice lists only the products bought by one customer on one day. It is highly unlikely that an individual customer would buy every product in the store even if stocking up for an expected quarantine.)

    Then, in the original invoice table itself:

    1. Replace all product names (in the invoice) by formulas consisting of just an equals sign followed by a cell reference to a cell in the inventory table containing the same product name. (This will ensure consistent spelling between inventory and invoice.) In the invoice spreadsheet portrayed on the first page of the example file, one appropriate such formula might be:  =B30
    2. Replace the hard-coded numbers in the "unit price" column of the original invoice table with VLookUp formulas (in the invoice table) which look up the unit price in the separate inventory table.
    3. Likewise, replace the hard-coded T's and blanks in the "taxable" column of the original invoice table with VLookUp formulas which look up whether an item is taxable.

    Make sure the VLookUp formulas are correctly draggable. (Important note: The single-cell product name formulas CANNOT be draggable because there are more products in the inventory than in the invoice, and there is no reason to expect customers to limit themselves to buying sets of products that happen all to be listed next to each other in the inventory. But the VLookUp formulas can and should be draggable.) In order to be correctly draggable, the VLookup parameter "table array" must encompass the data portion of the inventory table, but the "lookup value" must be a cell in the same row of the invoice table where the formula itself is.

    Then modify your old "roster" file (from Assignment 4) to contain an additional table which converts percentage scores to letter grades. (See A-Roster-grade-table.pdf in the example files.) Add a column to the main roster table for the letter grade, and use the separate table, with VLookup, to compute each student's letter grade, based on the final numeric score. Make sure your formula is correctly draggable.

    When you are finished, submit both your "invoice" file and your "roster" file as attachments to a single email message with the following subject line:

    HW 10 Excel LastName FirstName

    (replacing "LastName" and "FirstName" with your own actual last and first names). The files must have filenames like the following:

    • last-first-invoice-10.xlsx
    • last-first-roster-10.xlsx

    (replacing "last" and "first" with your own last and first name).

  2. Uploading your HTML pages to a remote Unix machine (due next week)

    Upload your HTML pages as instructed in this week's tutorials on uploading your website to a remote Unix machine.

    Make sure all your files are visible, and make sure all your links work.

    Copy and paste the URL of your website into an email message with a subject line like the following:

    HW 10 website LastName FirstName

    replacing "LastName FirstName" with your own actual last and first name. (Do not submit a file.)

  3. Wordpress blog assignment (due next week)

    Log in to the account you created two weeks ago on Wordpress.com, or create a new account there if you did not do so earlier. (See the blog portion of homework assignment 8.) Also you'll be able to do the following only if, last week, you succeeded in installing, on your blog, a new theme that supports widgets.

    You will now be asked to add two widgets to your blog, "Categories" and "Tag cloud," both of which are ways of organizing your blog posts by topic. The "Categories" widget is like a table of contents, containing links to groups of posts grouped by their main topic, whereas the "Tag cloud" widget is more like an index, containing links to groups of posts grouped by topics they touch on, not necessarily their main topics. For detailed instructions, see Tags and categories, as widgets in Notes on Wordpress.

    Then copy and paste the URL of your blog (http://username.wordpress.com/ replacing "username" with your actual Wordpress username) into an email message. (Do not submit a file.) Your email must have a subject line with the following format:

    HW 10 Blog, LastName FirstName

    replacing "LastName" and "FirstName" with your own actual last and first names.

  4. Paper on problem-solving, draft 3 (due next week)

    See instructions here.

  5. PowerPoint presentation, draft 2 (due next week):

    See the description of the new section of the PowerPoint presentation you were told to add in the Week 8 assignment.

    Your PowerPoint slideshow, with the new section added, must have a filename with the following format:

    • last-first-ppt-10-draft-2.ppt

    where "last" and "first" should be replaced by your own last and first name.

    Submit the file attached to an email message with a subject line like the following:

    PPT slideshow Draft 2, LastName FirstName

    where "LastName" and "FirstName" should be replaced by your own last name and and first name, respectively.

  6. Reminder: Excel extra-credit assignments (due tnext week):

    See instructions here.

  7. Reminder: Extra-credit project proposals):

    See instructions here.


To all information for Week 10  |  To all homework assignments



Example files for this week

Some example files will be uploaded here.

More may appear here before and during our online class session today (April 12).


To all information for Week 10  |  To all example files



Tutorials on Excel topics covered this week

On Excel's VLOOKUP function:


To all information for Week 10  |  To all tutorials about Excel



Tutorials on non-Web Internet file transfer and Unix file permissions

  1. Logging in to the CS 12 Unix (Linux) machine
  2. Uploading your HTML files to a remote Unix machine
  3. A brief intro to file permissions
  4. Adjusting file permissions
  5. Checking your HTML filenames and links
  1. Logging in to the CS 12 Unix (Linux) machine:

    Via WinSCP or whatever similar program you are able to use, log in to the Unix machine for this class. Use the following as the "Host name":

        cs12.cs.qc.cuny.edu
    

    Use the same username and password that we use on the machines in the lab. Make sure you type the letters of your username in all lower case. Unix is case-sensitive.

    If you have difficulty figuring out how to log in, refer to the tutorials listed here.

  2. Uploading your HTML files to a remote Unix machine:

    Once you have successfully logged in to the remote Unix machine, the left half of the WinSCP or FileZilla window will show files and folders (a.k.a. directories) on your local machine, whereas the right half of the window will show files and folders/directories on the remote machine.

    Prepare to upload your HTML files to the remote Unix machine, as follows: Before you upload them, copy and paste them out of your ZIP file into a new ordinary (NOT compressed) folder with the exact name of:

    	public_html
    

    Then, to upload your HTML files via WinSCP, once you are logged in: (1) On the left side of your WinSCP window, navigate to the folder which contains your  public_html  folder, on your local machine. (2) On the right side, above the file listing, click the house icon to make sure that the right side of your WinSCP window is looking at your home directory on the Unix machine. (3) On the left side of your WinSCP window, click on  public_html  to highlight it. (4) In WinSCP, select "Upload" from the "Files" menu, then click the "Copy" or "Upload" button on the dialog box that pops up. (For FileZilla, see tutorials here.)

    You should now see a copy of your  public_html  folder on the right side of your WinSCP window. If so, you have successfully uploaded it.

  3. A brief intro to file permissions:

    Once you've uploaded your HTML files and set the permissions, try to view your new website at a URL like the following:

    http://cs12.cs.qc.cuny.edu/~yourusername/

    replacing " yourusername " with your actual username. Be sure to include the tilde (" ~ ") just before your username.

    Even if you've uploaded your files correctly, you WON'T be able to see your new website yet. If you follow the above instructions correctly, you will see "Permission denied". (If you DIDN'T follow the above instructions correctly, you will see "Not Found".)

    To make your HTML files visible as a website, you will need to adjust the file permissions. Before we tell you how to do that, here is a very brief explanation of what file permissions are.

    All users who could potentially access your files are divided into 3 categories: (1) the owner (you), (2) the group (all users whom the system adminstrator has decided to group together with you for whatever reason), and (3) others (all other logged-in users with accounts on the machine). A person viewing your files via a web browser is NOT in ANY of these categories, since you don't need to be logged in to CS 12 Unix machine itself to do that. However, on a Unix machine, programs such as the Apache Web Server (which enables your website to be seen) are treated as if they were users in either the "group" or "other" category. Therefore, you need to ensure that both "group" and "other" can read your files.

    Each of the three types of "user" can be given three kinds of permissions: (1) read, (2) write, and (3) eexcute. In WinSCP these are abbreviated R,  W,  and X,  respectively. On the Unix machine itself they are abbreviated r,  w,  and x. 

    Permissions are given on a per-file basis. In the right hand side of your WinSCP window, double-click the  public_html  folder to see a listing of your files. Next to each filename, you will see

    • In WinSCP, in the "Rights" column, you'll see a sequence of nine characters like the following:

      rw-r--r--
      
    • In FileZilla, in the "Permissions" column, you'll see a sequence of ten characters like the following:

      -rw-r--r--
      

      In FileZilla, the first of these ten characters not really one of the "persissions" but indicates whether the listed file is a regular file or a folder. (If it's a regular file, that character is a dash. If it's a folder, that character is a 'd' -- which stands for "directory," and older word for a folder.) The remaining nine characters are the permissions.

    In both WinSCP and FileZilla, among the nine characters that constitute the rights/permissions:

    • The first three characters are the permissions for the owner.
    • The next three characters are the permissions for the group.
    • The last three characters are the permissions for other.

    Within each of these three sets of three characters:

    • The first of the three characters is either  r  or  -  depending on whether the user does (r) or does not (-) have read permission.
    • The second of the three characters is either  w  or  -  depending on whether the user does (w) or does not (-) have write permission.
    • The third of the three characters is either  x  or  -  depending on whether the user does (x) or does not (-) have execute permission.

    To read a file means to read its contents. To write to a file means to modify it. To execute a file means to run it, if the file happens to be an executable program or script. (It is meaningless to execute an HTML file, but you should note that, on HTML files, it is best to deny execute permission to everyone, including even the owner, just to make sure the file doesn't do anything weird.) On an HTML file, it is standard to give both read and write permission to the owner, and to give only read permission to both group and other.

    Now double-click the double dot at the top of the right hand panel of your WinSCP or FileZilla window to go back to your home directory (the folder you were in when you originally logged in). Next to the name of the  public_html  folder, you will see something like the following in the "Rights"/"Permissions" column:

    • In WinSCP, in the "Rights" column:

      rwxr-xr-x
      
    • In FileZilla, in the "Permissions" column:

      drwxr-xr-x
      

    Permissions can be set not only for individual files, but also for folders, also known as directories. In the example above, you, the owner, have all 3 permissions (read, write, and execute) for the  public_html  folder, whereas the group and other have both read and execute permissions but not write permission.

    To read a folder means to view a listing of the files (and other folders) within the folder. To write to a folder means to add or delete a file within the folder. To execute a folder means to access any file (or other folder) within the folder. Thus, to be able to read a file on a Unix system, you not only need read permission for the file itself, you also need execute permission for the folder containing that file -- and you also need execute permisson for the folder containt that folder, and so on, all the way up to the root directory (the one big folder containing all the others).

    Now click the double dot at the top to go outside your home directory into a director which contains all the home directories of all the students in this class. You will see a list of folders, each with a student username (4 letters, followed by 4 digits), including yours. Note the following in the "Rights"/"Permissions" column:

    • In WinSCP, in the "Rights" column:

      rwxr------
      
    • In FileZilla, in the "Permissions" column:

      drwxr------
      

    Thus each student, each of whom is the owner of a directory with one's own username, has full permissions for that directory, but no one else has permission to do anything. That's why the web server can't read your files, which is why you got "Forbidden" when trying to access them via a web browser. Remember, the web server (treated as a user in either the "group" or "other" category) needs execute permission for your home directory in order to access your  public_html  directory, which the web server needs to access to get to your HTML files.

    Now go back to your home directory (the one with your username), either by double-clicking its icon or by clicking on the house icon at the top. Look again at the permissions for your  public_html  directory:

    • In WinSCP, in the "Rights" column:

      rwxr-xr-x
      
    • In FileZilla, in the "Permissions" column:

      drwxr-xr-x
      

    These permissions are actually more generous than they need to be. The web server needs execute permission but NOT read permission on directories/folders. The web server needs to be able to access all files and folders within a folder, but does NOT need a listing of the contents of a folder. The web server already knows what file to go to, based on the URL you entered in your web browser, so it doesn't need a file listing; it just needs execute permission on all folders that contain the file either directly or indirectly.

    For security reasons, it is generally best to give no more permissions than are necessary. Hence, for a website, the following permissions are most appropriate for your  public_html  folder as well as for your home directory:

    • In WinSCP, in the "Rights" column:

      rwx--x--x
      
    • In FileZilla, in the "Permissions" column:

      drwx--x--x
      
  4. Adjusting file permissions:

    To make your HTML files visible as a website, you will need to adjust the permissions. Here's how to do this in WinSCP:

    1. In the right side of the WinSCP window, right-click on the  public_html  folder and select "Properties." In the dialog box that pops up, make sure that the R, W, and X boxes are checked for "Owner," and make sure that only the X box, but NOT the R or W box, is checked for both "Group" and "Others." Then, if you had to make any changes, click "OK"; otherwise click "Cancel."

    2. In the right side of the WinSCP window, double-click on the  public_html  folder to enter. Then right-click on each HTML file, one at a time and select "Properties." In the dialog box that pops up, make sure that the R and W boxes, but NOT the X box, are checked for "Owner," and make sure that only the R box, but NOT the W or X box, is checked for both "Group" and "Others."

    3. You can go back to your home directory by double-clicking the double-dot at the top of the file listing on the right hand side. Once you are in your home directory double-click the double dot again to go outside your home directory, into the directory with a list of folders including the one with your username. Right-click on the folder with your user name, and give execute permission to the "Group" and "Others." In the dialog box that pops up when you select "Properties", make sure that the R, W, and X boxes are checked for "Owner," and make sure that only the X box, but NOT the R or W box, is checked for both "Group" and "Others."

    For FileZilla, see the tutorial Changing File Permissions using Filezilla, then do the nearest equivalent of the above.

    For CyberDuck, see the tutorials Changing File Permissions Using Cyberduck (text-based) and File & Folder Permissions using Cyberduck (video), then do the nearest equivalent of the above.

  5. Checking your HTML filenames and links:

    Once you've uploaded your HTML files and set the permissions, view your files at a URL like the following, to make sure you've done this correctly:

    http://cs12.cs.qc.cuny.edu/~yourusername/

    replacing " yourusername " with your actual username. Be sure to include the tilde (" ~ ") just before your username.

    If you still can't see your website and you are sure you typed the URL correctly, make sure that the main page has the following EXACT filename, spelled in all-lower-case letters:

    index.html
    

    Once you are able to view your website, click on the links to make sure they still work. Note that Unix is a big fussier than Windows in the following ways: (1) Unix is case-sensitive. If the filename contains one or more letters that are upper case in the filename of the file itself, but lower case in the link, or vice versa, the link won't work. (2) If your filename contains spaces (and so did your links), the links won't work on a Unix system.

    Additionally, regardless of the operating system of the web host, there's another possible issue that may cause your links not to work: If you used complete absolute pathnames, rather than just filenames, in your local links, then the links won't work anywhere except in the same folder, on your local machine, where you created the files.

    Once you've gotten all the links to work correctly, submit your homework as per the instructons on the homework assignments page.


To all information for Week 10  |  To all tutorials about computer basics, operating systems, and non-web Internet



Tutorials on Wordpress categories and tags, as widgets

Making your blog easier for readers to navigate:


To all information for Week 10  |  To all tutorials about the World Wide Web



Study guide for next week's quiz

The April 26 quiz will be an online quiz, similar to the last two quizzes, and will include the following.

  1. Excel: There will be one or more spreadsheet problems involving VLOOKUP, and there will be one or more spreadsheet problems involving one or more of the previously-covered Excel functions, possibly including the decision-making functions (IF, AND, OR, COUNTIF, SUMIF, and AVERAGEIF), the date functions (DATE, TODAY, DAYS360, YEARFRAC), and/or other functions (LARGE, SMALL, SUM, COUNT, AVERAGE, MIN, MAX). There may also be another Goal Seek problem. There may also be fill-in-the-blanks questions about various other Excel features we have studied so far.

    About half of the quiz will be devoted to Excel.

  2. Websites, WinSCP, and Unix file permissions: There will be some fill-in-the-blanks and/or multiple-choice questions about Unix file permissions as you encountered them via WinSCP or FileZilla. Make sure you understand what "read" (R), "write" (W), and "execute" (X) permissions mean for both regular files (including HTML files) and directories/folders, and make sure you are aware of the three categories of users ("owner," "group," and "others") for whom permissions are set. Also make sure you understand the rationale for the recommended permissions for HTML files and the folders/directories that contain them, either directly or indirectly. For example, make sure you understand how the web server program gets treated as a user, and what permissions it does and does not need. (See the relevant tutorial.)

  3. Programming concepts: There will be some fill-in-the-blanks and/or multiple choice questions about Scratch, similar those in the quiz two weeks ago.

  4. Databases: There will be one or more fill-in-the-blanks and/or multiple choice questions about database terminology. See last week's study guides for the list of terms covered so far.

  5. Wordpress categories and tags: There will be one or more fill-in-the-blanks or multiple choice questions about Wordpress categories and/or tags. Know that categories and tags are both ways of grouping your posts, and linking to those groups, to make it easier for readers to find posts on your blog. Know that categories are intended to be similar to a table of contents whereas tags are intended to be similar to an index.


To all information for Week 10  |  To all quiz and exam study guides

D. Nixon: CS 12 > Week 10