Question

2. Considering the following schema of a bank database,
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)

(a) Identify the candidate keys and the primary key of each relation. [15 points]
(b) Draw a schema diagram for the database. Make sure to underline the primary keys. [15 points]
(c) Considering the schema above, write relational expressions for the following sentences [15 points]
Find all customers from ‘Kent’ city.
Find the names of all borrowers with a loan amount greater than $5000
Find the names of all depositors who have an account with a balance greater than $9,000 at the “Kent” branch.
(d) What is Cartesian-product? Explain its importance in the relational data model. [10 points]

Answer 1

a. Every primary key is a candidate key and there can be many candidate key in a relation but there will be only one primary key in every relation. Candidate key can be one simple attribute or more than one attributes (combined). Combined attributes as a candidate key can be written in {}.

Entity	Primary Key	Candidate Key
Branch	branch_name	branch_name, {branch_name, branch_city}
customer	customer_name	customer_name, {customer_name, customer_street}
loan	loan_number	loan_number, {loan_number, branch_name}
borrower	loan_number	loan_number, {customer_name, loan_number}
account	account_number	account_number, {account_number, branch_name}
depostier	account_number	account_number, {customer_name, account_number}

b.c.
π customer_name (σ customer_city = 'Kent'(customers))

π customer_name (σ borrower.loan_number = loan.loan_number AND amount > 5000 (borrower X loan))

π customer_name (σ account.account_number = depositer.account_number AND balance > 9000 AND branch_name = "Kent" (account X depositer))

d. Cartesian-product is multiple of two table. Every row of table 1 will be combined with all the rows of table 2, if Table 1 have 2 rows and Table 2 have 3 rows then there cartesian-product will have 2x3 = 6 row (rows get multiplied). The rows of cartesian-product of table 1 and table 2 will contain all the columns from both the table, if Table 1 have 3 columns and Table 2 have 2 columns then there cartesian-product will have 3+2 = 5 columns (columns are add up)
For example:
Table 1(loan): 2 rows and 3 columns

loan_number	branch_name	amount
123	Kent	1500
124	Dent	1600

Table 2(depositer): 3 rows and 2 columns

account_number	customer_name
345	Tom
346	Daisy
347	Harry

Cartesian-Product (loan x depositer): 6 rows and 5 columns

loan_number	branch_name	amount	account_number	customer_name
123	Kent	1500	345	Tom
123	Kent	1500	346	Daisy
123	Kent	1500	347	Harry
124	Dent	1600	345	Tom
124	Dent	1600	346	Daisy
124	Dent	1600	347	Harry

Cartesian-product is important, sometimes when select command is done and when columns are required from both the table, then this is done using cartesian-product and this operation is also known as joining. This helps joining the table in ralational data model and helps to select things from both the table. It denotes by symbol 'X'.