Database

1

• Database
• collection of related information stored in an
  organized form
• Database program
• software tool for storage retrieval of that
  information.

2
Database Terminology

• Data is held in tables
• Record one row in a a table
• Field columns that each record is divided into
• Filing cabinet analogy
• 
  
• Database Table
  Record Field
• (ABC Inc.) (Customers) (cust78)
  (last name)

3
Relational Database one-to-one relationship
table

• EMPLOYEE TABLE
• Last First Work
• Id Name Name Station
• 001 Smith Joan 6-1942-A
• 002 Jones Paul 9-0865-A
• 003 Doe John 8-3649-B
• 004 Black Jason 8-3775-A
• ... .... .... .......

• WORK STATION TABLE
• Work Make and Operating
• Station Model System
• 6-1942-A IBM-153 MS
• 8-3649-B DELL-A14 Linux
• 8-3775-A IBM-153 MS
• 9-0865-A HP-A919 MS
• ........ ........ ......

key
record
EMPLOYEE
WORK STATION
1
1
each employee has one workstation
4
Relational Database one-to-many relationship

• EMPLOYEE TABLE
• Last First
• Id Name Name Department
• 001 Smith Joan IT
• 002 Jones Paul MF
• 003 Doe John IT
• 004 Black Jason HR
• ... .... .... .......

• DEPARTMENT TABLE
• Id Name
• AR Accounts Receivable
• IT Information Technology
• HR Human Resources
• MF Manufacturing
• ... .... .... .......

key
EMPLOYEE
DEPARTMENT

1
a department has many employees an employee works
in one department
5
Relational Database many-to-many relationship

• STUDENT TABLE
• Last First
• Id Name Name
• 001 Green Jack
• 002 Black Millie
• 003 Taylor Peter
• 004 Trane Oscar
• ... .... ....

COURSE TABLE Id Name ASTB03 Astronomy
CSCA02 The Why and How... CSCA48
Programming MTGA04 Management ...... ........
key

• ENROLMENT TABLE
• Student Course
• 001 CSCA02
• 001 MTGA04
• 003 CSCA02
• 004 CSCA48
• ...... ........

STUDENT
COURSE
1
1
a student takes many courses
ENROLMENT
a course has many students


6
Flat vs Relational

• Flat
• database system that performs operations on a
  single file
• Relational
• database system that has a number of different
  tables, which may be linked together by common
  fields

7
Flat

• Suppose you want to store information about books
  and their authors
• 1st Solution

8
Flat
2nd Solution

• both solutions replication data accuracy
  problems, wastes space, data retrieval problems

9
Relational
Correct solution uses 2 linked tables authors
name common to both tables one-to-many
relationship

8
1
10
Primary Key

• Every table should have a primary key
• must be unique
• prevents duplicates
• maintains sort order (table index)
• used to define relationships
• may be a combination of several fields

Good Bad
SIN Last Name
Employee ID Address
Account Dept ID
User ID Job Title
Email any item that could be duplicated
11
Database Terminology

• this is an Access Table

Record
Field value
12

• primary key
• (every table needs one)
• is used for
• sorting the table
• accessing individual
• records
• linking tables

13
Form for entering records into a Table
14
Form for entering records into a Table
15
example of a Query on one Table
16
similar Query but showing only failing marks
17
similar Query, now modified to include name and
address from another Table
18
example of a Report based on a Table
19
example of a Report based on a Query
20
MS Access Environment
Menu Bar
Toolbar
Database Window
wizards
Object Buttons
tables
Status Bar
21
Creating a Table

• Using Table Wizard
• Designing a table yourself (using Design view)
• field names
• data types
• primary key
• properties
• Views Datasheet or Design
• enter data using the Datasheet view

22
Design View
Primary Key
Field Names
23
Data Types
Data Types Text Memo Number Date/Time Currency Aut
oNumber Yes/No OLE Object Hyperlink
24
Properties
Properties Field size Format Input
Mask Caption Default Value Validation
Rule Validation Text Required Allow Zero
Length Indexed
25
Datasheet View

• Use to add, edit, or delete records

Currently selected record editing ? saved
End
Number of current record
Total records
26
Relationships

• Tools?Relationships

• Create a 1-to-many relationship between the
  CustomerID fields

27
Relationships

• One-To-Many relationship (Join)

• a CustomerID can appear once in Customers and
  many times in Invoices
• a CustomerID in Invoices must be in Customers

28
Relationships

• Enforcing Referential Integrity causes an error
  message if a CustomerID not in the Customers
  table is entered

29
Indexes

• Examine indexes set up for database
• make it faster to search a table
• should be set up if searching on a field is
  anticipated

Note indexing refers to how data is stored,
whereas sorting changes how the table is
displayed.
30
Create a Form wizard

• Form view

31
Form Design view
sizing handles
drag Toolbox
32
Form complex example
Each page shows one record
Label
Text Box(bound to field)
Command Button