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: