ER2 Department Relationships

Source: example from an ER drawing program
ER characteristics
14 entities
15 relationships: 8 of 1:1, 6 of 1:m, 1 of m:n
XML Schema
DEEP
SHALLOW
EN: 2 colors
MCMR: 2 colors
DR: 6 colors
Q1 Return the name of the Development branch id = 0 for CEO id =0
DEEP, AF
For $a in //CEO[@id = "0"]
For $b in $a//Development_branch[@id = "0"]
Return $b/name

SHALLOW
For $a in //CEO[@id = "0"]
$b in //Development_branch[@id = "0"]
where $a/manages2/@idref = $b/@id
return $b/name

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

Q2 Return all employees that share
DEEP
For $a in //share
Return $a/employees5

AF, SHALLOW
For $a in //share
For $b in //employees5
Where $a/@idref = $b/@id
Return $b

EN, MCMR, DR 
For $a in //{c1}share
Return $a/{c1}employees6

Q3 Return the ids of all the employees in QCC who has department Share starting in QCC (@ in contains) before content branch
DEEP
For $a in //QCC/contains5
For $b in //content_branch/contains6
For $c in //share
For $d in $a/employees5
For $e in $b/employees6
where $d/@id = $ c/employees5/@id
and $e/@id = $ c/employees6/@id
and $a/date < $b/date
return $d/@id

AF
For $a in //QCC/contains5
For $b in //content_branch
For $v in $b/contains6
For $c in //share
For $d in $a/employees5
For $e in $v/employees6
where $d/@id = $ c/@employees5_idref
and $e/@id = $ c /@employees6_idref
and $b/@id = $c/@content_branch_idref
and $a/date < $v/date
return $d/@id

SHALLOW
For $a in //QCC
For $b in //content_branch
For $c in //share
For $d in //employees5
For $e in //employees6
where $d/@id = $ c/@employees5_idref
and $e/@id = $ c/@employees6_idref
and $b/@id = $c/@content_branch_idref
and $d/contains5/date < $e/contains6/date
and $a/@id = $d/contains5/@idref
and $b/@id = $e/contains6/@idref
return $d/@id

EN, MCMR
For $a in //{c1}QCC
For $b in $a/{c1}contains5
For $c in //{c2}contains6
For $d in //{c2}share
For $e in $a/{c1}share
Where $b/{c1}date < $c{c2}/date
And $d = $e
And $d/{c2}employees6 = $e/{c1}employees6
And $b/{c1}employees5 = $d/{c2}employees5
Return $b/{c1}employees5/@id

DR
For $a in //{c5}QCC
For $b in //{c6}share
For $c in //{c5}content_branch
For $d in $a/{c5}contains5
For $e in $c/{c5}contains6
Where $d/{c5}date < $e{c5}/date
And $d/{c5}employees5 = $b/{c6}employees5
And $e/{c5}employees6 = $b/{c6}employees6
Return $d/{c5}employees5/@id

Q4 List QCC where average employee salary = 10K
DEEP, AF
For $a in //QCC
Let $b := $a/employees5
Where AVG($b/salary) = 10K
Return $a

SHALLOW
For $a in //QCC
Let $b := //employees5
Where $b/contains5/@idef = $a/@id
and AVG($b/salary) = 10K
Return $a

EN, MCMR, DR
For $a in //{c1}QCC
Let $b := $a/{c1}employees5
Where AVG($b/{c1}salary) = 10K
Return $a

Q5 How many vice presidents have salary more than 90K
DEEP, AF, SHALLOW
COUNT(For $a in //vice_president[salary > 90K] return $a)

EN, MCMR, DR
COUNT(For $a in //{c1}vice_president[salary > 90K] return $a)

Q6 How many accountings managed by CEO in Chicago
DEEP, AF
COUNT(For $a in //CEO[address= "Chicago"]
For $b in $a//accounting
Return $b)

SHALLOW
COUNT(For $a in //CEO[address= "Chicago"]
For $b in //accounting
where $a/manages1/@idref = $b/@id
return $b)

EN, MCMR, DR
COUNT(For $a in //{c1}CEO[address= "Chicago"]
For $b in $a//{c1}accounting
Return $b)

Q7 How many pieces of name in the database
DEEP, AF, SHALLOW
For $a in //
Return count($a//employees1) + count($a//employees2) + count($a//employees3) + count($a//employees4)+ count($a//employees5)+ count($a//employees6)

EN, MCMR, DR
For $a in //
Return count($a//{c1}employees1) + count($a//{c1}employees2) + count($a//{c1}employees3) + count($a//{c1}employees4)+ count($a//{c1}employees5)+ count($a//{c1}employees6)


Q8 List the name of vice president and the content branch they managed
DEEP, AF
For $a in //vice_president
Return $a, $a//content_branch

SHALLOW
For $a in //vice_president
$b in //content_branch
Where $a/manages6/@idref = $b/@id
Return $a, $b

EN, MCMR, DR
For $a in //{c1}vice_president
Return $a, $a//{c1}content_branch

Q9 List the name of vice president and the content branch they managed under CEO in Chicago
DEEP, AF
For $a in //CEO[city="Chicago"]//vice_president
Return $a, $a//content_branch

SHALLOW
For $a in //CEO[city= "Chicago"]
For $b in //vice_president
For $c in //content_branch
where $a/manages7/@idref = $b/@id
and $b/manages6/@idref = $c/@id
Return $a, $c

EN, MCMR, DR
For $a in //{c1}CEO[{c1}city="Chicago"]//{c1}vice_president
Return $a, $a//{c1}content_branch


Q10 List all Development branches according to CEO’s city
DEEP, AF
For $a in //CEO/city/text()
Let $b := For $c in //CEO[city = $a]
	  For $d in c//Development_branch
 	  Return $d
Return <city>
	$a
	$b
	</city>

SHALLOW
For $a in //CEO/city/text()
Let $b := For $c in //CEO[city = $a]
	  For $d in //Development_branch
	   Where $c/manages2/@idref = $d/@id
 	  Return $d
Return <city>
	$a
	$b
	</city>

EN, MCMR, DR
For $a in //{c1}CEO/{c1}city/text()
Let $b := For $c in //{c1}CEO[{c1}city = $a]
	  For $d in c//{c1}Development_branch
 	  Return $d
Return <city>
	$a
	$b
	</city>

Q11 For each QCC, show the Development branch work closely with where starting work closely 1 year after the start of QCC department start itself
DEEP
For $a in //QCC
For $b in $a/work_closely[start_date > $a/start_date]/Development_branch
Where count($b) > 0
Return $a, $b

AF, SHALLOW
For $a in //QCC
For $b in //Development_branch
Where $b/work_closely/@idref = $a/@id
And $b/work_closely/startdate > $a/start_date
And count($b) > 0
Return $a, $b

EN
For $a in //{c1}Development_branch 
For $b in $a//{c2}QCC
Where $b/{c1}work_closely/{c1}start_date  > $b/{c2}start_date
And count($a) > 0
Return $b, $a

MCMR, DR
For $a in //{c2}QCC
For $b in $a/{c2}work_closely[{c2}start_date > $a/{c2}start_date]/ {c2}Development_branch
Where count($b) > 0
Return $a, $b

Q12 For each QCC that has  number of employees more than 10, list the number of Development branch work closely with where starting work closely 1 year after the start of QCC department start itself
DEEP
For $a in //QCC
For $b in $a/work_closely[start_date > $a/start_date]/Development_branch
Let $c := $a//employees5
Where count($b) > 0, and count($c) >10
Return $a, $b

AF
For $a in //QCC
$b in //Development_branch
Let $c := $a//employees5
Where $b/work_closely/@idref = $a/@id
And $b/work_closely/start_date > $a/start_date
And count($b) > 0
and count($c) >10
Return $a, $b

SHALLOW
For $a in //QCC
$b in //Development_branch
Let $c := //employees5
Where $b/work_closely/@idref = $a/@id
And $c/contains5/@idref = $a/@id
And $b/work_closely/start_date > $a/start_date
And count($b) > 0
and count($c) >10
Return $a, $b

EN
For $a in //{c1}Development_branch 
For $b in $a//{c1}QCC
Let $c := $b//{c1}employees5
Where $b/{c1}work_closely/{c1}start_date  > $b/{c2}start_date
And count($a) > 0
and count($c) >10
Return $b, $a

MCMR, DR
For $a in //{c2}QCC
For $b in $a/{c2}work_closely[{c2}start_date > $a/{c2}start_date]/ {c2}Development_branch
Let $c := $a//{c2}employees5
Where count($b) > 0, and count($c) >10
Return $a, $b

Q13 List the name of accounting with CEO id=2 along with their address
DEEP, AF
For $a in //CEO[id = "2"]//accounting
Return $a, $a/address

SHALLOW
For $a in //CEO[id = "2"]
For $b in accounting
Where $a/manages1/@idref = $b/@id
Return $b, $b/address

EN, MCMR, DR
For $a in //{c1}CEO[id = "2"]//accounting
Return $a, $a/{c1}address

Q14 Return the name of all marketing whose address contains the word "NY"
DEEP, AF, SHALLOW
For $a in //marketing
Where CONTAINS($a/address,NY)
Return $a/name

EN, MCMR, DR
For $a in //{c1}marketing
Where CONTAINS($a/{c1}address,NY)
Return $a/{c1}name

Q15 Print the vice president of each CEO
DEEP, AF
For $a in //CEO
Return $a//vice_president

SHALLOW
For $a in //CEO
For $b in //vice_president
Where $a/manages7/@ref =$b/@id
Return $b

EN, MCMR, DR
For $a in //{c1}CEO
Return $a//{c1}vice_president

Q16 Return the id of those vice presidents that managed 3 departments at the same time
DEEP, AF, SHALLOW
For $a in //vice_president
Where NOT EMPTY($a/manages3)
And NOT EMPTY($a/manages4)
And NOT EMPTY($a/manages6)
Return $a/@id

EN, MCMR, DR
For $a in //{c1}vice_president
Where NOT EMPTY($a/{c1}manages3)
And NOT EMPTY($a/{c1}manages4)
And NOT EMPTY($a/{c1}manages6)
Return $a/@id

Q17 Which QCC doesn’t have shared employee?
DEEP
For $a in //QCC
For $b in //share
Where EMPTY ($b/QCC[@id = $a/@id])
Return $a

AF, SHALLOW
For $a in //QCC
Where EMPTY($a/share)
Return $a

EN, MCMR, DR
For $a in //{c1}QCC
Where EMPTY($a/{c1}share)
Return $a

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

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


Q19 Give an alphabetically ordered list of all marketing along with there address
DEEP, AF, SHALLOW
For $a in //marketing
ORDER BY $a/name
Return $a, $a/address

EN, MCMR, DR
For $a in //{c1}marketing
ORDER BY $a/{c1}name
Return $a, $a/{c1}address

Q20 Group CEO by their income and output cardinality for each group
DEEP, AF, SHALLOW
<result>
 <high>
	COUNT(//CEO[income > 100K])
</high>
<low>
COUNT(//CEO[income <= 100K])
</low>
<na>
COUNT (For $a in //CEO
 Where EMPTY ($a/income)
Return $a)
</na>

EN, MCMR, DR
<result>
 <high>
	COUNT(//{c1}CEO[{c1}income > 100K])
</high>
<low>
COUNT(//{c1}CEO[{c1}income <= 100K])
</low>
<na>
COUNT (For $a in //{c1}CEO
 Where EMPTY ($a/{c1}income)
Return $a)
</na>

U1 Insert an accounting department manage by CEO id 1
DEEP,AF
Insert <manages1><accounting/></manages1> under $a in //CEO[@id = "1"]

SHALLOW
Insert <account/> under $a in // get id
Insert <manages1 idref=id></> under $b in //CEO[@id = "1"]

EN,MCMR,DR
Insert <manages1><accounting/></manages1> under $a in //{c1}CEO[@id = "1"]

U2 Delete the last employee of marketing dept id =0

DEEP, AF
Delete $a in //marketing[@id = "0"]/contains3[last()]

SHALLOW
Delete $a in //employee[containts/@idref = "0"]

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

U3 Replace Development Branch location of CEO [id=0]
DEEP,AF
For $a in //CEO[@id = "0"]
Replace $a//Development_branch/location with <location>..</location>

SHALLOW 
For $a in //CEO[id = "0"]
For $b in //Development_branch
where $b/@id = $a/manages2/@idref
Replace $b/location with <location></location>

EN,MCMR,DR
For $a in //{c1}CEO[@id = "0"]
Replace $a//{c1}Development_branch/{c1}location with <location>..</location>

U4 If CEO[id = 100] exists, change its name to "David", otherwise insert a new CEO in the CEO list
DEEP,AF,SHALLOW
If $a in //CEO[@id = "100"] exists
Then replace $a/name with <name>David</name>
Else insert <CEO id = "100"></> to $b in //

EN, MCMR, DR
If $a in //{c1}CEO[@id = "100"] exists
Then replace $a/{c1}name with <name>David</name>
Else insert <CEO id = "100"></> to $b in //

U5 For all QCC iinsert a count of a total number of employees as new employees count
DEEP, AF, EN, MCMR, DR (color5)
Update For $a in //QCC
Insert <employees_count> {COUNT($a//employees5)</employees_count> as last into $a

SHALLOW
Update For $a in //QCC
	Let $b := //employees5
	Where $a/contains5/@idref = $b/@id
Insert <employees_count> {COUNT($b)</employees_count> as last into $a

EN, MCMR, DR
Update For $a in //{c1}QCC
Insert <employees_count> {COUNT($a//{c1}employees5)</employees_count> as last into $a

U6 Delete all employees worked for QCC id =1
DEEP, AF
For $a in //QCC[@id = "1"]
Delete $a//employees5

SHALLOW
For $a in //QCC[@id = "1"]
Let $b := //employees5
Where $b/@id = $a/contains5
Delete $b

EN, MCMR, DR
For $a in //{c1}QCC[@id = "1"]
Delete $a//{c1}employees5

U7 Remove CEO id 3 . remove all department/employees connect to it
DEEP, AF, 
Delete subtree $a in //CEO[id = "3"]

SHALLOW
For $a in //CEO
 $b in //accounting
….
…
(14 variables, 13 value-join conditions = 28 path expressions
delete every variables)

EN, MCMR, DR
Delete subtree $a in //{c1}CEO[id = "3"]

U8 For each content branch compute the number of employees that share with QCC and insert it into contains6

DEEP
For $a in //content_branch
Let $b := $a//share
Insert <count>COUNT($b) </count> into $a/contains6

AF, SHALLOW
For $a in //content_branch
Let $b := //share
Where $a/@id - $b/idref
Insert <count>COUNT($b) </count> into $a/contains6

EN, MCMR, DR
For $a in //{c1}content_branch
Let $b := $a//{c1}share
Insert <count>COUNT($b)</count> into $a/{c1}contains6