Write a simple relational algebra interpreter in Python. A relation is nothing more than a table of fixed-size data rows, which you can store as a set of tuples. All the required functionality can reside in a single Table class. Here is the interface you should implement:

class Table: def __init__(self,name='',fields=tuple(),tups=None): ... # Relational operations: def select(self,field,val): ... def project(self,*fields): ... @staticmethod def join(tab1,tab2): ... def insert(self,*tup): ... def remove(self,field,val): ... # Serialization and text backup def store(self): ... @staticmethod def restore(fname): ... @staticmethod def read(fname): ... def write(self,fname): ... 

The file dbtest.py includes examples of using all methods, so make sure your class works with it. (dbtest.py is in the hw5.zip file linked near the bottom of this page.)

A Table has 3 data attributes: a name, a group of fields (which defines the columns), and a set of tuples (the rows). Use the __ prefix trick for these attributes so they are not inadvertently changed by users. Provide properties for them as follows: name: read/write fields: read only tuples: read only The following methods return a new Table as a result: select, project, join, restore, read. The first three should name the returned table result. The last two read their names and other data from files.The other methods do not return anything.

The first line is the table name, and the second line contains the field (column) names. The rest of the lines represent the tuples (rows) of the table.

The first line is the table name followed by the tuple of field names. After that each tuple is printed on a line by itself. Notice that the table name is underlined. Also notice that you will need a __str__ method that returns a string formatted as above for use by print. The write method writes a table to a text file in the same format as you saw in parts.txt.

The store and restore methods merely use pickle to serialize/deserialize a Table object. store writes to a file named <name>.db, where name is the internal name of the table. (Lookup pickle.dump and pickle.load in the Python documentation.)

The select method takes a field name and a value as parameters and returns a result table with each row having the value of the specified field matching the passed in value.

The project method takes one or more field names and returns a result table with only the associated columns from each row.

The join method prints the rows of the cartesian product of its two tables where the values in the like-named field are equal. For this assignment you may assume that joined tables only have one field name in common.

Note that the rows are such that pno fields in each original pair of rows are equal (but the pno field is not repeated in the result).

This notes page has a smaller example of a join: Homework 5 notes

The insert method is a no-brainer, except make sure that you only attempt to add tuples to a table that have the correct number of fields (we want the tables to be perfectly rectangular). And of course, tables allow no duplicate tuples. The remove method removes all tuples from a table where the indicated field matches the passed-in value.

All needed text files for relations and dbtest.py below:

===================#parts.txt=======================

parts pno,pname,color,weight,city p1,Nut,Red,12,London p2,Bolt,Green,17,Paris p3,Screw,Blue,17,Rome p4,Screw,Red,14,London p5,Cam,Blue,12,Paris p6,Cog,Red,19,London

==================#projects.txt=======================

projects jno,jname,city j1,Sorter,Paris j2,Punch,Rome j3,Reader,Athens j4,Console,Athens j5,Collator,London j6,Terminal,Oslo j7,Tape,London

===================#spj.txt=============================

spj sno,pno,jno,qty s1,p1,j1,200 s1,p1,j4,700 s2,p3,j1,400 s2,p3,j2,200 s2,p3,j3,200 s2,p3,j4,500 s2,p3,j5,600 s2,p3,j6,400 s2,p3,j7,800 s2,p5,j2,100 s3,p3,j1,200

===============#suppliers.txt===============

suppliers sno,sname,status,city s1,Smith,20,London s2,Jones,10,Paris s3,Blake,30,Paris s4,Clark,20,London s5,Adams,30,Athens s3,p4,j2,500 s4,p6,j3,300 s4,p6,j7,300 s5,p2,j2,200 s5,p2,j4,100 s5,p5,j5,500 s5,p5,j7,100 s5,p6,j2,200 s5,p1,j4,100 s5,p3,j4,200 s5,p4,j4,800 s5,p5,j4,400 s5,p6,j4,500

=============dbtest.py=================

from table import Table

if __name__ == '__main__': parts = Table.read('parts.txt') print(parts) suppliers = Table.read('suppliers.txt') print(suppliers) spj = Table.read('spj.txt') print(spj) projects = Table.read('projects.txt') print(projects) print(Table.join(parts,spj)) print(Table.join(suppliers,spj)) print(Table.join(projects,spj)) print(parts.fields) print() print(parts.select('pno','p2')) print(parts.project('pname','color')) print(parts.project('color'))

suppliers.write('supp2.txt') del suppliers suppliers = Table.read('supp2.txt') print(suppliers) projects.store() del projects projects = Table.restore('projects.db') print(projects)

projects.remove('city','Athens') projects.insert('j11','Disk','Baltimore') print(projects) print(Table.join(spj,projects))

''' Output: parts('pno', 'pname', 'color', 'weight', 'city') ===== ('p2', 'Bolt', 'Green', '17', 'Paris') ('p1', 'Nut', 'Red', '12', 'London') ('p5', 'Cam', 'Blue', '12', 'Paris') ('p6', 'Cog', 'Red', '19', 'London') ('p3', 'Screw', 'Blue', '17', 'Rome') ('p4', 'Screw', 'Red', '14', 'London')

suppliers('sno', 'sname', 'status', 'city') ========= ('s2', 'Jones', '10', 'Paris') ('s5', 'Adams', '30', 'Athens') ('s1', 'Smith', '20', 'London') ('s3', 'Blake', '30', 'Paris') ('s4', 'Clark', '20', 'London')

spj('sno', 'pno', 'jno', 'qty') === ('s4', 'p6', 'j3', '300') ('s1', 'p1', 'j1', '200') ('s2', 'p3', 'j7', '800') ('s3', 'p4', 'j2', '500') ('s5', 'p2', 'j4', '100') ('s5', 'p6', 'j2', '200') ('s2', 'p3', 'j5', '600') ('s2', 'p3', 'j3', '200') ('s5', 'p6', 'j4', '500') ('s5', 'p5', 'j5', '500') ('s5', 'p3', 'j4', '200') ('s5', 'p5', 'j4', '400') ('s2', 'p3', 'j2', '200') ('s5', 'p2', 'j2', '200') ('s1', 'p1', 'j4', '700') ('s2', 'p3', 'j4', '500') ('s5', 'p1', 'j4', '100') ('s2', 'p3', 'j1', '400') ('s2', 'p5', 'j2', '100') ('s3', 'p3', 'j1', '200') ('s5', 'p4', 'j4', '800') ('s4', 'p6', 'j7', '300') ('s5', 'p5', 'j7', '100') ('s2', 'p3', 'j6', '400')

projects('jno', 'jname', 'city') ======== ('j1', 'Sorter', 'Paris') ('j5', 'Collator', 'London') ('j6', 'Terminal', 'Oslo') ('j4', 'Console', 'Athens') ('j7', 'Tape', 'London') ('j3', 'Reader', 'Athens') ('j2', 'Punch', 'Rome')

result('pno', 'pname', 'color', 'weight', 'city', 'sno', 'jno', 'qty') ====== ('p3', 'Screw', 'Blue', '17', 'Rome', 's2', 'j6', '400') ('p3', 'Screw', 'Blue', '17', 'Rome', 's3', 'j1', '200') ('p2', 'Bolt', 'Green', '17', 'Paris', 's5', 'j4', '100') ('p5', 'Cam', 'Blue', '12', 'Paris', 's5', 'j7', '100') ('p6', 'Cog', 'Red', '19', 'London', 's5', 'j2', '200') ('p3', 'Screw', 'Blue', '17', 'Rome', 's2', 'j3', '200') ('p6', 'Cog', 'Red', '19', 'London', 's5', 'j4', '500') ('p6', 'Cog', 'Red', '19', 'London', 's4', 'j7', '300') ('p3', 'Screw', 'Blue', '17', 'Rome', 's2', 'j2', '200') ('p4', 'Screw', 'Red', '14', 'London', 's3', 'j2', '500') ('p5', 'Cam', 'Blue', '12', 'Paris', 's5', 'j5', '500') ('p1', 'Nut', 'Red', '12', 'London', 's1', 'j1', '200') ('p3', 'Screw', 'Blue', '17', 'Rome', 's2', 'j5', '600') ('p3', 'Screw', 'Blue', '17', 'Rome', 's5', 'j4', '200') ('p5', 'Cam', 'Blue', '12', 'Paris', 's5', 'j4', '400') ('p5', 'Cam', 'Blue', '12', 'Paris', 's2', 'j2', '100') ('p1', 'Nut', 'Red', '12', 'London', 's1', 'j4', '700') ('p1', 'Nut', 'Red', '12', 'London', 's5', 'j4', '100') ('p3', 'Screw', 'Blue', '17', 'Rome', 's2', 'j4', '500') ('p2', 'Bolt', 'Green', '17', 'Paris', 's5', 'j2', '200') ('p3', 'Screw', 'Blue', '17', 'Rome', 's2', 'j7', '800') ('p6', 'Cog', 'Red', '19', 'London', 's4', 'j3', '300') ('p4', 'Screw', 'Red', '14', 'London', 's5', 'j4', '800') ('p3', 'Screw', 'Blue', '17', 'Rome', 's2', 'j1', '400')

result('sno', 'sname', 'status', 'city', 'pno', 'jno', 'qty') ====== ('s4', 'Clark', '20', 'London', 'p6', 'j3', '300') ('s5', 'Adams', '30', 'Athens', 'p1', 'j4', '100') ('s2', 'Jones', '10', 'Paris', 'p3', 'j2', '200') ('s2', 'Jones', '10', 'Paris', 'p5', 'j2', '100') ('s3', 'Blake', '30', 'Paris', 'p4', 'j2', '500') ('s1', 'Smith', '20', 'London', 'p1', 'j4', '700') ('s2', 'Jones', '10', 'Paris', 'p3', 'j3', '200') ('s3', 'Blake', '30', 'Paris', 'p3', 'j1', '200') ('s5', 'Adams', '30', 'Athens', 'p5', 'j5', '500') ('s4', 'Clark', '20', 'London', 'p6', 'j7', '300') ('s2', 'Jones', '10', 'Paris', 'p3', 'j7', '800') ('s5', 'Adams', '30', 'Athens', 'p6', 'j2', '200') ('s5', 'Adams', '30', 'Athens', 'p2', 'j4', '100') ('s2', 'Jones', '10', 'Paris', 'p3', 'j5', '600') ('s5', 'Adams', '30', 'Athens', 'p5', 'j4', '400') ('s2', 'Jones', '10', 'Paris', 'p3', 'j6', '400') ('s1', 'Smith', '20', 'London', 'p1', 'j1', '200') ('s5', 'Adams', '30', 'Athens', 'p2', 'j2', '200') ('s2', 'Jones', '10', 'Paris', 'p3', 'j1', '400') ('s5', 'Adams', '30', 'Athens', 'p4', 'j4', '800') ('s5', 'Adams', '30', 'Athens', 'p5', 'j7', '100') ('s5', 'Adams', '30', 'Athens', 'p6', 'j4', '500') ('s2', 'Jones', '10', 'Paris', 'p3', 'j4', '500') ('s5', 'Adams', '30', 'Athens', 'p3', 'j4', '200')

result('jno', 'jname', 'city', 'sno', 'pno', 'qty') ====== ('j1', 'Sorter', 'Paris', 's2', 'p3', '400') ('j7', 'Tape', 'London', 's5', 'p5', '100') ('j4', 'Console', 'Athens', 's5', 'p2', '100') ('j7', 'Tape', 'London', 's2', 'p3', '800') ('j6', 'Terminal', 'Oslo', 's2', 'p3', '400') ('j7', 'Tape', 'London', 's4', 'p6', '300') ('j2', 'Punch', 'Rome', 's2', 'p5', '100') ('j4', 'Console', 'Athens', 's5', 'p4', '800') ('j4', 'Console', 'Athens', 's2', 'p3', '500') ('j4', 'Console', 'Athens', 's5', 'p1', '100') ('j5', 'Collator', 'London', 's5', 'p5', '500') ('j1', 'Sorter', 'Paris', 's3', 'p3', '200') ('j2', 'Punch', 'Rome', 's3', 'p4', '500') ('j4', 'Console', 'Athens', 's5', 'p3', '200') ('j4', 'Console', 'Athens', 's5', 'p5', '400') ('j2', 'Punch', 'Rome', 's5', 'p6', '200') ('j4', 'Console', 'Athens', 's1', 'p1', '700') ('j5', 'Collator', 'London', 's2', 'p3', '600') ('j2', 'Punch', 'Rome', 's5', 'p2', '200') ('j3', 'Reader', 'Athens', 's4', 'p6', '300') ('j2', 'Punch', 'Rome', 's2', 'p3', '200') ('j4', 'Console', 'Athens', 's5', 'p6', '500') ('j3', 'Reader', 'Athens', 's2', 'p3', '200') ('j1', 'Sorter', 'Paris', 's1', 'p1', '200')

('pno', 'pname', 'color', 'weight', 'city')

result('pno', 'pname', 'color', 'weight', 'city') ====== ('p2', 'Bolt', 'Green', '17', 'Paris')

result('pname', 'color') ====== ('Nut', 'Red') ('Cam', 'Blue') ('Cog', 'Red') ('Screw', 'Blue') ('Screw', 'Red') ('Bolt', 'Green')

result('color',) ====== ('Blue',) ('Red',) ('Green',)

suppliers('sno', 'sname', 'status', 'city') ========= ('s2', 'Jones', '10', 'Paris') ('s5', 'Adams', '30', 'Athens') ('s1', 'Smith', '20', 'London') ('s3', 'Blake', '30', 'Paris') ('s4', 'Clark', '20', 'London')

projects('jno', 'jname', 'city') ======== ('j1', 'Sorter', 'Paris') ('j5', 'Collator', 'London') ('j6', 'Terminal', 'Oslo') ('j4', 'Console', 'Athens') ('j7', 'Tape', 'London') ('j3', 'Reader', 'Athens') ('j2', 'Punch', 'Rome')

projects('jno', 'jname', 'city') ======== ('j1', 'Sorter', 'Paris') ('j5', 'Collator', 'London') ('j6', 'Terminal', 'Oslo') ('j7', 'Tape', 'London') ('j11', 'Disk', 'Baltimore') ('j2', 'Punch', 'Rome')

result('sno', 'pno', 'jno', 'qty', 'jname', 'city') ====== ('s2', 'p3', 'j1', '400', 'Sorter', 'Paris') ('s2', 'p3', 'j7', '800', 'Tape', 'London') ('s2', 'p3', 'j6', '400', 'Terminal', 'Oslo') ('s2', 'p3', 'j2', '200', 'Punch', 'Rome') ('s5', 'p5', 'j5', '500', 'Collator', 'London') ('s1', 'p1', 'j1', '200', 'Sorter', 'Paris') ('s4', 'p6', 'j7', '300', 'Tape', 'London') ('s5', 'p2', 'j2', '200', 'Punch', 'Rome') ('s5', 'p5', 'j7', '100', 'Tape', 'London') ('s3', 'p4', 'j2', '500', 'Punch', 'Rome') ('s2', 'p3', 'j5', '600', 'Collator', 'London') ('s3', 'p3', 'j1', '200', 'Sorter', 'Paris') ('s5', 'p6', 'j2', '200', 'Punch', 'Rome') ('s2', 'p5', 'j2', '100', 'Punch', 'Rome')