ISP 121 Week 2 Normalization and Queries Normalization

ISP 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) • Create a separate table for just the cars • How do you link the two tables together?

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 Dues Paid? National Member? Member ID Relationship 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 Class Taken (repeats 1 -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 enter this one into Access and create a few records of data.

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

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

Parameter Query • What if you are 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 a prompt between square braces: [Enter your prompt here]

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 • The others on counter, number, currency, date/time, and yes/no

Heights and weights database Let’s look at a database of human heights and weights. We’ll perform queries that aggregate data, that is, queries that compute such things as counts, averages, minimum values, and maximum values. We’ll also group records.