To Lecture Notes

LSP 121 -- 10/21/10

Review Questions

1. True or False: the primary key must be the first column in the table.

   Ans: False. The primary key can be any column or combination of columns in the table.

2. In this PersonData table could Age be used as the primary key?

   PersonData
   FirstName	ID	Gender	Age
   Judy	5555	F	27
   Shawn	2222	M	31
   Alice	8888	F	36

   Ans: Access would not show an error if Age were used as the primary key because all the Age fields are different. However, Age would not be a good to use for a primary key because the ages of two persons in the table could easily be the same if more rows were added.

3. What is the difference between importing into and exporting from an Access database?

   Ans: Importing into an Access database means taking the data in a .txt or .xls file and converting it to an Access table. Exporting means taking an Access table and converting it to some other format (like .txt or .xls).

4. Using the table SwimDriveAge in the database ages.accdb, answer the following questions by creating filters:
   1. Who are the persons that can drive, but they can't swim.

   2. Who are the persons that learned to swim after the age of 16.

   3. Who are the persons that learned to swim after they learned to drive.

   Ans: Use these advanced filters:

   Problem a:
   

   Field:	CanDrive	CanSwim
   Sort:
   Criteria:	Yes	No

   Problem b:
   

   Field:	CanSwim	AgeLearnedToSwim
   Sort:
   Criteria:	Yes	>16

   Problem c:
   

   Field:	CanDrive	CanSwim	AgeLearnedToSwim
   Sort:
   Criteria:	Yes	Yes	>[AgeLearnedToDrive]

Database Normalization

• Go over Section J in the Access Tutorial.

• Look at the Three Database Rules.

• Normalizing a database means modifying it so that it conforms to the Three Database Rules. See below for more details.

• In a normalized database, a field cannot contain repeated records. Each field must contain a single value.

• A table cannot contain any data anomalies. A data anomaly occurs when a column depends completely on another column that is not the primary key.

• Example 1:   Find the repeating groups and data anomalies in this 1985 Bears Roster table.

• Example 2:   Find the data anomalies in this Chicago TV reporters info table.

• Example 3:   Find the data anomalies in this meanings of names in a kindergarten class table.

• A foreign key is a column that references a primary key in a different table.

• To eliminate repeated records or data anomalies, use data normalization:
  Data normalization means splitting a table into two or more tables, linked with a foreign key.

Examples for Homework 4

• Normalize the following tables:
  1. Persons
     
     Name
     Gender
     PhoneNumbers (repeats 1 to n times)
     PhoneType
     Number

     Ans:
     

     Persons     Name PK     Gender

     PhoneNumbers     PhoneType     Number PK     Name FK

  2. AirlineReservations
     
     FlightNumber
     Origin
     Destination
     DayOfWeek
     Departure
     Arrival
     Passengers (Repeats 0 to n times)
     
     Seat
     Name
     PhoneNumber
     Ans:
     

     AirlineReservations     FlightNumber PK     Origin     Destination     DayOfWeek     Departure     Arrival

     Passengers     Seat PK     Name     Phone     FlightNumber FK

• Look at Homework 4.

Relationships Between Tables

• An Access relationship specifies how the rows of two or more tables can be used to form a combined table.

• This combined table is called the join of the tables.

• A relationship can be one-to-many or many-to-many.

• Example: A One-to-many Relationship

  Consider the Patients and Doctors tables in Section J of the Access Tutorial. To form the join of these tables, form rows consisting of all fields from both tables, subject to the relationship that DoctorId from the Doctors table is equal to the DoctorID from the Patients table. This forms a relationship that relates one doctor to many patients (one-to-many relationship). The result is the third table in Section J, the Join of Doctors and Patients.

• Here is the patients.accdb database.

• See Section J of the Access Tutorial for details of how to create and edit relationships between tables.

• Example:   Create a relationship between the Pet and CustomerInfo tables in the pets.accdb table. This relationship should connect each pet to its owner.

• Look at the StudentsAdvisors, StudentsCourses, and Classes databases in the Access Databases on the DataSets Page.

Access Queries

• A query is like a filter with extra capabilities.
  1. Queries can receive names and be saved like tables.

  2. Queries can work with table joins in addition to ordinary tables.

  3. Queries can restrict which columns appear (unlike a filter where all columns remain).

• A query window contains the same information as a filter window: Field, Sort, Criteria. In addition, it contains the table from which each field comes, and a check box indicating whether to show the variable in the final query table.

• See Section L of the the Access Tutorial for details of how to create an Access query.

• Example: Look at Query1 in the patients.accdb table. It shows how to create a table join that shows, for each patient, the name of his or her doctor. Here is the query specification:

  Field:	Name	DoctorID	DoctorName
  Table:	Patients	Doctors	Doctors
  Sort:	Ascending
  Show:	Checked	Checked	Checked

Aggregate Queries

• Aggregate queries allow us to find these statistical summaries of columns: Ave, Count, First, Max, Min, Sum, StDev, Var(iance).

• To create an aggregate query, choose Summary, instead of Detail, in the Simple Query Wizard.

• Sometimes you may need to use this trick, described in Section L under the subsection Queries with Summaries or Groupings. This trick consists of changing to Query Design view, then changing the query type to Crosstab Query and back to Select Query.

• Example: Forming Aggregate Queries
  Use aggregate queries to answer the following questions from the pets.accdb.
  1. What is the average weight of all pets?

     Ans:

     Field:	Weight
     Table:	Pet
     Total:	Ave
     Sort:
     Show:	Checked
     Criteria:
     or:

  2. What is the average weight of pets by Type of Animal?

     Field:	Weight	Weight
     Table:	Pet	Type of Animal
     Total:	Ave	Group By
     Sort:
     Show:	Checked	Checked
     Criteria:
     or:

  3. What is the average weight of all cats?

     Field:	Weight	Weight
     Table:	Pet	Type of Animal
     Total:	Ave	Group By
     Sort:
     Show:	Checked	Checked
     Criteria:
     or:

  4. What is the minimum and maximum weight of all dogs.

     Field:	Weight	Weight
     Table:	Pet	Type of Animal
     Total:	Ave	Group By
     Sort:
     Show:	Checked	Checked
     Criteria:
     or:

  5. Count the number of animals of each type.

     Field:	Weight	Weight
     Table:	Pet	Type of Animal
     Total:	Ave	Group By
     Sort:
     Show:	Checked	Checked
     Criteria:
     or:

Activity 10

• Work on Activity 10.

• Responses to questions typically require three parts:
  1. The Advanced Query/Sort that you used (enter as a Word table).

  2. The table produced by the Advanced Query/Sort (copy from Access and paste into your table).

  3. The actual answer to the question (type into your Word document).

• Remember to reset the Query/Sort in Access after each question.