Domain Relational Calculus A nonprocedural query language equivalent in power to the tuple relational calculus Each query is an expression of the form: {<X1,X2,,Xn>|P(X1,X2,n)} X1,X2,...Xn represent domain variables P represents a formula similar to that of the predicate calculus Database System Concepts-5th Edition,July 8,2005 5.12 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 5.12 ©Silberschatz, Korth and Sudarshan th Edition, July 8, 2005 Domain Relational Calculus A nonprocedural query language equivalent in power to the tuple relational calculus Each query is an expression of the form: { x1 , x2 , …, xn | P (x1 , x2 , …, xn )} x1 , x2 , …, xn represent domain variables P represents a formula similar to that of the predicate calculus
Example Queries Find the loan number,branch name,and amount for loans of over $1200 {kl,b,a>|<1,b,a>∈1oanΛa>1200} Find the names of all customers who have a loan of over $1200 {kc>|3l,b,a(kc,1>∈borrowerΛ<l,b,a>∈loan ^a>1200)} Find the names of all customers who have a loan from the Perryridge branch and the loan amount: {kc,a>|3I(kc,I>∈borrower∧3b(kl,b,a>∈loan b=“Perryridge")} {kc,a>|3I(kc,I>∈borrower<l,“Perryridge,a>∈loan)} Database System Concepts-5th Edition,July 8,2005 5.13 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 5.13 ©Silberschatz, Korth and Sudarshan th Edition, July 8, 2005 Example Queries Find the loan_number, branch_name, and amount for loans of over $1200 Find the names of all customers who have a loan from the Perryridge branch and the loan amount: { c, a | l ( c, l borrower b ( l, b, a loan b = “Perryridge”))} { c, a | l ( c, l borrower l, “ Perryridge”, a loan)} { c | l, b, a ( c, l borrower l, b, a loan a > 1200)} Find the names of all customers who have a loan of over $1200 { l, b, a | l, b, a loan a > 1200}
Example Queries Find the names of all customers having a loan,an account,or both at the Perryridge branch: [c>(<c,I>E borrower A3b,a(kl,b,a>∈loan∧b=“Perryridge") v3a(kc,a>∈depositor A3b,n(ka,b,n>∈account∧b=“Perryridge")} Find the names of all customers who have an account at all branches located in Brooklyn: [<c>3s,n (<c,s,n>E customer) 廿X,yz(kX,yz>∈branch ^y=“Brooklyn"→ 3a,b(<X,yz>∈account∧<c,a>∈depositor)} Database System Concepts-5th Edition,July 8,2005 5.14 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 5.14 ©Silberschatz, Korth and Sudarshan th Edition, July 8, 2005 Example Queries Find the names of all customers having a loan, an account, or both at the Perryridge branch: { c | s,n ( c, s, n customer) x,y,z ( x, y, z branch y = “Brooklyn”) a,b ( x, y, z account c,a depositor)} Find the names of all customers who have an account at all branches located in Brooklyn: { c | l ( c, l borrower b,a ( l, b, a loan b = “Perryridge”)) a ( c, a depositor b,n ( a, b, n account b = “Perryridge”))}
Safety of Expressions The expression: {<X1,X2,,Xn>|P(X1,X2,,Xn)} is safe if all of the following hold: 1.All values that appear in tuples of the expression are values from dom(P)(that is,the values appear either in P or in a tuple of a relation mentioned in P). 2.For every "there exists"subformula of the form 3x(P(x ))the subformula is true if and only if there is a value of x in dom(P1) such that P(x)is true. 3.For every "for all"subformula of the form Vx (P1(x)),the subformula is true if and only if P(x)is true for all values x from dom(P1). Database System Concepts-5th Edition,July 8,2005 5.15 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 5.15 ©Silberschatz, Korth and Sudarshan th Edition, July 8, 2005 Safety of Expressions The expression: { x1 , x2 , …, xn | P (x1 , x2 , …, xn )} is safe if all of the following hold: 1. All values that appear in tuples of the expression are values from dom (P ) (that is, the values appear either in P or in a tuple of a relation mentioned in P ). 2. For every “there exists” subformula of the form x (P1 (x )), the subformula is true if and only if there is a value of x in dom (P1 ) such that P1 (x ) is true. 3. For every “for all” subformula of the form x (P1 (x )), the subformula is true if and only if P1 (x ) is true for all values x from dom (P1 )
Query-by-Example(QBE) Basic Structure Queries on One Relation Queries on Several Relations The Condition Box The Result Relation Ordering the Display of Tuples Aggregate Operations Modification of the Database Database System Concepts-5th Edition,July 8,2005 5.16 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 5.16 ©Silberschatz, Korth and Sudarshan th Edition, July 8, 2005 Query-by-Example (QBE) Basic Structure Queries on One Relation Queries on Several Relations The Condition Box The Result Relation Ordering the Display of Tuples Aggregate Operations Modification of the Database