exercisesassignment.pptx [sola lettura] - roma tre …atzeni/didattica/bd/20112012/...exercise4:...
TRANSCRIPT
![Page 2: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/2.jpg)
Exercises:2x From instance to DTD2x From instance to XSD3x From specs to XPath2x From specs to XQuery
![Page 3: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/3.jpg)
Document 1: Student<?xml version="1.0" encoding="UTF‐8"?><student>
<firstName>Luca</firstName><lastName>Rossi</lastName><id>281283</id><plan>
<courses year="3"><course>
<name> Programmazione Orientata agli Oggetti </name><shortName>POO</shortName><record>
<grade>30</grade><date>13/06/11</date>
</record></course><course>
<name>Analisi e progettazione del software</name><shortName>APS</shortName>
</course></courses>
</plan></student>
![Page 4: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/4.jpg)
Document 2: Email<?xml version="1.0" encoding="UTF‐8"?><email>
<from> [email protected] </from><to> [email protected] </to><content>
Dear <person> Paolo </person>,here are some very hard exercises for the upcoming assignment of <course> Basi di Dati 2 </course>:<exercises>
<exercise><topic> DTD </topic><description> From Instance to DTD </description>
</exercise><exercise>
<topic> XPath </topic><description> Find students with average grade better than 26 </description>
</exercise></exercises>Best Regards,<person> Luca </person>
</content></email>
![Page 5: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/5.jpg)
Exercise 1Define a DTD that validates Document 1
![Page 6: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/6.jpg)
Exercise 1: Solution<!DOCTYPE student [<!ELEMENT student (firstName, lastName, id, plan)><!ELEMENT firstName (#PCDATA)><!ELEMENT lastName (#PCDATA)><!ELEMENT id (#PCDATA)><!ELEMENT plan (courses*)><!ELEMENT courses (course*)><!ATTLIST courses year CDATA #REQUIRED><!ELEMENT course (name, shortName, record?)><!ELEMENT record (grade, date)><!ELEMENT name (#PCDATA)><!ELEMENT shortName (#PCDATA)><!ELEMENT grade (#PCDATA)><!ELEMENT date (#PCDATA)>
]>
![Page 7: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/7.jpg)
Exercise 2Define a DTD that validates Document 2
![Page 8: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/8.jpg)
Exercise 2: Solution<!DOCTYPE email [<!ELEMENT email (from, to, content)><!ELEMENT from (#PCDATA)><!ELEMENT to (#PCDATA)><!ELEMENT content (#PCDATA|person|exercises|course)*><!ELEMENT exercises (exercise*)><!ELEMENT exercise (topic, description)><!ELEMENT topic (#PCDATA)><!ELEMENT description (#PCDATA)><!ELEMENT person (#PCDATA)><!ELEMENT course (#PCDATA)>
]>
![Page 9: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/9.jpg)
Exercise 3Define an XSD that validates Document 1
![Page 10: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/10.jpg)
Exercise 3: Solution<?xml version="1.0" encoding="UTF‐8"?><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="student"><xsd:complexType><xsd:sequence><xsd:element name="firstName" type="xsd:string"/><xsd:element name="lastName" type="xsd:string"/><xsd:element name="id" type="xsd:ID"/><xsd:element ref="plan"/>
</xsd:sequence></xsd:complexType>
</xsd:element>…
![Page 11: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/11.jpg)
Exercise 3: Solution<?xml version="1.0" encoding="UTF‐8"?><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="student"><xsd:complexType><xsd:sequence><xsd:element name="firstName" type="xsd:string"/><xsd:element name="lastName" type="xsd:string"/><xsd:element name="id" type="xsd:ID"/><xsd:element ref="plan"/>
</xsd:sequence></xsd:complexType>
</xsd:element>…
![Page 12: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/12.jpg)
Exercise 3: Solution<xsd:element name="record">
<xsd:complexType><xsd:sequence><xsd:element name="grade" type="xsd:string"/><xsd:element name="date" type="xsd:date"/>
</xsd:sequence></xsd:complexType>
</xsd:element>
<xsd:element name="course"><xsd:complexType><xsd:sequence><xsd:element name="name" type="xsd:string"/><xsd:element name="shortName" type="xsd:string"/><xsd:element ref="record" minOccurs="0"/>
</xsd:sequence></xsd:complexType>
</xsd:element>
![Page 13: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/13.jpg)
Exercise 3: Solution<xsd:element name="courses"><xsd:complexType><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element ref="course"/>
</xsd:sequence><xsd:attribute name="year" type="xsd:string"/>
</xsd:complexType> </xsd:element>
<xsd:element name="plan"><xsd:complexType><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element ref="courses"/>
</xsd:sequence></xsd:complexType><xsd:unique name="coursesYear"><xsd:selector xpath="courses"/><xsd:field xpath="@year"/>
</xsd:unique></xsd:element>
![Page 14: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/14.jpg)
Exercise 3: Solution<xsd:element name="courses"><xsd:complexType><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element ref="course"/>
</xsd:sequence><xsd:attribute name="year" type="xsd:string"/>
</xsd:complexType> </xsd:element>
<xsd:element name="plan"><xsd:complexType><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element ref="courses"/>
</xsd:sequence></xsd:complexType><xsd:unique name="coursesYear"><xsd:selector xpath="courses"/><xsd:field xpath="@year"/>
</xsd:unique></xsd:element>
![Page 15: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/15.jpg)
Exercise 4Define an XSD that validates Document 2
![Page 16: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/16.jpg)
Exercise 4: Solution<?xml version="1.0" encoding="UTF‐8"?><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="email"><xsd:complexType><xsd:sequence><xsd:element name="from" type="xsd:string" /><xsd:element name="to" type="xsd:string" /><xsd:element name="content">….
</xsd:element></xsd:sequence>
</xsd:complexType> </xsd:element>
</xsd:schema>
![Page 17: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/17.jpg)
Exercise 4: Solution<xsd:complexType mixed="true"><xsd:sequence><xsd:element name="person" type="xsd:string"/><xsd:element name="course" type="xsd:string"/><xsd:element name="exercises"><xsd:complexType><xsd:sequence minOccurs="0" maxOccurs="unbounded"><xsd:element name="exercise"><xsd:complexType><xsd:sequence><xsd:element name="topic" type="xsd:string"/><xsd:element name="description" type="xsd:string"/>
</xsd:sequence> </xsd:complexType>
</xsd:element></xsd:sequence>
</xsd:complexType></xsd:element><xsd:element name="person" type="xsd:string"/>
</xsd:sequence></xsd:complexType>
![Page 18: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/18.jpg)
Exercise 4From specs to XPath
![Page 19: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/19.jpg)
Exercise 4: Solution1) Find students who have passed POO with grade > 242) Find students with average grade > 263) Find students who have passed more exams in the 2nd year than
in the 3rd
![Page 20: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/20.jpg)
Exercise 4: Solution1) Find students who have passed POO with grade > 24
//student[.//course[shortName="POO" and record>24]]
2) Find students with average grade > 263) Find students who have passed more exams in the 2nd year than
in the 3rd
![Page 21: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/21.jpg)
Exercise 4: Solution1) Find students who have passed POO with grade > 242) Find students with average grade > 263) Find students who have passed more exams in the 2nd year than
in the 3rd
![Page 22: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/22.jpg)
Exercise 4: Solution1) Find students who have passed POO with grade > 242) Find students with average grade > 26
//student[avg(.//grade)>26]
3) Find students who have passed more exams in the 2nd year than in the 3rd
![Page 23: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/23.jpg)
Exercise 4: Solution1) Find students who have passed POO with grade > 242) Find students with average grade > 263) Find students who have passed more exams in the 2nd year
than in the 3rd
![Page 24: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/24.jpg)
Exercise 4: Solution1) Find students who have passed POO with grade > 242) Find students with average grade > 263) Find students who have passed more exams in the 2nd year
than in the 3rd//student[count(.//courses[@year="3"]/course) > count(.//courses[@year="2"]/course)]
![Page 25: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/25.jpg)
Exercise 5From specs to XQuery
![Page 26: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/26.jpg)
Exercise 5: Solution1) For each student, return distinct couples of exams passed with
the same grade2) Return students who improved their average grade in the 3rd
year with respect to the 2nd.
![Page 27: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/27.jpg)
Exercise 5: Solution1) For each student, return distinct couples of exams passed with
the same grade
xquery version "1.0";for $s1 in fn:doc("student.xml")//studentfor $c1 in $s1//coursefor $c2 in $s1//coursewhere $c2 >> $c1 and $c1//grade = $c2//gradereturn<coppiaCorsi><corso><name>{$c1/name}</name><grade>{$c1//grade}</grade>
</corso><corso><name>{$c2/name}</name><grade>{$c2//grade}</grade>
</corso></coppiaCorsi>
![Page 28: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/28.jpg)
Exercise 5: Solution1) For each student, return distinct couples of exams passed with
the same grade2) Return students who improved their average grade in the 3rd
year with respect to the 2nd.
![Page 29: ExercisesAssignment.pptx [Sola lettura] - Roma Tre …atzeni/didattica/BD/20112012/...Exercise4: Solution 1) Find students who have passed POO with grade > 24 2) Find students with](https://reader034.vdocuments.us/reader034/viewer/2022051802/5af0969b7f8b9abc788d5f0a/html5/thumbnails/29.jpg)
Exercise 5: Solution1) For each student, return distinct couples of exams passed with
the same grade2) Return students who improved their average grade in the 3rd
year with respect to the 2nd.xquery version "1.0";for $s in fn:doc("student.xml")//studentlet $avg3 := fn:avg($s//courses[@year="3"]//grade)let $avg2 := fn:avg($s//courses[@year="2"]//grade)where $avg3 > $avg2return<students><student>{$s/id}<avg year="3">{$avg3}</avg><avg year="2">{$avg2}</avg>
</student></students>