Computer Science 12
Sunday, April 12, 2020
- Assignment 9 (due next week)
- Tutorials on Wordpress blogs
- Example files for this week
- Study guide for next week's quiz
- Access assignment (due next week, April 19)
- HTML assignment (due next week, April 19)
- Blog assignment (due next week, April 19)
- Reminder: Paper on problem-solving, draft 3 (due Sunday, April 19)
- Reminder: Powerpoint slide show, draft 2 (due Sunday, April 26)
- Reminder: Excel extra-credit assignments (due Sunday, April 26)
- Extra-credit project proposals
Access assignment (due next week)
[For Spring 2020, this assignment is optional. It turns out that Access is not available for the Mac, and, of course, if you happen to have a Mac but not a Windows PC, you can no longer access (pun intended) the Windows PC's on campus. It is still recommended that you do this assignment if you can, in which case you will receive a small amount of extra credit for it.]
In the previous Access exercise, we created a single-table database. Let's now create a multi-table database that could be used as a class roster by an elementary school teacher. As detailed in the steps below, the database will have three tables: "Students," "Subjects," and "Grades," where the "Grades" table will have a primary key consisting of two fields, which will be foreign keys referencing the other two tables. Create the three tables as follows:
As before, begin by opening Access and selecting "Blank desktop database." Then:
A) "Students" table:
- To begin creating the "Students" table, click "Create." Then right-click the "Table1" tab at the top of the main panel, and select "Save."
- In the "Save As" dialog box that pops up, change "Table1" to "Students".
- After saving the table, right-click the "Students" tab and select "Design View." (ALWAYS go to "Design View" and define your fields before you start entering data!)
- Observe that the table automatically has an "ID" field whose data type is "AutoNumber" (meaning it will automatically assign unique numbers). Do NOT do anything to this field. Leave the ID field alone, allowing it to automatically assign student ID's Observe the little key icon next to "ID" in the "Design View," meaning that the ID field will be the primary key of the record.
- Add three new fields for "Last Name," "First Name," and "Phone," all of data type "Short Text," which is the default data type. (You should now have a total of four fields including the ID.)
- Save the design (by right-clicking the "Students" tab and clicking "Save")
- Right-click the "Students" tab and select "Datasheet view," so you can enter data.
- Enter data for five students.
- Save the table (by right-clicking the "Students" tab and clicking "Save") and then close it (by right-clicking the "Students" tab and clicking "Close")
B) "Subjects" table:
Then create the "Subjects" table. At the top, click "Create," then click "Table." Give the new table the name "Subjects" by saving it with that name. As before, define the fields in "Design View" before you start entering data in "Datashheet View." Also as before, leave the "ID" field alone, so you can make use of the automatically generated ID numbers,. In "Design View", add two new fields, one for "Subject," and one for "Notes," both of data type "Short Text." Again observe the little key icon indicating that the "ID" field is the primary key. In the "Datasheet View," add records for at least three of the subjects typically taught in elementary school, such as "reading," "math," and "science." (You may want to stretch out the width of the "Notes" column in the datasheet view; this can be done similarly to the way it's done in Excel.) When finished, save this table and close it.
C) "Grades" table:
The "Grades" table will be very different from the other two. As detailed in the steps below, it will have fields for "Student," "Subject," and "Grade," where "Student" and "Subject" will each be foreign keys referring to the other two tables. Proceed as follows:
At the top, click "Create," then click "Table." Give the new table the name "Grades" by saving it with that name.
- The "Grades" table should not have an auto-generated ID field. Get rid of this field as follows: In the Design View, right-click on the name of the "ID" field and select "Primary Key" so that is no longer the primary key. (See the key icon to the left of the name disappear.) Then right-click to the left of the field name and select "Delete Rows." (You needed to make it not be the primary key anymore before you could delete it; Access will not allow you to delete a field that is a primary key.)
While still in Design Vew, create the "Student" field. Give it the name "Student" and then, for its data type, select "Lookup Wizard;"; then select "I want the lookup field to get the values from another table or query"; then click "Next"; then select "Tables: Students"; then click "Next"; then select "ID," "Last name," and "First name"; then click "Next"; then select a sort order by (1) last name and (2) first name, both in ascending order; then click "Next"; then shrink the column widths to be as narrow as possible and still be readable; then click "Next"; then check "Enable data integrity"; then click "Finish"; then click "Yes" to save the table.
Similarly, create a field with the name "Subject" and then, for its data type, select "Lookup Wizard;"; then select "I want the lookup field to get the values from another table or query"; then click "Next"; then select "Tables: Subjects"; then click "Next"; then select "ID" and "Subject," then click "Next"; then select a sort order by "Subject" in ascending order; then click "Next"; then shrink the column widths to be as narrow as possible and still be readable; then click "Next"; then check "Enable data integrity"; then click "Finish"; then click "Yes" to save the table.
Then give the "Grades" table a third field: "Grade," of type "Short Text."
We will now define the primary key for the "Grades" table. Note that each record of the "Grades" table is uniquely identified not by any one field but by a combination of the two fields "Student" and "Subject." Therefore, let's make the combination of these two fields the primary key, as follows: While still in the Design vew, highlight the two boxes to the left of the names of these fields. (Highlight one of these boxes, then use the up or down arrow key to highlight the other one too.) Then right-click and select "Primary Key." Make sure that a key icon appears to the left of BOTH "Student ID" and "Subject ID."
Then save the "Grades" table and go to the Data Sheet view.
In the Data Sheet view, enter grades for every possible combination of "Student" and "Subject". Notice how the data cells in the "Student" and "Subject" columns contain drop-down menus, thereby allowing you to select only those students and subjects that are already in the other tables. (This is one of the ways that Access enforces referential integrity.) Also, see what happens if you try to enter two separate grades for the same combination of student and subject. (Access should not allow you to do this if you defined the fields correctly.)
After you've entered a grade for each possible combination of student and subject, save the table.
When finished with the above, save the entire database (by clicking the "File" tab, then "Save As"), with a filename like the following:
replacing "last" and "first" with your own actual last and first name. Submit it as a file attachment to an email message with a subject line like the following:
HW 9 Access LastName FirstName
replacing "LastName FirstName" with your own actual last and first name.
HTML assignment (due next week)
Create three HTML pages that would actually be appropriate to display on a personal website. The three pages must be:
- A file named
index.html, which will briefly introduce your site and contain (1) a link to our Computer Science 12 website and (2) links to both of the two other twp pages described below. (You may re-use and expand on the version of
index.htmlthat you created in the the HTML portion of homework assignment 8.)
- A file named
aboutme.html, which will briefly introduce you and contain (1) links to at least two websites pertaining to your life and/or topics you are interested in, (2) a link to your blog for this class, and (3) a link back to
index.html. (You may re-use and expand on the version of
aboutme.htmlthat you created in the the HTML portion of homework assignment 8.)
- At least one other page about any topic you like, within reason. The topic does not need to be computer-related. The topic can be any hobby of yours, for example, or something relevant to your career goals, or something relevant to another course you're taking, or any other topic you like, as long as it stays within Queens College's computer use policies. This page must contain at least two sentences of text in addition to the required links, which are: (1) links to at least two remote websites about the page's topic, and (2) a link back to your main index page.
To summarize the required links:
index.htmlmust contain a link to our CS 12 course website plus local links to both of the other two pages, both of which pages, in turn, must contain links back to
index.html, Each of the other two pages must contain both a link back to
index.htmland at least two remote links to other websites.
When you are finished, put all your HTML files into a ZIP file with the following filename:
where "last" and "first" should be replaced by your own last and first name. Submit the ZIP file, by no later than the beginning of our next class session, via an email message with a subject line like the following:
HW 9 HTML, LastName FirstName
where "LastName" and "FirstName" should be replaced by your own last name and and first name, respectively.
- A file named
Wordpress blog assignment (due next week)
You will now be asked to explore some Wordpress themes, which determine the overall appearance of your blog. You will be asked to choose a theme for your blog and also write a new blog post briefly voicing your thoughts and feelings about three specific WordPress themes (which you should mention by name). For detailed instructions, see Selecting a WordPress Theme 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 9 Blog, LastName FirstName
replacing "LastName" and "FirstName" with your own actual last and first names.
Reminder: Paper on problem-solving, draft 3 (due next week)
See instructions here.
Reminder: Powerpoint slide show, draft 2 (due two weeks from now)
See instructions here.
Excel extra-credit assignments (due two weeks from now):
See instructions here.
Extra-credit project proposals:
You may propose an extra-credit project relevant to your major and/or career goals. Possible examples include, but are not limited to, the following:
- For K-12 education majors: Write a paper comparing Scratch to the programming capabilities provided in the lessons for grades 1 to 6 on Code.org.
- For art majors: Write a paper comparing Scratch to some commonly-used animation software used by professional animators.
- For political science majors: Write a paper about the security issues involved in electronic voting.
Alternatively, you may propose an extra-credit project that deepens your knowledge of the core material of this course and also helps us improve this course for future semesters. Examples include:
- Find three YouTube video tutorials on one of the Excel topics we've covered this semester. Write a review of the three videos, comparing them in various ways.
- Do the equivalent of either Access assignment 6 or Access assignment 9, or both, using the open-source Libre Office "Base" database program. Write a tutorial explaining in detail how to do it, similar to the instructions for the Access assignment (s). (You will be thanked by name, in addition to receiving extra credit, if your tutorial or something closely based on it is used in next semester's version of this course.)
Details of the above, or any similar proposals, must be negotiated via email (and/or in person after class) before you write the paper. Do NOT submit a paper out of the blue and expect to be given extra credit for it. Send an email to the usual homework email address with a subject line like the following:
Extra credit proposal, LastName FirstName
Creating a Wordpress blog, and creating and editing posts:
- Notes on Wordpress. (The section titled Getting started on Wordpress was intended to be used with the blog assignment in Assignment 8.)
Customizing your blog's appearance:
- Notes on Wordpress. (The section titled Selecting a WordPress Theme is intended to be used with the blog assignment in Assignment 9.)
Some example files will be uploaded here.
More may appear here before and during our online class session today (April 12).
Online quiz 3 to be given on Sunday, April 19, will include the following:
- Scratch: Expect meatier (in terms of programming concepts) questions than you've been asked about Scratch thus far. You may be asked, for example, what specific block(s) can be used to accomplish some specific task that you've done in the Scratch assignments. (If you're not up-to-date on the Scratch assignments, please try to catch up on them now; there will be no more Scratch assignments for the remainder of the semester.)
- HTML: You will be expected to write out the HTML for a trivially short page which will be described on the quiz. You will be expected to know all the HTML we have covered in class so far. You will be expected to know how to create HTML links using the
atag with the
hrefattribute, and you will be expected to know how to use the
- Excel: There will be at least two problems requiring you to write an Excel formula, and possibly also some multiple-choice and/or fill in the blanks questions regarding Excel as well. You will be responsible for all concepts learned thus far. The Excel part of the quiz will include at least one problem in which you will need to write Excel formulas involving the functions that were covered in class last week, i.e. the SMALL and LARGE functions. It may also involve other functions we've covered in the past, including the SUM, COUNT, AVERAGE, MIN, MAX, IF (including nested IF), AND, OR, COUNTIF, SUMIF, and AVERAGEIF, and also the basic arithmetic operations (addition, subtraction, multiplication, and division), plus the date functions, including computation of the time interval (in actual days, accounting days, actual years, or accounting years) between two dates. In particular, make sure your know how to obtain the sum of the N smallest/largest numbers (and do not confuse that sum with the simpler formula for just the Nth smallest/largest number itself). Also you will likely need to use the SMALL or LARGE function w ithin a more complex formula, e.g. involving nested IF and/or the AND or OR function, or involving COUNTIF, SUMIF, or AVERAGEIF.
- Blog: Know what a Wordpress "theme" is. You may need to know this as the answer to a fill-in-the-blanks question. Also you may be asked some brief short-answer essay question about blogs.
- Databases: As on the last few quizzes, there will likely be some questions on database terminology. Any of the basic database concepts covered in class will be fair game. Know the meaning of the following terms, in the context of databases: "table," "record" (a.k.a. "tuple"), "field" (a.k.a. "attribute"), "data type", "query," "key," "primary key," "form," "report," and two new terms: "foreign key" and "referential integrity.". In addition to fill-in-the-blanks questions, there will likely be a few short-answer essay question in which you are asked to define a term or briefly explain its relevance to some hypothetical database similar to ones discussed in class.