CS122aSlides09

CS122aSlides09 - ICS122A / EECS116 Introduction to Data...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
1 ICS122A / EECS116 Introduction to Data Management Spring 2009 Prof. Mike Carey Bren School of ICS UC Irvine Slides based on previous CS122a lecture notes as well as  material borrowed from U-Wisconsin, Stanford, & Berkeley
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ICS122A/EECS116 Notes 02 2 Announcements HWs and projects HW #3 due tonight by midnight Midterm a week from today (in class) Project Part 2 due a week from Thursday Midterm preparation Lectures and readings are both fair game TA/Grader will reopen HW #1-3 for practice I’ll review SQL (and more) on Thursday in class Today SQL Constraints ( cont.) and Triggers Any lingering questions before we move on…?
Background image of page 2
ICS122A/EECS116 Notes 08 3 SQL Constraints and Triggers ( cont. ) Keys Primary keys Unique Foreign keys Value-based checks Tuple-based checks Assertions Triggers Also: NULLs (time permitting!)
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ICS122A/EECS116 Notes 10 4 One-Column CHECK Constraints For declaring constraints on an attribute’s values Syntax: CHECK (condition) Ex: CREATE TABLE Emp ( name CHAR(30), dno int, gender CHAR(1) CHECK (gender in (‘F’, ‘M’)) , age int CHECK (age >= 18 AND age < 100) ); Checked whenever there is an insertion or an update of the attribute in question
Background image of page 4
ICS122A/EECS116 Notes 10 5 One-Column CHECK Constraints ( cont. ) Syntax: CHECK (condition) A check constraint may utilize a subquery Condition may involve The checked attribute Other attributes and other relations (used in the subquery) Ex: CREATE TABLE Emp ( ssn int, name CHAR(30), dno int CHECK (dno in (SELECT dno from Dept)) ); Condition checked only when the associated attribute changes I.e., when we insert or update Emp tuples NOT checked when we modify Dept (so beware!)
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ICS122A/EECS116 Notes 10 6 Multi-Column CHECK Constraints CHECK (condition) clauses may be added in the last section of a CREATE TABLE statement Ex: CREATE TABLE Emp ( ssn int, gender CHAR(1), age int, dno int, CHECK (gender in (‘F’, ‘M’)), CHECK (age >= 18 AND age < 100) ); Checked whenever a tuple is inserted or updated Again, a subquery can be used if data from other tables is needed for checking the update…. .
Background image of page 6
ICS122A/EECS116 Notes 10 7 CREATE TABLE Emp ( ename CHAR(15),  dno int, sal int, CHECK (dno in (SELECT dno from Dept)) ); If someone inserts an employee whose dno does not exist in
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 22

CS122aSlides09 - ICS122A / EECS116 Introduction to Data...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online