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

Week 8 (13 October 2023)

Join Operator

During this week you will write the following methods:

1. (Tuple.py):

     ## 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 None 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 None
     def join(self,t2):
       ## collect information about "common" attributes and their positions in the respective lists.
       ## Verify if the two tuples can join; if not return None
       ## If tuples can join then produce the joined tuple and return it.
   	pass
   

2. (Relation.py) 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 (keep first occurrence)
     ## Two tuples join with each other only if they have the same values under the common columns.
     def join(self,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 None, add to rel
       ## return rel
   	pass
   

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.py and Driverb.py:

---

Mac-mini:week8-join raj$ python3 Drivera.py 
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: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:

Mac-mini:week8-join raj$

---

Mac-mini:week8-join raj$ python3 Driverb.py
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:

Mac-mini:week8-join raj

---