Download Database Systems: Tuple Relational Calculus & Datalog in CIS 560 at Kansas State Univ. and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Lecture 10 of 42
Friday, 15 September 2006
William H. Hsu Department of Computing and Information Sciences, KSU
KSOL course page: http://snipurl.com/va Course web site: http://www.kddresearch.org/Courses/Fall-2006/CIS Instructor home page: http://www.cis.ksu.edu/~bhsu
Reading for Next Class: Rest of Chapter 5, Silberschatz et al. , 5 th^ edition
Datalog
Notes: MP2, Review of Relational Calculi
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Other Relational Languages:
Review
Other Relational Languages:
Review
z Tuple Relational Calculus z Domain Relational Calculus z Query-by-Example (QBE) z Datalog
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Tuple Relational CalculusTuple Relational Calculus
z A nonprocedural query language, where each query is of the form { t | P ( t ) } z It is the set of all tuples t such that predicate P is true for t z t is a tuple variable , t [ A ] denotes the value of tuple t on attribute A z t ∈ r denotes that tuple t is in relation r z P is a formula similar to that of the predicate calculus
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Banking ExampleBanking Example
z branch ( branch_name, branch_city, assets ) z customer ( customer_name, customer_street, customer_city ) z account ( account_number, branch_name, balance ) z loan ( loan_number, branch_name, amount ) z depositor ( customer_name, account_number ) z borrower ( customer_name, loan_number )
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Domain Relational CalculusDomain Relational Calculus
z A nonprocedural query language equivalent in power to the tuple relational calculus z Each query is an expression of the form:
{ < x 1 , x 2 , …, x (^) n > | P ( x 1 , x 2 , …, x (^) n )}
x 1 , x 2 , …, xn represent domain variables P represents a formula similar to that of the predicate calculus
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Example Queries Example Queries
z Find the loan_number, branch_name, and amount for loans of over $
Find the names of all customers who have a loan from the Perryridge branch and the loan amount: f {< c, a > | ∃ l (< c, l > ∈ borrower ∧ ∃ b (< l, b, a > ∈ loan ∧ b = “Perryridge”))} f {< 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 $
{< l , b, a > | < l , b, a > ∈ loan ∧ a > 1200}
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Example Queries Example Queries
z 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”))}
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Safety of ExpressionsSafety of Expressions
The expression: { < x 1 , x 2 , …, x (^) n > | P ( x 1 , x 2 , …, x (^) n )}
is safe if all of the following hold:
- 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 ).
- For every “there exists” subformula of the form ∃ x ( P 1 ( x )), the subformula is true if and only if there is a value of x in dom ( P 1 ) such that P 1 ( x ) is true.
- For every “for all” subformula of the form ∀x ( P 1 ( x )), the subformula is true if and only if P 1 ( x ) is true for all values x from dom ( P 1 ).
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Example Queries Example Queries
z Each rule defines a set of tuples that a view relation must contain. E.g. v1 ( A , B ) :– account ( A , “ Perryridge”, B ) , B > 700 is read as for all A, B if ( A , “Perryridge”, B ) ∈ account and B > 700 then ( A, B ) ∈ v z The set of tuples in a view relation is then defined as the union of all the sets of tuples defined by the rules for the view relation. z Example: interest_rate ( A , 5) :– account ( A, N, B ) , B < 10000 interest_rate ( A , 6) :– account ( A, N, B ) , B >= 10000
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Negation in Datalog Negation in Datalog
z Define a view relation c that contains the names of all customers who have a deposit but no loan at the bank: c ( N ) :– depositor ( N, A ) , not is_borrower ( N ). is_borrower ( N ) :– borrower ( N,L ). z NOTE: using not borrower ( N, L ) in the first rule results in a different meaning, namely there is some loan L for which N is not a borrower. To prevent such confusion, we require all variables in negated “predicate” to also be present in non-negated predicates
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Named Attribute Notation Named Attribute Notation
z Datalog rules use a positional notation that is convenient for relations with a small number of attributes z It is easy to extend Datalog to support named attributes. E.g., v1 can be defined using named attributes as v1 ( account_number A , balance B ) :– account ( account_number A , branch_name “ Perryridge”, balance B ) , B > 700.
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Formal Syntax and Semantics of DatalogFormal Syntax and Semantics of Datalog
z We formally define the syntax and semantics (meaning) of Datalog programs, in the following steps
- We define the syntax of predicates, and then the syntax of rules
- We define the semantics of individual rules
- We define the semantics of non-recursive programs, based on a layering of rules
- It is possible to write rules that can generate an infinite number of tuples in the view relation. To prevent this, we define what rules are “safe”. Non-recursive programs containing only safe rules can only generate a finite number of answers.
- It is possible to write recursive programs whose meaning is unclear. We define what recursive programs are acceptable, and define their meaning.
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Layering of Rules Layering of Rules
z Define the interest on each account in Perryridge interest(A, l) :– perryridge_account ( A,B), interest_rate(A,R), l = B * R/ 100_. perryridge_account(A,B)_ :– account ( A, “Perryridge”, B). interest_rate (A, 5) :– account ( N, A, B), B < 10000. interest_ rate ( A, 6) :– account ( N , A, B), B >= 10000. z Layering of the view relations
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Layering Rules (Cont.) Layering Rules (Cont.)
z A relation is a layer 1 if all relations used in the bodies of rules defining it are stored in the database. z A relation is a layer 2 if all relations used in the bodies of rules defining it are either stored in the database, or are in layer 1. z A relation p is in layer i + 1 if it is not in layers 1, 2, ..., i all relations used in the bodies of rules defining a p are either stored in the database, or are in layers 1, 2, ..., i
Formally:
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Semantics of a ProgramSemantics of a Program
z Define I 0 = set of facts stored in the database. z Recursively define li+ 1 = li ∪ infer (ℜ i +1 , li ) z The set of facts in the view relations defined by the program (also called the semantics of the program) is given by the set of facts ln corresponding to the highest layer n.
Let the layers in a given program be 1, 2, ..., n. Let ℜ i denote the set of all rules defining view relations in layer i.
Note: Can instead define semantics using view expansion like in relational algebra, but above definition is better for handling extensions such as recursion.
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Safety Safety
z It is possible to write rules that generate an infinite number of answers. gt(X, Y) :– X > Y not_in_loan ( B, L ) :– not loan ( B, L ) To avoid this possibility Datalog rules must satisfy the following conditions. Every variable that appears in the head of the rule also appears in a non-arithmetic positive literal in the body of the rule. Ö This condition can be weakened in special cases based on the semantics of arithmetic predicates, for example to permit the rule p ( A ) :- q ( B ) , A = B + 1 Every variable appearing in a negative literal in the body of the rule also appears in some positive literal in the body of the rule.
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Example of Datalog-FixPoint IterationExample of Datalog-FixPoint Iteration
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
A More General ViewA More General View
z Create a view relation empl that contains every tuple ( X, Y ) such that X is directly or indirectly managed by Y. empl ( X, Y ) :– manager ( X, Y ). empl ( X, Y ) :– manager ( X, Z ) , empl ( Z, Y ) z Find the direct and indirect employees of Jones. ? empl (X, “Jones”). z Can define the view empl in another way too: empl ( X, Y ) :– manager ( X, Y ). empl ( X, Y ) :– empl (X, Z ), manager ( Z, Y ).
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
The Power of Recursion The Power of Recursion
z Recursive views make it possible to write queries, such as transitive closure queries, that cannot be written without recursion or iteration. Intuition: Without recursion, a non-recursive non-iterative program can perform only a fixed number of joins of manager with itself Ö This can give only a fixed number of levels of managers Ö Given a program we can construct a database with a greater number of levels of managers on which the program will not work
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Recursion in SQL Recursion in SQL
z Starting with SQL:1999, SQL permits recursive view definition z E.g. query to find all employee-manager pairs
with recursive empl ( emp , mgr ) as ( select emp , mgr from manager union select manager. emp , empl. mgr from manager , empl where manager. mgr = empl. emp ) select * from empl
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
QBE — Basic Structure QBE — Basic Structure
z A graphical query language which is based (roughly) on the domain relational calculus z Two dimensional syntax – system creates templates of relations that are requested by users z Queries are expressed “by example”
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
QBE Skeleton Tables for the Bank ExampleQBE Skeleton Tables for the Bank Example
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Microsoft Access QBEMicrosoft Access QBE
z Microsoft Access supports a variant of QBE called Graphical Query By Example (GQBE) z GQBE differs from QBE in the following ways Attributes of relations are listed vertically, one below the other, instead of horizontally Instead of using variables, lines (links) between attributes are used to specify that their values should be the same. Ö Links are added automatically on the basis of attribute name, and the user can then add or delete links Ö By default, a link specifies an inner join, but can be modified to specify outer joins. Conditions, values to be printed, as well as group by attributes are all specified together in a box called the design grid
Computing & Information Sciences CIS 560: Database System Concepts Friday, 15 Sep 2006 Kansas State University
Example query: Find the customer_name , account_number and balance for all accounts at the Perryridge branch
Example Query in
Microsoft Access QBE
Example Query in
Microsoft Access QBE
