Transcript Database Models: Flat Files and the Relational Database

Database Models: Flat Files
and the Relational Database
Objectives:
•Understand the fundamental structure
of the relational database model
•Learn the circumstances under which
it is a better choice than the flat file
What is a database?
• Structured Data
• Procedures for
– Data entry
– Storage
– Validity checking
– Sorting
– Selection
– Reporting
Why use a database?
• To organize & preserve data
• To facilitate analysis and modeling
• To gain insights into the relationships in
your data
• To help turn data into information
• To explore data using exploratory
techniques
• To support the organization
Flat file databases
• Text files (word processing packages)
• Loosely structured storage
• Sorting (whole tables or individual
columns, by number/data/text)
• Reports (via mail and merging functions)
Excel as a database
• All Word database capabilities plus
– Data entry
– Selection/Queries
– Statistics
– Calculations
– Graphics / chart development data
summarization
Relational Database Systems
• All the features support by Excel
– Plus all of the following features
•
•
•
•
•
Validity Checking
Relational queries
Extensive import/export capabilities
Object-oriented model
Strong data typing: support for formatted text,
memo fields, OLE, calculated fields
Why use the relational model?
• Minimize redundancy
• Minimize wasted storage
• Facilitate updates, appending new data to
existing systems
• Facilitate queries, makes asking questions
of the database easier
• Keeps tables small for portability and
improved data processing
Things to be cautious about
• Issues and Design considerations
– Boolean, Integer, Long, Currency, Single,
Double, Date, String, and Variant
– How expressions with different data types are
defined
– When to split entries between different fields
and when to combine them in a common field
– When to use more than one table in a data
base and when to use only one table
Other things to consider
• Redundancy – unnecessary repetition of
information, wastes computing resources
• One-to-one relationships
• One-to-many relationships
• Many-to-many relationships (special case)
Databases are dynamic
• Can accept entry of new data even while
displaying results from current data
• Desktop vs. Client/Server
• Transaction databases
• Internet databases
Timber Stand Inventory
Compartment
Stand
Origin
Watershed
ELTP
63
184
1889
Dry Gulch
26
78
200
1967
Dead Horse
18
210
24
1942
Whitefish
Mountain
45
Information in each column are the attributes of the database, information in
each row are records.
How to design a relational
database
• Identify the purpose of the relational database
• Identify the various the subjects of the database
• Determine what information on each subject is
necessary
• Determine how the subject information is
related to other subject information
• Refine and redesign the database when design
flaws are identified. Final designs are seldom
the first designs considered.
Example
• Tall Timbers Forest Management Inc.
– Tables
•
•
•
•
•
•
•
Forest communities
Harvest schedules
Prescribed burn schedule
Wildlife habitat quality
Riparian management objectives
Restoration plans
Special concerns for threatened and endangered
species