Title: Intro to Access and Data Management
1- Intro to Access and Data Management
2Announcements
- No Class Monday
- Chapter 5 Wednesday
- Access Tutorial DB Creation - Friday
3The Database Approach
- Database management system (DBMS)
- Minimize the following problems
- Maximize the following
- Data redundancy
- Data isolation
- Data inconsistency
- Data security
- Data integrity
- Data independence
4How 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
5Data Hierarchy (continued)
Field
Table
Record
Database
6Database 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
7Relational 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.
8Exercise 1 Online Shopping DB
- Create a new DB called OnlineShopping that will
consist of three tables. - Customers
- Orders
- Products
9Exercise 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 ? Â
10Exercise 1 Online Shopping DB
- Create the appropriate relationships for the
tables in your database. - Customers -gt Order
- Products -gt Order
11Exercise 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 (910) 555-5555
2222 Larry Bird 456 Somewhere St French Lick IN 47432 (812) 555-5555
3333 Irvin Johnson 789 Nowhere Ave Los Angeles CA 90001 (213) 555-5555
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
12Exercise 1 Online Shopping DB
- Create a query to answer the following question
- What is the total amount spent on all orders by
each customer?