Transcript PowerPoint

LSP 121
Week 2
Normalization and Queries
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 1:M relationship) (the
Address/Phone book had a similar
problem)
• Create a separate table for just the cars
• How do you “link: the two tables together?
Normalization Example
Car Club (originally)
Member ID (primary key)
Member Name
Member Address
Member City
Member State
Member Zip
Member Phone
Dues Paid?
National Member?
these fields repeat 1 to n times
Model of Car
Make of Car
Year of Car
Member Table
Member ID (primary key)
Name
Address
Relationship
City
State
Zip
Phone
Dues Paid?
National Member?
Car Table
Model of Car
Make of Car
Year of Car
Member ID (not
a primary key here!)
Primary Key in Member Table?
Foreign Key in Car Table?
1:M
Another Example
Student Records
Student ID (primary key)
Name
Address
City
State
Zip
Phone
these fields repeat 1 to n times
Class Name
Grade
Number Credits
Major
Minor
Degree Sought
Before
Student Info
Grades
Student ID
Name
Address
City
State
Zip
Phone
Major
Minor
Degree Sought
Class Name
Grade
Number Credits
Student ID
After
Let’s Try This One In Access
Customer ID
Customer Last Name
Customer Phone
Customer Address
Customer City
Customer State
Customer Zip
following fields repeat 1 to n times:
Sales Transaction Date
Sales Amount
Let’s first create the two tables using paper and pencil, then
when we know they are correct, let’s enter the tables into Access.
Relationships
• If:
– you create two tables, and
– the first table has a primary key, and
– you carry that primary key over to the second
table as a foreign key, and
– the primary key and the foreign key are
spelled the same and have the same type
• Then Access will automatically create a
relationship between the two tables
Relationships
• You can also create a relationship
between the two tables by hand
• Go to the Tools drop-down menu and click
on Relationships
• Add the two tables to the view, click on
one of the Student IDs and drag it over to
the other Student ID and un-click
• Check Enforce Referential Integrity (you
don’t want children without parents)
Why Relational?
• Eliminates redundancy
• Makes adding data easier
• Allows for more secure access to only
parts of the data
Simple Queries
• To create an Access query, don’t use the
query wizard. Instead, create query in
Design view
• Let’s see how Access does it
• Copy the Pets database from the Basic
Information page to your desktop (or My
Documents)
• Then open the Pets database
Queries
• You can look for something after a certain
date IF the data was stored as date/time
and you say >1/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#
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 <3
Queries
• Logical AND - You can also use an AND in
one field. For example, in the Size field
you can enter >=3 AND <=9
• A slightly easier way of doing this is using
the BETWEEN operator: Between 3 and 9
• Possible operators include =, <>, <, >, <=,
>=
• Let’s stop here for now and do Activity 2
Parameter Query
• What if you a looking for a person’s name
in a database, but you don’t 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:
[Enter … your prompt here]
– Open the Pets database for this and the
following examples
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
Example
• Say you have a database for a vet that
treats dogs. Each dog treated has an
entry including ID, weight, and height
• If you want to find the average weight and
height of all pets: (you may have to click on View / Totals)
Field: Pet ID
Weight
Height
Total: Count
Avg
Avg
Show:
X
X
X
Example
• What if you want to find the average height
and weight for all dogs?
Field:
Weight
Height
Type of Animal
Total:
Avg
Avg
Group By
Show:
X
Criteria:
X
X
“Dog”
Example
• What if you want to find the minimum and
maximum weight for all dogs?
Field: Weight
Weight
Type of Animal
Total:
Min
Max
Group By
Show:
X
Criteria:
X
X
“Dog”
More Examples
• You can also perform totals on groups of
records.
• For example, suppose you want to count
how many different types of pets the vet
has on record
Field: Type of Animal
Pet ID
Total:
Group By
Count
Show
X
X
Further Examples?
• Let’s play with the Pets database some
more