Transcript Using MIS 6e Chapter 5

Chapter 5
Database Processing
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-2
A List of Student Grades, Presented in a
Spreadsheet
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-3
Student Data Shown in a Form, from a
Database
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-4
Q2: What Is a Database?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-5
Components of a Database
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-6
What Are Relationships Among Rows?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-7
Sample Metadata (in Access)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-8
Q3: What Are the Components of a
Database Application System?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-9
Processing the Database
Four DBMS operations
1. Read
2. Insert
3. Modify
4. Delete
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-10
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-11
Summary of Database Administration
Tasks
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-12
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-13
Q4: How Do Database Applications
Make Databases More Useful?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-14
What Are Forms, Reports, and Queries?
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-15
Sample Query Form Used to Enter
Phrase for Search and Result
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-16
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-17
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-18
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-19
Q5: How Are Data Models Used for
Database Development?
Database Development Process
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-20
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-21
Student Data Model Entities
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-22
Example of Department, Adviser, and
Student Entities and Relationships
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-23
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-24
Crow’s-Foot Diagram Version
Minimum and Maximum Cardinality
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-25
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-26
Normalizing for Data Integrity
Two
normalized
tables
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-27
Summary of Normalization
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-28
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-29
Transforming a Data Model into a
Database Design: 1:N Relationship
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-30
Representing an N:M Relationship
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
5-31