Transcript Chapter 10: Databases & Information Management

Databases Management Systems
CIS105
Brain Teaser…
Draw a pyramid using 10 dots
(see next slide)
After moving to the next slide, don’t
click until you are ready to have the
answer revealed.
Invert the pyramid so that the peak is
at the bottom by moving as few dots as
possible.
2
3
Topics





Explain the differences between File
Management Systems and Database
Management Systems
Describe the concept of data integrity
Describe the functions of a database
management system
Describe some key items to consider when
designing and building a database
Compare information systems such as
TPS, MIS, DSS, and EIS
4
File Management System (FMS)
Using the operating system’s features to
manage files and data (ie: Windows Explorer).
5
File Management System - limitations
User manually creates folders and files.
User needs to remember what data is stored in
each file.
The operating system does NOT know which
files are related to each other.
Will require programming to create
relationships and random access.
Ad-hoc queries are almost impossible –
need to write a program.
Not too efficient.
6
Data Organization and
Access Methods for FMS
Sequential File Organization



Records are stored in the order they were entered.
If a particular record is desired, all prior records
must be read first.
Tape storage uses sequential organization
Random File Organization


Go directly to desired record by using a key.
Organization may be...
 Direct File Organization
 Indexed File Organization
 (see next slides).
7
Direct File Organization
Must be created using a programming language
and must be supported by the operating system.
Location on storage device is usually determine by
hashing (randomizing algorithm).
8
Indexed File Organization
Combines elements of sequential and direct
methods.
Records stored sequentially, but file contains an
index for direct access.
Index is sorted and contains record key.
Data is directly accessed using the key.
May have more than one index
(Customer Id, Last Name).
More efficient to use a database management
system that incorporates all of these features...
9
Database Management Systems (DBMS)
Software that allows us to
Create a database
 Maintain data (add, change, delete)
 Query data (retrieve and sort)
 Create forms and reports
 Access is the software used for this class
Relational products are known as Relational
Database Management Systems (RDBMS).
Now most DBMS products are relational, so
they may be referred to as DBMS or RBDMS. 10

Relational Model
1969, Dr. Edgar Codd developed the
Relational Model of Data.
Application of math theory.
Relational databases became popular
because they:



can be “easily” modified and expanded.
relationships between tables can “easily” be
defined.
data can “easily” be queried
11
Microsoft Access is a RDBMS
Database - a collection of
related tables and objects
Table – a collection of
records for a single subject
Query – selects data based
on selection criteria
Form – simplifies data
entry by displaying one
record at a time
Report – formatted
information suitable for
distribution (read-only)12
Hierarchy of Data (DBMS)
Data is organized in layers, and each higher
level of data consists of one or more items from
the lower level.
File – contains a collection of related tables
 Tables – a collection of records (rows) for a
single subject
 Record (rows) – a group of related fields
that describe a person, place, or thing
 Fields (columns) – store characters for
specific item (acct #,name, address)
 Characters – are defined using bits.
13
Database Design
Databases must be designed and developed before
data can be entered.
During design consider the needs:
What data needs to be collected?
 What reports will be needed?
 What queries you may want to make?
 We usually begin with the end in mind (Covey).
 If we know the required output, we can identify
the required input and processing (computations).
Much time should be spent in the design phase,
before anything is developed. It will save time and
money.
During the design phase, and ERD is developed…

14
Entity Relationship Diagram (ERD)
See next slide…
15
Entity Relationship Diagram (ERD)
Databases designs are depicted in an ERD.
ERD shows:






Tables
Fields (columns)
Relationships
Primary keys – uniquely identifies record (Id)
Foreign keys – used to link from one table back to the
primary key of another table.
Index – a field that sorted to facilitate fast searches
(Name)
The data types of the fields will need to be
determined before building (next slide).
16
Data Types
The data type
of each field
determines
what kind of
data can be
entered into
the field.
17
Eliminate Data Redundancy during Design
18
Data Management
Data Management – the procedures used to
acquire, access, and maintain data.
Data will be used to provide information that
is used to make decisions.
Data integrity – the degree to which data is
accurate and reliable.
Defining validation rules increases data
integrity (see next slide).
19
Data Validation
Data entered into the felds can be checked by
the DBMS by specifying Validation Rules
 Alphabetic or numeric check
 Completeness check
 Range and consistency check
 Required entry
20
Data Maintenance
Three basic operations


Entering new data
Updating existing data
 Some systems record data before and after change

Deleting data
 May need to archive data before removing
 Some systems set record to inactive instead of
removing it
These operations can be perform via



Datasheet View
Forms
Custom Programming
Datasheet
Form
21
Structured Query Language (SQL)
Pronounced by saying the 3 letters S-Q-L,
or as see-quell.
SQL is a language included in a RDBMS, and it
is used to maintain (insert, update, delete) and
query (select) a database.
There is a basic set of SQL commands that are
common in all RDBMS, but many vendors then
extend the language.
Because of SQL, 3rd party companies can
develop products that will work with many
different database such as Crystal Reports
(reporting tool) by Business Objects.
Such reporting tools use the data dictionary to
22
get to the data.
Database Query
A query is a request for specific data from the database.
The query would list the columns you want to display.
The records display may be limited by entering selection
criteria (select all Freshmen).
A Wildcard is a special character (* or ?) inserted in the
selection criteria and it used to search for a pattern of
characters. For example: lastName Like “Sm*” would select
all last names that begin with Sm: Smitts, Smith, etc.
Common queries can be saved for future use or to use it to
create a report or mailing labels.
The data selected by the query is not saved with the query
– many confuse a query with a table.
A query is dynamic – each time a query is executed, it is
processed against the data currently stored in the database,
so the results are always up-to-date.
Query-by-Example (QBE) uses a graphical interface or a
23
form to generate the SQL for users.
Mail Merge
A Mail merge is when a form letter and
a list of names and addresses are used
to create personalized letters.
Form Letter in Word
or an Access Report
Names and
Addresses
Personalized letter
for each individual
in the database.
24
DBMS Key Features
Reduces data redundancy
Query capabilities
Security – can be set at the table and
field levels.


Read-only privileges
Update privileges
Backup & recovery tools
Concurrent access to data (multi-user)
Data dictionary (next slide)
25
Data Dictionary
Data Dictionary contains data or
documentation about the tables and fields
included in the database.
The data dictionary is automatically created
as the tables and columns are defined and
documented.
Contains tables names, field names, field
sizes, validation rules, input masks, default
values, table relationships, etc.
Used by DBMS to automatically generate
forms, reports, queries, and SQL based tools.
26
Object-Oriented Databases
Databases now need to be able to store:




Images
Video
Audio
Other complex formats
For example



X-rays, MRI
Audio and video segments
Geographical Information Systems (GIS) maps
27
Examples of DBMS Products
Corporate





Oracle
MySql
Informix
IBM DB2
Microsoft SqlServer
Personal





MS Access
MS FoxPro
Filemaker Pro (Mac & Win)
Paradox
Approach
Free versions of Oracle, MySql, and MS SqlServer are
available via download.
28
Database Administrator (DBA)
Coordinate the creation, maintenance
and use of the database.
Database Design and redesign
Security administration
Performance monitoring
Backup and recovery
Other duties as assigned.
29
Databases for Information Systems
Information Systems are built with a
database at the core.
Information Systems may include:

Operational or Transaction Processing Systems (TPS)

Management Information Systems (MIS)
Decision Support Systems (DDS)
Executive Information Systems (EIS)

See next few slides for details…


30
Operational or Transaction Processing Systems (TPS)
Operational systems are designed to support
the day-to-day transactions of a business –
sales, orders, purchasing, accounts payable,
payroll, etc.
Details of all transactions are captured and
saved in a database.
The detail is then manipulated and
summarized for other systems
(see next few slides).
31
Management Information Systems
(MIS)
MIS is designed to provide accurate, timely, and useful
information from the operational systems.
Managers received reports as scheduled
(daily, weekly, monthly, etc.)
A MIS manager runs the MIS department


Also called Chief Information Officer (CIO)
Must be comfortable with computer technology and the
organization’s business
MIS still exist, but has expanded to…
 Decision Support Systems (DSS) – support managers in
non-routine decision-making tasks


Executive Information Systems (EIS) – support executives
in decisions that affect the entire organization
EIS includes information from outside the organization such as
from competitors, market, and government
32
Differences Between
MIS and DSS (or EIS)
MIS emphasizes planned reports, so reports are
standard, structured, scheduled, and routine.
Generating ad-hoc reports is constrained by the
design of the operational system.
DSS is designed to support ad-hoc queries, so it
is a separate stand-alone system, but most of
the data comes from the operational system
and other sources.
DSS focuses on supporting decision making
33
Informatics
Informatics – the study of how information is
gathered, stored, manipulated, accessed, transferred,
secured, given meaning, and presented.
Biomedical Informatics (BMI) – is an emerging
discipline that combines the health science
knowledge including medicine, dentistry, pharmacy,
nursing, radiology and biological sciences with
computer science, mathematics, statistics,
engineering, information technologies and
management.
Good field for those interested in medicine and
technology.
ASU – School of Computing and Informatics has a
program: bmi.asu.edu/index.php
34
MCC’s Database Courses
CIS117DM – Access Level I
CIS217AM – Access Level II
CIS119DO – Oracle
CIS276DA – MySql
CIS276DB – MS SqlServer
35
Access Introduction/Overview
Access (application)
File: databaseName.accdb
A file contains database Objects and
the actual data.
Table
Definitions
Database
Dictionary
Actual
Data
Forms
Reports
Queries
36