ISQA 425

1
ISQA 425

• Dr. Sergio Davalos
• Winter 2002

2
Topics

• Data and Database Administration
• Distributed Databases

3
Data and Database Administration

• Data Administration overall management of data
  resources including data dictionary and standards
• Database administration physical DB design and
  technical issues performance, security, backup,
  and recovery

4
Data administrator

• functions
• DB planning
• Analysis
• Design
• Implementation
• Maintenance
• Protection
• DB performance procedures
• Education/training/consulting

5
Distributed Databases

• Function
• DB physical design
• Involved in at DB development steps
• Carries out policies set by DA

6
Key issues addressed

• Data policies, procedures, and standards well
  communicated and documented
• Planning
• Data conflict resolution
• Internal marketing
• Managing the information repository
• Hardware and software selection
• Installing and upgrading the DBMS
• Tuning DB performance
• Improving DB query processing
• Managing data security, privacy and integrity
• DB backup and recovery

7
DBA and DA functions

• Figure 12-1

8
Key DBA considerations

• Modeling enterprise data enterprise
  architectures projects utilize it
• Planning for DBs
• What data to be included
• What data processing
• What business locations are affected
• What work units are involved
• How will the DB fit with business operations
• How much and when will data be stored
• Data security
• Accidental loss human, software
• Theft/fraud
• Loss of privacy or confidentiality
• Loss of data integrity
• Loss of availability

9
Security features of DB management

• Views or subschemas
• Integrity controls - validation/ referential
• Authorization rules
• Controls to restrict data access and actions
• Levels of passowrds
• Authorization table
• User defined security procedures
• Encryption
• Authentication schemes
• Biometric, voice, call back

10
Backing up databases

• Backup
• Journalizing
• Checkpoint systems goes into quiet state
  synchronizes DB and transactionlogs
• Recovery manager
• Transaction discrete unit of work all or none
  at all
• Transaction log
• Database change log
• Before image
• After image

11
Recovery and restart procedures

• Restore/ rerun
• Switch to mirrored copy
• Transaction integrity
• Atomic
• Consistent
• Only send committed transactions
• Backward recovery
• Forward recovery earlier point

12
Types of DB failure

• Aborted transactions
• Incorrect data
• System Failure
• DB destrcution

13
Concurrent access control

• Concurrency control managing simultaneous
  operations against a database so that data
  integrity is maintained
• Inconsistent read problem lost update
• Serializability
• Locking
• Locking level
• Types shared, exclusive
• Deadlock figut 12-10
• Deadlock prevention
• Two-phase locking protocol
• All locks acquired before any released
• Versioning each change recorded

14
Managing data quality

• Personnel controls
• Physical access
• Maintenance
• Data protection and privacy

15
Data dictionary and repositories

• Data dictionary

16
Distributed Databases

• DDB single user DB spread out across several
  locations
• Decentralized
• Homogeneous heterogeneous figure 13-1
• Figure 13-2 homogeneous
• Fig 13-3 heterogeneous
• Synchronous DDB
• Asynchronous DDB

17
Options for Distributing a DB

• Data replication
• Snapshot
• Pull replication
• Horizontal
• Vertical - figure 13-5-7
• Comparison of DDB strategies

18
Distributed DBMS

• Local transaction global transaction -
  reference to data local or global
• Location transparency
• Replication transparency
• Failure transparency
• Commit protocol
• Algorithm to ensure that a transaction
  successfully completed or aborted
• Two-phase protocol broadcasting of a data
  transaction, if all OK then commit if not abort
• Concurrency transparency only one on the system
• Timestamping timestamp for each transaction