Transcript Database Management Systems

Chapter 12: Information Systems
Information systems are software
applications that facilitate the
organization and analysis of data.
Example: Spreadsheet software allows
users to place raw data in tables and
then utilize formulas and basic
graphical mechanisms to generate
calculations and illustrations from it.
Chapter 12
Information
Systems
Page 127
Database Structures
An alternative to a distributed information system based
upon files is a centralized system based upon the concept
of databases.
File-Oriented
System
Rental
Dept.
Sales
Dept.
Purchasing
Dept.
Marketing Dept.
Database-Oriented
System
Maintenance
Dept.
Payroll
Dept.
Sales
Dept.
Purchasing
Dept.
Rental
Dept.
Video
Rental
Files
Video
Sales
Files
Video
Purchase
Files
Advertisement
Files
Store
Upkeep
Files
Store
Personnel
Files
DISADVANTAGES OF EACH SYSTEM
File-Oriented
Database-Oriented
Duplication of effort
Data security problems
Multiple error sources
Widespread error effects
Integrated
Database
Payroll
Dept.
Maintenance
Dept.
Marketing Dept.
Chapter 12
Information
Systems
Page 128
A Modular View of a Database System
APPLICATION
SOFTWARE
System of programs
specifying how to
present data to the
user
DATABASE
MANAGEMENT
SYSTEM
System of programs
controlling how data
is accessed
The DBMS uses schema
and subschema to
ensure data security.
A schema describes the
way the entire
database is organized.
A subschema describes
the organization of the
portion of the
database that is
accessible to a
particular type of user.
DATABASE
The stored data
of the system
Chapter 12
Information
Systems
Page 129
The Relational Database Model
The simplest conceptual arrangement of a database uses a table
of rows (called tuples) and columns (called attributes).
Last Name First Name M.I.
SID
Hrs Class GPA Major
DOB
Moose
Bullwinkle
J
900626977
112
Sr
3.24
CHEM
03/21/76
Bear
Yogi
D
900129875
48
So
2.17
BIOL
02/16/79
Coyote
Wile
E
900705548
54
So
3.16
MATH
11/05/81
Hound
Huckleberry
H
900339227
75
Jr
3.05
MKTG
09/12/83
Gorilla
Magilla
B
900187014
102
Sr
3.76
ELED
12/12/80
Pig
Porky
P
900882635
66
Jr
2.38
ECON
05/02/82
The major advantage of
this model is its logical
conceptualization.
The major disadvantage is the substantial
amount of software and hardware overhead
required to maintain and access the table.
Image
Chapter 12
Information
Systems
Page 130
Relational Operator SELECT
The SELECT operation determines which tuples have
particular attributes.
ORIGINAL TABLE
Code
Description
Price
213345
9v Battery
1.92
311452
Power Drill
34.99
254467
60W Bulb
1.47
309772
Mini-Ratchet Set
6.50
256568
Halogen Light
12.99
290031
Flat Screwdriver
8.45
NEW TABLE
Apply
SELECT with
Price < 10.00
Code
Description
Price
213345
9v Battery
1.92
254467
60W Bulb
1.47
309772
Mini-Ratchet Set
6.50
290031
Flat Screwdriver
8.45
Chapter 12
Information
Systems
Page 131
Relational Operator PROJECT
The PROJECT operation limits the scope of the
database to specific attributes.
ORIGINAL TABLE
NEW TABLE
Code
Description
Price
Code
Price
213345
9v Battery
1.92
213345
1.92
311452
Power Drill
34.99
311452
34.99
254467
60W Bulb
1.47
254467
1.47
309772
Mini-Ratchet Set
6.50
309772
6.50
256568
Halogen Light
12.99
256568
12.99
290031
Flat Screwdriver
8.45
290031
8.45
Apply
PROJECT with
Code & Price
Chapter 12
Information
Systems
Page 132
Relational Operator JOIN
The JOIN operation combines multiple tables that have
common attributes.
Table CUSTOMER
CusNo
CusName CusZip
Table SALESREP
RepNo
RepID
RepPhone
1132445
Walker
62449
231
125
6182439887
1321242
Rodriguez
62025
125
167
6183426778
1657399
Vanloo
62363
231
231
6182431124
1312243
Rakowski
62294
167
333
3145267759
1542311
Smithson
62025
421
1217782
Adares
62650
125
Apply JOIN with
CUSTOMER.RepNo =
SALESREP.RepID
NEW TABLE
CusNo
CusName
CusZip
RepNo
RepPhone
1132445
Walker
62449
231
6182431124
1321242
Rodriguez
62025
125
6182439887
1657399
Vanloo
62363
231
6182431124
1312243
Rakowski
62294
167
6183426778
1217782
Adares
62650
125
6182439887
Chapter 12
Information
Systems
Page 133
Concurrency Control
A potential problem with database systems that allow
multiple access points is the loss of data integrity.
At ATM #1: Deposit $400
At ATM #2: Withdraw $200
Get Balance...
$500
Add $400 to Balance... $900
Store new Balance... $900
Get Balance...
$500
Subtract $200 from Balance... $300
Store new Balance...
$300
Either balance that’s stored would be incorrect,
since the correct balance is $700!
Chapter 12
Information
Systems
Page 134
Databases and Privacy
The proliferation of information on database systems
poses a potential threat to the privacy of people about
whom the data refers.
Example: Medical Databases
Advantages:
Disadvantages:
•Reduction of paperwork
•Employer access might cost jobs
•Fewer false insurance claims
•“High risk” insurance increases
•Facilitates disease tracking
•Unsolicited advertisements
•Immediate access in emergency •Fear inhibits candid disclosure
•Cost-effective ID of treatment •Inaccuracies are spread easily
•Safer than paper records
•Dr./patient confidentiality loss
Chapter 12
Information
Systems
Page 135
Cryptography
Networks are set up to send messages right past
stations that aren’t authorized to read them, but
what’s to prevent such unauthorized viewing?
Message
The most common solution to this problem is
encryption, where the message is coded in such a
way that only the receiving station can decode it.
Chapter 12
Information
Systems
Page 136
Public-Key Encryption
I have affixed
to me the dirt
and dust of
countless ages!
1.
Create
Message
Chuck
Linus
Lucy
Patty
mdbriugndlwg
mamnsgfyddkd
qhgwdnchsgsh
ahwbsgcydhzx
2.
Look Up
Recipient’s
Public Key
xsjb2dhdkWb$
xzduYdm!dj5slL
ssghd8nd&hsnq
abi?dsjsg%
3.
Encrypt Message
With Recipient’s
Public Key
4.
Transmit Encrypted
Message
xsjb2dhdkWb$
xzduYdm!dj5slL
ssghd8nd&hsnq
abi?dsjsg%
I have affixed
to me the dirt
and dust of
countless ages!
5.
Decrypt Message With
Recipient’s Private Key
Chapter 12
Information
Systems
Page 137
Key-Based Authentication
When a message is received, how can you be sure who it came from?
I’m going to recruit that
funny-looking kid who
plays shortstop on
Chuck’s team!
Ma3ndhvyr#bcjaqwp
fQkguiorkfohskxi8vc
e%fpgkjfhikfvdamxx
yemfideychssfhsgdha
hdm$dlglyn7buchso
1.
Create Message
2.
Encrypt Message With
Sender’s Private Key
3.
Transmit Encrypted
Message
Ma3ndhvyr#bcjaqwp
fQkguiorkfohskxi8vc
e%fpgkjfhikfvdamxx
yemfideychssfhsgdh
ahdm$dlglyn7buchso
I’m going to recruit
that funny-looking kid
who plays shortstop on
Chuck’s team!
4.
Decrypt Message With
Sender’s Public Key
Chapter 12
Information
Systems
Page 138