SQL - Part 2

Question 1

What are the pros/cons of assertions, attribute/tuple checks, and triggers?

Answer 1

Assertions Pro: Powerful; Con: Don’t know when they need to be checked
Attribute/tuple Checks: Pro-checked at known times Con-not powerful
Triggers- Pro let use decide when to check, powerful; Con-complex!

Question 2

What are the 3 criteria for triggers?

Answer 2

ECA:
Event-type of data base modification (insert on sells)
Condition: SQL boolean valued expression
Action- ANY SQL Statement

Question 3

What are the 6 parts of trigger Syntax?

Answer 3

1 CREATE TRIGGER BeerTrig
2 AFTER INSERT ON SELL (event)
3 REFERENCING NEW ROW AS NewTuple
4 FOR EACH ROW
5 WHEN (NewTuple.beer NOT IN (SELECT name FROM Beers)) (condition)
6 INSERT INTO Beers(Name) VALUES NEwTuple.ber (action)

Question 4

What is another option instead of CREATE TRIGGER?

Answer 4

REPLACE TRIGGER

Question 5

What are the EVENT options?

Answer 5

AFTER/BEFORE/INSTEAD OF
INSERT/DELETE/UPDATE
(update may be on a particular attribute)

Question 6

What are the REFERENCING options and when is it used?

Answer 6

REFERENCING [NEW OLD][ROW TABLE] AS

• when inserting (use new)
• when deleting (use old)
• when updating (use both)

Question 7

When is FOR EACH ROW used and what is an alternative?

Answer 7

• indicates row-level; executed one for each modified tuple

- absence indicated statement-level; executed once for an SQL statement regardless of how many tuples are modified

Question 8

What is the condition? How do I access the different tuples? When is it evaluated?

Answer 8

Any boolean-value condition (When tuple NOT IN (Select statement))

• access the new/old tuples using the names in REFERENCING clause
• evaluated before or after depending on event

Question 9

What is the action? What if I have multiple?

Answer 9

SQL Statements that aren’t query; limited to modifications (insert, delete, update);
surround multiple by BEGIN..END

Question 10

What to “modify” views?

Answer 10

Use INSTEAD OF Triggers

- turn insertion of a view into actual insertions

Question 11

What are 3 differences between SQL triggers and Oracle Triggers?

Answer 11

1. Action is a PL/SQL statement
2. New/old tuples referenced automatically
3. Stronger restrictions on trigger actions to avoid infinite sequence of triggers

Question 12

What are 3 kind of database modification?

Answer 12

1. Insert tuples 2. Delete tuples 3. Update value of tuple(s)

Question 13

What is the insertion syntax?

Answer 13

INSERT INTO Likes
VALUES ('Sally', 'Bud')
OR (specify attributes)
INSERT INTO Likes(beer, drinker)
VALUES ('Bud, 'Sally');

Question 14

How do you insert using a subquery?

Answer 14

INSERT INTO PotBddfiers

(SELECT d2. drinker ..);

Question 15

What is the deletion syntax? How to delete all tupels?

Answer 15

DELETE FROM WHERE ;

DELETE FROM ;

Question 16

What are the stages of deletion?

Answer 16

1. Mark tuples for WHERE condition is satisfied.

2. DELETE marked tuples

Question 17

What is the syntax for updates?

Answer 17

UPDATE
SET phone=’555’
WHERE name=’James’;

Question 18

What is the syntax to create a view?

Answer 18

CREATE VIEW  AS ;
CREATE VIEW CanDrink As
SELECT drinker
FROM Drinkers
WHERE drinks > 10;

Question 19

How do you access a view?

Answer 19

Same as base table; just can’t modify

Question 20

How does the DBMS interpret a view?

Answer 20

1. Interpret query as if view was base table
2. Turn query into relational algebra
3. Transform definition of view by replacing algebraic equivalents
   Proj(view)
   Select
   Project (actual base table)

Question 21

What are the 2 key query optimizations?

Answer 21

1. Push selection down tree

2. Elimination necessary projections