Query on an Object-Oriented Database

Question

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.

This looks like it's missing some info. Namely, what in the world is "isa"? Is that a column in those tables or just the name of the relation? In other words, what identifier ties the Student table to the Person table? I don't see "StudentID" anywhere... — ChrisLively
– ChrisLively, Commented Oct 19, 2011 at 1:22
@ChrisLively "Is a," as in "Faculty is a Person," "Student is a Person." Presumably each record in Student and Faculty has an ID field that corresponds to an ID in the Person table but is not shown in the diagram to keep it concise. — Jordan Running
– Jordan Running, Commented Oct 19, 2011 at 1:26
Back to our regulary scheduled program: There isn't enough info here. The diagram is missing a LOT of information. Is "StudentID" from the Student Table a 1 to 1 relation to "ID" in the person table? What field maps Enrollment.StudentInfo to Student? What field maps Enrollment.OfferingInfo to Offering? What field maps to Offering.CourseInfo to Course? Also, where does "LetterGrade" reside? In short, the question names fields that flat don't exist on the diagram and the diagram isn't a complete data model. — ChrisLively
– ChrisLively, Commented Oct 19, 2011 at 6:19
From what I understand, the relationship between Student and Person isn't a "relation", it's an isa, so there isn't a 1-1 or 1-many relationship here. They way object oriented databases work with isa, is that the sub-objects inherit the fields from its parent. So calling Student.ID is valid because a student is a person — user446836
– user446836, Commented Oct 19, 2011 at 16:58

ChrisLively · Accepted Answer · 2011-10-19 16:52:43Z

1

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:

The student table has a field called "StudentID" which is a 1 to 1 relationship with the ID field in the Person table.
"Reorganize" means "select".. Odd phrasing that.
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
"LetterGrade" in the question actually refers to the "Grade" field in the enrollment table.
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

edited Oct 19, 2011 at 16:52

answered Oct 19, 2011 at 6:26

ChrisLively

88.3k27 gold badges174 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user446836 Over a year ago

They way object oriented databases work with isa, is that the sub-objects inherit the fields from its parent. So calling Student.ID is valid because a student is a person.

ChrisLively Over a year ago

@user446836: Believe me when I say I understand that. However, the database vendor you tagged (mysql) isn't an OO database... so it won't inherit anything without the proper join. If there is a different vendor that you are using, please update your tags.

Collectives™ on Stack Overflow

Query on an Object-Oriented Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related