The Relational Model slide 64 - - PowerPoint PPT Presentation

Relational data model - 1

• 63
• The Relational Model

slide 64

Operators of Relational Algebra

slide 77

Query examples

slide 94

Optimization

slide 99

64

• Type or domain

Cartesian Product Relation Attributes Key Relational Schema and Database Schema Foreign Key Data dictionary The Relational Model 65

• Definition

Set of values (defined in intension or in extension)

Examples

Integer, Real, String, Boolean Salary = 1000…100000 () Color = {‘blue’, ‘white’, ‘red’}

The Relational Model

Relational data model - 2

66

• Definition

The cartesian product of D1, ...., Dn is the set of tuples <V1,..,

Vn> where Vi ∈ Di

• Notation

D1 X ....X Dn

Example :

D1 = {‘DB’, ‘OO’} (Modules) D2 = {‘Lecocq’, ‘Bac’} (Teachers)

Bac OO Lecocq OO Bac DB Lecocq DB D2 D1 D1 X D2

The Relational Model

67

• Definition

Subset of the cartesian product of a list of domains Characterized by a name

Example

D1 = Modules D2 = Teachers

The Relational Model

Bac OO Lecocq DB D2 D1 ModulesCo

• rd

68

!

More simply, a relation is a 2-dimensional table,

composed of rows and columns

A row is called a tuple A name is associated to each column in order to

find it without order information = attribute

takes its values in a given domain Example : ModuleId

The Relational Model

Bac OO Lecocq DB coord ModuleId ModulesCo

• rd

69

"#$

21 Maisel Meunier 3 20 CROUS Millot 2 20 Maisel Bélaïd 1 Age Address Name StudentI d Student 10 DB 2 20 DB 1 18 OO 3 17 OO 2 Mark ModuleId StudentId

Registration

The Relational Model

Relational data model - 3

70

%

• Definition

A key is a minimal set of attributes that determines a unique

tuple in a relation (at every moment)

• Example

Key of Student ? Key of Module ? Key of Registration ?

• Integrity Constraint

Every relation must have a key that is filled (without an unknown

• r null value)

The Relational Model

71

&'

• Definition

A relational schema is composed of :

• A name
• A list of attributes and their associated domains
• The list of attributes composing the key (the key is underlined)
• Example

Student (StudentId : integer, name : string, address : string,

age : integer between 18 and 35)

Intension vs. Extension

Relational Schema : the intension of the relation Table : the extension of the relation Relational Database: a set of Relational Schemas

The Relational Model

72

(%

Definition

A Foreign Key is a set of attributes appearing as key in

another relation

R1(A1, A2, .... , Ap, Ap+1, ...., An) R2(B1, B2, ......, Bn)

Role

Foreign Keys define referential integrity constraints

between relations

The Relational Model

73

(% !

Updates and Foreign Keys

Insertion: the value of attributes must exist in the

referenced relation.

Insertion of the tuple (4, ‘DB’, 15) in Registration ?

deletion in the referenced relation is possible iff

there are no referencing tuples.

deletion of Student number 2 of Student ?

Foreign Keys reflect the relationships of the E/R

model

The Relational Model

Relational data model - 4

74

(%

• Examples

Student(StudentId, name, address, age) Module(ModuleID, nbh, coord) Registration(StudentId, ModuleId, mark) Book(BookId, title, StudentId, lendingDate) Room(RoomId, price, StudentId)

The Relational Model

75

• Definition

A “database” containing the set of schemas and

correspondence rules associated to a database

• Principle

A database describing other databases, that is:

• relations
• attributes
• domains
• keys .....

A particular Database managed by the Database Manager

The Relational Model

76

&$

The Relational Model

Key Table Schema Intension Tuple Relation2 Foreign Key Relation1 attributen attribute1 attributea attribute2 vn v1 v3 w1 v1 w2 w1 w2 w2 attributeb xi wn DB attribute 2 Relation 1 attribute 1 Relation 1 attribute RelName DB Schem a DD (Data Dictionary)

…

77

) '

• Languages for Data Definition (DDL) :

Definition /updates of relational schemas

Query Languages (QL) : allow data manipulation

and retrieval from a database

Queries : data retrieval Updates : insertion, deletion, updates

2 classes of languages :

Algebraic SQL Predicative QBE

Relational data model - 5

78

• Every result of an operation is a relation; it can thus be

given as input to a new operator (composition).

Operators can be classified as follows :

set operators / relational operators basic operators / derived operators unary operators / binary operators

• Unary : Selection , Projection,
• Binary : Union, Intersection, Set-difference, cartesian Product, Join,

Division

Relational Algebra

79

&

• Goal
• to "select" a subset of rows

(tuples) satisfying a condition

• The Selection Operator reduces

the “vertical” size of the relation

• Constraints
• Unary
• requires a condition
• Notation
• Textual Notation : Tσcond(R)
• Graphical Notation :
• Students registered in DB :

σModuleId=‘DB’(Registration)

• Major Students (mark >15) in DB:

σModuleId=‘DB’ and

mark>15(Registration)

Relational Algebra

Cond.

10 DB 2 20 DB 1 mark ModuleId StudentId Result 20 DB 1 mark ModuleId StudentId Result R T 80

*

• Goal
• "selecting" attributes
• The projection operator reduces

the “horizontal “ size of the relation

• Constraints
• Unary
• requires a list of attributes
• Notation
• Textual Notation : TΠattributes(R)
• Graphical Notation :

R T

• Students’ Addresses :

ΠAddress(Student)

• Id and nb. of hours of Modules :

ΠModuleID,nbh(Module)

Relational Algebra

attributes.

Maisel CROUS Address Result 15 DB 45 OO nbh ModuleID Result No doubles 81

+

• Goal
• allows to union 2 relations
• Constraints
• Binary
• The 2 input relations must

have the same schema

• Same number of attributes
• “Corresponding” attributes

have the same type (domain)

• Notation
• Textual Notation : T  R ∪ S
• Graphical Notation :
• 1st step: Teachers’ and Students’

Names

• People’s Names at INT : Prof∪Student

Relational Algebra

∪

R S T Lecocq Millot Bac Name Prof Meunier Millot Bélaïd Name Student Meunier Bélaïd Lecocq Millot Bac Name Result

No doubles

Relational data model - 6

82

• Goal
• allows to obtain the tuples

belonging to 2 relations at the same time

• Constraints
• Binary
• Same schema for the 2 input

relations

• Notation
• Textual Notation : T R ∩ S
• Graphical Notation :
• 1st step: Teachers’ and Students’

Names

• Teachers’ and Students’ names in

common : Prof∩Student Relational Algebra

∩

R S T Lecocq Millot Bac Name Prof Meunier Millot Bélaïd Name Student Millot Name Result 83

$$

• Goal
• Obtaining the set of tuples

being in Relation1, but not in Relation2

• Constraints
• Binary
• Same schema for the 2 input

relations

• Non commutative
• Notation
• Textual Notation : T  R - S
• Graphical Notation :

1st step: Teachers’ and

Students’ Names

• Students that do not have the

name of a Teacher : Student- Prof

Relational Algebra

• R

S T Lecocq Millot Bac Name Prof Meunier Millot Bélaïd Name Student Meunier Bélaïd Name Result 84

• Goal

to combine 2 relations : by concatenation of each tuple of R with each

tuple of S

• Constraints

Binary Resulting Schema :

• R(a1, a2, ...., an), S(b1, b2, ..., bp)
• T  R X S, T(a1, a2, ...., an, b1, b2, ..., bp)

Card (R X S) = Card (R) * Card (S)

Notation

Textual Notation : T  R X S Graphical Notation :

Relational Algebra

X

R S T 85

!

Relational Algebra 21 Maisel Meunier 3 20 CROUS Millot 2 20 Maisel Bélaïd 1 Age Address Name StuId Student Lecocq 15 DB Bac 45 OO Coord Nbh ModId Mod Lecocq 15 DB 21 Maisel Meunier 3 Lecocq 15 DB 20 CROUS Millot 2 Lecocq 15 DB 20 Maisel Bélaïd 1 21 20 20 Age OO OO OO ModId 45 45 45 Nbh Bac Bac Bac Coord Maisel Meunier 3 CROUS Millot 2 Maisel Bélaïd 1 Address Name StuId

Student X Mod

Relational data model - 7

86

,

• Goal

Permits to establish a semantic link between 2 relations

• Constraints

Binary Resulting schema :

• R(a1, a2, ...., an), S(b1, b2, ..., bp)
• T  R S T(a1, a2, ...., an, b1, b2, ..., bp)

Notation

Textual Notation : T  R S condition Graphical Notation :

Relational Algebra

R S T condition 87 200 150

Price

3 2 StudId 10 21 Maisel Meunier 3 21 20 CROUS Millot 2 Age RId Address Name StudId Student Room 150 200

Price

2 3 StudId 21 10 RId Room Student.StudId=Room.StudId

• #$'*
• 1 tuple of Room 1 tuple of results
• 1 tuple of Student 0 or 1 tuple of results
• We lost Bélaïd !

21 20 20 Age Maisel Meunier 3 CROUS Millot 2 Maisel Bélaïd 1 Address Name

StudId

Student 88 3 2 2 1 StudentId 18 10 17 20 mark OO 21 Maisel Meunier 3 DB 20 CROUS Millot 2 20 20 Age OO DB

ModuleId

CROUS Millot 2 Maisel Bélaïd 1 Address Name StudentId

Registration

Student 10 DB 2 20 DB 1 18 OO 3 17 OO 2 mark ModuleId StudentId Registr ation

21

Maisel Meunier 3

20

CROUS Millot 2

20

Maisel Bélaïd 1

Age

Address Name

Student Id

Studen t Registration.StudentId=Student.StudentId

#$'*

1 tuple of Registration 1 tuple of results 1 tuple of Student 0 to n tuples of results

Millot appears twice !

89

• Goal
• Answering to queries of the type « all»
• A tuple t is in T (the resulting relation) if and only if for every tuple s of S, the

tuple <t,s> belongs to R

• Constraints
• Binary
• Resulting schema:
• R(a1, a2, ...., an, b1, b2, ..., bp), S(b1, b2, ..., bp)
• T  R ÷ S, T(a1, a2, ...., an)
• Notation
• Textual Notation : T  R S
• Graphical Notation :
• Derived operator
• Projection + Cartesian Product + Difference.
• R S  T1 - T2 with:
• T1  ∏ schema(R) - schema(S) (R)
• T2  ∏ schema(R) - schema(S) ((∏ schema(R) - schema(S) (R) X S) - R)

Relational Algebra

• R

S T

Relational data model - 8

90

!

Example

Which are the students that are registered in all the

Modules?

Relational Algebra

10 DB 2 20 DB 1 18 OO 3 17 OO 2 mark ModuleId StudentId Registr ation 91

.!

• Example

Step 1: Building R, set of all

required informations = attributes StudentId and ModuleId of Registration (R)

Step 2 : Building S, set

corresponding to “all the Modules” = Module (S)

Result  R S Verification :

• Result X S ⊆ R

RΠStudentId,ModuleId(Registration) SΠModuleId(Module) Result

DB 2 DB 1 OO 3 OO 2 ModuleId StudentId R DB OO ModuleId S 2 StudentId Result

Relational Algebra

92

&'/$

Relational Algebra

T  R S T  R S

condition

T  R X S T  R - S T R ∩ S T  R ∪ S TΠattributs(R) Tσcond(R) Textual Notation Answers to queries of the type « all» Division Etablishes the semantic link between 2 relations Join Concatenates each tuple of R with each tuple of S Cartesian Product Tuples of a relation not belonging to another relation Difference Gives the set of tuples common to 2 relations Intersection Unions the extensions of 2 relations Union Selects some attributes (columns) from a relation Projection Selects a subset of tuples (rows) from a relation Selection Graphical Notation Semantics Operator Cond. attributs.

∪ ∩

−

X

• 93

'/$

Relational Algebra

Schema(R)=Schema(S)∪Schema( T) Schema(T)=Schema(S)∪Schema( R) Schema(T)=Schema(S)∪Schema( R) Schema(R)=Schema(S)=Schema(T ) Schema(R)=Schema(S)=Schema(T ) Schema(R)=Schema(S)=Schema(T ) Schema(T) ⊆ Schema(R) Schema(T) = Schema(R) Schemas Binary Division T  R S Condition of join on attributes of R and S Binary Join T  R S

condition

Binary Cartesian Product T  R X S Binary Difference T  R - S Binary Intersection T R ∩ S Binary Union T  R ∪ S List of attributes of R Unary Projection TΠattributes(R) Condition on attributes of R Unary Selection Tσcond(R) « Parameters » Unary/B inary Operator

Relational data model - 9

94

0"#

• Database : a company

Employee(ESSN, lastName, firstName, birthDate, address, sex, salary,

bonusAmount, superSSN, depNumber)

Department(depNumber, depName, mgrSSN, mgrStartDate) Dept_locat(depNumber, dLocation) Project(projNumber, name, pLocation, depNumber) Work_on(projNumber, ESSN, Nbh)

Examples

95

"$

Examples

(1,1) 0,1 0,1 0,n 0,n

Project projNumber name pLocation Dept-Local dLocation control Departement depNumber depName mgrStartDate Work_on nbh Employee ESSN lastName firstName birthDate address sex salary bonusAmount

0,n

supervise supervises

1,1 supervised_by 0,1

locate Works_for

0,n

manages

0,n 0,1

96

$

Textual writing :

Temp  σ salary>2000 (Employee) Result  Π ESSN(Temp) Result  Π ESSN(σ salary=2000 (Employee))

Algebraic Tree :

salary >2000 ESSN Employee Result

Examples

97

(*1&$12'*2' 2'1

Stan  σ pLocation=‘Stanford’(Project) DepStan  Stan Department

depNumber=depNumber

ManDepStan  DepStan Employee

mgrssn=ESSN

Result  Π projNumber,depNumber, lastName(ManDepStan)

Examples

pLocation=‘Stanford’

projNumber, depNumber, lastName

Project Result Department Employee

depNumber = depNumber

mgrssn=Essn

Relational data model - 10

98

' $ ' 3 *

AllEmp  Π ENSS (Employee) EmpOnProj  Π ENSS (Work_on) EmpWithoutProj  AllEmp - EmpOnProj Result  Π lastName (EmpWithoutProj >< Employee)

ENSS = ENSS

Examples

ENSS Result

Employee

ENSS

Work_on

• Employee

ENSS = ENSS

lastName

99

4

• Retrieve the lastname and firstname of all employees whose salary is greater

than 300 k and work for the 'R&D' department and are involved on a project for more than 30 hours

Many possible answers Optimizing = choosing the “best” answer, i.e the most

efficient one

Optimization

100

• salary>300

Employee

depNumber=depNumber ESSN=ESSN

lastName, firstName

Result nbh>30 Work_on

Optimization

depName ="R&D" Department

101

• Optimization

Salary>300 Employee

depNumber=depNumber ESSN=ESSN

lastName, firstName

Result nbh>30 Work_on depName ="R&D" Department

Relational data model - 11

102

.

Optimization

Salary>300 and Employee

depNumber=depNumber ESSN=ESSN

lastName firstName

Result nbh>30 Work_on depName ="R&D« and Department

103

5

Optimization

salary>300 Employee

depNumber=depNumber ESSN=ESSN

lastName, firstName

Result nbh>30 Work_on depName ="R&D" Department

depNumber ESSN, lastName, firstName ESSN

104

4'6&

Optimizing :

The number of inputs/outputs CPU Time …

Main Factors :

Order of execution of algebraic operations Choice of algorithm that implements algebraic operations Disk Space Management Size of intermediate relations

Optimization

105

• Mostly used heuristics

Unary operators come first (selection, projection) => to lower the

size of relations

Joins come afterwards

• Used properties

Associativity of joins Commutativity selection / projection Commutativity selection / join Commutativity projection / join

Optimization

Relational data model - 12

106

• How to set the user free of optimization

Expressing a query without defining the order of algebraic

• perations

non procedural language declarative language

DBMS’s Responsabilities

Translating the query as an algebraic tree Tree optimization (restructuration) and execution plan

construction (choice of algorithm, access methods, …)

processing the best execution plan

Optimization