ER characteristics
9 entitiesXML Schema
DEEPQ1 Return the name of person with id 1 DEEP, AF, SHALLOW For $a in //person[@id=1]f Return $a/name EN, MCMR, DR For $a in //{c1}person[@id=1] Return $a/{c1}name Q2 Return the first service of airplane DEEP, AF For $a in //airplane Return $a, $a/plane_service[1]/service SHALLOW For $a in //airplane For $b in //service where $a/plane_service[1]/@idref = $b/@id return $a, $b EN, MCMR, DR For $a in //{c1}airplane Return $a, $a/{c1}plain_service[1]/{c1}service Q3 Return the id of airplane whose last service is at least twice as high as initial service DEEP, AF For $a in //airplane Where $a/plane_service[1]/service/cost >= 2* $a/plane_service[last]/service/cost Return $a/@id SHALLOW For $a in //airplane For $b in //service For $c in //service Where $a/plane_service[1]/@idref = $b And $a/plane_service[last]/@idref = $c and $b/cost >= 2* $c/cost return $a EN, MCMR, DR For $a in //{c1}airplane Where $a/{c1}plane_service[1]/{c1}service/{c1}cost >= 2* $a/{c1}plane_service[last]/{c1}service/{c1}cost Return $a/@id Q4 List airplane where a certain owner owns before another owner DEEP For $a in //airplane Where $a//owner[@id=1]/date < $a//owner[@id=2]/date Return $a SHALLOW, AF For $a in //airplane For $b in //owner[@id=1] For $c in //owner[@id=2] For $d in $a/owns For $e in $a/owns where $d/@idref = $b and $e/@idref = $c and $d/date < $e/date return $a EN, MCMR, DR For $a in //{c1}airplane For $b in //{c3}owner[@id=1]/{c3}owns For $c in //{c3}owner[@id=2]/{c3}owns For $b in $a/{c1}owns For $c in $a/{c1}owns where $b/{c1}date < $c/{c1}date return $a Q5 How many owns airplane before 911 event? DEEP, AF, SHALLOW COUNT(For $a in //owns Where $a/date < "09/11/01" Return $a) EN, MCMR, DR COUNT(For $a in //{c3}owns Where $a/{c3}date < "09/11/01" Return $a) Q6 How many planetype flies by at least 1 pilot DEEP, AF, SHALLOW COUNT( For $a in //planetype Not empty $a/flies Return $a ) EN, MCMR, DR COUNT( For $a in //{c1}planetype Not empty $a/{c1}flies Return $a ) Q7 How many proses in the database? DEEP For $a in // Return count(DISTINCT $a//person) + count($a//corporation) + count(DISTINCT $a//employee) + count($a//airplane) + count(DISTINCT $a//hangar) + count($a//planetype) SHALLOW, AF For $a in // Return count($a//person) + count($a//corporation) + count($a//employee) + count($a//airplane) + count($a//hangar) + count($a//planetype) EN, MCMR, DR For $a in // Return count($a//{c1}person) + count($a//{c1}corporation) + count($a//{c2}employee) + count($a//airplane) + count($a//{c2}hangar) + count($a//{c1}planetype) Q8 List the employee and the number of plane type they work on DEEP For $a in DISTINCT //employee Let $b := //works_on Where $b/employee/@id = $a/@id Return $a, count ($b) SHALLOW, AF For $a in //employee Let $b := //works_on Where $b/@idref = $a/@id Return $a, count($b) EN, MCMR, DR For $a in //{c2}employee Return $a, count($a/{c2}works_on) Q9 List the employee and the number of planetype 1 they works_on DEEP For $a in DISTINCT //employee Let $b := //planetpe[@id="1"]]/works_on Where $b/employee/@id = $a/@idref Return $a, count($b) SHALLOW, AF For $a in //employee Let $b := //planetype[@id="1"]]/works_on Where $b/@idref = $a/@id Return $a, count($b) EN, MCMR, DR For $a in //{c2}employee Let $b := $a/{c2}works_on For $c in //{c1}planetype[@id="1"] where $c/{c1}works_on = $b return $a, count($b) Q10 List all pilots according to their flies hour DEEP For $a in DISTINCT //flies/hour Let $b := For $c in //flies Where $c/hour = $a Return $c/pilot Return $a, $b SHALLOW, AF For $a in DISTINCT //flies/hour Let $b := For $c in //pilot For $d in //flies where $d/@idre = $c/@id and $d/hour = $a return $a, $b EN, MCMR, DR For $a in DISTINCT //{c2}flies/{c2}hour Let $b := For $c in //{c2}pilot Where $c/{c2}flies/{c2}hour = $a Return $c Return $a, $b Q11 For each employee, list the number of airplane it is maintaining DEEP For $a DISTINCT //employee Let $b := //airplane Where $b//employee/@id = $a/@id Return $a, count($b AF For $a in //employee Let $b := //airplane/maintain Where $b/@idref = $a/@id Return $a, count($b) SHALLOW For $a in //employee Let $b := For $c in //airplane For $d in //service where $c/plane_service/@idref = $d/@id and $d/maintain/@idref = $a return $ return $a, count($b) EN, MCMR, DR For $a in //{c2}employee Let $b := //{c1}airplane//{c1}maintain Where $a/{c2}maintain = $b Return $a, count($b) Q12 For each rich employee, list the number of airplane it is maintaining DEEP For $a DISTINCT //employee[income > 100K] Let $b := //airplane Where $b//employee/@id = $a/@id Return $a, count($b AF For $a in //employee[income > 100K] Let $b := //airplane/maintain Where $b/@idref = $a/@id Return $a, count($b) SHALLOW For $a in //employee[income > 100K] Let $b := For $c in //airplane For $d in //service where $c/plane_service/@idref = $d/@id and $d/maintain/@idref = $a return $ return $a, count($b) EN, MCMR, Dr For $a in //{c2}employee[{c2}income > 100K] Let $b := //{c1}airplane//{c1}maintain Where $a/{c2}maintain = $b Return $a, count($b) Q13 List the planetype flied by pilot 1 DEEP For $a in //planetype Where $a//pilot[@id="1"] Return $a SHALLOW,AF For $a in //planetype For $b in //pilot[@id="1"] where $b/@id = $a/flies return $a EN, MCMR, DR For $a in //planetype For $b in //pilot[@id="1"] where $a/flies = $b/flies return $a Q14 Return the name of all hangar contain the word "A" DEEP, SHALLOW, AF For $a in //hangar Where CONTAINS($a/desc, "A") Return $a/name EN, MCMR, DR For $a in //{c2}hangar Where CONTAINS($a/{c2}desc, "A") Return $a/{c2}name Q15 Print all service associate with an owns DEEP, AF For $a in //airplane For $b in $a//service For $c in $a/owns return $b, $c SHALLOW For $a in //airplane For $b in $a/owns For $c in //service where $a/plane_service/@idref = $c/@id return $b, $c MCMR, EN, DR For $a in //{c1}airplane For $b in $a//{c1}service For $c in $a/{c1}owns return $b, $c Q16 Return owns when airplane is in hangar 1 DEEP For $a in //airplane[hangar/@id="1"]/owns Return $Sa SHALLOW, AF For $a in //hangar[@id="1"] For $b in //airplane where $a/stored_in/@idref = $b/@id return $b/owns EN, MCMR, DR For $a in //{c2}hangar[@id="1"]//{c2}airplane For $a in //{c1}airplane return $a/{c1}owns Q17 Which person doesn’t own a plane? DEEP For $a in //person Let $b := //owner Where $b/@id = $a/@id And count($B) = 0 Return $a SHALLOW, AF For $a in //person Let $b := //owns Where $b/@idref = $a/@id And count($b) = 0 Return $a EN, MCMR, DR For $a in //{c1}person Where empty ($a/{c3}owns) Return $a Q18 Convert currency of airplane price DEEP, SHALLOW, AF FUNCTION CONVERT($v) { RETURN 40 * $v } For $a in //airplane Return CONVERT($a/price) EN, MCMR, DR FUNCTION CONVERT($v) { RETURN 40 * $v } For $a in //{c1}airplane Return CONVERT($a/{c1}price) Q19 Give an alphabetically list of planetype DEEP, SHALLOW, AF For $a in //planetype ORDER BY $a/name Return $a/name, $a/description EN, MCMR, DR For $a in //{c1}planetype ORDER BY $a/{c1}name Return $a/{c1}name, $a/{c1}description Q20 Group employee by their income DEEP <High> COUNT(DISTINCT //employee[income > 100K]) </ High > <low> COUNT(DISTINCT // employee[income <= 100K]) </low> AF, SHALLOW <High> COUNT(//employee[income > 100K]) </ High > <low> COUNT(//employee[income <= 100K]) </low> EN, MCMR, DR <High> COUNT(//{c2}employee[{c2}income > 100K]) </ High > <low> COUNT(//{c2}employee[{c2}income <= 100K]) </low> U1 Insert new plane type DEEP, AF, SHALLOW Insert <planetype/> into $a in // EN, MCMR, DR Insert <planetype/> into $a in //{c1} U2 Delete the last service of an airplane (id = 2) DEEP For $a in //airplane[@id="2"]//plane_service[last()] Delete $a For $a in //employee Where NOT EMPTY($b/@id = $a//employee/@id) Move $b to // SHALLOW For $a in //airplane[@id="2"]/plane_service[last()] For $b in //service where $a/plane_service/@idref = $b/@id delete $a, $b AF For $a in //airplane[@id="2"]/plane_service[last()] Delete $a EN, MCMR, DR For $a in //{c1}airplane[@id="2"]/{c1}plane_service[last()] Delete $a U3 Replace name of person id = 0 DEEP, AF, SHALLOW Replace //person[@id="0"]/name with <name></name> EN, MCMR, DR Replace //{c1}person[@id="0"]/{c1}name with <name></name> U4 If pilot 1 exist change name, else add one DEEP If For $a in //pilot[@id="1"] exist Then change $a/name to <name/> If For $b in //person[@id="1"] exist change $b/name to <name/> If For $c in //owner[@id=1] exist change $c/name to <name/> else insert <person> to // insert <pilot> to // AF, SHALLOW If For $a in //pilot[@id="1"] exist Then change $b//person[@id="1"]/name to <name/> Else insert <person><type>pilot</type></person> to // EN, MCMR, DR If For $a in //pilot[@id="1"] exist Change $a/name to <name/> Else insert <pilot> //{c2} U5 For all airplane, insert the no. of owner as new element DEEP, AF, SHALLOW For $a in //airplane Insert <count>count($a/owns)</count> into $a EN, MCMR, DR For $a in //{c1}airplane Insert <count>count($a/{c1}owns)</count> into $a U6 Delete all airplane for planetype id= 2 DEEP For $a in //planetype[@id="2"] Delete $a//airplane Move DISTINCT $a//airplane/hangar to // Move DISTINCT //employee to // Move DISTINCT //owner to // SHALLOW For $a in //planetype[@id="2"] For $b in //airplane For $c in //stored_in where $b/@id = $a/of_type/@idref and $c/@idref = $b/@id delete $b, $c AF For $a in //planetype[@id="2"] Delete $a//airplane For $b in //stored_in where $b/@idref = $a/airplane/@id delete $b EN, MCMR, DR For $a in //planetype[@id="2"] Delete $a//1 U7 Remove pilot id 3<plane_service>, and all flies record DEEP For $a in //flies[pilot/@id="3"] Delete $a SHALLOW, AF For $a in //pilot[@id="3"] For $b in //flies where $b/@idref = $a/@id delete $b EN, MCMR, DR For $a in //{c2}pilot[@id="3"] Delete $a/{c2}flies U8 For each owner, compute total own, and insert it as new element DEEP For $a in DISTINCT //owner Let $b := //owns Where $b/owner/@id = $a/@id Insert <count>count($b)</count> into $a SHALLOW, AF For $a in //owner Let $b := //owns Where $b/@idref $a/@id Insert <count>count($b)</count> into $a EN, MCMR, DR For $a in //{c3}owner Insert <count>count($a/{c3}owns)</count> into $a