ER3 TPC-C, TPC-H

ER characteristics
6 entities
6 relationships: 4 of 1:m, 2 of m:n
XML Schema
DEEP
SHALLOW
EN: 2 colors
MCMR: 2 colors
DR: 3 colors
Q1 Return the order info of id = 0
DEEP, AF, SHALLOW
For $a in //order[@id = "0"]
Return $a

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

Q2 Return the first lineitem price of all partsupplier
DEEP, AF
For $a in //partsupplier
Return $a/lineitem[1]/price

SHALLOW
For $a in //partsupplier
Let $b := //lineitem
For $c in //lineitem
Where $a/@id = $b/@idref
And $c/date_invoiced = MIN($b/date_invoiced)
And $a/@id = $c/@idref
Return $c

EN, MCMR, DR
For $a in //{c1}partsupplier
Return $a/{c1}lineitem[1]/{c1}price

Q3 Return the id of all partsupplier whose lineitem average discount to 5%
DEEP, AF
For $a in //partsupplier
Let $b :=  $a/lineitem
Where AVG($b/discount) = 5%
Return $a/@id

SHALLOW
For $a in //partsupplier
Let $b := //lineitem
Where $a/@id = $b/@idref
And AVG($b/discount) = 5%
Return $a

EN, MCMR, DR
For $a in //{c1}partsupplier
Let $b :=  $a/{c1}lineitem
Where AVG($b/{c1}discount) = 5%
Return $a/@id

Q4 List the number in stock of all partsupplier where a certain customer order before another
DEEP
For $a in //customer[@id = "1"]//order
For $b //customer[@id = "2"]//order
where $a/date < $b/date
return $a/partsupplier/stock_number

AF
For $a in //partsupplier
For $b in //customer[@id = "1"]//order
For $c in //customer[@id = "2"]//order
Where $b/date < $c/date
And $a/lineitem/@idref = $b/@id
Return $a/stock_number

SHALLOW
For $a in //partsupplier
For $b in //order
For $c in //order
For $d in //customer[@id = "1"]
For $e in //customer[@id= "2"]
Where $d/make/@idref = $b/@id
And $e/make/@idref = $c/@id
And $b/date < $c/date
And $b/lineitem/@idref = $a/@id
return $a/stock_number

EN
For $a in //{c1}customer[@id = "1"]//{c1}order
For $b in //{c1}customer[@id = "2"]//{c1}order
For $a in //{c2}order
For $c in //{c1}partsupplier
Where $a/{c1}date < $b/{c1}date
And $a/{c2}lineitem = $c/{c1}lineitem
Return $c/{c1}stock_number

MCMR,DR
For $a in //{c2}customer[@id = "1"]//{c2}order
For $b in //{c2}customer[@id = "2"]// {c2}order
For $c //{c1}partsupplier
Where $a/{c2}date < $b/{c2}date
And $a/{c2}lineitem = $c/{c1}lineitem
return $c/{c1}stock_number

Q5 How many lineitem sold more than $40 ?
DEEP, AF, SHALLOW
COUNT(For $a //lineitem
Where $a/price >= 40
Return $a)

EN,MCMR,DR
COUNT(For $a //{c1}lineitem
Where $a/{c1}price >= 40
Return $a)

Q6 How many supplier for each region?
DEEP, AF
For $a //region
Return $a, COUNT($a//supplier)

SHALLOW
For $a //region
Let $b := For $c in //nation
	 Let $d := //supplier 
	where $c/@id = $a/group/@idref
	and $d/@id = $c/contains/@idref
	return $d
return $a, count($b)

EN, MCMR, DR
For $a //{c1}region
Return $a, COUNT($a//{c1}supplier)

Q7 How many pieces of prose in our database?
DEEP
For $a in //
Return count($a/region) + count($a/nation) + count($a/supplier) + count($a/customer) + count($a/order) + count(distinct($a/part))

AF, SHALLOW
For $a in //
Return count($a/region) + count($a/nation) + count($a/supplier) + count($a/customer) + count($a/order) + count($a/part)

EN, MCMR, DR
For $a in //
Return count($a/{c1}region) + count($a/{c1}nation) + count($a/{c1}supplier) + count($a/{c1}customer) + count($a/{c1}order) + count($a/{c1}part)

Q8 List the part name and the number of times they are ordered
DEEP
For $a in DISTINCT //part
Let $b := For $c in //partsupplier
	  Let $d := $b/lineitem
	  Where $c/part/@id = $a/@id
	  Return $d
Return $a/name, count($b)

AF
For $a in //part
Return $a/name, count($a//lineitem)

SHALLOW
For $a in //part
Let $b := //lineitem
Where $b/@idref = $a/partsupplier/@id
Return $a/name, count($b)

EN, MCMR, DR
For $a in //{c1}part
Return $a/{c1}name, count($a//{c1}lineitem)

Q9 List the part name and the name of supplied supplier in Europe
DEEP
For $a in DISTINCT //part
Let $b := For $c in //region[name = "Europe"]//supplier
	  Where $c//part/@id = $a/@id
	  Return $c
Return $a/name, $b

AF
For $a in //part
Let $b := For $c in //region[name= "Europe"]//supplier
	  Where $c/@id = $a/partsupplier/@idref
	  Return $c
Return $a/name, $b

SHALLOW
For $a in //part
Let $b := For $c in //region[name = "Europe"]
	 For $d in //nation
	For $e in //supplier
	where $c/group/@idref = $d/@id
	and $d/contains/@idref = $e/@id
	and $e/@id = $a/partsupplier/@id
	return $e
return $a/name, $b

EN
For $a in //{c1}part
Let $b := For $c in //{c1}region[{c1}name = "Europe"]// {c1}supplier
	  For $c in //{c2}supplier
	  Where $c/{c2}partsupplier = $a/{c1}partsupplier
	  Return $c
Return $a/{c1}name, $b


MCMR, DR
For $a in //{c1}part
Let $b := For $c in //{c2}region[{c2}name = "Europe"]// {c2}supplier
	  Where $c/{c2}partsupplier = $a/{c1}partsupplier
	  Return $c
Return $a/{c1}name, $b

Q10 List all orders according to their customer’s nation
DEEP, AF
For $a in //nation
Return $a, $a//order

SHALLOW
For $a in //nation
Let $b := For $c in //customer
	For $d in //order
	 Where $a/has/@idref = $c/@id
	And $c/make/@idref = $d/@id
	Return $d
Return $a, $b

EN, MCMR, DR
For $a in //{c1}nation
Return $a, $a//{c1}order

Q11 For each order list the item (partsupplier) whose price take 50% of the whole order
DEEP
For $a in //order
Let $b := For $c in $a//partsupplier
	 Where $c/price >= 0.5 * $a/total
	 Return $c
Return $a, $b

AF
For $a in //order
Let $b := For $c in //partsupplier
	 Where $c/price >= 0.5 * $a/total
	 And $c/lineitem/@idref = $a/@id
	 Return $c
Return $a, $b

SHALLOW
For $a in //order
Let $b := For $c in //partsupplier
	 Where $c/price >= 0.5 * $a/total
	 And $c /@id = $a/lineitem/@idref
	 Return $c
Return $a, $b

EN, MCMR,DR
For $a in //{c2}order
Let $b := For $c in //{c1}partsupplier
	 Where $c/{c1}price >= 0.5 * $a/{c1}total
	 And $c/{c1}lineitem = $a/{c2}lineitem
	 Return $c
Return $a, $b


Q12 For each order above 100$ list the item (partsupplier) whose price take 50% of the whole order
DEEP
For $a in //order[total > 100]
Let $b := For $c in $a//partsupplier
	 Where $c/price >= 0.5 * $a/total
	 Return $c
Return $a, $b

AF
For $a in //order[total > 100]
Let $b := For $c in //partsupplier
	 Where $c/price >= 0.5 * $a/total
	 And $c/lineitem/@idref = $a/@id
	 Return $c
Return $a, $b

SHALLOW
For $a in //order[total > 100]
Let $b := For $c in //partsupplier
	 Where $c/price >= 0.5 * $a/total
	 And $c /@id = $a/lineitem/@idref
	 Return $c
Return $a, $b

EN, MCMR,DR
For $a in //{c2}order[{c2}total > 100]
Let $b := For $c in //{c1}partsupplier
	 Where $c/{c1}price >= 0.5 * $a/{c1}total
	 And $c/{c1}lineitem = $a/{c2}lineitem
	 Return $c
Return $a, $b

Q13 List the name of supplier in Australia along with their description
DEEP, AF
For $a in //nation[name = "Australia"]//supplier
Return $a, $a/description

SHALLOW
For $a in //nation[name = "Australia"]
For $b in //supplier
where $a/contains/@idref = $b/@id
Return $b, $b/description

EN, MCMR, DR
For $a in //{c1}nation[{c1}name = "Australia"]// {c1}supplier
Return $a, $a/{c1}description

Q14 Return the name of all supplier whose description contains the word "Limited Company"
DEEP, AF, SHALLOW
For $a in //supplier
Where CONTAINS ($a/description,"LIMITED COMPANY")
Return $a/name/text()

EN, MCMR, DR
For $a in //{c1}supplier
Where CONTAINS ($a/{c1}description,"LIMITED COMPANY")
Return $a/{c1}name/text()

Q15 Print the lineitem’s price
DEEP, AF, SHALLOW
For $a in //lineitem
Return $a/price

EN, MCMR, DR
For $a in //{c2}lineitem
Return $a/{c2}price

Q16 Return the ID of those lineitem that oordered by a hugh price order
DEEP, AF, SHALLOW
For $a in //order[total > 10000]//lineitem
Return $a

EN, MCMR, DR
For $a in //{c2}order[{c2}total > 10000]// {c2}lineitem
Return $a

Q17 Which part do not have supplier?
DEEP
For $a in DISTINCT //part
Let $b := For $c in //partsupplier
	  Where $a/part/@id = $a/@id
	  Return $c
Where EMPTY ($b)
Return $a

AF, SHALLOW
For $a in //part
Where EMPTY($a/partsupplier)
Return $a

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

Q18 Convert the currency of all partsupplier’s price to another currency
DEEP, AF, SHALLOW
FUNCTION CONVERT($v)
{
	RETURN 40 * $v
}
For $i in //partsupplier
RETURN CONVERT ($i/price)

EN, MCMR, DR
FUNCTION CONVERT($v)
{
	RETURN 40 * $v
}
For $i in //{c1}partsupplier
RETURN CONVERT ($i/{c1}price)

Q19 Give an alphabetically orderd list of all supplier along with their address
DEEP, AF, SHALLOW
For $a in //supplier
ORDER BY $a/name
Return $a, $a/address

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

Q20 Group order by their total price and output cardinality
DEEP, AF, SHALLOW
<result>
<high>
	COUNT(//order[total> 1000])
</high>
<low>
	COUNT(//order[total<= 1000])
</low>
</result>

EN, MCMR, DR
<result>
<high>
	COUNT(//{c1}order[{c1}total> 1000])
</high>
<low>
	COUNT(//{c1}order[{c1}total<= 1000])
</low>
</result>

U1 Insert supplier with ID= 100 of USA into database
DEEP, AF
Insert as last child of $a //nation[name = "USA"] <contains><supplier @id="100">..</supplier></contains>

EN, MCMR, DR
Insert as last child of $a //{c1}nation[{c1}name = "USA"] <contains><supplier @id="100">..</supplier></contains>


SHALLOW
Insert <supplier id = "100"/> into $a in //
Insert <contains @idref = "100"/> into $b in //nation[name = "USA"]

U2 Delete the last partsupplier of part id = 0
DEEP
For $a in //part[id = "0"]
For $c in //partsupplier
Let $b := //partsupplier
Where $b/part/@id = $a/@id
$c/date = MAX ($b/date)
Delete $c


AF, SHALLOW
Delete $a //part[id=0]/partsupplier/last()

EN, MCMR, DR
Delete $a //{c1}part[id=0]/ {c1}partsupplier/last()

U3 Replace part information of part no. 1
DEEP, AF, SHALLOW
Replace $a in //part[id = "1"]/info with <info/>

EN, MCMR, DR
Replace $a in //{c1}part[id = "1"]/{c1}info with <info/>

U4 If nation 1 exists change its name to Thailand
Otherwise insert a new nation (in Asia (region))
DEEP, AF
If For $a in //nation[@id = "1"] EXIST
Then Replace $a/name with <name = "Thailand">
Else
For $b in //region[Asia]
Insert <group><nation>Thailand</></> into last element of $b

SHALLOW
If For $a in //nation[@id = "1"] EXIST
Then Replace $a/name with <name = "Thailand">
Else
Insert <nation>Thailand</nation into $b in //
Insert <group> into $c in //region[ASIA]

EN, MCMR, DR
If For $a in //{c1}nation[@id = "1"] EXIST
Then Replace $a/{c1}name with <name = "Thailand">
Else
For $b in //{c1}region[Asia]
Insert <group><nation>Thailand</></> into $b

U5 For all partsupplier inset a count of the total number of lineitems as new element
DEEP, AF
For $a in //partsupplier
Let $b := $a/lineitem
Insert COUNT($b) into $a/lastchild()

SHALLOW
For $a in //partsupplier
Let $b := //lineitem
Where $b/@idref = $a/@id
Insert COUNT($b) into $a/lastchild()

EN, MCMR, DR
For $a in //{c1}partsupplier
Let $b := $a/{c1}lineitem
Insert COUNT($b) into $a/lastchild()

U6 Delete all partsupplier for a part id = 2
DEEP
For $a in //partsupplier[part/@id = "2"]
For $b in //lineitem 
where $b/partsupplier = $a
Delete $a, $b
Insert //part[id = "2"]

AF
For $a in //part[@id = "2"]/partsupplier
Delete $a

SHALLOW
For $a in //part[@id = "2"]/partsupplier
Let $b := //lineitem[@idref = $a/@id]
Delete $a, $b

EN, MCMR, DR
For $a in //{c1}part[@id = "2"]/{c1}partsupplier
Delete $a

U7 Remove nation id = 8 Remove all associations to it
DEEP, AF
For $a in //nation[id = "8"]
Delete $a
Move $a/customer to //
Move $a//supplier to //

SHALLOW
For $a in //nation[id = "8"]
Let $b := //group[@idref = "8"]
Delete $a, $b

EN, MCMR, DR
For $a in //{c1}nation[id = "8"]
Delete $a

U8 For each part, compute the total ordered price and insert it into it’s information
DEEP
For $a in DISTINCT //part
Let $b := For $c in //partsupplier
	  Let $d = $c/lineitem   
       Where $c/part/@id = $a/@id
        Return $d
Insert <sum>SUM($b)</sum> into $a
	  
AF
For $a in //part
Let $b := $a//lineitem
Insert <sum>SUM($b)</sum> into $a

SHALLOW
For $a in //part
Let $b in //lineitem
Where $b/@idref = $a/partsupplier
Insert <sum>SUM($b)</sum> into $a

EN, MCMR, DR
For $a in //{c1}part
Let $b := $a//{c1}lineitem
Insert <sum>SUM($b)</sum> into $a