Management Information Systems By Effy Oz

1
Management Information SystemsBy Effy Oz Andy
Jones
Chapter 6 Databases and Data Warehouses
www.cengage.co.uk/oz
2
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

3
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

4
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

5
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

6
The Traditional File Approach (continued)
7
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

8
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

9
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

10
The Database Approach (continued)
11
The Database Approach (continued)
12
The Database Approach (continued)
13
Database Models

• Database model general logical structure
• How records stored in database
• Records linked differently in different models
• Models constantly changing

14
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

15
Relational Model (continued)
16
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

17
The Relational Model (continued)
18
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

19
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

20
The Object-Oriented Model (continued)
21
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

22
Structured Query Language

• Structured query language language of choice for
  DBMSs
• Advantages
• Standardised language
• Used in many host languages
• Portable

23
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

24
The Schema and Metadata (continued)

• Metadata data about data
• Source of data
• Tables related to data
• Field information
• Usage of data
• Population rules

25
The Schema and Metadata (continued)
26
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

27
Data Modeling (continued)

• Entity relationship diagram
• Boxes identify entities
• Lines indicate relationship
• Crossbars indicate mandatory fields
• Circles indicate optional
• Crows feet identify many

28
Data Modeling (continued)
29
Data Modeling (continued)
30
Databases on the Web

• Web dependent on databases
• Interface between Web and database required
• CGI
• ASP
• API

31
Databases on the Web (continued)
32
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

33
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

34
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

35
Phases in Building a Data Warehouse (continued)
36
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

37
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

38
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