ER5 Testing Software and Test Score Database

Source: example from an ER drawing program
ER characteristics
10 entities
13 relationships: 1 of 1:1, 7 of 1:m, 5 of m:n
XML Schema
DEEP
SHALLOW
EN: 2 colors
MCMR: 2 colors
DR: 3 colors
Q1 Return the name of student id = 0
DEEP, AF, SHALLOW
For $a in //student [id = "0"]
Return $a/name/text()

EN, MCMR, DR
For $a in //{c1}student [id = "0"]
Return $a/{c1}name/text()

Q2 Return the first enroll of all class
DEEP, AF
For $a in //class
Return $a/enrolled[first()]

SHALLOW
$a in //class
$b in //student/enrolled
Let $c := //student/enrolled
Where $b/@idref = $a/@id
And $c/@idref = $a/@id
And $b/date <= MIN ($c/date)
Return $b

EN, MCMR, DR
For $a in //{c1}class
Return $a/{c1}enrolled[first()]

Q3 Return the ids of all student whose first score at least twice last score
DEEP
For $a in //student
Where $a/has5/score/first() >= 2* $a/has5/score/last()
Return $a/@id

SHALLOW
For $a in //student
For $b in //score
For $c in //score
Where $b/@id = $a/has5[first()]/@idref
And $c/@id = $a/has5[last()]/@idref
And $b >= 2* $c
Return $a/@id

AF
For $a in //student
Where $a//score/first() >= 2* $a//score/last()
Return $a/@id

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

Q4 List the class where a certain teacher is a prime, another is secondary
DEEP
For $a in //class
Where EXISTS $a//teaches[type="prime"]/teacher[id = "1"] 
And EXISTS $a//teaches[type="secondary"]/teacher[id = "2"] 
Return $a

AF, SHALLOW
For $a in //class
For $b in //teacher[id = "1"]
For $c in //teacher[id = "2"]
Where $a/teaches[type = "prime"]/@idref = $b
And $a/teaches[type = "secondary"]/@idref = $c
Return $a

EN, MCMR, DR
For $a in //{c1}class
For $b in //{c2}teacher[@id = "1"]
For $c in //{c2}teacher[@id = "2"]
Where $a/{c1}teaches[type = "prime"] = $b/{c2}teaches
And $a/{c1}teaches[type = "secondary"] = $c/{c2}teaches
Return $a

Q5 How many grading are A?
DEEP, AF, SHALLOW
COUNT(For $a in //grade[value = "A"]
Return $a)

EN, MCMR, DR
COUNT(For $a in //{c2}grade[{c2}value = "A"]
Return $a)

Q6 How many teachers create equal or more than 5 tests?
DEEP
COUNT (For $a in DISTINCT //teacher
	  Let $b := $a//test
	  Where count($b) >= 5
	 Return $a)

AF
COUNT (For $a in //teacher
	  Let $b := $a//test
	  Where count($b) >= 5
	 Return $a)

SHALLOW
COUNT (For $a in //teacher
	 For $b := //test
	Where $b/@id = $a/has1/@idref
	Count ($b) >= 5
	Return $a)

EN, MCMR, DR
COUNT (For $a in //{c1}teacher
	  Let $b := $a//{c1}test
	  Where count($b) >= 5
	 Return $a)

Q7 How many pieces of prose in the database?
SHALLOW, AF
For $a in //
Return count($a//class) + count($a//teacher) + count($a//student)+ count($a//Software)

DEEP
For $a in //
Return count($a//class) + count(DISTINCT $a//teacher) + count(DISTINCT $a//student)+ count($a//Software)

EN, MCMR, DR
For $a in //
Return count($a//{c1}class) + count($a//{c1}teacher) + count($a//{c1}student)+ count($a//{c1}Software)

Q8 List the name of the student and the number of grade F they got
DEEP
For $a in distinct-values(//student)
Return $a/name, count($a//grade[value = "F"])

AF
For $a in //student
Return $a/name, count($a//grade[value = "F"])

SHALLOW
For $a in //student
Let $b := //grade[value = "F"]
Where $a/has6/@idref = $b/@id
Return $a/name, count($b)

EN, MCMR, DR
For $a in //{c1}student
Return $a/name, count($a//{c1}grade[{c1}value = "F"])

Q9 List the name of the student and the number of grade they got that correlate with the score generated from Software=MS
DEEP 
For $a in //student
Let $b :=  For $c in //grade
	For $d in //Software[company = "MS"]
	Where $c//score/@id = $d/score/@id
	Return $c
Return $a/name, count($b)

AF
For $a in //student
Let $b := For $c in $a//grade
	For $d in //score
	For $e in //Software[company = "MS"]
	Where $d/@id = $e/generates/@idref
	And $d/correlates/@idref = $c/@id
	Return $c
Return $a/name, count($b)

SHALLOW
For $a in //student
Let $b := For $c in //grade
	For $d in //score
	For $e in //Software[company = "MS"]
	Where $d/@id = $e/generates/@idref
	And $d/correlates/@idref = $c/@id
	And $c/@id = $a/has6/@idref
       Return $c
Return $a/name, count($b)

EN
For $a in //{c1}student
Let $b := For $c in $a/{c1}grade
	For $d in $c/{c2}correlates
	For $e in //{c2}score
 	For $f in $e/{c2}generates
	For $g in //{c1}Software[{c1}company = "MS"]
	Where $e/{c1}correlates = $d
	And $f = $g/{c1}generates
       Return $c
Return $a/{c1}name, count($b)

MCMR,DR
For $a in //{c2}student
Let $b := For $c in //{c1}score
	For $d in //{c2}Software[{c2}company = "MS"]
	Where $d//{c2}score = $c
	And $a/{c2}correlates = $c/{c1}correlates
	Return $c
Return $a/{c2}name, count($b)

Q10 List all students according to their student answer’s attribute
DEEP, AF
For $a in distinct-values(//student_answer/attribute)
Let $b := //student
Where $a//student_answer/attribute = $a
Return $a, DISTINCT($b)

EN, MCMR, DR
For $a in distinct-values(//{c1}student_answer/{c1}attribute)
Let $b := //{c1}student
Where $a//{c1}student_answer/{c1}attribute = $a
Return $a, DISTINCT($b)

SHALLOW 
For $a in distinct-values(//student_answer /attribute)
Let $b := For $c in //student
	For $d in // student_answer
	Where $d/attributes = $a
	And $d/@id = $c/gives/@idref
	Return $c
Return $a, DISTINCT($b)

Q11 For each student, list teachers who has him whose address is in the same city
DEEP, AF
For $a in //student
For $b in //teacher
Where $b/student/@id = $a/@id
And $b/address/city = $a/address/city
Return $a, $b

SHALLOW
For $a in //student
For $b in //teacher
Where $b/has1/@idref =$a/@id
And $b/address/city = $a/address/city
Return $a, $b

EN, MCMR, DR
For $a in //{c1}student
For $b in //{c1}teacher
Where $b/{c1}student = $a
And $b/{c1}address/{c1}city = $a/{c1}address/{c1}city
Return $a, $b

Q12 For each student in Michigan, list teachers who has him whose address is in the same city
DEEP, AF
For $a in //student[address/state = "MI"]
For $b in //teacher
Where $b/student/@id = $a/@id
And $b/address/city = $a/address/city
Return $a, $b

SHALLOW
For $a in //student[address/state = "MI"]
For $b in //teacher
Where $b/has1/@idref =$a/@id
And $b/address/city = $a/address/city
Return $a, $b

EN, MCMR, DR
For $a in //{c1}student[{c1}address/{c1}state = "MI"]
For $b in //{c1}teacher
Where $b/{c1}student = $a
And $b/{c1}address/{c1}city = $a/{c1}address/{c1}city
Return $a, $b

Q13 List the name of teacher produces at least 10 questions for each test
DEEP
For $a in DISTINCT //teacher
For $b in $a//test
For $c := $c//question
Where count($c) >= 10
Return $a/name

AF
For $a in //teacher
For $b in $a//test
For $c := $c//question
Where count($c) >= 10
Return $a/name

SHALLOW
For $a in //teacher
For $b in //test
For $c := $b/has2
Where count($c) >=10
And $a/create/@idref = $b/@id
Return $a/name

EN, MCMR, DR
For $a in //{c1}teacher
$b in $a//{c1}test
$c := $c//{c1}question
Where count($c) >= 10
Return $a/name

Q14 Return the name of all teachers whose description contains the word "Ph.D."
DEEP
For $a in DISTINCT //teacher
Where CONTAINS($a/description,"Ph.D.")
Return $a/name

SHALLOW, AF
For $a in //teacher
Where CONTAINS($a/description,"Ph.D.")
Return $a/name

EN, MCMR, DR
For $a in //{c1}teacher
Where CONTAINS($a/{c1}description,"Ph.D.")
Return $a/{c1}name

Q15 Print the score correlated to each grade of student id =1
DEEP
For $a in //student[@id = "1"]//grade
Return $a, $a/score

AF, SHALLOW
For $a in //student[@id = "1"]//grade
$b := //score
Where $b/correlates/@idref = $a/@id
Return $a, $b

EN
For $a in //{c1}student[@id = "1"]//{c1}grade
For $a in //{c2}grade
For $b := //{c1}score
Where $a{c2}/correlates = $b/{c1}correlates
Return $a, $b

MCMR,DR
For $a in //{c2}student[@id = "1"]//{c2}grade
For $b := //{c1}score
Where $a{c2}/correlates = $b/{c1}correlates
Return $a, $b

Q16 Return the grade whose at least one correct answer is A
DEEP
For $a in //grade
For $b in //correct_answer[answer = "A"]
For $c //compares
Where $b/compares = $c
And $c/score = $a/score
Return $a

AF
For $a in //grade
For $b in //score
For $c in //compare[correct_answer/answer = "A"]
Where $a/@id = $b/correlates/@idref
And $c//generates/@idref = $b/@id
Return $a

SHALLOW
For $a in //grade
For $b in //score
For $c in //Software
For $d in //student_answer
For $e in //correct_answer[answer = "A"]
Where $a/@id = $b/correlates/@idref
And $c//generates/@idref = $b/@id
And $d/compare/@idref = $c/@id
And $d/compare/@idref = $e/@id
Return $a

EN
For $a in //{c2}grade
For $b in //{c1}score
For $b in //{c2}score
For $c in //{c1}compares
Where $a/{c2}correlates = $b/{c1}correlates
And $b/{c2}generates = $c//{c1}generates
And $c/{c1}correct_answer/{c1}answer = "A"
Return $a

MCMR, DR
For $a in //{c2}grade
For $b in //{c1}score
For $c in //{c2}compares
Where $a/{c2}correlates = $b/{c1}correlates
And $b = $c//{c2}score
And $c/{c2}correct_answer/{c2}answer = "A"
Return $a

Q17 which student doesn’t have homepage?
DEEP
For $a in DISTINCT //student
Where EMPTY ($a/homepage)
Return $a

AF, SHALLOW
For $a in //student
Where EMPTY ($a/homepage)
Return $a

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

Q18 Convert the class room number to formatted floor + room
DEEP, AF, SHALLOW
FUNCTION CONVERT($v)
{
	RETURN expose($v, ",")
}
For $i in //class
RETURN CONVERT ($i/room_number)

EN, MCMR, DR
FUNCTION CONVERT($v)
{
	RETURN expose($v, ",")
}
For $i in //{c1}class
RETURN CONVERT ($i/{c1}room_number)

Q19 Give and alphabetically ordered list of all teachers along with the address
DEEP, AF, SHALLOW
For $a in // teacher
ORDER BY $a/name
Return $a, $a/address

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

Q20 Group student by their average student answer and output cardinality in each group
DEEP, AF
<result>
<A>
	count(For $a in //student
	Where 	AVG ($a//student_answer) = "A"
	Return $a)
</A>
<B>
	count(For $a in //student
	Where 	AVG ($a//student_answer) = "B"
	Return $a)


</B>
</result>

SHALLOW
<result>
<A>
	count(For $a in //student
	Let $b := //student_answer
	Where 	AVG ($b) = "A"
	And $b/@id = $a/gives/@idref
	Return $a)

</A>
<B>
	count(For $a in //student
	Let $b := //student_answer
	Where 	AVG ($b) = "B"
	And $b/@id = $a/gives/@idref
	Return $a)


</B>
</result>

EN, MCMR, DR
<result>
<A>
	count(For $a in //{c1}student
	Where 	AVG ($a//{c1}student_answer) = "A"
	Return $a)
</A>
<B>
	count(For $a in //student
	Where 	AVG ($a//{c1}student_answer) = "B"
	Return $a)


</B>
</result>

U1 Insert a new teacher into database
DEEP, SHALLOW, AF
Insert in into //
<teacher />

EN, MCMR, DR
Insert in into //{c1}
<teacher />

U2 Delete the last enroll student having class id = AA000
DEEP
For $a in //class[id = "aa000"] 
Delete $a/enrolled/last()
Move //student to //

AF
For $a in //class[id = "aa000"]
Delete $a/enrolled/last()

SHALLOW
For $a in //class[id = "aa000"]
For $b in //student/enrolled
Let $c := //student/enrolled
Where $b/@idref= $a
And $c/@idref = $a
And $b/date >= MAX ($c/date)
Delete $b

EN, MCMR, DR
For $a in //{c1}class[id = "aa000"]
Delete $a/{c1}enrolled/last()

U3 Replace interest of student id = 0
DEEP, AF, SHALLOW
Replace //student[id = "0"]/interest
With <interest/>

EN, MCMR, DR
Replace //{c1}student[id = "0"]/{c1}interest
With <interest/>

U4 If question "Limited" of test 1 exist, change its title to "Limited" else inset a new question to test 1
DEEP, AF
If
For $a in //test[id = "1"]//question[title = "Limited"]
Replace $a/title with <title>Limited</title>
Else
Insert <<has2><question/></has2> to $b in //test[id = "1"]

SHALLOW
If
For $a in //test[id = "1"]
For $b in //question[title = "Limited"]
Where $a/has2/@idref = $b/@id
Return $b
Replace $b/title with <title>Limited</title>
Else
Insert <has2/> to $c //test[id = "1"]
Insert <question/> to $d //

EN, MCMR, DR
If
For $a in //{c1}test[id = "1"]// {c1}question[{c1}title = "Limited"]
Replace $a/title with <title>Limited</title>
Else
Insert <has2><question/></has2> to $b in //{c1}test[id = "1"]

U5 For all classes, insert a total number of enrollment students
DEEP, AF
For $a in //class
Insert <count>count($a/enrolled)</count> as last() into $a

SHALLOW
For $a in //class
Let $b := //enrolled
Where $b/@idref = $a
Insert <count>count($b)</count> as last() into $a

EN, MCMR, DR
For $a in //{c1}class
Insert <count>count($a/{c1}enrolled)</count> as last() into $a

U6 Delete all classes’ enrollment of student id = 0
DEEP
Delete $a //enrolled[student/@id = "0"]
Move $b in //student to  //

AF
Delete $a in //enrolled[@idref = "0"]

SHALLOW
Delete $a in //student[id = "0"/enrolled

EN, MCMR, DR
Delete $a in //{c2}student[id = "0"]/{c2}enrolled

U7 Remove all questions that student answer is wrong
DEEP
For $a in //question[compares/result = "wrong"]
Delete $a

AF, SHALLOW
For $a in //question
$b in //student_answer[compare/result = "wrong"]
Where $a/has4/@idref = $b
Delete $a

EN
For $a in //{c1}question
For $b in //{c2}student_answer
For $b In //{c1}student_answer
Where $b/{c1}/compares/result = "wrong"
And $a/{c1}has4 = $b/{c2}has4
Delete $a

MCMR,DR
For $a in //{c1}question
For $b in //{c2}student_answer[{c2}compare/{c2}result = "wrong"]
Where $a/{c1}has4 = $b/{c2}has4
Delete $a

U8 For each student, compute the total GPA from grade and insert it into his element child
DEEP
For $a in DISTINCT //student
Let $b :=  For $c in //student
	Where $c/@id = $a/@id
       Return $c//grade
Insert <GPA>GPA($b)</GPA>
 into $d in //student where $d/@id = $a/@id , 

AF
For $a in // student
Let $b : = $a//grade
Insert <GPA>GPA($b)</GPA> into $a
 
SHALLOW
For $a in //student
Let $b := //grade
Where $a/has6/@idref  = $b/@id
Insert <GPA>GPA($b)</GPA> into $a

EN, MCMR, DR
For $a in //{c1}student
Let $b : = $a//{c1}grade
Insert <GPA>GPA($b)</GPA> into $a