how databases work for records management
DESCRIPTION
How Databases Work For Records Management. Presented By Joe Gentry President, Xpert Systems, Inc. Record Keeping Uses Databases. - PowerPoint PPT PresentationTRANSCRIPT
How Databases Work For Records Management
Presented By Joe Gentry
President, Xpert Systems, Inc.
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.
Our Direction Today
We will discuss database usage in records management, including in depth analysis of good and poor database designs.
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)
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
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
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
What We Will Not Cover
The “Tough” stuff – It’s unnecessary The “College” stuff – Who’s got time The “Bad” stuff – The Sky is Falling The “Hard” stuff – Easier Than You Think The “Boring” stuff – My Personal ChallengeNo one will walk out of here today and apply for a
job as a Database Administrator. There are lot’s of courses taught and books sold to give you more detailed information on databases.
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
Who Uses Databases?
Everyone who uses Windows! Run REGEDIT.EXE some time View the Microsoft Window’s Internal
Database Where Associations Are Stored Where Application Settings Are Stored Where Object Permissions Are Stored
(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?
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
Database Objects
Tables Keys Constraints Indexes Views Stored Procedures Triggers Defaults Rules
Who’s Afraid Of Databases?
Well If you’ve Taken A Course Of Any Kind On Database Design Theory – Probably You Are!
Today, We Are Going To Focus on Simplicity – So Never Fear
Can We Make This Complicated?
Before We Delve Into How The Internal Workings of A Database Operate, Let’s Consider The Following: It’s 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 That’s It!
Database Applications
Let’s Take A Few Minutes To Discuss How Databases Are
Used In Some Day-To-Day Programs
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
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 salesperson’s 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?
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?
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
Accounting Management
Similar to Quicken/Quickbooks Create Invoices Customers Who Owes Us Money Who We Owe Money To Taxes Collected/Owed Employees Payroll
Retail Store Manager
Customers Products Sold Taxes Collected Sales People Commissions Owed Inventory On Hand – Inventory To Order Reports
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 Batch’s Work-Flow
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
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.)
Human Resources Manager
Keeps Track of People Resumes Application Data Job Reviews – Performance Evaluations Equipment Assigned Office Locations Payroll
The Common Denominator
The Glue that holds all of these different applications together is the database
Every significant business application must store it’s information somewhere
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
Hypothetical Situation
It Never Fails! Your Boss comes to You and Assigns The Task of Tracking Records For A Special Project
How Are You Going To Keep Track Of Them?
But I’ve 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
Different Types Of Databases
There are Three Main Types of Databases: Desktop Databases
Access, FoxPro, Paradox, Btrieve, etc. Client/Server Databases
SQL Server, Sybase, Oracle, Informix, etc. Mainframe Databases
DB2, etc.
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
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
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
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…
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
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
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 User’s Computer
Server Databases
Server Databases usually run as a Service on the Server Computer
It’s 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
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 Database
Upgrading Lots Of Users=Lots 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
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
Let’s Do It!
Okay So Your Boss Is Still Dropping Hints That Your Project Has Yet To Show Any Progress
Let’s Get Busy Writing The Plan
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
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?
Database 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
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
How Do I Get Started?
Step 1. Describe The Scope Of Project
Basically, It’s To Manage All Of The Folders And Documents In A Five Drawer Filing Cabinet
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
Don’t Over-Analyze
Simple Design
The Objects:
Cabinet Drawers Folders Documents
Who’s Related To Who?
Step 3: Describe Relationships Of Objects
One Cabinet Has Many Drawers
One Drawer Has Many Folders
One Folder Has Many Documents
Initial Design
Step 4: Start Initial Design On Paper
Designing A Database Is As Much Art As It Is Science
Don’t Over-Complicate Matters If Possible
Create The First Database
Step 5. Design Actual Database
This is Where the “Fun” comes in …
The “Wrong” Way
We Create One Table Called FileCabinets With The Following: Cabinet Name – What we Call this Cabinet Cabinet Location – Where it’s 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
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
Before We Begin The Design
We Are Going To Have To Consider The “Right” Way To Build Our Database
What Is The “Right” Way?
Parental Advisory
The Next Few Slides May Be Deemed Inappropriate For People Of ALL Ages
“Stick With Me, Here Comes The Technical Stuff…”
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
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
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
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)
Eliminate 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
Separate Tables With ID’s
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
Second Normal Form
Create Separate Tables For Sets Of Values That Apply To Multiple Records
Relate These Tables With Foreign Keys
Tables 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
Relate 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
Third Normal Form
Eliminate Columns That Do Not Depend On the Primary Key
Eliminate Columns
The Columns In Each Table You Define Should Relate To Primary Key
Doesn’t Belong
Employees Table
Employee ID
Employee Name
Supervisor ID
Supervisor Name
Address
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
Doesn’t 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
Slow Down
Relax - We’re 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
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?
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
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
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
ADO - OLEDB
SQL – The Language Of Databases
A Little Is All You Need
A VIEW Of The Future
Thank You!
I Hope You Enjoyed This Presentation
I Am Available For Any Questions or Comments