D. Nixon: CS 12 > Week 11

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

Computer Science 12


Week 11




Friday, November 18, 2011



Assignment 11 (due next week)

  1. Access assignment:

    2. In the previous Access exercise, we created a single-table database. Let's now create a multi-table database similar to the one described on the page about Relationships between Tables in Allen Browne's Access tutorial. The database will have three tables: "Students," "Subjects," and "Grades." Create them as follows:

    As before, begin by opening Access and selecting "Blank database." 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." Note that the table automatically has an "ID" field whose data type is "AutoNumber" (meaning it automatically assigns unique numbers). Leave this field alone, allowing it to automatically assign student ID's. Add three new fields for "last name," "first name," and "phone," all of data type "Text." Then save the design (by right-clicking the "Students" tab and clicking "Save") and then right-click the "Students" tab and select "Datasheet view," so you can enter data. Then enter data for five students. Then save the table (by right-clicking the "Students" tab and clicking "Save") and close it (by right-clicking the "Students" tab and clicking "Close")

    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, make use of the automatically generated ID numbers, and add two new fields, one for "Subject," and one for "notes," both of data type "Text." Right-click on the "ID" field and select "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.) When finished, save this table and close it.

    Then create the "Grades" table, which will have fields for "Student ID," "Subject ID," and "Grade," where "Student ID" and "Subject ID" will each be foreign keys referring to the other two tables. The "Grades" table will not have an auto-generated ID field of its own. We will get rid of this field as follows: In the design view (after you've saved the new table as "Grades"), 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."

    Now create the "Student ID" field. Give it the name "Student ID" 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 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 select "ID" as the field that uniquely identifies the row; then check "Enable data integrity"; then click "Finish"; then click "Yes" to save the table.

    Similarly, create a field with the name "Subject ID" 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 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 select "ID" as the field that uniquely identifies the row; then check "Enable data integrity"; then click "Finish"; then click "Yes" to save the table.

    Then give the "Grades" table a third field, of type "Text."

    Note that each record of the "Grades" table will be uniquely identified not by any one field but by a combination of the two fields "Student ID" and "Subject ID." Therefore, let's make the combination of these two fields the primary key as follows: 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."

    Then save the "Grades" table and move to the data sheet view. As you enter grades, notice how the "Student ID" and "Subject ID" columns allow you to select only those students and subjects that are already in the other tables. Also, see what happens if you try to enter two separate grades for the same combination of student and subject. (It should not allow you to do this.) After you've entered a grade for each possible combination of student and subject, save the table.

    Then save the entire database, with a filename like the following:

    last-first-elementary.accdb

    replacing "last" and "first" with your own actual last and first name.


To all information for Week 11  |  To all homework assignments



Tutorials on miscellaneous topics covered this week

Brief introduction to the Windows Command Prompt:

Logging on to a Unix system via WinSCP:

To upload a file via WinSCP, once you are logged in: (1) On the left side, navigate to the directory where your files are, on your local machine. (2) On the right side, go into the  public_html  directory, which is where you must put your files in order for them to be visible on your website. (3) On the left side, select the files you want to copy. After highlighting them, select "Copy" from the "Files" menu. (4) Click on the right side, then select "Paste" from the "Files" menu and respond appropriately to subsequent prompts. (5) Once you've uploaded all your files, view them at a URL like the following, to make sure you've uploaded them correctly:

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

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

Brief introduction to Unix:

In WinSCP, you can access the Unix command line (shell) by selecting "Open Terminal" from the "Commands" menu, once you are logged in to  cs12.cs.qc.cuny.edu .  (You will be prompted to log in again.) You do NOT need to do this in order to uploaded your files, which can be done easily via the GUI interface. Nevertheless, once you have successfully uploaded (and viewed) your files, you should take the opportunity to learn a little about Unix.

File permissions and  chmod  on a Unix/Linux machine:


To all information for Week 11  |  To all tutorials about miscellaneous topics



Study guide for next week's quiz

The December 2 quiz will be an on-paper quiz.

There will be an Excel spreadsheet problem, which may involve any of the topics covered so far. Be especially prepared for problems involving VLookup, the logical functions (IF, AND, OR, nested IF), and functions using criteria (CountIF, SumIF, and AverageIF). The questions may include fill-in-the-blanks and/or multiple choice questions and/or a problem in which you are given a picture of a spreadsheet and asked to write an appropriate formula to go into some specified cell.

There will be some fill-in-the-blanks questions on Linux/Unix file permissions. You will expected to know the meaning of the symbols  d , r , w , x , and  -  in the output when you type  ls -l  or  ls -la  on a Unix/Linux system. You will also be expected to know how to use the  chmod  command.


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

D. Nixon: CS 12 > Week 11