Database and Microsoft Access

1
Database and Microsoft Access

• Joe Komar

2
Database Hierarchy
3
File Organization

• Sequential -- Physical Proximity
• Indexed -- Index File and Data File with key
  fields
• Direct or Random -- Physical Address from key
  using a hashing algorithm

4
Sequential File Organization
Record one
Record two
Record three
Record four
etc.
5
Indexed Sequential File Organization
Index File
Data File
Key
Address
Physical Location
A
4
B
2
C
1
D
3
E
5
F
G
6
Direct or Random File Organization
Data File
Physical Location
Key Value
Hashing Algorithm
(Computer Program)
7
Database Organization(Multiple Files)

• Hierarchical -- One parent for each child
• Network -- Each child can have more than one
  parent
• Relational -- Tables joined on common data items
  (rows are records, columns are fields, and tables
  are files)

8
Hierarchical Database Organization
File One Parent Record
Pointer
Pointer
File Two
Parent Record
Pointer
Pointer
Child Record
Child Record
File Three
Child Record
Child Record
9
Network Database Organization
File One
Parent
Parent
Parent
File Two
Child
File Three
Child
Child
10
Relational Database Organization
Common Field
Common Field
A
F
F
B
A
Q
D
D
File Two
Joined
File One
11
Object-Oriented Databases

• Object
• Methods

12
Distributed Database Systems

• Database Is Distributed
• Could be Partitioned, Copied, Replicated, etc.

13
Data Warehousing and Mining

• Data Warehouse
• Data Mining

14
Data Definition

• Data Definition Language (DDL)

15
Data Manipulation

• Data Manipulation Language (DML)
• Precompiler
• Host Languages

16
Advantages and Disadvantages of Database
Management

• Advantages
• Better Information
• Faster Response Time
• Lower Operating Costs
• Fewer Storage Requirements
• Better Data Integrity
• Better Data Management

• Disadvantages
• Database Software
• New Hardware
• Training
• Conversion Effort

17
Database Management Systems

• Easily create file structures
• Locate specific records quickly
• Easy to maintain
• Calculations performed
• Standard Reports produced
• Ad Hoc Retrieval

18
Microsoft Access

• Tables -- Files
• Queries -- Ad Hoc retrieval
• Forms -- Data Entry screens
• Reports -- Standard Reporting
• Pages HTML data entry page
• Macros -- Simple Procedures
• Modules -- Access Basic programming

19
Creating Access Tables

• Specify field names, sizes, types of fields, and
  key field
• Field types Text, Memo, Numbers, Date/Time,
  Currency, Counter, HyperLink Yes/No, and OLE
  Object

20
Access Assignment

• Do Exercise 3, p. 687
• Turn in on due date

21
Access Data Entry Forms

• Choose the Forms Tab
• Choose New and Table or Query
• Use the Forms Wizard

22
Access Assignment

• Do Exercise 2, pp. 738
• Turn in on due date

23
Access Reports

• Choose the Reports Tab
• Choose New
• Choose the Table or Query to use
• Use the Wizard

24
Access Queries

• Ad Hoc Retrieval of selected records
• Choose the Query Tab
• Choose New and Table or Query
• Dont use the Wizard!
• Setting Up Table Joins (Relationships)

25
Access Assignment

• Do Exercise 2, p. 799
• Turn in on due date

26
Access Summary

• Database Management System
• Uses Indexed Sequential File Organization
• Uses Relational Database Organization
• Has Tables, Forms, Queries, Reports, Macros, and
  Modules