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.

Download Report

Transcript 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.

Chapter 2: Intro to Relational Model Database System Concepts, 6 th 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 - 6 th Edition 2.2

©Silberschatz, Korth and Sudarshan

Attribute Types

    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 The special value

null

is a member of every domain The null value causes complications in the definition of many operations

Database System Concepts - 6 th Edition 2.3

©Silberschatz, Korth and Sudarshan

Relation Schema and Instance



1 ,

2 , …,

A n

are

attributes



= (

1 ,

2 , …,

A n

) is a

relation schema

Example: 

instructor

= (

ID, name, dept_name, salary

) Formally, given sets

1 ,

2 , ….

D n D

1 x

2 x … x

D n

relation

is a subset of Thus, a relation is a set of

-tuples (

1 ,

2 , …,

a n

) where each

a i



D i

  The current values (

relation instance

) of a relation are specified by a table An element

is a

tuple

, represented by a

row

in a table

©Silberschatz, Korth and Sudarshan Database System Concepts - 6 th Edition 2.4

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 - 6 th 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

©Silberschatz, Korth and Sudarshan Database System Concepts - 6 th Edition 2.6

Keys

  Let K  R

is a

superkey

if values for

are sufficient to identify a unique tuple of each possible relation

r(R)

 Example: {

} and {ID,name} are both superkeys of

instructor.

 Superkey

is a

candidate key

is minimal Example: {

} is a candidate key for

Instructor

 One of the candidate keys is selected to be the

primary key

  which one?

Foreign key

constraint: Value in one relation must appear in another 

Referencing

relation 

Referenced

relation

©Silberschatz, Korth and Sudarshan Database System Concepts - 6 th Edition 2.7

Schema Diagram for University Database Database System Concepts - 6 th Edition 2.8

©Silberschatz, Korth and Sudarshan

Relational Query Languages

   Procedural vs.non-procedural, or declarative “Pure” languages:  Relational algebra  Tuple relational calculus  Domain relational calculus Relational operators

Database System Concepts - 6 th Edition 2.9

©Silberschatz, Korth and Sudarshan

 Relation r

Selection of tuples

 Select tuples with A=B and D > 5  σ A=B and D > 5 (r)

Database System Concepts - 6 th Edition 2.10

©Silberschatz, Korth and Sudarshan

Selection of Columns (Attributes)

 Relation

:  Select A and C  Projection  Π A, C (r)

Database System Concepts - 6 th Edition 2.11

©Silberschatz, Korth and Sudarshan

Joining two relations – Cartesian Product

 Relations

r, s

: 

Database System Concepts - 6 th Edition 2.12

©Silberschatz, Korth and Sudarshan

 Relations

r, s:

Union of two relations

 r  s:

Database System Concepts - 6 th Edition 2.13

©Silberschatz, Korth and Sudarshan

Set difference of two relations

 Relations

: 

r – s:

Database System Concepts - 6 th Edition 2.14

©Silberschatz, Korth and Sudarshan

Set Intersection of two relations

 Relation

r, s

: 



Database System Concepts - 6 th Edition 2.15

©Silberschatz, Korth and Sudarshan

Joining two relations – Natural Join

 Let

and

be relations on schemas

Then, the “natural join” of relations

schema



obtained as follows: and

and

respectively. is a relation on   Consider each pair of tuples

t r

from

and

t s

from

. If

t r

and

t s



have the same value on each of the attributes , add a tuple

to the result, where 

has the same value as

t r



has the same value as

t s

Database System Concepts - 6 th Edition 2.16

 Relations r, s:

Natural Join Example

 Natural Join  r s

Database System Concepts - 6 th Edition 2.17

Figure in-2.1

Database System Concepts - 6 th Edition 2.18

End of Chapter 2 Database System Concepts, 6 th Ed

for conditions on re-use

Figure 2.01

Database System Concepts - 6 th Edition 2.20

Figure 2.02

Database System Concepts - 6 th Edition 2.21

Figure 2.03

Database System Concepts - 6 th Edition 2.22

Figure 2.04

Database System Concepts - 6 th Edition 2.23

Figure 2.05

Database System Concepts - 6 th Edition 2.24

Figure 2.06

Database System Concepts - 6 th Edition 2.25

Figure 2.07

Database System Concepts - 6 th Edition 2.26

Figure 2.10

Database System Concepts - 6 th Edition 2.27

Figure 2.11

Database System Concepts - 6 th Edition 2.28

Figure 2.12

Database System Concepts - 6 th Edition 2.29

Figure 2.13

Database System Concepts - 6 th Edition 2.30

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.

Transcript 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.

Directory