PowerPoint Presentation
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
Magnus Wahlström
Department Of Computer Science
McCrea – 120A
6. SQL – Intermediate
BI5631 – Database Systems
Slides adapted from Database System Concepts, 6th Edition
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
2SQL – IntermediateBI5631 – Database Systems
Overview of (most of ) the Course
Text description ER diagram
Relational ModelSQL
Relational Algebra
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
3SQL – IntermediateBI5631 – Database Systems
Built-in Data Types in SQL
1. date: Dates, containing a (4 digit) year, month and date
• Example: date ‘2005-7-27’
2. time: Time of day, in hours, minutes and seconds.
• Example: time ‘09:00:30’ time ‘09:00:30.75’
3. timestamp: date plus time of day
• Example: timestamp ‘2005-7-27 09:00:30.75’
4. interval: period of time
• Subtracting a date/time/timestamp value from another gives an interval value
• Interval values can be added to date/time/timestamp values
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
4SQL – IntermediateBI5631 – Database Systems
• Can extract values of individual fields from
date/time/timestamp
• Example: extract (year from r.starttime)
• Can cast string types to date/time/timestamp
• Example: cast
• Example: cast
• SQL allows comparisons on all these types
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
5SQL – IntermediateBI5631 – Database Systems
User-Defined Types
• It is possible for different attributes to have the same
domain but sometimes, conceptually, they are distinct
This fact can be used and be very helpful for avoiding
errors
• create type construct in SQL creates user-defined type
create type Dollars as numeric (12,2) final
• We cannot assign or compare a value of type Dollars to a value of
type Pounds.
• However, we can convert type with a cast expression
cast (account.balance to numeric(12,2))
• Drop type and alter type clauses are used for dropping or
modifying types
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
6SQL – IntermediateBI5631 – Database Systems
User defined Domains
• Domain constraints are the most elementary form of
integrity constraint. They test values inserted in the
database.
• New domains can be created from existing data types
• Example: create domain Dollars numeric(12, 2)
create domain Pounds numeric(12,2)
• Types and domains are similar.
• Domains are not strongly typed – values in one domain can be
assigned to values in another domain as long as the underlying
types are compatible
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
7SQL – IntermediateBI5631 – Database Systems
Large-Object Types
• Large objects (photos, videos, CAD files, etc.) are
stored as a large object:
• blob: binary large object — object is a large collection of
uninterpreted binary data (whose interpretation is left to an
application outside of the database system)
• clob: character large object — object is a large collection
of character data
• When a query returns a large object, a pointer is
returned rather than the large object itself.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
8SQL – IntermediateBI5631 – Database Systems
Integrity Constraints
Integrity constraints guard against accidental damage
to the database, by ensuring that authorized changes
to the database do not result in a loss of data
consistency.
• A checking account must have a balance greater than $10,000.00
• A salary of a bank employee must be at least $4.00 an hour
• A customer must have a (non-null) phone number
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
9SQL – IntermediateBI5631 – Database Systems
Constraints on a Single Relation
• not null
• primary key
• unique
• check (P ), where P is a predicate
These integrity-constraints statements can be included in
the create table (and some in the create domain)
command.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
10SQL – IntermediateBI5631 – Database Systems
1. Not Null Constraint
• The null value is a member of all domains, and as a
result by default it is a legal value for any attribute in
SQL
• Declare branch_name for branch is not null
branch_name char(15) not null
• Declare the domain Dollars to be not null
create domain Dollars numeric(12,2) not null
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
11SQL – IntermediateBI5631 – Database Systems
2. The Unique Constraint
• unique ( A1, A2, …, Am)
• The unique specification states that the attributes
A1, A2, … Am
form a candidate key: no two tuples in the relation can
be equal on all of them.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
12SQL – IntermediateBI5631 – Database Systems
3. The check clause
When applied to a relation declaration, the
check(P) clause specifies a predicate P that must
be satisfied by every tuple in the relation
1. Check in the create table command
Example: declare branch_name as the primary key
for branch and ensure that the values of assets are
non-negative
create table branch
(branch_name char(15),
branch_city char(30),
assets integer,
primary key (branch_name),
check (assets >= 0))
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
13SQL – IntermediateBI5631 – Database Systems
2. Check in the create domain command
Example: use the check clause to ensure that an
hourly_wage domain allows only values greater than a
specified value.
create domain hourly_wage numeric(5,2)
constraint value_test check(value > = 4.00)
• The domain has a constraint that ensures that the hourly_wage is
greater than 4.00
• The clause constraint value_test assigns a name to the constraint
• The check clause permits both attributes and
domains to be restricted
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
14SQL – IntermediateBI5631 – Database Systems
Referential Integrity
Ensures that a value that appears in one relation for a
given set of attributes also appears for a certain set of
attributes in another relation
• Example: If “Perryridge” is a branch name appearing in one of the
tuples in the account relation, then there exists a tuple in the branch
relation for branch “Perryridge”.
• Primary and candidate keys and foreign keys can be
specified as part of the SQL create table statement:
• The primary key clause lists attributes that comprise the primary key.
• The foreign key clause lists the attributes that comprise the foreign key
and the name of the relation referenced by the foreign key. By default, a
foreign key references the primary key attributes of the referenced table.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
15SQL – IntermediateBI5631 – Database Systems
Example
create table customer
(customer_name char(20),
customer_street char(30),
customer_city char(30),
primary key (customer_name ))
create table branch
(branch_name char(15),
branch_city char(30),
assets
numeric(12,2),
primary key (branch_name ))
create table account
(account_number char(10),
branch_name char(15),
balance integer,
primary key (account_number),
foreign key (branch_name) references branch )
create table depositor
(customer_name char(20),
account_number char(10),
primary key (customer_name, account_number),
foreign key (account_number ) references account,
foreign key (customer_name ) references customer )
It specifies that for each account tuple,
the branch name specified in the tuple
must exist in the branch relation.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
16SQL – IntermediateBI5631 – Database Systems
• When a referential integrity constraint is violated, the
normal procedure is to reject the action that caused
the violation. Other solutions are also possible…
• Integrity constraints can be added to an existing
relation by using the command:
alter table table_name add constraint
In this case the system first ensures that the relation
satisfies the new constraint. If it does, the constraint
is added.
M
a
g
n
u
s
W
a
h
ls
tr
ö
m
–
2
0
1
4
/1
5
17SQL – IntermediateBI5631 – Database Systems
Assertions
An assertion is a predicate expressing a condition that
we wish the database always to satisfy.
[the sum of all loans at the branch must be less than the sum of all
account balances]
create assertion • When an assertion is made, the system tests it for assertions should be used with great care. M g u W ls ö – 0 4 5 18SQL – IntermediateBI5631 – Database Systems Authorization Forms of authorizations on parts of the database, called privileges: • Select – allows reading, but not modification of data. • Insert – allows insertion of new data, but not modification of existing data • Update – allows modification, but not deletion of data. • Delete – allows deletion of data Forms of authorization to modify the database schema: • Resources – allows creation of new relations. • Alteration – allows addition or deletion of attributes in a relation. • Drop – allows deletion of relations. • All privileges M g u W ls ö – 0 4 5 19SQL – IntermediateBI5631 – Database Systems • The grant statement is used to confer authorization grant • • Granting a privilege on a view does not imply granting • The grantor of the privilege must already hold the M g u W ls ö – 0 4 5 20SQL – IntermediateBI5631 – Database Systems • The revoke statement is used to revoke authorization: revoke Example: revoke select on branch from U1, U2, U3 • • If • If the same privilege was granted twice to the same user by different • All privileges that depend on the privilege being revoked are also Slide 1
validity, and tests it again on every update that may
violate the assertion
• This testing may introduce a significant amount of overhead; hence
a
n
s
a
h
tr
m
2
1
/1
a
n
s
a
h
tr
m
2
1
/1
• a user-id
• public, which allows all valid users the privilege granted
• A role
any privileges on the underlying relations.
privilege on the specified item (or be the database
administrator).
a
n
s
a
h
tr
m
2
1
/1
hold.
those granted it explicitly.
grantees, the user may retain the privilege after the revocation.
revoked.
Slide 2
Slide 3
Slide 4
Slide 5
Slide 6
Slide 7
Slide 8
Slide 9
Slide 10
Slide 11
Slide 12
Slide 13
Slide 14
Slide 15
Slide 16
Slide 17
Slide 18
Slide 19
Slide 20