L7: Modifying Databases

Question 1

Database Insertions:

Insert Multiple Tuples from another Table

(SQL)

Answer 1

INSERT INTO tableA(key)

SELECT DISTINCT tableB.attr

FROM tableB

WHERE <condition></condition>

Use a subquery instead of the “VALUES” keyword

Can insert multiple tuples at once

Question 2

Database Insertions:

General Form

Answer 2

INSERT INTO R(A₁, …, A_n)

VALUES (V₁, …, V_n)

Notes:

• If an attribute is missing, NULL is inserted
• Can omit attribute names IF the values are in the corresponding order

Example:

INSERT INTO Purchases( buyer, seller, product, store)

VALUES (‘Joe’, ‘Fred’, ‘clock’, ‘The Sharper Image’)

Question 3

Database Deletions:

General Form

Answer 3

DELETE FROM table_name

WHERE <condition></condition>

Example:

DELETE FROM Purchase

WHERE seller = ‘Joe’ AND

product = ‘Brooklyn Bridge’

Question 4

Database Update:

General Form

Answer 4

UPDATE table_name

SET column1 = value1, col2 = value2

WHERE some_column = some_value;

Question 5

Views:

Definition

Answer 5

Views

Relations that are not physically stored.

Used to present different information to different users

Can be used as a table later in a session.

Question 6

Database

View Creation

• general form
• example

Answer 6

CREATE VIEW view_name AS <query></query>

Example:

CREATE VIEW Developers AS

SELECT name, project

FROM Employee

WHERE department = “Development”

Question 7

Updating Views

Answer 7

When inserting into a view, entries on actual tables are updated

Example:

Setup: Developers(name, project) is a view of

Employee( ssn, name, dept, project, salary)

Update:

INSERT INTO Developers VALUES(“Joe”, “Optimizer”)

is equivalent to

INSERT INTO Employee

VALUES( NULL, “Joe”, NULL, “Optimizer”, NULL)

Question 8

Constraints

vs

Triggers

Answer 8

Constraint

A relationship among data elements that the DBMS is required to enforce.

Ex: Key Constraints

Triggers

Are only executed when in specific condition occurs.

Easier to implement than complex constraints.

Ex: Only checked when a tuple is inserted

Question 9

Kinds of Constraints

Answer 9

• Keys
• Foreign-Keys
  
  • or Referential-Integrity
• Value based constraints
  
  • Constrains values of a particular attribute

Question 10

Constraints:

Defining Single Attribute Keys

and

Multiple Attribute Keys

Answer 10

Both done while creating tables

Defining Single Attribute Keys:

Use PRIMARY KEY or UNIQUE keywords after declaration of an attribute

example:

CREATE TABLE Beers(

name CHAR(20) PRIMARY KEY,

price FLOAT );

Defining Multiple Attribute Keys:

Precede the tuple with PRIMARY KEY within the variable list

example:

CREATE TABLE Sells(

bar CHAR(20), beer VARCHAR(20), price REAL,

PRIMARY KEY (bar, beer) );

Question 11

Constraints:

Defining Foreign Keys

Answer 11

Use the keyword REFERENCES between attribute and other relation.

Either:

• After an attribute, for single attribute keys
• As an element of the schema:
  
  • FOREIGN KEY(<list>) REFERENCES <relation>(<attributes>)</attributes></relation></list>

Referenced attributes must have been declared with PRIMARY KEY or UNIQUE in their own table

Question 12

Enforcing Foreign Key Constraints:

Two Possible Violations

Answer 12

If there is a foreign-key constraint,

from relation R to relation S, two violations are possible:

• An insert or update to R introducing values that are not found in S
• A deletion or update to S causes some tuples of R to “dangle” (found in R but not S anymore)

Question 13

Enforcing Foreign Key Constraints:

Handling an insertion/update violation of a

Foreign Key Constraint

Answer 13

The insertion or update must be rejected

Example:

Constraint: R=sells, S=beers,

sells(beer) references beers(name)

If an insertion is attempted to insert a beer in “sells” that is not in beers(name),

the operation is rejected.

Question 14

Enforcing Foreign Key Constraints:

Handling Deletion/Update operations that

violate a Foreign Key Constraint

(Dangling Attribute)

Answer 14

3 Ways to Handle

• Default:
  
  • Reject the modification
• Cascade:
  
  • Update the “downstream” tables as well
  • delete beer > delete “sells” tuple
  • update beer > change value in “sells”
• Set NULL:
  
  • leave tuple, but change value to NULL

Question 15

Enforcing Foreign Key Constraints:

Choosing a Policy

Answer 15

• When declaring a foreign key in SQL, we can choose policies for the database to enforce
• Follow the Foreign Key declaration with:
  
  • ON <operation> <policy></policy></operation>
  • ON [UPDATE/DELETE] [SET NULL/CASCADE]
• May set two of these clauses
• If not specified, the DEFAULT Policy will be set, meaning operations that violate the constraint will be rejected

Question 16

Attribute Based Checks

Answer 16

• Constraint on the value of a particular attribute
• Add CHECK( <condition> )
  </condition><ul>
  <li>to the declaration of attributes</li>
  </ul></condition>
• Conditions may use the name of the attribute
• Attribute based checks are performed only when a value for that attribute is inserted or updated

Example:

CREATE TABLE sells(

bar CHAR(20),

beer CHAR(20),

price REAL CHECK( price <= 5.00 )

);

Question 17

When are Attribute Checks performed?

Answer 17

Only when a value for that attribute is

inserted

or

updated

Question 18

Triggers:

Motivation

Answer 18

Triggers let the user decide

when to check for any condition

Question 19

Triggers:

ECA Rule

Answer 19

Event-Condition-Action Rule

Describes the structure of a trigger:

• Event
  
  • Typically a type of database modification(insert/update/delete)
• Condition
  
  • Any SWL boolean valued expression
• Action
  
  • Any SQL statements

Question 20

Triggers:

Syntax

Answer 20

CREATE TRIGGER trigger_name

(EVENT)

[BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON table

[FOR EACH ROW / FOR EACH STATEMENT] [WHEN condition] (CONDITION)

BEGIN

statements; (ACTION)

END;

Question 21

Triggers:

Possible Events

Answer 21

Can specify:

BEFORE or AFTER,

on the events:

INSERT

UPDATE

or

DELETE

Question 22

Triggers:

Condition

• Basics
• References

Answer 22

Basics:

• A condition can be any boolean valued expression
• If BEFORE is specified:
  
  • Condition is checked before triggering event
• If AFTER is specified:
  
  • Condition is checked after triggering event

References:

• Can access the new or old tuple/table by using the keywords OLD or NEW and the name
  
  • OLD.column_name
  • NEW.table_name

Question 23

Triggers:

Two Types

Answer 23

FOR EACH ROW trigger:

Trigger is fired for every affected tuple/row that is modified

FOR EACH STATEMENT trigger:

The trigger associated with a table is fired one time for each SQL statement,

regardless of how many tuples are modified

Note: SQLite does NOT support the FOR EACH STATEMENT type of trigger