Lecture 8, part I: Constraints and triggers

Constraints and triggers
• Keys
• Assertion-based constraints
• Foreign keys
• Triggers
Access constraints
• Granting and revoking privileges to access relations.
XML for data exchange (next slide set, if time allows)
• Semistructured data and XML.
• Defining XML formats using XML schemas.


In this lecture I will assume that you remember:
• What a key of a relation is (from the normalization lectures).
• Referential integrity constraints in E/R diagrams.
• How to convert an E/R diagram into relations.
• The SQL used when modifying or adding tuples in a relation.

Keys in SQL?

So far we used the concept of akeyof a relation only for normalization. 

However, keys play an important role in SQL, because specifying the values 

of key attributes is a way of referring to aunique tuplein a relation. 

Since updates (e.g., entered by users of the database) could violate that

certain attributes form a key, RDBMSs offer to check this.

Declaring a primary key

One key of a relation may be declared asprimary. This is done by adding 

to the relation schema a line of the form:

PRIMARY KEY (<list of attributes>)

If the primary key has just one attribute, we may instead write PRIMARY 

KEYimmediately after the definition of the data type of the attribute, e.g.:

id INT PRIMARY KEY, 

NULLvalues are not allowed in attributes of a primary key.

Primary keys and keys in E/R diagrams

The E/R diagram should beconsistentwith the primary keys declared in 

the corresponding relations as follows:

• The key of an entity set should be the same as the primary key (if any) 

of the corresponding relation.

• The primary key (if any) of a relation corresponding to a relationship 

should be the union of the keys of the entity sets connected by the 

relationship.

Download Lebih Lengkapnya :

Constraints and tringgers.pdf