ch05_c.ppt

Chapter 5: Other Relational Languages Query-by-Example (QBE) Datalog 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 QBE — Basic Structure 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” QBE Skeleton Tables (Cont.) Queries on One Relation Find all loan numbers at the Perryridge branch. Queries on One Relation (Cont.) Display full details of all loans Method 1: Method 2: Shorthand notation Queries on One Relation (Cont.) Find names of all branches that are not located in Brooklyn Queries on One Relation (Cont.) Find the loan numbers of all loans made jointly to Smith and Jones. Queries on Several Relations Find the names of all customers who have a loan from the Perryridge branch. Queries on Several Relations (Cont.) Find the names of all customers who have both an account and a loan at the bank. Negation in QBE Find the names of all customers who have an account at the bank, but do not have a loan from the bank. Negation in QBE (Cont.) Find all customers who have at least two accounts. The Condition Box 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 Condition Box (Cont.) QBE supports an interesting syntax for expressing alternative values Condition Box (Cont.) Find all account numbers with a balance between $1,300 and $1,500 Find all account numbers with a balance between $1,300 and $2,000 but not exactly $1,500. Condition Box (Cont.) Find all branches that have assets greater than those of at least one branch