Prepare for your exams
Get points
Guidelines and tips
Sell on Docsity
Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Relational Languages: Algebra, Tuple Calculus, Domain Calculus, and Datalog, Summaries of Algebra

University of Tabuk (UT)Algebra

express a database query in Relational Algebra, involving the basic operators (selection, projection, cross product, renaming, set union, intersection,.

Typology: Summaries

2021/2022

Uploaded on 09/07/2022

nabeel_kk 🇸🇦

4.6

(65)

1.3K documents

1 / 48

This page cannot be seen from the preview

Don't miss anything!

Unit 5 1

Laks V.S. Lakshmanan,; Based partly on Ramakrishnan & Gehrke, DB Management Systems

Unit 5

Formal Relational Languages

Text: Chapters 4 & 24

Relational Algebra (Ch. 4: 4.1—4.2)

Tuple Relational Calculus X

Domain Relational Calculus X

Datalog (Ch. 24)

Discover Summaries of Algebra University of Tabuk (UT)

Partial preview of the text

Download Relational Languages: Algebra, Tuple Calculus, Domain Calculus, and Datalog and more Summaries Algebra in PDF only on Docsity!

Unit 5 Laks V.S. Lakshmanan,;^ Based^ partly on^ Ramakrishnan^ &^ Gehrke, DB Management Systems 1

Unit 5

Formal Relational Languages

Text: Chapters 4 & 24

Relational Algebra (Ch. 4: 4.1—4.2)

Tuple Relational Calculus X

Domain Relational Calculus X

Datalog (Ch. 24)

Learning Goals

Given a database (a set of tables ) you will be

able to

 express a database query in Relational Algebra, involving the basic operators (selection, projection, cross product, renaming, set union, intersection, difference ), join, division and assignment

 rewrite RA expressions (queries) using a subset of the operators with expressions using another subset

 show that two RA queries are/aren’t equivalent

 express a DB query in Datalog

 translate RA queries to Datalog; translate queries from a fragment of Datalog to RA.

Relational Algebra (RA)

 Procedural language

 Basic operations:

 Selection - Selects a subset of rows from relation.  Projection - Deletes unwanted columns from relation.  Cross-product - Allows us to combine two relations.  Set-difference - Tuples in reln. 1, but not in reln. 2.  Union - Tuples in reln. 1 and tuples in reln. 2.  Rename – Assigns a(nother) name to a relation

 Additional operations:

 intersection, join, division, assignment: not essential, but very useful

 The operators take one or two relations as inputs and give a new relation as a result.

 Operations can be composed. (Algebra is “closed”.)

Datalog (Lite)

 Will see Datalog (full) later.

 Rule-based: head  body.

 head is of the form 𝑝 𝑋 1 , … , 𝑋𝑛. Here, 𝑋𝑖 are

variables. Think of 𝑝(… ) as a “collector” of

answers to the query expressed by the body.

 body is a conjunction (i.e., AND) of atoms of

two kinds:

 𝑟(𝑌 1 , … , 𝑌𝑘) where 𝑟 is a database relation.  𝑋 𝑜𝑝 𝑐 or 𝑋 𝑜𝑝 𝑌 where 𝑋, 𝑌 are variables and 𝑐 is a constant.

 Variables are bound to values appearing in the

DB when query is evaluated.

 Constants can be numerical, string, etc.

Selection

 Notation:  p(r)

 p is called the selection predicate

Set of tuples of r that satisfy p

 Defined as:

 p ( r ) = { t | t  r and p(t) }

Where p is a formula in propositional calculus consisting of predicates connectives :  ( and ),  ( or ),  ( not ) A predicate is one of: op or op where op is one of: =, , >, , <, .  Result schema is same as r’s schema

Selection Example 1

Relation r A B C D

   

   

1 5 12 23

7 7 3 10

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

A B C D

 

1 23

7 10

RA (^) Datalog

attribute

variable

constant

Selection Example 3

 Find customers in Cust with rating less than 9

and salary more than 80.

cid cname rating salary 21 Y. Yuppy 5 95 50 B. Rusty 10 65 55 S. Sneezy 8 70

Cust

Projection

 Notation:

where 𝐴 1 , … , 𝐴𝑘 are attributes (the projection

list) and 𝑟 is a relation.

 The result = relation over the k attributes

𝐴 1 , 𝐴 2 , … , 𝐴𝑘 obtained from 𝑟 by erasing the

columns that are not listed and eliminating

duplicate rows.

 Remember: relations are sets!

Projection Example 2

cname rating R. Rudy 9 G. Grumpy 8 S. Sneezy 5 R. Rusty 10

( )

, Cust cname rating

salary 95 55

( Cust )

salary

( ))?

7 ( , Cust cname rating rating 

cid cname rating salary 38 R. Rudy 9 95 32 G. Grumpy 8 55 51 S. Sneezy 5 95 78 R. Rusty 10 55 Cust

𝑎𝑛𝑠 𝐶, 𝑅 ← 𝑐𝑢𝑠𝑡 𝐼, 𝐶, 𝑅, 𝑆 , 𝑅 > 7.^1

Union, Intersection, Set-

Difference

 Notation: r  s r  s r – s

 Defined as:

r  s = {t | t  r or t  s}

r  s ={ t | t  r and t  s }

r – s = {t | t  r and t  s}

 For these operations to be well-defined:

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 has same domain of values

as the 2nd column of s)

 What is the schema of the result?

Unit 5 16

Union,Int., Diff. Examples

cid cname rating salary

22 J. Justin 7 65 31 R. Rubber 8 85 58 N. Nusty 10 85 44 G. Guppy 5 70 28 Y. Yuppy 9 95

C 1  C 2

cid cname rating salary 31 R.Rubber 8 85 58 N. Nusty 10 85

C 1  C 2

cid cname rating salary 58 J. Justin 7 65

C 1  C 2

cid cname rating salary

22 J. Justin 7 65 31 R. Rubber 8 85 58 N. Nusty 10 85

cid cname rating salary 28 Y. Yuppy 9 95 31 R. Rubber 8 85 44 G. Guppy 5 70 58 N. Nusty 10 85

C 1 C 2

How do you write these queries in Datalog?

Cartesian (or Cross)-Product

 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 r and s have common attributes, they must

be renamed in the result.

Unit 5 19

Cartesian-Product Example 2

cid cname rating salary 22 J. Justin 7 65 31 R. Rubber 8 85 58 N. Nusty 10 85

cid iid day qty

22 101 10/10/0 6 10 58 103 11/12/0 6 5

Customer (^) Order

conflicting Customer x Order names

Customer .cid

sname rating salary Order. cid

iid day qty

22 J. Justin 7 65 22 101 10/10/96 10 22 J. Justin 7 65 58 103 11/12/96 5 31 R. Rubber 8 85 22 101 10/10/96 10 31 R. Rubber 8 85 58 103 11/12/96 5 58 N. Nusty 10 85 22 101 10/10/96 10 58 N. Nusty 10 85 58 103 11/12/96 (^52)

Attribute names present no issues for Datalog.

Unit 5 20

Rename

 Allows us to name results of relational-algebra expressions.  Allows us to assign more names to a relation.  Allows us to rename attributes of a relation.  Notation

Relational Languages: Algebra, Tuple Calculus, Domain Calculus, and Datalog, Summaries of Algebra

Related documents

Partial preview of the text

Download Relational Languages: Algebra, Tuple Calculus, Domain Calculus, and Datalog and more Summaries Algebra in PDF only on Docsity!

Unit 5

Formal Relational Languages

Text: Chapters 4 & 24

Relational Algebra (Ch. 4: 4.1—4.2)

Tuple Relational Calculus X

Domain Relational Calculus X

Datalog (Ch. 24)

Learning Goals

Given a database (a set of tables ) you will be

able to

Relational Algebra (RA)

 Operations can be composed. (Algebra is “closed”.)

Datalog (Lite)

 Will see Datalog (full) later.

 Rule-based: head  body.

 head is of the form 𝑝 𝑋 1 , … , 𝑋𝑛. Here, 𝑋𝑖 are

variables. Think of 𝑝(… ) as a “collector” of

answers to the query expressed by the body.

 body is a conjunction (i.e., AND) of atoms of

two kinds:

 Variables are bound to values appearing in the

DB when query is evaluated.

 Constants can be numerical, string, etc.

Selection

 Notation:  p(r)

 p is called the selection predicate

 p ( r ) = { t | t  r and p(t) }

Selection Example 1

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

Selection Example 3

 Find customers in Cust with rating less than 9

and salary more than 80.

Cust

Projection

 Notation:

where 𝐴 1 , … , 𝐴𝑘 are attributes (the projection

list) and 𝑟 is a relation.

 The result = relation over the k attributes

𝐴 1 , 𝐴 2 , … , 𝐴𝑘 obtained from 𝑟 by erasing the

columns that are not listed and eliminating

duplicate rows.

 Remember: relations are sets!

Projection Example 2

( )

( Cust )

( ))?

𝑎𝑛𝑠 𝐶, 𝑅 ← 𝑐𝑢𝑠𝑡 𝐼, 𝐶, 𝑅, 𝑆 , 𝑅 > 7.^1

Union, Intersection, Set-

Difference

 Notation: r  s r  s r – s

r  s = {t | t  r or t  s}

r  s ={ t | t  r and t  s }

r – s = {t | t  r and t  s}

1. r, s must have the same arity (same number of

2. The attribute domains must be compatible

(e.g., 2nd column of r has same domain of values

as the 2nd column of s)

Cartesian (or Cross)-Product

 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 r and s have common attributes, they must

be renamed in the result.

Cartesian-Product Example 2

Rename

 x (E)

returns the expression E under the name X

 If 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 A 1 , A2, …., An.

  x (B1→ A1, …, Bk→ Ak) (E)