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

1. BRANCH-SCHEME (BRANCH-NAME, ASSETS, BRANCH-CITY) CUSTOMER (CUSTOMER-NAME, STREET, CUSTOMER-CITY). DEPOSIT (BRANCH-NAME, ACCOUNT-NUMBER, CUSTOMER-NAME, BALANCE) BORROW-SCHEME = (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 < 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 > 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-NAME=“JOHNSON” CLIENTCUSTOMER) NOW CLIENT.CUTOMER-NAME COLUMN CONTAINS ONLY CUSTOMERS of Banker JOHNSON. TO FIND ALL THE CLIENTS OF BANKER JOHNSON, WE WRITE CLIENT.CUSTOMER-NAME = CUSTOMER.CUSTOMER- NAME (BANKER-NAME=“JOHNSON” (CLIENTCUSTOMER))

5. NOW WE WANT ONLY CUSTOMER-NAME AND CUSTOMER-CITY, WE WRITE CLIENT.CUSTOMER-NAME,CUSTOMER-CITY(CLIENT.CUSTOMER-NAME = CUSTOMER.CUSTOMER-NAME (BANKER-NAME = “JOHNSON” (CLIENT*CUSTOMER)))

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 < D.BALANCE (DEPOSIT D(DEPOSIT))) THIS EXPRESSION GIVES ALL THE BALANCES EXCEPT THE LARGETS ONE. NOW BALANCE(DEPOSIT) DEPOSIT.BALANCE ((DEPOSIT.BALANCE < 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 WRITTENAS 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-CITY=“STAMFORD” (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-NAME=“PERRYRIDGE” (BORROWDEPOSIT)

14. DIVISION () : QUERIES THAT INCLUDE THEPHRASE “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-CITY=“BROOKLYN” (BRANCH)) TO OBTAIN ALL CUSTOMER-NAME, BRANCH-NAME PAIRS FOR WHICH THE CUSTOMER HAS AN ACCOUNT AT A BRANCH : S = CUSTOMER-NAME,BRANCH-NAME (DEPOSIT) NOW TO FIND CUSTOMERS WHO APPEAR IN S WITH EVERY BRANCH NAME IN R. CUSTOMER-NAME,BRANCH-NAME (DEPOSIT) BRANCH-NAME(BRANCH-CITY=“BROOKLYN” (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 SMITH’S 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-CITY=“NEEDHAM” (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BALANCE*1.05(DEPOSIT) Q. TO INCREASE THE BALANCE BY 5% FOR BALANCES OVER $1200, WHILE OTHERS BY 6%. BALANCEBALANCE*1.05(BALANCE > 1200 (DEPOSIT)) BALANCEBALANCE*1.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 <QUERY EXPRESSION> 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.