Management Information Systems, Sixth Edition

1
Management Information Systems, Sixth Edition

• Chapter 7
• Databases and Data Warehouses

2
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

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
Shine in 10 (Activity 7)

• Define IYOW any of the following that is assigned
  to you
• Database approach vs Traditional file approach
• Hierarchy of Data (Show the hierarchy and
  examples)
• Primary, Composite and Foreign keys
• SQL (what it does and how it works)
• ERD (how it is used be specific)
• Data Warehouse
• This is worth 20pts

5
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

6
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

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

9
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

10
(No Transcript)
11
(No Transcript)
12
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

13
The Database Approach (continued)
Visual Query from MS Access
14
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
15
(No Transcript)
16
(No Transcript)
17
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

18
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

19
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

20
(No Transcript)
21
The Relational Model (continued)
22
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

23
The Relational Model (continued)
Primary Key
Foreign Key
Related One-to-Many Table in MS Access
24
The Relational Model (continued)
Demo
25
The Object-Oriented Model

• Object-oriented database model uses
  object-oriented 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

26
(No Transcript)
27
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

28
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

29
Structured Query Language
SQL in MS Access
30
Structured Query Language
Demo
31
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

32
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

33
(No Transcript)
34
Data Modeling

• Databases must be carefully planned and designed
  to meet business goals
• Data modeling analysis of an organizations 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

35
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
36
ERD Popular Notation

• Chen Notation
• Crows Foot Notation

37
Chen Notation - Symbol
Rectangle represents an Entity
Diamond represents a Relationship
1
M
Lines with labels represents Cardinality
38
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.

39
Database Background

• Entity could be a

Person
(ex. Teacher, Student, Physician)
Place
(ex. School, Hotel, Store )
Object
(ex. Mouse, Books, Bulding )
Event
(ex. Enroll, Withdraw, Order )
Idea or Concept
(ex. Courses, Account, Delivery )
40
Entity - Example
Customer
Sales Rep
Order
Parts
41
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.

42
Relationship - Example
represents
Customer
Sales Rep
has
Could be read as A Sales Rep Represents a
Customer. And a Customer has an Order.
Order
43
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

44
Cardinality - Symbols
1
M
One-is-to-many Relationship
M
N
Many-to-many Relationship
45
Cardinality Symbols - Example
represents
Sales Rep
1
M
Customer
Could be read as A Sales Rep could represent 1
or Many Customers.
46
Cardinality Symbols Example (Contd)
has
Order
M
N
Parts
Could be read as An Order could have many Parts
(e.g. Products Ordered) and a Part could have
many Orders.
47
Degree of Relationship

• There are three Degree of Relationships in ERD
  notation, namely
• Unary
• Binary
• Ternary

48
Degree of Relationship (Contd)
Unary
Binary
Ternary
49
Degree of Relationship (Contd)
Manages
Unary
Employee
makes
Customer
Orders
Binary
Vendor
Warehouse
Ternary
supplies
Part
50
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.

51
Attribute - Example
Lastname
Firstname
RepNum
Street
Sales Rep
City
State
Take note that a Primary Key is underlined.
Rate
Zip
Commission
52
Attribute More Example
CustomerName
CustomerNum
Lastname
Firstname
RepNum
Street
Street
represents
1
M
City
Sales Rep
City
Customer
State
State
Rate
CreditLimit
Zip
Zip
Commission
Balance
53
Crows Foot notation - Symbol
Entity name
Attribute 1 Attribute 2 Attribute 3 Attribute 4
54
Crows Foot notation - Example
Entity
Student
StudentID Firstname Lastname Gender Program
Attributes
55
Crows Foot notation - Keys
Student
PK Primary Key
StudentID (PK) Firstname Lastname Gender ProgramID
(FK)
FK Foreign Key
56
Crows 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.
57
Crows Foot notation with Cardinality
Customer
Rep
Customernum(PK) Customername Street City State Zip
Balance CreditLimit Repnum (FK)
Repnum (PK) Firstname Lastname CommissionRate
58
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
• ------------------------------------------------
  -------------

59
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

60
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)

61
Databases on the Web (continued)
62
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

63
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

64
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

65
(No Transcript)
66
VB.net Database Demo
Demo
67
MS Access Demo
Demo
68
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

69
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

70
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