Transcript Database

Management
Information Systems,
10/e
Raymond McLeod and George 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
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 & 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 which 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 as a Simple
Database
© 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 & 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 Management System
► 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 & 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
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 Hierarchical Structure
© 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
► A 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.
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
13
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
© 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
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
18
Figure 6.7 Access View
© 2007 by Prentice Hall
Management Information Systems, 10/e
Raymond McLeod and George Schell
19
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 & 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 &
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 (Cont’d)
► Enterprise
modeling approach takes a
broad view of the firm’s data resources; all
areas are considered, & 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 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 (Cont’d)
► 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, & relationship between objects.
► Class diagrams consist of the named class,
fields in the class, & 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 1 record at a time & 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
© 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
© 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 SQL Code
© 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, & data warehousing
focus on methodologies that offer users quick
access to aggregated data specific to their
decision-making needs.
► Knowledge discovery analyzes data usage &
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 & selection
► End
user generates reports & forms, post
queries to the database, & use results from
their database inquiries to make decisions
that affect the firm & 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 & 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