Mapping of GraphQL Schema to Relational Schema - LiUGraphQL/LinGBM GitHub Wiki

This document provides an informal definition of how the elements of the LinGBM GraphQL schema map to the database schema of the benchmark dataset.

When designing the LinGBM GraphQL schema, we first have created an initial sketch of this schema that closely resembles the database schema. Thereafter, we have extended this initial version with additional fields and types that we needed to define the query workloads of the benchmark.

In the following we first outline the general approach adopted to create the initial sketch of the LinGBM GraphQL schema. Thereafter, we define the mapping in detail by focusing on the object types of the schema, one after another.

1. General Idea
2. The Mapping in Detail

• University
• Department
• ResearchGroup
• Faculty
• Professor
• Lecturer
• GraduateStudent
• UndergraduateCourse
• Publication
• GraduateCourse
• UndergraduateStudent

The general approach adopted to create the initial sketch of the LinGBM GraphQL schema is based on the following four rules.

1. For every table in the database schema (except for the tables CoAuthorOfPublication, GraduateStudentTakeCourse and UndergraduateStudentTakeCourse), create an object type in the GraphQL schema; the name of this object type is the same as the name of the table.
2. For every attribute in these tables, create a field in the corresponding object type; the name of this field is the same as the name of the attribute and the type of the field resembles the domain (datatype) of the attribute.
3. For an attribute that is a foreign key into some other table, the type of the corresponding field has to be the object type for the table referenced by the foreign key. Additionally, that object type (for the table referenced by the foreign key) is extended with another field to follow the foreign key in the reverse direction (i.e., back to the object type for the table that contains the foreign key); the type of this field is a list type that wraps the object type for the table containing the foreign key.
4. The many-to-many relationship tables CoAuthorOfPublication GraduateStudentTakeCourse and UndergraduateStudentTakeCourse are captured in a similar manner by adding cross-reference fields in the corresponding object types.

type University  
{ 
  id: ID! 
  undergraduateDegreeFromObtainedByFaculty: [Faculty] 
  mastergraduateDegreeObtainers: [Faculty]
  doctoralDegreeObtainers: [Faculty]
  undergraduateDegreeFromObtainedBystudent: [GraduateStudent]
  departments: [Department] 
} 

university (nr)
faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
graduateStudent (nr, telephone, emailAddress, age, undergraduateDegreeFrom, advisor, memberOf)
department (nr, subOrganizationOf)

The object type University in the GraphQL schema corresponds to the table university in the relational model. That is, for every row in the university table, there exists an object of type University. For this object, the values of its fields are determined as follows:

• The value of the field id is the value of the attribute nr that the corresponding row has in the table university;
  

• The value of the undergraduateDegreeFromObtainedByFaculty field is an array containing each Faculty object (created as described for the Faculty type) whose corresponding row in the faculty table has the nr attribute of this University as its 'undergraduateDegreeFrom' attribute value.
  

• The value of the mastergraduateDegreeObtainers field is an array containing each Faculty object (created as described for the Faculty type) whose corresponding row in the faculty table has the nr attribute of this University as its 'masterDegreeFrom' attribute value.
  

• The value of the doctoralDegreeObtainers field is an array containing each Faculty object (created as described for the Faculty type) whose corresponding row in the faculty table has the nr attribute of this University as its 'doctoralDegreeFrom' attribute value.
  

• The value of the undergraduateDegreeFromObtainedBystudent field is an array containing each GraduateStudent object (created as described for the GraduateStudent type) whose corresponding row in the graduateStudent table has the nr attribute of this University as its 'undergraduateDegreeFrom' attribute value.
  

• The value of the departments field is an array containing each Department object (created as described for the Department type) whose corresponding row in the department table has the nr attribute of this University as its 'subOrganizationOf' attribute value.
  

interface Faculty  
{ 
  id: ID! 
  telephone: String 
  emailAddress: String 
  undergraduteDegreeFrom: University 
  masterDegreeFrom: University 
  doctoralDegreeFrom: University 
  worksFor: Department 
  teacherOfGraduateCourses: [GraduateCourse] 
  teacherOfUndergraduateCourses: [UndergraduateCourse] 
  publications: [Publication] 
} 

faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
university (nr)
department (nr, subOrganizationOf)
undergraduateCourse (nr, teacher, teachingAssistant)
graduateCourse (nr, teacher)
publication (nr, title, abstract, mainAuthor)

The interface Faculty in the GraphQL schema corresponds to the table Faculty in the relational model. That is, for every row in the Faculty table, there exists a Faculty. For this object, the values of its fields are determined as follows:

• The value of the field id is the value of the attribute nr that the corresponding row has in the table faculty;
  

• The value of the field telephone is the value of the attribute telephone that the corresponding row has in the table faculty;
  

• The value of the field emailAddress is the value of the attribute emailAddress that the corresponding row has in the table faculty;
  

• The value of the field undergraduteDegreeFrom is the value of the University object (created as described for the University type) whose corresponding row in the university table has the nr attribute, whose value is the same as the 'undergraduteDegreeFrom' attribute value of this Faculty.
  

• The value of the field masterDegreeFrom is the value of the University object (created as described for the University type) whose corresponding row in the university table has the nr attribute, whose value is the same as the 'masterDegreeFrom' attribute value of this Faculty.
  

• The value of the field doctoralDegreeFrom is the value of the University object (created as described for the University type) whose corresponding row in the university table has the nr attribute, whose value is the same as the 'doctoralDegreeFrom' attribute value of this Faculty.
  

• The value of the field worksFor is the value of the Department object (created as described for the Department type) whose corresponding row in the department table has the nr attribute, whose value is the same as the 'worksFor' attribute value of this Faculty.
  

• The value of the teacherOfGraduateCourses field is an array containing each GraduateCourse object (created as described for the GraduateCourse type) whose corresponding row in the graduateCourse table has the nr attribute of this University as its 'teacher' attribute value.
  

• The value of the teacherOfUndergraduateCourses field is an array containing each UndergraduateCourse object (created as described for the UndergraduateCourse type) whose corresponding row in the undergraduateCourse table has the nr attribute of this University as its 'teacher' attribute value.
  

• The value of the publications field is an array containing each Publication object (created as described for the Publication type) whose corresponding row in the publication table has the nr attribute of this University as its 'mainAuthor' attribute value.
  

type Department  
{ 
  id: ID! 
  subOrganizationOf: University 
  head: Professor 
  researchGroups: [ResearchGroup] 
  faculties: [Faculty] 
  professors: [Professor]
  lecturers: [Lecturer]
  graduateStudents: [GraduateStudent] 
  undergraduateStudents: [UndergraduateStudent] 
}

department (nr, subOrganizationOf)
university (nr)
researchGroup (nr, subOrganizationOf)
faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
professor (nr, professorType, researchInterest, headOf)
lecturer (nr)
graduateStudent (nr, telephone, emailAddress, age, undergraduateDegreeFrom, advisor, memberOf)
undergraduateStudent (nr, telephone, emailAddress, age, advisor, memberOf)

The object type Department in the GraphQL schema corresponds to the table department in the relational model. That is, for every row in the department table, there exists an object of type Department. For this object, the values of its fields are determined as follows:

• The value of the field id is the value of the attribute nr that the corresponding row has in the table department;
  

• The value of the field subOrganizationOf is the value of the University object (created as described for the University type) whose corresponding row in the university table has the nr attribute, whose values is the same as the 'subOrganizationOf' attribute value of this Department.
  

• The value of the field head is the value of the Professor object (created as described for the University type) whose corresponding row in the professor table has the headOf attribute, whose values is the same as the 'nr' attribute value of this Department.
  

• The value of the researchGroups field is an array containing each ResearchGroup object (created as described for the ResearchGroup type) whose corresponding row in the researchGroup table has the nr attribute of this Department as its 'subOrganizationOf' attribute values.
  

• The value of the faculties field is an array containing each Faculty object (created as described for the Faculty type) whose corresponding row in the faculty table has the nr attribute of this Department as its 'worksFor' attribute values.
  

• The value of the professors field is an array containing each Professor object (created as described for the Professor type) whose corresponding row in the professor table has the same nr attribute value as corresponding faculty table, where the corresponding faculty has the nr attribute of this Department as its 'worksFor' attribute values.
  

• The value of the lecturers field is an array containing each Lecturer object (created as described for the Lecturer type) whose corresponding row in the lecturer table has the same nr attribute value as corresponding faculty table, where the corresponding faculty has the nr attribute of this Department as its 'worksFor' attribute values.
  

• The value of the graduateStudents field is an array containing each GraduateStudent object (created as described for the GraduateStudent type) whose corresponding row in the graduateStudent table has the nr attribute of this Department as its 'memberOf' attribute values.
  

• The value of the undergraduateStudents field is an array containing each UndergraduateStudent object (created as described for the UndergraduateStudent type) whose corresponding row in the undergraduateStudent table has the nr attribute of this Department as its 'memberOf' attribute values.
  

type ResearchGroup  
{ 
  id: ID! 
  subOrgnizationOf: Department 
} 

researchGroup (nr, subOrganizationOf)
department (nr, subOrganizationOf)

The object type ResearchGroup in the GraphQL schema corresponds to the table researchGroup in the relational model. That is, for every row in the researchGroup table, there exists an object of type ResearchGroup. For this object, the values of its fields are determined as follows:

• The value of the field id is the value of the attribute nr that the corresponding row has in the table researchGroup;
  

• The value of the field subOrganizationOf is the value of the Department object (created as described for the Department type) whose corresponding row in the department table has the nr attribute, whose values is the same as the 'subOrganizationOf' attribute value of this ResearchGroup.
  

interface Author  
{ 
  id: ID! 
  telephone: String 
  emailAddress: String
} 

faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
graduateStudent (nr, telephone, emailAddress, age, undergraduateDegreeFrom, advisor, memberOf)
publication (nr, title, abstract, mainAuthor)

type Professor implements Faculty & Author
{ 
  id: ID! 
  telephone: String 
  emailAddress: String 
  researchInterest: String 
  profType: String
  undergraduateDegreeFrom: University 
  masterDegreeFrom: University 
  doctoralDegreeFrom: University 
  worksFor: Department 
  teacherOfGraduateCourses: [GraduateCourse] 
  teacherOfUndergraduateCourses: [UndergraduateCourse]
  publications(order: PublicationSortCriterion): [Publication]
  supervisedUndergraduateStudents: [UndergraduateStudent] 
  supervisedGraduateStudents: [GraduateStudent]
} 

professor (nr, professorType, researchInterest, headOf)
faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
university (nr)
department (nr, subOrganizationOf)
undergraduateCourse (nr, teacher, teachingAssistant)
graduateCourse (nr, teacher)
graduateStudent (nr, telephone, emailAddress, age, undergraduateDegreeFrom, advisor, memberOf)
undergraduateStudent (nr, telephone, emailAddress, age, advisor, memberOf)
publication (nr, title, abstract, mainAuthor)

type Lecturer implements Faculty & Author 
{ 
  id: ID! 
  telephone: String 
  emailAddress: String 
  position: String
  undergraduateDegreeFrom: University 
  masterDegreeFrom: University 
  doctoralDegreeFrom: University 
  worksFor: Department 
  teacherOfGraduateCourses: [GraduateCourse] 
  teacherOfUndergraduateCourses: [UndergraduateCourse] 
  publications: [Publication] 
}

lecturer (nr)
faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
university (nr)
department (nr, subOrganizationOf)
undergraduateCourse (nr, teacher, teachingAssistant)
graduateCourse (nr, teacher)
publication (nr, title, abstract, mainAuthor)

type Publication  
{ 
  id: ID! 
  title: String 
  abstract: String 
  authors: [Author] 
} 

publication (nr, title, abstract, mainAuthor)
faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
graduateStudent (nr, telephone, emailAddress, age, undergraduateDegreeFrom, advisor, memberOf)

type GraduateStudent implements Author 
{ 
  id: ID! 
  telephone: String 
  emailAddress: String 
  age: Int
  memberOf: Department 
  undergraduateDegreeFrom: University 
  advisor: Professor 
  takeGraduateCourses: [GraduateCourse] 
  assistCourses: [UndergraduateCourse] 
}  

graduateStudent (nr, telephone, emailAddress, age, undergraduateDegreeFrom, advisor, memberOf)
department (nr, subOrganizationOf)
university (nr)
professor (nr, professorType, researchInterest, headOf)
undergraduateCourse (nr, teacher, teachingAssistant)
graduateCourse (nr, teacher)

type UndergraduateStudent  
{ 
  id: ID! 
  telephone: String 
  emailAddress: String 
  age: Int
  memberOf: Department 
  advisor: Professor 
  takeCourses: [UndergraduateCourse] 
}  

undergraduateStudent (nr, telephone, emailAddress, age, advisor, memberOf)
department (nr, subOrganizationOf)
professor (nr, professorType, researchInterest, headOf)
undergraduateCourse (nr, teacher, teachingAssistant)

type GraduateCourse  
{ 
  id: ID! 
  teachedby: Faculty 
  graduateStudents: [GraduateStudent] 
} 

graduateCourse (nr, teacher)
faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
graduateStudent (nr, telephone, emailAddress, age, undergraduateDegreeFrom, advisor, memberOf)

type UndergraduateCourse  
{ 
  id: ID! 
  teachedby: Faculty 
  undergraduateStudents: [UndergraduateStudent] 
  teachingAssistants: GraduateStudent 
} 

undergraduateCourse (nr, teacher, teachingAssistant)
faculty (nr, telephone, emailAddress, undergraduateDegreeFrom, masterDegreeFrom, doctoralDegreeFrom, worksFor)
undergraduateStudent (nr, telephone, emailAddress, age, advisor, memberOf)
graduateStudent (nr, telephone, emailAddress, age, undergraduateDegreeFrom, advisor, memberOf)