Database Management Systems
(DBMS)
Hsci 391 - Computer Applications in Health Science
California State University Northridge
Characteristics of Database
Management Systems (DBMS)
A Database is a collection of one or
more tables.
A table is a collection of records.
– A record is a collection of pieces of
information (fields or variables) about
someone or something.
– Records are similar to the concept of
ROWS in a spreadsheet.
DBMS Characteristics
Fields are similar to the concept of
columns in a spreadsheet
– Fields can be used in boolean logic
statements to generate a query.
The structure of a table must be pre-
defined before any data can be input into
not
Not as free-form as a spreadsheet.
Differences between DBMS’ and
Spreadsheets
Spreadsheets hold a limited amount of
information.
– Maximum number of records in Excel is
about 65,000
DBMS capacity is only limited by the
computer.
DBMS’ are much faster at finding and
manipulating databases.
Types of Fields
Fields have to be pre-defined before
data can be input. Types include:
– Numeric - can only contain numeric values
– Character - can contain anything but
cannot be used for math calculations
– Date - used to hold and manipulate dates
– Logical - 2 possible values (Yes or No)
– Memo - can be a document or an object.
Year 2000 Date Problem
In order to save space in databases dates were only
entered using the last two digits of the year EG.
– 07/13/97 instead of 07/13/1997
– Many programs only know to look at 2 digits
At 12/31/2001 computers will be unable to distinguish
the year 1900 from 2000 for these dates.
How to fix
– Windowing
– Manual fixes
2 Types of Databases
Flat File Database
– Consists of only one table
Relational Database
– Consists of two r more inter-related tables
linked by a common field.
A class attendance database
FirstName LastName Address Bdate SSN LastClass
Joe Smith 123 Main Street 1/2/85 123-456-789 5/1/97
Joe Smith 123 Main Street 1/2/85 123-456-789 6/4/96
Joe Smith 123 Main Street 1/2/85 123-456-789 6/4/94
Larry Jone 3445 Oak Ave. 2/6/70 123-456-790 8/1/89
Larry Jone 3445 Oak Ave. 2/6/70 123-456-790 8/9/98
Larry Jone 3445 Oak Ave. 2/6/70 123-456-790 9/1/97
Fred Williams 5632 Walnut Blvd. 3/5/69 123-456-791 2/4/96
Fred Williams 5632 Walnut Blvd. 3/5/69 123-456-791 2/4/95
Fred Williams 5632 Walnut Blvd. 3/5/69 123-456-791 2/6/91
Fred Williams 5632 Walnut Blvd. 3/5/69 123-456-791 7/2/95
Jim Keating 2343 Cypress Dr. 6/1/79 123-456-792 9/2/95
Jim Keating 2343 Cypress Dr. 6/1/79 123-456-792 11/1/98
Jim Keating 2343 Cypress Dr. 6/1/79 123-456-792 8/6/99
Mary Lou 4353 Redwood Ln. 8/3/58 123-456-793 2/1/99
Mary Lou 4353 Redwood Ln. 8/3/58 123-456-793 2/6/98
Mary Lou 4353 Redwood Ln. 8/3/58 123-456-793 6/5/97
Re lational Exam ple
FirstName LastName Address Bdate SSN
SSN LastClass
Joe Smith 123 Main Street 1/2/85 123-456-789
123-456-7899 05/01/97
Larry Jone 3445 Oak Ave. 2/6/70 123-456-790 123-456-7899 06/04/96
Fred Williams 5632 Walnut Blvd. 3/5/69 123-456-791 123-456-7899 06/04/94
Jim Keating 2343 Cypress Dr. 6/1/79 123-456-792 123-456-7901 08/01/89
123-456-7901 08/09/98
Mary Lou 4353 Redwood Ln. 8/3/58 123-456-793 123-456-7901 09/01/97
123-456-7910 02/04/96
123-456-7910 02/04/95
Static information is kept in its own table, linked 123-456-7910 02/06/91
123-456-7910 07/02/95
by a key field, and used as a lookup table. 123-456-7921 06/02/95
This means: 123-456-7921
123-456-7921
11/01/98
08/06/99
Static information is entered only one time 123-456-7930 02/01/99
123-456-7930 02/06/98
Smaller file sizes 123-456-7930 06/05/97
Address and other changes need only be made in
one place
Less likelihood of error
Lower cost of data input
Relational Databases
Only useful when there are repetitive
records.
Save Space
Save Data Input time and cost
Lower probability of error
Require a unique key field in order to
link.
Changing the order of records -
Two Methods
Sorting
– A copy of the table is made with records in
the new order
Indexing
– A small file is generated which keeps track
of each record based on what position it
should be in.
– Allows faster “Seeks” to be performed
Indexing
FirstName LastName Address
1 Joe Smith 123 Main Street
2 Larry Jone 3445 Oak Ave. Original
3 Fred Williams 5632 Walnut Blvd. Table
4 Jim Keating 2343 Cypress Dr.
5 Mary Lou 4353 Redwood Ln.
4 Smith
1 Jone
5 Williams
2 Keating Index Key
3 Lou
FirstName LastName Address
2 Larry Jones 3445 Oak Ave.
4 Jim Keating 2343 Cypress Dr. Indexed
5 Mary Lou 4353 Redwood Ln. Table
1 Joe Smith 123 Main Street
3 Fred Williams 5632 Walnut Blvd.
Considerations in Creating
Databases
Attention to initial design - is everything you
will need in there?
Procedure to ensure consistency and integrity
Eliminate redundancy between data collection
and data entry
Ensure access for those who need it.
Protect sensitive data from inappropriate
access.
