Structured Query Language SQL Section 3

1
Structured Query LanguageSQLSection 3
2
Reading Assignments

• Database Systems The Complete Book finish
  Chapters 6
• Oracle8 Programming finish Chapters 2
• Following lecture slides are modified from Jeff
  Ullmans slides for Fall 2002 -- Stanford

3
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)

4
Defining Database Schema

• Database Schema declarations for the relations
  (tables) of the database
• Other components
• Views
• Indexes
• Triggers

5
Relation

• CREATE TABLE
• ()
• DROP TABLE

6
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

7
Example

• CREATE TABLE Dog (
• name VARCHAR(20),
• age INT,
• weight REAL,
• breed CHAR(10) )

8
Dates and Times

• DATE and TIME are types in SQL
• Forms
• DATE yyyy-mm-dd
• DATE 2003-03-18 for March 18, 2003
• TIME hhmmss with optional decimal point for
  second
• TIME 110000 for 11 am

9
Declaring Keys

• Attribute (or set of attributes) declared as
  PRIMARY KEY or UNIQUE
• Note, Keys functionally determine all other
  attributes of the relation schema

10
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) )

11
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.

12
Example

• Dog(name,age,weight,breed)
• CREATE TABLE Dog (
• name VARCHAR(20),
• age INT,
• weight REAL,
• breed CHAR(10)
• PRIMARY KEY (name, breed) )

13
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

14
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

15
Other declarations

• NOT NULL value for this attribute may never be
  NULL
• DEFAULT , i.e., if no value is know for
  this attributes component, use the default
  

16
Example

• CREATE TABLE Owner (
• name CHAR(30) ,
• SSN INT PRIMARY KEY,
• phone CHAR(16),
• DEFAULT 999-999-9999 )

17
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)

18
Change Relation Schema

• Add new attribute to the schema
• ALTER TABLE ADD ()
• Example
• ALTER TABLE Owner ADD (
• address CHAR(20) DEFAULT unknown)

19
Change Relation Schema

• Deleting Attributes from a relation schema
• ALTER TABLE
• DROP ()
• Example
• ALTER TABLE Dog DROP (age)

20
Modify Database

• Insert tuple
• Delete tuple
• Modify tuple

21
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 breedG.S.

22
Delete Tuples

• Delete from where
• Delete all tuples of German Shepherd dogs in the
  Dog relation.
• DELETE FROM Dog
• WHERE breed G.S.

23
Update Tuple

• Update set where
  
• Change the the 2 year old G.S. dogs weight to 72
  lb.
• UPDATE Dog
• SET weight72
• WHERE age2
• Order is important
• Can also use the CASE statement

24
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

25
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.breedG.S.

26
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

27
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