Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
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
An introduction to negation in Query By Example (QBE) and Datalog, two important database query languages. It covers the use of negation in QBE through the Condition Box and the Result Relation, and discusses the syntax and semantics of Datalog rules and their instantiation. The document also touches upon the concept of safety in Datalog and the use of recursion.
Typology: Essays (university)
1 / 36
This page cannot be seen from the preview
Don't miss anything!
Database System Concepts 5.1 ©Silberschatz, Korth and Sudarshan
Database System Concepts 5.2 ©Silberschatz, Korth and Sudarshan
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 5.3 ©Silberschatz, Korth and Sudarshan
A graphical query language which is based (roughly) on the domain relational calculus Two dimensional syntax – system creates templates of relations that are requested by users Queries are expressed “by example”
Database System Concepts 5.4 ©Silberschatz, Korth and Sudarshan
Database System Concepts 5.7 ©Silberschatz, Korth and Sudarshan
Display full details of all loans Method 1:
Method 2: Shorthand notation
P._x P._y P._z
Database System Concepts 5.8 ©Silberschatz, Korth and Sudarshan
Find names of all branches that are not located in Brooklyn
Find the loan number of all loans with a loan amount of more than $
Database System Concepts 5.9 ©Silberschatz, Korth and Sudarshan
Find the loan numbers of all loans made jointly to Smith and Jones.
Find all customers who live in the same city as Jones
Database System Concepts 5.10 ©Silberschatz, Korth and Sudarshan
Find the names of all customers who have a loan from the Perryridge branch.
Database System Concepts 5.13 ©Silberschatz, Korth and Sudarshan
Find all customers who have at least two accounts.
¬ means “not equal to”
Database System Concepts 5.14 ©Silberschatz, Korth and Sudarshan
Allows the expression of constraints on domain variables that are either inconvenient or impossible to express within the skeleton tables. Complex conditions can be used in condition boxes E.g. Find the loan numbers of all loans made to Smith, to Jones, or to both jointly
Database System Concepts 5.15 ©Silberschatz, Korth and Sudarshan
QBE supports an interesting syntax for expressing alternative values
Database System Concepts 5.16 ©Silberschatz, Korth and Sudarshan
Find all account numbers with a balance between $1,300 and $1,
Find all account numbers with a balance between $1,300 and $2, but not exactly $1,500.
Database System Concepts 5.19 ©Silberschatz, Korth and Sudarshan
The resulting query is:
Database System Concepts 5.20 ©Silberschatz, Korth and Sudarshan
AO = ascending order; DO = descending order. E.g. list in ascending alphabetical order all customers who have an account at the bank
When sorting on multiple attributes, the sorting order is specified by including with each sort operator (AO or DO) an integer surrounded by parentheses. E.g. List all account numbers at the Perryridge branch in ascending alphabetic order with their respective account balances in descending order.
Database System Concepts 5.21 ©Silberschatz, Korth and Sudarshan
The aggregate operators are AVG, MAX, MIN, SUM, and CNT The above operators must be postfixed with “ALL” (e.g., SUM.ALL.or AVG.ALL._x) to ensure that duplicates are not eliminated. E.g. Find the total balance of all the accounts maintained at the Perryridge branch.
Database System Concepts 5.22 ©Silberschatz, Korth and Sudarshan
UNQ is used to specify that we want to eliminate duplicates Find the total number of customers having an account at the bank.
Database System Concepts 5.25 ©Silberschatz, Korth and Sudarshan
Database System Concepts 5.26 ©Silberschatz, Korth and Sudarshan
Deletion of tuples from a relation is expressed by use of a D. command. In the case where we delete information in only some of the columns, null values, specified by – , are inserted. Delete customer Smith
Delete the branch-city value of the branch whose name is “Perryridge”.
Database System Concepts 5.27 ©Silberschatz, Korth and Sudarshan
Delete all loans with a loan amount between $1300 and $1500. For consistency, we have to delete information from loan and borrower tables
Database System Concepts 5.28 ©Silberschatz, Korth and Sudarshan
Delete all accounts at branches located in Brooklyn.
Database System Concepts 5.31 ©Silberschatz, Korth and Sudarshan
Use the U. operator to change a value in a tuple without changing all values in the tuple. QBE does not allow users to update the primary key fields. Update the asset value of the Perryridge branch to $10,000,000.
Increase all balances by 5 percent.
Database System Concepts 5.32 ©Silberschatz, Korth and Sudarshan
Microsoft Access supports a variant of QBE called Graphical Query By Example (GQBE) 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
Database System Concepts 5.33 ©Silberschatz, Korth and Sudarshan
Example query: Find the customer-name , account-number and balance for all accounts at the Perryridge branch
Database System Concepts 5.34 ©Silberschatz, Korth and Sudarshan
Find the name, street and city of all customers who have more than one account at the bank
Database System Concepts 5.37 ©Silberschatz, Korth and Sudarshan
Prolog-like logic-based language that allows recursive queries; based on first-order logic. A Datalog program consists of a set of rules that define views. Example: define a view relation v1 containing account numbers and balances for accounts at the Perryridge branch with a balance of over $700. v1(A , B) :– account(A , “ Perryridge”, B), B > 700. Retrieve the balance of account number “A-217” in the view relation v1. ? v1(“ A-217”, B). To find account number and balance of all accounts in v1 that have a balance greater than 800 ? v1(A,B), B > 800
Database System Concepts 5.38 ©Silberschatz, Korth and Sudarshan
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 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. Example: interest-rate(A , 5 ) :– account(A, N, B), B < 10000 interest-rate(A , 6 ) :– account(A, N, B), B >= 10000
Database System Concepts 5.39 ©Silberschatz, Korth and Sudarshan
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). 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
Database System Concepts 5.40 ©Silberschatz, Korth and Sudarshan
Datalog rules use a positional notation, which is convenient for relations with a small number of attributes 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.
