Transcript integrity-constraints

IC and Triggers in SQL
HAVING Clause
Find age of the youngest sailor
with age <=18, for each rating
with at least 2 such sailors
SELECT S.rating, MIN (S.age) AS minage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT (*) > 1
Answer relation:
rating
3
7
8
minage
25.5
35.0
25.5
Sailors instance:
sid
22
29
31
32
58
64
71
74
85
95
96
sname rating age
dustin
7 45.0
brutus
1 33.0
lubber
8 55.5
andy
8 25.5
rusty
10 35.0
horatio
7 35.0
zorba
10 16.0
horatio
9 35.0
art
3 25.5
bob
3 63.5
frodo
3 25.5
Integrity Constraints (Review)
• An IC describes conditions that every legal
instance of a relation must satisfy
• To disallow inserts/deletes/updates that
violate IC’s
• Types of IC’s: Domain constraints, primary key
constraints, foreign key constraints, non-null,
general constraints
An Example via CHECK Clause
CREATE TABLE Stu (
sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK (rating >= 1 AND rating <= 10 )
)
CHECK Example
CREATE TABLE Stu (
sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CONSTRAINT checkRating
CHECK (rating >= 1 AND rating <= 10 )
)
ASSERTION Example
Constraints Over Multiple Relations
•
•
•
Consider a very small school: the count of students and professors should be less than 500
The following is a poor integrity test as it is associated with one relation (the Stu table could
be empty and thus the integrity rule is never checked!
Disassociate from the Stu table
CREATE TABLE Stu (
sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK (
(SELECT COUNT (S.sid) FROM Stu S) +
(SELECT COUNT (P.pid) FROM Prof P) < 500 )
)
ASSERTION Example
Constraints Over Multiple Relations
CREATE ASSERTION smallSchool
CHECK (
(SELECT COUNT (S.sid) FROM Stu S) +
(SELECT COUNT (P.pid) FROM Prof P) < 500
)
ASSERTION Example
The KEY Constraint
CREATE ASSERTION Key
CHECK (
(SELECT COUNT (DISTINCT sid) FROM Stu) =
(SELECT COUNT (*) FROM Stu)
);
• Note: ASSERTION is in standard SQL but not
implemented
Triggers
• Event-Condition-Action rules: procedures that start
automatically if a change occurs to the DBMS
When event occurs, check condition; if true do action
• Why: to move monitoring logic from applications into the DBMS
(more modular, consistency among applications, no
duplications)
• Allow automatic repair
• Database stores triggers just as regular data so they are
persistent and accessible to all database operations
• Useful for security purposes
• Triggers make a passive database “active”
Advantages
• To move application logic and business rules
into database
• Allows more functionality for DBAs to
establish vital constraints/rules of applications
• Rules managed in some central “place”
• Rules automatically enforced by DBMS, no
matter which applications later come on line
The Event-Condition-Action Model
• Actions may apply before or after the
triggering event is executed
• An SQL statement may change several rows
– Apply action once per SQL statement
– Apply action for each row changed by SQL
statement
The Company Database
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode)
DEPARTMENT(DNO, TotalSalary, ManagerSSN)
STARTING_PAY(JobCode, StartPay)
1. Limit all salary increases to 50%.
2. Enforce policy that salaries may never decrease.
3. Maintain TotalSalary in DEPARTMENT relation as
employees and their salaries change. (EN, Fig. 23.2 (a))
4. Inform a supervisor whenever a supervisee’s salary
becomes larger than the supervisor’s. (EN, Fig. 23.2 (b))
5. All new hires for a given job code get the same starting
salary, which is available in the STARTING_PAY table.
Limit all salary increases to 50%
create trigger emp_salary_limit
before update of EMPLOYEE
for each row
when (new.Salary > 1.5 * old.Salary)
set new.Salary = 1.5 * old.Salary;
“new” refers to
the new tuple.
“old” refers to
the old tuple.
Enforce policy that salaries may never decrease
create trigger emp_salary_no_decrease
before update of EMPLOYEE
for each row
Method depends on
when (new.Salary < old.Salary)
DBMS.
begin
log the event;
signal error condition;
end
All new hires for a given job code get the same
starting salary
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode)
STARTING_PAY(JobCode, StartPay)
create trigger emp_start_pay
before insert on EMPLOYEE
for each row
set Salary =
(select StartPay
from
STARTING_PAY
where JobCode = new.JobCode)
Trigger Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;