Transcript kroenke_umis8e_inppt05x

Chapter 5
Database Processing
“No, Drew, You Don’t Know Anything About
Creating Queries."
• Sales database used to determine which parts to consider for 3D
printing.
• If Addison and Drew depend on Lucas (or IT dept) to produce reports,
they will (a) wait a long time, and (b) probably not get what they want.
• Once they get data, Addison creates queries and reports.
• Relying on own skills gives more freedom and better results.
• Relational database technology not appropriate for 3D parts files or
graphics used for query-by-graphics. MongoDB a better choice.
Copyright © 2016 Pearson Education, Inc.
5-2
Study Questions
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What is a database management 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: 2025?
Copyright © 2016 Pearson Education, Inc.
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 - use a database
• What's a theme?
– Ex: Student Grades
Copyright © 2016 Pearson Education, Inc.
5-4
A List of Student Grades Presented in a
Spreadsheet – Single Theme
Copyright © 2016 Pearson Education, Inc.
5-5
Student Data Form from a Database
Copyright © 2016 Pearson Education, Inc.
5-6
Q2: What Is a Database?
Copyright © 2016 Pearson Education, Inc.
5-7
Hierarchy of Data Elements
Copyright © 2016 Pearson Education, Inc.
5-8
Components of a Database
Copyright © 2016 Pearson Education, Inc.
5-9
Example of
Relationships
Among Rows
Copyright © 2016 Pearson Education, Inc.
5-10
Sample Metadata (in Access 2003)
Copyright © 2016 Pearson Education, Inc.
5-11
Ethics Guide: Querying Inequality?
• MaryAnn has a data mart.
• Business professional majored in HR, now "expert"
in SQL
• Uses SQL to do her job faster and better
• What are your personal, professional, social
responsibilities?
Copyright © 2016 Pearson Education, Inc.
5-12
Ethics Guide: Querying Inequality? (cont’d)
• Query databases to learn all sorts of patterns and trends.
– Be sure what answers you want before starting query.
• How strongly do you feel about social and personal
responsibility, considering your needs and those of your
family?
• How important is social responsibility posture of an employer
to you? Is that something you want to add to your criteria for
finding a job?
Copyright © 2016 Pearson Education, Inc.
5-13
So What? Not What the Data Says . . .
• Perception based on small sample
– “I called four different sales reps, and they said they
can’t get any prospects to bite.”
• Fact: “Not what the data says."
• Key skill - isolating facts from our perceptions.
– Look objectively at facts and not just perceptions of
facts
Copyright © 2016 Pearson Education, Inc.
5-14
Q3: What Is a Database Management System
(DBMS)?
• Program used to create, process, and administer a
database
• Licensed from vendors such as IBM, Microsoft, Oracle,
and others
– DB2, Access and SQL Server, Oracle Database
– MySQL - open source, license-free for most
applications
Copyright © 2016 Pearson Education, Inc.
5-15
Processing the Database
Four DBMS operations
1. Read
2. Insert
3. Modify
4. Delete data
Copyright © 2016 Pearson Education, Inc.
5-16
Processing the Database
• Structured Query Language - SQL (see-quell)
– International standard
– Used by most popular DBMS
INSERT INTO Student
([Student Number], [Student Name], HW1, HW2,
MidTerm)
VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100);
Copyright © 2016 Pearson Education, Inc.
5-17
Adding a New Column to a Table (in Access 2013)
Copyright © 2016 Pearson Education, Inc.
5-18
Administering the Database
• Set up security system involving user accounts,
passwords, permissions, and limits for processing
• Limit user permissions in very specific ways
• Backing up database data, adding structures to
improve performance of database applications,
removing unwanted data
Copyright © 2016 Pearson Education, Inc.
5-19
Summary of Database Administration Tasks
Copyright © 2016 Pearson Education, Inc.
5-20
Summary of Database Administration Tasks
(cont'd)
Copyright © 2016 Pearson Education, Inc.
5-21
Q4: How Do Database Applications Make
Databases More Useful?
Forms
View data; insert new, update existing, and
delete existing data.
Queries
Search based upon data values provided by
the user.
Reports
Structured presentation of data using sorting,
grouping, Filtering, and other operations.
Application programs
Provide security, data consistency, and
special purpose processing, e.g., handle
out-of-stock situations.
Copyright © 2016 Pearson Education, Inc.
5-22
Q4: How Do Database Applications Make
Databases More Useful? (cont'd)
Copyright © 2016 Pearson Education, Inc.
5-23
Example of a Student Report
Copyright © 2016 Pearson Education, Inc.
5-24
Query Example
Copyright © 2016 Pearson Education, Inc.
5-25
Browser Forms, Reports, Queries, and
Applications
Copyright © 2016 Pearson Education, Inc.
5-26
Account Creation Browser Form
Copyright © 2016 Pearson Education, Inc.
5-27
Browser
Report
Copyright © 2016 Pearson Education, Inc.
5-28
Dynamic Content Report: Clicked to Produce
Other Reports or Take Other Actions
Copyright © 2016 Pearson Education, Inc.
5-29
Multi-User Processing Problem
3. Andrea puts both in
shopping baskets,
but delays checking
out.
1. Andrea reads pedal
record showing
balance of 2 pedals.
1
2
3
2. Jeffrey reads
same record
showing balance
of 2 pedals.
5. Andrea proceeds to
checkout. Someone
will be disappointed.
4
5
4. Jeffrey puts both in
shopping basket
and checks out
before Andrea.
Copyright © 2016 Pearson Education, Inc.
5-30
Q5: How Are Data Models Used for Database
Development?
Copyright © 2016 Pearson Education, Inc.
5-31
Components of the Entity-Relationship Data Model
Entities
• Something users want to track
• Order, customer, salesperson, item,
volunteer, donation
Attributes
• Describe characteristics of an entity
• OrderNumber, CustomerNumber,
VolunteerName, PhoneNumber
Identifier
• Uniquely identifies one entity
instance from other instances
• Student_ID_Number
Copyright © 2016 Pearson Education, Inc.
5-32
Student Data Model Entities
Copyright © 2016 Pearson Education, Inc.
5-33
Example of Department, Adviser, and Student
Entities and Relationships (cont'd)
Copyright © 2016 Pearson Education, Inc.
5-34
Sample of Relationships―Version 1
Crow’s
Feet
1:N
One department may
have many advisers,
but an adviser may be
in only one department
N:M
An Adviser may have
many students, and
one student may have
many advisers
Copyright © 2016 Pearson Education, Inc.
5-35
Sample of Relationships─Version 2
“Crow’s
Foot”
N:M
A department has many
advisors, and an
advisor may advise for
more than one depart
1:N
A student has only
one advisor, but an
adviser may advise
many students
Copyright © 2016 Pearson Education, Inc.
5-36
Crow’s-Foot Diagram Version
Maximum cardinality─maximum number of entities involved in a
relationship. Vertical bar on a line means that at least one entity
required.
Minimum cardinality—minimum number of entities in a
relationship. Small oval means entity is optional; relationship
need not have an entity of that type.
Copyright © 2016 Pearson Education, Inc.
5-37
Q6: How Is a Data Model Transformed into a
Database Design?
• Normalization
– Converting poorly structured tables into two or
more well-structured tables
• Goal
– Construct tables with data about a single theme
or entity
• Purpose
– To minimize data integrity problems
Copyright © 2016 Pearson Education, Inc.
5-38
Data Integrity Problems
• Data integrity problems
– Produce incorrect and inconsistent information
– Users lose confidence in information
– System gets a poor reputation
• Can only occur if data are duplicated
Copyright © 2016 Pearson Education, Inc.
5-39
Poorly Designed Employee Table Causes Data
Integrity Problem
Copyright © 2016 Pearson Education, Inc.
5-40
Two Normalized Tables
Single
Themes
Copyright © 2016 Pearson Education, Inc.
5-41
Summary of Normalization
Copyright © 2016 Pearson Education, Inc.
5-42
Representing 1:N Relationships
Copyright © 2016 Pearson Education, Inc.
5-43
Representing an N:M Relationship: Strategy for
Foreign Keys
Copyright © 2016 Pearson Education, Inc.
5-44
Q7: What Is the Users’ Role in the Development of
Databases?
• Users are the final judges
• Users review of data model is crucial
– Ensure it accurately reflects users’ view of the
business
– Entities must contain all of the data you and your
employees need to do your jobs, and relationships
must accurately reflect your view of the business
 Mistakes will come back to haunt them
Copyright © 2016 Pearson Education, Inc.
5-45
Q8: 2025?
• Volume of database data continues to grow,
probably exponentially
• Cheap, unlimited storage space and greater
processing speeds do in relational databases
• Relational model not needed, not natural fit with
business documents
• Store new types of data stored as images,
audios, and videos
Copyright © 2016 Pearson Education, Inc.
5-46
Q8: 2025? (cont'd)
• More reliance on open source community
• Many NoSQL databases
• New opportunities and career paths develop
around NoSQL databases
Copyright © 2016 Pearson Education, Inc.
5-47
Q8: 2025? (cont'd)
NoSQL DBMS (Not Relational DBMS)
– Supports very high transaction rates
– Relatively simple data structures
– Replicated on many servers in the cloud
• Examples
– Dynamo (Amazon)
– Bigtable (Google)
– Cassandra (Facebook)
Copyright © 2016 Pearson Education, Inc.
5-48
Q8: 2025? (cont'd)
• Will NoSQL Replace Relational DBMS Products?
– Conversion very expensive and disruptive
– Very technical, limited to those with a deep
background in computer science
– Requires years of training to use
– Organization choose NoSQL products for
specialized applications
Copyright © 2016 Pearson Education, Inc.
5-49
Security Guide: Theft by SQL Injection
• One common way of stealing data from corporations
• Done by people outside of host nation to avoid prosecution
• Many of these groups are highly organized and well-funded
– Criminal syndicates, nation states, or political hacktivists
– Mandiant report “APT1: Exposing One of China’s Cyber
Espionage Units”
• Attempting SQL injection on a Web site could result in your
arrest
Copyright © 2016 Pearson Education, Inc.
5-50
Security Guide:
Theft by SQL
Injection (cont'd)
Copyright © 2016 Pearson Education, Inc.
5-51
Guide: Immanuel Kant, Data Modeler
• Perception of reality based on our perceptive
apparatus
• Brain processes perceptions into something
meaningful
• Human’s model what appears to be
• Users determine if a data model fits their
perception of reality
Copyright © 2016 Pearson Education, Inc.
5-52
Active Review
Q1: What is the purpose of a database?
Q2: What is a database?
Q3: What is a database management 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: 2025?
Copyright © 2016 Pearson Education, Inc.
5-53
Case Study 5: Searching for Pianos . . .
• Certified piano tuner and
technician repairing and restoring
pianos for many years
• Clown entertainer at children’s
parties
Copyright © 2016 Pearson Education, Inc.
5-54
Pianos in Storage
Copyright © 2016 Pearson Education, Inc.
5-55
Columns in
the Piano
Table
Copyright © 2016 Pearson Education, Inc.
5-56
Query Design and Result
Copyright © 2016 Pearson Education, Inc.
5-57
Piano Sound
Quality by Building
Copyright © 2016 Pearson Education, Inc.
5-58
Copyright © 2016 Pearson Education, Inc.
5-59