ER characteristics
7 entitiesXML Schema
DEEPQ1 Return the name of person id = 2 DEEP For $a in DISTINCT //person[@id="2"] Return $a/name SHALLOW, AF For $a in //person[@id="2"] Return $a/name EN, MCMR, DR For $a in //{c3}person[@id="2"] Return $a/{c3}name Q2 Return the department address of all departments of store id= 1 DEEP, AF For $a in //store[@id="1"]//department Return $a/address SHALLOW For $a in //store[@id ="1"] For $b in //department where $a/dept_of/@idref = $b/@id return $b/address EN, MCMR, DR For $a in //{c1}store[@id="1"]//{c1}department Return $a/{c1}address Q3 Return all departments whose last employee’s manager has at least twice salary as the last employee DEEP For $a in //department Where $a//works_for[last()]/employee/salary * 2 <= $a/works_for[last()]/employee/manages/employee/salary Return $a AF For $a in //department For $b in $a/works_for[last()]/employee For $c in $a/works_for/employee where $c/manages/@idref = $b/@id and $c/salary >= 2* $b/salary return $a SHALLOW For $a in //department For $b in //employee For $c in //employee where $b/@id = $c/manages/@idref and $a/works_for[last()]/@idref = $b/@id and $c/salary >= 2* $b/salary and $c/@id = $a/works_for/@idref return $a EN, MCMR, DR For $a in //{c1}department For $b in $a/{c1}works_for[last()]/{c1}employee For $c in //employee where $b/{c1}manages = $c/{c2}manages and $b/{c1}salary *2 <= $c/{c2}salary return $a Q4 List the department name where a certain employee work before another DEEP, AF For $a in //department Where $a/works_for[employee/@id="1"]/date < $a/works_for[employee/@id="2"]/date Return $a SHALLOW For $a in //department For $b in //employee[@id="1"] For $c in //employee[@id="2"] For $d in $a/works_for For $e in $a/works_for where $d.date < $e/date and $d/@idref = $b/@id and $e/@idref = $c/@id return $a EN, MCMR, DR For $a in //{c1}department Where $a/{c1}works_for[{c1}employee/@id="1"]/{c1}start_date < $a/{c1}works_for[{c1}employee/@id="2"]/{c1}start_date Return $a Q5 How many malls have more than 1 stores? DEEP, AF, SHALLOW COUNT (For $a in //mall Let $b := $a/located_in where count($b) >1 return $a) EN, MCMR, DR COUNT (For $a in //{c1}mall Let $b := $a/{c1}located_in where count($b) >1 return $a) Q6 How many stores owned by owner id=1? DEEP COUNT(For $a in //store[//owner[@id="1"] Return $a) AF, SHALLOW COUNT (For $a in //store For $b in //owner[@id="1"] where $a/owns/@idref = $b/@id Return $a) EN COUNT(For $a in //store For $b in //{c2}owner[@id="1"] where $a/{c1}owns = $b/{c2}owns return $a) MCMR, DR For $a in //{c2}owner[@id="1"] Return count($a//{c2}store) Q7 How may pieces of prose in the database DEEP For $a in // Return count(DISTINCT $a//person) + count($a//mall) + count($a//store) + count($a//department) SHALLOW, AF, EN, MCMR, DR For $a in // Return count($a//person) + count($a//mall) + count($a//store) + count($a//department) EN, MCMR, DR For $a in // Return count($a//{c3}person) + count($a//{c1}mall) + count($a//{c1}store) + count($a//{c1}department) Q8 List the name of who owns store [id=1] DEEP For $a in //store[@id="1"] Return $a//owner/name SHALLOW, AF For $a in //store[@id="1"] For $b in //owner where $a/owns/@idref = $b/@id return $b/name EN For $a in //{c1}store[@id="1"] For $b in //{c2}owns where $a/{c1}owns = $b/{c2}owns return $b/name MCMR,DR For $a in //{c2}owner[//{c2}store/@id="1"] Return $a/{c2}name Q9 List the name of store owner of the mall id=1 DEEP For $a in //mall[@id="1"]//owner Return $a AF For $a in //mall[@id="1"]//owns For $b in //owner where $a/@idref = $b/@id return $b SHALLOW For $a in //mall[@id="1"] For $b in //store For $c in //owner where $a/located_in/@idref = $b/@id and $b/owns/@idref = $c/@id return $c EN, MCMR, DR For $a in //{c1}mall[@id="1"]//{c1}owns For $b in //{c2}owner where $a = $b/{c2}owns return $b Q10 List all employees according to their manager status DEEP For $a in DISTINCT-VALUES( //employee/status) Let $b := Let $c in //employee Where $c/manages/employee/status = $a Return $c Return $a, $b AF, SHALLOW For $a in DISTINCT-VALUES(//employee/status) Let $b := For $c in //employee Let $d in //employee Where $c/manages/@idref = $d/@id And $c/status = $a Return $d Return $a, $b EN, MCMR, DR For $a in DISTINCT-VALUES(//{c2}employee/{c2}status} Let $b := For $c in //{c2}employee Let $d := //{c1}employee Where $c/{c2}/status = $a And $d/{c1}manages = $c/{c2}manages Return $d Return $a, $b Q11 For each employee, show the department they works for DEEP, AF For $a in //employee For $b in //department where $a/@id = $b/employee/@id return $a, $b SHALLOW For $a in //employee For $b in //department where $a/@id = $b/works_for/@idref return $a, $b EN, MCMR, DR For $a in //{c1}employee For $b in //{c1}department where $a/@id = $b/{c1}employee/@id return $a, $b Q12 For each temporary employee, show the department they works for DEEP, AF For $a in //employee[{c1}status="temporary"] For $b in //department where $a/@id = $b/employee/@id return $a, $b SHALLOW For $a in //employee[{c1}status="temporary"] For $b in //department where $a/@id = $b/works_for/@idref return $a, $b EN, MCMR, DR For $a in //{c1}employee[{c1}status="temporary"] For $b in //{c1}department where $a/@id = $b/{c1}employee/@id return $a, $b Q13 List the store name of owner [1] along with description DEEP For $a in //store[//owner/@id="1"] Return $a/name, $a/description SHALLOW, AF For $a in //owner[id=1] For $b in //store where $b/owns/@idref = $a/@id return $b/name, $b/description EN For $a in //{c1}store For $b in //{c2}owner[@id=1] where $b/{c2}owns = $a/{c1}owns return $a/{c1}name, $a/{c1}description MCMR, DR For $a in //{c2}owner[id=1]//{c2}store Return $a/{c2}name, $a/{c2}description Q14 Return the name of all stores whose description contain the words "accessories" DEEP, AF, SHALLOW For $a in //store Where CONTAINS ($a/description , "accessories") Return $a/name EN, MCMR, DR For $a in //{c1}store Where CONTAINS ($a/{c1}description , "accessories") Return $a/{c1}name Q15 For mall id 1, print the employee name and his manager DEEP For $a in //mall[@id="1"]//employee For $b in $a/manages/employee return $a, $b AF For $a in //mall[@id="1"]//employee For $b in //employee where $a/manages/@idref = $b/@id return $a, $b SHALLOW For $a in //mall[@id="1"] For $b in //store For $c in //department For $d in //employee For $e in //employee where $a/located_in/@idref = $b/@id and $b/dept_of/@idref = $c/@id and $c/works_for/@idref = $d and $e/manages/@idref = $d return $d, $e EN, MCMR, DR For $a in //{c1}mall[@id="1"]//{c1}employee For $b in //{c2}employee where $a/{c1}manages = $b/{c2}manages return $a, $b Q16 Return the id of all malls that have at least 5 stores DEEP, AF, SHALLOW For $a in //mall Let $b := $a/located_in Where count($b) > 5 return $a EN, MCMR, DR For $a in //{c1}mall Let $b := $a/{c1}located_in Where count($b) > 5 return $a Q17 Which person don’t have a homepage? DEEP, AF, SHALLOW For $a in //person Where EMPTY($a//homepage) Return $a/name EN, MCMR, DR For $a in //{c3}person Where EMPTY($a//{c3}homepage) Return $a/name Q18 Convert currency of all department renovation budget DEEP, AF, SHALLOW FUNCTION CONVERT($v) { RETURN 40 * $v } For $a in //department Return CONVERT($a/budget) EN, MCMR, DR FUNCTION CONVERT($v) { RETURN 40 * $v } For $a in //{c1}department Return CONVERT($a/{c1}budget) Q19 Give an alphabetically list of all stores along with their store manager DEEP, AF For $a in //store ORDER BY $a/name Return $a/name, $a//store_manager SHALLOW For $a in //store For $b in //store_manager Where $a/manages/@idref = $b/@id ORDER BY $a/name Return $a/name, $b EN, MCMR, DR For $a in //{c1}store ORDER BY $a/{c1}name Return $a/name, $a//store_manager Q20 Group person by their status DEEP <owner> COUNT(DISTINCT //owner) </ owner> <store_manager> COUNT(DISTINCT //store_manager) </ store_manager > <employee> COUNT(DISTINCT //employee) </employee> AF, SHALLOW <owner> COUNT(//owner) </ owner> <store_manager> COUNT(//store_manager) </ store_manager > <employee> COUNT(//employee) </employee> EN, MCMR, DR <owner> COUNT(//{c2}owner) </ owner> <store_manager> COUNT(//{c1}store_manager) </ store_manager > <employee> COUNT(//{c1}employee) </employee> U1 Insert new store id 333 of mall id 10 DEEP, AF For $a in //mall[@id="10"] Insert <located_in><store id=333></store></located_in> into $a SHALLOW For $a in //mall[10] Insert <located_in idref=33> into $a Insert <store> into $b in // EN, MCMR, DR For $a in //{c1}mall[@id="10"] Insert <located_in><store id=333></store></located_in> into $a U2 Delete the last worker of department id=0 DEEP For $a in //department[@id="0"] Delete $a//works_for[last()] For $b in //employee Where $b/@id = $a//works_for[last()]/employee/@id Delete $b AF For $a in //department[@id="0"] For $b in $a//employee For $c in $b/manages where $c/@idref = $a/works_for[last()]/employee delete $a/works_for[last], $c SHALLOW For $a in //department[@id="0"] Delete $a/works_for[last()] For $b in //employee For $c in //employee For $d in $c/manages where $b/@id = $a/works_for[last()] and $d/@idref =$b/@Id delete $b, $d EN, MCMR, DR For $a in //{c1}department[@id="0"] Delete $a/{c1}works_for/last() U3 replace address information of person id= 0 DEEP, AF, SHALLOW For $a in //person[@id="0"] Replace $a/address with <address>new</address> EN, MCMR, DR For $a in //{c3}person[@id="0"] Replace $a/{c3}address with <address>new</address> U4 If mall id 4 exist, change its name, else insert DEEP, AF, SHALLOW If For $a in //mall[@id="4"] Exist Then change $a/name/text() to "New name" Else insert <mall id=4></mall> into // EN, MCMR, DR If For $a in //{c1}mall[@id="4"] Exist Then change $a/{c1}name/text() to "New name" Else insert <mall id=4></mall> into //{c1} U5 For all departments, insert a count of total worker as new count element DEEP, AF, SHALLOW For $a in //department Insert <count>count($a/works_for)</count> into $a EN, MCMR, DR For $a in //{c1}department Insert <count>count($a/{c1}works_for)</count> into $a U6 Delete all departments for stores managed by manager name David DEEP, AF For $a in //store[//store_manager/name="David"]//dept_of Delete $a SHALLOW For $a in //store_manager[name="David"] For $b in //store For $c in //department For $d in //employee where $a/@id = $b/manages/@idref and $b/dept_of /@idref= $c/@id and $c/works_for/@idref = $d/@id delete $b/dept_of, $c, $d EN, MCMR, DR For $a in //{c1}store[//{c1}store_manager/{c1}name="David"]//{c1}dept_of Delete $a U7 remove mall id= 3 DEEP, AF For $a in //mall[@id="3"] Delete Subtree $a SHALLOW For $a in //mall[@id="3"] For $b in //store For $c in //owner For $d in /department For $e in //employee For $f in //storemanger where $a/located_in/@idref = $b/@id and $c/@id = $b/owns/@idref and $f/@id = $b/manges/@idref and $e/@id = $d/works_for/@idref and $d/@id = $b/dept_of/@idref delete $a, $b, $c, $d, $e, $f EN, MCMR, DR For $a in //{c1}mall[@id="3"] Delete Subtree $a U8 For each owner, compute the total number of stores owned, insert this total DEEP For $a in DISTINCT(//owner) Let $b := //store Let $c := //owns Where $b/owns/@id = $a/@id and $c/@id = $a/@id insert <total>count($b)</total> into $c AF, SHALLOW For $a in //owner Let $b := owns Where $a/@id = $b/@idref Insert <total>count($b)</total> into $a EN, MCMR, DR For $a in //{c2}owner Insert <total>count($a/owns)</total> into $a