Structured Query Language SQL Section 3 : Structured Query Language SQL Section 3
Reading Assignments : Reading Assignments Database Systems The Complete Book: Chapters 6 and 7
Oracle9 Programming: finish Chapters 2 and 3.5
Following lecture slides are modified from Jeff Ullman’s slides for Fall 2002 -- Stanford
Slide3 : Example Database Dog-Kennel database
Dog(name,age,weight,breed)
Kennel(name,license#,address,phone)
Owner(name, SSN,phone)
Boards(K.name,K.address,D.name,D.breed)
Owns(O.SSN,D.name,D.breed)
Pays(O.SSN, K.name,K.address,amount)
Defining Database Schema : Defining Database Schema Database Schema: declarations for the relations (tables) of the database
Other components:
Views
Indexes
Triggers
Relation : Relation CREATE TABLE
();
DROP TABLE ;
Elements of Table Declarations : Elements of Table Declarations Pairs of: attribute attribute-type
Most common types
INT or INTEGER
REAL or FLOAT
CHAR(n): string of length n
VARCHAR(n): variable length string up to length n
Example : Example CREATE TABLE Dog (
name VARCHAR(20),
age INT,
weight REAL,
breed CHAR(10) );
Dates and Times : Dates and Times DATE and TIME are types in SQL
Forms:
DATE ‘yyyy-mm-dd’
DATE ‘2003-03-18’ for March 18, 2003
TIME ‘hh:mm:ss’ with optional decimal point for second
TIME ’11:00:00’ for 11 am
Declaring Keys : Declaring Keys Attribute (or set of attributes) declared as PRIMARY KEY or UNIQUE
Note, Keys functionally determine all other attributes of the relation schema
Single-Attribute Key : Single-Attribute Key Place PRIMARY KEY or UNIQUE after the type of the attribute in the declaration
Example
CREATE TABLE Owner (
name CHAR (20),
SSN INT UNIQUE,
Phone CHAR (12) );
Multi-Attribute Keys : Multi-Attribute Keys Use key declaration: PRIMARY KEY in the CREATE TABLE statement
Must be used for keys with more than one attribute
Can be used for single-attribute keys too.
Example : Example Dog(name,age,weight,breed)
CREATE TABLE Dog (
name VARCHAR(20),
age INT,
weight REAL,
breed CHAR(10)
PRIMARY KEY (name, breed) );
PRIMARY KEY v.s. UNIQUE : PRIMARY KEY v.s. UNIQUE SQL standards allow DBMS vendors to differentiate between PRIMARY KEY and UNIQUE
For example: create index for PRIMARY KEY but not for UNIQUE
Required Distinctions : Required Distinctions SQL standard requirements
Only 1 PRIMARY KEY for a relations
Several UNIQUE attributes are allowed
PRIMARY KEY attributes cannot be NULL
UNIQUE attributes may be NULL
Other declarations : Other declarations NOT NULL: value for this attribute may never be NULL
DEFAULT , i.e., if no value is know for this attribute’s component, use the default
Example : Example CREATE TABLE Owner (
name CHAR(30) ,
SSN INT PRIMARY KEY,
phone CHAR(16),
DEFAULT ‘999-999-9999’ );
Default Effect 1. : Default Effect 1. Insert tuples where not all attribute values are knows
Insert with a partial list of attributes:
INSERT INTO Owner(name, SSN) VALUES (‘John’, 111223333);
Change Relation Schema : Change Relation Schema Add new attribute to the schema
ALTER TABLE ADD ();
Example:
ALTER TABLE Owner ADD (
address CHAR(20) DEFAULT ‘unknown’);
Change Relation Schema : Change Relation Schema Deleting Attributes from a relation schema
ALTER TABLE
DROP ();
Example:
ALTER TABLE Dog DROP (age);
Modify Database : Modify Database Insert tuple
Delete tuple
Modify tuple
Insert Tuple in Relation : Insert Tuple in Relation Insert into [columns] values (expressions); or
Insert into [columns] ;
Example:
Insert into Dog(name,breed) values (Bella, bulldog);
Insert into G.S.-Dog
select * from Dog where breed=‘G.S.’
Delete Tuples : Delete Tuples Delete from [where ];
Delete all tuples of German Shepherd dogs in the Dog relation.
DELETE FROM Dog
WHERE breed = ’G.S.’
Update Tuple : Update Tuple Update set = [where ];
Change the the 2 year old G.S. dogs’ weight to 72 lb.
UPDATE Dog
SET weight=72
WHERE age=2;
Order is important
Can also use the CASE statement
Views : Views Virtual table – allows to hide certain data from the view of certain users
Relation that is defined in terms of the contents of other relations
CREATE VIEW AS ;
Real relation, stored in the database is called “base table”
Example : Example Owners’ name and phone who owns G.S. dogs
CREATE VIEW G.S.-Owner AS
SELECT name, phone
FROM Owner, Owns
WHERE Owner.SSN = Owns.SSN and
Owns.breed=‘G.S.’ ;
Accessing View : Accessing View May query views as if they were base relations
DBMS starts to interpret the view as a base-relation
DBMS turns any query into relational algebra View defining queries are also replaced by the corresponding relational algebra expression
Constraints and Triggers : Constraints and Triggers
Constraints and Triggers : Constraints and Triggers Constraint:
relationship among data elements
DBMS should enforce the constraints
Example: key constraints
Triggers:
Actions that are executed when a specified condition occurs
Easier to implement than many constraint
Example: insert a tuple
Constraints : Constraints Keys
Foreign-key (referential integrity)
Value-based constraints
Tuple-based constraints
Assertions (SQL boolean expression)
Foreign Keys : Foreign Keys Requires that values for certain attributes must appear in other relations.
Example: Dog(name, breed, Owner) the the value for the owner of the dog must appear in the Owner relation
Foreign keys : Foreign keys Keyword REFERENCES either
Within the declaration of an attribute when only one attribute involved OR
As an element of the schema
Example:
FOREIGN KEY () REFERENCES ();
Referenced attributes must be declared as PRIMARY KEY
Example 1. : Example 1. Dog(name,breed,owner)
CREATE TABLE Owner (
name CHAR (20) PRIMARY KEY,
SSN INT,
phone CHAR (20));
CREATE TABLE Dog (
name CHAR(20) PRIMARY KEY ,
breed CHAR(10),
owner CHAR(20) REFERECES Owner(name) );
Slide33 : Example e. Dog(name,breed,owner)
CREATE TABLE Owner (
name CHAR (20) PRIMARY KEY,
SSN INT,
phone CHAR (20));
CREATE TABLE Dog (
name CHAR(20) PRIMARY KEY ,
breed CHAR(10),
owner CHAR(20),
FOREIGN KEY (owner) REFERECES Owner(name) );
Enforcing Foreign-Key Constraints : Enforcing Foreign-Key Constraints If there is a foreign-key constraints form attributes A1, …, An of a relation R to the primary-key B1, …, Bm of an other relation S, then possible violations:
Insert or update on R may introduce values not in S
Deletion or update on S may remove values needed for tuples in R
Tuples of R without a matching primary-key in S are called dangling tuples
Actions to prevent Foreign-Key Violations : Actions to prevent Foreign-Key Violations Insertions or updates that would create a dangling tuple must be REJECTED
Example: If a new dog is inserted into the Dog relation before the owner’s data is inserted into the Owner relation.
Actions for Deleting/Modifying Tuples Needed for Foreign-Key : Actions for Deleting/Modifying Tuples Needed for Foreign-Key Three possible ways to handle:
Default: REJECT the modification
Cascade: make the same changes in the referencing relation
Set NULL: change the referencing attribute to NULL
Example: Cascade : Example: Cascade Suppose owner ‘Alexandra Smith’ is deleted from Owner relation
Delete all tuples from Dog where the owner attribute value is ‘Alexandra Smith’
Suppose Alexandra Smith wants to update her name to ‘Alexandra Ray’
Change the owner attribute values of all tuples in Dog from ‘Alexandra Smith’ to ‘Alexandra Ray’
Example: Set NULL : Example: Set NULL Suppose owner ‘Alexandra Smith’ is deleted from Owner relation
Change all tuples from Dog where the owner attribute value is ‘Alexandra Smith’ to owner=NULL
Suppose Alexandra Smith wants to update her name to ‘Alexandra Ray’ (same as before)
Change the owner attribute values of all tuples in Dog from ‘Alexandra Smith’ to ‘Alexandra Ray’
Choosing a Policy : Choosing a Policy When declaring a foreign key, policy can be set independently for deletions and updates
If not declared then default is used
Example:
CREATE TABLE Dog (
name CHAR(20) PRIMARY KEY ,
breed CHAR(10),
owner CHAR(20),
FOREIGN KEY (owner) REFERECES Owner(name)
ON DELETE SET NULL);
Attribute-Based Constraints : Attribute-Based Constraints Constraint the value of a particular attribute
CHECK() is added to the declaration of the attribute
Condition may use the name of the attribute or any other relation or attribute name may be in a sub-query
Example : Example CREATE TABLE Dog (
name CHAR(20) PRIMARY KEY ,
breed CHAR(10),
owner CHAR(20) CHECK (owner IN
(SELECT name FROM Owner)),
weight REAL CHECK (0
Timing of Checks : Timing of Checks Attribute value check is checked only when the value of the attribute is inserted or updated
Example:
CHECK (0 < weight AND weight < 120) is verified every time a new weight value is inserted/updated the Dog database
CHECK (owner IN (SELECT name FROM Owner)) is not checked when an owner is deleted from Owner – NOT LIKE FOREIGN KEY
Tuple-Based Checks : Tuple-Based Checks Check () may be added during schema definition
Condition may refer to any attribute of the relation but other relations and their attributes require sub-queries
Checked during insert or update
Example : Example CREATE TABLE Dog (
name CHAR(20) PRIMARY KEY ,
breed CHAR(10),
owner CHAR(20),
weight REAL,
CHECK (owner = ‘Alexandra Smith’ OR
breed = ‘G.S.’) );
Assertions : Assertions Holds on database-schema elements like relations and views
Must always be true
Condition may refer to any relation or attribute in the database schema
CREATE ASSERTION CHECK ();
Example : Example In Dog relation(name, breed, weight, owner) tiny dogs selected
CREATE ASSERTION TinyDogs
CHECK NOT EXISTS (
SELECT name
FROM Dog
WHERE weight < 12 );
Example 2 : Example 2 In Owners and Dogs cannot be more owners than dogs.
CREATE ASSERTION Few-owners CHECK (
(SELECT COUNT (*) FROM Owner) <=
(SELECT COUNT (*) FROM Dog) );
Timing Assertion : Timing Assertion In general, check every assertion after every modification to any relation of the database
Clever system: only certain changes can cause a given assertion to be violated check only after these changes
Triggers : Triggers Attribute and tuple-based checks: limited
Assertions: general and powerful but difficult to implement efficiently
Triggers:
Allows the user to specify when the check occurs.
General purpose conditions and sequence of SQL database modifications
Triggers : Triggers Also called event-condition-action (ECA) rules
Event: typically a type of database modification
Condition: and SQL boolean-valued expression
Action: any SQL statement
Example : Example Instead of using foreign-key constraints to reject an insertion of a dog into Dog if the owner is not present in Owner, use trigger to insert the same owner into Owner with NULL for phone and SSN.
CREATE TRIGGER OWNR
AFTER INSERT ON Dog
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.owner NOT IN
(SELECT name FROM Owner))
INSERT INTO Owner(name)
VALUES(NewTuple.owner);
Options: Create Trigger : Options: Create Trigger CREATE TRIGGER
Option:
CREATE OR REPLACE TRIGGER
useful to modify existing trigger CREATE TRIGGER OWNR
AFTER INSERT ON Dog
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.owner NOT IN
(SELECT name FROM Owner))
INSERT INTO Owner(name)
VALUES(NewTuple.owner);
Options: Condition : Options: Condition AFTER can be BEFORE or
INSTEAD OF for views (can be used to execute view modifications and translate them to modifications on the base relations
INSERT and be DELETE or UPDATE or UPDATE … ON a particular attribute
CREATE TRIGGER OWNR
AFTER INSERT ON Dog
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.owner NOT IN
(SELECT name FROM Owner))
INSERT INTO Owner(name)
VALUES(NewTuple.owner);
Options: For Each Row : Options: For Each Row Triggers:
Row-level
Statement-level
FOR EACH ROW indicates row-level, its absence indicates statement-level
Row-level triggers executed once for each modified tuple
Statement-level triggers executed once for an SQL statement, regardless of the number of modified tuples CREATE TRIGGER OWNR
AFTER INSERT ON Dog
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.owner NOT IN
(SELECT name FROM Owner))
INSERT INTO Owner(name)
VALUES(NewTuple.owner);
Options: Referencing : Options: Referencing INSERT statement implies a new tuple (row-level) or a new set of tuples (statement-level)
DELETE implies and old tuple or table
UPDATE implies both
Format of reference:
[NEW OLD] [TUPLE TABLE] AS CREATE TRIGGER OWNR
AFTER INSERT ON Dog
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.owner NOT IN
(SELECT name FROM Owner))
INSERT INTO Owner(name)
VALUES(NewTuple.owner);
Options: Condition : Options: Condition Any boolean-valued condition is appropriate
Eveluated before or after the triggering event, depending on whether BEFORE or AFTER was used
Access the new/old tuples or set of tuples through names declared in the REFERENCING clause CREATE TRIGGER OWNR
AFTER INSERT ON Dog
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.owner NOT IN
(SELECT name FROM Owner))
INSERT INTO Owner(name)
VALUES(NewTuple.owner);
Options: Action : Options: Action More than one SQL statements are allowed in action
Surround be BEGIN … END if there is more than one
Action: modification CREATE TRIGGER OWNR
AFTER INSERT ON Dog
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.owner NOT IN
(SELECT name FROM Owner))
INSERT INTO Owner(name)
VALUES(NewTuple.owner);
Triggers on Views : Triggers on Views Generally, it is impossible to modify a view because it does not exist
INSTEAD OF trigger lets us interpret view modifications
Example 1. : Example 1. Owner(name,phone,address)
Owns(O.name,D.name,D.breed)
Dog(name,age,weight,breed)
Create a view with the owner’s name and phone, and the dog’s name and weight
CREATE VIEW dog-and-owner AS
(SELECT o.name, o.phone,d.name, weight
FROM Owner o, Owns, Dog d
WHERE o.name = O.name and
d.name = D.name )
Example 2. : Example 2. CREEATE TRIGGER View-Update
INSTEAD OF INSERT ON dog-and-owner
REFERENCING NEW ROW AS n
FOR EACH ROW
BEGIN
INSERT INTO Dog(name,weight) VALUES(n.d.name, weight);
INSERT INTO OWNER(name,phone) VALUES(n.o.name, n.o.phone);
INSERT INTO OWNS(O.name,D.name) VALUES(n.o.name, n.o.phone);
END;
Query Optimization : Query Optimization After transforming an SQL query to relational algebra, the expression if replaced with an that is equivalent but can be executed faster.
Key optimizations:
Push selection down the tree
Eliminate unnecessary projections
Updating ....
Catch the
buzz on authorSTREAM
Copyright © 2002-2008 authorSTREAM. All rights reserved.
Updating ....
|
Send to Blogs and Networks |
 |