Chapter 12 - Introduction to Multi-User Database Processing

Multi-User Database Processing Architectures
1. Teleprocessing Systems - one computer (one CPU): multiple dumb terminals
2. Client-Server Systems - multiple compluters usually connected in a LAN where the database resides on the server and the applications are on the clients
3. File-Sharing Systems - server only contains the files, while the clients contain the applications and the DBMS (more traffic): used for nondatabase applications
4. Distributed Database Systems - database is distributed to different computers
  1. Distributed Processing vs. Distributed Database Processing
  2. Types of Distrubted Databases
    1. nonpartitioned, nonreplicated
    2. partitioned, nonreplicated
    3. nonpartitioned, replicated
    4. partitioned, replicated
  3. Comparison of Database Distribution
    1. increasing parallelism
    2. increasing independence
    3. increasing flexibility
    4. increasing availability
    5. increasing cost/complexity
    6. increasing difficulty of control
    7. increasing security risk
5. Intranet and Internet Database Processing
Database Reliability
- Concurrency control - mesures that are taken to prevent one user's work from interfering with another's
- Database recovery - methods and techniques that are used to ensure that the database can be repaired or reconstructed after failure
1. The Need for Atomic Transactions [transaction - logical units of work (LUW): series of actions to be taken on the database such that either all of them are performed successfully or none of them is perfomed at all {atomic}]
  1. Concurrent Transaction processing [2 tranactions being processed at the same time]
  2. Lost (Concurrent) Update Problem [2 transactions processing the same data at the same time]
2. Resource Locking
  1. Lock Terminology
    - implicit locks - locks placed by the DBMS
    - explicit locks - locks placed by command from the application program or query user
    - lock granularity - size of lock (records, pages, tables, database)
    - exclusive lock - locks the item from access of any type
    - shared lock - locks the item from change but not from read
  2. Serializable Transactions
    - serializable - results should be logically consistent with results had the transactions been processed in an arbitrary serial fashion
    - two-phased locking: growing phase (locks are obtained) and shrinking phases (locks are released): no data is committed until all of the object has been processed
  3. Deadlock deadly embrace
3. Database Recovery
  1. Recovery via Reprocessing: periodically make a copy of the database and keep a record of all transactions since the save; then reprocess [not viable for concurrent processing systems]
  2. Recovery via Rollback/Rollforward: periodically copy the database and keep a log of the changes made by transactions since the save; then reapply the tranactions on the saved copy

Database Security

Sample of Authorization Rules
Subject	Object	Action	Authorization Constraint
PGM OE104J	ORDER Record	Insert	Amount less than $500,000
Sally Smith	ORDER Record	Read	None
Payroll Dept	EMPLOYEE Record	Read	Hourly workers
Payroll Dept	EMPLOYEE Record	Modify	Hourly workers
Payroll Dept	EMPLOYEE Record	Insert	Hourly workers
Payroll Supv	EMPLOYEE Record	Delete	Hourly workers
Payroll Supv	Read Permission of EMPLOYEE Record	Grant	To payroll personnel

Subjects - any identifible user or user group that can process the database
Objects - database units to be protected by security
Actions - what subject can do to object
Authorization Constraints - limitations on permissions concerning subject, object, or action
Security in DBMS Products
- Subject-oriented security - each subject is defined to the DBMS and allocated permissions
- Object-oriented security - each object has an authorization matrix that shows what actions can be taken by various subjects on that object
- Constraints via programmable exits (exit to user-written programs whenever a specified action is performed)

Database Administration
1. Managing the Database Structure
  - Participate in Database and Application Development
    - assist in requirements state and evaluation of alternatives
    - play an active role in database design and creation
    - develop procedures for integrity and quality of database data
  - Facilitate Changes to Database Structure
    - seek community wide solutions
    - assess impact on all users
    - provide configuration control forum
    - be prepared for problems after changes are made
    - maintain documentation
2. DBA's Responsibilites for Managing Data Activity
  - establish database standards consistent with data administration standards
  - establish and maintain data dictionary
  - establish data proponencies
  - work with data proponents to develop data access and modification rights
  - develop, document, and train staff on backup and recovery procedures
  - publish and maintain data activity standards documentation
3. Managing the DBMS
  - generate database application performance reports
  - investigate user performance complaints
  - assess need for changes in database structure or application design
  - modify database structure
  - evaluate and implement new DBMS features
  - tune the DMBS
4. Estabishing the Database Data Dictionary