Csc 1302, Honors Principles of Computer Science II (Fall 2021)

Week 8 (15 October 2021)

Join Operator

During this week you will write the following methods:
  1. (Tuple.java): 
      // This method attempts to construct a "joined" tuple out of this.tuple and t2.tuple
  // If the two tuples can join, the joined tuple is returned; otherwise "null" is returned.
  // Let ENROLL(SID:INTEGER,SNAME:VARCHAR,PHONE:INTEGER,MAJOR:VARCHAR,GPA:DECIMAL)
  // and STUDENT(SID:INTEGER,COURSE:VARCHAR,GRADE:VARCHAR)
  // let this.tuple = 1111:Robert Adams:1234:Computer Science:4.0:
  // and   t2.tuple = 1111:Database Systems:A:
  // Then, joined tuple will be: 1111:Robert Adams:1234:Computer Science:4.0:Database Systems:A:
  // As another example,
  // let this.tuple = 1111:Robert Adams:1234:Computer Science:4.0:
  // and   t2.tuple = 1114:Database Systems:B:
  // These two tuples do not join because in the first tuple SID=1111 and in the second
  // tuple SID=1114; So, the result should be "null"
  public Tuple join(Tuple t2, ArrayList attr, ArrayList dom) {
    // Collect information about join columns in the following 4 array lists:
    //   leftJoinColumns, leftJoinDomains, rightJoinColumns, rightJoinDomains
    // Verify if the two tuples can join; if not return null
    // If tuples can join then produce the joined tuple and return it.
  }
  2. (Relation.java) Join Operator: 
      // The join operator combines two relations into one based on common columns in the two relations
  // The schema of the join relation contains all columns of the first relation followed by all columns
  // of the second relation, somewhat like the times operator, except that the common columns appear only
  // once in the join relation
  // Two tuples join with each other only if they have the same values under the common columns.
  public Relation join(Relation r2) {
    // Construct empty array lists for attr and dom
    // copy all attributes and corresponding domains from this.attributes and this.domains to attr and dom
    // copy attributes and corresponding domains from r2.attributes and r2.domains only if they do not appear
    //    in this.attributes
    // Construct new relation object, rel
    // Using nested for loops obtain cloned tuple t1 from this.table and cloned tuple t2 from r2.table
    //   and try to join these two tuples; if result is not null, add to rel
    // return rel
  }

Download the Driver Programs and implement all the methods in this assignment. Compile and run the driver programs. You should see the following output when you run Drivera.java and Driverb.java: 

[raj@tinman w7]$ java Drivera
ENROLL(SID:INTEGER,SNAME:VARCHAR,PHONE:INTEGER,MAJOR:VARCHAR,GPA:DECIMAL)
Number of tuples: 4

1111:Robert Adams:1234:Computer Science:4.0:
1112:Charles Bailey:5656:Computer Science:3.5:
1113:David Beatle:1212:Mathematics:3.5:
1114:Graham Gooch:5678:Computer Science:3.5:

REL2(SID:INTEGER,COURSE:VARCHAR,GRADE:VARCHAR)
Number of tuples: 3

1111:Database Systems:A:
1114:Database Systems:B:
1114:Java Programming:A:

STUDENT_JOIN_ENROLL(SID:INTEGER,SNAME:VARCHAR,PHONE:INTEGER,MAJOR:VARCHAR,GPA:DECIMAL,COURSE:VARCHAR,GRADE:VARCHAR)
Number of tuples: 3

1111:Robert Adams:1234:Computer Science:4.0:Database Systems:A:
1114:Graham Gooch:5678:Computer Science:3.5:Database Systems:B:
1114:Graham Gooch:5678:Computer Science:3.5:Java Programming:A:

[raj@tinman w7]$ java Driverb
STUDENT(SID:INTEGER,SNAME:VARCHAR,PHONE:INTEGER,MAJOR:VARCHAR,GPA:DECIMAL)
Number of tuples: 4

1111:Robert Adams:1234:Computer Science:4.0:
1112:Charles Bailey:5656:Computer Science:3.5:
1113:David Beatle:1212:Mathematics:3.5:
1114:Graham Gooch:5678:Computer Science:3.5:

ENROLL(SID:INTEGER,COURSE:VARCHAR,GRADE:VARCHAR)
Number of tuples: 3

1111:Csc 4710:A:
1114:Csc 2310:B:
1114:Csc 2310:A:

COURSES(COURSE:VARCHAR,TITLE:VARCHAR,CREDITS:INTEGER)
Number of tuples: 3

Csc 4710:Database Systems:4:
Csc 2010:Java I:3:
CSc 2310:Java II:3:

ANSWER(SNAME:VARCHAR)
Number of tuples: 1

Robert Adams: