Transcript McLeod_CH06

Management
Information Systems,
10/e
Raymond McLeod Jr. and George P.
Schell
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
1
Chapter 6
Database Management Systems
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
2
Learning Objectives
► Understand
the hierarchy of data.
► Understand database structures and how they
work.
► Know how to relate tables together in a database.
► Recognize the difference between a database and
a database management system.
► Understand the database concept.
► Know two basic methods for determining data
needs.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
3
Learning Objectives (Cont’d)
► Understand
entity-relationship diagrams and class
diagrams.
► Know the basics of reports and forms.
► Understand the basic difference between
structured query language and query-by-example.
► Know about the important personnel who are
associated with databases.
► Know the advantages and costs of database
management systems.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
4
The Data Hierarchy
► Data
field is the smallest unit of data.
► Record is a collection of related data fields.
► File is a collection of related records.
► Database is a collection of related files.
 General definition
 Restrictive definition
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
5
Database
► Table
of rows and columns can be represented in
a spreadsheet.
► Relational database structure is conceptually
similar to a collection of related tables.
► Flat file is a table that does not have repeating
columns; 1st normal form.
► Normalization is a formal process for eliminating
redundant data fields while preserving the ability
of the database to add, delete, and modify records
without causing errors.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
6
Figure 6.1 Spreadsheet Example of
the COURSE Table
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
7
Database (Cont’d)
► Key
in a table is a field (or combination of fields)
that contain a value that uniquely identifies each
record in the table.
► Candidate key is a field that uniquely identifies
each table row but is not the chosen key.
► Relating tables is done through sharing a common
field and the value of the field determines which
rows in the tables are logically joined.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
8
Database Structures
► Database
management system (DBMS)
is a software application that stores the
structure of the database, the data itself,
relationships among data in the database,
and forms and reports pertaining to the
database.
 Self-describing set of related data.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
9
Hierarchical Database Structures
► Hierarchical
is formed by data groups,
subgroups, and further subgroups; like
branches on a tree.
 Worked well with TPSs
 Utilized computer resources efficiently
► Network
allows retrieval of specific
records; allows a given record to point to
any other record in the database.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
10
Figure 6.2 The Hierarchical Structure Between
the DEPARTMENT and COURSE Tables
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
11
Database Structures (Cont’d)
► Relational
is when the relationship
between tables are implicit.
► Physical relationship is when the
database structure (hierarchical, network)
rely on storage addresses.
► Implicit relationship is when the
database structure (relational) can be
implied from the data.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
12
A Relational Database Example
database named Schedule has been
created from tables used earlier in the
chapter and some others
► The database is implemented in Microsoft
Access 2002 (also known as Access XP).
► Databases break information into multiple
tables because if information were stored in
a single table, many data field values would
be duplicated.
►A
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
13
The Schedule Database
The example is implemented on Microsoft Access DBMS
but would be similar on any relational DBMS product.
► The COURSE table in Access (Figure 6.4) is a list of data
field values. The table itself had to be defined in Access
before values were entered into the data fields.
► Figure 6.5 shows the definition of the Code field.
► Figure 6.6 illustrates that Abbreviation field values will be
looked up from a list of values in the DEPARTMENT table.
► Table 6.7 shows a single table of course and department
fields before they were separated into different tables.
►
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
14
Figure 6.4 The COURSE Table in
Access
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
15
Figure 6.5 Defining the CODE Field
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
16
Figure 6.6 Look-up Values
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
17
Table 6.7 Unseperated Table of
Course and Department Data Fields
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
18
Figure 6.7 Access View of Tables,
Fields, and their Relationships
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
19
The Database Concept
► Database
concept is the logical integration of
records across multiple physical locations.
► Data independence is the ability to make
changes in the data structure without making
changes to the application programs that access
the data.
► Data dictionary includes the definition of the
data stored within the database and controlled by
the database management system.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
20
Creating a Database
► Determine
data that needs to be collected
and stored is a key step.
► Process-oriented approach





Define the problem.
Identify necessary decisions.
Describe information needs.
Determine the necessary processing.
Specify data needs.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
21
Determine Data Needs
► Enterprise
modeling approach takes a
broad view of the firm’s data resources; all
areas are considered, and synergy of data
resources between business areas can be
leveraged.
 Result: Enterprise data model
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
22
Figure 6.8 Creating an Enterprise
Data Model
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
23
Data Modeling Techniques
► Entity-relationship
diagrams (ERDs) is
a graphical representation of data in entities
and the relationships between entities.
► Entity is a conceptual collection of related
data fields.
► Relationship is defined between entities.
 One-to-one – 1:1
 One-to-many – 1:M
 Many-to-many – M:N
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
24
Figure 6.11 Entity-Relationship
Diagram
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
25
Diagramming Techniques
► Class
Diagram is a graphical
representation of both the data used in an
application and the actions associated with
the data; object-oriented design model.
► Objects are the data, actions taken on the
data, and relationship between objects.
► Class diagrams consist of the named class,
fields in the class, and actions (methods)
that act upon the class.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
26
Figure 6.13 Class Diagram
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
27
Using the Database
► Forms
show one record at a time and can
be used to add, delete, or modify database
records.





Navigation
Accuracy
Consistency
Filtering
Subforms
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
28
Figure 6.15 Combined Data Entry Form for the
COURSE and PROJECT Tables
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
29
Using the Database (Cont’d)
► Reports
are aggregated data from the
database that are formatted in a manner
that aids decision making.
► Queries is a request for the database to
display selected records.
► Query-by-example (QBE) presents a
standardized form that the user completes
so the system can generate a true query.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
30
Figure 6.16 Report of Departments Showing
Courses Offered and Course Projects
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
31
Structured Query Language
► Structured
query language (SQL) is the
code that RDBMSs use to perform their
database tasks.
 Method of choice for interacting with Webbased databases.
 Writing SQL statements are not difficult for
most manager’s data needs.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
32
Figure 6.20 Structured Query Language Code
to Find Projects for the MIS105 Course
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
33
Advanced Database Processing
► On-line
analytical processing (OLAP) allows
data analysis similar to statistical cross-tabulation.
► Data mining, data marts, and data warehousing
focus on methodologies that offer users quick
access to aggregated data specific to their
decision-making needs.
► Knowledge discovery analyzes data usage and
data commonality among different tables.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
34
Database Personnel
► Database
Administrator (DBA) is an
expert in developing, providing, and
securing databases; duties include:




Database
Database
Database
Database
© 2007 by Prentice Hall
planning;
implementation;
operation;
security.
Management Information Systems, 10/e
Raymond McLeod and George Schell
35
Database Personnel (Cont’d)
► Database
programmer writes code to
strip and/or aggregate data from the
database
 High level of specialization and selection
► End
user generates reports and forms, post
queries to the database, and use results
from their database inquiries to make
decisions that affect the firm and its
environmental constituents.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
36
DBMSs in Perspective
► DBMS




Advantages
Reduce data redundancy.
Achieve data independence.
Retrieve data and information rapidly.
Improve security.
► DBMS
Disadvantages
 Obtain expensive software.
 Obtain a large hardware configuration.
 Hire and maintain a DBA staff.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
37