TDA357 (10) SQL4-Assertions-Triggers, Summaries of Construction

Download TDA357 (10) SQL4-Assertions-Triggers and more Summaries Construction in PDF only on Docsity!

Database Construction

(and Usage)

More on Modifications and Table Creation

Assertions

Triggers

Lecture 8

Summary – Modifications

• Modifying the contents of a database:

– Insertions

INSERT INTO tablename VALUES tuple

– Deletions

DELETE FROM tablename WHERE test over rows

– Updates

UPDATE tablename SET attribute = value WHERE test over rows

Explicit attribute lists

• Attribute order could be given explicit

when inserting.

– Example:

INSERT INTO

GivenCourses(course, period, teacher, nrStudents) (SELECT course, period + 2, teacher, NULL FROM GivenCourses WHERE period <= 2); Perhaps the teacher and nrStudents attributes were listed in the other order in the definition of the table? Doesn’t matter anymore since they are explicitly listed.

Quiz

What will the following insertion result in?

– Attribute lists can be partial. Any attributes not

mentioned will be given the value a default

value, which by default is NULL.

INSERT INTO

GivenCourses(course, period, teacher) VALUES (’TDA357’, 3, ’Niklas Broberg’); course period teacher numStud

Insertion with default values

• Leaving out an attribute in an insertion with

explicitly named attributes gives that row the

default value for that attribute:

• When no attribute list is given, the same effect

can be achieved using the DEFAULT keyword:

INSERT INTO

GivenCourses(course, period, teacher) VALUES (’TDA357’, 3, ’Niklas Broberg’); INSERT INTO GivenCourses VALUES (’TDA357’, 3, ’Niklas Broberg’, DEFAULT);

Quiz!

course per teacher nrSt TDA357 2 Niklas Broberg 130 TDA357 4 Rogardt Heldal 95 TIN090 1 Devdatt Dubhashi 62 code name TDA357 Databases TIN090 Algorithms Courses GivenCourses

DELETE FROM Courses

WHERE code = ’TDA357’;

Error, because of the reference from GivenCourses to

Courses. Is this reasonable?

Cascading

• Cascading: When the referenced row is

deleted/updated, also delete/update any

rows that refer to it.

– Typically used for ”parts of a whole”.

– Set using ON [DELETE|UPDATE] CASCADE

CREATE TABLE GivenCourses ( course CHAR(6), CONSTRAINT CourseExists FOREIGN KEY course REFERENCES Courses(code) ON DELETE CASCADE ON UPDATE CASCADE … more columns and constraints … );

Set NULL

• Set NULL: When the referenced row is

deleted/updated, set the corresponding attribute

in any referencing rows to NULL.

• Typically used when there is a connection, but one

that does not affect the actual existence of the

referencing row.

• Set using ON [DELETE|UPDATE] SET NULL CREATE TABLE GivenCourses ( teacher VARCHAR(50), CONSTRAINT TeacherExists FOREIGN KEY teacher REFERENCES Teachers(name) ON DELETE SET NULL ON UPDATE CASCADE … more columns and constraints … );

Argue for sensible policies for deletions and

updates for the Lectures table.

• GivenCourses.(course, period):
  • ON DELETE CASCADE
  • ON UPDATE CASCADE or RESTRICT
• Rooms.name:
  • ON DELETE SET NULL or RESTRICT
  • ON UPDATE CASCADE

Quiz!

Lectures(course, period, weekday, hour, room) (course, period) - > GivenCourses.(course, period) room - > Rooms.name

Single-attribute constraints

• Many constraints affect only the values of a

single attribute. SQL allows us to specify such

constraints together with the attribute itself, as

inline constraints.

• More than one inline constraint on the same

attribute is fine, just put them after one another.

• Default values should be specified before

constraints.

CREATE TABLE Courses ( code CHAR(6) CONSTRAINT CourseCode PRIMARY KEY, name VARCHAR(50) );

Special case: REFERENCES

• When a foreign key constraint is defined inline,

the FOREIGN KEY keywords can be left out.

• An attribute that references another attribute

could be seen as holding copies of that other

attribute. Why specify the type again?

• The type can be left out even if the foreign key

constraint is specified separately.

CREATE TABLE GivenCourses ( course REFERENCES Courses(code), … more columns and constraints … );

Quiz!

It might be tempting to write

Why will this not work?

An inline constraint only constrains the current

attribute. What the above tries to achieve is to

declare two separate primary keys, which is not

allowed in a table.

CREATE TABLE GivenCourses ( course REFERENCES Courses(code) PRIMARY KEY, period INT CHECK (period IN (1,2,3,4)) PRIMARY KEY, … more columns and constraints … );

Quiz!

”No teacher may hold more than two

courses in the same period!”

How can we formulate this constraint in

SQL?

NOT EXISTS (

SELECT teacher, period FROM GivenCourses GROUP BY teacher, period HAVING COUNT(course) > 2 ); course period teacher numStud

Assertions

• Assertions are a way to specify global

constraints on a database.

– Create using CREATE ASSERTION:

– Example:

CREATE ASSERTION NotOverworked CHECK (NOT EXISTS (SELECT teacher, period FROM GivenCourses GROUP BY teacher, period HAVING COUNT(course) > 2) ); CREATE ASSERTION name CHECK test PostgreSQL does not support CREATE ASSERTION, So we emulate them using TRIGGER