Transcript Data warehouse

Unit 5
Organizing Data and Information
Learning Outcomes
 Understand records in a database
 Understand components of a DBMS
 Identify database models and types
 Define
 Database concepts
 Structures & record keeping
 Data languages
 Development tools
 Security and admin
Principles of Information Systems, Eighth Edition
2
Data Management
 Without data and the ability to process it, an
organization could not successfully complete most
business activities
 Data consists of raw facts
 To transform data into useful information, it must first
be organized in a meaningful way
Principles of Information Systems, Eighth Edition
3
The Hierarchy of Data
 Character: a group of 8bits forming a byte
 Can be an uppercase letter, lowercase letter, numeric
digit, or special symbol
 Field: group of characters, typically a name, number,
or combination of characters that describes an aspect
of a business object or activity
 Record: collection of related data fields
 File: collection of related records
 Database: collection of integrated and related files
Principles of Information Systems, Eighth Edition
4
The Hierarchy of Data (continued)
Figure 5.1: The Hierarchy of Data
5
Definitions
 Database management system (DBMS): group of
tools that support database management and it’d
performance in an organisation
 Data independence: data is independent from the
program that creates it. This means programs and data
developed in for 1 app are compatible with programs
and data from another app.
 Data redundancy: duplication of data in separate
files – can lead to inconsistency.
Principles of Information Systems, Eighth Edition
6
Database Approach
Principles of Information Systems, Eighth Edition
7
Keys*
 Key: field or set of fields in a record that is used to
identify the record
 Primary key: field or set of fields that uniquely
identifies the record
 Foreign Key: A non-primary key attribute in the table
that is a primary key in the other table.
Principles of Information Systems, Eighth Edition
8
Data Views
 Physical View: Where is data stored physically?
 Drive, disk, surface, track, sector, Record
 Tape, block, record number (key)
 Used by programmers
 Logical View: What data is needed? How do users need
the data to be arranged?
 Organize factors into tables for application use
 Provide a way for tables to cross-reference
*
Principles of Information Systems, Eighth Edition
9
Accessing File Structures
 Sequential file structure e.g. payroll
 Data is stored and accessed in a sequence according to a key
field
 Good for periodic processing of a large volume of data, but
updating with new transactions can be troublesome
 Direct file structure e.g. reservations
 Data stored randomly and accessed directly using an index of
keys and related storage addresses.
 Indexed Sequential file structure
 Records stored sequentially on direct access medium (e.g.
hdd) with address for each record.
 Index keeps track of addresses for speed. Sequential access
for large amounts of data
O’BRIEN
10
Record Processing
 Online vrs Batch
Principles of Information Systems, Eighth Edition
11
DBMS Components
1.
2.
3.
4.
5.
6.
Data dictionary: Stores definitions of data elements, and data
characteristics e.g. data type, amount of storage: txt 30
Data Languages:

Data definition language: Specifies content and structure of
database and defines each data element e.g keys, relationship

Data manipulation language: allows users to access and
change data and produce reports. E.g. SQL, QBE
Application Development Tools: Programs to develop apps to
use the database, e.g. CASE tools
Software Security: protect database from hacking &
unauthorised access.
Archiving, backup & recovery systems: in case database is
lost/crashes, it can be recovered from backups or archives.
Report Writers: tools to specify a report format for retrieving data
Principles of Information Systems, Eighth Edition
12
Sample data dictionary report
Principles of Information Systems, Eighth Edition
13
Structured Query Language (SQL)
Examples of SQL Commands
Principles of Information Systems, Eighth Edition
14
Database Models
1. Hierarchical
2. Network
3. Relational
4. Multidimensional
5. Object-oriented
6. Object-relational
Principles of Information Systems, Eighth Edition
15
Hierarchical Database Model
 May have more than one offspring, but only one parent
 Treelike
 One-to-many relationship
 Used for structured, routine types of transaction processing
Laudon
16
Network database model
 More complex
 Many-to-many relationship
 More flexible but doesn’t support ad hoc requests well
Principles of Information Systems, Eighth Edition
17
Relational Database Model
 Data elements stored in simple tables
 Can link data elements from various tables
 Very supportive of ad hoc requests but slower at
processing large amounts of data than hierarchical or
network models
Principles of Information Systems, Eighth Edition
18
The Relational Database Model
Principles of Information Systems, Eighth Edition
19
The Relational Database Model
(continued)
Principles of Information Systems, Eighth Edition
20
The Relational Database Model
(continued)
Principles of Information Systems, Eighth Edition
21
Multidimensional DB model
 A variation of the
relational model
 Cubes of data and
cubes within cubes
 Popular for online
analytical
processing (OLAP)
applications
Principles of Information Systems, Eighth Edition
22
Object-oriented DB Model
 Data descriptions &





procedures that act on data
are grouped together.
Key technology of
multimedia web-based
applications
Good for complex, highvolume applications
Can store more data types
Objects can be reused
Faster data accessed
Principles of Information Systems, Eighth Edition
23
Object-Relational DB Model
 Combines features of object-oriented and relational
databases
 Standard relational database with facilities for new
data types (e.g. video) and operations to be added.
Principles of Information Systems, Eighth Edition
24
Types of Databases
1. Operational DB – to support business
operations
2. Analytical BD – Summarized data 4 analysis
3. Data Warehouse*
4. Distributed - data may be spread across several
smaller databases in different locations .
5. End-user –developed by managers and other
users on their own PCs
6. External/on-line – pay for access to external
statistical online databases
Principles of Information Systems, Eighth Edition
25
Data Warehouses, Data Marts, and
Data Mining
 Data warehouse: Logical collection of data gathered
from many operational DBs to support business and
decision making.
 Data mart: subset of a data warehouse
 Data mining: automated discovery of patterns, trends
and rules from data in data warehouse to predict
business strategy.
Principles of Information Systems, Eighth Edition
26
Data Warehouses, Data Marts, and
Data Mining (continued)
Figure 5.17: Elements of a Data Warehouse
Principles of Information Systems, Eighth Edition
27
Advantages of DBMSs
 Data sharing
 Reduced data redundancy (no duplicates)
 Improved data consistency (more integrity)
 Data independence (data independent of the
application using it.)
 Improved data admin & control – better overall
protection
 More emphasis on data as a resource
Principles of Information Systems, Eighth Edition
28
Problems with DBMSs
 Concurrency problem – data used at same time - lock
 Resource problems – costly initially
 Security problems - cos data all in one place
Principles of Information Systems, Eighth Edition
29