Module $1$ Assignment $2-$ Relational Model

Overview

This assignment is based on the following bank database.

branch $($branch$\_$name, branch$\_$city, assets$)$

customer $($ID$,$ customer$\_$name, customer$\_$street, customer$\_$city$)$

loan $($loan$\_$number, branch$\_$name, amount$)$

borrow $($ID$,$ loan$\_$number$)$

account $($account$\_$number, branch$\_$name, balance$)$

despitor $($ID$,$ account$\_$number$)$

Instructions

To complete this assignment, answer these questions:

$(30$ points$)$ Consider the bank database. Give an expression in the relational algebra for each of the following queries:

Find the name of each branch located in "Chicago".

Find the ID of each borrower who has a loan in branch "Downtown".

$(30$ points$)$ Consider the bank database. Assume that branch names and customer names uniquely identify branches and customers, but loans and accounts can be associated with more than one customer.

What are the appropriate primary keys for each relation?

Given your choice of primary keys, identify appropriate foreign keys for each relation $($if there is one$).$ In your answer, list which attribute is a foreign key referencing which relation.

$(40$ points$)$ Consider the bank database. Give an expression in the relational algebra for each of the following queries:

Find each loan number with a loan amount greater than $$10000.$

Find the ID of each depositor who has an account with a balance greater than $$6000.$

Find the ID of each depositor who has an account with a balance greater than $$6000$ at the $$Uptown$$ branch.