Transcript Advanced databases

LSP 121
Week 2
Normalization and Advanced Queries
Normalization
• Let’s create a database for a car club
• What if one person owns multiple cars? (One
owner can have many cars, so this is 1:M
relationship)
• Create a separate table for just the cars
• How do you “relate” 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 Consider the Following:
Sales Transactions at a Clothing Store
Customer ID
Customer Last Name
Customer Phone
Customer City
following fields repeat 1 to n times:
Sales Transaction Date
Sales Amount
Item
Clearance Item?
Let’s first create the two tables using paper and pencil.
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 records without parents)
Why Relational?
• Eliminates redundancy
• Makes adding data easier
• Allows for more secure access to only parts of
the data
• Now let’s create our tables and enter our data
into Access (data is on the next slide)
Data
1
2
3
Smith
Chen
Wilson
 3/3/09
 3/3/09
 3/4/09
20.45
5.99
29.99
555-5555
666-6666
777-7777
Shirt
Scarf
Jeans
Palos Heights
LaGrange
Chicago
Y
N
Y
1
1
3
This is the foreign key
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
• List all customer names and phone numbers
• List customer name, sales date, sale price and
item name of all clearance items sold
• List customer name, sales date, sale price and
item name of all clearance items sold on
3/3/09
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 3
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
• Sum, Avg, Max, Min, StDev, Var can be performed on
types on counter, number, currency, date/time, and
yes/no
Example
• Say you have a database for a vet (the pets
database on QRC website)
• 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
• Now we can do Activity 4