ISP 121

1
ISP 121

• Week 2
• Normalization and Queries

2
Normalization

• The Old Car Club database presented a problem
  what if one person owns multiple cars? (One owner
  can have many cars, so this is 1M relationship)
• Create a separate table for just the cars
• How do you link the two tables together?

3
Normalization Example
Car Club (originally) Member Name Member
Address Member City Member State Member
Zip Member Phone Dues Paid? National Member? Car
Owned (repeats 1-n times) Model of Car Make of
Car Year of Car Member ID (primary key)
Member Table Name Address City State Zip Phone Du
es Paid?National Member? Member ID
Car Table Model of Car Make of Car Year of
Car Member ID (not a primary key here!)
Relationship
Primary Key in Member Table? Foreign Key in Car
Table? 1M
4
Another Example
Student Records Student ID (primary
key) Name Address City State Zip Phone Class
Taken (repeats 1-n times) Class
Name Grade Number Credits Major Minor Degree
Sought
Student Info Student ID Name Address City State Z
ip Phone Major Minor Degree Sought
Grades Class Name Grade Number Credits Student ID
After
Lets enter this one into Access and create a few
records of data.
Before
5
Why Relational?

• Eliminates redundancy
• Makes adding data easier
• Allows for more secure access to only parts of
  the data

6
Simple Queries

• To create an Access query, dont use the query
  wizard. Instead, create query in Design view
• Lets see how Access does it

7
Queries

• You can look for something after a certain date
  IF the data was stored as date/time and you say
  gt1/1/2004
• Dates should be entered with before and after
  the date, and can be in many different formats,
  ie 1/1/2004, January 1, 2004, 1-Jan-2004

8
Queries

• Logical OR - You can look for records in the
  state of Indiana or Illinois by saying IL OR
  IN
• You can also say In (IL, IN, OH)
• Logical AND - you can make multiple entries in
  the query boxes. For example, in the State field
  enter IL and then in the Size field enter lt3

9
Queries

• Logical AND - You can also use an AND in one
  field. For example, in the Size field you can
  enter gt3 AND lt9
• A slightly easier way of doing this is using the
  BETWEEN operator Between 3 and 9

10
Parameter Query

• What if you are looking for a persons name in a
  database, but you dont know the name until you
  run the query?
• Use the built in parameter query
• Form your query as usual, but in the Criteria box
  enter a prompt between square braces Enter
  your prompt here

11
Queries That Calculate

• When performing a query, you can aggregate the
  data
• You can perform a Count, Sum, Avg, Max, Min,
  StDev, Var(iance), First, and Last
• Count, First, and Last can be performed on types
  counter, number, currency, date/time, yes/no,
  text, memo, and OLE object
• The others on counter, number, currency,
  date/time, and yes/no

12
Heights and weights database

• Lets look at a database of human heights and
  weights.
• Well perform queries that aggregate data, that
  is, queries that compute such things as counts,
  averages, minimum values, and maximum values.
• Well also group records.