Question

DATABASE QUESTION

Draw a schema diagram for the following database schema.

Branch (branch_name, branch_city, assets)
customer (customer_name, customer_street, customer_city)
loan (loan number, branch_name, amount)
borrower (customer_name, loan_number)
account (account_number, branch_name, balance)
depositor (customer_name, account_number)

Considering the schema above, write relational expressions for the following sentences
1.Find all loan numbers with a loan value greater than $15,000.
2.Find the names of all depositors who have an account with a value greater than $8,000.
3.Find the names of all depositors who have an account with a value greater than $9,000 at
the "ASU" branch.

What is Cartesian-product here?

Answer 1

/* HERE IS SQL QUERY */

1. SELECT LOAN_NUMBER FROM LOAN WHERE AMOUNT > 15000

2. SELECT D.CUSTOMER_NAME FROM DEPOSITOR D

INNER JOIN

ACCOUNT A

ON

D.ACOUNT_NUMBER = A.ACCOUNT_NUMBER

WHERE A.BALANCE > 8000

3.

SELECT D.CUSTOMER_NAME FROM DEPOSITOR D
INNER JOIN      
ACCOUNT A    
INNER JOIN
BRANCH B
ON     
D.ACCOUNT_NUMBER = A.ACCOUNT_NUMBER    
WHERE A.BALANCE > 9000 AND B.BRANCH_NAME = A.BRANCH_NAME

CARTESIAN PRODUCT:

Cartesian Product is resultant of all column of all the table

For Example, cartesian product of table Account and Customer would be the resultant of the rows of following:

customer_name,customer_street,customer_city,account,branch,balance

The cartesian product is made for the resultant of the multiple column