ER characteristics

XML Schema

Q1 Return the shift period of all restaurant id =0
DEEP
For $a in //shifts[//restaurant/@id = "0"]
Return $a

AF, SHALLOW
For $a in //shifts
For $b in //restaurant[@id = "0"]
Where $a/has/@idref = $b/@id
Return $a

EN
For $a in //{c1}shifts
For $b in //{c2}restaurant[@id = "0"]
Where $a/{c1}has = $b/{c2}has
Return $a

MCMR, DR
For $a in //{c2}restaurant[@id = "0"]//{c2}shifts
Return $a

Q2 Return the id of all employees whose work description is "R&D"
DEEP, AF, SHALLOW
For $a in //employees[work/description = "R&D"]
Return $a

EN, MCMR, DR
For $a in //{c1}employees[{c1}work/{c1}description = "R&D"]
Return $a

Q3 Return the ids of all employees whose the first shift hourly paid is at least twice the last shift
DEEP, AF, SHALLOW
For $a in //employees
Where $a/work/first() >= 2* $a/work/last()
Return $a/@id

EN, MCMR, DR
For $a in //{c1}employees
Where $a/{c1}work/first() >= 2* $a/{c1}work/last()
Return $a/@id

Q4 List the SSN of all employees where a certain shift is before another
DEEP
For $a in //employees
Where $a/work/[shifts/@id = "0"]/date < $a/work/[shifts/@id = "1"]/date()
Return $a

AF, SHALLOW
For $a in //employees
For $b in //shifts[@id = "0"]
For $c in //shifts[@id = "1"]
For $d in $a/work
For $e in $a/work
Where $d/@idref= $b/@id
And $e/@idref = $b/@id
And $d/date < $e/date
Return $a

EN, MCMR, DR
For $a in //{c1}employees
For $b in //{c2}shifts[@id = "0"]
For $c in //{c2}shifts[@id = "1"]
For $d in $a/{c1}work
For $e in $a/{c1}work
Where $d= $b/{c2}work
And $e = $b/{c2}work
And $d/{c1}date < $e/{c1}date
Return $a

Q5 How many restaurants has more than 400K yearly profit?
DEEP, AF, SHALLOW
Count(For $a in //restaurant[yearly_profit >400K]
Return $a)

EN, MCMR, DR
Count(For $a in //{c1}restaurant[{c1}yearly_profit >400K]
Return $a)

Q6 How many managers are employed by corporation id=1
DEEP, AF
COUNT(For $a in //corporation[@id = "1"]//manager
Return $a)

EN, MCMR, DR
COUNT(For $a in //{c1}corporation[@id = "1"]//{c1}manager
Return $a)

SHALLOW
COUNT(For $a in //corporation[@id = "1"]
For $b in //manager
Where $a/employs/@idref = $b/@id
Return $b)

Q7 How many pieces of prose in the database?
DEEP
For $a in //
Return count($a/corporation) + count($a//manager)  + count(DISTINCT $a//restaurant)  + count($a//employees)

SHALLOW, AF
For $a in //
Return count($a/corporation) + count($a//manager)  + count($a//restaurant)  + count($a//employees)

EN, MCMR, DR
For $a in //
Return count($a/{c1}corporation) + count($a//{c1}manager)  + count($a//{c1}restaurant)  + count($a//{c1}employees)

Q8 List shift and the restaurant assigned
DEEP
For $a in //shifts
Return $a, $a/restaurant

AF, SHALLOW
For $a in //shifts
For $b in //restaurant
Where $a/has/@idref = $b/@id
Return $a, $b

EN
For $a in //{c1}shifts
For $b in //{c2}restaurant
Where $a/{c1}has = $b/{c2}has
Return $a, $b

MCMR, DR
For $a in //{c2}restaurant
For $b in $a//{c2}shifts
Return $b, $a

Q9 List shift and the restaurant assigned, restaurant of corporation id = 0
DEEP
For $a in //shifts
For $b in //corporation[@id = "0"]
For $c in $a/restaurant
Where $b/restaurant/@id = $c/@id
Return $a, $c

AF, SHALLOW
For $a in //shifts
For $b in //corporation[@id = "0"]//restaurant
Where $a/has/@idref = $b/@id
Return $a, $b

EN
For $a in //{c1}shifts
For $b in //{c2}restaurant
For $b in //{c1}corporation[@id = "0"]//{c1}restaurant
Where $a/{c1}has = $b/{c2}has
Return $a, $b

MCMR, DR
For $a in //{c2}corporation[@id = "0"]//{c2}restaurant
For $b in $a/{c2}shifts
Return $b, $a

Q10 List all shifts according to their work’s description
DEEP
For $a in distinct-values( //work/description)
Let $b := For $c in //work
        For $d in $c/shifts
       Where $c/description = $a
       Return $d
Return $a, $b

AF, SHALLOW
For $a in distinct-values(//work/description)
Let $b := For $c in //shifts
	  For $d in //work
	  Where $d/description = $a
	And $d/@idref = $c/@id
	Return $c
Return $a, $b

EN, MCMR, DR
For a in distinct-values(//{c2}work/{c2}description)
Let $b := For $c in //{c2}shifts
	  Where $c/{c2}work/{c2}description = $a
	 Return $c
Return $a, $b

Q11 For each shift, show the managers currently manage it whose income > 10 times of hourly shift
DEEP, AF
For $a in //manager
For $b in $a//shifts
Where $a/income > 10*$b/hourly
Return $b, $a

SHALLOW
For $a in //shifts
For $b in //manager
Where $b/income > 10*$a/hourly
And $a/@id = $b/manages/@idref
Return $a, $b

EN, MCMR, DR
For $a in //{c1}manager
For $b in $a//{c1}shifts
Where $a/{c1}income > 10*$b/{c1}hourly
Return $b, $a

Q12 For each shift, show the managers currently manage it, whose income > 10 times of hourly shift, and manage more than 5 employees
DEEP, AF
For $a in //manager[income > 100K]
For $b in $a//shifts
Let $c := $a//employees
Where $a/income > 10*$b/hourly
And count($c) > 5
Return $b, $a

SHALLOW
For $a in //shifts
For $b in //manager[income > 100K]
Let $c := //employees
Where $b/income > 10*$a/hourly
And $a/@id = $b/manages/@idref
And $c/@id = $b/man/@idref
And count($c) > 5
Return $a, $b

EN, MCMR, DR
For $a in //{c1}manager[{c1}income > 100K]
For $b in $a//{c1}shifts
Let $c := $a//{c1}employees
Where $a/{c1}income > 10*$b/{c1}hourly
And count($c) > 5
Return $b, $a

Q13 List the name of managers for corporation id = 0,
DEEP, AF
For $a in //corporation[@id = "0"]//manager
Return $a/name

SHALLOW
For $a in //corporation[@id = "0"]
For $b in //manager
Where $a/employs/@idref = $b
Return $b/name

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

Q14 Return the name of all managers whose name contains the word "David"
DEEP, AF, SHALLOW
For $a in //manager
Where CONTAINS ($a/name, "David")
Return $a/name

EN, MCMR, DR
For $a in //{c1}manager
Where CONTAINS ($a/{c1}name, "David")
Return $a/{c1}name

Q15 Print the employment records of all restaurant
DEEP
For $a in DISTINCT //restaurant
Let $b := //employs
Where $b/restaurant/@idref = $a/@id
Return $a, $b

AF, SHALLOW
For $a in //restaurant
Let $b := //employs
Where $b/@idref = $a/@id
Return $a, $b

EN, MCMR, DR
For $a in //{c2}restaurant
Return $a, $a/{c2}employs

Q16 Return those restaurants that have ten or more employees
DEEP
For $a in DISTINCT //restaurant
Let $b := //employs
Where $b/restaurant/@id = $a/@id
And count ($b) > 10
Return $a

AF, SHALLOW
For $a in //restaurant
Let $b := //employs
Where $b/@idref = $a/@id
And count($b) > 10
Return $a

EN, MCMR, DR
For $a in //restaurant
Let $b := $a/employs
Where count ($b) >10
Return $a

Q17  which shift doesn’t have hourly
DEEP
For $a in DISTINCT //shifts
Where EMPTY($a/hourly)
Return $a

AF, SHALLOW
For $a in //shifts
Where EMPTY($a/hourly)
Return $a

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

Q18 Convert the currency of employee salary to another currency
DEEP, AF, SHALLOW
FUNCTION CONVERT($v)
{
	RETURN 40 * $v
}
For $a in //employees
Return CONVERT($a/salary)

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

Q19 Give an alphabetically list of all managers along with their SS#
DEEP, AF, SHALLOW
For $a in //manager
ORDER BY $a/name
Return $a/name, $a/SS#

EN, MCMR, DR
For $a in //{c1}manager
ORDER BY $a/{c1}name
Return $a/{c1}name, $a/{c1}SS#

Q20 Group and count shifts by their manager’s experience
DEEP
<Research>
 COUNT(//manager[experience = "Research"]/manages/shifts)
</Research>
<Business>
COUNT(//manager[experience = "Business"]/manages/shifts)
</Business>

SHALLOW
<Research>
 COUNT($a in //manager[experience = "Research"]
	Let $b := //shifts
       Where $a/manages/@idref = $b/@id
       Return $b)

</Research>
<Business>
COUNT($a in //manager[experience = "Business"]
	Let $b := //shifts
       Where $a/manages/@idref = $b/@id
       Return $b)
</BB>

AF, 
<Research>
 COUNT(//manager[experience = "Research"]//shifts)
</Research>
<Business>
COUNT(//manager[experience = "Business"]//shifts)
</Business>

EN, MCMR, DR
<Research>
 COUNT(//{c1}manager[{c1}experience = "Research"]//{c1}shifts)
</Research>
<Business>
COUNT(//{c1}manager[{c1}experience = "Business"]//{c2}shifts)
</Business>

U1 Insert manager with id = 100, for corporation 1
DEEP, AF
For $a in //corporation[@id=1]
Insert into $a <employs><manager id=100></manager></employs>

SHALLOW
Insert <manager id=100></manager> into $a in //
Insert <employs idref=100/> into $b in //corporation[@id=1]

EN, MCMR, DR
For $a in //{c1}corporation[@id=1]
Insert into $a <employs><manager id=100></manager></employs>

U2 Delete the last work of employee id=0
DEEP, AF, SHALLOW
For $a in//employees[@id = "0"]
Delete $a/work/last()

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

U3 Replace the info of shift of restaurant no. 1
DEEP
For $a in //shifts[//restaurant/@id = "1"]
Replace $a/info with <info/>

SHALLOW, AF
For $a in //shifts
For $b in //restaurant[@id = "1"]
Where $a/has/@idref = $b
Replace $b/info with <info/>

EN
For $a in //{c2}restaurant
For $b in //{c1}shifts
Where $a/{c2}has = $b/{c1}has
Replace $b/{c1}info with <info/>

MCMR, DR
For $a in //{c2}restaurant[@id = "1"]//{c2}shifts
Replace $b/{c2}info with <info/>

U4 If corporation 3 exist replace name with "XX" Else insert a new corporation 3
DEEP, AF, SHALLOW
If For $a in //corporation[@id = "3"]
Then Replace $a/name/text() with "XX"
Else
Insert into // <corporation id=3></corporation>

EN, MCMR, DR
If For $a in //corporation[@id = "3"]
Then Replace $a/name/text() with "XX"
Else
Insert into // <corporation id=3></corporation>

U5 For all employees, insert a count of total number works as new element
DEEP, AF, SHALLOW
For $a in //employees
Let $b := $a/work
Insert <count>count($b) into $a

EN, MCMR, DR
For $a in //{c1}employees
Let $b := $a/{c1}work
Insert <count>count($b) into $a

U6 Delete all employees’ work for the shift id 222
DEEP
For $a in //employees
Where $a//shifts/@id = "222"
Delete $a

AF, SHALLOW
For $a in //employees
For $b in //shifts[@id = "222"]
Where $a/work/@idref = $b/@id
Delete $a

EN, MCMR, DR
For $a in//{c1}employees
For $b in //{c2}shifts[@id = "222"]
Where $a/{c1}work = $/{c2}work
Delete $a

U7 Remove corporation id = 111 remove all information connected to it
DEEP, AF
For $a in //corporation[@id = "111"]
Delete $a

SHALLOW
For $a in //corporation
For $b in //restaurant
For $c in //manager
For $d in //employees
For $e in //shifts
Where $a/owns = $b/@id
And $a/employs = $c/@id
And $e/has = $b/@id
And $c/man = $d/@id
And $c/manages = $e/@id
And $d/work = $e/@id
And $d/employs = $b/@id
Delete $a, $b, $c, $d, $e

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

U8 For each shift compute the total hours and insert it into work profile
DEEP
For $a in DISTINCT(//shifts)
For $b in //work
Let $c := $a/hours
Where $b/shifts/@id = $a/@id
Insert into $b <total>COMPUTE($c)</total>

SHALLOW, AF
For $a in //shifts
For $b in //work
Let $c := $a/hours
Where $b/@idref = $a
Insert into $b <total>COMPUTE($c)</total>

EN, MCMR, DR
For $a in //{c2}shifts
Let $b:= $a/{c2}hours
Insert into $a/{c2}work <total>COMPUTE($b)</total>