mysqlobject-oriented-database

Query on an Object-Oriented Database

The following is a scheme for a database in an object-oriented setting. Every relation becomes a collection of objects. A Student is a person and a Faculty is also a person. These are shown as directed edges labeled "isa". All other directed edges show reference attributes. Note that PreReq attribute in Course is a set of references.

enter image description here

Here is the query I can't figure out how to write:

Reorganize Enrollment collection by StudentID. For each student, retrieve the student's id and a Grade. The Grade should be a relation consisting of CourseCode and LetterGrade attributes.

By re-organize, I am pretty sure it just means retrieve the info in that order, and NOT do any updates to the database.

Solution

  • Because not all of the fields are properly referenced and the instructions are a bit... well, lacking, I'm going to make a few assumptions. Namely:

    1. The student table has a field called "StudentID" which is a 1 to 1 relationship with the ID field in the Person table.

    2. "Reorganize" means "select".. Odd phrasing that.

    3. Other than the Person table, all other IDs follow normal naming conventions. Meaning, <TableName>Id. For example, the primary ID in the student table is StudentID

    4. "LetterGrade" in the question actually refers to the "Grade" field in the enrollment table.

    5. All fields ending in the word "Info" are foreign keys to the equivalent field ending in "Id". For example: Enrollment.StudentInfo maps to Student.StudentId

    Something along the lines of

    SELECT S.StudentID, E.Grade, C.CourseCode
FROM Student S
  INNER JOIN Enrollment E on (E.StudentInfo = S.StudentId)
  INNER JOIN Offering O on (O.OfferingId = E.OfferingInfo)
  INNER JOIN Course C on (C.CourseId = O.CourseInfo)
ORDER BY S.StudentId