Transcript Database Principles & Design By Colin Ritchie

Management Information
Systems
By Effy Oz & Andy Jones
Chapter 6: Databases and Data
Warehouses
www.cengage.co.uk/oz
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Objectives
• Explain the difference between traditional file
organisation and the database approach to managing
digital data
• Explain how relational and object oriented database
management systems are used to construct databases,
populate them with data, and manipulate the data to
produce information
• Enumerate the most important features and operations of
a relational database, the most popular database model
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Objectives (continued)
• Understand how data modeling and design
creates a conceptual blueprint of a database
• Discuss how databases are used on the Web
• List the operations involved in transferring
data from transactional databases to data
warehouses
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Managing Digital Data
• Businesses collect and dissect data
• Data can be stored in powerful database format
– Easy access and manipulation
• Databases have profound impact on business
• Database technology integrated with Internet
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Traditional File Approach
• Traditional file approach: no mechanism for
manipulating data
• Database approach: has mechanism for manipulating
data
• Traditional approach inconvenient
– High data redundancy
– Low data integrity
• Data redundancy: duplication of data
• Data integrity: accuracy of data
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Traditional File Approach
(continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Database Approach
• Database approach: data organised as entities
• Entity: object that has data
– People
– Events
– Products
• Character: smallest piece of data
• Field: single piece of information about entity
• Record: collection of fields
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Database Approach (continued)
• File: collection of related records
• Database management system (DBMS):
program used to build databases
– Populates with data
– Manipulates data
• Query: message requesting access to data
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Database Approach (continued)
• Database has security issues
• Database administrator (DBA): limits user
access to database
– Requires users to enter codes
• DBMS bundled with fourth-generation
languages
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Database Approach (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Database Approach (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Database Approach (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Database Models
• Database model: general logical structure
– How records stored in database
– Records linked differently in different models
– Models constantly changing
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Relational Model
• Relational Model: consists of tables
• Based on relational algebra
–
–
–
–
Tuple: record
Attribute: field
Relation: table
Key: identifier field
• Used to retrieve records
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Relational Model (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Relational Model (continued)
• Primary key: unique key
– Uniquely identifies record
– Required in table
• Composite key: combination of fields
– Serves as primary key
• Foreign key: shared field
– Links tables
• Join table: composite of tables
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Relational Model (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Relational Model (continued)
• Table relationships with other tables
• One-to-many relationship: one item in table
linked to many items in other table
• Many-to-many relationship: many items in table
linked to many items of other table
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Object-Oriented Model
• Object-Oriented model: uses object-oriented
approach
• Encapsulation: combined storage of data and relevant
procedures
– Allows object to be planted in different data
sets
• Inheritance: creates new object by replicating
characteristics of existing (parent) object
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Object-Oriented Model
(continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Relational Operations
• Relational operation: create temporary subset of
table
• Create limited list or joined table list
– Select records based on conditions
– Project columns
– Join tables to create temporary table
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Structured Query Language
• Structured query language: language of
choice for DBMSs
• Advantages
– Standardised language
– Used in many host languages
– Portable
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Schema and Metadata
• Schema: plan
– Describes structure of database
– Names and sizes of fields
– Identifies primary keys
• Data dictionary: repository of information about
data
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Schema and Metadata
(continued)
• Metadata: data about data
–
–
–
–
–
Source of data
Tables related to data
Field information
Usage of data
Population rules
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
The Schema and Metadata
(continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Data Modeling
• Databases must be carefully planned
• Data modeling: analysis and organisation of
data
– Proactive process
– Develop conceptual blueprint
• Entity relationship diagram: graphical
representation of relationships
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Data Modeling (continued)
• Entity relationship diagram
–
–
–
–
–
Boxes identify entities
Lines indicate relationship
Crossbars indicate mandatory fields
Circles indicate optional
Crows feet identify “many”
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Data Modeling (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Data Modeling (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Databases on the Web
• Web dependent on databases
• Interface between Web and database required
– CGI
– ASP
– API
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Databases on the Web (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Data Warehousing
• Data collections used for transactions
• Accumulation of transaction data useful
• Data warehouse: large database
– Typically relational
– Supports decision making
– Data copied from transactional database
• Data mart: collection of data focusing on
particular subject
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
From Database to Data Warehouse
• Transactional database not suitable for business
analysis
– Only current data
– Not historic
• Data warehouse requires large storage capacity
– Mainframe computers used
– Scalability issue
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Phases in Building a Data
Warehouse
• Begin building data warehouse after equipment
secured
– Extraction phase
• Create files from transactional database
– Transformation phase
• Cleanse and modify data
• Loading phase
• Transfer files to data warehouse
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Phases in Building a Data
Warehouse (continued)
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Summary
• organisations collect vast amounts of data
• Database approach has advantages over
traditional approach
• Character: smallest piece of data
• File: collection of records
• Designer must construct schema to construct
database
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Summary (continued)
• Database management system enables database
construction and manipulation
• Relational and object-oriented database models
have different advantages
• Keys used to form links among entities
• Object-oriented database maintains links
differently
• SQL adopted as international standard
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning
Summary (continued)
• Designers conduct data modeling to show required
tables
• Databases often linked to Web
• Data warehouses contain huge collections of historical
data
• Data warehouse allows data extraction, transformation,
and loading
• Invasion of privacy is exacerbated by database
technology
Use with Management Information Systems 1e
By Effy Oz & Andy Jones ISBN 9781844807581
© 2008 Cengage Learning