十一、事务

事务：

A transaction is asequence of queries and update statements on DB, executed as a single, and arestarted implicitly(隐式地) and terminated(结束) by one

ofcommit work(提交)or rollback/abort work.

– Commit work :makes the updates performed by the transaction become permanent(永久地) in thedatabase. 

– Rollback work:undoes all the updates performed by the SQL statements in the transaction.

 

l Unit of work

l Atomic(原子性) transaction

– either fullyexecuted or rolled back as if it never occurred

l Transactionsbegin implicitly(隐式地)

– Ended by commitwork or rollback work

l But default onmost databases: each SQL statementcommits automatically

– Can turn off autocommit for a session (e.g. using API)

– In SQL:1999, canuse:  begin atomic  ….  end

l Not supportedon most databases

 

IntegrityConstraints （完整性约束）

Integrityconstraints guard against accidental

damage to thedatabase(避免对数据的意外破坏), by

ensuring thatauthorized changes to the

database do notresult in a loss of data

consistency(保证用户对数据库所做的修改不会破坏数据的一致性).

– An instructor namecannot be null.

– No two instructorscan have the same instructor ID.

– Every departmentname in the course relation must

have a matchingdepartment name in the

department relation.

– The budget of adepartment must be greater than

$0.00.

 

Integrity Constraints on a Single Relation（单个关系的完整性约束）

l primary key

l not null

e.g： Declare name and budget to be not null

– name varchar(20)not null

– budgetnumeric(12,2) not null

l Unique

e.g:unique ( A1, A2, …, Am)

– The uniquespecification states that the

attributes A1, A2, …Am form a candidate key(候选码).

– Candidate keys are permitted(允许) to be null(in contrast to primarykeys).

l foreign keys

l check (P),where P is a predicate(谓词)

The check clause isapplied to relation declaration

– check (P), where Pis a predicate which must be satisfied by every tuple in the relation.

e.g:  ensure that the budget of adepartment must be greater than $0.00

– create table department

   (dept_namevarchar (20),

   buildingvarchar (15),

   budgetnumeric (12,2),

   primary key(dept_name)

  check(budget>0));

 

保证每个semester都属于集合：{Spring、Fall、Winter、Summer}

create table section (

           course_id  varchar (8),

           sec_id  varchar (8),

           semester  varchar (6),

           year  numeric (4,0),

           building  varchar (15),

           room_number  varchar (7),

            timeslot id  varchar (4), 

          primary key (course_id, sec_id, semester, year),

          check (semester  in (‟Fall‟, ‟Winter‟,‟Spring‟, ‟Summer‟))

      );

 

Referential Integrity(参照完整性)

 Ensures that a value that appears in onerelation for a given set of attributes also appears for a certain set ofattributes in another relation.(一个关系中给定属性集上的取值，在另一关系的特定属性集的取值中出现。实际上就是保证在被参照的关系中，那些被参照的元组一定存在。)

 

–Example:  If “Biology” is a departmentname appearing in one of the tuples in the course relation, then there exists atuple in the departmentrelation for “Biology”.  -- foreignkey 

也就是说如果course表中有某个元组的dept_name的属性是“Biology”，那么在被参照的department表中，就一定有一个元组的相应属性（dept_name）是“Biolog”。

create table course(

    course_id  char(5) primary key,

    title            varchar(20),

    dept_name varchar(20) references department

)

 

Referentialintegrity constraint also called subset dependency(子集依赖) 。

A good DB design should ensure that any relation schema  R2 (and its any tuples) can only referenceother relation schema R1  through itsforeign key. （一个设计得好的数据库，应该保证任何一个关系表R2（以及它的任何一个元组）只能仅仅通过它的外键参照另一个关系表R1。）

 

Review

 

l JoinExpressions

– left outer join,right outer join, full outer join

– inner join = join

– Join types andjoin conditions

l Views

– Create view

– Use views in SQLqueries

– Update view: withcheck option

l Transactions

– Atomic

– Commit work,Rollback work

 

SSDUT-SoftwareSchool of DUT

60

Review

l IntegrityConstraints

– Not null

– unique ( A1, A2,…, Am), candidate key, null

– Check(P)

– ReferentialIntegrity, foreign key, on

delete/updatecascade, on delete/update set

null, ondelete/update set default

– defer constraintchecking

– create assertion<assertion-name> check

<predicate>