Transcript info-Chapter7

Chapter 7
Storing Organizational
Information - Databases
McGraw-Hill/Irwin
© 2008 The McGraw-Hill Companies, All Rights Reserved
Learning Outcomes
1. Define the fundamental concepts of the relational
database model
2. Evaluate the advantages of the relational database
model
3. Define a database management system and its
relationship to a website
4. Explain why an organization would want to integrate its
databases
7-2
Relational Database
Fundamentals
• Information is everywhere in an organization
• Information is stored in databases
– Database (DB)– maintains information about
various types of objects (inventory), events
(transactions), people (employees), and places
(warehouses)
7-3
What is the purpose of a database?
• Spreadsheet and database keep track of
things
• Spreadsheets
– Keep lists of single concept
• Databases
– Keep lists that involve multiple themes
7-4
Relational Database
Fundamentals
• Database models include:
– Hierarchical database model – information is
organized into a tree-like structure (using
parent/child relationships)
– Network database model – a flexible way of
representing objects and their relationships; each
record may have multiple parents
– Relational database model – stores information
in the form of logically related two-dimensional
tables
7-5
Hierarchical database model
A method for
storing data in a
database that
looks like a family
tree with one root
and a number of
branches or
subdivisions.
7-6
Network database model
A database design for storing information by linking all records that
are related with a list of pointers.
7-7
Relational database model
A design used in database
systems in which
relationships are created
between one or more tables
based on the idea that each
pair of tables has a field in
common.
7-8
Tables
TABLES
7-9
In the rest of this course we only focus on
Relational Databases since they are far
more applicable to businesses.
7-10
Entities and Attributes
• Entity – a person, place, thing, transaction, or event about
which information is stored
– A table in relation DB is a collection of similar entities.
– Rows in each table contain entity instances.
– In Figure 7.1 CUSTOMER includes Dave’s Sub Shop
and Pizza Palace entities
• Attributes (fields, columns) – characteristics or
properties of an entity class
– The columns in each table contain the attributes
– In Figure 7.1 attributes for CUSTOMER include
Customer ID, Customer Name, Contact Name
7-11
Entities
Attributes
Keys
andand
Relationships
Customer
instance
Order Attribute
Fig. 7.1
7-12
Keys and Relationships
• Primary keys and foreign keys identify the
various entity classes (tables) in the
database
– Primary key – a field (or group of fields) that
uniquely identifies a given entity in a table
– Foreign key – a primary key of one table that
appears an attribute in another table (foreign
key) and acts to provide a logical relationship
among the two tables
7-13
Keys
Entities
andand
Relationships
Attributes
Keys
and
Relationships
Primary
Key
Foreign Key
7-14
Another example of Relationships among
tables
7-15
Keys and Relationships
• Potential relational database for Coca-Cola
7-16
Why Relational models
• What is wrong with putting all information
in the previous slide all in ONE table?
7-17
Relational Database Advantages
• Database advantages from a business
perspective include
– Increased flexibility
– Increased scalability and performance
– Reduced information redundancy
– Increased information integrity (quality)
– Increased information security
7-18
Increased Flexibility
• A well-designed database should:
– Handle changes quickly and easily
– Provide users with different views
• The physical view: of information deals with the
physical storage of information on a storage
device such as a hard disk.
• The logical view: of information focuses on how
users logically access information to meet their
particular business needs.
7-19
Physical vs. logical views
This separation of logical and physical
views is what allows each user to access
database information differently. That is,
while a database has only one physical
view, it can easily support multiple logical
views.
7-20
Example
• Example—a mail-order business. One
user might want a CRM report presented
in alphabetical format, in which case last
name should appear before first name.
Another user, working with a catalog
mailing system, would want customer
names appearing as first name and then
last name. Both are easily achievable, but
different logical views of the same
physical information.
7-21
Increased Scalability and Performance
• A database must scale to meet increased
demand, while maintaining acceptable
performance levels
– Scalability – refers to how well a system can
adapt to increased demands
– Performance – measures how quickly a
system performs a certain process or
transaction
7-22
Increased Scalability and Performance
• Some organizations must be able to
support hundreds or thousands of online
users including employees, partners,
customers, and suppliers, who all want to
access and share information.
7-23
Reduced Information Redundancy
• Databases reduce information redundancy
– Redundancy – the duplication of information or
storing the same information in multiple places
• Inconsistency is one of the primary problems
with redundant information
– Updating data without updating identical ones
7-24
Increase Information Integrity (Quality)
• Information integrity – measures the quality
of information
• Integrity constraint – rules that help ensure
the quality of information
– There are two types of integrity constraints:
• (1) relational integrity constraints and
• (2) business-critical integrity constraints.
7-25
Types of integrity constraints
• Relational integrity constraints are rules that
enforce basic and fundamental information-based
constraints.
– Example, an integrity constraint would not allow
someone to create an order for a nonexistent customer
• Business-critical integrity constraints enforce
business rules vital to an organization's success
– Example: no product returns are accepted after 15 days
past delivery.
7-26
Increased Information Security
• Information is an organizational asset and must be
protected
• Databases offer several security features
including:
– Password – provides authentication of the user
– Access level – determines who has access to the
different types of information
– Access control – determines types of user access,
such as read-only access
7-27
Database Management Systems
• Database management systems (DBMS) –
software through which users and application
programs interact with a database
7-28
DATA-DRIVEN WEB SITES
• Data-driven Web sites – an interactive Web site
kept constantly updated and relevant to the needs of
its customers through the use of a database –
example: Amazon, ebay, http://wwwb.autotrader.ca/
7-29
Data-Driven Business Intelligence
• Companies can gain business
intelligence by viewing the data
accessed and analyzed from their
website. The next figure displays how
running queries or using analytical tools,
such as a Pivot Table, on the database
that is attached to the website can offer
insight into the business, such as items
browsed, frequent requests, items bought
together, etc.
7-30
7-30
Data-Driven Business Intelligence
7-31
7-31
Integrating Information
among Multiple Databases
• Integration – allows separate systems to
communicate directly with each other
– Forward integration – takes information
entered into a given system and sends it
automatically to all downstream systems and
processes
– Backward integration – takes information
entered into a given system and sends it
automatically to all upstream systems and
processes
7-32
Integrating Information
among Multiple Databases
• Forward & Backward Integration
7-33
Integrating Information
among Multiple Databases
• Building a central repository specifically
for integrated information
7-34