程序代写代做代考 database SQL SQL: Queries, Constraints, Triggers

SQL: Queries, Constraints, Triggers

Null Values. SQL Constraints

CS430/630
Lecture 10

Slides based on “Database Management Systems” 3rd ed, Ramakrishnan and Gehrke

Null Values

 Field values in a tuple may sometimes be

 unknown: e.g., a rating has not been assigned, or a new

column is added to the table

 inapplicable: e.g., CEO has no manager, single person has no

spouse

 SQL provides a special value NULL for such situations

 Special operators IS NULL, IS NOT NULL

SELECT * FROM Sailors WHERE rating IS NOT NULL

 Note: NULL must not be used as constant in expressions!

 A field can be declared as NOT NULL, means NULL values

are not allowed (by default, PK fields are NOT NULL)

Dealing with Null Values

 The presence of NULL complicates some issues

 NULL op value has as result NULL (op is +,-,*,/)

 What does rating>8 evaluate to if rating is equal to NULL ?

 Answer: unknown

 3-valued logic: true, false and unknown

 Recall that WHERE eliminates rows that don’t evaluate to true

 What about AND, OR and NOT connectives?

unknown AND true = unknown

unknown OR false = unknown

NOT unknown = unknown

 Also, = is unknown!

Null Values and Aggregates

 The COUNT(*) result includes tuples with NULL

 COUNT(A) only counts tuples where value of attribute A

is not NULL

 All other aggregates skip NULL values (if aggregate is on

the field that is NULL)

 If all values are NULL on the aggregated field, the result of

aggregate is also NULL (except COUNT which returns 0)

Null Values and Aggregates

Following two queries DO NOT RETURN SAME RESULT if there are

NULLs (in field name):

SELECT COUNT(*) FROM Sailors S

SELECT COUNT(S.name) FROM Sailors S

Following two queries DO NOT RETURN SAME RESULT if there

are NULLs (in field rating):

SELECT COUNT(*) FROM Sailors S

SELECT COUNT(*) FROM Sailors

WHERE (rating>8) OR (rating <= 8) Null Values and Duplicates  Comparing two NULL values gives as result unknown  But there are anomalies when checking for duplicates!  NULL values are considered equal in this case!  Two tuples are duplicates if they match in all non-NULL attributes  Implications for DISTINCT, UNIQUE subqueries, set operations!  Tuples with NULL in some group-by attributes placed in same group if all non-NULL group-by attributes match!  DISTINCT: if multiple tuples have equal values in all non- NULL attributes only one of them is output Outer Joins  Include in join result non-matching tuples  Result tuple padded with NULL values  Variants  FULL: non-matching tuples in both relations included in result  LEFT: only non-matching tuples in left relation included in result  RIGHT: only non-matching tuples in right relation included in result Outer Joins sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 sid bid day 22 101 10/10/96 58 103 11/12/96 Reserves Sailors sid sname rating age bid day 22 dustin 7 45.0 101 10/10/96 31 lubber 8 55.5 NULL NULL 58 rusty 10 35.0 103 11/12/96 SELECT sid, sname, rating, age, bid, day FROM Sailors NATURAL LEFT OUTER JOIN Reserves Join Expressions  SQL shorthands for expressions we already saw Cross Product: Sailors CROSS JOIN Reserves Condition Join: Sailors JOIN Reserves ON

Natural Join:

Sailors NATURAL JOIN Reserves

Usage Example:

SELECT *

FROM Sailors JOIN Reserves ON Sailors.sid=Reserves.sid

Integrity Constraints (Review)

 An IC describes conditions that every legal instance of a

relation must satisfy.

 Inserts/deletes/updates that violate IC’s are disallowed.

 Types of IC’s:

 domain constraints

 Field values must be of right type – always enforced

 primary key constraints

 foreign key constraints

 general constraints

Sample Schema Enforcing Referential Integrity

 What should be done if an Enrolled tuple with a non-existent
student id is inserted?

 Reject the insert!

 What should be done if a Students tuple is deleted?

 Delete all Enrolled tuples that refer to it

 Correct as far as IC is concerned, but data is lost!

 Disallow deletion of a Students tuple that is referred to

 More appropriate in practice

 Set sid in Enrolled tuples that refer to it to a default sid

 Or, set it to NULL

14

Referential Integrity in SQL

 SQL/92 and SQL:1999 support

all options on deletes and

updates.

 Default is NO ACTION

(delete/update is rejected)

 CASCADE (delete/update all

tuples that refer to

deleted/updated tuple)

 SET NULL / SET DEFAULT (sets

foreign key value of

referencing tuple)

CREATE TABLE Enrolled
(sid CHAR(20) default ‘00’,
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid)
REFERENCES Students (sid)
ON DELETE SET DEFAULT
ON UPDATE CASCADE)

15

Complex Constraints: CHECK clause

 Useful when more general ICs

than keys are involved

 Can use queries to express

constraint

 Constraints can be named

 Not checked if table is empty!

 Standalone CHECK for single

table only!

CREATE TABLE Sailors
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CONSTRAINT RatingRange
CHECK ( rating >= 1
AND rating <= 10 ) ) Complex Constraints: Assertions  Number of boats plus number of sailors is < 100  Not associated with a particular table  Constraint may apply to multiple tables! CREATE ASSERTION smallClub CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 )