## Solutions - Relational Algebra Workshop 5 --- ### Exercise 1. Display customers with a student account in Armidale branch. **keyboard Form:** `customer_acc := (customer JOIN ssn=cssn customer_account) JOIN ano=anum account`
`student_acc := customer_acc WHERE atype = 'Student'`
`student_bank := student_acc JOIN (bco,bno) = (bco,bnum) bank_branch`
`armidale_student := student_bank WHERE b_address = 'Armidale'`
**Paper Form:** `\( customer\_acc\ \leftarrow (customer\ \bowtie\space _{ssn=cssn}\space\space\space\space customer\_account) \bowtie\space _{ano=anum}\space\space\space\space account\ \)` `\( student\_acc\ \leftarrow\ \sigma\space _{ atype = 'Student' }\space\space\space\space (customer\_acc) \)` `\( student\_bank\ \leftarrow\ student\_acc \bowtie \space _{(bco,bno) = (bco,bnum)}\space\space\space\space\space\space\space\space bank\_branch\)` `\( armidale\_student\ \leftarrow\ \sigma \space _{b\_address\ =\ 'Armidale'\ }\space\space\space\space\space\space student\_bank\ \)` --- ### Exercise 2. Lets display average, total and count of balances per branch. **Keyboard Form:** `customer_acc := customer_acount JOIN ano=anum account`
`customer_bank := customer_acc JOIN (bco,bno) = (bco,bnum) bank_branch`
`balances_per_branch := AVERAGE, balance, SUM, balance, COUNT, balance (customer_bank) GROUP BY bnum`
**Paper Form:** `\( customer\_acc \leftarrow\ customer\_acount \bowtie \space _{ano=anum}\space\space\space\space\space\space account \)` `\( customer\_bank \leftarrow\ customer\_acc \bowtie \space _{(bco,bno) = (bco,bnum)}\space\space\space\space\space\space bank\_branch \)` `\( balances\_per\_branch \leftarrow\ {bnum}\space \Im _{AVERAGE, balance, SUM, balance, COUNT, balance}\space\space\space\space\space\space\space\space\space\space\space\space (customer\_bank) \)` --- ### Exercise 3. List the number of accounts and names of customers who have more than one bank account. **Keyboard Form:** `customer_acc := customer JOIN ssn=cssn customer_account`
`no_customer_acc(ssn,count) := COUNT, ano (customer_acc) GROUP BY ssn`
`more_than_one_acc := no_customer_acc WHERE count > 1 `
`customer_details := (more_than_one_acc * customer)[count,ssn,name]`
**Paper Form:** `\( customer\_acc \leftarrow\ customer JOIN ssn=cssn customer_account \)` `\( no\_customer\_acc(ssn,count) \leftarrow\ {ssn}\space \Im \space _{COUNT, ano}\space\space\space\space (customer\_acc) \)` `\( more\_than\_one\_acc \leftarrow\ \sigma\space _{ count > 1 } \space\space\space\space (no\_customer\_acc) \)` `\( customer\_details \leftarrow\ \pi \space _{count, ssn, name}\space\space\space\space (more\_than\_one\_acc * customer) \)` --- ### Exercise 4. Use a UNION to display customer accounts and loans in one table. **Keyboard Form:** `customer_l(name,amount,type) := (customer JOIN ssn=cssn customer_loan)[name,amount,ltype]`
`customer_acc(name,amount,type) := (customer JOIN ssn=cssn customer_account)[name, balance, atype]`
`customer_acc_details := customer_l UNION customer_acc`
**Paper Form:** `\( customer\_l(name,amount,type) \leftarrow\ \pi \space _{name,amount,ltype}\space\space\space\space(customer \bowtie \space _{ssn=cssn}\space\space\space\space customer\_loan) \)` `\( customer\_acc(name,amount,type) \leftarrow\ \pi \space _{name, balance, atype}\space\space\space\space(customer \bowtie \space _{ssn=cssn}\space\space\space\space customer\_account) \)` `\( customer\_acc\_details \leftarrow\ customer\_l \cup\space customer\_acc \)` --- ### Exercise 5. Display customers with all accounts NOT in Armidale branch. **Keyboard Form:** `customer_acc := (customer JOIN ssn=cssn customer_account) JOIN ano=anum account`
`customer_bank := customer_acc JOIN (bco,bno) = (bco,bnum) bank_branch`
`bank_in_armidale := customer_bank WHERE b_address = 'Armidale'`
`not_in_armidale := customer_bank[ssn] MINUS bank_in_armidale[ssn]`
`details_not_armidale := (not_in_armdale * customer)[ssn,name]`
**Paper Form:** `\( customer\_acc \leftarrow\ (customer \bowtie \space _{ssn=cssn}\space\space\space\space customer_account)\bowtie \space _{ ano=anum}\space\space\space\space account \)` `\( customer\_bank \leftarrow\ customer\_acc \bowtie \space _{(bco,bno) = (bco,bnum)}\space\space\space\space\space\space\space\space bank\_branch \)` `\( bank\_in\_armidale \leftarrow\ \sigma\space _{ b\_address = 'Armidale'}\space\space\space\space\space\space customer\_bank \)` `\( not\_in\_armidale \leftarrow\ \pi \space _{ssn}\space\space (customer\_bank) - \pi \space _{ssn}\space\space (bank\_in\_armidale) \)` `\( details\_not\_armidale \leftarrow\ \pi \space _{ssn,name}\space\space\space\space (not\_in\_armdale * customer) \)` --- ### Exercise 6. Get Customers whos loan types are all general. **Keyboard Form:** `customer_l := customer_loan JOIN lno=lnum loan`
`customer_not_general := customer_l WHERE ltype <> 'General'`
`customer_all_general := customer_l[cssn] MINUS customer_not_general[cssn]`
`customer_all_gen_details := (customer JOIN ssn=cssn customer_all_general)[ssn,name]`
**Paper Form:** `\( customer\_l \leftarrow\ customer\_loan \bowtie \space _{lno=lnum}\space\space\space\space loan \)` `\( customer\_not\_general \leftarrow\ \sigma\space _{ ltype\space \neq \space 'General' }\space\space\space\space customer\_l \)` `\( customer\_all\_general \leftarrow\ (\pi \space _{cssn}\space\space (customer\_l)) - (\pi \space _{cssn}\space\space(customer\_not\_general)) \)` `\( customer\_all\_gen\_details \leftarrow\ \pi \space _{ssn,name}\space\space\space\space(customer \bowtie \space _{ssn=cssn}\space\space\space\space customer\_all\_general) \)` --- ### Exercise 7. Return customers who have the same account types as 'Erin Drake'. **Keyboard Form:** `accounts := (customer JOIN ssn=cssn customer_account)`
`account_types := (accounts JOIN ano=anum acount)[atype]`
`erin_accounts := (account_types WHERE name= 'Erin Drake') [atype]`
`result1 := accout_types[ssn,atype] DIVIDEDBY erin_accounts`
`final := (employee * result1)[fname,lname,ssn] `
**Paper Form:** ` Provided soon ` --- ### Exercise 8. Return a sole customer with general loan types and if another customer with loan type general exists return no result. **Keyboard Form:** `general_loan := (customer_loan JOIN lno=lnum loan) WHERE ltype='General'`
`count_of_gens(ltype,count) := COUNT,essn (general_loan) GROUP BY ltype`
`gen_loan_customer := customer JOIN ssn=cssn general_loan`
`sole_gen_customer := ((gen_loan_customer * count_of_gens) WHERE count= 1)[ssn,fname,lname`]
**Paper Form:** `\( general\_loan \leftarrow\ \sigma\space _{ltype\space=\space'General'}\space\space\space\space (customer\_loan \bowtie \space _{lno=lnum}\space\space\space\space loan) \)` `\( count\_of\_gens(ltype,count) \leftarrow\ {ltype}\space \Im \space _{COUNT,essn}\space\space\space\space (general\_loan) \)` `\( gen\_loan\_customer \leftarrow\ customer \bowtie \space _{ssn=cssn}\space\space\space\space general\_loan \)` `\( sole\_gen\_customer \leftarrow\ \pi \space _{ssn,fname,lname}\space\space\space\space (\sigma\space _{ count = 1}\space\space ( count\_of\_gens * gen\_loan\_customer ) )\)` --- Remember that there are multiple ways to solve these problems, if you are unsure, post your solution in the workshop forums for discussion.