ER characteristics

XML Schema

Q1 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