Students a is a list of base. embedded or extended attributes from r If A contains only base attributes, then the resulting relation is always a base one. Join, Mc, creates a new relation by combining tuples in Ri Ext_Students SuD Name Comments(CourselD), Rating. I and R, that meet some condition c, which refers only to base Paul Lite C1 I 2Feb2008 attributes of the two relations. i.e R:∞xB1:={(r,r)r∈RAr;∈B;An,r; satisfy c 15Mar2007 12Dec2007 A common type of join is natural join, which connects two c56622Jn2007 relations by equating attributes of the same name, and project- c7722Jn20 ing out one copy of each pair of equated attributes. This is The base opera handle extended relations Figure 3: A base and an extended relation and be combined with the new recommendation operators to be de- fined next but we have kept their original semantics. One could go have studied and implemented and we do not want to unnecessarily further and define operators with extended semantics. For example burden our design one could define that the select operator operates also on extended Example: We consider the course database with the base rela- attributes. There is a rich literature on nested relational algebras tions shown in Figure 1. figure 3 shows an instance of the Students that discuss extended, recursive operations such as nesting, unn base relation. We can define the extended relation Ext_ Students that g and projections and joins on embedded relations [9, 11, 25 contains all the base attributes of a student plus an extended at- We believe that such generality is not necessary for practical rec- tribute that represents the ratings made by each student as a singl ommendations. Our framework can be easily extended to handle "unit of information"per student more generalized relational operators Ext_Students(SulD, Name, Comments( CourselD, Rating, Date)) 3.3 The Extend Operator Figure 3 shows an instance for this relation. To refer to the ex with many(normalized) database schemas, information that con- tended attribute we use Ext Students. Comments, and to the rating at ceptually refers to a single entity(e. g, a student's course ratings) tribute within Ext._ Students, we use Comments. Rating or simply Rating. is often found in several relations. In our s. ded)relation.For we want to repre- In a similar way, we can define other extended relations, such sent such application entities with a single(e the extended relation Ext Courses that extends the base course a tuple in an extended relation could contain base infor- relation with an attribute that shows all the instructors per year and n a student(e. g, name, GPA), plus the set of courses a term that a course was taught as taken. For this purpose, we introduce a new operator Ext_ Courses(CourselD, DepID, Title, Instructors( Name, Year, Term)) es such extended attributes in the tuples of a relation In Section 3.3, we will show how these example extended rela- DEFINITION 2. Extend, E, creates an extended relation by em tions can be defined using the extend operator bedding a base relation Ri into another relation Ri, i.e. Extended relations can be thought of as"views e;:={(r;,v)lr∈R:At:=A(n凶R;)} group together information related to an individual resent it as a single tuple that can be easily handled by the recom- where A is the set of attributes of R mendation operators irrespective of the database structure (as we In words, Ri E Ri is an extended relation that contains all the will see in Sections 3.4-3.6). attributes and tuples from Ri plus an extended attribute whose(ex- Although the issue of whether extended relations are material- tended)value for each tuple ri from Ri is a set of tuples from R ized or not is orthogonal to their definition, in practice, extended that join to ri on the common attributes. If there are no joining relations may not be stored in the database. In the following sub- tuples, then the extended attribute will be an empty relation for ri ections, we describe the operators required to handle extended re- The default name of this extended attribute is the name of R, lations and formulate recommendations. We start with the standard In the above definition, only R is required to be a bas (core)relational algebra operators with set semantics and we extend because we allow one-level nesting. Ri can be a base or them enabling interaction with extended relations as well relation, since any extended relation can have more than ended attribute. The extend operator can be defined for 3.2 Base Operators level nesting as well. We consider the following operators that can operate on base and Example: Using the extend operator we can generate the ex- Select, ac, selects tuples from relation R, for which the con- PComments: =(SulD, CourselD, Rating. Date)(Comments) dition c holds. ie Ext_Students : T (SulD, Name)(Students) PComments c(B):={r|r∈R∧ r satisfies c} R can be a base or extended relation and c is a condition that combine information found in more than two relations as follows. refers to base attributes of R. o(R)will be a base or extended relation depending on the type of R. Schedules T(CourselD, ear, Term, strid)( Course Sched) reates a new relation by projecting R into a Ext_ Courses =T(CourselD, DeplD, Tte( Courses) Instruct_ Sched aller set of its attributes. i.e. Note that joining over attributes with common names simplifies 丌A(B):={r{A]r∈R the presentation and the definition of the extend operator. It doesStudents SuID Name Class Status 1 2 Paul Little John Doe 2009 2010 H N Ext_Students SuID Name Comments (CourseID, Rating, Date) 1 2 Paul Little John Doe C1 C2 5 6 2 Feb 2008 3 Dec 2007 C1 C2 5 6 15 Mar 2007 12 Dec 2007 C5 6.6 22 Jun 2007 C7 7 22 Jun 2007 Figure 3: A base and an extended relation have studied and implemented and we do not want to unnecessarily burden our design. Example: We consider the course database with the base rela￾tions shown in Figure 1. Figure 3 shows an instance of the Students base relation. We can define the extended relation Ext_Students that contains all the base attributes of a student plus an extended at￾tribute that represents the ratings made by each student as a single “unit of information” per student: Ext_Students(SuID, Name, Comments(CourseID, Rating, Date)) Figure 3 shows an instance for this relation. To refer to the ex￾tended attribute, we use Ext_Students.Comments, and to the rating at￾tribute within Ext_Students, we use Comments.Rating or simply Rating. In a similar way, we can define other extended relations, such as the extended relation Ext_Courses that extends the base course relation with an attribute that shows all the instructors per year and term that a course was taught: Ext_Courses(CourseID, DepID, Title, Instructors(Name, Year, Term)) In Section 3.3, we will show how these example extended rela￾tions can be defined using the extend operator. Extended relations can be thought of as “views” that collect and group together information related to an individual entity and rep￾resent it as a single tuple that can be easily handled by the recom￾mendation operators irrespective of the database structure (as we will see in Sections 3.4 − 3.6). Although the issue of whether extended relations are material￾ized or not is orthogonal to their definition, in practice, extended relations may not be stored in the database. In the following sub￾sections, we describe the operators required to handle extended re￾lations and formulate recommendations. We start with the standard (core) relational algebra operators with set semantics and we extend them enabling interaction with extended relations as well. 3.2 Base Operators We consider the following operators that can operate on base and extended relations. • Select, σc, selects tuples from relation R, for which the con￾dition c holds, i.e., σc(R) := {r|r ∈ R ∧ r satisfies c} R can be a base or extended relation and c is a condition that refers to base attributes of R. σc(R) will be a base or extended relation depending on the type of R. • Project, πA, creates a new relation by projecting R into a smaller set of its attributes, i.e., πA(R) := {r[A]|r ∈ R } A is a list of base, embedded or extended attributes from R. If A contains only base attributes, then the resulting relation is always a base one. • Join, ./c, creates a new relation by combining tuples in Ri and Rj that meet some condition c, which refers only to base attributes of the two relations, i.e., Ri ./cRj := {(ri, rj )|ri ∈ Ri ∧ rj ∈ Rj ∧ ri, rj satisfy c} A common type of join is natural join, which connects two relations by equating attributes of the same name, and project￾ing out one copy of each pair of equated attributes. This is denoted Ri ./ Rj (the condition c is implied.) The base operators defined above can handle extended relations and be combined with the new recommendation operators to be de- fined next but we have kept their original semantics. One could go further and define operators with extended semantics. For example, one could define that the select operator operates also on extended attributes. There is a rich literature on nested relational algebras that discuss extended, recursive operations such as nesting, unnest￾ing and projections and joins on embedded relations [9, 11, 25]. We believe that such generality is not necessary for practical rec￾ommendations. Our framework can be easily extended to handle more generalized relational operators. 3.3 The Extend Operator With many (normalized) database schemas, information that con￾ceptually refers to a single entity (e.g., a student’s course ratings) is often found in several relations. In our system, we want to repre￾sent such application entities with a single (extended) relation. For instance, a tuple in an extended relation could contain base infor￾mation on a student (e.g., name, GPA), plus the set of courses a student has taken. For this purpose, we introduce a new operator that creates such extended attributes in the tuples of a relation. DEFINITION 2. Extend, ε, creates an extended relation by em￾bedding a base relation Rj into another relation Ri, i.e., Ri ε Rj := {(ri, v)|ri ∈ Ri ∧ v := πA(ri ./ Rj )} where A is the set of attributes of Rj . In words, Ri ε Rj is an extended relation that contains all the attributes and tuples from Ri plus an extended attribute whose (ex￾tended) value for each tuple ri from Ri is a set of tuples from Rj that join to ri on the common attributes. If there are no joining tuples, then the extended attribute will be an empty relation for ri. The default name of this extended attribute is the name of Rj . In the above definition, only Rj is required to be a base relation because we allow one-level nesting. Ri can be a base or extended relation, since any extended relation can have more than one ex￾tended attribute. The extend operator can be defined for multiple￾level nesting as well. Example: Using the extend operator we can generate the ex￾tended relation Ext_Students described earlier as follows: PComments := π{SuID,CourseID,Rating,Date}(Comments) Ext_Students := π{SuID,Name}(Students) ε PComments In order to generate the extended relation Ext_Courses, we need to combine information found in more than two relations, as follows: Schedules := π{CourseID,Year,Term,InstrID}(CourseSched) Instruct_Sched := π{InstrID,Name}(Instructors) ./ Schedules Ext_Courses := π{CourseID,DepID,Title}(Courses) ε Instruct_Sched Note that joining over attributes with common names simplifies the presentation and the definition of the extend operator. It does