ER8 Small Airport

Source: Fundamentals of Database Systems, Ramez Elmasri, Shamkant B. Navathe

ER characteristics

9 entities
10 relationships: 3 of 1:m, 4 of m:n, 3 of ISA

XML Schema

DEEP

AF

SHALLOW

EN, MCMR, DR: 3 colors


Q1 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