To Lecture Notes

ISP 121 -- 3/31/08

Review Questions

1. Answer these questions about Windows Explorer:
   1. Why would you want to use it?
      Ans: To find, delete, rename, use or otherwise modify files or folders.

   2. How do you invoke it?
      Ans: Start >> Programs >> Accessories >> Windows Explorer.

   3. Why is it important to set it to display file extensions?
      So that you know exactly how the file extension is spelled, without trying to guess it from the file icon.

2. How do you invoke MS Access?
   Ans: Start >> Programs >> Microsoft Office >> Microsoft Access 2003.

3. List four ways in which databases are superior to spreadsheets.
   Safer (data is written to the harddrive more often, better facilities for insuring data integrity), Concurrency (more than one user can use a database at a time), Scalability (databases are faster for large amounts of data), Efficiency (easier to perform most queries).

4. List the steps for creating a new table in MS Access.
   Ans: To create a new database: Select main menu New, Select Blank database... on the right, enter database name in file dialog.
   To create a new table: Double click on Create table in Design view, Enter field names and corresponding data types, save changes to table.

5. Define these terms for an Access database: Table, Row, Column, Field.
   Ans: A table is a rectangular grid of data arranged into rows and columns; a row contains all of the data for a particular observation, for example, a student, patient, employee, transaction, ticket; a column contains data values of a certain type; a field is the intersection of a row and a column. A field contains a single piece of data for a row.

Introduction to MS Access

• Why Databases Instead of Spreadsheets?
  Safety   Databases write data to disk often; spreadsheets do calculations in memory. Losing data due to a power failure is less likely with a database.

  Scaleability   Databases are designed to work with large amounts of data; spreadsheets bog down when many columns (more than 100) or rows (more than 1000) are used.

  Efficiency   Databases make it easy to create queries; spreadsheets are hard to use for complex calculations on large amounts of data.

  Concurrency   Databases allow more than one user to use the database at the same time; spreadsheets only allow one user at a time.

  Security   Databases provide better facilities to guard against unauthorized access.

  Data Validation   Databases provide better facilities for checking data to insure consistancy and correctness.

• When is a spreadsheet better than a database?
  Faster for quick and dirty one-shot analyses.

  Better for simulations.

  More efficient for copying and duplication data with repetitive patterns.

  Very good for what-if analysis involving calculations and charts.

   

  Searching

  • Searching a table means find the first occurrence of a field value in a table.

  • In the PersonData table of the Students.mdb database, search for the record for the student with name Bill.

   

  Filtering and Sorting

  • Filtering a table means create a new table consisting of part of an existing table.

  • Filtering can be accomplished by Form, by Selection, by Excluding Selection and by Advanced Filtering.

  • Sorting a table means sort the rows of the table in order according to one or more columns.

  • Table rows may be sorted in ascending or descending order.

  • Sorting is done in numerical order for columns of type Number; it is done in alphabetic order for columns of type Text.

  • Use the database Students.mdb to create the following views by using advanced filtering:
    • All third year students.

      Field:	Year
      Sort:
      Criteria:	3
      or:

    • All fulltime students.

      Field:	FullTime
      Sort:
      Criteria:	Yes
      or:

    • All fulltime third year students.

      Field:	Year	FullTime
      Sort:
      Criteria:	3	Yes
      or:

    When using advanced filtering, show the components of your filter.

  • Complete Activity A1.

   

  Primary Keys

  • A primary key is a field that uniquely identifies a row as in this PersonData table:

    PersonData
    Name	Gender	Age
    Judy	F	27
    Michael	M	29
    Alice	F	32

    Name is the primary key.

  • Sometimes, a primary key can be a combination of two or more fields as in this table.

    PersonData
    LastName	FirstName	Gender	Age
    Chen	Judy	F	27
    Cooper	Alice	M	59
    Smith	Alice	F	32
    Suarez	Michael	M	29

    LastName and FirstName together form the primary key.

  • For each of these tables, identify a possible primary key:

    Table Name	Primary Key
    AirlineTickets	RecordLocatorNumber
    Passports	PassportNumber
    TelevisionShowListings	Date, Time, Channel