L7: Modifying Databases Flashcards
Database Insertions:
Insert Multiple Tuples from another Table
(SQL)
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
Database Insertions:
General Form
INSERT INTO R(A1, …, An)
VALUES (V1, …, Vn)
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’)
Database Deletions:
General Form
DELETE FROM table_name
WHERE <condition></condition>
Example:
DELETE FROM Purchase
WHERE seller = ‘Joe’ AND
product = ‘Brooklyn Bridge’
Database Update:
General Form
UPDATE table_name
SET column1 = value1, col2 = value2
WHERE some_column = some_value;
Views:
Definition
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.
Database
View Creation
- general form
- example
CREATE VIEW view_name AS <query></query>
Example:
CREATE VIEW Developers AS
SELECT name, project
FROM Employee
WHERE department = “Development”
Updating Views
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)
Constraints
vs
Triggers
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
Kinds of Constraints
- Keys
- Foreign-Keys
- or Referential-Integrity
- Value based constraints
- Constrains values of a particular attribute
Constraints:
Defining Single Attribute Keys
and
Multiple Attribute Keys
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) );
Constraints:
Defining Foreign Keys
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
Enforcing Foreign Key Constraints:
Two Possible Violations
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)
Enforcing Foreign Key Constraints:
Handling an insertion/update violation of a
Foreign Key Constraint
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.
Enforcing Foreign Key Constraints:
Handling Deletion/Update operations that
violate a Foreign Key Constraint
(Dangling Attribute)
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
Enforcing Foreign Key Constraints:
Choosing a Policy
- 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
Attribute Based Checks
- 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 )
);
When are Attribute Checks performed?
Only when a value for that attribute is
inserted
or
updated
Triggers:
Motivation
Triggers let the user decide
when to check for any condition
Triggers:
ECA Rule
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
Triggers:
Syntax
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;
Triggers:
Possible Events
Can specify:
BEFORE or AFTER,
on the events:
INSERT
UPDATE
or
DELETE
Triggers:
Condition
- Basics
- References
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
Triggers:
Two Types
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
