How Databases Work For Records Management

1
How Databases Work For Records Management
Presented By Joe Gentry President, Xpert Systems,
Inc.
2
Record 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.

3
Our Direction Today

• We will discuss database usage in records
  management, including in depth analysis of good
  and poor database designs.

4
Target 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) ?

5
Why 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

6
How Can I Apply This?

• Good Question!
• What Do You Do?
• Keep Track of Things Any Thing
• Records
• Files
• Collections
• To Do Items Task Lists

7
What 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

8
What 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.

9
What 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

10
Who 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?

12
Internet 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

13
Database Objects

• Tables
• Keys
• Constraints
• Indexes
• Views
• Stored Procedures
• Triggers
• Defaults
• Rules

14
Whos 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

15
Can 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!

16
Database Applications

• Lets Take A Few Minutes To Discuss How Databases
  Are Used In Some Day-To-Day Programs

17
Example Database Applications

• Contact Manager
• Document Manager
• Library Books Manager
• Accounting
• Retail Store Manager
• Document Capture Management
• Work-Flow Management
• Personal Organizer
• Human Resources Manager

18
Contact 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?

19
Document 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?

20
Library 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

21
Accounting Management

• Similar to Quicken/Quickbooks
• Create Invoices
• Customers
• Who Owes Us Money
• Who We Owe Money To
• Taxes Collected/Owed
• Employees
• Payroll

22
Retail Store Manager

• Customers
• Products Sold
• Taxes Collected
• Sales People
• Commissions Owed
• Inventory On Hand Inventory To Order
• Reports

23
Document 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

24
Work-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

25
Personal 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.)

26
Human Resources Manager

• Keeps Track of People
• Resumes
• Application Data
• Job Reviews Performance Evaluations
• Equipment Assigned
• Office Locations
• Payroll

27
The Common Denominator

• The Glue that holds all of these different
  applications together is the database
• Every significant business application must store
  its information somewhere

28
Levels 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

29
Hypothetical 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?

30
But 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

31
Different 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.

32
Database 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

33
Desktop 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

34
Microsoft 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

35
Desktop 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

36
Do 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

37
Room 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

38
Client/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

39
Server 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

40
Through 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

41
Applications 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

42
Lets 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

43
The 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

44
How 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?

45
Database System Comparison
46
Simple 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

47
How 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

48
Paralysis 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

49
Simple Design

• The Objects
• Cabinet
• Drawers
• Folders
• Documents

50
Whos Related To Who?

• Step 3 Describe Relationships Of Objects
• One Cabinet Has Many Drawers
• One Drawer Has Many Folders
• One Folder Has Many Documents

51
Initial 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

52
Create The First Database

• Step 5. Design Actual DatabaseThis is Where the
  Fun comes in

53
The 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

54
Get 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

55
Before We Begin The Design

• We Are Going To Have To Consider The Right Way
  To Build Our Database
• What Is The Right Way?

56
Parental Advisory

• The Next Few Slides May Be Deemed Inappropriate
  For People Of ALL Ages
• Stick With Me, Here Comes The Technical Stuff

57
Relational 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

58
Are 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

59
Normalization

• 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

60
First 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)

61
Eliminate Repeating Groups
62
Separate Tables With IDs
63
Second Normal Form

• Create Separate Tables For Sets Of Values That
  Apply To Multiple Records
• Relate These Tables With Foreign Keys

64
Tables For Values That Apply To Multiple Records
65
Relate With Foreign Keys
66
Third Normal Form

• Eliminate Columns That Do Not Depend On the
  Primary Key

67
Eliminate Columns

• The Columns In Each Table You Define Should
  Relate To Primary Key Doesnt Belong

68
Can 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

69
Doesnt 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

70
Slow 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

71
Ways 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?

72
The 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

73
ODBC 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

74
Out 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

75
ADO - OLEDB
76
(No Transcript)
77
SQL The Language Of Databases
78
A Little Is All You Need
79
A VIEW Of The Future
80
Thank You!

• I Hope You Enjoyed This Presentation
• I Am Available For Any Questions or Comments