Chapter 1: Introduction

 Purpose of Database Systems

 View of Data
 Data Models
 Data Definition Language
 Data Manipulation Language
 Transaction Management
 Storage Management
 Database Administrator
 Database Users
 Overall System Structure
 DBMS Vs. IRS

Database System Concepts 1.1 ©Silberschatz, Korth and Sudarshan

Database Management System (DBMS)

 Collection of interrelated data

 Set of programs to access the data
 DBMS contains information about a particular enterprise
 DBMS provides an environment that is both convenient
and efficient to use.
 Database Applications:
 Banking: all transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
 Databases touch all aspects of our lives

Database System Concepts 1.2 ©Silberschatz, Korth and Sudarshan

1
 Purpose of Database System

 In the early days, database applications were built on top

of file systems
 Drawbacks of using file systems to store data:
 Data redundancy and inconsistency
 Multiple file formats, duplication of information in different files
 Difficulty in accessing data
 Need to write a new program to carry out each new task
 Data isolation — multiple files and formats
 Integrity problems
 Integrity constraints (e.g. account balance > 0) become part
of program code
 Hard to add new constraints or change existing ones

Database System Concepts 1.3 ©Silberschatz, Korth and Sudarshan

Purpose of Database Systems (Cont.)

 Drawbacks of using file systems (cont.)

 Atomicity of updates
 Failures may leave database in an inconsistent state with partial
updates carried out
 E.g. transfer of funds from one account to another should either
complete or not happen at all
 Concurrent access by multiple users
 Concurrent accessed needed for performance
 Uncontrolled concurrent accesses can lead to inconsistencies
– E.g. two people reading a balance and updating it at the same
time
 Security problems
 Database systems offer solutions to all the above problems

Database System Concepts 1.4 ©Silberschatz, Korth and Sudarshan

2
 Is the WWW a DBMS?

 Fairly sophisticated search available

 crawler indexes pages for fast search
 But, currently
 data is mostly unstructured and untyped
 can’t manipulate the data
 few guarantees provided for freshness of data,
consistency across data items, fault tolerance, …
 Web sites typically have a DBMS in the
background to provide these functions.
 The picture is quickly changing
 New standards like XML can help data modeling
 Research groups are working on providing some of this functionality
across multiple web sites.

Database System Concepts 1.5 ©Silberschatz, Korth and Sudarshan

Is a File System a DBMS?

 Thought Experiment 1:
 You and your project partner are editing the same file.
 You both save it at the same time.
 Whose changes survive?
A) Yours B) Partner’s C) Both D) Neither E) ???

 Thought Experiment 2:
 You’re updating a file.
 The power goes out.
 Which of your changes survive?
A) All B) None C) All Since last save D) ???

Database System Concepts 1.6 ©Silberschatz, Korth and Sudarshan

3
 Levels of Abstraction

 Physical level describes how a record (e.g., customer) is

stored.
 Logical level: describes data stored in database, and the
relationships among the data.
type customer = record
name : string;
street : string;
city : integer;
end;
 View level: application programs hide details of data types.
Views can also hide information (e.g., salary) for security
purposes.

Database System Concepts 1.7 ©Silberschatz, Korth and Sudarshan

View of Data
An architecture for a database system

Database System Concepts 1.8 ©Silberschatz, Korth and Sudarshan

4
 Instances and Schemas
 Similar to types and variables in programming languages
 Schema – the logical structure of the database
 e.g., the database consists of information about a set of customers and
accounts and the relationship between them)
 Analogous to type information of a variable in a program
 Physical schema: database design at the physical level
 Logical schema: database design at the logical level
 Instance – the actual content of the database at a particular point
in time
 Analogous to the value of a variable
 Physical Data Independence – the ability to modify the physical
schema without changing the logical schema
 Applications depend on the logical schema
 In general, the interfaces between the various levels and components should
be well defined so that changes in some parts do not seriously influence others.

Database System Concepts 1.9 ©Silberschatz, Korth and Sudarshan

Data Models

 A collection of tools for describing

 data
 data relationships
 data semantics
 data constraints
 Entity-Relationship model
 Relational model
 Other models:
 object-oriented model
 semi-structured data models

 Older models: network model and hierarchical model

Database System Concepts 1.10 ©Silberschatz, Korth and Sudarshan

5
 Entity-Relationship Model

Example of schema in the entity-relationship model

Database System Concepts 1.11 ©Silberschatz, Korth and Sudarshan

Entity Relationship Model (Cont.)

 E-R model of real world

 Entities (objects)
 E.g. customers, accounts, bank branch
 Relationships between entities
 E.g. Account A-101 is held by customer Johnson
 Relationship set depositor associates customers with accounts

 Widely used for database design

 Database design in E-R model usually converted to design in the
relational model (coming up next) which is used for storage and
processing

Database System Concepts 1.12 ©Silberschatz, Korth and Sudarshan

6
 Relational Model
Attributes
 Example of tabular data in the relational model

Customer-id customer- customer- customer- account-

name street city number

192-83-7465 Johnson
Alma Palo Alto A-101
019-28-3746 Smith
North Rye A-215
192-83-7465 Johnson
Alma Palo Alto A-201
321-12-3123 Jones
Main Harrison A-217
019-28-3746 Smith
North Rye A-201

Database System Concepts 1.13 ©Silberschatz, Korth and Sudarshan

A Sample Relational Database

Database System Concepts 1.14 ©Silberschatz, Korth and Sudarshan

7
 Data Definition Language (DDL)

 Specification notation for defining the database schema

 E.g.
create table account (
account-number char(10),
balance integer)
 DDL compiler generates a set of tables stored in a data
dictionary
 Data dictionary contains metadata (i.e., data about data)
 database schema
 Data storage and definition language
 language in which the storage structure and access methods
used by the database system are specified
 Usually an extension of the data definition language

Database System Concepts 1.15 ©Silberschatz, Korth and Sudarshan

Data Manipulation Language (DML)

 Language for accessing and manipulating the data

organized by the appropriate data model
 DML also known as query language
 Two classes of languages
 Procedural – user specifies what data is required and how to get
those data
 Nonprocedural – user specifies what data is required without
specifying how to get those data
 SQL is the most widely used query language

Database System Concepts 1.16 ©Silberschatz, Korth and Sudarshan

8
 SQL
 SQL: widely used non-procedural language
 E.g. find the name of the customer with customer-id 192-83-7465
select [Link]-name
from customer
where [Link]-id = ‘192-83-7465’
 E.g. find the balances of all accounts held by the customer with
customer-id 192-83-7465
select [Link]
from depositor, account
where [Link]-id = ‘192-83-7465’ and
[Link]-number = [Link]-number
 Application programs generally access databases through
 Language extensions to allow embedded SQL
 Application program interface (e.g. ODBC/JDBC) which allow SQL
queries to be sent to a database

Database System Concepts 1.17 ©Silberschatz, Korth and Sudarshan

Database Users
 Users are differentiated by the way they expect to interact
with the system
 Application programmers – interact with system through
DML calls
 Sophisticated users – form requests in a database query
language
 Specialized users – write specialized database
applications that do not fit into the traditional data
processing framework
 Naïve users – invoke one of the permanent application
programs that have been written previously
 E.g. people accessing database over the web, bank tellers, clerical
staff

Database System Concepts 1.18 ©Silberschatz, Korth and Sudarshan

9
 Database Administrator

 Coordinates all the activities of the database

system; the database administrator has a good
understanding of the enterprise’s information
resources and needs.
 Database administrator's duties include:
 Schema definition
 Storage structure and access method definition
 Schema and physical organization modification
 Granting user authority to access the database
 Specifying integrity constraints
 Acting as liaison with users
 Monitoring performance and responding to changes in
requirements

Database System Concepts 1.19 ©Silberschatz, Korth and Sudarshan

Transaction Management

 A transaction is a collection of operations that performs a

single logical function in a database application
 Transaction-management component ensures that the
database remains in a consistent (correct) state despite
system failures (e.g., power failures and operating system
crashes) and transaction failures.
 Concurrency-control manager controls the interaction
among the concurrent transactions, to ensure the
consistency of the database.

Database System Concepts 1.20 ©Silberschatz, Korth and Sudarshan

10
 Storage Management

 Storage manager is a program module that provides the

interface between the low-level data stored in the database
and the application programs and queries submitted to the
system.
 The storage manager is responsible to the following tasks:
 interaction with the file manager
 efficient storing, retrieving and updating of data

Database System Concepts 1.21 ©Silberschatz, Korth and Sudarshan

Overall System Structure

Database System Concepts 1.22 ©Silberschatz, Korth and Sudarshan

11
 Application Architectures

Two-tier architecture: E.g. client programs using ODBC/JDBC to

communicate with a database
Three-tier architecture: E.g. web-based applications, and
applications built using “middleware”

Database System Concepts 1.23 ©Silberschatz, Korth and Sudarshan

Advantages of a DBMS

 Data independence
 Efficient data access
 Data integrity & security
 Data administration
 Concurrent access, crash recovery
 Reduced application development time

 So why not use them always?

 Expensive/complicated to set up & maintain
 This cost & complexity must be offset by need
 General-purpose, not suited for special-purposetasks (e.g. text
search!)

Database System Concepts 1.24 ©Silberschatz, Korth and Sudarshan

12
 DBMS vs. IRS
Distribution of selected information

DBMS: The entities are

uniquely and completely
described by its
attributes. Retrieval

IRS: The number of

content identifiers can
be very large and they
do not describe the Filtering
information uniquely
and completely.
Users Information

Database System Concepts 1.25 ©Silberschatz, Korth and Sudarshan

Search vs. Retrieval

DBMS: Strict matching between the Give me all

query and the information about ...
identifiers. Query
Identifiers
IRS: Degree of similarity between
the query and information
identifiers. similariey estimation
Content
Identifiers n
Content
Identifiers k
Document n
Content
Content Identifiers 2 Document k
Identifiers 1 .
Document 2
..
.
Document 1
..

Database System Concepts 1.26 ©Silberschatz, Korth and Sudarshan

13
 13.10.2011

Chapter 2: Entity-Relationship Model

 Entity Sets
 Relationship Sets
 Design Issues
 Mapping Constraints
 Keys
 E-R Diagram
 Extended E-R Features
 Design of an E-R Database Schema
 Reduction of an E-R Schema to Tables

Database System Concepts 2.1 ©Silberschatz, Korth and Sudarshan

Entity Sets

 A database can be modeled as:

 a collection of entities,
 relationship among entities.
 An entity is an object that exists and is distinguishable from
other objects.
 Example: specific person, company, event, plant
 Entities have attributes
 Example: people have names and addresses
 An entity set is a set of entities of the same type that share
the same properties.
 Example: set of all persons, companies, trees, holidays

Database System Concepts 2.2 ©Silberschatz, Korth and Sudarshan

1
 13.10.2011

Entity Sets customer and loan

customer-id customer- customer- customer- loan- amount
name street city number

Database System Concepts 2.3 ©Silberschatz, Korth and Sudarshan

Attributes
 An entity is represented by a set of attributes, that is
descriptive properties possessed by all members of an
entity set.
Example:
customer = (customer-id, customer-name,
customer-street, customer-city)
loan = (loan-number, amount)
 Domain – the set of permitted values for each attribute
 Attribute types:
 Simple and composite attributes.
 Single-valued and multi-valued attributes
 E.g. multivalued attribute: phone-numbers
 Derived attributes
 Can be computed from other attributes
 E.g. age, given date of birth

Database System Concepts 2.4 ©Silberschatz, Korth and Sudarshan

2
 13.10.2011

Composite Attributes

Database System Concepts 2.5 ©Silberschatz, Korth and Sudarshan

Relationship Sets

 A relationship is an association among several entities

Example:
Hayes depositor A-102
customer entity relationship set account entity
 A relationship set is a mathematical relation among n  2
entities, each taken from entity sets
{(e1, e2, … en) | e1  E1, e2  E2, …, en  En}

where (e1, e2, …, en) is a relationship

 Example:
(Hayes, A-102)  depositor

Database System Concepts 2.6 ©Silberschatz, Korth and Sudarshan

3
 13.10.2011

Relationship Set borrower

Database System Concepts 2.7 ©Silberschatz, Korth and Sudarshan

Relationship Sets (Cont.)

 An attribute can also be property of a relationship set.
 For instance, the depositor relationship set between entity sets
customer and account may have the attribute access-date

Database System Concepts 2.8 ©Silberschatz, Korth and Sudarshan

4
 13.10.2011

Degree of a Relationship Set

 Refers to number of entity sets that participate in a

relationship set.
 Relationship sets that involve two entity sets are binary (or
degree two). Generally, most relationship sets in a
database system are binary.
 Relationship sets may involve more than two entity sets.
E.g. Suppose employees of a bank may have jobs
(responsibilities) at multiple branches, with different jobs at
different branches. Then there is a ternary relationship set
between entity sets employee, job and branch
 Relationships between more than two entity sets are rare.
Most relationships are binary. (More on this later.)

Database System Concepts 2.9 ©Silberschatz, Korth and Sudarshan

Mapping Cardinalities

 Express the number of entities to which another entity

can be associated via a relationship set.
 Most useful in describing binary relationship sets.
 For a binary relationship set the mapping cardinality
must be one of the following types:
 One to one
 One to many
 Many to one
 Many to many

Database System Concepts 2.10 ©Silberschatz, Korth and Sudarshan

5
 13.10.2011

Mapping Cardinalities

One to one One to many

Note: Some elements in A and B may not be mapped to any
elements in the other set

Database System Concepts 2.11 ©Silberschatz, Korth and Sudarshan

Mapping Cardinalities

Many to one Many to many

Note: Some elements in A and B may not be mapped to any
elements in the other set

Database System Concepts 2.12 ©Silberschatz, Korth and Sudarshan

6
 13.10.2011

Mapping Cardinalities affect ER Design

 Can make access-date an attribute of account, instead of a
relationship attribute, if each account can have only one customer
 I.e., the relationship from account to customer is many to one,
or equivalently, customer to account is one to many

Database System Concepts 2.13 ©Silberschatz, Korth and Sudarshan

E-R Diagrams

 Rectangles represent entity sets.

 Diamonds represent relationship sets.
 Lines link attributes to entity sets and entity sets to relationship sets.
 Ellipses represent attributes
 Double ellipses represent multivalued attributes.
 Dashed ellipses denote derived attributes.
 Underline indicates primary key attributes (will study later)
Database System Concepts 2.14 ©Silberschatz, Korth and Sudarshan

7
 13.10.2011

E-R Diagram With Composite, Multivalued, and

Derived Attributes

Database System Concepts 2.15 ©Silberschatz, Korth and Sudarshan

Relationship Sets with Attributes

Database System Concepts 2.16 ©Silberschatz, Korth and Sudarshan

8
 13.10.2011

Roles
 Entity sets of a relationship need not be distinct
 The labels “manager” and “worker” are called roles; they specify how
employee entities interact via the works-for relationship set.
 Roles are indicated in E-R diagrams by labeling the lines that connect
diamonds to rectangles.
 Role labels are optional, and are used to clarify semantics of the
relationship

Database System Concepts 2.17 ©Silberschatz, Korth and Sudarshan

Cardinality Constraints
 We express cardinality constraints by drawing either a directed
line (), signifying “one,” or an undirected line (—), signifying
“many,” between the relationship set and the entity set.
 E.g.: One-to-one relationship:
 A customer is associated with at most one loan via the relationship
borrower
 A loan is associated with at most one customer via borrower

Database System Concepts 2.18 ©Silberschatz, Korth and Sudarshan

9
 13.10.2011

One-To-Many Relationship

 In the one-to-many relationship a loan is associated with at most

one customer via borrower, a customer is associated with
several (including 0) loans via borrower

Database System Concepts 2.19 ©Silberschatz, Korth and Sudarshan

Many-To-One Relationships

 In a many-to-one relationship a loan is associated with several

(including 0) customers via borrower, a customer is associated
with at most one loan via borrower

Database System Concepts 2.20 ©Silberschatz, Korth and Sudarshan

10
 13.10.2011

Many-To-Many Relationship

 A customer is associated with several (possibly 0) loans

via borrower
 A loan is associated with several (possibly 0) customers
via borrower

Database System Concepts 2.21 ©Silberschatz, Korth and Sudarshan

Participation of an Entity Set in a

Relationship Set
 Total participation (indicated by double line): every entity in the entity
set participates in at least one relationship in the relationship set
 E.g. participation of loan in borrower is total
 every loan must have a customer associated to it via borrower
 Partial participation: some entities may not participate in any
relationship in the relationship set
 E.g. participation of customer in borrower is partial

Database System Concepts 2.22 ©Silberschatz, Korth and Sudarshan

11
 13.10.2011

Alternative Notation for Cardinality

Limits
 Cardinality limits can also express participation constraints

Database System Concepts 2.23 ©Silberschatz, Korth and Sudarshan

Keys

 A super key of an entity set is a set of one or more

attributes whose values uniquely determine each
entity.
 A candidate key of an entity set is a minimal super key
 Customer-id is candidate key of customer
 account-number is candidate key of account
 Although several candidate keys may exist, one of the
candidate keys is selected to be the primary key.

Database System Concepts 2.24 ©Silberschatz, Korth and Sudarshan

12
 13.10.2011

Keys for Relationship Sets

 The combination of primary keys of the participating entity

sets forms a super key of a relationship set.
 (customer-id, account-number) is the super key of depositor
 NOTE: this means a pair of entity sets can have at most one
relationship in a particular relationship set.
 E.g. if we wish to track all access-dates to each account by each
customer, we cannot assume a relationship for each access.
We can use a multivalued attribute though
 Must consider the mapping cardinality of the relationship
set when deciding the what are the candidate keys
 Need to consider semantics of relationship set in selecting
the primary key in case of more than one candidate key

Database System Concepts 2.25 ©Silberschatz, Korth and Sudarshan

E-R Diagram with a Ternary Relationship

Database System Concepts 2.26 ©Silberschatz, Korth and Sudarshan

13
 13.10.2011

Cardinality Constraints on Ternary

Relationship
 We allow at most one arrow out of a ternary (or greater
degree) relationship to indicate a cardinality constraint
 E.g. an arrow from works-on to job indicates each employee
works on at most one job at any branch.
 If there is more than one arrow, there are two ways of defining
the meaning.
 E.g a ternary relationship R between A, B and C with arrows to B and C
could mean
 1. each A entity is associated with a unique entity from B and C or
 2. each pair of entities from (A, B) is associated with a unique C entity,
and each pair (A, C) is associated with a unique B
 Each alternative has been used in different formalisms
 To avoid confusion we outlaw more than one arrow

Database System Concepts 2.27 ©Silberschatz, Korth and Sudarshan

Binary Vs. Non-Binary Relationships

 Some relationships that appear to be non-binary may be
better represented using binary relationships
 E.g. A ternary relationship parents, relating a child to his/her father and
mother, is best replaced by two binary relationships, father and mother
 Using two binary relationships allows partial information (e.g. only
mother being know)
 But there are some relationships that are naturally non-binary
 E.g. works-on

Database System Concepts 2.28 ©Silberschatz, Korth and Sudarshan

14
 13.10.2011

Converting Non-Binary Relationships to

Binary Form
 In general, any non-binary relationship can be represented using
binary relationships by creating an artificial entity set.
 Replace R between entity sets A, B and C by an entity set E, and three
relationship sets:
1. RA, relating E and A [Link], relating E and B
3. RC, relating E and C
 Create a special identifying attribute for E
 Add any attributes of R to E
 For each relationship (ai , bi , ci) in R, create
1. a new entity ei in the entity set E 2. add (ei , ai ) to RA
3. add (ei , bi ) to RB 4. add (ei , ci ) to RC

Database System Concepts 2.29 ©Silberschatz, Korth and Sudarshan

Converting Non-Binary Relationships

(Cont.)
 Also need to translate constraints
 Translating all constraints may not be possible
 There may be instances in the translated schema that
cannot correspond to any instance of R
 Exercise: add constraints to the relationships RA, RB and RC to
ensure that a newly created entity corresponds to exactly one entity
in each of entity sets A, B and C
 We can avoid creating an identifying attribute by making E a weak
entity set (described shortly) identified by the three relationship sets

Database System Concepts 2.30 ©Silberschatz, Korth and Sudarshan

15
 13.10.2011

Weak Entity Sets

 An entity set that does not have a primary key is referred to as a

weak entity set.
 The existence of a weak entity set depends on the existence of a
identifying entity set
 it must relate to the identifying entity set via a total, one-to-many
relationship set from the identifying to the weak entity set
 Identifying relationship depicted using a double diamond
 The discriminator (or partial key) of a weak entity set is the set of
attributes that distinguishes among all the entities of a weak
entity set.
 The primary key of a weak entity set is formed by the primary key
of the strong entity set on which the weak entity set is existence
dependent, plus the weak entity set’s discriminator.

Database System Concepts 2.31 ©Silberschatz, Korth and Sudarshan

Weak Entity Sets (Cont.)

 We depict a weak entity set by double rectangles.
 We underline the discriminator of a weak entity set with a
dashed line.
 payment-number – discriminator of the payment entity set
 Primary key for payment – (loan-number, payment-number)

Database System Concepts 2.32 ©Silberschatz, Korth and Sudarshan

16
 13.10.2011

Weak Entity Sets (Cont.)

 Note: the primary key of the strong entity set is not

explicitly stored with the weak entity set, since it is implicit
in the identifying relationship.

 If loan-number were explicitly stored, payment could be

made a strong entity, but then the relationship between
payment and loan would be duplicated by an implicit
relationship defined by the attribute loan-number common
to payment and loan

Database System Concepts 2.33 ©Silberschatz, Korth and Sudarshan

Example: Logins (Email Addresses)

Login name = user name + host name, e.g.,
ark@[Link].
 A “login” entity corresponds to a user name on a particular host, but
the passwd table doesn’t record the host, just the user name, e.g.,
ark.
 Key for a login = the user name at the host (which is unique for that
host only) + the IP address of the host (which is unique globally).
name name

Logins @ Hosts

 Design issue: Under what circumstances could we simply make

login-name and host-name be attributes of logins, and dispense
with the weak E.S.?

Database System Concepts 2.34 ©Silberschatz, Korth and Sudarshan

17
 13.10.2011

All “Connecting” BBP

Entity Sets
Are Weak The- The- The-
Bar Beer Price

Bars Beers Price

name addr name manf price

 In this special case, where bar and beer determine a price, we can
omit price from the key, and remove the double diamond from
ThePrice.
 Better: price is attribute of BBP.

Database System Concepts 2.35 ©Silberschatz, Korth and Sudarshan

Relationship To Weak Entities

 Consider a relationship, Ordered, between two entity sets,
Buyer and Product UPC

Buyer Ordered Product

Name Qty

 How can we add Shipments to the mix?

UPC

Buyer Ordered Product

Qty ID
Name
Shipment
This is wrong. Why?
Database System Concepts 2.36 ©Silberschatz, Korth and Sudarshan

18
 13.10.2011

 Solution: make Ordered into a weak entity set.

UPC

Buyer OB Ordered OP Product

Name Qty

 And then add Shipment. UPC

Buyer OB Ordered OP Product

Name Qty
Ordered
Part-of is ID
many-many Part of Shipment
and not a weak Qty
relationship!
Shipped
Database System Concepts 2.37 ©Silberschatz, Korth and Sudarshan

Design Issues

 Use of entity sets vs. attributes

Choice mainly depends on the structure of the enterprise being modeled,
and on the semantics associated with the attribute in question.
 Use of entity sets vs. relationship sets
Possible guideline is to designate a relationship set to describe an action
that occurs between entities
 Binary versus n-ary relationship sets
Although it is possible to replace any nonbinary (n-ary, for n > 2)
relationship set by a number of distinct binary relationship sets, a n-ary
relationship set shows more clearly that several entities participate in a
single relationship.
 Avoid redundancy
Redudancy wastes space and encourages inconsistency.
 Don't overuse weak entity sets

Database System Concepts 2.38 ©Silberschatz, Korth and Sudarshan

19
 13.10.2011

Entity Sets Vs. Attributes

You may be unsure which concepts are worthy of being entity
sets, and which are handled more simply as attributes.
 Especially tricky for the class design project, since there is a
temptation to create needless entity sets to make project “larger.”

name name

Wrong:
Beers ManfBy Manfs

Make an entity set only if it either:

name manf Is more than a name of something; i.e., it
has nonkey attributes or relationships
Right:
with a number of different entity sets,
Beers or
Is the “many” in a many-one relationship.

Database System Concepts 2.39 ©Silberschatz, Korth and Sudarshan

Example

The following design illustrates both points:

name name addr

Beers ManfBy Manfs

 Manfs deserves to be an E.S. because we record addr, a nonkey

attribute.
 Beers deserves to be an E.S. because it is at the “many” end.
 If not, we would have to make “set of beers” an attribute of Manfs –
something we avoid doing, although some may tell you it is OK in E/R
model.

Database System Concepts 2.40 ©Silberschatz, Korth and Sudarshan

20
 13.10.2011

Avoid redundancy
Setting: client has (possibly vague) idea of what he/she wants. You must
design a database that represents these thoughts and only these thoughts.

name name addr

Good:
Beers ManfBy Manfs

name manf
Bad: Repeats manufacturer
address for each beer
Beers Manf they manufacture.
addr

name manf Manufacturer’s name addr

Bad: name said twice.

Beers ManfBy Manfs

Database System Concepts 2.41 ©Silberschatz, Korth and Sudarshan

Don't Overuse Weak E.S.

 There is a tendency to feel that no E.S. has its entities uniquely

determined without following some relationships.
 However, in practice, we almost always create unique ID's to
compensate: social-security numbers, VIN's, etc.
 The only times weak E.S.'s seem necessary are when:
 We can't easily create such ID's; e.g., no one is going to accept a
“species ID” as part of the standard nomenclature (species is a
weak E.S. supported by membership in a genus).
 There is no global authority to create them, e.g., crews and
studios.

Database System Concepts 2.42 ©Silberschatz, Korth and Sudarshan

21
 13.10.2011

This image cannot currently be display ed.

How about doing an ER design

interactively on the board?
Suggest an application to be modeled.

Specialization

 Top-down design process; we designate subgroupings within an

entity set that are distinctive from other entities in the set.
 These subgroupings become lower-level entity sets that have
attributes or participate in relationships that do not apply to the
higher-level entity set.
 Depicted by a triangle component labeled ISA (E.g. customer “is
a” person).
 Attribute inheritance – a lower-level entity set inherits all the
attributes and relationship participation of the higher-level entity
set to which it is linked.

Database System Concepts 2.44 ©Silberschatz, Korth and Sudarshan

22
 13.10.2011

Specialization Example

Database System Concepts 2.45 ©Silberschatz, Korth and Sudarshan

Generalization

 A bottom-up design process – combine a number of entity

sets that share the same features into a higher-level entity
set.
 Specialization and generalization are simple inversions of
each other; they are represented in an E-R diagram in the
same way.
 The terms specialization and generalization are used
interchangeably.

Database System Concepts 2.46 ©Silberschatz, Korth and Sudarshan

23
 13.10.2011

Specialization and Generalization

(Contd.)

 Can have multiple specializations of an entity set based on

different features.
 E.g. permanent-employee vs. temporary-employee, in
addition to officer vs. secretary vs. teller
 Each particular employee would be
 a member of one of permanent-employee or temporary-employee,
 and also a member of one of officer, secretary, or teller
 The ISA relationship also referred to as superclass -
subclass relationship

Database System Concepts 2.47 ©Silberschatz, Korth and Sudarshan

Design Constraints on a
Specialization/Generalization
 Constraint on which entities can be members of a given
lower-level entity set.
 condition-defined
 E.g. all customers over 65 years are members of senior-
citizen entity set; senior-citizen ISA person.
 user-defined
 Constraint on whether or not entities may belong to more than
one lower-level entity set within a single generalization.
 Disjoint
 an entity can belong to only one lower-level entity set
 Noted in E-R diagram by writing disjoint next to the ISA
triangle
 Overlapping
 an entity can belong to more than one lower-level entity set

Database System Concepts 2.48 ©Silberschatz, Korth and Sudarshan

24
 13.10.2011

Design Constraints on a
Specialization/Generalization (Contd.)
 Completeness constraint -- specifies whether or not an
entity in the higher-level entity set must belong to at least
one of the lower-level entity sets within a generalization.
 total : an entity must belong to one of the lower-level entity sets
 partial: an entity need not belong to one of the lower-level entity
sets

Database System Concepts 2.49 ©Silberschatz, Korth and Sudarshan

Aggregation
 Consider the ternary relationship works-on, which we saw earlier

 Suppose we want to record managers for tasks performed by an

employee at a branch

Database System Concepts 2.50 ©Silberschatz, Korth and Sudarshan

25
 13.10.2011

Aggregation (Cont.)
 Relationship sets works-on and manages represent overlapping
information
 Every manages relationship corresponds to a works-on relationship
 However, some works-on relationships may not correspond to any
manages relationships
 So we can’t discard the works-on relationship

 Eliminate this redundancy via aggregation

 Treat relationship as an abstract entity
 Allows relationships between relationships
 Abstraction of relationship into new entity
 Without introducing redundancy, the following diagram represents:
 An employee works on a particular job at a particular branch
 An employee, branch, job combination may have an associated manager

Database System Concepts 2.51 ©Silberschatz, Korth and Sudarshan

E-R Diagram With Aggregation

Database System Concepts 2.52 ©Silberschatz, Korth and Sudarshan

26
 13.10.2011

E-R Design Decisions

 The use of an attribute or entity set to represent an object.

 Whether a real-world concept is best expressed by an
entity set or a relationship set.
 The use of a ternary relationship versus a pair of binary
relationships.
 The use of a strong or weak entity set.
 The use of specialization/generalization – contributes to
modularity in the design.
 The use of aggregation – can treat the aggregate entity set
as a single unit without concern for the details of its
internal structure.

Database System Concepts 2.53 ©Silberschatz, Korth and Sudarshan

Beers-Bars-Drinkers Example

name addr license

Serves Bars Frequents

Beers Likes Drinkers

name manf name addr

Database System Concepts 2.54 ©Silberschatz, Korth and Sudarshan

27
 13.10.2011

E-R Diagram for a Banking Enterprise

Database System Concepts 2.55 ©Silberschatz, Korth and Sudarshan

This image cannot currently be display ed.

How about doing another ER design

interactively on the board?

28
 13.10.2011

Summary of Symbols Used in E-R

Notation

Database System Concepts 2.57 ©Silberschatz, Korth and Sudarshan

Summary of Symbols (Cont.)

Database System Concepts 2.58 ©Silberschatz, Korth and Sudarshan

29
 13.10.2011

Alternative E-R Notations

Database System Concepts 2.59 ©Silberschatz, Korth and Sudarshan

UML

 UML: Unified Modeling Language

 UML has many components to graphically model different
aspects of an entire software system
 UML Class Diagrams correspond to E-R Diagram, but
several differences.

Database System Concepts 2.60 ©Silberschatz, Korth and Sudarshan

30
 13.10.2011

Summary of UML Class Diagram Notation

Database System Concepts 2.61 ©Silberschatz, Korth and Sudarshan

UML Class Diagrams (Contd.)

 Entity sets are shown as boxes, and attributes are shown within the
box, rather than as separate ellipses in E-R diagrams.
 Binary relationship sets are represented in UML by just drawing a
line connecting the entity sets. The relationship set name is written
adjacent to the line.
 The role played by an entity set in a relationship set may also be
specified by writing the role name on the line, adjacent to the entity
set.
 The relationship set name may alternatively be written in a box,
along with attributes of the relationship set, and the box is
connected, using a dotted line, to the line depicting the relationship
set.
 Non-binary relationships drawn using diamonds, just as in ER
diagrams

Database System Concepts 2.62 ©Silberschatz, Korth and Sudarshan

31
 13.10.2011

UML Class Diagram Notation (Cont.)

overlapping

disjoint

*Note reversal of position in cardinality constraint depiction

*Generalization can use merged or separate arrows independent
of disjoint/overlapping
Database System Concepts 2.63 ©Silberschatz, Korth and Sudarshan

UML Class Diagrams (Contd.)

 Cardinality constraints are specified in the form l..h, where l denotes
the minimum and h the maximum number of relationships an entity
can participate in.
 Beware: the positioning of the constraints is exactly the reverse of the
positioning of constraints in E-R diagrams.
 The constraint 0..* on the E2 side and 0..1 on the E1 side means that
each E2 entity can participate in at most one relationship, whereas
each E1 entity can participate in many relationships; in other words,
the relationship is many to one from E2 to E1.
 Single values, such as 1 or * may be written on edges; The single
value 1 on an edge is treated as equivalent to 1..1, while * is
equivalent to 0..*.

Database System Concepts 2.64 ©Silberschatz, Korth and Sudarshan

32
 13.10.2011

Reduction of an E-R Schema to Tables

 Primary keys allow entity sets and relationship sets to be

expressed uniformly as tables which represent the
contents of the database.
 A database which conforms to an E-R diagram can be
represented by a collection of tables.
 For each entity set and relationship set there is a unique
table which is assigned the name of the corresponding
entity set or relationship set.
 Each table has a number of columns (generally
corresponding to attributes), which have unique names.
 Converting an E-R diagram to a table format is the basis
for deriving a relational database design from an E-R
diagram.

Database System Concepts 2.65 ©Silberschatz, Korth and Sudarshan

Representing Entity Sets as Tables

 A strong entity set reduces to a table with the same attributes.

Database System Concepts 2.66 ©Silberschatz, Korth and Sudarshan

33
 13.10.2011

Composite and Multivalued Attributes

 Composite attributes are flattened out by creating a separate attribute
for each component attribute
 E.g. given entity set customer with composite attribute name with
component attributes first-name and last-name the table corresponding
to the entity set has two attributes
[Link]-name and [Link]-name
 A multivalued attribute M of an entity E is represented by a separate
table EM
 Table EM has attributes corresponding to the primary key of E and an
attribute corresponding to multivalued attribute M
 E.g. Multivalued attribute dependent-names of employee is represented
by a table
employee-dependent-names( employee-id, dname)
 Each value of the multivalued attribute maps to a separate row of the
table EM
 E.g., an employee entity with primary key John and
dependents Johnson and Johndotir maps to two rows:
(John, Johnson) and (John, Johndotir)

Database System Concepts 2.67 ©Silberschatz, Korth and Sudarshan

Representing Weak Entity Sets

 A weak entity set becomes a table that includes a column for
the primary key of the identifying strong entity set

Database System Concepts 2.68 ©Silberschatz, Korth and Sudarshan

34
 13.10.2011

Representing Relationship Sets as

Tables
 A many-to-many relationship set is represented as a table with
columns for the primary keys of the two participating entity sets,
and any descriptive attributes of the relationship set.
 E.g.: table for relationship set borrower

Database System Concepts 2.69 ©Silberschatz, Korth and Sudarshan

Redundancy of Tables
 Many-to-one and one-to-many relationship sets that are total
on the many-side can be represented by adding an extra
attribute to the many side, containing the primary key of the
one side
 E.g.: Instead of creating a table for relationship account-
branch, add an attribute branch to the entity set account

Database System Concepts 2.70 ©Silberschatz, Korth and Sudarshan

35
 13.10.2011

Redundancy of Tables (Cont.)

 For one-to-one relationship sets, either side can be chosen to act

as the “many” side
 That is, extra attribute can be added to either of the tables
corresponding to the two entity sets
 If participation is partial on the many side, replacing a table by an
extra attribute in the relation corresponding to the “many” side
could result in null values
 The table corresponding to a relationship set linking a weak
entity set to its identifying strong entity set is redundant.
 E.g. The payment table already contains the information that would
appear in the loan-payment table (i.e., the columns loan-number
and payment-number).

Database System Concepts 2.71 ©Silberschatz, Korth and Sudarshan

Representing Specialization as Tables

 Method 1:
 Form a table for the higher level entity
 Form a table for each lower level entity set, include primary key of
higher level entity set and local attributes

table table attributes

person name, street, city
customer name, credit-rating
employee name, salary
 Drawback: getting information about, e.g., employee requires
accessing two tables

Database System Concepts 2.72 ©Silberschatz, Korth and Sudarshan

36
 13.10.2011

Representing Specialization as Tables

(Cont.)
 Method 2:
 Form a table for each entity set with all local and inherited
attributes
table table attributes
person name, street, city
customer name, street, city, credit-rating
employee name, street, city, salary

 If specialization is total, table for generalized entity (person) not

required to store information
 Can be defined as a “view” relation containing union of
specialization tables
 But explicit table may still be needed for foreign key constraints
 Drawback: street and city may be stored redundantly for persons
who are both customers and employees

Database System Concepts 2.73 ©Silberschatz, Korth and Sudarshan

Relations Corresponding to
Aggregation

 To represent aggregation, create a table containing

 primary key of the aggregated relationship,
 the primary key of the associated entity set
 Any descriptive attributes

Database System Concepts 2.74 ©Silberschatz, Korth and Sudarshan

37
 13.10.2011

Relations Corresponding to
Aggregation (Cont.)
 E.g. to represent aggregation manages between relationship
works-on and entity set manager, create a table
manages(employee-id, branch-name, title, manager-name)
 Table works-on is redundant provided we are willing to store
null values for attribute manager-name in table manages

Database System Concepts 2.75 ©Silberschatz, Korth and Sudarshan

This image cannot currently be display ed.

End of Chapter 2

38
 13.10.2011

E-R Diagram for Exercise 2.10

Database System Concepts 2.77 ©Silberschatz, Korth and Sudarshan

E-R Diagram for Exercise 2.15

Database System Concepts 2.78 ©Silberschatz, Korth and Sudarshan

39
 13.10.2011

E-R Diagram for Exercise 2.22

Database System Concepts 2.79 ©Silberschatz, Korth and Sudarshan

E-R Diagram for Exercise 2.15

Database System Concepts 2.80 ©Silberschatz, Korth and Sudarshan

40
 13.10.2011

Existence Dependencies

 If the existence of entity x depends on the existence of

entity y, then x is said to be existence dependent on y.
 y is a dominant entity (in example below, loan)
 x is a subordinate entity (in example below, payment)

loan loan-payment payment

If a loan entity is deleted, then all its associated payment entities

must be deleted also.

Database System Concepts 2.81 ©Silberschatz, Korth and Sudarshan

41
 Chapter 3: Relational Model

 Structure of Relational Databases

 Relational Algebra
 Tuple Relational Calculus
 Domain Relational Calculus
 Extended Relational-Algebra-Operations
 Modification of the Database
 Views

Database System Concepts 3.1 ©Silberschatz, Korth and Sudarshan

Example of a Relation

Database System Concepts 3.2 ©Silberschatz, Korth and Sudarshan

1
 Basic Structure
 Formally, given sets D1, D2, …. Dn a relation r is a subset of
D1 x D2 x … x Dn
Thus a relation is a set of n-tuples (a1, a2, …, an) where
each ai  Di
 Example: if
customer-name = {Jones, Smith, Curry, Lindsay}
customer-street = {Main, North, Park}
customer-city = {Harrison, Rye, Pittsfield}
Then r = { (Jones, Main, Harrison),
(Smith, North, Rye),
(Curry, North, Rye),
(Lindsay, Park, Pittsfield)}
is a relation over customer-name x customer-street x customer-city

Database System Concepts 3.3 ©Silberschatz, Korth and Sudarshan

Attribute Types
 Each attribute of a relation has a name
 The set of allowed values for each attribute is called the
domain of the attribute
 Attribute values are (normally) required to be atomic, that
is, indivisible
 E.g. multivalued attribute values are not atomic
 E.g. composite attribute values are not atomic
 The special value null is a member of every domain
 The null value causes complications in the definition of
many operations
 we shall ignore the effect of null values in our main presentation
and consider their effect later

Database System Concepts 3.4 ©Silberschatz, Korth and Sudarshan

2
 Relation Schema
 A1, A2, …, An are attributes
 R = (A1, A2, …, An ) is a relation schema
E.g. Customer-schema =
(customer-name, customer-street, customer-city)
 r(R) is a relation on the relation schema R
E.g. customer (Customer-schema)

Database System Concepts 3.5 ©Silberschatz, Korth and Sudarshan

Relation Instance
 The current values (relation instance) of a relation are
specified by a table
 An element t of r is a tuple, represented by a row in a
table

attributes
(or columns)
customer-name customer-street customer-city

Jones Main Harrison

Smith North Rye tuples
Curry North Rye (or rows)
Lindsay Park Pittsfield

customer

Database System Concepts 3.6 ©Silberschatz, Korth and Sudarshan

3
 Relations are Unordered
 Order of tuples is irrelevant (tuples may be stored in
an arbitrary order)
 E.g. account relation with unordered tuples

Database System Concepts 3.7 ©Silberschatz, Korth and Sudarshan

Why Relations?
 Very simple model.
 Often a good match for the way we think about our data.
 Abstract model that underlies SQL, the most important
language in DBMS’s today.
 But SQL uses “bags” while the abstract relational model is set-
oriented.

 All ingenious ideas are simple !

Database System Concepts 3.8 ©Silberschatz, Korth and Sudarshan

4
 Database
 A database consists of multiple relations
 Information about an enterprise is broken up into parts,
with each relation storing one part of the information
E.g.: account : stores information about accounts
depositor : stores information about which customer
owns which account
customer : stores information about customers
 Storing all information as a single relation such as
bank(account-number, balance, customer-name, ..)
results in
 repetition of information (e.g. two customers own an account)
 the need for null values (e.g. represent a customer without an
account)
 Normalization theory (Chapter 7) deals with how to design
relational schemas

Database System Concepts 3.9 ©Silberschatz, Korth and Sudarshan

The customer Relation

Database System Concepts 3.10 ©Silberschatz, Korth and Sudarshan

5
 The depositor Relation

Database System Concepts 3.11 ©Silberschatz, Korth and Sudarshan

E-R Diagram for the Banking Enterprise

Database System Concepts 3.12 ©Silberschatz, Korth and Sudarshan

6
 Keys
 Let K  R
 K is a superkey of R if values for K are sufficient to identify
a unique tuple of each possible relation r(R)
 by “possible r” we mean a relation r that could exist in the enterprise
we are modeling.
 Example: {customer-name, customer-street} and
{customer-name}
are both superkeys of Customer, if no two customers can possibly
have the same name.
 K is a candidate key if K is minimal
Example: {customer-name} is a candidate key for Customer,
since it is a superkey (assuming no two customers can possibly
have the same name), and no subset of it is a superkey.

Database System Concepts 3.13 ©Silberschatz, Korth and Sudarshan

Example 1
Drinkers(name, addr, beersLiked, manf, favoriteBeer)
 {name, beersLiked} FD’s all attributes, as seen.
 Shows {name, beersLiked} is a superkey.
 name  beersLiked is false, so name is not a superkey.
 beersLiked  name also false, so beersLiked is not a superkey.
 Thus, {name, beersLiked} is a key.
 No other keys in this example.
 Neither name nor beersLiked is on the right of any observed FD, so they
must be part of any superkey.

 Important point: “key” in a relation refers to tuples, not the entities they
represent. If an entity is represented by several tuples, then entity-key will
not be the same as relation-key.

Database System Concepts 3.14 ©Silberschatz, Korth and Sudarshan

7
 Example 2

Lastname Firstname Student ID Major

Key Key
(2 attributes)
Superkey

Note: There are alternate keys

 Keys are {Lastname, Firstname} and {StudentID}

Database System Concepts 3.15 ©Silberschatz, Korth and Sudarshan

Determining Keys from E-R Sets

 Strong entity set. The primary key of the entity set
becomes the primary key of the relation.
 Weak entity set. The primary key of the relation consists
of the union of the primary key of the strong entity set and
the discriminator of the weak entity set.
 Relationship set. The union of the primary keys of the
related entity sets becomes a super key of the relation.
 For binary many-to-one relationship sets, the primary key of the
“many” entity set becomes the relation’s primary key.
 For one-to-one relationship sets, the relation’s primary key can be
that of either entity set.
 For many-to-many relationship sets, the union of the primary keys
becomes the relation’s primary key

Database System Concepts 3.16 ©Silberschatz, Korth and Sudarshan

8
 Schema Diagram for the Banking Enterprise

Database System Concepts 3.17 ©Silberschatz, Korth and Sudarshan

Query Languages
 Language in which user requests information
from the database.
 Categories of languages
 procedural
 non-procedural
 “Pure” languages:
 Relational Algebra
 Tuple Relational Calculus
 Domain Relational Calculus
 Pure languages form underlying basis of
query languages that people use.

Database System Concepts 3.18 ©Silberschatz, Korth and Sudarshan

9
 Relational Algebra
 Procedural language
 Six basic operators
 select
 project
 union
 set difference
 Cartesian product
 rename
 The operators take one or more relations as inputs and
give a new relation as a result.

Database System Concepts 3.19 ©Silberschatz, Korth and Sudarshan

Select Operation – Example

• Relation r A B C D

  1 7
  5 7
  12 3
  23 10

• A=B ^ D > 5 (r)

A B C D

  1 7
  23 10

Database System Concepts 3.20 ©Silberschatz, Korth and Sudarshan

10
 Select Operation

 Notation:  p(r)
 p is called the selection predicate
 Defined as:
p(r) = {t | t  r and p(t)}
Where p is a formula in propositional calculus consisting
of terms connected by :  (and),  (or),  (not)
Each term is one of:
<attribute> op <attribute> or <constant>
where op is one of: =, , >, . <. 
 Example of selection:
 branch-name=“Perryridge”(account)

Database System Concepts 3.21 ©Silberschatz, Korth and Sudarshan

Project Operation – Example

 Relation r: A B C

 10 1
 20 1
 30 1
 40 2

 A,C (r) A C A C

 1  1
 1 =  1
 1  2
 2

Database System Concepts 3.22 ©Silberschatz, Korth and Sudarshan

11
 Project Operation
 Notation:

A1, A2, …, Ak (r)

where A1, A2 are attribute names and r is a relation name.
 The result is defined as the relation of k columns obtained
by erasing the columns that are not listed
 Duplicate rows removed from result, since relations are
sets
 E.g. To eliminate the branch-name attribute of account
account-number, balance (account)

Database System Concepts 3.23 ©Silberschatz, Korth and Sudarshan

Union Operation – Example

 Relations r, s: A B A B

 1  2
 2  3
 1 s
r

r  s: A B

 1
 2
 1
 3

Database System Concepts 3.24 ©Silberschatz, Korth and Sudarshan

12
 Union Operation
 Notation: r  s
 Defined as:
r  s = {t | t  r or t  s}
 For r  s to be valid.
1. r, s must have the same arity (same number of
attributes)
2. The attribute domains must be compatible (e.g., 2nd
column
of r deals with the same type of values as does the 2nd
column of s)
 E.g. to find all customers with either an account or a loan
customer-name (depositor)  customer-name (borrower)

Database System Concepts 3.25 ©Silberschatz, Korth and Sudarshan

Set Difference Operation – Example

 Relations r, s: A B A B

 1  2
 2  3
 1 s
r

r – s: A B

 1
 1

Database System Concepts 3.26 ©Silberschatz, Korth and Sudarshan

13
 Set Difference Operation
 Notation r – s
 Defined as:
r – s = {t | t  r and t  s}
 Set differences must be taken between compatible
relations.
 r and s must have the same arity
 attribute domains of r and s must be compatible

Database System Concepts 3.27 ©Silberschatz, Korth and Sudarshan

Cartesian-Product Operation-Example

Relations r, s: A B C D E

 1  10 a
 10 a
 2
 20 b
r  10 b
s
r x s:
A B C D E
 1  10 a
 1  10 a
 1  20 b
 1  10 b
 2  10 a
 2  10 a
 2  20 b
 2  10 b

Database System Concepts 3.28 ©Silberschatz, Korth and Sudarshan

14
 Cartesian-Product Operation
 Notation r x s
 Defined as:
r x s = {t q | t  r and q  s}
 Assume that attributes of r(R) and s(S) are disjoint.
(That is, R  S = ).
 If attributes of r(R) and s(S) are not disjoint, then renaming
must be used.

Database System Concepts 3.29 ©Silberschatz, Korth and Sudarshan

Composition of Operations
 Can build expressions using multiple operations
 Example: A=C(r x s)
 rxs A B C D E
 1  10 a
 1  10 a
 1  20 b
 1  10 b
 2  10 a
 2  10 a
 2  20 b
 2  10 b

 A=C(r x s) A B C D E

 1  10 a
 2  20 a
 2  20 b
Database System Concepts 3.30 ©Silberschatz, Korth and Sudarshan

15
 Rename Operation
 Allows us to name, and therefore to refer to, the results of
relational-algebra expressions.
 Allows us to refer to a relation by more than one name.
Example:
 x (E)
returns the expression E under the name X
If a relational-algebra expression E has arity n, then
x (A1, A2, …, An) (E)
returns the result of expression E under the name X, and with the
attributes renamed to A1, A2, …., An.

Database System Concepts 3.31 ©Silberschatz, Korth and Sudarshan

Banking Example

branch (branch-name, branch-city, assets)

customer (customer-name, customer-street, customer-only)

account (account-number, branch-name, balance)

loan (loan-number, branch-name, amount)

depositor (customer-name, account-number)

borrower (customer-name, loan-number)

Database System Concepts 3.32 ©Silberschatz, Korth and Sudarshan

16
 Example Queries

 Find all loans of over $1200

amount > 1200 (loan)

Find the loan number for each loan of an amount greater than
$1200

loan-number (amount > 1200 (loan))

Database System Concepts 3.33 ©Silberschatz, Korth and Sudarshan

Example Queries

 Find the names of all customers who have a loan, an account, or

both, from the bank

customer-name (borrower)  customer-name (depositor)

Find the names of all customers who have a loan and an

account at bank.

customer-name (borrower)  customer-name (depositor)

Database System Concepts 3.34 ©Silberschatz, Korth and Sudarshan

17
 Example Queries
 Find the names of all customers who have a loan at the Perryridge
branch.

customer-name (branch-name=“Perryridge”
([Link]-number = [Link]-number(borrower x loan)))

 Find the names of all customers who have a loan at the

Perryridge branch but do not have an account at any branch of
the bank.

customer-name (branch-name = “Perryridge”

([Link]-number = [Link]-number(borrower x loan))) –

customer-name(depositor)

Database System Concepts 3.35 ©Silberschatz, Korth and Sudarshan

Example Queries
 Find the names of all customers who have a loan at the Perryridge
branch.
Query 1
customer-name(branch-name = “Perryridge” (
[Link]-number = [Link]-number(borrower x loan)))

 Query 2
customer-name([Link]-number = [Link]-number(
(branch-name = “Perryridge”(loan)) x borrower))

Database System Concepts 3.36 ©Silberschatz, Korth and Sudarshan

18
 Example Queries
Find the largest account balance
 Rename account relation as d
 The query is:

balance(account) - [Link]
([Link] < [Link] (account x d (account)))

Database System Concepts 3.37 ©Silberschatz, Korth and Sudarshan

Formal Definition
 A basic expression in the relational algebra consists of
either one of the following:
 A relation in the database
 A constant relation
 Let E1 and E2 be relational-algebra expressions; the
following are all relational-algebra expressions:
 E1  E2
 E1 - E2
 E1 x E2
 p (E1), P is a predicate on attributes in E1
 s(E1), S is a list consisting of some of the attributes in E1
  x (E1), x is the new name for the result of E1

Database System Concepts 3.38 ©Silberschatz, Korth and Sudarshan

19
 Additional Operations

We define additional operations that do not add any power

to the relational algebra, but that simplify common queries.

 Set intersection

 Natural join
 Division
 Assignment

Database System Concepts 3.39 ©Silberschatz, Korth and Sudarshan

Set-Intersection Operation
 Notation: r  s
 Defined as:
 r  s ={ t | t  r and t  s }
 Assume:
 r, s have the same arity
 attributes of r and s are compatible
 Note: r  s = r - (r - s)

Database System Concepts 3.40 ©Silberschatz, Korth and Sudarshan

20
 Set-Intersection Operation - Example
 Relation r, s: A B A B
 1  2
 2  3
 1

r s

 rs A B

 2

Database System Concepts 3.41 ©Silberschatz, Korth and Sudarshan

Natural-Join Operation
 Notation: r s
 Let r and s be relations on schemas R and S respectively.
Then, r s is a relation on schema R  S obtained as
follows:
 Consider each pair of tuples tr from r and ts from s.
 If tr and ts have the same value on each of the attributes in R  S, add
a tuple t to the result, where
 t has the same value as t on r
r
 t has the same value as t
s on s
 Example:
R = (A, B, C, D)
S = (E, B, D)
 Result schema = (A, B, C, D, E)
 r s is defined as:
r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D = s.D (r x s))
Database System Concepts 3.42 ©Silberschatz, Korth and Sudarshan

21
 Natural Join Operation – Example

 Relations r, s:
A B C D B D E

 1  a 1 a 
 2  a 3 a 
 4  b 1 a 
 1  a 2 b 
 2  b 3 b 
r s

r s
A B C D E
 1  a 
 1  a 
 1  a 
 1  a 
 2  b 

Database System Concepts 3.43 ©Silberschatz, Korth and Sudarshan

Division Operation

rs
 Suited to queries that include the phrase “for all”.
 Let r and s be relations on schemas R and S
respectively where
 R = (A1, …, Am, B1, …, Bn)
 S = (B1, …, Bn)
The result of r  s is a relation on schema
R – S = (A1, …, Am)

r  s = { t | t   R-S(r)   u  s ( tu  r ) }

Database System Concepts 3.44 ©Silberschatz, Korth and Sudarshan

22
 Division Operation – Example

Relations r, s: A B B
 1 1
 2
 3 2
 1 s
 1
 1
 3
 4
 6
 1
 2
r  s: A r



Database System Concepts 3.45 ©Silberschatz, Korth and Sudarshan

Another Division Example

Relations r, s:
A B C D E D E

 a  a 1 a 1
 a  a 1 b 1
 a  b 1 s
 a  a 1
 a  b 3
 a  a 1
 a  b 1
 a  b 1
r

r  s: A B C

 a 
 a 

Database System Concepts 3.46 ©Silberschatz, Korth and Sudarshan

23
 Division Operation (Cont.)

 Property
 Let q – r  s
 Then q is the largest relation satisfying q x s  r
 Definition in terms of the basic algebra operation
Let r(R) and s(S) be relations, and let S  R

r  s = R-S (r) –R-S ( (R-S (r) x s) – R-S,S(r))

To see why
 R-S,S(r) simply reorders attributes of r

 R-S(R-S (r) x s) – R-S,S(r)) gives those tuples t in

R-S (r) such that for some tuple u  s, tu  r.

Database System Concepts 3.47 ©Silberschatz, Korth and Sudarshan

Assignment Operation
 The assignment operation () provides a convenient way
to express complex queries.
 Write query as a sequential program consisting of
 a series of assignments
 followed by an expression whose value is displayed as a result of
the query.
 Assignment must always be made to a temporary relation variable.
 Example: Write r  s as
temp1  R-S (r)
temp2  R-S ((temp1 x s) – R-S,S (r))
result = temp1 – temp2
 The result to the right of the  is assigned to the relation variable on
the left of the .
 May use variable in subsequent expressions.

Database System Concepts 3.48 ©Silberschatz, Korth and Sudarshan

24
 Example Queries
 Find all customers who have an account from at least the
“Downtown” and the Uptown” branches.
Query 1

CN(BN=“Downtown”(depositor account)) 

CN(BN=“Uptown”(depositor account))

where CN denotes customer-name and BN denotes

branch-name.

Query 2
customer-name, branch-name (depositor account)
 temp(branch-name) ({(“Downtown”), (“Uptown”)})

Database System Concepts 3.49 ©Silberschatz, Korth and Sudarshan

Example Queries
 Find all customers who have an account at all branches
located in Brooklyn city.

customer-name, branch-name (depositor account)

 branch-name (branch-city = “Brooklyn” (branch))

Database System Concepts 3.50 ©Silberschatz, Korth and Sudarshan

25
 Extended Relational-Algebra-Operations

 Generalized Projection
 Outer Join
 Aggregate Functions

Database System Concepts 3.51 ©Silberschatz, Korth and Sudarshan

Generalized Projection
 Extends the projection operation by allowing arithmetic functions
to be used in the projection list.

 F1, F2, …, Fn(E)

 E is any relational-algebra expression
 Each of F1, F2, …, Fn are are arithmetic expressions involving
constants and attributes in the schema of E.
 Given relation credit-info(customer-name, limit, credit-balance),
find how much more each person can spend:
customer-name, limit – credit-balance (credit-info)

Database System Concepts 3.52 ©Silberschatz, Korth and Sudarshan

26
 Aggregate Functions and Operations
 Aggregation function takes a collection of values and returns a
single value as a result.
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values
 Aggregate operation in relational algebra

G1, G2, …, Gn g F1( A1), F2( A2),…, Fn( An) (E)

 E is any relational-algebra expression
 G1, G2 …, Gn is a list of attributes on which to group (can be empty)
 Each Fi is an aggregate function
 Each Ai is an attribute name

Database System Concepts 3.53 ©Silberschatz, Korth and Sudarshan

Aggregate Operation – Example

 Relation r:
A B C

  7
  7
  3
  10

sum-C
g sum(c) (r)
27

Database System Concepts 3.54 ©Silberschatz, Korth and Sudarshan

27
 Aggregate Operation – Example

 Relation account grouped by branch-name:

branch-name account-number balance

Perryridge A-102 400
Perryridge A-201 900
Brighton A-217 750
Brighton A-215 750
Redwood A-222 700

branch-name g sum(balance) (account)

branch-name balance
Perryridge 1300
Brighton 1500
Redwood 700

Database System Concepts 3.55 ©Silberschatz, Korth and Sudarshan

Aggregate Functions (Cont.)

 Result of aggregation does not have a name
 Can use rename operation to give it a name
 For convenience, we permit renaming as part of aggregate
operation

branch-name g sum(balance) as sum-balance (account)

Database System Concepts 3.56 ©Silberschatz, Korth and Sudarshan

28
 Outer Join
 An extension of the join operation that avoids loss of information.
 Computes the join and then adds tuples form one relation that do
not match tuples in the other relation to the result of the join.
 Uses null values:
 null signifies that the value is unknown or does not exist
 All comparisons involving null are (roughly speaking) false by
definition.
 Will study precise meaning of comparisons with nulls later

Database System Concepts 3.57 ©Silberschatz, Korth and Sudarshan

Outer Join – Example

 Relation loan

loan-number branch-name amount

L-170 Downtown 3000
L-230 Redwood 4000
L-260 Perryridge 1700

 Relation borrower
customer-name loan-number
Jones L-170
Smith L-230
Hayes L-155

Database System Concepts 3.58 ©Silberschatz, Korth and Sudarshan

29
 Outer Join – Example

 Inner Join

loan Borrower
loan-number branch-name amount customer-name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith

 Left Outer Join

loan Borrower
loan-number branch-name amount customer-name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-260 Perryridge 1700 null

Database System Concepts 3.59 ©Silberschatz, Korth and Sudarshan

Outer Join – Example

 Right Outer Join
loan borrower

loan-number branch-name amount customer-name

L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-155 null null Hayes

 Full Outer Join

loan borrower
loan-number branch-name amount customer-name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-260 Perryridge 1700 null
L-155 null null Hayes

Database System Concepts 3.60 ©Silberschatz, Korth and Sudarshan

30
 Null Values
 It is possible for tuples to have a null value, denoted by null, for
some of their attributes
 null signifies an unknown value or that a value does not exist.
 The result of any arithmetic expression involving null is null.
 Aggregate functions simply ignore null values
 Is an arbitrary decision. Could have returned null as result instead.
 We follow the semantics of SQL in its handling of null values
 For duplicate elimination and grouping, null is treated like any
other value, and two nulls are assumed to be the same
 Alternative: assume each null is different from each other
 Both are arbitrary decisions, so we simply follow SQL

Database System Concepts 3.61 ©Silberschatz, Korth and Sudarshan

Null Values
 Comparisons with null values return the special truth value
unknown
 If false was used instead of unknown, then not (A < 5)
would not be equivalent to A >= 5
 Three-valued logic using the truth value unknown:
 OR: (unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
 AND: (true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
 NOT: (not unknown) = unknown
 In SQL “P is unknown” evaluates to true if predicate P evaluates
to unknown
 Result of select predicate is treated as false if it evaluates to
unknown

Database System Concepts 3.62 ©Silberschatz, Korth and Sudarshan

31
 Modification of the Database
 The content of the database may be modified using the following
operations:
 Deletion
 Insertion
 Updating
 All these operations are expressed using the assignment
operator.

Database System Concepts 3.63 ©Silberschatz, Korth and Sudarshan

Deletion
 A delete request is expressed similarly to a query, except instead
of displaying tuples to the user, the selected tuples are removed
from the database.
 Can delete only whole tuples; cannot delete values on only
particular attributes
 A deletion is expressed in relational algebra by:
rr–E
where r is a relation and E is a relational algebra query.

Database System Concepts 3.64 ©Silberschatz, Korth and Sudarshan

32
 Deletion Examples

 Delete all account records in the Perryridge branch.

account  account – branch-name = “Perryridge” (account)

Delete all loan records with amount in the range of 0 to 50

loan  loan – amount 0and amount  50 (loan)

Delete all accounts at branches located in Needham.

r1  branch-city = “Needham” (account branch)

r2  branch-name, account-number, balance (r1)
r3   customer-name, account-number (r2 depositor)
account  account – r2
depositor  depositor – r3

Database System Concepts 3.65 ©Silberschatz, Korth and Sudarshan

Insertion
 To insert data into a relation, we either:
 specify a tuple to be inserted
 write a query whose result is a set of tuples to be inserted
 in relational algebra, an insertion is expressed by:
r r  E
where r is a relation and E is a relational algebra expression.
 The insertion of a single tuple is expressed by letting E be a
constant relation containing one tuple.

Database System Concepts 3.66 ©Silberschatz, Korth and Sudarshan

33
 Insertion Examples
 Insert information in the database specifying that Smith has
$1200 in account A-973 at the Perryridge branch.

account  account  {(“Perryridge”, A-973, 1200)}

depositor  depositor  {(“Smith”, A-973)}

 Provide as a gift for all loan customers in the Perryridge

branch, a $200 savings account. Let the loan number serve
as the account number for the new savings account.
r1  (branch-name = “Perryridge” (borrower loan))
account  account  branch-name, account-number,200 (r1)
depositor  depositor  customer-name, loan-number(r1)

Database System Concepts 3.67 ©Silberschatz, Korth and Sudarshan

Updating
 A mechanism to change a value in a tuple without charging all
values in the tuple
 Use the generalized projection operator to do this task
r   F1, F2, …, FI, (r)
 Each Fi is either
 the ith attribute of r, if the ith attribute is not updated, or,
 if the attribute is to be updated Fi is an expression, involving only
constants and the attributes of r, which gives the new value for the
attribute

Database System Concepts 3.68 ©Silberschatz, Korth and Sudarshan

34
 Update Examples
 Make interest payments by increasing all balances by 5 percent.

account   AN, BN, BAL * 1.05 (account)

where AN, BN and BAL stand for account-number, branch-name

and balance, respectively.

 Pay all accounts with balances over $10,000 6 percent interest

and pay all others 5 percent

account   AN, BN, BAL * 1.06 ( BAL  10000 (account))

 AN, BN, BAL * 1.05 (BAL  10000 (account))

Database System Concepts 3.69 ©Silberschatz, Korth and Sudarshan

Views
 In some cases, it is not desirable for all users to see the entire
logical model (i.e., all the actual relations stored in the database.)
 Consider a person who needs to know a customer’s loan number
but has no need to see the loan amount. This person should see
a relation described, in the relational algebra, by
customer-name, loan-number (borrower loan)
 Any relation that is not of the conceptual model but is made
visible to a user as a “virtual relation” is called a view.

Database System Concepts 3.70 ©Silberschatz, Korth and Sudarshan

35
 View Definition
 A view is defined using the create view statement which has the
form
create view v as <query expression

where <query expression> is any legal relational algebra query

expression. The view name is represented by v.
 Once a view is defined, the view name can be used to refer to
the virtual relation that the view generates.
 View definition is not the same as creating a new relation by
evaluating the query expression
 Rather, a view definition causes the saving of an expression; the
expression is substituted into queries using the view.

Database System Concepts 3.71 ©Silberschatz, Korth and Sudarshan

View Examples
 Consider the view (named all-customer) consisting of branches
and their customers.

create view all-customer as

branch-name, customer-name (depositor account)
 branch-name, customer-name (borrower loan)

 We can find all customers of the Perryridge branch by writing:

customer-name
(branch-name = “Perryridge” (all-customer))

Database System Concepts 3.72 ©Silberschatz, Korth and Sudarshan

36
 Updates Through View
 Database modifications expressed as views must be translated
to modifications of the actual relations in the database.
 Consider the person who needs to see all loan data in the loan
relation except amount. The view given to the person, branch-
loan, is defined as:
create view branch-loan as
branch-name, loan-number (loan)
 Since we allow a view name to appear wherever a relation name
is allowed, the person may write:

branch-loan  branch-loan  {(“Perryridge”, L-37)}

Database System Concepts 3.73 ©Silberschatz, Korth and Sudarshan

Updates Through Views (Cont.)

 The previous insertion must be represented by an insertion into the
actual relation loan from which the view branch-loan is constructed.
 An insertion into loan requires a value for amount. The insertion
can be dealt with by either.
 rejecting the insertion and returning an error message to the user.
 inserting a tuple (“L-37”, “Perryridge”, null) into the loan relation
 Some updates through views are impossible to translate into
database relation updates
 create view v as branch-name = “Perryridge” (account))
v  v  (L-99, Downtown, 23)
 Others cannot be translated uniquely
 all-customer  all-customer  {(“Perryridge”, “John”)}
 Have to choose loan or account, and
create a new loan/account number!

Database System Concepts 3.74 ©Silberschatz, Korth and Sudarshan

37
 Views Defined Using Other Views
 One view may be used in the expression defining another view
 A view relation v1 is said to depend directly on a view relation v2
if v2 is used in the expression defining v1
 A view relation v1 is said to depend on view relation v2 if either v1
depends directly to v2 or there is a path of dependencies from
v1 to v2
 A view relation v is said to be recursive if it depends on itself.

Database System Concepts 3.75 ©Silberschatz, Korth and Sudarshan

View Expansion
 A way to define the meaning of views defined in terms of other
views.
 Let view v1 be defined by an expression e1 that may itself contain
uses of view relations.
 View expansion of an expression repeats the following
replacement step:
repeat
Find any view relation vi in e1
Replace the view relation vi by the expression defining vi
until no more view relations are present in e1
 As long as the view definitions are not recursive, this loop will
terminate

Database System Concepts 3.76 ©Silberschatz, Korth and Sudarshan

38
 Tuple Relational Calculus
 A nonprocedural query language, where each query is of the form
{t | P (t) }
 It is the set of all tuples t such that predicate P is true for t
 t is a tuple variable, t[A] denotes the value of tuple t on attribute A
 t  r denotes that tuple t is in relation r
 P is a formula similar to that of the predicate calculus

Database System Concepts 3.77 ©Silberschatz, Korth and Sudarshan

Predicate Calculus Formula

1. Set of attributes and constants
2. Set of comparison operators: (e.g., , , , , , )
3. Set of connectives: and (), or (v)‚ not ()
4. Implication (): x  y, if x if true, then y is true
x  y x v y
5. Set of quantifiers:
 t r (Q(t)) ”there exists” a tuple in t in relation r
such that predicate Q(t) is true
 t r (Q(t)) Q is true “for all” tuples t in relation r

Database System Concepts 3.78 ©Silberschatz, Korth and Sudarshan

39
 Banking Example
 branch (branch-name, branch-city, assets)
 customer (customer-name, customer-street, customer-city)
 account (account-number, branch-name, balance)
 loan (loan-number, branch-name, amount)
 depositor (customer-name, account-number)
 borrower (customer-name, loan-number)

Database System Concepts 3.79 ©Silberschatz, Korth and Sudarshan

Example Queries
 Find the loan-number, branch-name, and amount for loans of
over $1200
{t | t  loan  t [amount]  1200}

Find the loan number for each loan of an amount greater than $1200

{t |  s loan (t[loan-number] = s[loan-number]  s [amount]  1200)}

Notice that a relation on schema [loan-number] is implicitly defined

by the query

Database System Concepts 3.80 ©Silberschatz, Korth and Sudarshan

40
 Example Queries
 Find the names of all customers having a loan, an account, or
both at the bank

{t | s  borrower( t[customer-name] = s[customer-name])

 u  depositor( t[customer-name] = u[customer-name])

 Find the names of all customers who have a loan and an account
at the bank

{t | s  borrower( t[customer-name] = s[customer-name])

 u  depositor( t[customer-name] = u[customer-name])

Database System Concepts 3.81 ©Silberschatz, Korth and Sudarshan

Example Queries
 Find the names of all customers having a loan at the Perryridge
branch

{t | s  borrower(t[customer-name] = s[customer-name]
 u  loan(u[branch-name] = “Perryridge”
 u[loan-number] = s[loan-number]))}

 Find the names of all customers who have a loan at the

Perryridge branch, but no account at any branch of the bank

{t | s  borrower( t[customer-name] = s[customer-name]

 u  loan(u[branch-name] = “Perryridge”
 u[loan-number] = s[loan-number]))
 not v  depositor (v[customer-name] =
t[customer-name]) }

Database System Concepts 3.82 ©Silberschatz, Korth and Sudarshan

41
 Example Queries
 Find the names of all customers having a loan from the
Perryridge branch, and the cities they live in

{t | s  loan(s[branch-name] = “Perryridge”
 u  borrower (u[loan-number] = s[loan-number]
 t [customer-name] = u[customer-name])
  v  customer (u[customer-name] = v[customer-name]
 t[customer-city] = v[customer-city])))}

Database System Concepts 3.83 ©Silberschatz, Korth and Sudarshan

Example Queries
 Find the names of all customers who have an account at all
branches located in Brooklyn:

{t |  c  customer (t[[Link]] = c[customer-name]) 

 s  branch(s[branch-city] = “Brooklyn” 
 u  account ( s[branch-name] = u[branch-name]
  s  depositor ( t[customer-name] = s[customer-name]
 s[account-number] = u[account-number] )) )}

Database System Concepts 3.84 ©Silberschatz, Korth and Sudarshan

42
 Safety of Expressions
 It is possible to write tuple calculus expressions that generate
infinite relations.
 For example, {t |  t r} results in an infinite relation if the
domain of any attribute of relation r is infinite
 To guard against the problem, we restrict the set of allowable
expressions to safe expressions.
 An expression {t | P(t)} in the tuple relational calculus is safe if
every component of t appears in one of the relations, tuples, or
constants that appear in P
 NOTE: this is more than just a syntax condition.
 E.g. { t | t[A]=5  true } is not safe --- it defines an infinite set with
attribute values that do not appear in any relation or tuples or
constants in P.

Database System Concepts 3.85 ©Silberschatz, Korth and Sudarshan

Domain Relational Calculus

 A nonprocedural query language equivalent in power to the tuple
relational calculus
 Each query is an expression of the form:

{  x1, x2, …, xn  | P(x1, x2, …, xn)}

 x1, x2, …, xn represent domain variables

 P represents a formula similar to that of the predicate calculus

Database System Concepts 3.86 ©Silberschatz, Korth and Sudarshan

43
 Example Queries
 Find the loan-number, branch-name, and amount for loans of over
$1200
{ l, b, a  |  l, b, a   loan  a > 1200}

 Find the names of all customers who have a loan of over $1200

{ c  |  l, b, a ( c, l   borrower   l, b, a   loan  a > 1200)}

 Find the names of all customers who have a loan from the
Perryridge branch and the loan amount:

{ c, a  |  l ( c, l   borrower  b( l, b, a   loan 

b = “Perryridge”))}
or { c, a  |  l ( c, l   borrower   l, “Perryridge”, a   loan)}

Database System Concepts 3.87 ©Silberschatz, Korth and Sudarshan

Example Queries
 Find the names of all customers having a loan, an account, or
both at the Perryridge branch:

{ c  |  l ({ c, l   borrower
  b,a( l, b, a   loan  b = “Perryridge”))
  a( c, a   depositor
  b,n( a, b, n   account  b = “Perryridge”))}

 Find the names of all customers who have an account at all

branches located in Brooklyn:

{ c  |  s, n ( c, s, n   customer) 
 x,y,z( x, y, z   branch  y = “Brooklyn”) 
 a,b( x, y, z   account   c,a   depositor)}

Database System Concepts 3.88 ©Silberschatz, Korth and Sudarshan

44
 Safety of Expressions
{  x1, x2, …, xn  | P(x1, x2, …, xn)}

is safe if all of the following hold:

[Link] values that appear in tuples of the expression are values
from dom(P) (that is, the values appear either in P or in a tuple
of a relation mentioned in P).
[Link] every “there exists” subformula of the form  x (P1(x)), the
subformula is true if and only if there is a value of x in dom(P1)
such that P1(x) is true.
3. For every “for all” subformula of the form x (P1 (x)), the
subformula is true if and only if P1(x) is true for all values x
from dom (P1).

Database System Concepts 3.89 ©Silberschatz, Korth and Sudarshan

This image cannot currently be display ed.

End of Chapter 3

45
 Result of  branch-name = “Perryridge” (loan)

Database System Concepts 3.91 ©Silberschatz, Korth and Sudarshan

Loan Number and the Amount of the Loan

Database System Concepts 3.92 ©Silberschatz, Korth and Sudarshan

46
 Names of All Customers Who Have
Either a Loan or an Account

Database System Concepts 3.93 ©Silberschatz, Korth and Sudarshan

Customers With An Account But No Loan

Database System Concepts 3.94 ©Silberschatz, Korth and Sudarshan

47
 Result of borrower  loan

Database System Concepts 3.95 ©Silberschatz, Korth and Sudarshan

Result of  branch-name = “Perryridge” (borrower  loan)

Database System Concepts 3.96 ©Silberschatz, Korth and Sudarshan

48
 Result of customer-name

Database System Concepts 3.97 ©Silberschatz, Korth and Sudarshan

Result of the Subexpression

Database System Concepts 3.98 ©Silberschatz, Korth and Sudarshan

49
 Largest Account Balance in the Bank

Database System Concepts 3.99 ©Silberschatz, Korth and Sudarshan

Customers Who Live on the Same Street and In the

Same City as Smith

Database System Concepts 3.100 ©Silberschatz, Korth and Sudarshan

50
 Customers With Both an Account and a Loan
at the Bank

Database System Concepts 3.101 ©Silberschatz, Korth and Sudarshan

Result of customer-name, loan-number, amount

(borrower loan)

Database System Concepts 3.102 ©Silberschatz, Korth and Sudarshan

51
 Result of branch-name(customer-city =
“Harrison”(customer account depositor))

Database System Concepts 3.103 ©Silberschatz, Korth and Sudarshan

Result of branch-name(branch-city =
“Brooklyn”(branch))

Database System Concepts 3.104 ©Silberschatz, Korth and Sudarshan

52
 Result of customer-name, branch-name(depositor account)

Database System Concepts 3.105 ©Silberschatz, Korth and Sudarshan

The credit-info Relation

Database System Concepts 3.106 ©Silberschatz, Korth and Sudarshan

53
 Result of customer-name, (limit – credit-balance) as
credit-available(credit-info).

Database System Concepts 3.107 ©Silberschatz, Korth and Sudarshan

The pt-works Relation

Database System Concepts 3.108 ©Silberschatz, Korth and Sudarshan

54
 The pt-works Relation After Grouping

Database System Concepts 3.109 ©Silberschatz, Korth and Sudarshan

Result of branch-name  sum(salary) (pt-works)

Database System Concepts 3.110 ©Silberschatz, Korth and Sudarshan

55
 Result of branch-name  sum salary, max(salary) as
max-salary (pt-works)

Database System Concepts 3.111 ©Silberschatz, Korth and Sudarshan

The employee and ft-works Relations

Database System Concepts 3.112 ©Silberschatz, Korth and Sudarshan

56
 The Result of employee ft-works

Database System Concepts 3.113 ©Silberschatz, Korth and Sudarshan

The Result of employee ft-works

Database System Concepts 3.114 ©Silberschatz, Korth and Sudarshan

57
 Result of employee ft-works

Database System Concepts 3.115 ©Silberschatz, Korth and Sudarshan

Result of employee ft-works

Database System Concepts 3.116 ©Silberschatz, Korth and Sudarshan

58
 Tuples Inserted Into loan and borrower

Database System Concepts 3.117 ©Silberschatz, Korth and Sudarshan

Names of All Customers Who Have a

Loan at the Perryridge Branch

Database System Concepts 3.118 ©Silberschatz, Korth and Sudarshan

59
 E-R Diagram

Database System Concepts 3.119 ©Silberschatz, Korth and Sudarshan

The branch Relation

Database System Concepts 3.120 ©Silberschatz, Korth and Sudarshan

60
 The loan Relation

Database System Concepts 3.121 ©Silberschatz, Korth and Sudarshan

The borrower Relation

Database System Concepts 3.122 ©Silberschatz, Korth and Sudarshan

61
 Chapter 7: SQL

 Basic Structure
 Simple Queries
 Nested Subqueries
 Aggregate Functions
 Set Operations
 With Clause
 Views
 Modification of the Database
 Joined Relations
 Data Definition Language
 Embedded SQL, ODBC and JDBC

Database System Concepts 4.1 ©Silberschatz, Korth and Sudarshan

Basic Structure
 SQL is based on set and relational operations with certain
modifications and enhancements
 A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where predicate
 Ais represent attributes
 ris represent relations (tables)
 predicate is any predicate.
 This query is equivalent to the relational algebra expression.
A1, A2, ..., An(P (r1 x r2 x ... x rm))
 The result of an SQL query is a relation.

 NOTE: SQL does not permit the ‘-’ character in names. SQL names are
case insensitive, i.e. you can use capital or small letters.

Database System Concepts 4.2 ©Silberschatz, Korth and Sudarshan

 Schema Used in Examples
S# Sname Status City
S1 Smith 20 London
S2 Jones 10 Paris
Suppliers
S3 Blake 30 Paris S (S#, Sname, Status, City)
S4 Clark 20 London
S5 Adams 30 Athens
S# P# QTY
S1 P1 300 S2 P1 300
S1 P2 200 S2 P2 400 Shipments
S1 P3 400 S3 P2 200
S1 P4 200 S4 P2 200 SP (S#, P#, QTY)
S1 P5 100 S4 P4 300
S1 P6 100 S4 P5 400
P# Pname Color Weight City
P1 Nut Red 12 London
P2 Bolt Green 17 Paris Parts
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P (P#, Pname, Color, Weight, City)
P5 Cam Blue 12 Paris
P6 Cog Red 19 London

Database System Concepts 4.3 ©Silberschatz, Korth and Sudarshan

Simple Queries (1)

S# Sname Status City Get part numbers for Get part numbers for all parts
S1 Smith 20 London all parts supplied. supplied (no duplicates).
S2 Jones 10 Paris
select P# select distinct P#
S3 Blake 30 Paris
S4 Clark 20 London
from SP ; from SP ;
S5 Adams 30 Athens
Result: Result:
P# Pname Color Weight City
P# P#
P1 Nut Red 12 London P1 P1 P1
P2 Bolt Green 17 Paris P2 P2 P2
P3 Screw Blue 17 Rome P3 P2 P3
P4 P2 P4
P4 Screw Red 14 London P5 P4 P5
P5 Cam Blue 12 Paris P6 P5 P6
P6 Cog Red 19 London
Get supplier numbers from Paris with Status above 20.
S# P# QTY
S1 P1 300 S2 P1 300 select S#
S1 P2 200 S2 P2 400 from S
S1 P3 400 S3 P2 200 where City = ‘Paris’ and Status > 25;
S1 P4 200 S4 P2 200
Result:
S1 P5 100 S4 P4 300
S#
S1 P6 100 S4 P5 400
S3

Database System Concepts 4.4 ©Silberschatz, Korth and Sudarshan

 Simple Queries (2)
S# Sname Status City Get supplier numbers and For all blue parts, get
S1 Smith 20 London status for suppliers in Paris the weights in grams.
S2 Jones 10 Paris in desceding order of status.
S3 Blake 30 Paris
select S#, Status select P#, Weight454
S4 Clark 20 London
from S from P
S5 Adams 30 Athens
where City = ‘Paris’ where Color = ‘Blue’
P# Pname Color Weight City
order by Status desc ; order by 2, P# ;
P1 Nut Red 12 London
P2 Bolt Green 17 Paris Result: Result:
P3 Screw Blue 17 Rome
S# Status P# Weight
P4 Screw Red 14 London S3 30 P5 5448
P5 Cam Blue 12 Paris S2 10 P3 7718
P6 Cog Red 19 London
Include constatnt in select clause.
S# P# QTY
select P#, ‘Weights in grams = ‘, Weight*454
S1 P1 300 S2 P1 300
from P
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200
where Color = ‘Blue’ ;
S1 P4 200 S4 P2 200 Result:
S1 P5 100 S4 P4 300 P#
S1 P6 100 S4 P5 400 P3 Weights in grams = 7718
P5 Weights in grams = 5448

Database System Concepts 4.5 ©Silberschatz, Korth and Sudarshan

Simple Queries (between)

S# Sname Status City Get parts whose weight is in range 16 to 19 (inclusive).
S1 Smith 20 London select 
S2 Jones 10 Paris
from P
S3 Blake 30 Paris
where Weight between 16 and 19 ;
S4 Clark 20 London
S5 Adams 30 Athens Result:
P# Pname Color Weight City P# Pname Color Weight City
P1 Nut Red 12 London P2 Bolt Green 17 Paris
P2 Bolt Green 17 Paris P3 Screw Blue 17 Rome
P3 Screw Blue 17 Rome P6 Cog Red 19 London
P4 Screw Red 14 London
P5 Cam Blue 12 Paris Get parts whose weight is not in range 16 to 19.
P6 Cog Red 19 London select P#, Pname, Color, Weight, City
S# P# QTY from P
S1 P1 300 S2 P1 300 where Weight not between 16 and 19 ;
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200 Result:
S1 P4 200 S4 P2 200 P# Pname Color Weight City
S1 P5 100 S4 P4 300 P1 Nut Red 12 London
P4 Screw Red 14 London
S1 P6 100 S4 P5 400 P5 Cam Blue 12 Paris

Database System Concepts 4.6 ©Silberschatz, Korth and Sudarshan

 Simple Queries (in)
S# Sname Status City Get parts whose weight is in range 16 to 19 (inclusive).
S1 Smith 20 London select 
S2 Jones 10 Paris from P
S3 Blake 30 Paris where Weight in {12, 16, 17} ;
S4 Clark 20 London
S5 Adams 30 Athens Result:
P# Pname Color Weight City
P# Pname Color Weight City
P1 Nut Red 12 London
P1 Nut Red 12 London P2 Bolt Green 17 Paris
P2 Bolt Green 17 Paris P3 Screw Blue 17 Rome
P3 Screw Blue 17 Rome P5 Cam Blue 12 Paris
P4 Screw Red 14 London
P5 Cam Blue 12 Paris Get parts whose weight is not in range 16 to 19.
P6 Cog Red 19 London
S# P# QTY
select P#, Pname, Color, Weight, City
S1 P1 300 S2 P1 300
from P
S1 P2 200 S2 P2 400 where Weight not in {12, 16, 17} ;
S1 P3 400 S3 P2 200
Result:
S1 P4 200 S4 P2 200
S1 P5 100 S4 P4 300
P# Pname Color Weight City
P4 Screw Red 14 London
S1 P6 100 S4 P5 400 P6 Cog Red 19 London

Database System Concepts 4.7 ©Silberschatz, Korth and Sudarshan

Simple Queries (like)

S# Sname Status City
Get parts whose names begin with the letter C.
S1 Smith 20 London select 
S2 Jones 10 Paris from P
S3 Blake 30 Paris where Pname like ‘C*’ ;
S4 Clark 20 London
S5 Adams 30 Athens Result:
P# Pname Color Weight City
P# Pname Color Weight City
P1 Nut Red 12 London P5 Cam Blue 12 Paris
P2 Bolt Green 17 Paris P6 Cog Red 19 London
P3 Screw Blue 17 Rome % stands for any string, ? stands for any character
P4 Screw Red 14 London
P5 Cam Blue 12 Paris Sname like ‘?la*’ – all supplier names with second
P6 Cog Red 19 London character l and third characer a.
S# P# QTY Pname like ‘????’ – all part names 4 character long.
S1 P1 300 S2 P1 300 City not like ‘*o*’ – all city names which does not
S1 P2 200 S2 P2 400 contain characer o.
S1 P3 400 S3 P2 200 like ‘Main\*’ escape ‘\’ – match Main*
S1 P4 200 S4 P2 200
SQL supports a variety of string operations such as: con-
S1 P5 100 S4 P4 300
catenation (“||”), converting from upper to lower case (and
S1 P6 100 S4 P5 400 vice versa), finding string length, extracting substrings, etc.

Database System Concepts 4.8 ©Silberschatz, Korth and Sudarshan

 Simple Queries (null values)
S# Sname Status City
Get parts whose color is not null.
S1 Smith 20 London select 
S2 Jones 10 Paris from P
S3 Blake 30 Paris where Color is not null ;
S4 Clark 20 London
S5 Adams 30 Athens Result:
P# Pname Color Weight City P# Pname Color Weight City
P1 Nut Red 12 London P1 Nut Red 12 London
P2 Bolt Green 17 Paris P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P4 Screw Red 14 London P5 Cam Blue 12 Paris
P5 Cam Blue 12 Paris P6 Cog Red 19 London
P6 Cog Red 19 London
S# P# QTY
S1 P1 300 S2 P1 300 null signifies an unknown value or that a value does not
S1 P2 200 S2 P2 400 exist.
The result of any arithmetic expression involving null is null
S1 P3 400 S3 P2 200
(E.g. 5 + null returns null).
S1 P4 200 S4 P2 200
S1 P5 100 S4 P4 300 Any comparison with null returns unknown (E.g. 5 < null
S1 P6 100 S4 P5 400 or null <> null or null = null).

Database System Concepts 4.9 ©Silberschatz, Korth and Sudarshan

Simple Queries (natural join)

S# Sname Status City Get all combination suppliers - parts located in the
S1 Smith 20 London same city.
S2 Jones 10 Paris select S., P.
S3 Blake 30 Paris from S, P
S4 Clark 20 London where [Link] = [Link] ;
S5 Adams 30 Athens
Result:
P# Pname Color Weight City S# Sname Status [Link] P# Pname Color Weight [Link]
P1 Nut Red 12 London S1 Smith 20 London P1 Nut Red 12 London
S1 Smith 20 London P4 Screw Red 14 London
P2 Bolt Green 17 Paris S1 Smith 20 London P6 Cog Red 19 London
P3 Screw Blue 17 Rome S2 Jones 10 Paris P2 Bolt Green 17 Paris
P4 Screw Red 14 London S2 Jones
S3 Blake
10
30
Paris
Paris
P5
P2
Cam
Bolt
Blue 12
Green 17
Paris
Paris
P5 Cam Blue 12 Paris S3 Blake 30 Paris P5 Cam Blue 12 Paris
P6 Cog Red 19 London S4 Clark 20 London P5 Nut Red 12 London
S4 Clark 20 London P5 Screw Red 14 London
S# P# QTY S4 Clark 20 London P5 Cog Red 19 London
S1 P1 300 S2 P1 300
S1 P2 200 S2 P2 400 How conceptualy join is constructed:
S1 P3 400 S3 P2 200 - Form the cartesian product of the tables listed in from clause
S1 P4 200 S4 P2 200 (in our example the new table will have 56 = 30 rows)
S1 P5 100 S4 P4 300 - Eliminate from the cartesian product all those rows that do
S1 P6 100 S4 P5 400 not satisfy join predicate (where clause)

Database System Concepts 4.10 ©Silberschatz, Korth and Sudarshan

 Simple Queries (natural join)
Same, but suplier city follows part city (alphabetically).
S# Sname Status City
S1 Smith 20 London select S., P.
S2 Jones 10 Paris from S, P
S3 Blake 30 Paris where [Link] > [Link] ;
S4 Clark 20 London
S5 Adams 30 Athens Result:
S# Sname Status [Link] P# Pname Color Weight [Link]
P# Pname Color Weight City S2 Jones 10 Paris P1 Nut Red 12 London
P1 Nut Red S2 Jones
12 London S2 Jones 10 10 Paris P4 Screw Red 14 London
Paris P6 Cog Red 19 London
P2 Bolt Green 17 Paris S3 Blake 30 Paris P1 Nut Red 12 London
P3 Screw Blue 17 Rome S3 Blake 30 Paris P4 Screw Red 14 London
S3 Blake 30 Paris P6 Cog Red 19 London
P4 Screw Red 14 London
P5 Cam Blue 12 Paris Get all combination suppliers - parts located in the
P6 Cog Red 19 London same city, without suppliers that have status 20.
S# P# QTY select S., P.
S1 P1 300 S2 P1 300 from S, P
S1 P2 200 S2 P2 400 where [Link] = [Link] and [Link] <> 20 ;
S1 P3 400 S3 P2 200
S1 P4 200 S4 P2 200
Result:
S# Sname Status [Link] P# Pname Color Weight [Link]
S1 P5 100 S4 P4 300 S2 Jones 10 Paris P2 Bolt Green 17 Paris
S1 P6 100 S4 P5 400 S2 Jones 10 Paris P5 Cam Blue 12 Paris
S3 Blake 30 Paris P2 Bolt Green 17 Paris
S3 Blake 30 Paris P5 Cam Blue 12 Paris

Database System Concepts 4.11 ©Silberschatz, Korth and Sudarshan

Simple Queries (natural join)

S# Sname Status City
S1 Smith 20 London
Get all pairs of city names such that a supplier located
S2 Jones 10 Paris in the first city supplies a part stored in the second city.
S3 Blake 30 Paris For example, supplier S1 supplies part P1; suppliers S1 is
S4 Clark 20 London located in London, and part P1 is stored in London; so
S5 Adams 30 Athens ‘London, London’ is a pair of cities in the result.
P# Pname Color Weight City
P1 Nut Red 12 London select distinct [Link], [Link]
P2 Bolt Green 17 Paris from S, SP, P
P3 Screw Blue 17 Rome where S.S# = SP.S# and SP.P# = P.P# ;
P4 Screw Red 14 London
P5 Cam Blue 12 Paris Result:
P6 Cog Red 19 London
[Link] [Link]
S# P# QTY London London
S1 P1 300 S2 P1 300 London Paris
London Rome
S1 P2 200 S2 P2 400
Paris London
S1 P3 400 S3 P2 200 Paris Paris
S1 P4 200 S4 P2 200
S1 P5 100 S4 P4 300 This example shows join of 3 tables.
S1 P6 100 S4 P5 400

Database System Concepts 4.12 ©Silberschatz, Korth and Sudarshan

 Simple Queries (join a table with itself)
S# Sname Status City
Get all pairs of supplier numbers such that the two
S1 Smith 20 London
suppliers are co-located.
S2 Jones 10 Paris select Sup1.S#, Sup2.S#
S3 Blake 30 Paris from S as Sup1, S as Sup2
S4 Clark 20 London where [Link] = [Link] ;
S5 Adams 30 Athens
Result:
P# Pname Color Weight City
S# S#
P1 Nut Red 12 London S1 S1 S3 S3
P2 Bolt Green 17 Paris S1 S4 S4 S1
P3 Screw Blue 17 Rome S2 S2 S4 S4
S2 S3 S5 S5
P4 Screw Red 14 London S3 S2
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
This result can be cleared up as follows:
S# P# QTY select Sup1.S#, Sup2.S#
S1 P1 300 S2 P1 300 from S as Sup1, S as Sup2
S1 P2 200 S2 P2 400 where [Link] = [Link] and Sup1.S# > Sup2.S# ;
S1 P3 400 S3 P2 200
S1 P4 200 S4 P2 200 Result:
S1 P5 100 S4 P4 300 S# S#
S1 P6 100 S4 P5 400
S1 S4
S2 S3

Database System Concepts 4.13 ©Silberschatz, Korth and Sudarshan

SubQueries
S# Sname Status City Get suppliers names for suppliers who supplies part P2.
S1 Smith 20 London
select [Link]
S2 Jones 10 Paris
from S
S3 Blake 30 Paris
where S.S# in ( select SP.S#
S4 Clark 20 London
from SP
S5 Adams 30 Athens
where SP.P# = ‘P2’ ) ;
P# Pname Color Weight City
P1 Nut Red 12 London Result: The nested subqueries are evaluated first.
P2 Bolt Green 17 Paris Sname So, our query is equivalent to:
P3 Screw Blue 17 Rome Smith select [Link]
Jones from S
P4 Screw Red 14 London Blake
P5 Cam Blue 12 Paris Clark where S.S# in ( ‘S1’, ‘S2’, ‘S3’, ‘S4’ ) ;
P6 Cog Red 19 London
The same using join.
S# P# QTY
S1 P1 300 S2 P1 300 select [Link]
S1 P2 200 S2 P2 400 from S, SP
S1 P3 400 S3 P2 200 where S.S# = SP.S# and SP.P# = ‘P2’ ;
S1 P4 200 S4 P2 200 The join of S and SP over supplier numbers
S1 P5 100 S4 P4 300 is a table of 12 rows from which we select
S1 P6 100 S4 P5 400 those 4 rows that have the part number P2.

Database System Concepts 4.14 ©Silberschatz, Korth and Sudarshan

 SubQueries (correlated)
Get suppliers names for suppliers who supplies part P2.
S# Sname Status City
S1 Smith 20 London select Sname
S2 Jones 10 Paris from S
S3 Blake 30 Paris where ‘P2’ in ( select P#
S4 Clark 20 London from SP
S5 Adams 30 Athens where S# = S.S# ) ;
P# Pname Color Weight City Result: In the last line the unqualified reference S# is
P1 Nut Red 12 London implicitl qualified by SP. Here, inner subquery
Sname
P2 Bolt Green 17 Paris Smith cannot be evaluated once and for all before the
P3 Screw Blue 17 Rome Jones outher query is evaluated (variable S.S# is
Blake uknown). Such subqueries are called correlated.
P4 Screw Red 14 London
Clark The system examines one by one rows of table S
P5 Cam Blue 12 Paris
P6 Cog Red 19 London and each time evaluate the subquery.
S# P# QTY Some people prefer to use aliases in correlated
S1 P1 300 S2 P1 300 subqueries.
S1 P2 200 S2 P2 400
select [Link]
S1 P3 400 S3 P2 200
from S as SX
S1 P4 200 S4 P2 200
where ‘P2’ in ( select P#
S1 P5 100 S4 P4 300
from SP
S1 P6 100 S4 P5 400
where S# = SX.S# ) ;

Database System Concepts 4.15 ©Silberschatz, Korth and Sudarshan

SubQueries (more nesting)

S# Sname Status City Get suppliers names for suppliers who supplie at least
S1 Smith 20 London one red part.
S2 Jones 10 Paris select Sname
S3 Blake 30 Paris from S
S4 Clark 20 London where S# in ( select S#
S5 Adams 30 Athens from SP
P# Pname Color Weight City where P# in ( select P#
P1 Nut Red 12 London from P
P2 Bolt Green 17 Paris where Color = ‘Red’ ) );
P3 Screw Blue 17 Rome The innermost subquery evaluates to the set {‘P1’,
P4 Screw Red 14 London
Result:
‘P4’, ‘P6’}. The next subquery evaluates in turn to
P5 Cam Blue 12 Paris Sname
Smith the set {‘S1’, ‘S2’, ‘S4’}. Last, the outermost
P6 Cog Red 19 London Jones select evaluates to the final result. In general,
S# P# QTY Clark subqueries can be nested to any depth.
S1 P1 300 S2 P1 300
The same using join.
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200 select distinct [Link]
S1 P4 200 S4 P2 200 from S, SP, P
S1 P5 100 S4 P4 300 where S.S# = SP.S# and SP.P# = P.P#
S1 P6 100 S4 P5 400 and [Link] = ‘Red’ ;

Database System Concepts 4.16 ©Silberschatz, Korth and Sudarshan

 SubQueries (with same table)
S# Sname Status City Get supplier numbers for suppliers who supply at least
S1 Smith 20 London one part supplied by supplier S2.
S2 Jones 10 Paris
S3 Blake 30 Paris select distinct S#
S4 Clark 20 London from SP
S5 Adams 30 Athens where P# in ( select P#
P# Pname Color Weight City from SP
P1 Nut Red 12 London where S# = ‘S2’ );
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
Result:
S# The reference SP in the subquery does not mean
P4 Screw Red 14 London the same thing as reference to SP in the outher
S1
P5 Cam Blue 12 Paris S2 query. They are different variables. Using aliases
P6 Cog Red 19 London S3 will make this fact explicit.
S# P# QTY S4
S1 P1 300 S2 P1 300
The same using join.
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200 select distinct SP1.S#
S1 P4 200 S4 P2 200 from SP as SP1, SP as SP2
S1 P5 100 S4 P4 300 where SP1.P# = SP2.P#
S1 P6 100 S4 P5 400 and SP2.S# = ‘S2’ ;

Database System Concepts 4.17 ©Silberschatz, Korth and Sudarshan

SubQueries (correlated with same table)

S# Sname Status City Get part numbers for all parts supplied by more than
S1 Smith 20 London one supplier.
S2 Jones 10 Paris
S3 Blake 30 Paris select distinct SP1.P#
S4 Clark 20 London from SP as SP1
S5 Adams 30 Athens where SP1.P# in ( select SP2.P#
P# Pname Color Weight City from SP as SP2
P1 Nut Red 12 London where SP2.S# = SP1.S# );
P2 Bolt Green 17 Paris Result:
P3 Screw Blue 17 Rome Operation of this query: For each row in turn, SP1
P#
P4 Screw Red 14 London P1 of table SP, extract the P# value, iff that P# value
P5 Cam Blue 12 Paris P2 appears in some row SP2 of table SP whose S#
P4 value is not that in row SP1. Note that at least one
P6 Cog Red 19 London P5 alias must be used, but not both.
S# P# QTY
S1 P1 300 S2 P1 300 Get supplier numbers for suppliers who are located in
S1 P2 200 S2 P2 400 the same city as supplier S1. Result:
S1 P3 400 S3 P2 200 select S#
S#
S1 P4 200 S4 P2 200 from S
S1
S1 P5 100 S4 P4 300 where City = ( select City S4
S1 P6 100 S4 P5 400 from S
where S# = ‘S1’ );
Database System Concepts 4.18 ©Silberschatz, Korth and Sudarshan
 SubQueries (exists)
S# Sname Status City Get suppliers names for suppliers who supplies part P2.
S1 Smith 20 London select Sname
S2 Jones 10 Paris from S
S3 Blake 30 Paris
where exists ( select 
S4 Clark 20 London
from SP
S5 Adams 30 Athens
where S# = S.S# and P# = ‘P2’ );
P# Pname Color Weight City
Result:
P1 Nut Red 12 London Predicate exists x (predicate-involving-x) is true iff
Sname
P2 Bolt Green 17 Paris predicate-involving-x is true for some x. For exam-
Smith
P3 Screw Blue 17 Rome Jones ple if x=1,2,…,10 then exists x (x<5) is true, while
P4 Screw Red 14 London Blake exists x (x<0) is false.
P5 Cam Blue 12 Paris Clark
P6 Cog Red 19 London Get suppliers names for suppliers who do not supply
S# P# QTY part P2. In general, exists is one of the most important SQL
S1 P1 300 S2 P1 300 select Sname feature. In fact, any query expresssed using in can
S1 P2 200 S2 P2 400 from S be formulated using exists. The converse is not true.
S1 P3 400 S3 P2 200
where not exists ( select 
S1 P4 200 S4 P2 200
from SP
S1 P5 100 S4 P4 300 Result:
Sname
where S# = S.S# and P# = ‘P2’ );
S1 P6 100 S4 P5 400
Adams

Database System Concepts 4.19 ©Silberschatz, Korth and Sudarshan

SubQueries (not exists)

S# Sname Status City Get supplier names for suppliers who supply all parts.
S1 Smith 20 London
S2 Jones 10 Paris
select Sname
S3 Blake 30 Paris from S
S4 Clark 20 London where not exists
S5 Adams 30 Athens ( select 
P# Pname Color Weight City from P
P1 Nut Red 12 London where not exists
P2 Bolt Green 17 Paris ( select 
P3 Screw Blue 17 Rome from SP
P4 Screw Red 14 London where S# = S.S# and P# = [Link] );
P5 Cam Blue 12 Paris
P6 Cog Red 19 London Result:
S# P# QTY Sname
S1 P1 300 S2 P1 300 Smith
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200 The query can be paraphrased according to the above
S1 P4 200 S4 P2 200 SQL statement: Select supplier names for supplier such
S1 P5 100 S4 P4 300 that there does not exists a part that they do not supply.
S1 P6 100 S4 P5 400

Database System Concepts 4.20 ©Silberschatz, Korth and Sudarshan

 SubQueries (all, some)
S# Sname Status City Get the all part numbers that have greater shipment
S1 Smith 20 London quantity than all parts located in London.
S2 Jones 10 Paris
S3 Blake 30 Paris select P# Result:
S4 Clark 20 London from SP
P#
S5 Adams 30 Athens where QTY > all
P3
P# Pname Color Weight City ( select QTY P2
P1 Nut Red 12 London from SP, P P5
P2 Bolt Green 17 Paris where City = ‘London’ ) ;
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris Get the all part numbers that have greater shipment
P6 Cog Red 19 London quantity than some part located in London. Result:
S# P# QTY select P# P#
S1 P1 300 S2 P1 300 from SP P1
S1 P2 200 S2 P2 400 P2
where QTY > some P3
S1 P3 400 S3 P2 200 ( select QTY P4
S1 P4 200 S4 P2 200 from SP, P P5
S1 P5 100 S4 P4 300 where City = ‘London’ ) ;
S1 P6 100 S4 P5 400

Database System Concepts 4.21 ©Silberschatz, Korth and Sudarshan

Definition of Some and All Clauses

0 0
(5< some 5 ) = true (5< some 5 ) = false
6
0 0
(5 = some 5 ) = true (5  some 5 ) = true (since 0  5)

(= some)  in. However, ( some)  not in

0 6
(5< all 5 ) = false (5< all ) = true
10
6
4 4
(5 = all 5 ) = false (5  all 6 ) = true (since 5  4 and 5  6)

( all)  not in. However, (= all)  in

Database System Concepts 4.22 ©Silberschatz, Korth and Sudarshan

 Aggregate Functions (count, sum, max)
S# Sname Status City Get the number of shipments for part P2.
S1 Smith 20 London
S2 Jones 10 Paris
select count() Result:
S3 Blake 30 Paris from SP
where P# = ‘P2’ ; 4
S4 Clark 20 London
S5 Adams 30 Athens
P# Pname Color Weight City Get the total quantity of part P2 supplied.
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
select sum(QTY)
from SP Result:
P3 Screw Blue 17 Rome
P4 Screw Red 14 London where P# = ‘P2’ ; 1000
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
Get supplier numbers for suppliers with status less
S# P# QTY
S1 P1 300 S2 P1 300
then current maximum status.
S1 P2 200 S2 P2 400 select S# Result:
S1 P3 400 S3 P2 200 from S
S#
S1 P4 200 S4 P2 200 where Status < S1
S1 P5 100 S4 P4 300 ( select max(Status) S2
S1 P6 100 S4 P5 400 from S ) ; S4

Database System Concepts 4.23 ©Silberschatz, Korth and Sudarshan

Aggregate Functions (min, avg)

S# Sname Status City
Get the all part names for parts with minimum
S1 Smith 20 London
weights.
S2 Jones 10 Paris
S3 Blake 30 Paris select Pname Result:
S4 Clark 20 London from P
S5 Adams 30 Athens where Weight = Pname
Nut
P# Pname Color Weight City ( select min(Weight)
Cam
P1 Nut Red 12 London from P ) ;
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London Get supplier numbers, status nad city for all suppliers
P5 Cam Blue 12 Paris whose status is greater than or equal to the average
P6 Cog Red 19 London for their city.
Result:
S# P# QTY select S#, Status, City
S1 P1 300 S2 P1 300 from S as S1 S# Status City
S1 P2 200 S2 P2 400 S1 20 London
where Status >= S3 30 Paris
S1 P3 400 S3 P2 200 ( select avg(Status) S4 30 London
S1 P4 200 S4 P2 200
from S as S2 S5 30 Athens
S1 P5 100 S4 P4 300 where [Link] = [Link] ) ;
S1 P6 100 S4 P5 400

Database System Concepts 4.24 ©Silberschatz, Korth and Sudarshan

 Aggregate Functions (group by)
S# Sname Status City Get the total quantity supplied for each part.
S1 Smith 20 London
S2 Jones 10 Paris select P#, sum(QTY) Result:
S3 Blake 30 Paris from SP P#
S4 Clark 20 London group by P# ; P1 600
S5 Adams 30 Athens P2 1000
P3 400
P# Pname Color Weight City P4 500
P1 Nut Red 12 London P5 500
P2 Bolt Green 17 Paris P6 100
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris For each part supplied, get the part number and the
P6 Cog Red 19 London total quantity supplied of that part, excluding
S# P# QTY shipment from supplier S1.
S1 P1 300 S2 P1 300 select P#, sum(QTY) Result:
S1 P2 200 S2 P2 400 from SP P#
S1 P3 400 S3 P2 200 where S# <> ‘S1’ P1 300
S1 P4 200 S4 P2 200 group by P# ; P2 800
S1 P5 100 S4 P4 300 P4 300
P5 400
S1 P6 100 S4 P5 400

Database System Concepts 4.25 ©Silberschatz, Korth and Sudarshan

Aggregate Functions (having)

S# Sname Status City Get part numbers for all parts supplied by more than
S1 Smith 20 London one supplier.
S2 Jones 10 Paris Result:
S3 Blake 30 Paris select P#
P#
S4 Clark 20 London from SP
P1
S5 Adams 30 Athens group by P# P2
P# Pname Color Weight City having count() > 1 ; P4
P1 Nut Red 12 London
P5
P2 Bolt Green 17 Paris Having is to groups what where is to rows. (If having is
P3 Screw Blue 17 Rome specified, group by should be also specified). Having is
P4 Screw Red 14 London
used to eliminate groups just as where is used to
P5 Cam Blue 12 Paris
eliminate rows.
P6 Cog Red 19 London
S# P# QTY
S1 P1 300 S2 P1 300
The same without group by/having.
S1 P2 200 S2 P2 400 select P#,
S1 P3 400 S3 P2 200 from P
S1 P4 200 S4 P2 200 where 1 < ( select count(S#)
S1 P5 100 S4 P4 300 from SP
S1 P6 100 S4 P5 400 where P# = P.P# );

Database System Concepts 4.26 ©Silberschatz, Korth and Sudarshan

 Set Operations (union)
S# Sname Status City Get part numbers for parts with weight more than 16
S1 Smith 20 London pounds or are supplied by supplier S2.
S2 Jones 10 Paris Result:
S3 Blake 30 Paris
select P#
P#
S4 Clark 20 London from P P1
S5 Adams 30 Athens where Weight > 16 union select P# P2
P# Pname Color Weight City from SP P3
P1 Nut Red 12 London where S# = ‘S2’ ; P6
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome Since a relation is set of rows, it is possible to construct union, in-
P4 Screw Red 14 London tersection and difference between them. However, to be result a
P5 Cam Blue 12 Paris
relation the two original relation must be set-compatable:
P6 Cog Red 19 London 1. to have the same number of columns.
2. the i-th column of both relations must have the same data type.
S# P# QTY
S1 P1 300 S2 P1 300 The set operations union, intersect, and except operate on
S1 P2 200 S2 P2 400 relations and correspond to the relational algebra operations
S1 P3 400 S3 P2 200 
S1 P4 200 S4 P2 200 Each of the above operations automatically eliminates duplicates;
S1 P5 100 S4 P4 300 to retain all duplicates use the corresponding multiset versions
S1 P6 100 S4 P5 400 union all, intersect all and except all.

Database System Concepts 4.27 ©Silberschatz, Korth and Sudarshan

Set Operations (intersect, except)

S# Sname Status City Get supplier numbers for suppliers who supply part
S1 Smith 20 London P1 and are located in London.
S2 Jones 10 Paris
select S# Result:
S3 Blake 30 Paris
S4 Clark 20 London from SP S#
S5 Adams 30 Athens where P# = ‘P1’ intersect select S# S1

P# Pname Color Weight City from S

P1 Nut Red 12 London where City = ‘London’ ;
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome Get supplier numbers for suppliers who supply part
P4 Screw Red 14 London
P2 and are not located in London.
P5 Cam Blue 12 Paris
P6 Cog Red 19 London select S#
S# P# QTY from SP
S1 P1 300 S2 P1 300 where P# = ‘P2’ except select S#
S1 P2 200 S2 P2 400 from S
S1 P3 400 S3 P2 200 Result: where City = ‘London’ ;
S1 P4 200 S4 P2 200
S#
S1 P5 100 S4 P4 300 S2
S1 P6 100 S4 P5 400 S3

Database System Concepts 4.28 ©Silberschatz, Korth and Sudarshan

 A Comprehensive Example
S# Sname Status City For all red and blue parts such that the total quantity suppli-
S1 Smith 20 London ed is greater than 350 (excluding from the total all shipments
S2 Jones 10 Paris for which the quantity is less than or equal to 200), get the
S3 Blake 30 Paris part number, the weight in grams, the color, and the maxi-
mum supplied of that part. Order the result by decreasing
S4 Clark 20 London
part number within asceding values of that maximum.
S5 Adams 30 Athens
P# Pname Color Weight City select P.P#, ‘Weight in grams = ‘, [Link]454,
P1 Nut Red 12 London [Link], ‘MSQuantity = ‘, max ([Link])
P2 Bolt Green 17 Paris from P, SP
P3 Screw Blue 17 Rome where P.P# = SP.P#
P4 Screw Red 14 London and [Link] in (‘Red’, ‘Blue’)
P5 Cam Blue 12 Paris and [Link] > 200
P6 Cog Red 19 London group by P.P#, [Link]; [Link]
S# P# QTY having sum (QTY) > 350
S1 P1 300 S2 P1 300 order by 6, P.P#, desc ;
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200 Result:
S1 P4 200 S4 P2 200 P# Color
P1 Weight in grams = 5448 Red MSQuantity = 300
S1 P5 100 S4 P4 300 P5 Weight in grams = 5448 Blue MSQuantity = 400
S1 P6 100 S4 P5 400 P3 Weight in grams = 7718 Blue MSQuantity = 400

Database System Concepts 4.29 ©Silberschatz, Korth and Sudarshan

With Clause
Get all supplier names with maximum status.
S# Sname Status City
with maxst(value) as
S1 Smith 20 London
select max(Status) Result:
S2 Jones 10 Paris
from S
S3 Blake 30 Paris
Sname
select Sname
Blake
S4 Clark 20 from S
London
Adams
S5 Adams 30 where Status = [Link];
Athens
P# Pname Color Weight City With clause allows views to be defined locally to a query, rather
P1 Nut Red 12 London than globally. Analogous to procedures in a programming language.
P2 Bolt Green 17 Paris Get all part numbers where the total their shipments is greater
P3 Screw Blue 17 Rome than the average of the total supplier shipments at all
P4 Screw Red 14 London suppliers.
P5 Cam Blue 12 Paris with ptotal(P#, value) as
P6 Cog Red 19 London select P#, sum(QTY) Result:
S# P# QTY from SP P#
S1 P1 300 S2 P1 300 group by P# P1
S1 P2 200 S2 P2 400
with pavg(S#, value) as P2
select S#, avg(QTY)
S1 P3 400 S3 P2 200
from SP
S1 P4 200 S4 P2 200
group by P#
S1 P5 100 S4 P4 300 select P#
S1 P6 100 S4 P5 400 from ptotal, pavg
where [Link] > [Link];

Database System Concepts 4.30 ©Silberschatz, Korth and Sudarshan

 Derived Relations
S# Sname Status City Get the average quantity of those supplier shipments
S1 Smith 20 London where the average quantity is greater than 250.
S2 Jones 10 Paris
S3 Blake 30 Paris select S#, AvgShip Result:
S4 Clark 20 London from (select S#, avg (QTY)
S5 Adams 30 Athens S# AvgShip
from SP S2 350
P# Pname Color Weight City group by S#) S4 300
P1 Nut Red 12 London as result (S#, AvgShip)
P2 Bolt Green 17 Paris
where AvgShip > 250
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
Note that we do not need to use the having clause, since
P5 Cam Blue 12 Paris
we compute the temporary (view) relation result in the
P6 Cog Red 19 London
from clause, and the attributes of result can be used
S# P# QTY
directly in the where clause.
S1 P1 300 S2 P1 300
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200
S1 P4 200 S4 P2 200
S1 P5 100 S4 P4 300
S1 P6 100 S4 P5 400

Database System Concepts 4.31 ©Silberschatz, Korth and Sudarshan

Views
S# Sname Status City Create view from good suppliers (with status greater
S1 Smith 20 London than 15).
S2 Jones 10 Paris
S# Status City
S3 Blake 30 Paris create view GoodSup S1 20 London
S4 Clark 20 London as select S#, Status,City
S5 Adams 30 Athens S3 30 Paris
from S S4 20 London
P# Pname Color Weight City where Status > 15 ; S5 30 Athens
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
GoodSup is in effect a “window” into real table S. The window
P3 Screw Blue 17 Rome
is dynamic because changes of S is automatically visible
P4 Screw Red 14 London
through the window GoodSup. Some users may genuinely
P5 Cam Blue 12 Paris believe that GoodSup is a “real” table.
P6 Cog Red 19 London
S# P# QTY Provide a mechanism to hide certain data from the view of
S1 P1 300 S2 P1 300 certain users. To create a view we use the command:
S1 P2 200 S2 P2 400
create view v as <query expression>
S1 P3 400 S3 P2 200
S1 P4 200 S4 P2 200 where:
S1 P5 100 S4 P4 300 • <query expression> is any legal expression
S1 P6 100 S4 P5 400 • the view name is represented by v

Database System Concepts 4.32 ©Silberschatz, Korth and Sudarshan

 Views
S# Sname Status City Query on view (suppliers not located in London).
S1 Smith 20 London Result:
S2 Jones 10 Paris select S#, City
S3 Blake 30 Paris
S# City
from GoodSup S3 Paris
S4 Clark 20 London
where City <> ‘London’ ; S5 Athens
S5 Adams 30 Athens
P# Pname Color Weight City
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome Create view of part numbers and names for parts
P4 Screw Red 14 London with weight more than 16 pounds or are supplied
P5 Cam Blue 12 Paris by supplier S2.
P6 Cog Red 19 London
S# P# QTY select P#, Pname Result:
S1 P1 300 S2 P1 300 from P P# Pname
S1 P2 200 S2 P2 400 where Weight > 16 union P1 Nut
S1 P3 400 S3 P2 200 select distinct P#, Pname P2 Bolt
P3 Screw
S1 P4 200 S4 P2 200 from P, SP P6 Cog
S1 P5 100 S4 P4 300 where P.P# = SP.P#
S1 P6 100 S4 P5 400 and S# = ‘S2’ ;

Database System Concepts 4.33 ©Silberschatz, Korth and Sudarshan

Modification of the Database – Deletion

S# Sname Status City Delete all suppliers in Paris.
S1 Smith 20 London
S2 Jones 10 Paris delete S#, City
S3 Blake 30 Paris from S
S4 Clark 20 London where City = ‘Paris’ ;
S5 Adams 30 Athens
P# Pname Color Weight City Delete all shipments.
P1 Nut Red 12 London
P2 Bolt Green 17 Paris delete
P3 Screw Blue 17 Rome from SP ;
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
S# P# QTY Delete all shipments for suppliers in London.
S1 P1 300 S2 P1 300
S1 P2 200 S2 P2 400 delete
S1 P3 400 S3 P2 200 from SP
S1 P4 200 S4 P2 200 where ‘London’ = ( select City
S1 P5 100 S4 P4 300 from S
S1 P6 100 S4 P5 400 where S.S# = SP.S# ) ;

Database System Concepts 4.34 ©Silberschatz, Korth and Sudarshan

 Modification of the Database – Deletion
S# Sname Status City General form of delete statement:
S1 Smith 20 London
S2 Jones 10 Paris delete
S3 Blake 30 Paris from table
S4 Clark 20 London [ where predicate ]
S5 Adams 30 Athens
P# Pname Color Weight City
P1 Nut Red 12 London
Delete all shipments with quantity below the average.
P2 Bolt Green 17 Paris
delete
P3 Screw Blue 17 Rome
from SP
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
where QTY < ( select avg(QTY)
P6 Cog Red 19 London from SP ) ;
S# P# QTY Problem: as we delete tuples from SP, the
S1 P1 300 S2 P1 300 average quantity changes
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200
Solution used in SQL:
S1 P4 200 S4 P2 200 1. First, compute avg balance and find all tuples to delete
S1 P5 100 S4 P4 300 2. Next, delete all tuples found above (without
S1 P6 100 S4 P5 400 recomputing avg or retesting the tuples)

Database System Concepts 4.35 ©Silberschatz, Korth and Sudarshan

Modification of the Database – Insertion

S# Sname Status City Add part P7 with unknown name and color.
S1 Smith 20 London
S2 Jones 10 Paris insert
S3 Blake 30 Paris into P (P#, City, Weight) Name and color will
S4 Clark 20 London values (‘P7’, ‘Athens’, 2) ; have null values.
S5 Adams 30 Athens
P# Pname Color Weight City Add part P8 to table P.
P1 Nut Red 12 London
P2 Bolt Green 17 Paris insert
P3 Screw Blue 17 Rome into P
P4 Screw Red 14 London values (‘P8’, ‘Sprocket’, ‘Pink’, 14, ‘Nice’) ;
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
S# P# QTY
Add a new shipment with supplier S20, part number
S1 P1 300 S2 P1 300
p20, and quantity 1000.
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200 insert
S1 P4 200 S4 P2 200 into SP (S#, P#, QTY)
S1 P5 100 S4 P4 300 values (‘S20’, ‘P20’, 1000) ;
S1 P6 100 S4 P5 400

Database System Concepts 4.36 ©Silberschatz, Korth and Sudarshan

 Modification of the Database – Insertion
S# Sname Status City General form of insert statement:
S1 Smith 20 London
insert
S2 Jones 10 Paris
into table [ (field1, field2, field3, …) ]
S3 Blake 30 Paris
values ( constant1, constant2, constant3, … ) ; or
S4 Clark 20 London
S5 Adams 30 Athens insert
P# Pname Color Weight City into table [ (field1, field2, field3, …) ]
P1 Nut Red 12 London subquery ;
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
For each part supplied, get the part number and the
P4 Screw Red 14 London total quantity, and save the result in the database.
P5 Cam Blue 12 Paris
P6 Cog Red 19 London create table temp
S# P# QTY ( P# char(6)
S1 P1 300 S2 P1 300 TOTQTY integer ) ;
S1 P2 200 S2 P2 400
S1 P3 400 S3 P2 200 insert into temp ( P#, TOTQTY )
S1 P4 200 S4 P2 200 select P#, sum(QTY)
S1 P5 100 S4 P4 300 from SP
S1 P6 100 S4 P5 400 group by P# ;

Database System Concepts 4.37 ©Silberschatz, Korth and Sudarshan

Modification of the Database – Updates

S# Sname Status City Double status for all suppliers in London.
S1 Smith 40 London
S2 Jones 10 Paris update S
S3 Blake 30 Paris set status = status  2
S4 Clark 40 London where city = ‘London’ ;
S5 Adams 30 Athens
P# Pname Color Weight City Change the color and weight of part P2.
P1 Nut Red 12 London
P2 Bolt Yellow 22 Paris update P
P3 Screw Blue 17 Rome set color = ‘Yellow’, weight = weight + 5
P4 Screw Red 14 London where P# = ‘P2’ ;
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
Set the shipment quantity to zero for all suppliers in
S# P# QTY
Paris.
S1 P1 300 S2 P1 0
S1 P2 200 S2 P2 0 update SP
S1 P3 400 S3 P2 0 set QTY = 0
S1 P4 200 S4 P2 200 where ‘Paris’ = ( select city
S1 P5 100 S4 P4 300 from S
S1 P6 100 S4 P5 400 where S.S# = SP.S# ) ;

Database System Concepts 4.38 ©Silberschatz, Korth and Sudarshan

 Modification of the Database – Updates
S# Sname Status City General form of update statement:
S1 Smith 20 London
update table
S2 Jones 10 Paris
set field = expression
S3 Blake 30 Paris
[ , field = expression ] …
S4 Clark 20 London
where predicate ;
S5 Adams 30 Athens
P# Pname Color Weight City Increase all shipment quantities over 200 by 6%,
P1 Nut Red 12 London and all others by 5%.
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
update SP update account
P4 Screw Red 14 London set QTY = QTY  1.06 set QTY = QTY  1.05
P5 Cam Blue 12 Paris where QTY > 200 where QTY <= 200
P6 Cog Red 19 London
The order is important
S# P# QTY Can be done better using the case statement
S1 P1 318 S2 P1 318
S1 P2 210 S2 P2 424 update SP
S1 P3 424 S3 P2 210 set QTY = case
S1 P4 210 S4 P2 210 when QTY <= 200 then QTY  1.05
S1 P5 105 S4 P4 318 else QTY  1.06
S1 P6 105 S4 P5 424 end

Database System Concepts 4.39 ©Silberschatz, Korth and Sudarshan

Modification of the Views

S# Sname Status City Create a view of shipment relation (SP), hiding the
S1 Smith 20 London QTY attribute.
S2 Jones 10 Paris
S3 Blake 30 Paris create view Ship as
S4 Clark 20 London select S#, P#
S5 Adams 30 Athens from SP
P# Pname Color Weight City
Add a new shipment to ship.
P1 Nut Red 12 London
P2 Bolt Green 17 Paris insert
P3 Screw Blue 17 Rome into Ship
P4 Screw Red 14 London values (‘S5’, ‘P6’) ;
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
S# P# QTY  Updates on more complex views are difficult
S1 P1 300 S2 P1 300 or impossible to translate, and hence are
S1 P2 200 S2 P2 400 disallowed.
S1 P3 400 S3 P2 200
S1 P4 200 S4 P2 200
 Most SQL implementations allow updates
S1 P5 100 S4 P4 300 only on simple views (without aggregates)
S1 P6 100 S4 P5 400 defined on a single relation

Database System Concepts 4.40 ©Silberschatz, Korth and Sudarshan

 Transactions
 A transaction is a sequence of queries and update statements
executed as a single unit
 Transactions are started implicitly and terminated by one of
 commit work: makes all updates of the transaction permanent in
the database
 rollback work: undoes all updates performed by the transaction.

 Motivating example
 Transfer of money from one account to another involves two steps:
 deduct from one account and credit to another
 If one steps succeeds and the other fails, database is in an
inconsistent state
 Therefore, either both steps should succeed or neither should
 If any step of a transaction fails, all work done by the transaction
can be undone by rollback work.
 Rollback of incomplete transactions is done automatically, in case
of system failures

Database System Concepts 4.41 ©Silberschatz, Korth and Sudarshan

Transactions (Cont.)
 In most database systems, each SQL statement that executes
successfully is automatically committed.
 Each transaction would then consist of only a single statement
 Automatic commit can usually be turned off, allowing multi-
statement transactions, but how to do so depends on the database
system
 Another option in SQL:1999: enclose statements within

begin atomic
…
end

Database System Concepts 4.42 ©Silberschatz, Korth and Sudarshan

 Joined Relations

 Join operations take two relations and return as a result another

relation.
 These additional operations are typically used as subquery
expressions in the from clause
 Join condition – defines which tuples in the two relations match,
and what attributes are present in the result of the join.
 Join type – defines how tuples in each relation that do not match
any tuple in the other relation (based on the join condition) are
treated.

Join Types Join Conditions

inner join natural
left outer join on <predicate>
right outer join using (A1, A2, ..., An)
full outer join

Database System Concepts 4.43 ©Silberschatz, Korth and Sudarshan

Joined Relations – Datasets for Examples

 Relation loan

loan-number branch-name amount

L-170 Downtown 3000
L-230 Redwood 4000
L-260 Perryridge 1700

 Relation borrower
customer-name loan-number
Jones L-170
Smith L-230
Hayes L-155
 Note: borrower information missing for L-260 and loan
information missing for L-155

Database System Concepts 4.44 ©Silberschatz, Korth and Sudarshan

 Joined Relations – Examples

 loan inner join borrower on

[Link]-number = [Link]-number
loan-number branch-name amount customer-name loan-number

L-170 Downtown 3000 Jones L-170

L-230 Redwood 4000 Smith L-230

 loan left outer join borrower on

[Link]-number = [Link]-number

loan-number branch-name amount customer-name loan-number

L-170 Downtown 3000 Jones L-170
L-230 Redwood 4000 Smith L-230
L-260 Perryridge 1700 null null

Database System Concepts 4.45 ©Silberschatz, Korth and Sudarshan

Joined Relations – Examples

 loan natural inner join borrower

loan-number branch-name amount customer-name

L-170 Downtown 3000 Jones

L-230 Redwood 4000 Smith

 loan natural right outer join borrower

loan-number branch-name amount customer-name

L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
L-155 null null Hayes

Database System Concepts 4.46 ©Silberschatz, Korth and Sudarshan

 Joined Relations – Examples
 loan full outer join borrower using (loan-number)

loan-number branch-name amount customer-name

L-170 Downtown 3000 Jones

L-230 Redwood 4000 Smith
L-260 Perryridge 1700 null
L-155 null null Hayes

 Find all customers who have either an account or a loan (but

not both) at the bank.

select customer-name
from (depositor natural full outer join borrower)
where account-number is null or loan-number is null

Database System Concepts 4.47 ©Silberschatz, Korth and Sudarshan

Data Definition Language (DDL)

Allows the specification of not only a set of relations but also

information about each relation, including:

 The schema for each relation.

 The domain of values associated with each attribute.
 Integrity constraints
 The set of indices to be maintained for each relations.
 Security and authorization information for each relation.
 The physical storage structure of each relation on disk.

Database System Concepts 4.48 ©Silberschatz, Korth and Sudarshan

 Domain Types in SQL
 char(n). Fixed length character string, with user-specified length n.
 varchar(n). Variable length character strings, with user-specified maximum
length n.
 int. Integer (a finite subset of the integers that is machine-dependent).
 smallint. Small integer (a machine-dependent subset of the integer
domain type).
 numeric(p,d). Fixed point number, with user-specified precision of p digits,
with n digits to the right of decimal point.
 real, double precision. Floating point and double-precision floating point
numbers, with machine-dependent precision.
 float(n). Floating point number, with user-specified precision of at least n
digits.
 Null values are allowed in all the domain types. Declaring an attribute to be
not null prohibits null values for that attribute.
 create domain construct in SQL-92 creates user-defined domain types
create domain person-name char(20) not null

Database System Concepts 4.49 ©Silberschatz, Korth and Sudarshan

Date/Time Types in SQL (Cont.)

 date. Dates, containing a (4 digit) year, month and date
 E.g. date ‘2001-7-27’
 time. Time of day, in hours, minutes and seconds.
 E.g. time ’[Link]’ time ’[Link].75’
 timestamp: date plus time of day
 E.g. timestamp ‘2001-7-27 [Link].75’
 Interval: period of time
 E.g. Interval ‘1’ day
 Subtracting a date/time/timestamp value from another gives an interval value
 Interval values can be added to date/time/timestamp values
 Can extract values of individual fields from date/time/timestamp
 E.g. extract (year from [Link])
 Can cast string types to date/time/timestamp
 E.g. cast <string-valued-expression> as date

Database System Concepts 4.50 ©Silberschatz, Korth and Sudarshan

 Create Table Construct
 An SQL relation is defined using the create table
command:
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
 r is the name of the relation
 each Ai is an attribute name in the schema of relation r
 Di is the data type of values in the domain of attribute Ai
 Example:
create table S
(S# char(5) not null,
Sname char(20),
Status smallint,
City char(15) ) ;

Database System Concepts 4.51 ©Silberschatz, Korth and Sudarshan

Integrity Constraints in Create Table

 not null
 primary key (A1, ..., An)
 check (Predicate), where Predicate is a predicate

Example: Declare table P (Parts).

create table P
( P# char(6) not null,
Pname char(20)
Color char(10),
Weight smallint,
City char(15),
primary key (P#),
check (Weight >= 0))

primary key declaration on an attribute automatically

ensures not null in SQL-92 onwards, needs to be
explicitly stated in SQL-89
Database System Concepts 4.52 ©Silberschatz, Korth and Sudarshan
 Drop and Alter Table Constructs
 The drop table command deletes all information about the
dropped relation from the database.
 The alter table command is used to add attributes to an
existing relation.
alter table r add A D
where A is the name of the attribute to be added to relation r
and D is the domain of A.
 All tuples in the relation are assigned null as the value for the
new attribute.
 The alter table command can also be used to drop attributes
of a relation
alter table r drop A
where A is the name of an attribute of relation r
 Dropping of attributes not supported by many databases

Database System Concepts 4.53 ©Silberschatz, Korth and Sudarshan

 13.10.2011

This image cannot currently be display ed.

Chapter 5: Relational Database Design

Chapter 5: Relational Database Design

 First Normal Form

 Pitfalls in Relational Database Design
 Functional Dependencies
 Decomposition
 Boyce-Codd Normal Form
 Third Normal Form
 Multivalued Dependencies and Fourth Normal Form
 Overall Database Design Process

Database System Concepts 7.2 ©Silberschatz, Korth and Sudarshan

1
 13.10.2011

First Normal Form

 Domain is atomic if its elements are considered to be

indivisible units
 Examples of non-atomic domains:
 Set of names, composite attributes
 Identification numbers like CS101 that can be broken up into
parts
 A relational schema R is in first normal form if the domains
of all attributes of R are atomic
 Non-atomic values complicate storage and encourage
redundant (repeated) storage of data
 E.g. Set of accounts stored with each customer, set of children
stored with each person, etc.

Database System Concepts 7.3 ©Silberschatz, Korth and Sudarshan

First Normal Form (Contd.)

 Atomicity is actually a property of how the elements of the
domain are used.
 E.g. Strings would normally be considered indivisible
 Suppose that students are given roll numbers which are strings of
the form CS0012 or EE1127
 If the first two characters are extracted to find the department, the
domain of roll numbers is not atomic.
 Doing so is a bad idea: leads to encoding of information in
application program rather than in the database.

Database System Concepts 7.4 ©Silberschatz, Korth and Sudarshan

2
 13.10.2011

Pitfalls in Relational Database Design

 Relational database design requires that we find a

“good” collection of relation schemas. A bad
design may lead to
 Repetition of Information.
 Inability to represent certain information.
 Design Goals:
 Avoid redundant data
 Ensure that relationships among attributes are
represented
 Facilitate the checking of updates for violation of
database integrity constraints.

Database System Concepts 7.5 ©Silberschatz, Korth and Sudarshan

Example
 Consider the relation schema:
Lending-schema = (branch-name, branch-city, assets,
customer-name, loan-number, amount)

 Redundancy:
 Data for branch-name, branch-city, assets are repeated for each loan
that a branch makes
 Wastes space
 Complicates updating, introducing possibility of inconsistency of
assets value
 Null values
 Cannot store information about a branch if no loans exist
 Can use null values, but they are difficult to handle.
Database System Concepts 7.6 ©Silberschatz, Korth and Sudarshan

3
 13.10.2011

Decomposition

 Decompose the relation schema Lending-schema into:

Branch-schema = (branch-name, branch-city, assets)
Loan-info-schema = (customer-name, loan-number,
branch-name, amount)
 All attributes of an original schema (R) must appear in
the decomposition (R1, R2):
R = R1  R2
 Lossless-join decomposition.
For all possible relations r on schema R
r = R1 (r) R2 (r)

Database System Concepts 7.7 ©Silberschatz, Korth and Sudarshan

Example of Non Lossless-Join Decomposition

 Decomposition of R = (A, B)
R1 = (A) R2 = (B)

A B A B

 1  1
 2  2
 1 B(r)
A(r)
r
A B
A (r) B (r)
 1
 2
 1
 2

Database System Concepts 7.8 ©Silberschatz, Korth and Sudarshan

4
 13.10.2011

Goal — Devise a Theory for the Following

 Decide whether a particular relation R is in “good” form.

 In the case that a relation R is not in “good” form, decompose it
into a set of relations {R1, R2, ..., Rn} such that
 each relation is in good form
 the decomposition is a lossless-join decomposition
 Our theory is based on:
 functional dependencies
 multivalued dependencies

Database System Concepts 7.9 ©Silberschatz, Korth and Sudarshan

Functional Dependencies

 Constraints on the set of legal relations.

 Require that the value for a certain set of attributes determines
uniquely the value for another set of attributes.
 A functional dependency is a generalization of the notion of a
key.

Database System Concepts 7.10 ©Silberschatz, Korth and Sudarshan

5
 13.10.2011

Functional Dependencies (Cont.)

 Let R be a relation schema   R and   R
 The functional dependency

holds on R if and only if for any legal relations r(R), whenever any
two tuples t1 and t2 of r agree on the attributes , they also agree
on the attributes . That is,
t1[] = t2 []  t1[ ] = t2 [ ]
 Example: Consider r(A,B) with the following instance of r.
1 4
1 5
3 7

 On this instance, A  B does NOT hold, but B  A does

hold.

Database System Concepts 7.11 ©Silberschatz, Korth and Sudarshan

Functional Dependencies (Cont.)

 K is a superkey for relation schema R if and only if K  R

 K is a candidate key for R if and only if
 K  R, and
 for no   K,   R
 Functional dependencies allow us to express constraints that
cannot be expressed using superkeys. Consider the schema:
Loan-info-schema = (customer-name, loan-number,
branch-name, amount).
We expect this set of functional dependencies to hold:
loan-number  amount
loan-number  branch-name
but would not expect the following to hold:
loan-number  customer-name

Database System Concepts 7.12 ©Silberschatz, Korth and Sudarshan

6
 13.10.2011

Example
Drinkers(name, addr, beersLiked, manf, favoriteBeer)

name addr beersLiked manf favoriteBeer

Janeway Voyager Bud A.B. WickedAle
Janeway Voyager WickedAle Pete's WickedAle
Spock Enterprise Bud A.B. Bud

 Reasonable FD's to assert:

1. name  addr
2. name  favoriteBeer
3. beersLiked  manf
 Sometimes, several attributes jointly determine another
attribute, although neither does by itself. Example:
beer bar  price

Database System Concepts 7.13 ©Silberschatz, Korth and Sudarshan

Functional Dependencies

 A functional dependency is trivial if it is satisfied by all instances

of a relation
 E.g.
 customer-name, loan-number  customer-name
 customer-name  customer-name
 In general,    is trivial if   

 “Nontrivial” = right-side attribute not in left side

Database System Concepts 7.14 ©Silberschatz, Korth and Sudarshan

7
 13.10.2011

Closure of a Set of Functional

Dependencies
 Given a set F set of functional dependencies, there are certain
other functional dependencies that are logically implied by F.
 E.g. If A  B and B  C, then we can infer that A  C
 The set of all functional dependencies logically implied by F is the
closure of F.
 We denote the closure of F by F+.

 We can find all of F+ by applying Armstrong’s Axioms:

 if   , then    (reflexivity)
 if   , then      (augmentation)
 if   , and   , then    (transitivity)

Database System Concepts 7.15 ©Silberschatz, Korth and Sudarshan

Example
 R = (A, B, C, G, H, I)
F={ AB
AC
CG  H
CG  I
B  H}
 some members of F+
 AH
 by transitivity from A  B and B  H
 AG  I
 by augmenting A  C with G, to get AG  CG
and then transitivity with CG  I
 CG  HI
 from CG  H and CG  I : “union rule” can be inferred from
– definition of functional dependencies, or
– Augmentation of CG  I to infer CG  CGI, augmentation of
CG  H to infer CGI  HI, and then transitivity

Database System Concepts 7.16 ©Silberschatz, Korth and Sudarshan

8
 13.10.2011

Procedure for Computing F+

 To compute the closure of a set of functional dependencies F:

F+ = F
repeat
for each functional dependency f in F+
apply reflexivity and augmentation rules on f
add the resulting functional dependencies to F+
for each pair of functional dependencies f1and f2 in F+
if f1 and f2 can be combined using transitivity
then add the resulting functional dependency to F+
+
until F does not change any further

NOTE: We will see an alternative procedure for this task later

Database System Concepts 7.17 ©Silberschatz, Korth and Sudarshan

Closure of Functional Dependencies

(Cont.)

 We can further simplify manual computation of F+ by using

the following additional rules.
 If    holds and    holds, then     holds (union)
 If     holds, then    holds and    holds
(decomposition)
 If    holds and     holds, then     holds
(pseudotransitivity)
The above rules can be inferred from Armstrong’s axioms.

Database System Concepts 7.18 ©Silberschatz, Korth and Sudarshan

9
 13.10.2011

Closure of Attribute Sets

 Given a set of attributes  define the closure of  under F

(denoted by +) as the set of attributes that are functionally
determined by  under F:
   is in F+    +
 Algorithm to compute +, the closure of  under F
result := ;
while (changes to result) do
for each    in F do
begin
if   result then result := result  
end

Database System Concepts 7.19 ©Silberschatz, Korth and Sudarshan

Example of Attribute Set Closure

 R = (A, B, C, G, H, I)
 F = {A  B
AC
CG  H
CG  I
B  H}
 (AG)+
1. result = AG
2. result = ABCG (A  C and A  B)
3. result = ABCGH (CG  H and CG  AGBC)
4. result = ABCGHI (CG  I and CG  AGBCH)
 Is AG a candidate key?
1. Is AG a super key?
1. Does AG  R? == Is (AG)+  R
2. Is any subset of AG a superkey?
1. Does A  R? == Is (A)+  R
2. Does G  R? == Is (G)+  R

Database System Concepts 7.20 ©Silberschatz, Korth and Sudarshan

10
 13.10.2011

Uses of Attribute Closure

There are several uses of the attribute closure algorithm:
 Testing for superkey:
 To test if  is a superkey, we compute +, and check if + contains
all attributes of R.
 Testing functional dependencies
 To check if a functional dependency    holds (or, in other words,
is in F+), just check if   +.
 That is, we compute + by using attribute closure, and then check if
it contains .
 Is a simple and cheap test, and very useful
 Computing closure of F
 For each   R, we find the closure +, and for each S  +, we
output a functional dependency   S.

Database System Concepts 7.21 ©Silberschatz, Korth and Sudarshan

Goals of Normalization
 Decide whether a particular relation R is in “good” form.
 In the case that a relation R is not in “good” form, decompose it
into a set of relations {R1, R2, ..., Rn} such that
 each relation is in good form
 the decomposition is a lossless-join decomposition
 Our theory is based on:
 functional dependencies
 multivalued dependencies

Database System Concepts 7.22 ©Silberschatz, Korth and Sudarshan

11
 13.10.2011

Decomposition

 Decompose the relation schema Lending-schema into:

Branch-schema = (branch-name, branch-city,assets)
Loan-info-schema = (customer-name, loan-number,
branch-name, amount)
 All attributes of an original schema (R) must appear in the
decomposition (R1, R2):
R = R1  R2
 Lossless-join decomposition.
For all possible relations r on schema R
r = R1 (r) R2 (r)
 A decomposition of R into R1 and R2 is lossless join if and only if
at least one of the following dependencies is in F+:
 R1  R2  R1
 R1  R2  R2

Database System Concepts 7.23 ©Silberschatz, Korth and Sudarshan

Normalization Using Functional Dependencies

 When we decompose a relation schema R with a set of

functional dependencies F into R1, R2,.., Rn we want
 Lossless-join decomposition: Otherwise decomposition would result in
information loss.
 No redundancy: The relations Ri preferably should be in either Boyce-
Codd Normal Form or Third Normal Form.
 Dependency preservation: Let Fi be the set of dependencies F+ that
include only attributes in Ri.
 Preferably the decomposition should be dependency preserving,
that is, (F1  F2  …  Fn)+ = F+
 Otherwise, checking updates for violation of functional
dependencies may require computing joins, which is expensive.

Database System Concepts 7.24 ©Silberschatz, Korth and Sudarshan

12
 13.10.2011

Example

 R = (A, B, C)
F = {A  B, B  C)
 Can be decomposed in two different ways
 R1 = (A, B), R2 = (B, C)
 Lossless-join decomposition:
R1  R2 = {B} and B  BC
 Dependency preserving
 R1 = (A, B), R2 = (A, C)
 Lossless-join decomposition:
R1  R2 = {A} and A  AB
 Not dependency preserving
(cannot check B  C without computing R1 R2)

Database System Concepts 7.25 ©Silberschatz, Korth and Sudarshan

Second Normal Form

A relation schema R is in 2NF respect to a set F of functional

dependencies if for all nonkey set of attributes  holds:

    where  is a superkey for R

A relation is said to be in Second Normal Form when

every nonkey attribute is fully functionally dependent on
the primary key. (No attribute dependent on a portion of
primary key)
 That is, every nonkey attribute needs the full primary key for
unique identification
 It is important only in cases of keys containing more than
one attribute

Database System Concepts 7.26 ©Silberschatz, Korth and Sudarshan

13
 13.10.2011

Example
Drinkers (name, addr, beersLiked, manf, favoriteBeer)

name addr beersLiked manf favoriteBeer

Janeway Voyager Bud A.B. WickedAle
Janeway Voyager WickedAle Pete's WickedAle
Spock Enterprise Bud A.B. Bud

FD’s: name  addr, name  favoriteBeer, beersLiked  manf

violates 2NF.

Lending-schema (branch-name, branch-city, assets,

customer-name, loan-number, amount)

FD’s: branch-name branch-city  assets violates 2NF.

Database System Concepts 7.27 ©Silberschatz, Korth and Sudarshan

Boyce-Codd Normal Form

A relation schema R is in BCNF with respect to a set F of functional

dependencies if for all functional dependencies in F+ of the form
 , where   R and   R, at least one of the following holds:

    is trivial (i.e.,   )
  is a superkey for R

R is in BCNF if for every nontrivial FD for R, say X  A,

then X is a superkey.
Follow from the idea “key  everything.”
1. Guarantees no redundancy due to FD’s.
2. Guarantees no update anomalies = one occurrence of a fact is
updated, not all.
3. Guarantees no deletion anomalies = valid fact is lost when
tuple is deleted.

Database System Concepts 7.28 ©Silberschatz, Korth and Sudarshan

14
 13.10.2011

Example

 R = (A, B, C)
F = {A  B
B  C}
Key = {A}
 R is not in BCNF
 Decomposition R1 = (A, B), R2 = (B, C)
 R1 and R2 in BCNF
 Lossless-join decomposition
 Dependency preserving

Database System Concepts 7.29 ©Silberschatz, Korth and Sudarshan

Example of Problems
Drinkers(name, addr, beersLiked, manf, favoriteBeer)
name addr beersLiked manf favoriteBeer
Janeway Voyager Bud A.B. WickedAle
Janeway ??? WickedAle Pete's ???
Spock Enterprise Bud ??? Bud

FD’s:
1. name  addr
2. name  favoriteBeer
3. beersLiked  manf

 ???’s are redundant, since we can figure them out from the FD’s.
 Update anomalies: If Janeway gets transferred to the Intrepid,
will we change addr in each of her tuples?
 Deletion anomalies: If nobody likes Bud, we lose track of Bud’s
manufacturer.

Database System Concepts 7.30 ©Silberschatz, Korth and Sudarshan

15
 13.10.2011

Each of the given FD’s is a BCNF violation:

 Key = {name, beersLiked}
 Each of the given FD’s has a left side that is a proper subset of the
key.

Another Example
Beers(name, manf, manfAddr). (Note: 2NF is satisfied)
 FD’s = name  manf, manf  manfAddr.
 Only key is name.
 manf  manfAddr violates BCNF with a left side unrelated to
any key.

Database System Concepts 7.31 ©Silberschatz, Korth and Sudarshan

Testing for BCNF

 To check if a non-trivial dependency  causes a violation of
BCNF
1. compute + (the attribute closure of ), and
2. verify that it includes all attributes of R, that is, it is a superkey of R.
 Simplified test: To check if a relation schema R is in BCNF, it
suffices to check only the dependencies in the given set F for
violation of BCNF, rather than checking all dependencies in F+.
 If none of the dependencies in F causes a violation of BCNF, then
none of the dependencies in F+ will cause a violation of BCNF either.
 However, using only F is incorrect when testing a relation in a
decomposition of R
 E.g. Consider R (A, B, C, D), with F = { A B, B C}
 Decompose R into R1(A,B) and R2(A,C,D)
 Neither of the dependencies in F contain only attributes from
(A,C,D) so we might be mislead into thinking R2 satisfies BCNF.
 In fact, dependency A  C in F+ shows R2 is not in BCNF.

Database System Concepts 7.32 ©Silberschatz, Korth and Sudarshan

16
 13.10.2011

BCNF Decomposition Algorithm (1)

result := {R};
done := false;
compute F+;
while (not done) do
if (there is a schema Ri in result that is not in BCNF)
then begin
let    be a nontrivial functional
dependency that holds on Ri
such that   Ri is not in F+,
and    = ;
result := (result – Ri )  (Ri – )  (,  );
end
else done := true;
Note: each Ri is in BCNF, and decomposition is lossless-join.

Database System Concepts 7.33 ©Silberschatz, Korth and Sudarshan

Example of BCNF Decomposition

 R = (branch-name, branch-city, assets,

customer-name, loan-number, amount)
F = {branch-name  assets branch-city
loan-number  amount branch-name}
Key = {loan-number, customer-name}
 Decomposition
 R1 = (branch-name, branch-city, assets)
 R2 = (branch-name, customer-name, loan-number, amount)
 R3 = (branch-name, loan-number, amount)
 R4 = (customer-name, loan-number)
 Final decomposition
R 1, R 3, R 4

Database System Concepts 7.34 ©Silberschatz, Korth and Sudarshan

17
 13.10.2011

BCNF Decomposition Algorithm (2)

Setting: relation R, given FD’s F.
Suppose relation R has BCNF violation X  B.
1. Compute X+.
 Cannot be all attributes – why?
2. Decompose R into X+ and (R–X+)  X.

R X X+

3. Find the FD’s for the decomposed relations.

 Project the FD’s from F = calculate all consequents of F that
involve only attributes from X+ or only from (RX+)  X.

Database System Concepts 7.35 ©Silberschatz, Korth and Sudarshan

Example
R = Drinkers(name, addr, beersLiked, manf, favoriteBeer)
F=
1. name  addr
2. name  favoriteBeer
3. beersLiked  manf

Pick BCNF violation name  addr.

 Close the left side: name+ = name addr favoriteBeer.
 Decomposed relations:
Drinkers1(name, addr, favoriteBeer)
Drinkers2(name, beersLiked, manf)
 Projected FD’s (skipping a lot of work that leads nowhere interesting):
 For Drinkers1: name  addr and name  favoriteBeer.
 For Drinkers2: beersLiked  manf.

Database System Concepts 7.36 ©Silberschatz, Korth and Sudarshan

18
 13.10.2011

(Repeating)
 Decomposed relations:
Drinkers1(name, addr, favoriteBeer)
Drinkers2(name, beersLiked, manf)
 Projected FD’s:
 For Drinkers1: name  addr and name  favoriteBeer.
 For Drinkers2: beersLiked  manf.

 BCNF violations?
 For Drinkers1, name is key and all left sides of FD’s are
superkeys.
 For Drinkers2, {name, beersLiked} is the key, and beersLiked
 manf violates BCNF.

Database System Concepts 7.37 ©Silberschatz, Korth and Sudarshan

Decompose Drinkers2
 First set of decomposed relations:
Drinkers1(name, addr, favoriteBeer)
Drinkers2(name, beersLiked, manf)

 Close beersLiked+ = beersLiked, manf.

 Decompose Drinkers2 into:
Drinkers3(beersLiked, manf)
Drinkers4(name, beersLiked)

 Resulting relations are all in BCNF:

Drinkers1(name, addr, favoriteBeer)
Drinkers3(beersLiked, manf)
Drinkers4(name, beersLiked)

Database System Concepts 7.38 ©Silberschatz, Korth and Sudarshan

19
 13.10.2011

Testing Decomposition for BCNF

 To check if a relation Ri in a decomposition of R is in BCNF,

 Either test Ri for BCNF with respect to the restriction of F to Ri (that
is, all FDs in F+ that contain only attributes from Ri)
 or use the original set of dependencies F that hold on R, but with the
following test:
– for every set of attributes   Ri, check that + (the attribute
closure of ) either includes no attribute of Ri- , or includes all
attributes of Ri.
 If the condition is violated by some   in F, the dependency
 (+ - )  Ri
can be shown to hold on Ri, and Ri violates BCNF.
 We use above dependency to decompose Ri

Database System Concepts 7.39 ©Silberschatz, Korth and Sudarshan

BCNF and Dependency Preservation

It is not always possible to get a BCNF decomposition that is

dependency preserving

 R = (J, K, L)
F = {JK  L
L  K}
Two candidate keys = JK and JL
 R is not in BCNF
 Any decomposition of R will fail to preserve

JK  L

Database System Concepts 7.40 ©Silberschatz, Korth and Sudarshan

20
 13.10.2011

Third Normal Form: Motivation

 There are some situations where

 BCNF is not dependency preserving, and
 efficient checking for FD violation on updates is important
 Solution: define a weaker normal form, called Third Normal
Form
 Allows some redundancy (with resultant problems; we will see
examples later)
 But FDs can be checked on individual relations without computing a
join.
 There is always a lossless-join, dependency-preserving decomposition
into 3NF.

Database System Concepts 7.41 ©Silberschatz, Korth and Sudarshan

Example
One FD structure causes problems:
 If you decompose, you can’t check all the FD’s only in the
decomposed relations.
 If you don’t decompose, you violate BCNF.
Abstractly: R = (A, B, C), F = {AB  C, C  B.}
Example: street city  zip, zip  city.
Keys: {A, B} and {A, C}, but C  B has a left side that is not a superkey.

 Suggests decomposition into {B, C} and {A, C}.

 But you can’t check the FD: AB  C in only these relations (requires a
join)
 Equivalent to example in book:
Banker-schema = (branch-name, customer-name, banker-name)
banker-name  branch name
branch name customer-name  banker-name

Database System Concepts 7.42 ©Silberschatz, Korth and Sudarshan

21
 13.10.2011

Third Normal Form

 A relation schema R is in third normal form (3NF) if for all:

   in F+
at least one of the following holds:
    is trivial (i.e.,   )
  is a superkey for R
 Each attribute A in  –  is contained in a candidate key for R.
(NOTE: each attribute may be in a different candidate key)
 If a relation is in BCNF it is in 3NF (since in BCNF one of the first
two conditions above must hold).
 Third condition is a minimal relaxation of BCNF to ensure
dependency preservation (will see why later).

Database System Concepts 7.43 ©Silberschatz, Korth and Sudarshan

Testing for 3NF

 Optimization: Need to check only FDs in F, need not check all

FDs in F+.
 Use attribute closure to check for each dependency   , if  is
a superkey.
 If  is not a superkey, we have to verify if each attribute in  is
contained in a candidate key of R
 this test is rather more expensive, since it involve finding candidate
keys
 testing for 3NF has been shown to be NP-hard
 Interestingly, decomposition into third normal form (described
shortly) can be done in polynomial time

Database System Concepts 7.44 ©Silberschatz, Korth and Sudarshan

22
 13.10.2011

3NF Decomposition Algorithm

Let Fc be a canonical cover for F;
i := 0;
for each functional dependency    in Fc do
if none of the schemas Rj, 1  j  i contains  
then begin
i := i + 1;
Ri :=  
end
if none of the schemas Rj, 1  j  i contains a candidate key for R
then begin
i := i + 1;
Ri := any candidate key for R;
end
return (R1, R2, ..., Ri)

Database System Concepts 7.45 ©Silberschatz, Korth and Sudarshan

What 3NF Gives You

There are two important properties of a decomposition:
1. We should be able to recover from the decomposed relations
the data of the original.
 Recovery involves projection and join.
2. We should be able to check that the FD’s for the original relation
are satisfied by checking the projections of those FD’s in the
decomposed relations.

 Without proof, we assert that it is always possible to decompose

into BCNF and satisfy (1).
 Also without proof, we can decompose into 3NF and satisfy both
(1) and (2).
 But it is not possible to decompose into BNCF and get both (1)
and (2).
 Street-city-zip is an example of this point.

Database System Concepts 7.46 ©Silberschatz, Korth and Sudarshan

23
 13.10.2011

Example

 Relation schema:
Banker-info-schema = (branch-name, customer-name,
banker-name, office-number)
 The functional dependencies for this relation schema are:
banker-name  branch-name office-number
customer-name branch-name  banker-name
 The key is:
{customer-name, branch-name}

Database System Concepts 7.47 ©Silberschatz, Korth and Sudarshan

Applying 3NF to Banker-info-schema

 The for loop in the algorithm causes us to include the

following schemas in our decomposition:
Banker-office-schema = (banker-name, branch-name,
office-number)
Banker-schema = (customer-name, branch-name,
banker-name)

 Since Banker-schema contains a candidate key for

Banker-info-schema, we are done with the decomposition
process.

Database System Concepts 7.48 ©Silberschatz, Korth and Sudarshan

24
 13.10.2011

Comparison of BCNF and 3NF

 It is always possible to decompose a relation into relations in

3NF and
 the decomposition is lossless
 the dependencies are preserved
 It is always possible to decompose a relation into relations in
BCNF and
 the decomposition is lossless
 it may not be possible to preserve dependencies.

Database System Concepts 7.49 ©Silberschatz, Korth and Sudarshan

Comparison of BCNF and 3NF (Cont.)

 Example of problems due to redundancy in 3NF

 R = (A, B, C)
F = {AB  C, C  B}
A B C
a1 b1 c1
a2 b1 c1
a3 b1 c1

null b2 c2

A schema that is in 3NF but not in BCNF has the problems of

 repetition of information (e.g., the relationship b1, c1)
 need to use null values (e.g., to represent the relationship
b2, c2 where there is no corresponding value for A).

Database System Concepts 7.50 ©Silberschatz, Korth and Sudarshan

25
 13.10.2011

Design Goals
 Goal for a relational database design is:
 BCNF.
 Lossless join.
 Dependency preservation.
 If we cannot achieve this, we accept one of
 Lack of dependency preservation
 Redundancy due to use of 3NF
 Interestingly, SQL does not provide a direct way of specifying
functional dependencies other than superkeys.
Can specify FDs using assertions, but they are expensive to test
 Even if we had a dependency preserving decomposition, using
SQL we would not be able to efficiently test a functional
dependency whose left hand side is not a key.

Database System Concepts 7.51 ©Silberschatz, Korth and Sudarshan

Multivalued Dependencies
 There are database schemas in BCNF that do not seem to be
sufficiently normalized
 Consider a database
classes(course, teacher, book)

such that (c,t,b)  classes means that t is qualified to teach c,

and b is a required textbook for c
 The database is supposed to list for each course the set of
teachers any one of which can be the course’s instructor, and the
set of books, all of which are required for the course (no matter
who teaches it).

Database System Concepts 7.52 ©Silberschatz, Korth and Sudarshan

26
 13.10.2011

Multivalued Dependencies (Cont.)

course teacher book
database Avi DB Concepts
database Avi Ullman
database Hank DB Concepts
database Hank Ullman
database Sudarshan DB Concepts
database Sudarshan Ullman
operating systems Avi OS Concepts
operating systems Avi Shaw
operating systems Jim OS Concepts
operating systems Jim Shaw
classes
 There are no non-trivial functional dependencies and therefore
the relation is in BCNF
 Insertion anomalies – i.e., if Sara is a new teacher that can teach
database, two tuples need to be inserted
(database, Sara, DB Concepts)
(database, Sara, Ullman)
Database System Concepts 7.53 ©Silberschatz, Korth and Sudarshan

Multivalued Dependencies (Cont.)

 Therefore, it is better to decompose classes into:

course teacher
database Avi
database Hank
database Sudarshan
operating systems Avi
operating systems Jim
teaches
course book
database DB Concepts
database Ullman
operating systems OS Concepts
operating systems Shaw
text
We shall see that these two relations are in Fourth Normal
Form (4NF)
Database System Concepts 7.54 ©Silberschatz, Korth and Sudarshan

27
 13.10.2011

Multivalued Dependencies Def.

The multivalued dependency X  Y holds in a relation R if

whenever we have two tuples of R that agree in all the attributes
of X, then we can swap their Y components and get two new
tuples that are also in R.

X Y others

Database System Concepts 7.55 ©Silberschatz, Korth and Sudarshan

Example (Cont.)

 In our example:
course  teacher
course  book
 The above formal definition is supposed to formalize the
notion that given a particular value of Y (course) it has
associated with it a set of values of Z (teacher) and a set
of values of W (book), and these two sets are in some
sense independent of each other.
 Note:
 If Y  Z then Y  Z
 Indeed we have (in above notation) Z1 = Z2
The claim follows.

Database System Concepts 7.56 ©Silberschatz, Korth and Sudarshan

28
 13.10.2011

Example
Drinkers(name, addr, phones, beersLiked)
with MVD Name  phones. If Drinkers has the two tuples:

name addr phones beersLiked

sue a p1 b1
sue a p2 b2

it must also have the same tuples with phones components swapped:

name addr phones beersLiked

sue a p2 b1
sue a p1 b2

Note: we must check this condition for all pairs of tuples

that agree on name, not just one pair.

Database System Concepts 7.57 ©Silberschatz, Korth and Sudarshan

MVD Rules

1. Every FD is an MVD.
 Because if X Y, then swapping Y’s between tuples that agree on X
doesn’t create new tuples.
 Example, in Drinkers: name  addr.
2. Complementation: if X  Y, then X  Z, where Z is all
attributes not in X or Y.
 Example: since name  phones
holds in Drinkers, so does
name  addr beersLiked.

Database System Concepts 7.58 ©Silberschatz, Korth and Sudarshan

29
 13.10.2011

Fourth Normal Form

 A relation schema R is in 4NF with respect to a set D of
functional and multivalued dependencies if for all multivalued
dependencies in D+ of the form   , where   R and   R,
at least one of the following hold:
    is trivial (i.e.,    or    = R)
  is a superkey for schema R
 If a relation is in 4NF it is in BCNF

4NF eliminates redundancy due to multiplicative effect of MVD’s.

 Formally: R is in Fourth Normal Form if whenever MVD
X  Y is nontrivial (Y is not a subset of X, and X  Y is not all
attributes), then X is a superkey.
 Remember, X  Y implies X  Y,
so 4NF is more stringent than BCNF. R X Y
 Decompose R, using NF violation X  Y,
into XY and X  (R—Y).

Database System Concepts 7.59 ©Silberschatz, Korth and Sudarshan

4NF Decomposition Algorithm

result: = {R};
done := false;
compute D+;
Let Di denote the restriction of D+ to Ri
while (not done)
if (there is a schema Ri in result that is not in 4NF) then
begin
let    be a nontrivial multivalued dependency that holds
on Ri such that   Ri is not in Di, and ;
result := (result - Ri)  (Ri - )  (, );
end
else done:= true;
Note: each Ri is in 4NF, and decomposition is lossless-join

Database System Concepts 7.60 ©Silberschatz, Korth and Sudarshan

30
 13.10.2011

Splitting Doesn’t Hold

Sometimes you need to have several attributes on the right of an
MVD. For example:
Drinkers(name, areaCode, phones, beersLiked, beerManf)

name areaCode phones beersLiked beerManf

Sue 831 555-1111 Bud A.B.
Sue 831 555-1111 Wicked Ale Pete’s
Sue 408 555-9999 Bud A.B.
Sue 408 555-9999 Wicked Ale Pete’s

 name  areaCode phones holds, but neither

name  areaCode nor name  phones do.

Database System Concepts 7.61 ©Silberschatz, Korth and Sudarshan

Example
Drinkers(name, addr, phones, beersLiked)
 FD: name  addr
 Nontrivial MVD’s: name  phones and
name  beersLiked.
 Only key: {name, phones, beersLiked}
 All three dependencies above violate 4NF.
 Successive decomposition yields 4NF relations:
D1(name, addr)
D2(name, phones)
D3(name, beersLiked)

Database System Concepts 7.62 ©Silberschatz, Korth and Sudarshan

31
 13.10.2011

Normalization
No transitive
dependency
between
nonkey
Functional
attributes
dependency
of nonkey
attributes on
Boyce- the primary
No key - Atomic
multivalued values only
4NF
dependency
Full
Codd Functional
dependency
All of nonkey
determinants attributes on
are candidate the primary
keys - Single key
multivalued
dependency

Database System Concepts 7.63 ©Silberschatz, Korth and Sudarshan

Further Normal Forms

 Join dependencies generalize multivalued dependencies
 lead to project-join normal form (PJNF) (also called fifth normal
form)
 A relation is in 5NF if every join dependency in the relation is
implied by the keys of the relation
 Implies that relations that have been decomposed in previous NF
can be recombined via natural joins to recreate the original relation
 A class of even more general constraints, leads to a normal form
called domain-key normal form.
 Problem with these generalized constraints: are hard to reason
with, and no set of sound and complete set of inference rules
exists.
 Hence rarely used

 The normalized relations grows in additive way while

non-normalized relations grows in multiplicative way.
Database System Concepts 7.64 ©Silberschatz, Korth and Sudarshan

32
 13.10.2011

Overall Database Design Process

 We have assumed schema R is given

 R could have been generated when converting E-R diagram to a set of
tables.
 R could have been a single relation containing all attributes that are of
interest (called universal relation).
 Normalization breaks R into smaller relations.
 R could have been the result of some ad hoc design of relations, which
we then test/convert to normal form.

 In practice, usually we start with more relations that intuitively satisfy

some normal forms.

Database System Concepts 7.65 ©Silberschatz, Korth and Sudarshan

ER Model and Normalization

 When an E-R diagram is carefully designed, identifying all entities

correctly, the tables generated from the E-R diagram should not need
further normalization.
 However, in a real (imperfect) design there can be FDs from non-key
attributes of an entity to other attributes of the entity
 E.g. employee entity with attributes department-number and
department-address, and an FD department-number  department-
address
 Good design would have made department an entity
 FDs from non-key attributes of a relationship set possible, but rare ---
most relationships are binary

Database System Concepts 7.66 ©Silberschatz, Korth and Sudarshan

33
 13.10.2011

Denormalization for Performance

 May want to use non-normalized schema for performance

 E.g. displaying customer-name along with account-number and
balance requires join of account with depositor
 Alternative 1: Use denormalized relation containing attributes of
account as well as depositor with all above attributes
 faster lookup
 Extra space and extra execution time for updates
 extra coding work for programmer and possibility of error in extra code
 Alternative 2: use a materialized view defined as
account depositor
 Benefits and drawbacks same as above, except no extra coding work
for programmer and avoids possible errors

Database System Concepts 7.67 ©Silberschatz, Korth and Sudarshan

Other Design Issues

 Some aspects of database design are not caught by

normalization
 Examples of bad database design, to be avoided:
Instead of earnings(company-id, year, amount), use
 earnings-2000, earnings-2001, earnings-2002, etc., all on the
schema (company-id, earnings).
 Above are in BCNF, but make querying across years difficult and
needs new table each year
 company-year(company-id, earnings-2000, earnings-2001,
earnings-2002)
 Also in BCNF, but also makes querying across years difficult and
requires new attribute each year.
 Is an example of a crosstab, where values for one attribute
become column names
 Used in spreadsheets, and in data analysis tools

Database System Concepts 7.68 ©Silberschatz, Korth and Sudarshan

34
 13.10.2011

This image cannot currently be display ed.

End of Chapter

Sample lending Relation

Database System Concepts 7.70 ©Silberschatz, Korth and Sudarshan

35
 13.10.2011

Sample Relation r

Database System Concepts 7.71 ©Silberschatz, Korth and Sudarshan

The customer Relation

Database System Concepts 7.72 ©Silberschatz, Korth and Sudarshan

36
 13.10.2011

The loan Relation

Database System Concepts 7.73 ©Silberschatz, Korth and Sudarshan

The branch Relation

Database System Concepts 7.74 ©Silberschatz, Korth and Sudarshan

37
 13.10.2011

The Relation branch-customer

Database System Concepts 7.75 ©Silberschatz, Korth and Sudarshan

The Relation customer-loan

Database System Concepts 7.76 ©Silberschatz, Korth and Sudarshan

38
 13.10.2011

The Relation branch-customer customer-loan

Database System Concepts 7.77 ©Silberschatz, Korth and Sudarshan

An Instance of Banker-schema

Database System Concepts 7.78 ©Silberschatz, Korth and Sudarshan

39
 13.10.2011

Tabular Representation of 

Database System Concepts 7.79 ©Silberschatz, Korth and Sudarshan

Relation bc: An Example of Reduncy in a BCNF Relation

Database System Concepts 7.80 ©Silberschatz, Korth and Sudarshan

40
 13.10.2011

An Illegal bc Relation

Database System Concepts 7.81 ©Silberschatz, Korth and Sudarshan

Decomposition of loan-info

Database System Concepts 7.82 ©Silberschatz, Korth and Sudarshan

41
 13.10.2011

Relation of Exercise 7.4

Database System Concepts 7.83 ©Silberschatz, Korth and Sudarshan

42
 A Form with Visual Basic

Database System Concepts 1.1 ©Silberschatz, Korth and Sudarshan

Cisti_Click()
sdob najdi Prikazi_Click()
sdel najdi Nov_Click()
kol najdi Otvori_Click()
Brisi_Click()
data najdi
Nazad_Click()

Database System Concepts 1.2 ©Silberschatz, Korth and Sudarshan

1
 Cisti_Click()
sdob najdi Prikazi_Click()
sdel najdi Nov_Click()
kol najdi Otvori_Click()
Brisi_Click()
data najdi
Nazad_Click()

Database System Concepts 1.3 ©Silberschatz, Korth and Sudarshan

Database System Concepts 1.4 ©Silberschatz, Korth and Sudarshan

2
 Procedure for a Query Building
Option Compare Database
Option Explicit
Private Sub AddToWhere(FieldValue As Variant, FieldName As String,
MyCriteria As String, ArgCount As Integer)
' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists. Chr(34) = “
If ArgCount > 0 Then
Chr(39) = ‘
MyCriteria = MyCriteria & " and "
End If Chr(42) = *

' Append criterion to existing criteria.

' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & Chr(42) &
FieldValue & Chr(42) & Chr(39))

' Increase argument count.

ArgCount = ArgCount + 1
End If
End Sub

Database System Concepts 1.5 ©Silberschatz, Korth and Sudarshan

Procedure for Field Cleaning in a Form

Private Sub Cisti_Click()
Dim MySQL As String
Dim Tmp As Variant
MySQL = "SELECT * FROM NajdiNaracka WHERE False"
' Clear search text boxes.
Me![sdob najdi] = Null
Me![sdel najdi] = Null
Me![kol najdi] = Null
Me![data najdi] = Null
' Reset subform's RecordSource property to remove records.
Me![Naracka subform].[Link] = MySQL

' Move insertion point to Look For Company text box.

Me![sdob najdi].SetFocus
‘ Exit_Cisti_Click:
' Exit Sub

‘ Err_Cisti_Click:
' MsgBox "Greska-->" & [Link], vbInformation, "Greska"
' Resume Exit_Cisti_Click
End Sub
Database System Concepts 1.6 ©Silberschatz, Korth and Sudarshan

3
 Procedure for Searching in Database
Private Sub Prikazi_Click()
On Error GoTo Err_Prikazi_Click
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
' Initialize argument count.
ArgCount = 0
' Initialize SELECT statement.
MySQL = "SELECT * FROM NajdiNaracka WHERE "
MyCriteria = ""
' Use values entered in text boxes in form header to create criteria for WHERE clause.
AddToWhere [sdob najdi], "[[Link]]", MyCriteria, ArgCount
AddToWhere [sdel najdi], "[[Link]]", MyCriteria, ArgCount
AddToWhere [kol najdi], "[[Link]]", MyCriteria, ArgCount
AddToWhere [data najdi], "[[Link]]", MyCriteria, ArgCount

' If no criterion specifed, return all records.

If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.

MyRecordSource = MySQL & MyCriteria & " ORDER BY sdob"

Database System Concepts 1.7 ©Silberschatz, Korth and Sudarshan

Continues
' Set RecordSource property of Find Customers Subform.
Me![Naracka subform].[Link] = MyRecordSource

' If no records match criteria, display message.

' Move focus to Clear button.
If Me![Naracka subform].[Link] = 0 Then
MsgBox "Nema zapisi! ", 48, "Greska"
Me![Link]
Else
'Enable control in detail section.
' [Link](acDetail).Enabled = True
'Tmp = EnableControls("Detail", True)
' Move insertion point to Find Customers Subform.
Me![Naracka subform].SetFocus
End If

Exit_Prikazi_Click: Exit Sub

Err_Prikazi_Click:
MsgBox "Greska-->" & [Link], vbInformation, "Greska"
Resume Exit_Prikazi_Click
End Sub

Database System Concepts 1.8 ©Silberschatz, Korth and Sudarshan

4
 Do almost Nothing
Private Sub Form_Activate()
' Used by Solutions to show toolbar that includes Show Me button.
' Hide built-in Form View toolbar.
' Show Custom Form View toolbar.
' [Link] "Form View", A_TOOLBAR_NO
' [Link] "Custom Form View", A_TOOLBAR_YES
End Sub

Private Sub Form_Deactivate()

' Used by Solutions to hide toolbar that includes Show Me button.
' Hide Custom Form View toolbar.
' Show built-in Form View toolbar.
' [Link] "Custom Form View", A_TOOLBAR_NO
' [Link] "Form View", A_TOOLBAR_WHERE_APPROP
End Sub

Private Sub Form_Open(Cancel As Integer)

' Move insertion point to sdob when form is opened.
Me![sdob najdi].SetFocus
End Sub

Database System Concepts 1.9 ©Silberschatz, Korth and Sudarshan

Levels of Abstraction
Private Sub Nov_Click()
On Error GoTo Err_Nov_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Naracka"
[Link] stDocName, , , stLinkCriteria, acFormAdd
Exit_Nov_Click: Exit Sub
Err_Nov_Click:
MsgBox [Link] Private Sub Otvori_Click()
Resume Exit_Nov_Click On Error GoTo Err_Otvori_Click
End Sub Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Naracka"
If IsNull(Me![Naracka subform].Form![sdob]) Then
MsgBox "-->Nemate sifra za ovoj zapis!", vbInformation, "Greska"
Else
stLinkCriteria = "[sdob]='" & Me![Naracka subform].Form![sdob] & "'"
[Link] stDocName, , , stLinkCriteria, acFormEdit
End If
Exit_Otvori_Click: Exit Sub
Err_Otvori_Click:
MsgBox "Greska-->" & [Link], vbInformation, "Greska"
Resume Exit_Otvori_Click
End Sub

Database System Concepts 1.10 ©Silberschatz, Korth and Sudarshan

5
 Instances and Schemas
Private Sub Brisi_Click()
On Error GoTo Err_Brisi_Click

Dim MySQL As String, MyCriteria As String

MySQL = "DELETE FROM Naracka WHERE sdob LIKE "

If IsNull(Me![Naracka subform].Form![sdob]) Then
MsgBox "-->Nemate izbrano zapis!", vbInformation, "Greska"
Else
MySQL = (MySQL & Chr(34) & Me![Naracka subform].Form![sdob] & Chr(34) & " and sdel
LIKE " & Chr(34) & Me![Naracka subform].Form![sdel] & Chr(34))
[Link] MySQL
Prikazi_Click
End If

Exit_Brisi_Click: Exit Sub

Err_Brisi_Click:
MsgBox "Greska-->" & [Link], vbInformation, "Greska"
Resume Exit_Brisi_Click
End Sub

Database System Concepts 1.11 ©Silberschatz, Korth and Sudarshan

Procedure for Opening Form

Private Sub Nazad_Click()
On Error GoTo Err_Nazad_Click

Dim stDocName As String

Dim stLinkCriteria As String
[Link]
stDocName = "GlavnoMeni"
[Link] stDocName, , , stLinkCriteria

Exit_Nazad_Click: Exit Sub

Err_Nazad_Click:
MsgBox [Link]
Resume Exit_Nazad_Click
End Sub

Database System Concepts 1.12 ©Silberschatz, Korth and Sudarshan