LSP 121 Week 2 Normalization and Advanced Queries

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/4/09 20. 45 5. 99 29. 99 555 -5555 666 -6666 777 -7777 Shirt Scarf Jeans Palos Heights La. Grange 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 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, St. Dev, 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, St. Dev, 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 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 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 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