Tutorial 6

For this final tutorial we will once again return to the same domain. Previously we have used a relational model, for which we have identified the following relations:

• Teachers(name, room)
  room → Rooms.name
• TeacherTitles(teacher, title)
  teacher → Teachers.name
• Rooms(name, nrSeats)
• Courses(name, teacher, nrStudents)
  teacher → Teachers.name
• Classes(course, day, hour)
  course → courses.name

Now we will switch to a semi-structured data model.

DTDs

Your first task is to come up with a schema for a semi-structured model of the same domain, in other words to create a DTD for the domain. Note that there are several ways of creating a DTD that would make perfect sense for this domain.

• Create a schema that directly mirrors the relational model. Remember that a "relational" DTD consists of exactly three levels below the root, with values only at the outermost level (in the leaves).
• Create a schema that makes use of the flexibility of the semi-structured model. In particular your schema should allow values on other levels than the outermost, and have a varying number of levels in various parts of the schema.

XML

Once you have your second schema, construct a small sample XML document that conforms to it.

Note that XML is a fairly verbose language, and this task could take quite some time if you don't restrict yourself. The point is to practise the relationship between XML and DTDs, not to practise your skill at writing < and > symbols…

XPath

Use the second schema you constructed. For each of the following, write an XPath expression that:

• finds all courses that have at least 20 students.
• lists all professors at the school.
• finds all courses that have classes on Mondays.
• finds all rooms that are used on Mondays.

XQuery

Use the second schema you constructed. For each of the following, write an XQuery expression that:

• finds all courses that have at least 20 students.
• finds the room(s) with the highest number of seats.
• finds the teacher(s) with the highest number of students on their courses.
• finds all classes where the number of students exceed the number of seats.
• finds the teacher(s) with the highest average number of students on his or her (their) courses.

You can assume that the standard aggregation functions exist, and that they work over lists of values. You may return in any format you think is suited, that retains all relevant data.

Examples

Files containing both the DTDs and XML data are available on-line:

• Relational approach
• More natural approach
• More natural approach, with more data added (for testing the XPath and XQuery solutions)

These XML files have been validated using the W3C validation service.