事务:
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>