ER characteristics
5 entitiesXML Schema
DEEPQ1 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