[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 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 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