SLIDE 4 SQL as DDL 4
*
/ ,(
Movie database schema
MOVIES(id, title, yr, score, votes, director) ACTORS(id, name) CASTINGS(movieid, actorid, ord) DIRECTORS(id, name) Relational data model:
key uniqueness (id in MOVIES) foreign key (director to DIRECTORS)
Domain:
yr between 1900 and 2005
Not null
actor’s name is mandatory
Behavioral:
the value of votes is always increasing
*
/ !'&
When ?
At schema definition (mainly) During schema lifecycle
How to express IC ?
SQL SQL extensions
What about commercial DBMS ?
Just a few ICs Other ICs supported by programs (triggers) *
/ !'&
Norms:
SQL86 : unicity, not null, view with «check option » SQL89 : domain, key, referential integrity with reject SQL2 (SQL92) : referential integrity with «cascade delete
and update »
Integrity constraints definition
2 different syntaxes:
- At the end of an attribute definition
- At the end of a table definition
*"
CREATE TABLE CASTINGS ( movieid integer, actorid integer,
- rd integer CONSTRAINT ICord
CHECK (ord between 1 and 10)) ALTER TABLE CASTINGS add primary key (movieid, actorid) ALTER TABLE CASTINGS add constraint fkACT foreign key (actorid) references ACTORS(id) on delete cascade ALTER TABLE CASTINGS add constraint fkMOV foreign key (movieid) references MOVIES(id) on delete cascade ALTER TABLE MOVIES add constraint fkDIR foreign key (director) references DIRECTORS(id) on delete cascade CREATE TABLE MOVIES ( id integer PRIMARY KEY, title char (40) NOT NULL, yr integer CONSTRAINT ICyr CHECK (yr between 1900 and 2005), score integer CONSTRAINT ICscore CHECK (score between 0 and 10), votes integer NOT NULL , director integer)) CREATE TABLE ACTORS ( id integer, name char(40) PRIMARY KEY(id) ) CREATE TABLE DIRECTORS ( id integer PRIMARY KEY, name char(40))
.,(
