BRANCH-SCHEME%20(BRANCH-NAME,%20ASSETS,%20BRANCH-CITY)

1
BRANCH-SCHEME (BRANCH-NAME, ASSETS,
BRANCH-CITY)   CUSTOMER (CUSTOMER-NAME, STREET,
CUSTOMER-CITY).   DEPOSIT (BRANCH-NAME,
ACCOUNT-NUMBER, CUSTOMER-NAME, BALANCE)   BORROW-S
CHEME (BRANCH-NAME, LOAN-NUMBER, CUSTOMER-NAME,
AMOUNT)   CLIENT (CLIENT-NAME, BANKER-NAME)  
2
SELECT (?) Q. SELECT THOSE TUPLES OF THE
BORROW RELATION WHERE BRANCH IS
PERRYRIDGE. ?BRANCH-NAME PERRYRIDGE(BORROW)
  Q. FIND ALL TUPLES IN WHICH AMOUNT BORROWED IS
LESS THAN 1200. ?AMOUNT lt 1200 (BORROW)   WE
CAN USE THE FOLLOWING OPERATIONS , ?, ?, ?, ?,
?. ALSO, WE DENOTE AND BY ?, OR BY ?. Q. FIND
THOSE TUPLES PERTAINING TO LOANS OF MORE THAN
1200 MADE BY THE PERRYRIDGE BRANCH.
?BRANCH-NAME PERRYRIDGE ? AMOUNT gt 1200
(BORROW )
3
PROJECT (?)   Q. SHOW CUSTOMERS AND THEIR
BORROWING BRANCH-NAME. ?BRANCH-NAME,
CUSTOMER-NAME(BORROW)   Q. FIND ALL THOSE
CUSTOMERS WHO HAVE THE SAME NAME AS THEIR
PERSONAL BANKER. ?CUSTOMER-NAME?CUSTOMER-NAME
BANKER-NAME (CLIENT).      
4
  CARTESIAN PRODUCT (?) Q. FIND ALL CLIENTS OF
BANKER JOHNSON AND THE CITY IN WHICH THEY LIVE.
?BANKER-NAMEJOHNSON CLIENT?CUSTOMER) NOW
CLIENT.CUTOMER-NAME COLUMN CONTAINS ONLY
CUSTOMERS of Banker JOHNSON.   TO FIND ALL THE
CLIENTS OF BANKER JOHNSON, WE WRITE ?CLIENT.CUSTOM
ER-NAME CUSTOMER.CUSTOMER- NAME (
?BANKER-NAMEJOHNSON (CLIENT?CUSTOMER))
5

• NOW WE WANT ONLY CUSTOMER-NAME AND CUSTOMER-CITY,
  WE WRITE
• ?CLIENT.CUSTOMER-NAME,CUSTOMER-CITY(?CLIENT.CUSTOM
  ER-NAME
• CUSTOMER.CUSTOMER-NAME ( ?BANKER-NAME
• JOHNSON (CLIENTCUSTOMER)))

6
RENAME OPERATION (?) IN CASE A SAME RELATION
APPEARS TWICE, WE RENAME ONE OF THE RELATION.
Q. FIND THE NAMES OF ALL CUSTOMERS WHO LIVE ON
THE SAME STREET AND IN THE SAME CITY AS SMITH.
TO OBTAIN, THE CITY AND STREET OF SMITH , WE
WRITE ?SREET,CUSTOMER-CITY(?CUSTOMER-NAME
SMITH (CUSTOMER)) TO FIND OTHER CUSTOMERS
WITH THIS STREET AND CITY, WE MUST REFERENCE THE
CUSTOMER RELATION A SECOND TIME ?P(CUSTOMER ?
?SREET,CUSTOMER-CITY (?CUSTOMER-NAME SMITH
(CUSTOMER))    
7
NOW TO SPECIFY WHICH STREET VALUES, WE CAN NOT
USE CUSTOMER.SREET SINCE BOTH VALUES ARE TAKEN
FROM CUSTOMER. TO SOLVE THE PROBLEM, USE RENAME
OPERATOR ?. SO RENAME CUSTOMER AS CUSTOMER2.
?CUSTOMER-NAME?(CUSTOMER2.STREET
CUSTOMER.STREET ? CUSTOMER2.CITY
CUSTOMER.CUSTOMER-CITY(CUSTOMER ?
(?SREET,CUSTOMER-CITY (?CUSTOMER-NAME SMITH
(?CUSTOMER2 (CUSTOMER))))))
8
UNION OPERATION (?) Q. FIND ALL CUSTOMERS OF
THE PERRYRIDGE BRANCH. NOTE THAT THESE CUSTOMERS
INCLUDE EVERYONE WHO HAS LOAN, OR ACCOUNT OR
BOTH. NOW TO FIND THE CUSTOMERS WITH A LOAN AT
PERRYRIDGE BRANCH ?CUSTOMER-NAME (?BRANCH-NAME
PERRYRIDGE (BORROW)) NOW TO FIND THE CUSTOMERS
WITH AN ACCOUNT AT PERRYRIDGE BRANCH ,
?CUSTOMER-NAME (?BRANCH-NAME PERRYRIDGE
(DEPOSIT)) TO FIND BOTH, WE HAVE TO TAKE A UNION
OF BOTH ?CUSTOMER-NAME (?BRANCH-NAME
PERRYRIDGE (BORROW)) ? ?CUSTOMER-NAME
(?BRANCH-NAME PERRYRIDGE (DEPOSIT))
9
SET DIFFERENCE OPERATION (?) Q. FIND ALL
CUSTOMERS OF THE PERRYRIDGE BRANCH WHO HAVE AN
ACCOUNT THERE BUT NOT A LOAN. ?CUSTOMER-NAME
(?BRANCH-NAME PERRYRIDGE (DEPOSIT)) ?
?CUSTOMER-NAME (?BRANCH-NAME PERRYRIDGE
(BORROW))    
10
Q. FIND THE LARGEST ACCOUNT BALANCE IN THE BANK.
FIRST, FIND A TEMPORARY RELATION CONTAINING
THOSE RELATIONS WHICH ARE NOT THE LARGEST. THEN
TAKE THE SET DIFFERENCE BETWEEN THE RELATION
DEPOSIT AND THE TEMPORARY RELATION COMPUTED. THE
TEMPORARY RELATION IS COMPUTED BY
?DEPOSIT.BALANCE(?(DEPOSIT.BALANCE lt D.BALANCE
(DEPOSIT ? ?D (DEPOSIT))) THIS EXPRESSION GIVES
ALL THE BALANCES EXCEPT THE LARGETS ONE.
  NOW ?BALANCE(DEPOSIT) ? ?DEPOSIT.BALANCE
(?(DEPOSIT.BALANCE lt D.BALANCE (DEPOSIT ? ?D
(DEPOSIT)))
11
SET INTERSECTION (?) Q. FIND ALL CUSTOMERS WITH
BOTH A LOAN AND AN ACCOUNT AT THE PERRYRIDGE
BRANCH. ?CUSTOMER-NAME (?BRANCH-NAME
PERRYRIDGE (DEPOSIT)) ? ?CUSTOMER-NAME
(?BRANCH-NAME PERRYRIDGE (BORROW))   NOTE
THAT SET INTERSECTION CAN BE WRITTEN AS R ? S R
- (R - S)
12
NATURAL JOIN ( ??? ) Q. FIND ALL CUSTOMERS WHO
HAVE A LOAN AT THE BANK AND THE CITIES IN WHICH
THEY LIVE. ?CUSTOMER-NAME, CUSTOMER-CITY (?CLIENT
.CUSTOMER-NAME CUSTOMER.CUSTOMER-NAME (BORROW?
CUSTOMER) ?CUSTOMER-NAME, CUSTOMER-CITY(BORROW???
CUSTOMER)   Q. FIND THE ASSETS AND NAME OF ALL
BRANCHES WHICH HAVE DEPOSITORS LIVING IN
STAMFORD. ?BRANCH-NAME, ASSETS
(?CUSTOMER-CITYSTAMFORD (CUSTOMER ??? DEPOSIT
??? BRANCH)   NOTE THAT NATURAL JOIN IS
ASSOCIATIVE.
13
Q. FIND ALL CUSTOMERS WHO HAVE BOTH AN ACCOUNT
AND A LOAN AT THE PERRYRIDGE BRANCH.
?CUSTOMER-NAME(?BRANCH-NAMEPERRYRIDGE
(BORROW???DEPOSIT)
14
DIVISION (?) QUERIES THAT INCLUDE THE PHRASE
FOR ALL. Q. FIND ALL CUSTOMERS WHO HAVE AN
ACCOUNT AT ALL BRANCHES LOCATED IN BROOKLYN. TO
OBTAIN ALL BRANCHES IN BROOKLYN R
?BRANCH-NAME(?BRANCH-CITYBROOKLYN
(BRANCH)) TO OBTAIN ALL CUSTOMER-NAME,
BRANCH-NAME PAIRS FOR WHICH THE CUSTOMER HAS AN
ACCOUNT AT A BRANCH S ?CUSTOMER-NAME,BRANCH-NA
ME (DEPOSIT) NOW TO FIND CUSTOMERS WHO APPEAR IN
S WITH EVERY BRANCH NAME IN R. ?CUSTOMER-NAME,BRA
NCH-NAME (DEPOSIT) ? ?BRANCH-NAME(?BRANCH-CITYB
ROOKLYN (BRANCH))
15
Some more queries

• Retrieve the name and address of all the
  employees who work in research department

16

• Find the names of employees who work on all the
  projects controlled by the department number 5

17

• List the names of employees with two or more
  dependents

18

• Retrieve the names of employees who have no
  dependents

19

• List names of managers who have atleast one
  dependent

20
MODIFYING THE DATABASE DELETION Q. DELETE ALL
OF SMITHS ACCOUNTS DEPOSIT ? DEPOSIT -
(?CUSTOMER-NAME SMITH (DEPOSIT) Q. DELETE
ALL LOANS WITH LOAN NUMBERS BETWEEN 1300 AND
1450 BORROW ? BORROW - (?LOAN-NUMBER ? 1300 ?
LOAN-NUMBER ? 1450 (BORROW) Q. DELETE ALL
ACCOUNTS AT BRANCHES LOCATED IN NEEDHAM. R ?
?BRANCH-CITYNEEDHAM (DEPOSIT ??? BRANCH) S ? ?
BRANCH-NAME, ACCOUNT-NUMBER, CUSTOMER-NAME,
BALANCE(R) DEPOSIT ? DEPOSIT - S
21
INSERTION TO INSERT DATA INTO RELATION Q.
INSERT THAT SMITH HAS 1200 IN ACCOUNT 9732 AT
THE PERRYRIDGE BRANCH. DEPOSIT ? DEPOSIT ?
?(PERRYRIDGE,9732,SMITH,1200) Q. PROVIDE
TO ALL LOAN CUSTOMERS IN THE PERRYRIDGE BRANCH
WITH A 200 IN SAVING ACCOUNT. LET THE LOAN
NUMBER SERVE AS THE ACCOUNT NUMBER. R ?
(?BRANCH-NAME PERRYRIDGE (BORROW)) S ? ?
BRANCH-NAME, LOAN-NUMBER, CUSTOMER-NAME(R) DEPOSIT
? DEPOSIT ? (R ? (200)
22
UPDATING UPDATE OPERATOR ? HAS THE FOLLOWING FORM
?A?E(R) WHERE R IS THE NAME OF THE
RELATION WITH ATTRIBUTE A, WHICH IS ASSIGNED THE
VALUE OF THE EXPRESSION E.   Q. TO INCREASE THE
BALANCE BY 5. ?BALANCE?BALANCE1.05
(DEPOSIT) Q. TO INCREASE THE BALANCE BY 5 FOR
BALANCES OVER 1200, WHILE OTHERS BY 6.
?BALANCE?BALANCE1.05(?BALANCE gt 1200
(DEPOSIT)) ?BALANCE?BALANCE1.06(?BALANCE ? 1200
(DEPOSIT))
23
VIEWS A VIEW IS DEFINED USING A CREATE VIEW
STATEMENT. THE FORM OS THIS STATEMENT IS CREATE
VIEW V AS ltQUERY EXPRESSIONgt WHERE V IS THE
VIEW-NAME.     Q. CREATE A VIEW OF BRANCHES AND
THERE CUSTOMERS. CREATE VIEW all-customers AS
?CUSTOMER-NAME,BRANCH-NAME (DEPOSIT) ?
?BRANCH-NAME, CUSTOMER-NAME (BORROW)
24

• ONCE A VIEW IS DEFINED, IT CAN BE USED AS ANY
  OTHER RELATION (TABLE). THAT IS, WE CAN USE VIEW
  all-customers IN ANY QUERY.
• UPDATES THROUGH VIEWS AND NULL VALUES
• ANY MODIFICATION IN THE VIEW TABLE MUST BE
  REFLECTED IN THE ACTUAL RELATIONS.

25
Q. CONSTRUCT A VIEW OF LOAN DATA FROM THE BORROW
RELATION EXCEPT LOAN-AMOUNT. CREATE VIEW
loan-info AS ?BRANCH-NAME, LOAN-NUMBER,
CUSTOMER-NAME (BORROW) NOW SUPPOSE WE INSERT ONE
TUPLE IN loan-info AS loan-info ? loan-info ?
(perryridge,3,ruth) WE MUST INSERT THIS IN
THE BORROW RELATION. HOWEVER, BORROW RELATION
NEEDS THE VALUE FOR THE AMOUNT ATTRBUTE. TWO
CASES ARE POSSIBLE 1. REJECT THE INSERTION AND
RETURN AN ERROR MESSAGE TO THE USER. OR 2. INSERT
NULL IN THE AMOUNT COLUMN. NULL MEANS EITHER THE
VALUE IS UNKNOWN OR DOES NOT EXISTS.