Access Lecture

1
Access Lecture

• 3 Jun 2003

2
DBMS

• Database Management System
• Data filing and retrieval

3
Relational Database
4
Versions

• Access 2002/2000
• Access 97
• NOT compatible

5
Run all from My Computer
6
Help

• F1
• ShiftF1
• Help

7
Example

• Northwind.MDB

8
Access contains
9
4 Objects

• Table
• Query
• Form
• Report

10
Open a Database

• Mountain Animal Start.MDB

11
Open a table

• Pets table
• Caution you can delete records here

12
Click sign

• Show related information

13
Different Views

• Datasheet view
• Design view

14
Database Concept
15
Access vs Excel

• of records
• Assignment 1

16
Single Table Problem

• Redundant data
• Hard to maintain
• Deletion anomaly

17
Multiple Tables

• One table for every entity
• Set relationship among tables

18
What fields?

• Enough fields for reports?
• Which fields go to which table?
• Normalization

19
E.F. Codd

• Tables should conform to 3NF (third normal form)

20
Design Fields
21
Proper Data Type

• Date
• Tel
• Yes/No
• True/False
• On/Off
• nonzero value/0

22
Field Size

• just make

23
Changing a Table Design

• You may not undo
• delete fields
• You may not be allowed to do so
• delete field which is part of a relationship
• You have to update all other relevant objects
• change a field name

24
Field Properties
25
Field Size
26
Replication ID
27
Text Format
28
Text Format
29
Number and Currency Format
30
Number and Currency Format
31
Date/Time Format
32
Date/Time Format
33
Boolean Format
34
Boolean Format
35
Format Example

• Pets Table

36
Input Mask
37
Input Mask
38
Input Mask Characters
39
Input Mask vs Format
40
Input Mask for Password
41
Decimal Places
42
Caption
43
Default Value
44
Data Validation Field Level
45
Data Violation
46
Date Validation
47
Validation Rule by Expression
48
(No Transcript)
49
(No Transcript)