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
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
62Separate Tables With IDs
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
65Relate With Foreign Keys
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
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