Relational algebra for banking scenario -

i have situation nowhere. don't know how solve relational algebra questions.

deposit (branch, acc-no, cust-name, balance) loan (branch, loan-no, cust-name, balance) branch (branch, assets, branch-county) customer (cust-name, cust-county, branch) 

produce relation shows branch, customer name, balance , account number customers have loan bigger £2500.00 , customers have deposit account balance smaller £100.00. customers should @ romford branch.

this came far. correct?

π branch, acc-no, cust-name, balance (         σ(loan.balance > 2000 ∧ branch='romford')(loan)     ∪   σ(deposit.balance < 150 ∧ branch='romford')(customer ∩ deposit) ) 

correct ansers is:

 π branch, cust-name, balance, acc-no,       (σ balance < 100^branch=”romford”  (deposit))          ∪          π branch, cust-name, balance, loan-no,      (σ balance > 2500 ^branch=”romford”(loan)) 

given statements. every table/relation has statement parameterized columns/attributes. (its "characteristic predicate".) rows/tuples make statement true go in table/relation. first find statements given tables/relations:

// customer [cust-name] has £[balance] in account [acc-no] @ branch [branch] deposit (branch, acc-no, cust-name, balance)  // customer [cust-name] loan [loan-no] balance £[balance] @ branch [branch] loan(branch, loan-no, cust-name, balance)  . . . 

notice table/relation definition shorthand statement.

query statements. put these given statements statement rows want satisfy. use and, or, , not, , condition. keep or drop names. use new name if need one.

i example part of assignment:

-- informal style version branch, customer name, account balance , account number     customers have loan bigger £2000     @ romford branch 

i want rows. want statement rows make true. make statements closer , closer 1 want. start:

-- columns/attributes cust-name, loan-no, balance, branch customer [cust-name] loan [loan-no] balance £[balance] @ branch [branch] 

now want use different name loan balances because want end using balance account balances only:

-- columns/attributes cust-name, loan-no, loan-balance, branch   customer [cust-name] loan [loan-no] balance £[loan-balance] @ branch [branch] 

now want account balances too:

-- columns/attributes cust-name, loan-no, loan-balance, branch, balance, acc-no     customer [cust-name] loan [loan-no] balance £[loan-balance] @ branch [branch] , customer [cust-name] has £[balance] in account [acc-no] @ branch [branch] 

if had used 1 name balance have had loan balance , account balance. rows/tuples have been customers loan balance same account balance. , balance column/attribute have been values.

now want limit balances , branch:

-- columns/attributes cust-name, loan-no, loan-balance, branch, balance, acc-no     customer [cust-name] loan [loan-no] balance £[loan-balance] @ branch [branch] , customer [cust-name] has £[balance] in account [acc-no] @ branch [branch] , [loan-balance]>2000 , [branch]='romford' 

now want of columns/attributes:

-- statement style version -- columns/attributes cust-name, branch, balance, acc-no keeping branch, cust-name, balance, acc-no: (     customer [cust-name] loan [loan-no] balance £[loan-balance] @ branch [branch] , customer [cust-name] has £[balance] in account [acc-no] @ branch [branch] , [loan-balance]>2000 , [branch]='romford') 

this statement example rows.

you can use "keeping names keep" or "dropping names drop. (in logic, dropping called or there exists. because want statement inside true value(s) name, ie want there exists value(s) name makes statement true.)

query shorthand. replace each statement shorthand.

in example get:

-- shorthand style version -- columns/attributes cust-name, branch, balance, acc-no keeping branch, cust-name, balance, acc-no: (     loan (branch, loan-no, cust-name, loan-balance) , deposit (branch, acc-no, cust-name, balance) , loan-balance>2000 , branch='romford') 

(notice in second half of question's attempt don't need customer. because don't need statement. because can state rows want without it. adds cust-county throw away without using.)

query algebra algebra replace:

• every statement table/relation
• every , of table/relation statements ⋈ (natural join)
• every or of table/relation statements (which must have same columns/attributes) ∪ (union)
• every , not of statements (which must have same columns/attributes) \ (difference)
• every , condition σ condition
• every keeping names keep π names keep (projection) (and dropping π names keep)
• every column/attribute renaming in given statement ρ (rename).

∩ (intersection) , x (product) special cases of ⋈ (∩ both sides same columns/attributes , x no shared columns/attributes).

remember column/attribute names introduced table/relation statements & tables/relations removed keeping/dropping & π. remember renaming in given statement becomes ρ.

i get:

-- algebra style version π branch, cust-name, balance, acc-no     σ branch='romford' σ loan-balance>2000             ((ρ loan-balance/balance loan) ⋈ deposit) 

(i don't know particular algebra notation supposed use. learn rules dotting names , using equijoin vs natural join. don't know kind of σ conditions allows.)

follow example. take description of rows , write statement rows make true. convert given statements. replace shorthands. replace algebra.

• what given statements?
• what shorthands?
• what table/relation names?
• what columns/attributes?
• what columns/attributes of rows/tuples want?
• what clear, plain, natural language statement rows want make true rows don't want don't? avoid pronouns, because don't translate algebra; reuse column/attribute names. , if need new name invent 1 , make statements it.
• what one given statement part of overall statement?
• what shorthand version?
• what algebra version?
• did change name in given statement? rename in table/relation via ρ.

continue part of overall statement.

• do want combination of given statements? use and, or, , not , keeping/dropping.
• do not want know value of column/attribute mentioned? use keeping/dropping (then π).
• did mention many names? keep ones want via π (and corresponding keeping/dropping).

keep going.

you have find right order things in. try different orders. because have use not via , not of statements/tables or via condition. , or , and not of statements/tables must have same columns/attributes on each side. , name in in condition has mentioned in statement anded with.

your question. took me while parse , correct goal gave:

show branch, customer name, balance , account number customers have loan bigger £2000 , customers deposit account balance smaller £150. these customers should @ romford branch.

this is:

• show branch, customer name, account balance , account number ( customers have loan bigger £2000 , customers deposit account balance smaller £150 ) . these customers should @ romford branch.

(i had add word make sense of this. unbelievable supposed mean "branch, customer name, balance , number [labelled what??] balance , number loan balance , number customers loans > 2000 or balance , number account balance , number customers account balances < 150".)

this has , in middle might think give algebra ⋈ (natural join) or ∩ (intersection). must describe columns/attributes in terms of given statements , conditions. turns out , gets turned or. turns out have add deposit statement. have loan customers account info. remember have have same columns/attributes on both side of or (or , not).

first "branch, customer name, account balance, account number for" "all customers have loan bigger £2000". looks did above. time lets limit branches later:

-- statement -- columns/attributes cust-name, branch, balance, acc-no keeping branch, cust-name, balance, acc-no: (     customer [cust-name] loan [loan-no] balance £[loan-balance] @ branch [branch] , customer [cust-name] has £[balance] in account [acc-no] @ branch [branch] , [loan-balance]>2000) 

now "branch, customer name, account balance, account number for" "all customers deposit account balance smaller £150". simpler before hope can understand directly:

-- statement b -- columns/attributes cust-name, branch, balance, acc-no     customer [cust-name] has £[balance] in account [acc-no] @ branch [branch] , [balance]<150 

now want rows make statement "statement or statement b" true:

-- columns/attributes cust-name, branch, balance, acc-no     keeping branch, cust-name, balance, acc-no: (         customer [cust-name] loan [loan-no] balance £[loan-balance] @ branch [branch]     , customer [cust-name] has £[balance] in account [acc-no] @ branch [branch]     , [loan-balance]>2000) or  customer [cust-name] has £[balance] in account [acc-no] @ branch [branch]     , [balance]<150 

now limit branch:

-- statement goal -- columns/attributes cust-name, branch, balance, acc-no (   keeping branch, cust-name, balance, acc-no: (         customer [cust-name] loan [loan-no] balance £[loan-balance] @ branch [branch]     , customer [cust-name] has £[balance] in account [acc-no] @ branch [branch]     , [loan-balance]>2000) or  customer [cust-name] has £[balance] in account [acc-no] @ branch [branch]     , [balance]<150 ) , [branch]='romford' 

this statement wanted rows. replace shorthands:

-- shorthand goal -- columns/attributes cust-name, branch, balance, acc-no (   keeping branch, cust-name, balance, acc-no: (         loan (branch, loan-no, cust-name, loan-balance)     , deposit (branch, acc-no, cust-name, balance)     , [loan-balance]>2000) or  deposit (branch, acc-no, cust-name, balance)     , [balance]<150 ) , [branch]='romford' 

an answer. replace algebra:

-- algebra style version σ branch='romford'     (   π branch, cust-name, balance, acc-no             σ loan-balance>2000                 ((ρ loan-balance/balance loan) ⋈ deposit)     ∪   σ balance<150 deposit)) 

ps: algebra = loopless calculation whole point of relational algebra statements correspond algebraic expressions: statements correspond tables/relations , (statements') logic operators correspond algebra operators. algebra version loopless description can automatically calculated. rows make statement true value of algebraic version. give rows table/relation statements , algebra calculates rows other statement combine them.