Scribe: Sebastian Poliak and Vitor Carmo Vannuchi
× : Cartesian product – select * from R,S
∪ : Union – select * from R union select * from S
∩ : Intersection – select * from R intersect select * from S
− : Difference – select * from R minus(except) select * from S
⨝: Join – select * from R,S where [pred]
π projection – column filtering – select [Attributes] from R
σ selection – row filtering – select * from R where [pred]
The operators difference and join are non-essential – can be created by other operators. Join – special case of cross product plus selection
we download a file called person.sql and then open the terminal directing yourself for the same directory of the person.sql and then typing the commands
next line explanation: person.db = Database file, binary representation of the data. person.sql = command for creating tables. $ sqlite3 person.db < person.sql Evalyn John|Mary Robert|Evalyn Susan|Evalyn
$ ls -la person.db Mar 14 11:06 person.db
$ sqlite3 person.db
$ select * from person; Albert|M|20 Dennis|M|40 Evalyn|F|20 John|M|60 Mary|F|40 Robert|M|60 Susan|F|40
$ select * from birth; Dennis|Albert John|Mary Mary|Albert Robert|Evalyn Susan|Evalyn Susan|Richard
select * from marriage; Dennis|Mary|20 Robert|Susan|30
1) Relation Answer(A) consisting of all ages of people -Answer: select Age from Person;
2) All women who are less or equal than 33 years old. -Answer: select Name from Person where age <=32 and sex=’F’;
3) Answer(Parent, Daughter) -Answer: select Parent,Child as Daughter from Person,Birth where Child=Name and Sex=’F’;
4) Answer(Father,Daughter) -Answer: select Parent as Father,Child as Daughter from Person,Birth as p1 where parent=person.name and child=p1.name and person.sex=’M’ and p1.sex=’F’;
5) Answer(Father_in_law,Son_in_law)
6) Answer(Grandparent,Grandchild)
7) Answer(great_grand_parent, great_grand_child)
8) Answer(great_great_grand_parent, great_great_grand_child)
9) Answer(Ancestor, Descendant)
10) Answer(A) consisting of persons that are not ages of marriages. (Looking for people that have age that is not in the marriage table)