Hawaii at Manoa 2 CREATE TABLE Studio ( name CHAR(30) NOT NULL PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#) ) CREATE TABLE Studio ( name CHAR(30) NOT NULL, address VARCHAR(255), presC# INT, PRIMARY KEY(name), FOREIGN KEY(presC#) REFERENCES MovieExec(cert#) ) Cert# must be declared with PRIMARY KEY or UNIQUE constraint
• UPDATE studio SET presC#=? ... • DELETE FROM MovieExec WHERE ... • UPDATE MovieExec SET cert#=? ... Lipyeow Lim -‐-‐ University of Hawaii at Manoa 3 CREATE TABLE Studio ( name CHAR(30) NOT NULL PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#) ) If new presC# value does not exist in MovieExec, reject! If deleted cert# values are used in studio, reject! If old cert# values are used in studio, reject!
to referenced a]ributes are mimicked at FK. • SET NULL : changes to referenced a]ributes makes affected FK null • DEFERABLE : checking can wait Cll end of transacCon – INITIALLY DEFERRED : defer checking – INITIALLY IMMEDIATE : check immediately Lipyeow Lim -‐-‐ University of Hawaii at Manoa 4 CREATE TABLE Studio ( name CHAR(30) NOT NULL PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#) ON DELETE SET NULL ON UPDATE CASCADE )
CHECK condi&onal-‐expression Lipyeow Lim -‐-‐ University of Hawaii at Manoa 5 CREATE TABLE Studio ( name CHAR(30) NOT NULL PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#) CHECK ( presC# >=100000 ) ) CREATE TABLE MovieStar ( name CHAR(30) NOT NULL PRIMARY KEY, address VARCHAR(255), gender CHAR(1), birthdate DATE, CHECK ( gender = ‘F’ OR name NOT LIKE ‘Ms.%’ ) )
you can refer to them in alter table statements Lipyeow Lim -‐-‐ University of Hawaii at Manoa 6 CREATE TABLE Studio ( name CHAR(30) CONSTRAINT nameiskey PRIMARY KEY, address VARCHAR(255), presC# INT REFERENCES MovieExec(cert#) CONSTRAINT sixdigit CHECK ( presC# >=100000 ) ) ALTER TABLE Studio DROP CONSTRAINT nameiskey; ALTER TABLE Studio ADD CONSTRAINT nameiskey PRIMARY KEY(name) ;
+ number of sailors < 100 Lipyeow Lim -‐-‐ University of Hawaii at Manoa 7 CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 ) • When is the constraint enforced ? • What happens if the sailors table is empty ? • Think of a case when the constraint is violated but the system never catches it.
with any table. • Evaluated whenever tables in the condiCon are updated Lipyeow Lim -‐-‐ University of Hawaii at Manoa 8 CREATE ASSERTION smallClub CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 )
specified changes occur to the DBMS • Three parts: – Event (acCvates the trigger) – CondiCon (tests whether the triggers should run) – AcCon (what happens if the trigger runs) Lipyeow Lim -‐-‐ University of Hawaii at Manoa 9
? • What is the difference between a constraint and a trigger ? Lipyeow Lim -‐-‐ University of Hawaii at Manoa 10 CREATE TRIGGER youngSailorUpdate AFTER INSERT ON SAILORS REFERENCING NEW TABLE NewSailors FOR EACH STATEMENT INSERT INTO YoungSailors(sid, name, age, rating) SELECT sid, name, age, rating FROM NewSailors N WHERE N.age <= 18
that will cause an error when an update occurs that would result in a salary increase greater than ten percent of the current salary. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 11 CREATE TRIGGER RAISE_LIMIT AFTER UPDATE OF SALARY ON EMPLOYEE REFERENCING NEW AS N OLD AS O FOR EACH ROW WHEN (N.SALARY > 1.1 * O.SALARY) SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%'
we store a defini&on, rather than a set of tuples. • Views can be dropped using the DROP VIEW command. • What if table that the view is dependent on is dropped ? • DROP TABLE command has opCons to let the user specify this. CREATE VIEW YoungAcCveStudents (name, grade) AS SELECT S.name, E.grade FROM Students S, Enrolled E WHERE S.sid = E.sid and S.age<21 12 Lipyeow Lim -‐-‐ University of Hawaii at Manoa
Manoa 13 CREATE VIEW YoungAcCveStudents (name, grade) AS SELECT S.name, E.grade FROM Students S, Enrolled E WHERE S.sid = E.sid and S.age<21 SELECT name FROM YoungAcCveStudents WHERE grade = ‘A’ SELECT name FROM (SELECT S.name, E.grade FROM Students S, Enrolled E WHERE S.sid = E.sid and S.age<21) WHERE grade = ‘A’ Query views as with any table Conceptually, you can think of rewriCng using a subquery
Why? • A view on R is updateable when – WHERE : must not involve R in a subquery – FROM : only one occurrence of R and no joins. – SELECT : include enough a]ributes to fill out other a]ributes in R Lipyeow Lim -‐-‐ University of Hawaii at Manoa 14 CREATE VIEW ParamountMovies AS SELECT Ctle, year FROM movies WHERE studioName=‘Paramount’ INSERT INTO ParamountMovies VALUES (‘Star Trek’, 1979) INSERT INTO Movies ( Ctle, year ) VALUES (‘Star Trek’, 1979) SELECT * FROM ParamountMovies
at Manoa 15 SELECT * FROM Movies WHERE studioName=‘Disney’ AND year=1990 Title Year Length Genre studioN ame produc erC# ... 1990 10,000 rows 200 movies are made in 1990 An index on a]ribute A is a data structure that makes it efficient to find those tuples that have a fixed value for a]ribute A
an a]ribute that the tuples are sorted in. • If a primary key is specified in the CREATE TABLE statement, an (unclustered) index is automaCcally created for the PK. • To create a clustered PK index: – Create table without PK constraint – Create index on PK with cluster opCon – Alter table to add PK constraint • To get rid of unused indexes: DROP INDEX myIdx; Lipyeow Lim -‐-‐ University of Hawaii at Manoa 16 CREATE INDEX myIdx ON mytable(col1, col3) CREATE UNIQUE INDEX myUniqIdx ON mytable(col2, col5) CREATE INDEX myIdx ON mytable(col1, col3) CLUSTER
• UpdaCng the materialized view (materialized query table in DB2) : incremental or batch Lipyeow Lim -‐-‐ University of Hawaii at Manoa 17 CREATE VIEW ParamountMovies AS SELECT Ctle, year FROM movies WHERE studioName=‘Paramount’ CREATE TABLE ParamountMovies AS (SELECT Ctle, year FROM movies WHERE studioName=‘Paramount’) SELECT Ctle FROM movies WHERE studioName=‘Paramount’ AND year=1990) Queries on base relaCon may be able to exploit materialized views!
lipyeow
matleenalaakso
signer
yosukemurata
miki_small_pin
terasawat
aki_moon
codeforeveryone
ukky86
trishagee
paulrobertlloyd
moore
andyhume
tanoku
pauljervisheath
destraynor
62gerente
chriscoyier
cassininazir
mongodb
bkeepers
![Check Constraints • A]ribute, tuple-‐based, mulC-‐table • Syntax:](https://files.speakerdeck.com/presentations/1240742a081b4ca08a7949333f0c0453/slide_4.jpg){kind=link}
