HW01 CISC3000 Assignment 01

Bank database

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

Question 01

Consider the above bank database. Give an expression in the relational algebra for each of the following queries.
a. Find the name of each branch located in “Chicago”.

-- a. Find the name of each branch located in “Chicago”.
select branch_name [π branch_name]
from branch [(branch)]
where branch_city = "Chicago" [σ branch_city = "Chicago"]
a. π branch_name (σ branch_city = "Chicago"(branch))

b. Find the ID of each borrower who have a loan in branch “Downtown”.

-- b. Find the ID of each borrower who have a loan in branch “Downtown”.
select ID [π ID]
from loan natural join borrower using (ID) [(borrower ⋈ borrower.ID=loan.ID loan)]
where branch_name = "Downtown" [σ branch_name = "Downtown"]
b. π ID ( σ branch_name = "Downtown" (borrower ⋈ borrower.ID=loan.ID loan))

Question 02

Consider the above bank database.
Assume that branch names and IDs uniquely identify branches and customers, but loans and accounts can be associated with more than one customer.

Depositor

Borrower

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

a. What are the appropriate primary keys?

a. What are the appropriate primary keys?
Ans:
1) branch_name in the 'branch' table is the primary key in 'branch' table
2) ID in the 'customer' table is the primary key in 'customer' table
3) loan_number - 'loan'
4) account_number - 'account'
5) ID - 'depositor'
6) ID - 'borrower'

Correct Answer:
a. The primary keys of the various schemas are underlined.
We allow customers to have more than one account, and more than one loan.
branch(branch_name, branch_city, assets)
customer (ID, customer_name, customer_street, customer_city)
loan (loan_number, branch_name, amount)
borrower (ID, loan_number)
account (account_number, branch_name, balance)
depositor (ID, account_number)

b. Given your choice of primary keys, identify appropriate foreign keys.

Foreign key constraint: Value in one relation must appear in another

b. Given your choice of primary keys, identify appropriate foreign keys.
Ans:
1) ID in customer - 'depositor'
2) ID in customer - 'borrower'
3) brancher_name in branch - 'loan'
4) brancher_name in branch - 'account'

Correct Answer
b. The foreign keys are as follows:
i. For loan: branch name referencing branch.
ii. For borrower: Attribute ID referencing customer and loan number referencing loan
iii. For account: branch name referencing branch.
iv. For depositor: Attribute ID referencing customer and account number referencing account

Question 03

Construct a schema diagram for the above bank database.
Use the primary and foreign keys of your previous exercise.
🟢 as primary key
🔶 as foreign keys

Question 04

Consider the above bank database.
Construct the following SQL queries for this relational database.
a. Find the ID of each customer of the bank who has an account but not a loan.

-- 4.a. Find the ID of each customer of the bank who has an account but not a loan.
select C.ID
from customer as C,depositor as D
where C.ID = D.ID 
and C.ID not in (
  select ID
  from borrower)

Correct Answer
a. Find the ID of each customer of the bank who has an account but not a loan.

(select ID from depositor)
except
(select ID from borrower)

b. Find the ID of each customer who lives on the same street and in the same city as customer “12345”.

-- 4.b. Find the ID of each customer who lives on the same street and in the same city as customer “12345”.
select ID
from customer
where customer_street, customer_city in (
  select customer_street, customer_city
  from customer
  where ID = '12345')

Correct b.
b. Find the ID of each customer who lives on the same street and in the same city as customer “12345”.

select F.ID
from customer as F, customer as S
where F.customer_street = S.customer_street
         and F.customer_city = S.customer_city
         and S.customer_id = '12345'

c. Find the name of each branch that has at least one customer who has an account in the bank and who lives in “Harrison”.

/*
c. Find the name of each branch that has at least one customer 
who has an account in the bank and who lives in “Harrison”.
*/
select branch_name
from account natural join depositor
where ID in (
  select ID
  from customer
  where customer_city = 'Harrison')

Correct c.
c. Find the name of each branch that has at least one customer who has an account in the bank and who lives in Harrison.

select distinct branch name
from account, depositor, customer
where customer.id = depositor.id
          and depositor.account_number = account.account_number
          and customer_city = 'Harrison'

Question 05

Consider the above bank database.
Construct the following SQL queries for this relational database.
a. Find each customer who has an account at every branch located in “Brooklyn”.

/*
a. Find each customer who has an account 
at every branch located in “Brooklyn”.
*/
select ID, account_number
from depositor natural join account
where branch_name in (
  select branch_name
  from branch
  where branch_city = "Brooklyn"
  )

Correct 5.a
a. Find each customer who has an account at every branch located in “Brooklyn”.

select ID, customer_name
from customer as c
where (
  -- Count the number of branch in Brooklyn--
  select count(*)
  from branch
  where branch_city = 'Brooklyn')
    =
  -- Customer has an account in Brooklyn -- 
  (select count(distinct branch.branch name)
  from customer, depositor, account, branch
  where (
    depositor.ID = customer.ID
    and depositor.account number = account.account number
    and account.branch_name = branch.branch_name
    and branch city = 'Brooklyn'))

There are other ways of writing this query, for example by first finding customers who do not have an account at some branch in Brooklyn, and then removing these customers from the set of all customers by using an except clause.

b. Find the total sum of all loan amounts in the bank.

/*
b. Find the total sum of all loan amounts in the bank.
*/
select sum(amount) as SUM_LOAN, branch_name
from loan
group by branch_name

Correct 5.b
b. Find the total sum of all loan amounts in the bank.

select sum(amount)
from loan

c. Find the names of all branches that have assets greater than
those of at least one branch located in “Brooklyn”.

/*
c. Find the names of all branches that have assets greater than 
those of at least one branch located in “Brooklyn”.
*/
select branch_name
from branch
where assets > some(
         select assets
         from branch
         where branch_city = 'Brooklyn')

Correct 5.c
c. Find the names of all branches that have assets greater than
those of at least one branch located in “Brooklyn”.

select branch name
from branch
where assets > some(
  select assets
  from branch
  where branch city = 'Brooklyn')

The keyword any could be used in place of some above.