Functions of a Database
Management System
Functions of a DBMS
C.J. Date
Backup/Recovery
Indexing
Views
Design
Security
Documentation
Integrity
Update/Query
Concurrency
Views
Views Permit
Maintaining a constant user interface
Restricting access to specified
attributes
Specifying user rights
Views
3 Schema Architecture
User Views (Views or
LOGICAL
Queries)
Database administrators
CONCEPTUAL model for the data (E-R
diagrams)
Actual data placement and
PHYSICAL
structure (SQL statements)
Security
Components that limit access or
actions to limit potential damage
to data.
Security
Limit data access to properly authorized
users or programs. Protect data against
accidental or intentional damage.
• Deter
• Detect
• Minimize
• Recover
• Investigate
Security Approaches
Views limit access and actions
Authorization Rules identify users and
restrict actions
User Defined Procedures in addition
to database security functions
Encryption encode stored data
Authentication positively identify users
Authorization Rules
Subject Object Action Constraint
Sales Dept Cust Insert Credit < $5000
Program Ar4 Order Modify None
Terminal 12 Cust Modify Balance Due
Order Trans Cust Read None
Authorization Rules
Some DBMS products authorize actions
based on specific records and functional
descriptions. However, most DBMS’s limit
actions on tables to one of:
• Read: view but not change
• Insert: read and add records
• Update: read, insert and change records
• Alter/Delete: read, insert, update and
delete records, change table structure
User Defined Procedures
Code modules that enforce security
procedures are run during
processing
User DBMS
Procedures Constraints DBMS
Integrity
Components that preserve the
relationship among different
related records in the database
Integrity
The relationship among records in the
database
Referential Integrity
Non Key Integrity
Derived Conditions
Constraints in SQL
CREATE TABLE … or
ALTER TABLE … ADD
CHECK(condition)
PRIMARY KEY attribute-name
FOREIGN KEY attribute-name
REFERENCES parent-table
The parent table must already have a primary key defined
Concurrency
Preventing two users from
interfering with each other when
they use the same information
Concurrency
Lockout
Restricting access to users who could be
misled by partial transactions
Versioning
Making trial updates on versions of the
database and denying one if there is a data
conflict.
Locks
Master
Program 1 locks record
Student Grade
<exclusive>.
00 Fred No other program can
01 Anthony read the record.
No program can have
02 Steve an active lock.
03 Ivan Program 2 locks record
<shared>
Other programs can
read, but not change
record.
No program can have
an exclusive lock.
Locks
On INSERT or UPDATE statements
SELECT column-names
FROM table-names
WHERE …
FOR UPDATE OF column-names
NOWAIT;
Concurrency
Locks
Exclusivity
Granularity
• Exclusive
Field
• Shared
Record
• Table
• Database
Concurrency
Deadlock
Two programs request conflicting sets of data
lock up the database while awaiting access.
• Program 1 locks record A
• Program 2 locks record B
• Program 1 requests lock on record B; waits
• Program 2 requests lock on record A; waits
System either times out and restarts each
transaction after a random wait or recognizes
the deadlock to abort one program.
Versioning
Version 1
Time 1 Version 2
Version 3
Time 2
Time 3
Commits version 3 only after changes to versions 1
and 2 have been rolled back.
Backup and Recovery
Processes to confirm and repeat
transactions so that database
can be restored to a valid state
after a problem.
Backup and Recovery
Backup Copies
• Master
• Transaction Log
Journalization
• Forward Log
• Backward Log
Checkpoints
DBMS Logs
Master Transaction
Student Grade Insert Li with grade A
00 Fred Change Fred’s grade to A
01 Anthony
02 Steve
03 Ivan
Recover from Backup
Transac-
Backup + tion = Recovered
Database
Slow
May give different answers from original
DBMS Logs
Transaction Forward Log
Ins Li with grade A Student Grade
Chg Fred’s grade to A 03 Li A
00 Fred A
Master Backward Log
Student Grade Student Grade
00 Fred A 03 n/p
01 Anthony 00 Fred
02 Steve
03 Li A
DBMS Logs
Transaction Forward Log
Ins Li with grade A Student Grade
Chg Fred’s grade to A 03 Li A
10:00 Checkpoint 00 Fred A
Chkpt
Master Backward Log
Student Grade Student Grade
00 Fred A 03 n/p
01 Anthony 00 Fred
02 Steve
Chkpt
03 Li A
DBMS Logs
Transaction Forward Log
Ins Li with grade A Student Grade
Chg Fred’s grade to A 03 Li A
10:00 Checkpoint 00 Fred A
Chkpt
Chg Steve grade to B
02 Steve B
Master Backward Log
Student Grade Student Grade
00 Fred A 03 n/p
01 Anthony 00 Fred
02 Steve B Chkpt
03 Li A 02 Steve
Recover to Checkpoint
Using Logs
Backward
Contaminated
Database
- Log = Correct at
Checkpoint
Recent
+ Transactions = Recovered
Database
Transaction Processing
A set of computer operations required to
process a single unit of work.
A transaction must conclude with the
database in a valid state whether the
transaction terminates correctly or
abnormally
Transaction Processing
Transaction Boundary
• Locking
Exclusive Shared
• Logging
Forward Backward Transaction
• Modification
Delete Insert Update
• Commitment
Commit Rollback
Transaction Boundaries
Set Boundary
• Obtain Locks
• Execute Code Modules
• Evaluate Correctness
Commit or Rollback
• Release Locks
Transaction Boundaries
Set savepoint:
SAVEPOINT order_save;
Commit or rollback:
ROLLBACK TO order_save;
Transaction Boundaries
Premiere Products Example
SALESREP CUSTOMER
ORDER
PRODUCT ORDER-PRODUCT
Place an order for a new customer
with a 1500 credit limit
Transaction Boundaries
Premiere Products Example
SALESREP CUSTOMER
ORDER
PRODUCT ORDER-PRODUCT
• Insert CUSTOMER Record
• Update CUSTOMER with SALESREP Foreign Key
• Insert ORDER Record
• Insert ORDER-PRODUCT with Foreign Keys
• Update ProductOnHand in PRODUCT
• Check Credit Limit
Transaction Processing
Programming Logic
Two phased locking requires obtaining
locks on all necessary records before
releasing locks on any records.
Obtain locks on all records needed
Perform calculations
Release locks
Functions of a DBMS
C.J. Date
Backup/Recovery
Indexing
Views
Design
Security
Documentation
Integrity
Update/Query
Concurrency
