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

Week 9 (20 October 2016)

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 DriverW9a.java and DriverW9b.java:

---

[raj@tinman w7]$ java DriverW9a
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 DriverW9b
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: