Transcript Chapter 5

Chapter 5
Database Processing
This Could Happen to You: “The Database
Shows . . .”
Neil uses software to query a database, but it has
about 25 standard queries that don’t give him all
he needs.
He imports data from database into Excel where
he moves, sorts, sums and averages data until he
gets information he wants.
It’s a pain, but it works.
FlexTime video clip
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-2
Study Questions
Q1
What is the purpose of a database?
Q2
What does a database contain?
Q3
What is a DBMS, and what does it do?
Q4
What is a database application?
Q5
What is the difference between an enterprise DBMS
and a personal DBMS?
How does the knowledge in this chapter help FlexTime and
you?
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-3
Q1: What Is the Purpose of a Database?
Purpose: To organize and keep track of things
Spreadsheets
•Keep track of only a single theme
Example: Student test scores in a course
Databases
•Allow keeping track of multiple themes
Examples: Student grades, office visits, student email
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-4
A List of Student Grades, Presented in a Spreadsheet
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-5
Student Data Shown in a Form from a
Database
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-6
Q2: What Does a Database Contain?
Table or file:
A group of records
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-7
Hierarchy of Data Elements
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-8
Components of a Database
Metadata: Describes structure of database (tables,
relationships, primary key, etc.) and its data such as data
type (text, number, date), size, default, format, etc.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-9
Relationships Among Records
Database has multiple tables (one
for each theme)
Values in one table may relate to
records in other tables
Relational
database
Primary or
Unique key
Field(s) that uniquely
identify a record in a table
Each table must have a key
Foreign key
A non-primary key in one table
that is related to a primary key
of a different table
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-10
Special Terms
Key
• A column or group of columns that
identifies a unique row in a table
• Student Number is key of Student table
• Every table must have a key
• Sometimes more than one column is
needed to form a unique identifier
Example: key of City table, would be a
combination of City and State columns.
• Email_Num is key of Email Table
• VisitID is key of Office_Visit Table
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
1-11
More Special Terms
Relational
databases
Relation
•Relationships among tables are
created by using foreign keys
•Formal term for a table
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
1-12
Relationships Among Records in Three Tables
Figure 5-6
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-13
Metadata
Database: Self-describing collection of integrated records
Metadata
Describes data by:
•Data that describes data
•Makes databases more
useful
•Makes databases easier
to use
•Data type: text, number,
date, etc.
•Field name
•Field properties
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-14
Sample Metadata in Access
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-15
Q3: What Is a DBMS and What Does It Do?
Database Management System (DBMS):
― Program that creates, processes, and administers
databases
― Examples: DB2, Microsoft Access, SQL Server, Oracle,
MySQL (open-source DBMS)
Database
Collection of
tables,
relationships,
and
metadata
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-16
Components of a Database Application System
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-17
Creating the Database and Its Structure:
Adding “Response?” Field to a Table
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-18
Processing the Database: SQL
Four DBMS
operations
• Read, Insert, Modify, and Delete
data
Structured Query
Language (SQL)
• International standard language
for processing databases
Example SQL
statement
INSERT INTO Student
([Student Number], [Student Name], HW1, HW2,
MidTerm)
VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100)
Assignment: View this in your Access assignment as
you create a query
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-19
Administering the Database
DBMS security features to set up user
accounts, passwords, permissions,
processing limits
Permissions—setting data access rights
for specific users or groups of users. We
typically do not do it in Access database,
but in network database like SQL Server
Database backup and replication, adding
structures, removing unneeded data
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-20
Q4: What Is a Database Application?
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-21
Forms, Reports, and Queries
Forms
• Used to read, insert, modify,
and delete data
Reports
• Show data in structured context
• May compute values such as
Totals, within a report
Queries
• Are a means of getting answers
from database data
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-22
Example of a Database Query and Query
Result
Query result
Query form
Report
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-23
Database Application Programs
Internet database
processing to:
Processing logic for
a specific business
need
Connect web
server and
database
Respond to
events
Read, insert,
modify, delete
data
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-24
Four Database Application Programs
Running on a Web Server Via Internet
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-25
Multiuser Processing Considerations
Lost-update problem
Occurs when two or more transactions select same record at same
time, then update it based on values first received. Each transaction is
independent. Last update overwrites updates previously made by
others.
10-5 = 5
5, 7 or 3?
10-3 = 7
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-26
Multi-User Processing Problem: Lost-update
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 © 2012 Pearson Education, Inc. Publishing as Prentice Hall
1-27
Preventing Lost Update Problem
Locking
• Prevents another user or process to change
a record currently being used by another
user or process.
• Gets only a Read-only copy.
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-28
Q5: What Is the Difference Between an
Enterprise DBMS and a Personal DBMS?
Enterprise DBMS
Personal DBMS
• Processes large organizational • Designed for smaller, simpler
and workgroup databases
database applications
• Supports many users
• Supports fewer than 100 users
(thousands plus)
(mostly 1–10 users)
• Examples: DB2, SQL Server,
• Examples: Access, dBase,
Oracle, MySQL
FoxPro, R-Base
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-29
Personal Database System
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-30
Personal Assignment
Create a Microsoft Access database that will contain
three to four tables such as:
•
•
•
•
Customer
Products
Orders
Billing
• Create a Form through which Customers can make
orders
• Create a Query and then a Report of recent Orders
by Customers
Copyright © 2012 Pearson Education, Inc. Publishing as Prentice Hall
5-31