Transcript Using MIS 6e Chapter 5

Chapter 5
Database Processing
GearUp: “YOU Don’t Know Anything
About Report Writing”
• GearUp needs operating data to analyze for
cost-cutting decisions
• Need to extract and combine data from
multiple systems
• Will use Access to create reports
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-2
Study Questions
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What are the components of a database application
system?
Q4: How do database applications make databases more
useful?
Q5: How are data models used for database development?
Q6: How is a data model transformed into a database design?
Q7: What is the users’ role in the development of databases?
Q8: 2023?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-3
Q1: What Is the Purpose of a Database?
• Organize and keep track of things
• Keep track of multiple themes
• General rule:
 Single theme store in a spreadsheet
 Multiple themes require a database
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-4
A List of Student Grades, Presented in a
Spreadsheet
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-5
Student Data Shown in a Form, from a
Database
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-6
Q2: What Is a Database?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-7
Components of a Database
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-8
What Are Relationships Among Rows?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-9
Sample Metadata (in Access)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-10
Ethics Guide: Nobody Said I Shouldn’t
1. Chris made copy of backup database, took it
home
2. Queried sysTables to find table and field
names
3. Found data on orders, customers, salespeople
4. Discovered peculiar sales discounts
5. Mentioned it to Jason (sales clerk)
6. Chris fired next business day
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-11
Q3: What Are the Components of a
Database Application System?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-12
Adding a New Column to a Table (in
Access)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-13
Processing the Database
Four DBMS operations
1. Read
2. Insert
3. Modify
4. Delete
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-14
Structured Query Language (SQL)
• SQL (see-quell)
– International standard
– Used by most popular DBMS
• SQL statement:
– INSERT INTO Student
([Student Number], [Student Name], HW1,
HW2, MidTerm)
– VALUES
(1000, ’Franklin, Benjamin’, 90, 95, 100);
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-15
Summary of Database Administration
Tasks
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-16
Using MIS InClass 5: How Much Is a
Database Worth?
• Data has resale value
• Data on everything customers do
• Use to target customer for offerings they
care about, avoid those they don’t
• Costly and difficult to replace data collected
over many years
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-17
Q4: How Do Database Applications
Make Databases More Useful?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-18
What Are Forms, Reports, and Queries?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-19
Sample Query Form Used to Enter
Phrase for Search and Result
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-20
Why Are Database Application Programs
Needed?
• Process logic specific for a business need
• Enable processing via Internet
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-21
Multi-User Processing Problem
1. Process A reads
customer record
from file
containing
customer’s
account balance.
1
3. Process A
updates account
balance in its
copy of customer
record and writes
record to file.
2
2. Process B reads
same record
from same file,
now has its own
copy.
3
5. Process B writes
stale account
balance value to file,
causing changes
made by Process A to
be lost.
4
5
4. Process B has original stale
value for account balance.
Updates customer’s phone
number and writes
customer record to file.
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-22
Enterprise DBMS vs. Personal DBMS
• Enterprise DBMS process large organizational and
workgroup databases for 100s/1000s of users
• Personal DBMS designed for smaller databases
used by 1 to 100 users.
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-23
Q5: How Are Data Models Used for
Database Development?
Database Development Process
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-24
What Is the Entity-Relationship (E-R)
Data Model?
• Tool for constructing data models
• Describes contents of data model by
defining entities and relationships among
entities
• Unified Modeling Language (UML), less
popular, tool for data modeling
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-25
Student Data Model Entities
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-26
Example of Department, Adviser, and
Student Entities and Relationships
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-27
Sample E-R Diagrams
Version 1
Crow’s
Feet
1:N
N:M
Version 2
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-28
Crow’s-Foot Diagram Version
Minimum and Maximum Cardinality
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-29
Q6: How Is a Data Model Transformed
into a Database Design?
Poorly
structured
Employee
table
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-30
Normalizing for Data Integrity
Two
normalized
tables
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-31
Summary of Normalization
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-32
Steps for Transforming Data Model Into
a Database Design
1. Construct Adviser table and Student tables
with key fields
2. Represent relationships by adding foreign
keys
3. Create new table for N:M relationships
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-33
Transforming a Data Model into a
Database Design: 1:N Relationship
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-34
Representing an N:M Relationship
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-35
Q7: What Is the Users’ Role in the
Development of Databases?
• Define what data database must contain
• User review of data model is crucial
– Does model accurately reflect your
view of the business?
• Final approval of data models
• Devote time to do it right
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-36
Q8: 2023?
• Volume of database data to grow exponentially
• Relational databases will look much different
• Use of non-relational data stores, called NoSQL will
be more common
• Major DBMS vendors lose out to open-source
products and shift focus to services supporting open
source software like Bigtable, Dynamo, Cassandra?
• Database models will be very different
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-37
Guide: No, Thanks, I’ll Use a
Spreadsheet
• Databases take time to build
• Complicated to operate
• Need IS people to create and keep them
running
• Salesman doesn’t want to share data
• Spreadsheets sometimes better option,
especially if data needs are simple
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-38
Guide: Immanuel Kant, Data Modeler
• Perception of reality is based on our
perceptive apparatus.
• Brain processes perceptions into something
meaningful.
• Human’s model what appears to be.
• Users must determine if a data model fits
their perception of reality.
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-39
Active Review
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What are the components of a database application
system?
Q4: How do database applications make databases more
useful?
Q5: How are data models used for database development?
Q6: How is a data model transformed into a database design?
Q7: What is the users’ role in the development of databases?
Q8: 2023?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-40
Case Study 5: Fail Away with Dynamo,
Bigtable, and Cassandra
• Current relational DBMS products not designed for
large, multi-server systems
• NoSQL databases – Dynamo, Bigtable, Cassandra
• Amazon: Dynamo
• Google: Bigtable processes petabytes of data on
hundreds of thousands of servers
• Both created to be elastic
• Cassandra used by Facebook, Twitter, Digg, Reddit
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-41
5-42