Title: Management Information Systems By Effy Oz
1Management Information SystemsBy Effy Oz Andy
Jones
Chapter 6 Databases and Data Warehouses
www.cengage.co.uk/oz
2Objectives
- 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
3Objectives (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
4Managing 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
5The 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
6The Traditional File Approach (continued)
7The 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
8The 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
9The 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
10The Database Approach (continued)
11The Database Approach (continued)
12The Database Approach (continued)
13Database Models
- Database model general logical structure
- How records stored in database
- Records linked differently in different models
- Models constantly changing
14The Relational Model
- Relational Model consists of tables
- Based on relational algebra
- Tuple record
- Attribute field
- Relation table
- Key identifier field
- Used to retrieve records
15Relational Model (continued)
16The 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
17The Relational Model (continued)
18The 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
19The 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
20The Object-Oriented Model (continued)
21Relational 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
22Structured Query Language
- Structured query language language of choice for
DBMSs - Advantages
- Standardised language
- Used in many host languages
- Portable
23The Schema and Metadata
- Schema plan
- Describes structure of database
- Names and sizes of fields
- Identifies primary keys
- Data dictionary repository of information about
data
24The Schema and Metadata (continued)
- Metadata data about data
- Source of data
- Tables related to data
- Field information
- Usage of data
- Population rules
25The Schema and Metadata (continued)
26Data 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
27Data Modeling (continued)
- Entity relationship diagram
- Boxes identify entities
- Lines indicate relationship
- Crossbars indicate mandatory fields
- Circles indicate optional
- Crows feet identify many
28Data Modeling (continued)
29Data Modeling (continued)
30Databases on the Web
- Web dependent on databases
- Interface between Web and database required
- CGI
- ASP
- API
31Databases on the Web (continued)
32Data 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
33From 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
34Phases 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
35Phases in Building a Data Warehouse (continued)
36Summary
- 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
37Summary (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
38Summary (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