[SOLVED] ER database SQL PowerPoint Presentation

PowerPoint Presentation

M
a

g
n

u
s

W
a
h

ls
tr

m

2

0
1

4
/1

5

Magnus Wahlstrm
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:timestamp2005-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 as date

Example: cast as time

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_namechar(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_namechar(15),
branch_citychar(30),
assetsinteger,
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 check

When an assertion is made, the system tests it for
validity, and tests it again on every update that may
violate the assertion
This testing may introduce a significant amount of overhead; hence

assertions should be used with great care.

M
a

g
n

u
s

W
a
h

ls
tr

m

2

0
1

4
/1

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
a

g
n

u
s

W
a
h

ls
tr

m

2

0
1

4
/1

5

19SQL IntermediateBI5631 Database Systems

The grant statement is used to confer authorization

grant on to

is:
a user-id
public, which allows all valid users the privilege granted
A role

Granting a privilege on a view does not imply granting
any privileges on the underlying relations.

The grantor of the privilege must already hold the
privilege on the specified item (or be the database
administrator).

M
a

g
n

u
s

W
a
h

ls
tr

m

2

0
1

4
/1

5

20SQL IntermediateBI5631 Database Systems

The revoke statement is used to revoke authorization:

revoke on from

Example: revoke select on branchfrom U1, U2, U3

may be all to revoke all privileges the revokee may
hold.

If includes public, all users lose the privilege except
those granted it explicitly.

If the same privilege was granted twice to the same user by different
grantees, the user may retain the privilege after the revocation.

All privileges that depend on the privilege being revoked are also
revoked.

Slide 1
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