Transcript Chapter 7

Management Information Systems,
Sixth Edition
Chapter 7:
Databases and Data Warehouses
Objectives
• Explain the difference between traditional file
organization 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
Management Information Systems, Sixth Edition
2
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
Management Information Systems, Sixth Edition
3
Shine in 10 (Activity 7)
• Define IYOW any of the following that is assigned to you:
1. Database approach vs Traditional file approach
2. Hierarchy of Data (Show the hierarchy and examples)
3. Primary, Composite and Foreign keys
4. SQL (what it does and how it works)
5. ERD (how it is used – be specific)
6. Data Warehouse
• This is worth 20pts
4
Man
Managing Digital Data
• Businesses collect and dissect data for many
purposes
• Data can be stored in database format
– Easy access and manipulation
• Databases have had a profound impact on
business
– An information industry has been created
• Database technology integrated with the Internet
has contributed to commerce significantly
Management Information Systems, Sixth Edition
5
The Traditional File Approach
• Traditional file approach: no mechanism for
tagging, retrieving, or manipulating data
• Database approach: provides powerful
mechanism for managing and manipulating data
• Traditional approach is inconvenient:
– Program-data dependency
– High data redundancy
– Low data integrity
• Data redundancy: duplication of data
• Data integrity: accuracy of data
Management Information Systems, Sixth Edition
6
The Traditional File Approach
(continued)
Management Information Systems, Sixth Edition
7
The Database Approach
• Database approach: data organized as entities
• Entity: an object about which an organization
chooses to collect data, such as:
– People
– Events
– Products
• Character: smallest piece of data
– A single letter or a digit
• Field: single piece of information about entity
Management Information Systems, Sixth Edition
8
The Database Approach (continued)
• Record: collection of related fields
• File: collection of related records
• Database fields can hold images, sounds, video
clips, etc.
• Field name allows easy access to the data
• Database management system (DBMS):
program used to:
– Build databases
– Populate a database with data
– Manipulate data in a database
Management Information Systems, Sixth Edition
9
Management Information Systems, Sixth Edition
10
Management Information Systems, Sixth Edition
11
The Database Approach (continued)
• Query: a message to the database requesting
data from specific records and/or fields
• Database must be properly secured
– Not everyone should have access to all data
– Users will have different views of the database,
based on the data they are allowed to see
Management Information Systems, Sixth Edition
12
The Database Approach (continued)
Visual Query from MS Access
Management Information Systems, Sixth Edition
13
The Database Approach (continued)
• Database administrator (DBA): the person
responsible for managing the database
– Sets user limits for access to data in the database
• DBMS is usually bundled with a programming
language
Management Information Systems, Sixth Edition
14
Management Information Systems, Sixth Edition
15
Management Information Systems, Sixth Edition
16
Database Models
• Database model: general logical structure
– How records stored in the database
– How relationships between records are
established
• Database models differ in:
– How records are linked to each other
– How users can navigate the database, retrieve
records, and create records
Management Information Systems, Sixth Edition
17
The Relational Model
• Relational Model: consists of tables
• Based on relational algebra
– Tuple: record (or row)
– Attribute: field (or column)
– Relation: table of records
• To design a relational database, you must
understand the entities to be stored in the
database and how they relate
• Tables are independent of each other, but can
be related to each other
Management Information Systems, Sixth Edition
18
The Relational Model (continued)
• Key: a field whose values identify records
– Used to retrieve records
• Primary key: a field by which records are
uniquely identified
– Each record in the table must have a unique key
value
• Composite key: combination of fields that serve
as a primary key
Management Information Systems, Sixth Edition
19
Management Information Systems, Sixth Edition
20
The Relational Model (continued)
Management Information Systems, Sixth Edition
21
The Relational Model (continued)
• Foreign key: a field that is common to two tables
– Used to link the tables
– This field is a primary key in one table and a
foreign key in the other
• Join table: composite of tables
• Two types of table relationships:
– One-to-many relationship: one item in a table is
linked to many items in the other table
– Many-to-many relationship: many items in a
table are linked to many items of the other table
Management Information Systems, Sixth Edition
22
The Relational Model (continued)
Primary Key
Foreign Key
Related One-to-Many Table in MS Access
Management Information Systems, Sixth Edition
23
The Relational Model (continued)
Management Information Systems, Sixth Edition
24
The Object-Oriented Model
• Object-oriented database model: uses objectoriented approach for the database structure
• Encapsulation: combined storage of data and
relevant procedures to process it
– Allows object to be “planted” in different data sets
• Inheritance: the ability to create a new object by
replicating the characteristics of an existing
(parent) object
• Object-oriented databases (ODBs) store data
objects, not records
Management Information Systems, Sixth Edition
25
Management Information Systems, Sixth Edition
26
Relational Operations
• Relational operation: creates a temporary
subset of a table or tables
• Used to create a limited list or a joined table list
• Three important relational operations:
– Select: a selection of records based on conditions
– Project: a selection of certain columns from a
table
– Join: join data from multiple tables to create a
temporary table
Management Information Systems, Sixth Edition
27
Structured Query Language
• Structured Query Language (SQL): query
language of choice for DBMSs
• Advantages of SQL:
– It is an international standard
– It is provided with most relational DBMSs
– It has easy-to-remember, intuitive commands
Management Information Systems, Sixth Edition
28
Structured Query Language
SQL in MS Access
Management Information Systems, Sixth Edition
29
Structured Query Language
Management Information Systems, Sixth Edition
30
The Schema and Metadata
• Schema: a plan that describes the structure of
the database, including:
– Names and sizes of fields
– Identification of primary keys
– Relationships
• Data dictionary: a repository of information
about the data and its organization
– Also called metadata: the data about the data
Management Information Systems, Sixth Edition
31
The Schema and Metadata (continued)
• Metadata includes:
–
–
–
–
–
Source of the data
Tables related to the data
Field and index information
Programs and processes that use the data
Population rules: what is inserted, or updated,
and how often
Management Information Systems, Sixth Edition
32
Management Information Systems, Sixth Edition
33
Data Modeling
• Databases must be carefully planned and
designed to meet business goals
• Data modeling: analysis of an organization’s
data and identification of the data relationships
– A proactive process
– Develops a conceptual blueprint of the database
• Entity relationship diagram: a graphical
representation of all entity relationships
Management Information Systems, Sixth Edition
34
Building Blocks of ERD
Type
English Grammar Equivalent
Example
Entity
Proper Noun
Student, Employee,
Instructor, Courses,
Room
Relationship
Verb
has, teaches,
belongs, handles
Attribute
Adjective
Height, Age, Gender,
Nationality, First
name
35
ERD Popular Notation
• Chen Notation
• Crow’s Foot Notation
36
Chen Notation - Symbol
Rectangle represents an Entity
Diamond represents a Relationship
1
M
37
Lines with labels represents Cardinality
Entity (Chen Notation)
• is a real-world object distinguishable or unique from
other objects.
• An entity can be a concrete or physical object like
employee, student, faculty, customer etc. Or it could also
be conceptual or abstract like transaction, order,
course, subjects etc.
• It can be thought of as a noun like student, employee
etc.
• It is normally represented by a rectangle shape.
38
Database Background
– Entity could be a :
(ex. Teacher, Student, Physician)
Person
Place
Object
(ex. Mouse, Books, Bulding )
Event
39
(ex. School, Hotel, Store )
Idea or Concept
(ex. Enroll, Withdraw, Order )
(ex. Courses, Account, Delivery )
Entity - Example
Customer
Sales Rep
Order
Parts
40
Relationship
• is a way of relating one entity to another. Entities can
therefore participate in a relationship.
• it is commonly thought as a verb connecting the entities
or nouns.
• It is normally represented by a diamond shape.
41
Relationship - Example
Sales Rep
represents
Customer
has
Could be read as : A Sales Rep
Represents a Customer. And a
Customer has an Order.
42
Order
Cardinality
• Cardinality: number of items that must be
included in a relationship
– An entity in a relationship with minimum
cardinality of zero plays an optional role in the
relationship
– An entity with a minimum cardinality of one plays
a mandatory role in the relationship
43
Cardinality - Symbols
1
M
One-is-to-many Relationship
M
N
Many-to-many Relationship
44
Cardinality Symbols - Example
1
Sales Rep
M
represents
Customer
Could be read as : A Sales Rep could represent
1 or Many Customers.
45
Cardinality Symbols – Example
(Cont’d)
M
Order
N
has
Parts
Could be read as : An Order could have many Parts (e.g. Products
Ordered) and a Part could have many Orders.
46
Degree of Relationship
• There are three Degree of Relationships in ERD
notation, namely:
– Unary
– Binary
– Ternary
47
Degree of Relationship (Cont’d)
Unary
Binary
Ternary
48
Degree of Relationship (Cont’d)
Manages
Unary
Employee
makes
Customer
Orders
Vendor
Warehouse
supplies
Part
49
Binary
Ternary
Attribute
• Refers to the characteristic or basic fact or field of an
Entity or Relationship.
• For example a Student entity could have the following
attributes ID Number, Last Name, First Name, Address,
Birth Date etc.
• A relationship could also have an attribute for example
an Entity name Student enrolls (relationship) to a
Course/Program. Now, when you enroll you enroll on a
certain date so you will have an attribute of Enrollment
Date under Enroll relationship.
• It is normally represented by an oval.
50
Attribute - Example
Firstname
Lastname
RepNum
Street
City
Sales Rep
State
Zip
51
Rate
Commission
Take note that
a Primary Key
is underlined.
Attribute – More Example
CustomerName
Firstname
CustomerNum
Lastname
RepNum
Street
Street
1
City
Sales Rep
M
represents
Customer
State
State
Zip
Rate
CreditLimit
Zip
Commission
Balance
52
City
Crow’s Foot notation - Symbol
Entity name
Attribute 1
Attribute 2
Attribute 3
Attribute 4
53
Crow’s Foot notation - Example
Entity
Student
StudentID
Firstname
Lastname
Gender
Program
54
Attributes
Crow’s Foot notation - Keys
Student
StudentID (PK)
Firstname
Lastname
Gender
ProgramID (FK)
55
PK – Primary Key
FK – Foreign Key
Crow’s Foot Cardinality - Symbols
One and only one included in the relationship
Zero or many could be included in the relationship.
This is optional mode.
One or many could be included in the relationship.
This is mandatory mode.
56
Crow’s Foot notation – with Cardinality
Customer
Rep
Repnum (PK)
Firstname
Lastname
Commission
Rate
57
Customernum(PK)
Customername
Street
City
State
Zip
Balance
CreditLimit
Repnum (FK)
Mini-Project 2 – ERD
•Create an ERD for a small Store or Mini-store (like the stores at the back of COM).
The store of course store Information about their products, supplier, the sale that they made
each day but not of their Customer.
•This is worth 100 points but the scoring is based on Group Output (50 points) and Individual
participation (50 points)
Group Criteria :
ERD reflects real world entities
- 40%
Relationships are well defined
- 30%
Attributes are complete and well defined
- 30%
------------------------------------------------------------TOTAL
100%
Individual Criteria :
Joins meaningful discussion
- 20%
Has a substantive and meaningful assignment
- 30%
Execute well his/her given assignment
- 30%
Does not do unrelated stuff
- 20%
-----------------------------------------------------------TOTAL
100%
Management Information Systems, Sixth Edition
58
Mini-Project No. 3 - Database
•Creating Mini-Store Database
•Create a Database for the Mini-store based on your ERD that you have just created
•Create tables that reflects an entity, namely, People, Event or Products
•Create a relationship among tables
•You could use MS Access or MySQL Database Management Systems on this one.
• This is worth 100 points but the scoring is based on Group Output (50 points) and Individual
participation (50 points)
Group Criteria :
Tables reflects real world entities
- 40%
Table Relationships are right defined
- 40%
Complete sets of tables are defined
- 20%
------------------------------------------------------------TOTAL
100%
Individual Criteria :
Joins meaningful discussion
- 20%
Has a substantive and meaningful assignment
- 30%
Execute well his/her given assignment
- 30%
Does not do unrelated stuff
- 20%
-----------------------------------------------------------TOTAL
100%
Management Information Systems, Sixth Edition
59
Databases on the Web
• Web is dependent on databases
– Organizations must link their databases to the
Web
• Interface between Web and database required
• Interface may be programmed in one of several
Web programming languages, including:
–
–
–
–
Java servlets
Active server pages (ASP)
PHP (Hypertext Preprocessor)
Web application program interfaces (APIs)
Management Information Systems, Sixth Edition
60
Databases on the Web (continued)
Management Information Systems, Sixth Edition
61
Data Warehousing
• Most data collections are used for transactions
• Accumulation of transaction data is useful
• Data warehouse: a large repository database
that supports management decision making
– Typically relational
– Data is collected from transactional databases
• Data mart: a smaller collection of data focusing
on a particular subject or department
Management Information Systems, Sixth Edition
62
From Database to Data Warehouse
• Transactional databases are not suitable for
business analysis
– Contain only current, not historical data
• Data warehouse requires large storage capacity:
– Mainframe computers are often used
– Scalability is an issue
– Data warehouses grow continually
Management Information Systems, Sixth Edition
63
Phases in Data Warehousing
• Three phases in transferring data from a
transactional database to a data warehouse:
– Extraction phase: create files from transactional
database
– Transformation phase: cleanse and modify the
data format
– Loading phase: transfer files to data warehouse
• A properly built data warehouse becomes a single
source for all data required for analysis
• Data mining and online analytical processing
(OLAP) use data in data warehouses
Management Information Systems, Sixth Edition
64
Management Information Systems, Sixth Edition
65
VB.net Database Demo
Management Information Systems, Sixth Edition
66
MS Access Demo
Management Information Systems, Sixth Edition
67
Summary
• Organizations collect vast amounts of data
• Database approach has several advantages
over traditional file approach
• Character: smallest piece of data
• Field: made up of multiple characters
• Record: collection of related fields
• File: collection of related records
• Database management system (DBMS): tool to
construct databases
Management Information Systems, Sixth Edition
68
Summary (continued)
• Relational and object-oriented database models
have different advantages
• Keys are used to form links among entities
• Primary keys are unique identifiers
• Object-oriented database maintains objects that
contain data and procedures that process it
• Structured Query Language (SQL) is an
international standard for querying databases
• Database designer must construct a schema to
construct a database
Management Information Systems, Sixth Edition
69
Summary (continued)
• Database designers conduct data modeling and
create entity relationship diagrams to plan
databases
• Many databases are linked to Web
• Data warehouses contain huge collections of
historical transaction data
• Data warehouse requires data extraction,
transformation, and loading of transactional data
• Invasion of privacy is exacerbated by database
technology
Management Information Systems, Sixth Edition
70