Transcript Database System Concepts, 6 th Ed

Chapter 2: Intro to Relational Model
Database System Concepts, 6th Ed.
©Silberschatz, Korth and Sudarshan
See www.db-book.com for conditions on re-use
Example of a Relation
attributes
(or columns)
tuples
(or rows)
Database System Concepts - 6th Edition
2.2
©Silberschatz, Korth and Sudarshan
Attribute Types
 The set of allowed values for each attribute is called the domain
of the attribute

Eg., Domain of name attribute is a set of all possible names.
 Attribute values are (normally) required to be atomic; that is,
indivisible

Eg., a attribute phone number is atomic if it stores a single
phone number
 The special value null is a member of every domain
 The null value causes complications in the definition of many
operations
Database System Concepts - 6th Edition
2.3
©Silberschatz, Korth and Sudarshan
Relation Schema and Instance
 Relation schema

Defines the logical design of the relation

Example: student(sid, name)
 Relation instance

Snapshot of the data in the relation at a given instant in time

Example:
sid
name
123-45-6789
John
987-65-4321
Mary
Database System Concepts - 6th Edition
2.4
©Silberschatz, Korth and Sudarshan
Relations are Unordered
 Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
 Example: instructor relation with unordered tuples
Database System Concepts - 6th Edition
2.5
©Silberschatz, Korth and Sudarshan
Database
 A database consists of multiple relations
 Information about an enterprise is broken up into parts
instructor
student
advisor
 Bad design:
univ (instructor -ID, name, dept_name, salary, student_Id, ..)
results in

repetition of information (e.g., two students have the same instructor)

the need for null values (e.g., represent an student with no advisor)
 Normalization theory (Chapter 7) deals with how to design “good”
relational schemas
Database System Concepts - 6th Edition
2.6
©Silberschatz, Korth and Sudarshan
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)

Example: {ID} and {ID,name} are both superkeys of instructor.
 Superkey K is a candidate key if K is minimal
Example: {ID} is a candidate key for Instructor
 One of the candidate keys is selected to be the primary key.
 Foreign key constraint: Value in one relation must appear in another

Referencing relation

Referenced relation
Database System Concepts - 6th Edition
2.7
©Silberschatz, Korth and Sudarshan
Schema Diagram for University Database
Database System Concepts - 6th Edition
2.8
©Silberschatz, Korth and Sudarshan
Relational Query Languages
 Procedural vs. non-procedural, or declarative

In a procedural language, the user instructs the system to perform a
sequence of instructions on the database to compute the desired
result

In a nonprocedural language, the user describes the desired
information without giving a specific procedure for obtaining the
information
 Pure query languages:

Relational algebra – procedural

Tuple relational calculus - nonprocedural

Domain relational calculus - nonprocedural
 Relational operators
Database System Concepts - 6th Edition
2.9
©Silberschatz, Korth and Sudarshan
Selection of tuples
 Relation r
 Select tuples with A=B
and D > 5
σ
A=B and D > 5
Database System Concepts - 6th Edition
(r)
2.10
©Silberschatz, Korth and Sudarshan
Selection of Columns (Attributes)
 Relation r:
 Select A and C
Projection
Π A, C (r)
Database System Concepts - 6th Edition
2.11
©Silberschatz, Korth and Sudarshan
Joining two relations – Cartesian Product
 Relations r, s:
 r x s:
Database System Concepts - 6th Edition
2.12
©Silberschatz, Korth and Sudarshan
Union of two relations
 Relations r, s:
 r  s:
Database System Concepts - 6th Edition
2.13
©Silberschatz, Korth and Sudarshan
Set difference of two relations
 Relations r, s:
 r – s:
Database System Concepts - 6th Edition
2.14
©Silberschatz, Korth and Sudarshan
Set Intersection of two relations
 Relation r, s:
 rs
Database System Concepts - 6th Edition
2.15
©Silberschatz, Korth and Sudarshan
Natural Join Example
 Relations r, s:
 Natural Join
 r
s
Database System Concepts - 6th Edition
2.16
©Silberschatz, Korth and Sudarshan
Figure in-2.1
Database System Concepts - 6th Edition
2.17
©Silberschatz, Korth and Sudarshan
End of Chapter 2
Database System Concepts, 6th Ed.
©Silberschatz, Korth and Sudarshan
See www.db-book.com for conditions on re-use