Transcript PPT

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
Database System Concepts






Chapter 1: Introduction
Part 1: Relational databases

Chapter 2: Introduction to the Relational Model

Chapter 3: Introduction to SQL

Chapter 4: Intermediate SQL

Chapter 5: Advanced SQL

Chapter 6: Formal Relational Query Languages
Part 2: Database Design

Chapter 7: Database Design: The E-R Approach

Chapter 8: Relational Database Design

Chapter 9: Application Design
Part 3: Data storage and querying

Chapter 10: Storage and File Structure

Chapter 11: Indexing and Hashing

Chapter 12: Query Processing

Chapter 13: Query Optimization
Part 4: Transaction management

Chapter 14: Transactions

Chapter 15: Concurrency control

Chapter 16: Recovery System
Part 5: System Architecture

Chapter 17: Database System Architectures

Chapter 18: Parallel Databases

Chapter 19: Distributed Databases
Database System Concepts - 6th Edition





Part 6: Data Warehousing, Mining, and IR

Chapter 20: Data Mining

Chapter 21: Information Retrieval
Part 7: Specialty Databases

Chapter 22: Object-Based Databases

Chapter 23: XML
Part 8: Advanced Topics

Chapter 24: Advanced Application Development

Chapter 25: Advanced Data Types

Chapter 26: Advanced Transaction Processing
Part 9: Case studies

Chapter 27: PostgreSQL

Chapter 28: Oracle

Chapter 29: IBM DB2 Universal Database

Chapter 30: Microsoft SQL Server
Online Appendices

Appendix A: Detailed University Schema

Appendix B: Advanced Relational Database Model

Appendix C: Other Relational Query Languages

Appendix D: Network Model

Appendix E: Hierarchical Model
2.2
©Silberschatz, Korth and Sudarshan
Chapter 2: Intro to Relational Model
 2.1 Structure of Relational Databases
 2.2 Database Schema
 2.3 Keys
 2.4 Schema Diagrams
 2.5 Relational Query Languages
 2.6 Relational Operations
Database System Concepts - 6th Edition
2.3
©Silberschatz, Korth and Sudarshan
In The Beginning...
 Everything in Table
 Set-oriented Query Language
E.F. Codd
-- 1970 CACM Paper
-- Turing Award
1970
4
Database System Concepts -
6th
Edition
2.4
©Silberschatz, Korth and Sudarshan
Example of a Relation
attributes
(or columns)
tuples, records
(or rows)
 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 - 6th Edition
2.5
©Silberschatz, Korth and Sudarshan
Relation Schema and Instance
 A1, A2, …, An are attributes
 R = (A1, A2, …, An ) is a relation schema
Example: instructor = (ID, name, dept_name, salary)
 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
 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
Database System Concepts - 6th Edition
2.6
©Silberschatz, Korth and Sudarshan
Relations are Unordered
 Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
 Relations are basically set!
 Example: the instructor relation with unordered tuples
Database System Concepts - 6th Edition
2.7
©Silberschatz, Korth and Sudarshan
Database
 A database consists of multiple relations
 Information about an University enterprise is broken up into parts
instructor
student
advisor
 Bad design:
university (instructor -ID, name, dept_name, salary, building, budget..)
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.8
©Silberschatz, Korth and Sudarshan
University Relation
(instructor -ID, name, dept_name, salary, building, budget)
Database System Concepts - 6th Edition
2.9
©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.

which one?
 Foreign key constraint: Value in one relation must appear in another

Referencing relation

Referenced relation
Database System Concepts - 6th Edition
2.10
©Silberschatz, Korth and Sudarshan
Schema Diagram for University Database
Database System Concepts - 6th Edition
2.11
©Silberschatz, Korth and Sudarshan
Figure 2.02: The Course relation
Figure 2.05: The Department Relation
Figure 2.03: The Pre-requisite relation
Database System Concepts - 6th Edition
2.12
Figure 2.04: The Instructor relation
©Silberschatz, Korth and Sudarshan
Figure 2.06: The Section relation
Figure 2.07: The Teaches relation
Database System Concepts - 6th Edition
2.13
©Silberschatz, Korth and Sudarshan
Relational Query Languages
 Procedural vs. non-procedural (declarative)
 “Pure” formal query languages:

Relational algebra

Tuple relational calculus

Domain relational calculus
 Relational operators

Select

Project

Cartesian Product

Set Union

Set Minus
Database System Concepts - 6th Edition
2.14
©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.15
©Silberschatz, Korth and Sudarshan
Projection (Π) of Columns (Attributes)
 Relation r:
 Select A and C
Projection
Π A, C (r)
Database System Concepts - 6th Edition
2.16
©Silberschatz, Korth and Sudarshan
Cartesian Product ( x ):
Cross-Product two relations
 Relations r, s:
 r x s:
Database System Concepts - 6th Edition
2.17
©Silberschatz, Korth and Sudarshan
Union (  ) of two relations
 Relations r, s:
 r  s:
Database System Concepts - 6th Edition
2.18
©Silberschatz, Korth and Sudarshan
Set difference ( – ) of two relations
 Relations r, s:
 r – s:
Database System Concepts - 6th Edition
2.19
©Silberschatz, Korth and Sudarshan
Set Intersection (  ) of two relations
 Relation r, s:
rs
Database System Concepts - 6th Edition
2.20
©Silberschatz, Korth and Sudarshan
Natural Join ( ) : Joining two relations
 Let r and s be relations on schemas R and S respectively.
 The “natural join” of relations R and 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 tr on r

t has the same value as ts on s

Relations r, s:

Natural Join

Database System Concepts - 6th Edition
r
s
2.21
©Silberschatz, Korth and Sudarshan
Some of Relational Algebra Operators: Figure in-2.1
Database System Concepts - 6th Edition
2.22
©Silberschatz, Korth and Sudarshan
Figure 2.02: The Course relation
Figure 2.05: The Department Relation
Figure 2.03: The Pre-requisite relation
Database System Concepts - 6th Edition
2.23
Figure 2.04: The Instructor relation
©Silberschatz, Korth and Sudarshan
Figure 2.06: The Section relation
Figure 2.07: The Teaches relation
Database System Concepts - 6th Edition
2.24
©Silberschatz, Korth and Sudarshan
Figure 2.13: selecting attributes
ID and salary of instructors with
salary greater than $85000
Figure 2.10: selecting instructors
with salary greater than $85000
Figure 2.11: selecting ID and salary
attributes from the instructor relations
Figure 2.12: Natural Join of the
instructor and department relations
Database System Concepts - 6th Edition
2.25
©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