# KBS Architecture

Document Sample

```					A Formal Definition of Theta-Join
A Formal Definition of Natural Join
A Formal Definition of Division: I
A Formal Definition of Division: II
Commercial Query Languages

• Query-by-Example (QBE): based on domain
relational calculus
• Quel: based on tuple relational calculus
• SQL: based on a combination of relational algebra
and relational calculus constructs.
– SQL=Structured Query Language (previous
name: Sequel)
– Qoriginally introduced as the query language for
the DBMS System R.
SQL Expressions: I

A typical SQL expression has the form:
select A1, A2, …., An
from r1, r2, …, rm
where P

• A1,…, An are attributes
• r1, …, rm are relations
• P is a predicate
SQL Expressions: II

• The select clause corresponds to the projection
operation in relational algebra. It is used to list the
attributes desired in the result of a query.
• The from is a list of relations to be scanned in the
execution of the expression
• The where clause corresponds to the selection
predicate of relational algebra. It consists of a
predicate involving attributes of the relations that
appear in the from clause.
SQL Expressions: III

A relational algebra expression equivalent to the
general form of an SQL query presented above:
Duplicate Elimination in SQL

• Unless explicitly required, SQL does not eliminate
duplicate tuples.
• Duplicate elimination can be enforced by placing the
keyword distinct after select.
SQL Example: I

Find the names of all branches in the DEPOSIT relation:

select branch-name
from DEPOSIT
SQL Example: II

Find the set of all distinct names of branches in the
DEPOSIT relation:

select distinct branch-name
from DEPOSIT
SQL Example: III

Find the names of all customers who have an account at
the Wollongong branch:

select customer-name
from DEPOSIT
where branch-name=“Wollongong”
SQL Set-theoretic Operations

• union
• intersect
• minus
SQL Example: IV

Find the names of all customers who have a loan from
the Wollongong branch:

select customer-name
from BORROW
where branch-name=“Wollongong”
SQL Example: V

Find the names of all customers who have a loan, an
account or both at the Wollongong branch:
(select customer-name
from DEPOSIT
where branch-name=“Wollongong”)
union
(select customer-name
from BORROW
where branch-name=“Wollongong”)
SQL Example: VI

Find the names of all customers who have both a loan
and an account at the Wollongong branch:
(select customer-name
from DEPOSIT
where branch-name=“Wollongong”)
intersect
(select customer-name
from BORROW
where branch-name=“Wollongong”)
SQL Example: VII

Find the names of all customers of the Wollongong
branch who have an account there but no loan:
(select customer-name
from DEPOSIT
where branch-name=“Wollongong”)
minus
(select customer-name
from BORROW
where branch-name=“Wollongong”)
SQL Example: VIII

Find all customers having a loan at some branch and
their city:
select customer.customer-name, city
from BORROW, CUSTOMER
where BORROW.customer-
name=CUSTOMER.customer-name
SQL Example: IX

Find all customers who have a loan at the Wollongong
branch and their city:
select customer.customer-name, city
from BORROW, CUSTOMER
where BORROW.customer-
name=CUSTOMER.customer-name and branch-
name=“Wollongong”
SQL Example: X

Find all customers who have both a loan and an account
at the Wollongong branch:
select customer-name
from BORROW
where branch-name=“Wollongong” and
customer-name in (select customer-name
from DEPOSIT
where branch-name=“Wollongong”)
SQL Example: XI

Find all customers who have both an account and a loan
at the Wollongong branch:
select customer-name
from BORROW
where branch-name=“Wollongong” and
<branch-name, customer-name> in
(select branch-name, customer-name
from DEPOSIT)
SQL Example: XII

Find all customers who have an account but do not
have a loan at the Wollongong branch:
select customer-name
from DEPOSIT
where branch-name=“Wollongong” and
customer-name not in (select customer-name
from BORROW
where branch-name=“Wollongong”)
SQL Example: XIII

Find all customers having a loan at some bank and their
city:
select T.customer-name, city
from BORROW S, CUSTOMER T
where S.customer-name=T.customer-name
SQL Example: XIV

Find all customers who have an account at some bank
at which “Jones” has an account:
select T.customer-name
from DEPOSIT S, DEPOSIT T
where S.customer-name=“Jones” and
S.branch-name=T.branch-name
SQL Example: XV

Find all customers who have an account at some bank
at which Jones has an account:
select customer-name
from DEPOSIT
where branch-name in
(select branch-name
from DEPOSIT
where customer-name=“Jones”)
SQL Example: XVI

Find all branches that have greater assets than some
branch located in Sydney

select T.branch-name
from BRANCH T, BRANCH S
where T.assets > S.assets and
S.branch-city=“Sydney”
SQL Example: XVII

Find all branches that have greater assets than some
branch located in Sydney

select branch-name
from BRANCH
where assets > any
(select assets
from BRANCH
where branch-city=“Sydney”)
SQL Example: XVIII

Find all branches that have greater assets than all
branches in Sydney

select branch-name
from BRANCH
where assets > all
(select assets
from BRANCH
where branch-city=“Sydney”)
SQL Example: XIX

Find all customers who have an account at all branches
located in Sydney

select customer-name
from DEPOSIT S
where (select branch-name
from DEPOSIT T
where S.customer-name=T.customer-name)
contains
(select branch-name
from BRANCH
SQL Example: XX

List all customers having a loan at the Wollongong
branch in alphabetical order

select customer-name
from BORROW
where branch-name=“Wollongong”
order by customer-name
SQL Functions

•   average: avg
•   minimum/maximum: min/max
•   total: sum
•   count: count
SQL Example: XXI

List the average account balances of branches for which
the average account balance is greater than \$1200

select branch-name, avg(balance)
from DEPOSIT
group by branch-name
having avg(balance) > 1200
SQL Example: XXII

Find the number of tuples in the CUSTOMER relation

select count(*)
from CUSTOMER
SQL Example: XXIII

Find all customers who have a deposit in the
Wollongong branch for whom no address is on file
select customer-name
from DEPOSIT
where branch-name=“Wollongong”
and 0=
(select count(*)
from CUSTOMER
where DEPOSIT.customer-
name=CUSTOMER.customer-name)
SQL Example: XXIV

Find all customers who have a deposit in the
Wollongong branch for whom no address is on file
select customer-name
from DEPOSIT
where branch-name=“Wollongong”
and not exists
(select *
from CUSTOMER
where DEPOSIT.customer-
name=CUSTOMER.customer-name)
Deletion in SQL

• Delete all loans with loan numbers between 1300 and
1500
delete BORROW
where loan-number>1300 and loan-number<1500
• Delete all accounts at branches located in Sydney
delete DEPOSIT
where branch-name in (select branch-name
from BRANCH
where branch-city=“Sydney”)
Insertion in SQL

• insert into DEPOSIT
values (“Sydney”, 9732, “Smith”, 1200)
• Provide all loan customers in the Epping branch with
a \$200 savings account
insert into DEPOSIT
select branch-name, loan-number, customer-name, 200
from BORROW
where branch-name=“Epping”
Updating in SQL

Update all accounts with balances over \$10,00 to receive
6% interest, while all other accounts receive 5%
interest

update DEPOSIT
set balance=balance*1.06
where balance > 10,000

update DEPOSIT
set balance = balance* 1.05
where balance <= 10,000
Views:I

• A view is any relation not part of the conceptual
model that is made available to a user as a virtual
relation.
• Views are useful enforcing security - a bank clerk
may be allowed to see the branch-name, loan-number
and customer-name attributes from the BORROW
relation, but not the loan-amount attribute.
• Views are also useful for creating personalized collections of
relations that are more suitable for a user’s intuition about the
database than the conceptual model - e.g., a marketing
employee may want to see customer-name and branch-name
values only from both DEPOSIT and BORROW.
Views:II

• A view name may appear in any place where a
relation name might appear in an SQL expression.
• View creation in SQL:
create view v as <query expression>
• create view LOAN-INFO as
select branch-name, loan-number, customer-name
from BORROW
Views: III

create view ALL-CUSTOMER as
(select branch-name, customer-name
from DEPOSIT)
union
(select branch-name, customer-name
from BORROW)

• Problem: How does one translate updates to views
into updates of actual relations ?
• If we executed this command:
insert into LOAN-INFO
values (“Perryridge”, 3, “Ruth”)
we may either:
» reject the insertion and return an error message
» insert a tuple (“Perryridge”, 3, “Ruth”, NULL)
into the BORROW relation.
Most systems take the second approach.

• All aggregation operators except count ignore tuples
with NULL values.
• Find all customers who appear in the BORROW
relation with a NULL value for balance:
select customer-name
from BORROW
where balance is null

create view BRANCH-CITY as
select branch-name, customer-city
from BORROW, CUSTOMER
where BORROW.customer-
name=CUSTOMER.customer-name

insert into BRANCH-CITY
values (“Brighton”, “Woodside”)

If we execute:
select *
from BRANCH-CITY
we will not get the tuple (“Brighton”, “Woodside”)
because all comparisons involving NULL are defined
to be false. Hence the where clause in the view
definition is never satisfied for the tuple we just
inserted into the BRANCH-CITY view.
In general: A modification is permitted through a view
only if the view is defined in terms of only one
relation in the actual database.

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 12 posted: 3/24/2012 language: English pages: 45
How are you planning on using Docstoc?