Chapter 4: Advanced SQL

Prepared By: Dr. Shalini Bhaskar Bajaj

 Chapter 4: Advanced SQL
n SQL Data Types
n Integrity Constraints
n Authorization

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Built-in Data Types in SQL
n date: Dates, containing a (4 digit) year, month and date
l Example: date ‘2005-7-27’
n time: Time of day, in hours, minutes and seconds.
l Example: time ‘09:00:30’ time ‘09:00:30.75’
n timestamp: date plus time of day
l Example: timestamp ‘2005-7-27 09:00:30.75’
n interval: period of time
l Example: interval ‘1’ day
l Subtracting a date/time/timestamp value from another gives an interval
value
l Interval values can be added to date/time/timestamp values

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 User-Defined Types
n create type construct in SQL creates user-defined type

create type Dollars as numeric (12,2) final

n create domain construct in SQL-92 creates user-defined domain types

create domain person_name char(20) not null

n Types and domains are similar. Domains can have constraints, such as
not null, specified on them.

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Domain Types in SQL
n char(n). Fixed length character string, with user-specified length n.
n Varchar (n). Variable length character strings, with user-specified maximum
length n.
n int. Integer (a finite subset of the integers that is machine-dependent).
n smallint. Small integer (a machine-dependent subset of the integer domain
type).
n numeric(p,d). Fixed point number, with user-specified precision of p digits,
with n digits to the right of decimal point.
n real, double precision. Floating point and double-precision floating point
numbers, with machine-dependent precision.
n float(n). Floating point number, with user-specified precision of at least n
digits.

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Domain Constraints
n Domain constraints are the most elementary form of integrity constraint.
They test values inserted in the database, and test queries to ensure that
the comparisons make sense.
n New domains can be created from existing data types
l Example: create domain Dollars numeric(12, 2)
create domain Pounds numeric(12,2)
n We cannot assign or compare a value of type Dollars to a value of type
Pounds.

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 User-Defined Types
n create table account
(account_number char(10),
branch_name char(15),
balance Dollars);

An attempt to compare a value of type dollar with a value of type

pound will give compile time error

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Large-Object Types
n Large objects (photos, videos, CAD files, etc.) are stored as a large
object:
l 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)
l clob: character large object -- object is a large collection of character
data
l When a query returns a large object, a pointer is returned rather than
the large object itself.

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Integrity Constraints
n 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.
l A checking account must have a balance greater than $10,000.00
l A salary of a bank employee must be at least $4.00 an hour
l A customer must have a (non-null) phone number

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Constraints on a Single Relation

n not null
n primary key
n unique
n check (P ), where P is a predicate

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Not Null Constraint

n Declare branch_name for branch is not null

branch_name char(15) not null

n Declare the domain Dollars to be not null

create domain Dollars numeric(12,2) not null

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 The Unique Constraint

n unique ( A1, A2, …, Am)

n The unique specification states that the attributes

A1, A2, … Am
form a candidate key.
n Candidate keys are permitted to be null (in contrast to primary keys).

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 The check clause
n check (P ), where P is a predicate

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

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 The check clause (Cont.)
n The check clause in SQL-92 permits domains to be restricted:
l Use 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)
l The domain has a constraint that ensures that the hourly_wage is
greater than 4.00
l The clause constraint value_test is optional; useful to indicate which
constraint an update violated.

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Referential Integrity
n 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.
l 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”.
n Primary and candidate keys and foreign keys can be specified as part of the
SQL create table statement:
l The primary key clause lists attributes that comprise the primary key.
l The unique key clause lists attributes that comprise a candidate key.
l 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.

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Referential Integrity in SQL – 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 ))

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Referential Integrity in SQL – Example (Cont.)

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 )

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Authorization
Forms of authorization on parts of the database:
n Read - allows reading, but not modification of data.

n Insert - allows insertion of new data, but not modification of existing data.
n Update - allows modification, but not deletion of data.
n Delete - allows deletion of data.

Forms of authorization to modify the database schema :

n Index - allows creation and deletion of indices.
n Resources - allows creation of new relations.
n Alteration - allows addition or deletion of attributes in a relation.
n Drop - allows deletion of relations.

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Authorization Specification in SQL
n The grant statement is used to confer authorization
grant <privilege list>
on <relation name or view name> to <user list>
n <user list> is:
l a user-id
l public, which allows all valid users the privilege granted
l A role
n Granting a privilege on a view does not imply granting any privileges on
the underlying relations.
n The grantor of the privilege must already hold the privilege on the
specified item (or be the database administrator).

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Privileges in SQL
n select: allows read access to relation,or the ability to query using the
view
l Example: grant users U1, U2, and U3 select authorization on the
branch relation:
grant select on branch to U1, U2, U3
n insert: the ability to insert tuples
n update: the ability to update using the SQL update statement
n delete: the ability to delete tuples.
n all privileges: used as a short form for all the allowable privileges

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 Revoking Authorization in SQL
n The revoke statement is used to revoke authorization.
revoke <privilege list>
on <relation name or view name> from <user list>
n Example:
revoke select on branch from U1, U2, U3
n All privileges that depend on the privilege being revoked are also
revoked.
n <privilege-list> may be all to revoke all privileges the revokee may hold.
n If the same privilege was granted twice to the same user by different
grantees, the user may retain the privilege after the revocation.

Prepared By: Dr. Shalini Bhaskar Bajaj

4.
 End of Chapter

Prepared By: Dr. Shalini Bhaskar Bajaj