Intro to Access and Data Management

1

• Intro to Access and Data Management

2
Announcements

• No Class Monday
• Chapter 5 Wednesday
• Access Tutorial DB Creation - Friday

3
The Database Approach

• Database management system (DBMS)
• Minimize the following problems
• Maximize the following

• Data redundancy
• Data isolation
• Data inconsistency

1. Data security
2. Data integrity
3. Data independence

4
How is data organized in a DB Data Hierarchy

• Field
• Record
• File (or table)
• Database

Is a grouping of
Is a grouping of
Is a grouping of
5
Data Hierarchy (continued)
Field
Table
Record
Database
6
Database Management Systems

• Database management system (DBMS)
• Focus of this course Relational database model
• Related Tables (Primary Keys Important)
• Data dictionary
• How do you request data?
• Structured Query Language (SQL) - keywords
• Query by Example (QBE) forms/templates

7
Relational DB Effectiveness Normalization

• Normalization (most streamlined DB)
• Minimum redundancy
• Maximum data integrity
• Best processing performance

Normalized data occurs when attributes in the
table depend only on the primary key.
8
Exercise 1 Online Shopping DB

• Create a new DB called OnlineShopping that will
  consist of three tables.
• Customers
• Orders
• Products

9
Exercise 1 Online Shopping DB
Customers Data_Type Field Size
CustomerID Text 5
FirstName Text 50
LastName Text 100
Address Text 150
City Text 30
State Text 2
PostalCode Text Input Mask
PhoneNum Text Input Mask
Orders Data_Type Field Size
OrderNo ?  
CustomerID ? ?
ProductID ? ?
OrderDate ?  
Products Data_Type Field Size
ProductID Text 5
ProductName Text 50
Category Text 50
Cost ?  
10
Exercise 1 Online Shopping DB

• Create the appropriate relationships for the
  tables in your database.
• Customers -gt Order
• Products -gt Order

11
Exercise 1 Online Shopping DB

• Enter information into tables.

Customers Customers Customers Customers Customers Customers Customers Customers
CustomerID FirstName LastName Address CIty State PostalCode PhoneNumber
1111 Michael Jordan 123 Anywhere Rd Wilmington NC 28401 [phone]
2222 Larry Bird 456 Somewhere St French Lick IN 47432 [phone]
3333 Irvin Johnson 789 Nowhere Ave Los Angeles CA 90001 [phone]
Orders Orders Orders Orders
OrderNo CustomerID ProductID OrderDate
1 1111 00002 9 /18/2012
2 2222 00001 9 /18/2012
3 3333 00003 9 /18/2012
4 1111 00001 9 /20/2012
Products Products Products Products
ProductID ProductName Category Cost
00001 Basketball Sporting Goods 25.00
00002 Shorts Apparel 20.00
00003 Shoes Apparel 75.00
autonumber
12
Exercise 1 Online Shopping DB

• Create a query to answer the following question
• What is the total amount spent on all orders by
  each customer?