A maior rede de estudos do Brasil

Grátis
Ch3-Relational-Model

Pré-visualização | Página 3 de 4

Properties s
Select is distributive over the binary Boolean operations, that is,
s(A = a)(r g s) ≡ s(A = a) (r) g s(A = a) (s); where g = È, Ç and r and s are relations over R
Proof: We want to prove s(A = a)(r Ç s) ≡ s(A = a)(r) Ç s(A = a)(s) 
s(A = a)(r Ç s) 
= s(A = a) ({t|t Î r and t Î s})
= {t’ Î ({t|t Î r and t Î s}) t'(A) = a}
= {t’’|t’’Î r and t’’(A) = a} Ç { t|t Î s and t(A) = a} 
= s (A= a) ({t’’|t’’Î r}) Ç s (A= a) ({t|t Îs}) 
= s (A= a) (r) Ç s (A= a) (s)
 CS470: Introduction to Database Systems	 V. Kumar
46
Relational Algebra Operations
Example:
s(FN=‘Susan’)(Student Ç Prof) = ? 
s(FN=‘Susan’)(Student) Ç s(FN=‘Susan’)(Prof) =?
 CS470: Introduction to Database Systems	 V. Kumar
47
Relational Algebra Operations
Projection P
A unary (monadic) operator on a relation r that creates a new relation r’ that is a vertical subset of the r
Let X be a subset of the attributes of R(A1, A2, …, An). Let X be (A1, A3, A5). The result of the projection of r(R) onto X is a new relation r'(X), which obtained by extracting the columns (attributes) from R which are in X, i.e., A1, A2, and A5. At the end of this process the relation r'(X) may contain duplicate tuples, which are removed by PROJECT operation. Also known as duplicate elimination.
 CS470: Introduction to Database Systems	 V. Kumar
48
Relational Algebra Operations
Format of P
P <list of attributes of the operand relation separated by ","> (r)
Example
P Age(Emp)  Result: 	Age
 			21
 			22
 			19
P Age,SSN (Emp)  Result: 	Age	SSN
 				21	123456789
 				22	113456789
 				19	122456789
 CS470: Introduction to Database Systems	 V. Kumar
49
Relational Algebra Operations
 CS470: Introduction to Database Systems	 V. Kumar
50
Relational Algebra Operations
 CS470: Introduction to Database Systems	 V. Kumar
51
Relational Algebra Operations
Properties of P
If two P s are applied on a relation in a row, then the latter subsumes the former
Proof
If YÎ XÎ R then P Y(P X (r)) = P Y(r). Similarly, for a string of P s only the outermost P counts. If X1 Î X2 Î ... Î Xm Î R then P x1(P x2 ( . . . (P xm(r)) . . .)) = P x1(r).
Point to remember: YÎ XÎ R or X1 Î X2 Î ... Î Xm Î R 
 CS470: Introduction to Database Systems	 V. Kumar
52
Relational Algebra Operations
Properties of P
P commutes with s when the attribute(s) in the predicate(s) of are among the attributes in the set onto which the P is being taken. That is if AÎ X, X Í R and r is a relation on R, then P X (s(A = a)(r)) = s(A = a)(P X (r))
Proof
P X(s(A = a) (r))	= P X ({t Î r|t(A) = a})
	=	{t'(X)|t' Î {t Î r|t(A) = a}}
	=	{t(X)|t Î r and t(A) = a}
	=	{t(X) t Î r | t(A) = a}
	=	{t Î r | t(A) = a and t(X) |t Î r }
	= 	s(A = a)({t(X)|t Î r})
	= 	s(A = a)(P X(r))
 CS470: Introduction to Database Systems	 V. Kumar
53
SELECT with PROJECTION and RENAME
 CS470: Introduction to Database Systems	 V. Kumar
54
Boolean Operations
We will use the following relations in the discussion of these operators.
	r(A	B	C)	s(A	B	C)
	a1	b1	c1	a1	b2	c1
	a1	b2	c1	a2	b2	c1
	a2	b1	c2	a2	b2	c2
Union compatibility rule: two relations are union compatible iff they have the same degree and the domain of corresponding attributes are the same, but their names may be different (synonyms)
2 relations R(A1, A2…An) and S(B1, B2…Bn) are union compatible if they have same degree n and if dom(Ai) = dom (Bi) for 1<=i<=n.
Boolean operators require that operand relations must be union compatible.
 CS470: Introduction to Database Systems	 V. Kumar
55
Boolean Operations
Intersection Ç
A binary operator. It creates a new relation from the two (operand) relations. The new relation contains the tuples, which are common in both the operand relations.
Example
r Ç s = r(A	B	C)	s(A	B	C)
	a1	b1	c1		a1	b2	c1
	a1	b2	c1	Ç 	a2	b2	c1
	a2	b1	c2		a2	b2	c2
= q(A	B	C) 
	a1	b2	c1
Class task: Prove that 	r Ç s = r - (r - s)
 CS470: Introduction to Database Systems	 V. Kumar
56
Boolean Operations
Union È
A binary operator. It creates a new relation from the two (operand) relations that contains the common and non-common tuples of the operand relations.
Example
r È s = r(A	B	C)	s(A	B	C) = q(A	B	C) 
	a1	b1	c1		a1	b2	c1	a1	b1	c1
	a1	b2	c1	 È 	a2	b2	c1	a1	b2	c1
	a2	b1	c2		a2	b2	c2	a2	b1	c2
	a2	b2	c1
	a2	b2	c2
	
	
 CS470: Introduction to Database Systems	 V. Kumar
57
Boolean Operations
Difference -
A binary operator. It creates a new relation from the two (operand) relations. The new relation contains the tuples, which are in the left hand relation but not in the right hand relation. Note that r - s ¹ s - r
Example
r - s = r(A	B	C)	s(A	B	C) = q(A	B	C) 
	a1	b1	c1		a1	b2	c1	a1	b1	c1
	a1	b2	c1	 - 	a2	b2	c1	a2	b1	c2
	a2	b1	c2		a2	b2	c2	
Class task. Prove that r - s ¹ s - r
	
	
 CS470: Introduction to Database Systems	 V. Kumar
58
Boolean Operations
 CS470: Introduction to Database Systems	 V. Kumar
59
Boolean Operations
 CS470: Introduction to Database Systems	 V. Kumar
60
Boolean Operations
 CS470: Introduction to Database Systems	 V. Kumar
61
Boolean Operations
 CS470: Introduction to Database Systems	 V. Kumar
62
Boolean Operations
 CS470: Introduction to Database Systems	 V. Kumar
63
Relational Algebra Operations-Set Theory
CARTESIAN PRODUCT
 CS470: Introduction to Database Systems	 V. Kumar
64
Relational Algebra Operations-Set Theory
CARTESIAN PRODUCT
 CS470: Introduction to Database Systems	 V. Kumar
65
Relational Algebra Operations-Set Theory
CARTESIAN PRODUCT
 CS470: Introduction to Database Systems	 V. Kumar
66
Relational Algebra Operations-Set Theory
CARTESIAN PRODUCT
 CS470: Introduction to Database Systems	 V. Kumar
67
Join Operations
Join
A Join combines two relations on a common attribute. In order to combine two relations there must at least be one attribute with common domain between both the relations but their names may or may not be the same. For example, one database may call income as salary and other as monthly income, however, their domains are the same. The result of a join is a new relation, which contains those tuples that satisfy the join condition
	
	
 CS470: Introduction to Database Systems	 V. Kumar
68
Join Operations
 CS470: Introduction to Database Systems	 V. Kumar
69
Join Operations
 CS470: Introduction to Database Systems	 V. Kumar
70
Join Operations
Is this what we wanted? Name of Manager? How to get?
 CS470: Introduction to Database Systems	 V. Kumar
71
Join Operations
 CS470: Introduction to Database Systems	 V. Kumar
72
Join Operations
Join types
There are three types of join
Equijoin 
Natural join (*)
Theta join ()
 CS470: Introduction to Database Systems	 V. Kumar
73
Join Operations
 CS470: Introduction to Database Systems	 V. Kumar
74
Join Operations
 CS470: Introduction to Database Systems	 V. Kumar
75
Join Operations
Equijoin steps 
Get cross product of two relations
Apply the join condition on the result of the cross product
Example
Get cross product.
r (A	B	C)	s (M	B)
	a1	b1	c1	m1	b1
	a2	b2	c2	m2	b2
	r  s = A	B	C	M	B
		a1	b1	c1	m1	b1
		a1	b1	c1	m2	b2
		a2	b2	c2	m1	b1
		a2	b2	c2	m2	b2
Common domain
attribute = B
 CS470: Introduction