Title: How Databases Work For Records Management
1How Databases Work For Records Management
Presented By Joe Gentry President, Xpert Systems,
Inc.
2Record Keeping Uses Databases
- Databases are increasingly at the core of records
management classification and indexing from
simple electronic card catalogs to complex
applications such as electronic record-keeping
systems.
3Our Direction Today
- We will discuss database usage in records
management, including in depth analysis of good
and poor database designs.
4Target Audience
- Ever Heard of the Beginner Series of books that
begin with For Dummies? - This is the Databases For Dummies Presentation
- (But, of course WE are not Dummies) ?
5Why Do I Need To Know This?
- Databases are everywhere
- You use them in everyday applications
- They Force you to be Organized
- People will talk about them you should at least
know enough to talk about them - Business Data Collection
6How Can I Apply This?
- Good Question!
- What Do You Do?
- Keep Track of Things Any Thing
- Records
- Files
- Collections
- To Do Items Task Lists
7What We Will Cover
- What is a Database?
- Different Database Uses
- How To Create a Database
- How To Design Tables (Good and Bad)
- How To Connect To Your Data
- How To Find Your Data
- How To Apply This To Your Work
8What We Will Not Cover
- The Tough stuff Its unnecessary
- The College stuff Whos got time
- The Bad stuff The Sky is Falling
- The Hard stuff Easier Than You Think
- The Boring stuff My Personal Challenge
- No one will walk out of here today and apply for
a job as a Database Administrator. There are
lots of courses taught and books sold to give
you more detailed information on databases.
9What Is A Database Really?
- The Purpose of a Database is to Store Data
- Physically, a Database is Implemented as one or
more files depending on the type of database
used - Databases consist of Multiple Objects
10Who Uses Databases?
- Everyone who uses Windows!
- Run REGEDIT.EXE some time
- View the Microsoft Windows Internal Database
- Where Associations Are Stored
- Where Application Settings Are Stored
- Where Object Permissions Are Stored
11(Windows Database Trick)
- Ever Install A Program That Puts an Annoying
Reminder Message That Just Never Seems to Want To
Go AWAY? - Would You Like to Know the Trick Hiding Place
For This Application?
12Internet Giants Use Databases
- The Following Web Sites have to handles thousands
of requests each day all relying heavily on, you
guessed it, Databases! - E-Bay
- Amazon.Com
- Imovies
- CheapTickets
13Database Objects
- Tables
- Keys
- Constraints
- Indexes
- Views
- Stored Procedures
- Triggers
- Defaults
- Rules
14Whos Afraid Of Databases?
- Well If youve Taken A Course Of Any Kind On
Database Design Theory Probably You Are! - Today, We Are Going To Focus on Simplicity So
Never Fear
15Can We Make This Complicated?
- Before We Delve Into How The Internal Workings of
A Database Operate, Lets Consider The Following
Its Like Driving A Car - Many people have no idea how the Internal
Combustion Engine operates in their Automobile - They Just Need To Know How To Turn The Key To
Start The Motor And Thats It!
16Database Applications
- Lets Take A Few Minutes To Discuss How Databases
Are Used In Some Day-To-Day Programs
17Example Database Applications
- Contact Manager
- Document Manager
- Library Books Manager
- Accounting
- Retail Store Manager
- Document Capture Management
- Work-Flow Management
- Personal Organizer
- Human Resources Manager
18Contact Manager
- As used by the salespeople in any company
- Keep track of all the customers you work with
- Contacts at that company
- Every phone conversation
- Every letter sent out
- Every follow-up that needs to be performed
- Tie all of the different salespersons data
together to give the sales manager an overall
view of what their people are doing - Where they are in the different sales cycles
- Are they even working?
19Document Manager
- Keeps track of your documents
- Paper, Electronic Files, Images, Email, etc.
- For every document you need to know
- What it is?
- Who it Belongs to?
- Where should it go?
- How long do I need to keep it?
- How do I need to secure it?
- How do I find it later?
20Library Books Manager
- Used to track the Corporate Library
- What books on shelves
- What books are loaned out
- When are They due back
- Who has what
- Where are the books located
21Accounting Management
- Similar to Quicken/Quickbooks
- Create Invoices
- Customers
- Who Owes Us Money
- Who We Owe Money To
- Taxes Collected/Owed
- Employees
- Payroll
22Retail Store Manager
- Customers
- Products Sold
- Taxes Collected
- Sales People
- Commissions Owed
- Inventory On Hand Inventory To Order
- Reports
23Document Capture Management
- Used When Scanning Paper Documents
- What Type Of Document Is It?
- Where Does It Get Stored?
- What Index Information Do I Need?
- Is It Part of a Batch of Documents?
- Do I Want To QA, OCR, Index Each One?
- Internal Routing of Batchs Work-Flow
24Work-Flow Management
- In-Box Work-Flow
- Moves Documents From Point to Point
- Tracks Documents
- Which Work-Flow
- Where/Who It Came From?
- Where It Goes Next?
- Approval Action (Initials, etc.)
- Age of Items
- Reports
25Personal Organizer
- Day-Timer Type of Application - Outlook
- Daily To-Do Lists
- Short Term Goals
- Long Term Goals
- Personal Phone Numbers
- Important Dates (Birth Dates, etc.)
26Human Resources Manager
- Keeps Track of People
- Resumes
- Application Data
- Job Reviews Performance Evaluations
- Equipment Assigned
- Office Locations
- Payroll
27The Common Denominator
- The Glue that holds all of these different
applications together is the database - Every significant business application must store
its information somewhere
28Levels Of Database Usage
- Ordinary Application User No Idea What A
Database Even Is - Seasoned Application User Knows Enough About
Databases To Be Dangerous Knows Where Data is
Stored And How To Poke Around - Application DeveloperIs Responsible For
Designing Database Architecture - Database AdministratorThe Guru
29Hypothetical Situation
- It Never Fails! Your Boss comes to You and
Assigns The Task of Tracking Records For A
Special ProjectHow Are You Going To Keep Track
Of Them?
30But Ive Got Excel Here!
- Over The Years, I Have Seen Them All
- Post-It Notes
- Plain Notebook Paper
- Index Cards
- Excel Spreadsheets
- Notepad Text Document
- A Database
- A Really High-End Database
31Different Types Of Databases
- There are Three Main Types of Databases
- Desktop DatabasesAccess, FoxPro, Paradox,
Btrieve, etc. - Client/Server DatabasesSQL Server, Sybase,
Oracle, Informix, etc. - Mainframe DatabasesDB2, etc.
32Database Components
- Data FilesWhere The Actual Data is Stored
- Database EngineHandles Search Requests, Record
Additions/Deletions, etc. - User Interface Some Method to Connect To The
Database Engine
33Desktop Databases
- These are the Do It All databases
- The Three Main Components All Operate Together On
Your Local Desktop Machine - The Database File is Stored On Your Local C
Drive - The Database Engine Resides In Memory on Your
Machine - The User Interface is Running On the Same Machine
34Microsoft Access
- Part of the Microsoft Office Suite of Products
(Professional Edition) - Simple to Use
- Easy To Create Objects using Wizards
- Built-In Reports
- Built-In Programming Language
- Stores File as .MDB file on Drive
35Desktop Utility
- Desktop Databases Get the Job Done And Have Been
Doing So For Many Years - You Can Store Your Data For Many Years And You
May Still Get The Job Done - But Then, The Question Is
36Do You Share?
- If you have a Desktop Database That Is Being
Shared By Many Other Users, You Start To Edge
Into The Next Level - When There Is A Lot of Activity Against A Desktop
Database such as Access, Locking Issues Creep
Into The Picture
37Room To Grow
- The Next Most Pressing Issue With A Desktop
Database is Volume - As The Size Grows Depending On The Situation,
The Database May Become Inappropriate For The Job
38Client/Server Who?
- A Client/Server Database Breaks Down The Three
Main Components Over Two Or More Computers - The Server is Where The Database Files Are
Located - The Server is Where The Database Engine is
Located (Possibly A Different Server From Where
The Database Files Are Located) - The Client is The User Interface Portion of The
Process And Is Processed By The Local Users
Computer
39Server Databases
- Server Databases usually run as a Service on the
Server Computer - Its A Program That is Running In Memory Just
Waiting For Users To Request Work - This Service Program Can Handle Many Different
User Requests At Once - They Can Store Large Volumes of Data
- Data Files Can Even Span Multiple Physical
Devices
40Through Thick And Thin Clients
- Early Client/Server Applications Were known as
Thick Clients - An Actual Program Is Installed On Your Local
- Machine That Connects To The DatabaseUpgrading
Lots Of UsersLots Of Pain - Today More People Desire Thin Clients
- Usually Little Or Nothing is Installed On Local
Computer - Updates Are Done In One Place The Server
- Often, But not Always, Browser-Based
41Applications Store Data
- New Thin Client Applications Allow me To Do My
Work Through A Browser - The Data Application can Add New Records, Perform
Searches, Run Reports - Basically I can Do All I need To Do With Nothing
Installed On My Machine - Okay, Need a Browser with the Latest Java Runtime
42Lets Do It!
- Okay So Your Boss Is Still Dropping Hints That
Your Project Has Yet To Show Any Progress - Lets Get Busy Writing The Plan
43The Plan
- The Project Needs A Plan
- Even If It Is A Simple One
- Step 1. Describe The Scope of Project
- Step 2. Outline Objects Involved
- Step 3. Describe Relationships Of Objects
- Step 4. Start Initial Design On Paper
- Step 5. Design Actual Database
44How Do I Pick A Database?
- When Deciding Which Database to Use, There Are
Three Major Decisions That Need To Be Answered - What Can I Afford?
- How Much Data Will I Have?
- How Many Users Will I Have?
45Database System Comparison
Access Low Number Users/Moderate Frequency of Transactions, or Moderate Number of Users/Low Frequency of Transactions
SQL Server High Number Users/Moderate Frequency of Transactions, orModerate Number Users/High Frequency of Transactions
Oracle High Number Users/High Frequency of Transactions
46Simple Decision For Us
- Since Our Department Has NO BUDGET And We Already
Own Microsoft Access, We Will Do Our Project
Using Access - The Good News All Of Our Data Can Easily Be
Imported Into Either SQL Server Or Oracle When
The Time Comes
47How Do I Get Started?
- Step 1. Describe The Scope Of Project
- Basically, Its To Manage All Of The Folders And
Documents In A Five Drawer Filing Cabinet
48Paralysis Of Analysis
- Step 2. Outline Objects Involved
- This is Where A Lot Of Projects Can Get Bogged
Down Or Go In The Wrong Direction - Dont Over-Analyze
49Simple Design
- The Objects
- Cabinet
- Drawers
- Folders
- Documents
50Whos Related To Who?
- Step 3 Describe Relationships Of Objects
- One Cabinet Has Many Drawers
- One Drawer Has Many Folders
- One Folder Has Many Documents
51Initial Design
- Step 4 Start Initial Design On Paper
- Designing A Database Is As Much Art As It Is
Science - Dont Over-Complicate Matters If Possible
52Create The First Database
- Step 5. Design Actual DatabaseThis is Where the
Fun comes in
53The Wrong Way
- We Create One Table Called FileCabinets With The
Following - Cabinet Name What we Call this Cabinet
- Cabinet Location Where its Physically Located
- Cabinet Type We Have Many Different
- Drawer Number Drawer 1, 2, 3, etc.
- Drawer Name Label on the Front of Drawer
- Folder Name Label on Actual Folder
- Folder Owner Who is Responsible For This
Folder? - Document Name What is The Document Called
- Document Type What Kind of Document is it?
- Document Attribute 1 Used To Describe The
Document - Document Attribute 2 Used To Describe The
Document
54Get The Job Done
- Pick up any book on Database Design and you will
probably get overwhelmed by all of the jargon. - There are lots of Preferred ways to do things
- Most Import of All is Get The Job Done
55Before We Begin The Design
- We Are Going To Have To Consider The Right Way
To Build Our Database - What Is The Right Way?
56Parental Advisory
- The Next Few Slides May Be Deemed Inappropriate
For People Of ALL Ages - Stick With Me, Here Comes The Technical Stuff
57Relational Database Design
- Before We Look At Relational Database Design We
Should Define The Term Relational Database - A Relational Database is a Database That Has Been
Organized Into Related Tables, Rows, and Columns - This Type of Database Normally Uses More Tables
That Are Narrow Vs. Few Tables That Are Wide - More Tables That Contain Fewer Columns
58Are You Normal?
- When You Take A Course On Relational Database
Management Systems, Or Pick Up A Book That Covers
Database Design, Count On One Thing To Come Up - Normalization!
- A Normalized Database Improves Performance Even
Though There Are More Tables
59Normalization
- There Are Six Different Forms of Normalization
- Each Method is Usually Referred To As Normal
Form - There Are Three Normal Forms Commonly Used
- Over-Normalized Databases Will Be Normalized To
The Fourth, Fifth, Or Sixth Forms And May
Actually Slow Overall Performance
60First Normal Form
- Eliminates Repeating Groups of Data in a Table
- Create a Separate Table For Each Set of Related
Data - Assign a Primary Key To Each Table Which is Used
as an ID (Identifier)
61Eliminate Repeating Groups
Cabinet Name Cabinet Location Cabinet Type Drawer Number
Row 1 HR Cabinet HR Break-room 5 Drawer 1
Row 2 HR Cabinet HR Break-room 5 Drawer 1
Row 3 HR Cabinet HR Break- room 5 Drawer 1
62Separate Tables With IDs
Cabinets
Cabinet ID
Cabinet Name
Cabinet Location
Cabinet Type
Drawers
Drawer ID
Drawer Number
Drawer Name
Folders
Folder ID
Folder Name
Folder Owner
Documents
Document ID
Document Name
Document Type
Document Attribute 1
Document Attribute 2
63Second Normal Form
- Create Separate Tables For Sets Of Values That
Apply To Multiple Records - Relate These Tables With Foreign Keys
64Tables For Values That Apply To Multiple Records
Document Types
Document Type ID
Document Type
Locations
Location ID
Location
Owners
Owner ID
Owner Name
Cabinet Types
Cabinet Type ID
Cabinet Type
65Relate With Foreign Keys
Cabinets
Cabinet ID
Cabinet Name
Location ID
Cabinet Type ID
Drawers
Drawer ID
Drawer Number
Drawer Name
Folders
Folder ID
Folder Name
Owner ID
Documents
Document ID
Document Name
Document Type ID
Document Types
Document Type ID
Document Type
Locations
Location ID
Location
Owners
Owner ID
Owner Name
Cabinet Types
Cabinet Type ID
Cabinet Type
66Third Normal Form
- Eliminate Columns That Do Not Depend On the
Primary Key
67Eliminate Columns
- The Columns In Each Table You Define Should
Relate To Primary Key Doesnt Belong
Employees Table
Employee ID
Employee Name
Supervisor ID
Supervisor Name
Address
68Can You ID That Person?
- The Trick To The Primary Key is Uniqueness
- Sometimes Defining the Primary Key is Fairly
Simple - Social Security Number
- Phone Number
- Employee Number
- License Number
- Sometimes You Have To Completely Make One Up
- Folder ID, Document ID, Cabinet ID, Drawer ID
- Fortunately Auto-Incrementing Columns Help
69Doesnt Feel Very Normal
- Okay, We Now Have a Normalized Relational
Database Designed and Created What Next? - If You Are Adding Records One By One, It Could Be
Considered Painful - The Best Solution is to Write a Client
Interface Of Some Sort That Connects To The
Database And Allows You To Enter Data And
Retrieve Data
70Slow Down
- Relax - Were Not Going To Turn You Into
Programmers At This Point - This Process Is Very Much Required In Every
Electronic Records Management Application
Developed Today Only Much More Complicated Than
What We Have Done, Of Course
71Ways To Connect To A Database
- We Have A Database And With The Help Of One Our
Programmer Friends (NICE People To Have Around )
We Also Have A Client Program To Use With The
Database As Well - How Do They Talk To Each Other?
72The Old Way
- In The Early Days, Programmers Wrote Applications
That Were Tightly Coupled With A Specific
Database - If The Customer Needed A Version For A Different
Database - Several Years Ago
73ODBC K.I.S.S.
- Several Years Ago Microsoft And Other Database
Software Vendors Defined A Standard Interface For
Connecting To Databases Called ODBC - Open Database Connectivity
- Application Developers Could Now Write One
Application That Could Connect To Practically Any
ODBC-Compliant Database - Simple
74Out With The Old
- As Is Common In Technology ODBC is now considered
Old - The Current Revision of The ODBC Standard is 3.0
and Will Be The Last - The Newest Kid On The Block Is Called
75ADO - OLEDB
76(No Transcript)
77SQL The Language Of Databases
78A Little Is All You Need
79A VIEW Of The Future
80Thank You!
- I Hope You Enjoyed This Presentation
- I Am Available For Any Questions or Comments