Chapter 7 constraints and triggers
This presentation is the property of its rightful owner.
Sponsored Links
1 / 41

Chapter 7 Constraints and Triggers PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 7 Constraints and Triggers . Spring 2011 Instructor: Hassan Khosravi. SQL: Constraints and Triggers. Certain properties we’d like our database to hold Modification of the database may break these properties Data entry may have errors Build handlers into the database definition.

Download Presentation

Chapter 7 Constraints and Triggers

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 7 constraints and triggers

Chapter 7 Constraints and Triggers

Spring 2011

Instructor: Hassan Khosravi


Sql constraints and triggers

SQL: Constraints and Triggers

  • Certain properties we’d like our database to hold

  • Modification of the database may break these properties

  • Data entry may have errors

  • Build handlers into the database definition


Sql constraints and triggers1

SQL: Constraints and Triggers

  • (integrity) constraints (static)

    • Constrain allowable database states

  • Triggers (Dynamic)

    • Monitor database changes

    • Check conditions and initiate conditions


Integrity constraints

(integrity) Constraints

  • Impose restrictions on allowable data beyond those imposed by structure and types

    • Examples on university database

      • 0 < gpa < 4.0

      • Enrollment < 50,000

      • Decision attribute: ‘y’ or ‘n’

      • Major = ‘CS’  decision = null

      • sizeHS < 200  not addmittedenr > 5000

  • Why use integrity constraints

    • Data-entry error (insert)

      • gpa in range

    • Correctness criteria (update)

    • Enforce consistency

      • Referenced tuples

    • Tell system about your data


Classification of integrity constraints

Classification of Integrity Constraints

  • Non- null

  • Keys

    • Uniqueness

  • Referential integrities (foreign key)

  • Attribute-based

    • Constraining values in attributes

  • Tuples-based

    • How values in different tuples should correlate

  • General assertions


Declaration and enforcing constraints

Declaration and enforcing Constraints

  • Declaration

    • With original schema when tables are declared

    • Once you have a running database

  • Enforcement

    • Check after every dangerous modification

      • Changing major we don’t need to check the gpa constraint

    • Deferred constraint checking

      • We may do some modifications that would raise errors

      • But after we have done all the modifications it should be ok

      • Check once some modifications are done (transaction)


Triggers

Triggers

  • Event-Condition-Action rules

    • When event occurs, check condition, if true, then do action

    • Example

      • Enrolllment > 75000  reject all applications

      • If application with gpa > 3.95  accept automatically

      • Update sizehs to be > 7000  change to wrong and raise error

  • Why use triggers?

    • Move codes from application to DBMS

    • Enforce constraints

      • Some of the assertions and checks are not implemented in some DBMS

      • Triggers could not only detect the problem, they can also solve it


Non null constraints

Non-null constraints

  • Defining that a specific attribute in a specific table can not take the value of null

    • Create table A(A1int, A2 int not null, A3 text)

    • Examples


Key constraints

Key Constraints

  • The primary key of the tables has to be unique

    • Create table A (A1int primary key, A2 int, A3 text)

    • Create table A (A1int, A2 int, A3 text) primary key A1, A2))

  • You can also define other combination of attributes to be unique ( without declaring them as key)

    • Create table A (A1int primary key, A2 int, A3 text unique)

    • Create table A (A1int primary key, A2 int, A3 text , unique(A1, A2))

  • Examples


Attribute base check constraints

Attribute base check constraints

  • Constraints on the attributes to have specific ranges or types

    • Create table A (A1int, A2 int, A3 text, check (A2 >0))

  • Examples


Assertions

Assertions

  • Assertions are very strong they are checks done over all tables at the same time. (assertions are generally not implemented in current dbms)

    • Create assertion name check ()

  • Example


Referential integrity

Referential integrity

  • Referential integrity is integrity of references.

    • No dangling pointers

    • What would referential integrity from S.sid to A.sid mean?


Example

Example


Example1

Example


Referential integrity1

Referential integrity


Referential integrity2

Referential integrity


Restrict

Restrict


Set null

Set Null


Cascade

Cascade


Update

Update


Referential integrity3

Referential Integrity

  • Examples


Triggers1

Triggers

  • Event-Condition-Action rules

    • When event occurs, check condition, if true, then do action

    • Example

      • Enrolllment > 75000  reject all applications

      • If application with gpa > 3.95  accept automatically

      • Update sizehs to be > 7000  change to wrong and raise error


Triggers2

Triggers


Triggers using each row

Triggers Using Each Row


Triggers using table statement

Triggers using table statement


Tricky issues with triggers

Tricky issues with Triggers

  • Row-Level vs. Statement-level

    • Which one do you use?

  • Multiple triggers activated at the same time

    • For example a delete command triggers multiple triggers

    • Which one do you run first?

  • Chaining trigger action

    • Self triggering

    • Cycles Trigger A Trigger B Trigger C Trigger A

    • Nested triggers

  • Implementations are significantly different in different systems

    • Tricky example


Trigger examples

Trigger Examples

  • For each student with GPA between 3.3 and 3.6, make them apply to geology in Stanford and biology in MIT


Trigger examples1

Trigger Examples

  • Implement referential integrity using triggers.

    • Delete all applications of a student from apply table after the student is deleted in the student table


Trigger examples2

Trigger Examples

  • Implement referential integrity using triggers on cascade.

    • Update the name of all colleges in the apply table if the name of the college is updated in the college table


Trigger examples3

Trigger Examples

  • Implement primary key using triggers.

    • Before inserting into table college check if the college exists. Raise an error if you get a duplicate

    • Example


Trigger examples4

Trigger Examples

  • Once a college has received 10 applications, add “-Done” to the end of the name of the college

  • Example of chains in triggers


Trigger examples5

Trigger Examples

  • If the size of the sizehk <100 or sizehk> 5000, then assume that its an error and ignore

  • Example considering before and after insert in chaining


Trigger examples6

Trigger Examples

  • Automatically accept students applying to Berkeley that have GPA > 3.7 and are coming from a highschool > 1200


Trigger examples7

Trigger Examples

  • Once a college passes a thresh-hold of 16000 applicants, delete new EE applicant and set all the decisions for all majors to undecided


More trigger examples self triggers

More Trigger Examples (self triggers)

  • By default this may get triggered only once.

  • Recursive_trigger = on


More trigger examples cycles

More Trigger Examples (cycles)

Example


More trigger examples conflicts

More Trigger Examples (Conflicts)

  • What happens when you have multiple triggers being triggered at the same time


More trigger examples nested triggers

More Trigger Examples (Nested Triggers)

  • What order are triggers carried out in you have nested triggers

    • behaves like imperative programming


End of chapter 7

End of Chapter 7


  • Login