• keys

• foreign keys

• domain

• tuple-based

• assertions

• REFERENCES attribute must be PRIMARY KEY or UNIQUE

  FOREIGN KEYS <list-attributes>
    REFERENCES <relation>(attributes)

enforcing constraints from relation $R$ to relation $S$, the following violation are possible:

1. insert/update $R$ introduces values not found in $S$
2. deletion/update to $S$ causes tuple of $R$ to “dangle”

ex: suppose $R=\text{Sell} \cap S=\text{Beer}$

delete or update to $S$ that removes a beer value found in some tuples of $R$

actions:

1. Default: reject modification
2. CASCADE: make the same changes in Sells
   • Delete beer: delete Sells tuple
   • Update beer: change value in Sells
3. SET NULL: change beer to NULL

Can choose either CASCADE or SET NULL as policy, otherwise reject as default

create table Sells (
  bar CHAR(20),
  beer CHAR(20) CHECK (beer IN
      (SELECT name FROM Beers)),
  price REAL CHECK (price <= 5.00),
  FOREIGN KEY(beer)
    REFERENCES Beers(name)
    ON DELETE SET NULL
    ON UPDATE CASCADE
)

attributed-based check

CHECK(<cond>): cond may use name of attribute, but any other relation/attribute name MUST BE IN subquery

CHECK only runs when a value for that attribute is inserted or updated.

Tuple-based checks

added as a relation-schema element

check on insert or update only

create table Sells (
  bar CHAR(20),
  beer CHAR(20),
  price REAL,
  CHECK (bar = 'Joe''s Bar' OR price <= 5.00),
)

SELECT name FROM Beers WHERE manf = 'Anheuser-Busch';
 
SELECT t.name FROM Beers t WHERE t.manf = 'Anheuser-Busch';
 
SELECT * FROM Beers WHERE manf = 'Anheuser-Busch';
 
SELECT name AS beer, manf FROM Beers WHERE manf = 'Anheuser-Busch';
 
SELECT bar, beer,
       price*95 AS priceInYen
FROM Sells;
 
-- constants as expr (using Likes(drinker,beer))
SELECT drinker,
       'likes Bud' as whoLikesBud
FROM Likes
WHERE beer = 'Bud';

patterns

% is any string, and _ is any character

SELECT name FROM Drinkers
WHERE phone LIKE '%555-_ _ _ _';

In sql, logics are 3-valued: TRUE, FALSE, UNKNOWN

• comparing any value with NULL yields UNKNOWN
• A tuple in a query answer iff the WHERE is TRUE

ANY(<queries>) and ALL(<queries>) ensures anyof or allof relations.

IN operator

IN is concise

SELECT * FROM Cartoons WHERE LastName IN ('Simpsons', 'Smurfs', 'Flintstones')

IN is a predicate about R tuples

-- (1,2) satisfies the condition, 1 is output once
SELECT a FROM R -- loop once
where b in (SELECT b FROM S);
 
-- (1,2) with (2,5) and (1,2) with (2,6) both satisfies the condition, 1 is output twice
SELECT a FROM R, S  -- double loop
WHERE R.b = S.b;

NOT EQUAL operator in SQL is <>

Difference between ANY and NOT IN

• ANY means not = a, or not = b, or not = c
• NOT IN means not = a, and not = b, and not = c. (analogous to ALL)

EXISTS operator

EXISTS(<subquery>) is true iff subquery result is not empty.

UNION, INTERSECT, EXCEPT

structure: (<subquery>)<predicate>(<subquery>)

or a multiset, is like a set, but an element may appear more than once.

• Force results to be a set with SELECT DISTINCT
• Force results to be a bag with UNION ALL

ORDER BY ops followed with desc

INSERT INTO Likes VALUES('Sally', 'Bud');
 
-- or
INSERT INTO Likes(beer, drinker) VALUES('Bud', 'Sally');

add DEFAULT value during CREATE TABLE (DEFAULT value will be used if inserted tuple has no value for given attributes)

create table Drinkers (
  name CHAR(30) PRIMARY KEY,
  addr CHAR(50)
    DEFAULT '123 Sesame Street',
  phone CHAR(16)
);
 
-- in this case, this will use DEFAULT value for addr
-- | name  | address           | phone |
-- | Sally | 123 Sesame Street | NULL  |
INSERT INTO Drinkers(name) VALUES('Sally');

Those drinkers who frequent at least one bar that Sally also frequents

INSERT INTO Buddies
  (SELECT d2.drinker
   FROM Frequents d1, Frequents d2
   WHERE d1.drinker = 'Sally' AND
     d2.drinker <> 'Sally' AND d1.bar = d2.bar)

DELETE FROM:

-- remove a relation
DELETE FROM Beers WHERE name = 'Bud';
 
-- remove all relation
DELETE FROM Likes;
 
-- Delete from Beer(name, manf) all beers for which there is another beer by the same manufacturer
DELETE FROM Beers b
  WHERE EXISTS (
      SELECT name FROM Beers
      WHERE manf = b.manf AND name <> b.name
    )

UPDATE schema:

UPDATE <relation>
SET <list-of-attribute-assigments>
WHERE <condition-on-tuple>

SUM, AVG, COUNT, MIN, MAX can be applied toa column in SELECT clause

COUNT(*) counts the number of tuples

-- find average price of Bud
SELECT AVG(price) FROM Sells WHERE beer = 'Bud';
 
-- to get distinct value, then use DISTINCE
SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = 'Bud';

NULL never contributes to a sum, average, or count

however, if all values in a column are NULL, then aggregation is NULL

exception: COUNT of an empty set is 0

GROUP BY: according to the values of all those attributes, and any aggregation is applied only within each group:

-- find the youngest employees per rating
SELECT rating, MIN(age)
FROM Employees
GROUP BY rating
 
-- find for each drinker the average price of Bud at the bars they frequent
SELECT drinker, AVG(price)
FROM Frequents, Sells
WHERE beer = 'Bud' AND Frequents.bar = Sells.bar
GROUP BY drinker;

restriction on SELECT with aggregation

each element of SELECT must be either:

1. Aggregated

2. An attribute on GROUP BY list

illegal example

SELECT bar,beer,AVG(price) FROM Sells GROUP BY bar
-- only one tuple out for each bar, no unique way to select which beer to output

HAVING(<condition>) may followed by GROUP_BY

If so, the condition applies to each group, and groups not satisfying the condition are eliminated.

-- Get average price of beer given all beer groups exists with at
-- least three bars or manufactured by Pete's
SELECT beer, AVG(price)
FROM Sells
GROUP BY beer
HAVING COUNT(bar) >= 3 OR
  beer in (SELECT name FROM Beers WHERE manf = 'Pete''s');

requirements on HAVING:

• Anything goes in a subquery
• Outside subqueries they may refer to attributes only if they are either:
  • A grouping attribute
  • aggregated

-- Frequents            x Sells
-- (Bar) | Beer | Price | Drinker | (Bar)
-- Joe   | Bud  | 3.00  | Aaron   | Joe
-- Joe   | Bud  | 3.00  | Mary    | Jane
SELECT drinker
FROM Frequents, Sells
WHERE beer = 'Bud' AND Frequents.bar = Sells.bar;

Or known as join operations ⇒ all join operations are considered cartesian products.

Outer join preserves dangling tuples by padding with NULL

A tuple of $R$ that has no tuple of $S$ which it joins is said to be dangling

Left outer join

Right outer join

full outer join

inner join

R [NATURAL] [LEFT|RIGHT|FULL] OUTERJOIN [ON<condition>] S
 
-- example
R NATURAL FULL OUTERJOIN S

• natural: check equality on all common attributes && no two attributes with same name
• left: padding dangling tuples of R only
• right: padding dangling tuples of S only
• full: padding both (default)

note: each node are at least 50% full

cost

tree is “height-balanced”

insert/delete at $\log_{F}N$ cost

min 50% occupancy, each node contains $d \leq m \leq 2d$ entries, where $d$ is the order or the tree

• index is a collection of buckets

Insert: if bucket is full ⇒ split