Database Management

1
Database Management Fall 2003 Data
Integrity Chapter 18
2
Hand out slides!
3
Strategies for data integrity

• Protecting existence
• Preventative
• Isolation
• Remedial
• Database backup and recovery
• Maintaining quality
• Update authorization
• Integrity constraints
• Data validation
• Concurrent update control
• Ensuring confidentiality
• Data access control
• Encryption

4
Strategies for data integrity

• Legal
• Privacy laws
• Administrative
• Storing database backups in a locked vault
• Technical
• Using the DBMS to enforce referential integrity
  constraint

5
Data Integrity Issues

• Data Consistency
• Making sure the data at all times accurately
  depicts the real world
• Solution
• Transaction Processing
• Concurrancy
• Maintaining consistency when multiple people are
  making changes to the database at once
• Solutions
• Before image for read consistency
• Data locking

6
Transaction Processing

• Consider a banking application that processes
  fund transfers from one account to another.
• Steps to transfer from savings to checking
• Credit checking account
• Debit savings account
• If a system problem after the first step prevents
  the second from completing, the account
  information will be inaccurate.
• Solution encapsulate the steps in a transaction
  that can either be committed or rolled back
  (undone) as a single unit.

7
Transaction processing

• A transaction is a series of actions to be taken
  on the database such that they must be entirely
  completed or aborted
• A transaction is a logical unit of work (LUW)
• Example
• BEGIN TRANSACTION
• EXEC SQL INSERT
• EXEC SQL UPDATE
• EXEC SQL INSERT
• COMMIT TRANSACTION
• COMMIT TRANSACTION
• makes the modifications permanent
• ROLLBACK TRANSACTION
• returns database to the state at BEGIN
  TRANSACTION

8
ACID
9
Transaction Processing

• Transaction processing requires the use of a
  before image, also known as a ROLLBACK log or an
  UNDO log, and an after image, also known as a
  transaction or REDO log.
• The rollback and transaction logs are areas of
  memory or disk reserved by the database for the
  purpose of recording changes to the data

10
Rollback log or journal

• Referred to in the textbook as a before image
• Records a backup copy of all data before they are
  changed
• Used by the database to undo changes when a
  transaction is rolled back
• Can also used when another transaction executes a
  SELECT against the same data before the changes
  are committed

11
Read Consistency
12
Read Consistency

• When a transaction changes data, no other
  transactions may see the changes until they are
  commited.
• The transaction that makes the changes does see
  them before commit.
• If a COMMIT occurs while another person is doing
  a SELECT, the SELECT will not see those changes.

13
Concurrent update

• The lost data problem

Time
Action
Database record
Part
Quantity
P10
40
T1
User A receives paperwork
for a delivery of 80 units of P10
T2
User A reads P10
P10
40
T3
User B sells 20 units of P10
T4
User B reads P10
P10
40
T5
User A processes the delivery
(40 80 120)
T6
User A updates the file
P10
120
T7
User B processes the sales
(40 - 20 20)
T8
User B updates the file
P10
20
14
Locking

• Locking data prevents other users from modifying
  it
• Different levels of locking
• Table lock
• Page lock
• Row lock
• Different kinds of locks
• Shared lock
• Allows INSERT, UPDATE or DELETE of other rows
• Allows other shared locks but not exclusive locks
• Exclusive lock
• Prevents INSERT, UPDATE or DELETE
• Does not allow any other locks

15
Concurrent update

• Avoiding the lost data problem - locking

Time
Action
Database record
Part
Quantity
P10
40
T1
User A receives paperwork
for a delivery of 80 units of P10
T2
User A reads P10
P10
40
T3
User B sells 20 units of P10
T4
User B attempts to read P10
denied
P10
40
T5
User A processes the delivery
(40 80 120)
T6
User A updates the file
P10
120
T7
User B reads P10
P10
120
T8
User B processes the sales
(120 - 20 100)
T9
User B updates the file
P10
100
16
Concurrent update

• The deadly embrace
• User As update transaction locks record 1
• User Bs update transaction locks record 2
• User A attempts to read record 2 for update
• User B attempts to read record 1 for update

17
Database Backups

• Why backup databases?
• Things go wrong
• Things go really wrong with computers
• Hardware failure
• Disk crash
• Power failure
• Natural disasters
• Software data corruption
• Application defects
• Operating system failures
• User input errors

18
Database update process

• Database should be recoverable to any given state

U
p
d
a
t
e
U
p
d
a
t
e
U
p
d
a
t
e
t
r
a
n
s
a
c
t
i
o
n

A
t
r
a
n
s
a
c
t
i
o
n

B
t
r
a
n
s
a
c
t
i
o
n

C
D
a
t
a
b
a
s
e
D
a
t
a
b
a
s
e
D
a
t
a
b
a
s
e
D
a
t
a
b
a
s
e
(
s
t
a
t
e

2
)
(
s
t
a
t
e

3
)
(
s
t
a
t
e

4
)
(
s
t
a
t
e

1
)
Backup
Transaction or REDO logs are required to restore
the database to a given state
D
a
t
a
b
a
s
e
(
s
t
a
t
e

2
)
19
Backup options
20
Mirroring Disks

• Mirroring disk drives
• Two or more duplicate copies of database
• Two-way mirror protects against disk failure but
  not server failure
• Mirror can be broken to make backup copies
• Mirror is resilvered after the backup

One third of the mirror is broken to make
a backup of the database while the server is
still running
Copy
3-way mirror
21
Standby Database

• Standby database is a duplicate database server,
  preferably in a separate location
• Database software keeps the databases in sync
  (often using the transaction logs)
• In the event of a crisis, the system will fail
  over to the standby database

Standby database
Production database
Transaction logs
Denver, CO
Boston, MA
22
Transaction Logging

• The transaction log, or journal, is a file where
  the database records every data change
• If necessary, the database can automatically
  apply all the previous transactions (or roll
  forward) to any point in time
• Rolling forward requires having a saved copy of
  the database from a previous point in time

23
Database Dumps

• A snapshot of the database at a particular point
  in time
• Copies of the database files are made to another
  disk, server or to tape
• Cold backup
• Shutdown database before copying
• Faster, more reliable, but database is down
• Hot backup
• Make copies while database is running
• Slower, less consistent, but database stays up
• Requires use of transaction logs
• Incremental backup
• Just the changes since the last complete backup

24
Database Dumps
Typical backup schedule
Cold backup
Day 1
Transaction logs
Hot backup
Day 2
Transaction logs
Incremental
Day 3, etc
25
Recovery strategies

• Switch to a duplicate database
• RAID technology approach
• Backup recovery or rollback
• Return to prior state by applying before-images
• Forward recovery or rollforward
• Recreate by applying after-images to prior backup
• Rollback uncommited transactions
• Use saved before-images to rollback
• Reprocess transactions

26
Database Recovery
Recover database to its state at 1200 noon on
day 3
Copy hot backup back to database
Backward Recovery
Day 2
Forward Recovery
Rerun transactions using transaction logs
Day 3
Backups and transaction logs combine to enable
point-in-time recovery
27
Data recovery
28
Data quality

• Definition
• Data are high quality if they fit their intended
  uses in operations, decision making, and
  planning. They are fit for use if they are free
  of defects and possess desired features.
• Determined by the customer
• Relative to the task

29
Integrity constraints
30
Integrity constraints
31
Data Security

• Three levels of security
• Authentication
• Who are you?
• Authorization
• What do you have privileges to do?
• Encryption
• Keeping private data secret

32
A general model of data security
33
Authenticating mechanisms

• Information remembered by the person
• Name
• Account number
• Password
• Object possessed by the person
• Badge
• Plastic card
• Key
• Personal characteristic
• Fingerprint
• Signature
• Voiceprint
• Handsize

34
Firewall

• A device placed between an organizations network
  and the Internet
• Monitors and controls traffic between the
  Internet and Intranet
• Approaches
• Restrict packets to those with designated IP
  addresses
• Restrict access to applications

35
Authorization tables (views)

• Indicate authority of each user or group

36
SQL authorization

• Views
• Grant
• Giving object privileges to users
• Revoke
• Removing privileges
• Roles
• Assigning groups of privileges to groups of
  people
• System privileges
• Privileges to create, alter or drop objects

37
Encryption

• Encryption is as old as writing
• Sensitive information needs to remain secure
• Critical to electronic commerce
• Encryption hides the meaning of a message
• Decryption reveals the meaning of an encrypted
  message

38
Public key encryption

• Receiver has two keys public and private
• Sender uses public key to encrypt
• Receiver uses private (secret) key to decrypt

Sender
Decrypt
Receiver
Encrypt
Receivers public key
Receivers private key
39
Signing

• Message authentication
• Identifies the message sender
• Requires digital certificates issued by a
  certificate authority (CA), such as Verisign

Sender
Verify
Receiver
Sign
Senders private key
Senders public key
40
Monitoring activity

• Audit trail analysis
• Time and date stamp all transactions
• Audit trail must be kept secure
• Monitor a sequence of queries
• Tracker queries
• Transaction log mining