10/15/2014
1. Integrity
• Integrity support
Database Systems – Consistency
Integrity, Security and – Domain constraints
Distribution – Relation constraints
– Referential integrity
– Named constraints
CSI481 Database systems 2
Consistency Domain constraints
• Need to maintain DB consistency • Specific domains can be created
• User-defined types
• General scheme for consistency rules
CREATE DOMAIN NAME_TYPE AS VARCHAR(15)
– TRIGGER: An event that causes a check to be CREATE DOMAIN NIN_TYPE AS VARCHAR(9)
made
…
– CONSTRAINT: The check that is required CREATE TABLE EMPLOYEE
– VIOLATION RESPONSE: What to do about it (FNAME NAME_TYPE NOT NULL,
NIN NIN_TYPE NOT NULL
…
CSI481 Database systems 3 CSI481 Database systems 4
Relation constraints Referential integrity
• Action clauses can be attached to any foreign key
• Can impose constraints on relations constraint
– PRIMARY KEY • Triggered by delete or update operations
– UNIQUE
– FOREIGN KEY (DNO) REFERENCES DEPARTMENT
ON {DELETE / UPDATE}
(DNUMBER)
{CASCADE / SET NULL / SET DEFAULT}
CREATE TABLE DEPARTMENT
… • CASCADE: delete referenced child tuples
PRIMARY KEY (DNUMBER) • SET NULL: set foreign key values in child tuples to NULL
UNIQUE (DNAME) • SET DEFAULT: set foreign key values in child tuples to
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN) default value
CSI481 Database systems 5 CSI481 Database systems 6
1
10/15/2014
Referential integrity Named constraints
CREATE TABLE EMPLOYEE CREATE ASSERTION SALARY_CONSTRAINT
(…
PRIMARY KEY (FSSN) CHECK (NOT EXISTS
FOREIGN KEY (DNO) (SELECT *
REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT
FROM EMPLOYEE E, EMPLOYEE M,
ON UPDATE CASCADE DEPARTMENT D
FOREIGN KEY (SUPERSSN) WHERE E.SALARY > M.SALARY
REFERENCES EMPLOYEE(FSSN)
ON DELETE SET NULL
AND E.DNO = D.NUMBER
ON UPDATE CASCADE AND D.MGRSSN = M.SSN))
CSI481 Database systems 7 CSI481 Database systems 8
2. Security SQL Security Model
Security and Authorisation • Access matrix model
• DB security and authorisation subsystem – Rows of users, accounts, programs
prevent unauthorised access – Columns of relations, records, views and
• Access control handled by DBMS through user operations
accounts, password • Cell contents indicate privileges that subject
• DBA can grant or revoke privileges to users or holds on object
groups for account creation, privilege – Privileges expressed in terms of SQL operations
granting, revocation and security level
– Ability to grant privileges is itself a privilege
assignment
CSI481 Database systems 9 CSI481 Database systems 10
SQL Security Model Examples
GRANT INSERT, DELETE
• GRANT ON EMPLOYEE, DEPARTMENT
– Add a privilege to a particular user for a particular TO A2;
object
GRANT SELECT
• REVOKE ON EMPLOYEE, DEPARTMENT
– Remove a privilege from a particular user TO A3
WITH GRANT OPTION
REVOKE SELECT
ON EMPLOYEE
FROM A3
CSI481 Database systems 11 CSI481 Database systems 12
2
10/15/2014
Distribution Two-Tier Client-Server Architecture
Distribution Client
User interface
• Centralised Database Main business logic
– Data and software are all at a single node Data processing logic
• Client-Server
– Data and DBMS software are at server Database server
Server-side validation
– Some application software may run at clients Database access
• Distributed Database
– Data spread among many machines on a network
CSI481 Database systems 13 CSI481 Database systems 14
Three-Tier Client-Server Architecture Distributed Databases
Client
User interface
Application server
Business logic
Data processing logic
Database server
Data validation
Database access
CSI481 Database systems 15 CSI481 Database systems 16
Functions of a
Advantages of distribution
Distributed Database System
• Suits distributed applications • Access remote site, transmit queries and data via
e.g. local and global users of banks communication network
• Increased reliability and availability • Keep track of data distribution and replication in
– Reliability is the probability that a system is up at a DDBMS catalogue
particular moment • Devise execution strategies for queries and
– Availability is the probability that the system is transactions on multiple sites
continuously available during the time interval • Decide which replicated copy of data to access
• Data sharing with local control • Maintain consistency of replicated copies
• Improved performance • Recover from individual crashes and new types of
– Smaller transactions and small local database failure
CSI481 Database systems 17 CSI481 Database systems 18
3
10/15/2014
Transaction Processing in
Transparency
Distributed Databases
• A Distributed DBMS aims to make distribution • Transactions are key to effective recovery from
transparent, or invisible to the user failure
• Distribution adds several new modes of failure:
• Database is perceived as a single, logical entity
– Individual message loss
• User doesn't see that the data is fragmented – Failure of a communication link
=> queries look as they would on a monolithic – Failure of a site
database – Network partitioning
• Atomicity must be preserved when related
• User doesn't know where the data is stored updates are carried out in different parts of the
=> might see fragmented data database
CSI481 Database systems 19 CSI481 Database systems 20
Transaction Processing in
The Two-Phase Commit Protocol
Distributed Databases
• Distinguish between: • Used to manage the interaction between
– The global transaction coordinator and participants
– The local transactions into which the global • Preserves atomicity
transaction is decomposed
• Each local transaction may be executed on a • Operates in two phases: voting and decision
separate site Phase 1: Voting
• The global transaction has one site which • Coordinator sends PREPARE message to all
manages it, known as the coordinator participants
• Other sites are participants • Coordinator waits for participants to respond
• When participants are done with their work, they within a timeout period
inform the coordinator
CSI481 Database systems 21 CSI481 Database systems 22
Two-Phase Commit Protocol
Phase 2: Decision
Normal Operation
• If all participants return READY (to commit), send
GLOBAL_COMMIT to all participants. Wait for
acknowledgements within timeout period.
• If any participant returns ABORT, send GLOBAL_ABORT
to all participants. Wait for acknowledgements within
timeout period.
• When all acknowledgements received, transaction is
completed.
• If a site does not acknowledge, resend global decision
until it is acknowledged.
CSI481 Database systems 23 CSI481 Database systems 24
4
10/15/2014
Two-Phase Commit Protocol Two-Phase Commit Protocol
Logging Participant Abort
CSI481 Database systems 25 CSI481 Database systems 26
Coordinator State Diagram Participant State Diagram
CSI481 Database systems 27 CSI481 Database systems 28
Termination Protocol: Coordinator Termination Protocol: Participant
• Timeout in WAITING • Timeout in INITIAL
– Coordinator is waiting for participants to declare – Participant is waiting for a PREPARE message from the
whether they're going to commit or abort coordinator
– A missing vote means that the coordinator cannot – Participant can either ignore subsequent PREPARE
commit the global transaction message, or send an ABORT message to the
– Coordinator may abort the global transaction coordinator
• Timeout in DECIDED • Timeout in PREPARED
– Coordinator is waiting for participants to acknowledge – Participant is waiting for the instruction to commit or
successful commit or abort abort – blocked without further information
– Coordinator resends global decision to participants – Participant can contact other participants to find one
who have not acknowledged that knows the decision – cooperative termination
protocol
CSI481 Database systems 29 CSI481 Database systems 30
5
10/15/2014
Recovery Protocol: Coordinator Recovery Protocol: Participant
• Failure in INITIAL • Failure in INITIAL
– Commit not yet begun, restart commit procedure – Participant has not yet voted
• Failure in WAITING – Coordinator cannot have reached a decision
– Coordinator has sent PREPARE, but has not yet – Participant may unilaterally abort
received all COMMIT/ABORT messages from
participants • Failure in PREPARED
– Recovery restarts commit procedure – Participant has voted, but doesn't know what the
• Failure in DECIDED global decision was
– If coordinator has received all ACK messages, – Cooperative termination protocol
complete successfully • Failure in ABORTED/COMMITTED
– Otherwise, terminate – Transaction has been completed, no action necessary
CSI481 Database systems 31 CSI481 Database systems 32
