ER9 Bank Database

Source: Fundamentals of Database Systems, Ramez Elmasri, Shamkant B. Navathe
ER characteristics
5 entities
5 relationships: 3 of 1:m, 2 of m:n
XML Schema
DEEP
SHALLOW
EN, MCMR, DR: 2 colors
Q1 Return the name of customer id = 0
DEEP
For $a in DISTINCT(//customer[@id="0"])
Return $a/name

SHALLOW, AF
For $a in //customer[@id="0"]
Return $a/name

EN, MCMR, DR
For $a in //{c2}customer[@id="0"]
Return $a/{c2}name

Q2 Return the balance of the first transaction of all accounts
DEEP, AF, SHALLOW
For $a in //account
Return $a/a-c[1]/balance

EN, MCMR, DR
For $a in //{c1}account
Return $a/{c1}a-c[1]/{c1}balance

Q3 Return the id of all account whose last balance is more than twice of the first balance
DEEP, AF, SHALLOW
For $a in //account
Where $a/a-c[1]/balance *2 <= $a/a-c[last()]/balance
Return $a/@id

EN, MCMR, DR
For $a in //{c1}account
Where $a/{c1}a-c[1]/{c1}balance *2 <= $a/{c1}a-c[last()]/{c1}balance
Return $a/@id

Q4 List account where a certain customer open before another
DEEP
For $a in //account
For $b in //account
For $c in $a/a-c[customer/@id="0"]
For $d in $b/a-c[customer/@id="1"]
where $c/date < $d/date
return $a, $b

SHALLOW, AF
For $a in //account
For $b in //account
For $c in $a/a-c
For $d in $b/a-c
where $c/date < $d/date
and $c/@idref = 0
and $d/@idref = 1
return $a, $b

EN, MCMR, DR
For $a in //{c1}account
For $b in //{c2}customer[@id="0"]/{c2}a-c
For $c in //{c1}account
For $d in //{c2}customer[@id="1"]/{c2}a-c
where $b/{c1}date < $d/{c1}date
and $a/{c1}a-c = $b
and $c/{c1}a-c = $d
return $a, $b

Q5 How many loan cost more than 40K?
DEEP, AF, SHALLOW
COUNT(For $a in //loan[cost > 40K])
Return $a)

EN, MCMR, DR
COUNT(For $a in //{c1}loan[{c1}cost > 40K])
Return $a)

Q6 How many bank-branch are overall?
DEEP, AF, SHALLOW
COUNT (for $a in //
Return $a//bank-branch)

EN, MCMR, DR
COUNT (for $a in //
Return $a//{c1}bank-branch)

Q7 How may pieces of prose in the database?
DEEP
For $a in  //
Return count(DISTINCT $a//customer) + count($a//bank) + count($a//account + count($a//loan) + count($a//bank-branch)

SHALLOW, AF
For $a in  //
Return count($a//customer) + count($a//bank) + count($a//account + count($a//loan) + count($a//bank-branch)

EN, MCMR, DR
For $a in  //
Return count($a//{c2}customer) + count($a//{c1}bank) + count($a//{c1}account + count($a//{c1}loan) + count($a//{c1}bank-branch)

Q8 List the name of customer and the number of loans they buy
DEEP
For $a in DISTINCT //customer
Let $b := //l-c
Where $b/customer/@id = $a/@id
Return $a, count($b)

AF, SHALLOW
For $a in //customer
Let $b := //l-c
where $b/@idref = $a/@id
return $a, count($b)

EN, MCMR, DR
For $a in //{c2}customer
Return $a, count ($a/{c2}l-c)

Q9 List the name of customer and the number of loans they borrowed from bank id=1
DEEP
For $a in DISTINCT //customer
Let $b := //bank[@id="1"]//l-c
Where $b/customer/@id = $a/@id
Return $a, count($b)

AF
For $a in //customer
Let $b := //bank[@id="1"]//l-c
Where $b/@idref = $a/@id
Return $a, count($b)

SHALLOW
For $a in //customer
Let $b := for $c in //loan
	For $d in //bank-branch
	For $e in //bank[@id="1"]
	For $f in $c/l-c
	where $f/@idref = $a
	and $d/loans/@idref = $c/@id
	and $e/branch/@idref = $d/@id
return $a, count($b)

EN, MCMR, DR
For $a in //{c2}customer
Let $b := //{c1}bank["@id=1"]//{c1}l-c
Where $b = $a/{c2}l-c
Return $a, count($b)

Q10 list all banks according to their account’s average lowest balance
DEEP, AF
For $a in DISTINCT-VALUES //account/lowest
Let $b := Let $c in //bank
	Where AVG($c//lowest) = $a/text()
	Return $c
Return $a, $b

SHALLOW
For $a in DISTINCT-VALUES //account/lowest
Let $b := For $c in //bank
	For $d in //bank-branch
	Let $e := //account
	Where $c/branch/@idref = $d/@id
	And $d/accts/@idref = $e/!id
	And AVG($e//lowest) = $a/text()
	return $c
return $a, $b

EN, MCMR, DR
For $a in DISTINCT-VALUES //{c1}account/{c1}lowest
Let $b := Let $c in //{c1}bank
	Where AVG($c//{c1}lowest) = $a/text()
	Return $c
Return $a, $b

Q11 For each customer, list the number of accounts whose balance do not exceed monthly income
DEEP
For $a in DISTINCT //customer
Let $b := //account
Where $b//customer/@id = $a/@id
And $b/balance < $a/income
Return $a, count($b)

AF, SHALLOW
For $a in //customer
Let $b := //account
Where $b/a-c/@idref = $a/@id
And $b/balance < $a/income
return $a, count($b)

EN, MCMR, DR
For $a in //{c2}customer
Let $b := //{c1}account
Where $b/{c1}a-c = $a/{c2}a-c
And $b/{c1}balance < $a/{c2}income
return $a, count($b)

Q12 For each rich customer, list the number of accounts whose balance do not exceed monthly income
DEEP
For $a in DISTINCT //customer[income > 100K]
Let $b := //account
Where $b//customer/@id = $a/@id
And $b/balance < $a/income
Return $a, count($b)

AF, SHALLOW
For $a in //customer[income > 100K]
Let $b := //account
Where $b/a-c/@idref = $a/@id
And $b/balance < $a/income
return $a, count($b)

EN, MCMR, DR
For $a in //{c2}customer[{c2}income > 100K]
Let $b := //{c1}account
Where $b/{c1}a-c = $a/{c2}a-c
And $b/{c1}balance < $a/{c2}income
return $a, count($b)

Q13 List the bank branch of bank in MI
DEEP, AF
For $a in //bank[state = "MI"]//bank-branch
Return $a/name, $a/location

SHALLOW
For $a in //bank-branch
For $b in //bank[state="MI"]
where $b/branch/@idref = $a/@id
return $a/name, $a/location

EN, MCMR, DR
For $a in //{c1}bank[{c1}state = "MI"]//{c1}bank-branch
Return $a/{c1}name, $a/{c1}location

Q14 Return the location of all bank branches whose location contain the word "campus"
DEEP, AF, SHALLOW
For $a in //bank-branch
Where CONTAINS($a/location, "campus")
Return $a/location

EN, MCMR, DR
For $a in //{c1}bank-branch
Where CONTAINS($a/l{c1}ocation, "campus")
Return $a/location

Q15 Print the bank-branch that gives loans
DEEP, AF
For $a in //bank-branch
Let $b := $a//loan
return $a, $b

SHALLOW
For $a in //bank-branch
Let $b := //loan
Where $a/loans/@idref = $b/@id
Return $a, $b

EN, MCMR, DR
For $a in //{c1}bank-branch
Let $b :=  $a//{c1}loan
return $a, $b

Q16 Return the id of those loans that has the same branch as account [0]
DEEP, AF
For $a in //bank-branch[//account/@id =0]
Return $a//loan/@id

SHALLOW
For $a in //bank-branch
For $b in //account[id=0]
For $c in //loan
where $a/accts/@idref = $b
and $c/@id = $a/loans/@idref
return $c

EN, MCMR, DR
For $a in //{c1}bank-branch[//account/@id =0]
Return $a//{c1}loan/@id

Q17 Which customer doesn’t have an email?
DEEP
For $a in DISTINCT //customer
Where EMPTY($a/email)
Return $a

SHALLOW, AF
For $a in //customer
Where EMPTY($a/email)
Return $a

EN, MCMR, DR
For $a in //{c2}customer
Where EMPTY($a/{c2}email)
Return $a

Q18 Convert currency of all account balance
DEEP, AF, SHALLOW
FUNCTION CONVERT($v)
{
	RETURN 40 * $v
}
For $a in //account
Return CONVERT($a/balance)

EN, MCMR, DR
FUNCTION CONVERT($v)
{
	RETURN 40 * $v
}
For $a in //{c1}account
Return CONVERT($a/{c1}balance)

Q19 Give an alphabetically list of all branches of a Credit Union bank
DEEP, AF
For $a in //bank[name="Credit Union"]//bank-branch
ORDER BY $a/name
Return $a/name

SHALLOW
For $a in //bank[name="Credit Union"]
$b in //bank-branch
where $b/@idref = $a/branch/@idref
ORDER BY $b/name
Return $b/name

EN, MCMR, DR
For $a in //{c1}bank[{c1}name="Credit Union"]//{c1}bank-branch
ORDER BY $a/{c1}name
Return $a/{c1}name

Q20 Group customer by their income and output the cardinality
DEEP
<High>
 COUNT(DISTINCT //customer[income > 100K])
</ High >
<low>
COUNT(DISTINCT // customer [income <= 100K])
</low>

AF, SHALLOW
<High>
 COUNT(//customer [income > 100K])
</ High >
<low>
COUNT(//customer [income <= 100K])
</low>

EN, MCMR, DR
<High>
 COUNT(//{c2}customer [{c2}income > 100K])
</ High >
<low>
COUNT(//{c2}customer [{c2}income <= 100K])
</low>

U1 Insert the new bank branch for bank id=100
DEEP, AF
For $a in //bank[@id="100"]
Insert <bank-branch/> into $a

SHALLOW
For $a in //bank[100]
Insert <branch idref="100"> into $a
Insert <bank-branch/> into //

EN, MCMR, DR
For $a in //{c1}bank[@id="100"]
Insert <bank-branch/> into $a

U2 Delete the last a-c of account id=0
DEEP
For $a in //account[@id="0"]
$b in //a-c[last]//customer
move $b to //
delete $a/a-c[last()]

AF, SHALLOW
For $a in //account[@id="0"]
Delete $a/a-c[last()]

EN, MCMR, DR
For $a in //{c1}account[@id="0"]
Delete $a/{c1}a-c[last()]

U3 Replace SSN info of customer id=0
DEEP, AF, SHALLOW
For $a in //customer[@id="0"]
Replace $a/SSN/text() with "000-00-0000"

EN, MCMR, DR
For $a in //{c2}customer[@id="0"]
Replace $a/{c2}SSN/text() with "000-00-0000"

U4 If bank 4 exist, change name else insert a new bank
DEEP, AF, SHALLOW
If for $a in //bank[@id="4"]
Change $a/name to <name>ddd</name>
Else
Insert <bank id=4></bank>

EN, MCMR, DR
If for $a in //{c1}bank[@id="4"]
Change $a/{c1}name to <name>ddd</name>
Else
Insert <bank id=4></bank>

U5 For all accounts, insert a number of a-c account associated with customers
DEEP, AF, SHALLOW
For $a in //account
Insert <count>count($a/a-c)</count>

EN, MCMR, DR
For $a in //{c1}account
Insert <count>count($a/{c1}a-c)</count

U6 Delete all account a-c for customer id=3
DEEP
For $a in //a-c/customer[@id="3"]
For $b in //customer[@id="3"]
Move $b to //
Delete $a

SHALLOW, AF
For $a in //a-c
For $v in //customer[@id="3"]
where $a/@idref = $b/@id
Delete $a

EN, MCMR, DR
For $a in //{c2}customer[@id=3]//{c2}a-c
Delete $a

U7 Remove bank id = 3 and all associate information
DEEP, AF, EN, MCMR, DR 
For $a in //bank[@id="3"]
Delete $a

SHALLOW
For $a in //bank[@id = "3"]
For $b in //bank-branch
For $c in //account
For $d in //loan
where $a/branches/@idref = $b
and $b/accts/@idref = $c/@id
And $b/loans/@idref = $d/@id
delete $a, $b, $c, $d

EN, MCMR, DR 
For $a in //{c1}bank[@id="3"]
Delete $a

U8 For each customer, compute the total loan account and insert it into customer’s element
DEEP
For $a in DISTINCT //customer
Let $b := //loan
Where $b/l-c/customer/@id = $a/@id
Insert <total>SUM($b/amount)</total> into $a

SHALLOW, AF
For $a in //customer
Let $b := //loan
Where $b/l-c/@idref = $a/@id
Insert <total>SUM($b/amount)</total> into $a

EN, MCMR, DR
For $a in //{c2}customer
Let $b := $a/{c1}loan
Where $a/{c2}l-c = $b/{c1}l-c
Insert <total>SUM($b/{c2}amount)</total> into $a