Transcript MBA 664 Database Management Dave Salisbury (email)

MBA 664
Database Management
Dave Salisbury
[email protected] (email)
http://www.davesalisbury.com/ (web site)
SDLC and the database
development process
SDLC
Project ID
Project init
Database Deliverable
Enterprise model
Conceptual Model
Analysis
Logical design
Physical design
Logical model
Implementation
Maintenance
Working system
Data structures and storage plan
Management
Data as a Resource
Proper delivery of information not
only depends on the capabilities
of the computer hardware and
software but also on the
organization’s ability to manage
data as an important resource
How Does IS Manage Data?
• Data Administrators: A high-level function that is
responsible for the overall management of data
resources in an organization, including maintaining
corporate-wide definitions and standards.
• Database Administrators: A technical function that
is responsible for physical database design and for
dealing with technical issues such as security
enforcement, database performance, and backup
and recovery.
• Data Stewardship: Manages a specific logical data
resource for all business functions. Distribute data
admin. to those most knowledgeable about specific
data
Data Administration Functions
•
•
•
•
•
Data policies, procedures, standards
Planning
Data conflict (ownership) resolution
Internal marketing of DA concepts
Managing the data repository
Database Administration
Functions
• Selection of hardware and software
• Managing data security, privacy, and
integrity
• Data backup and recovery
• Figure 12.1 offers a list of DA and DBA
functions
Threats to Data Security
• Accidental losses attributable to:
– Human error.
– Software failure.
– Hardware failure.
• Theft and fraud.
• Improper data access:
– Loss of privacy (personal data).
– Loss of confidentiality (corporate data).
• Loss of data integrity.
• Loss of availability (through, e.g. sabotage).
Data security threat sources
Database Security Features
• Protection of the database against
accidental or intentional loss,
destruction or misuse
– Views
– Authorization rules
– User-defined procedures
– Encryption procedures
– Authentication schemes
Database Security Features
• Views
– Restrict user access to data
– Various ways to get around so not
sufficient measure
• Authorization Rules
– Controls embedded in DBMS that restrict
user access to data and user actions that
can be enacted on data
• Who can update? Insert? Read?
Authorization matrix
Database Security Features
• User-Defined Procedures
– Allows system designers to add other security
features
• Passwords
• Valid procedure name
• Encryption
– Coding of data so that it cannot be read by
humans
• Financial and military data
• WWW issues
• Government ability to decode all encryption schemes
Database Security Features
• Authentication Schemes
– How to positively identify that person trying
to gain access to a computer resource is
“that” person
• Biometric devices--measure fingerprints, voice
prints, retina prints
• Smart card would have biometric data
embedded
Database Failures
• Aborted Transactions
– A transaction is not completed
• Incorrect Data
– data entry error, calculation error, coding
error
• System Failure
– Component failure, power failure
• Database Destruction
– drive failure, disaster recovery
Database Recovery and Basic
Recovery Facilities
• Backup facilities
• Periodic backup copies of entire DB
• Journalizing facilities
• Maintain an audit trail of transactions and DB changes
• Checkpoint facilities
• DBMS suspends all processing and synchronizes files
and journals
• Recovery manager
• Allows the DBMS to restore the DB to correct condition
and restart
Recovery and Restart
Procedures
• Restore/Rerun
– Reprocess the day’s transactions up to the
point of failure against a backup copy of
the database
– Simple
– Time to reprocess may be prohibitive
– Sequencing of transactions may be
different than when originally run
• withdrawal posted prior to deposit
Recovery and Restart
Procedures
• Transaction Integrity
– Transaction changes are not made to the
DB until the entire transaction has been
completed and the changes are committed
– If transaction fails at any point, the
transaction is aborted
Recovery and Restart
Procedures
• Backward Recovery (Rollback)
– Back out of unwanted changes to the database
– Used to reverse the changes that have been
made to transactions that have been aborted
• Forward Recovery (Rollforward)
– Use an earlier copy of the DB and apply after
images of good transactions
– More accurate and faster than restore/rerun
Basic recovery techniques
(a) Rollback
(b) Rollforward
Concurrency Control
• Concerns with preventing loss of data
integrity due to interference between users in
a multi-user environment
– Pessimistic approach: interference will always
occur so we LOCK records
– Optimistic approach: interference will rarely occur
so we VERSION records
• Multiple concurrent updates to a database
can lead to lost updates and therefore to
errors
Lost Update Example
Time
John
Read account Balance
(balance = $1,000)
.
.
.
Withdraw $200
(balance = $800
.
.
.
Write account balance
(balance = $800)
Marsha
Read account balance
(balance = $1,000)
.
.
.
Withdraw $300
(balance = $700)
.
.
.
Write account balance
(balance = $700)
ERROR
Locking
• Deny access of data to other users
while an update is underway
• Locking level (granularity)
– Database - during backups
– Table - during batch updates
– Block or page - generally not used
– Record - Often used
– Field - Useful when only one field is likely
to change
Types of Locks
• Shared
– Allows others to read, but not write
– Prevents others from putting Exclusive lock
on the record
• Exclusive
– Denies other access to the record (even
read)
– Necessary when updating the record
Deadlock
(aka: Deadly Embrace)
• Two or more transactions have placed
locks on record(s) that the others need.
• Each waits for the other(s) to release
• Requires DBMS intervention
– Prevention, often not practical
– Resolution, common solution
• Detects deadlock and backs one or more
transactions out, lets one finish, then restarts
next transaction.
Versioning
• Each transaction is restricted to a view of the
database as of the transaction start time.
• When transaction modifies a record, the
DBMS creates a new version of record
instead of overwriting old record
• Changes to 2 identical views simultaneously
– First change (according to time stamp) is enacted
– Second change is informed of conflict and
transaction must be performed again
Versioning
John
Read account Balance
(balance = $1,000)
.
.
.
Withdraw $200
(balance = $800
.
.
.
Commit
Marsha
Read account balance
(balance = $1,000)
.
.
.
Attempt to withdraw $300
(Denied - balance update
conflict)
.
.
Rollback
Restart transaction
Managing Data Quality
•
•
•
•
Security policy and disaster recovery
Personnel controls
Physical access controls
Maintenance controls (hardware &
software)
• Data protection and privacy