How Databases Work For Records Management - PowerPoint PPT Presentation

1 / 80
About This Presentation
Title:

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 Databases are increasingly at the core ... – PowerPoint PPT presentation

Number of Views:357
Avg rating:3.0/5.0
Slides: 81
Provided by: JoeGe7
Category:

less

Transcript and Presenter's Notes

Title: 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
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
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
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
62
Separate 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
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
Document Types
Document Type ID
Document Type
Locations
Location ID
Location
Owners
Owner ID
Owner Name
Cabinet Types
Cabinet Type ID
Cabinet Type
65
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
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

Employees Table
Employee ID
Employee Name
Supervisor ID
Supervisor Name
Address
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
Write a Comment
User Comments (0)
About PowerShow.com