STAGE 2 - XML/XQuery
Construct XML representations for the following. Express your design using a diagram and/or a schema.
1. Composition of courses, i.e. a list of the modules that make up a course. You may assume that the composition of a course does not
change.
2. Pre-requisite information for modules, i.e. modules that must be taken before that module can be taken.
3. Module enrolment - list of students taking the module.
4. Module results. The results field should be optional, to distinguish between the current and completed runs of a module.
a. For each module, list of final marks for each student. Marks are in the range 0 -100.
b. Other possible results for a module. For these purposes, these are limited to include:
i. Medical resit
ii. Resit - for other reasons
iii. Fail - by virtue of not submitting coursework, or not
turning up to exam. This is different to a mark of 0.
Then construct XQuery expressions to answer the following questions.
1. List the module results for a given student
2. List the module results for a given course
3. List the student results for a given course
4. List any students with an invalid programme - this includes:
a. Students enrolled on modules that are not part of their course
b. Students who have not taken the prerequisite required for a particular module.
c. Students who have not reached a mark of at least 30 in all the pre-requisite modules for a given module. List the prerequisite
modules concerned.
For sample data: a subset of the MSc course structure in the Faculty of Technology, Design and Environment will be used. Some synthetic data are provided (i.e. data not taken from real students) Folder STAGE 2
The module structure for the courses CM51, CM79 and CM85 is contained in the spreadsheet "module matrix.xls".
Modules acceptable to a course are denoted by "a", compulsory modules are denoted by "c".
Modules for which there is a blank entry are not acceptable for that course. 8 The prerequisite structure is contained in the spreadsheet module"prerequisites.xls". Each module has either one or no prerequisites. A prerequisite module must be taken and a mark of at least 30 attained. Enrollment of students in modules is contained in the spreadsheet students_to_modules.csv (in .csv format).
Marks for each module taken are in the spreadsheet mark_sheets.csv. This again is in .csv format. Marks are in the range 0 to 100. There are also some non numerical values, fail, resit and medical resit.
An XML version of the marks can be found in the file mark_sheets.xml
For the information that is contained only in spreadsheets, you may represent this in XML however you like.
You may use the file new_mark_sheets.xml directly if you wish. Alternatively if you wish to modify the format you are free to do so.
You may find it convenient to use XQuery to convert one XML representation into another.
Some of the student programmes contained in these files have deliberately been made invalid - for example missing prerequisite modules and modules taken that are invalid for the particular course of study. One of the tasks is to identify these.