Databases (HT2012)
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 rooms that are used on Mondays.
- finds all courses that have classes on Mondays.
(At least) one of the above cannot be written using XPath only. Which one?
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.
Last Modified: 26 October 2012
by Graham Kemp