Create a view called OlderFriend(ID1,name1,grade1,ID2,name2,grade2) containing the names and grades of friends who are at least two years apart in school, with name1/grade1 being the younger student. After reloading the original database, your view should initially contain (in some order):

Create triggers (SQLite) or rules (PostgreSQL) that enable deletions and insertions to be executed on view OlderFriend. For insertions, only permit new friendships that obey the restrictions of the view and do not create duplicates. Make sure to maintain the symmetric nature of the underlying Friend relation even though OlderFriend is not symmetric: a tuple (A,B) is in Friend if and only if (B,A) is also in Friend. a.) Execute the following deletions:

delete from OlderFriend where name2 = 'Alexis'; delete from OlderFriend where ID1 = 1381; Check the resulting database by writing SQL queries to compute the number of tuples in the Friend table and OlderFriend view. Compare your results against ours (in Query Results at the bottom of the page).

*a.) Friend contains 36 tuples and OlderFriend contains 1 tuple

b.) Then execute the following insertions: insert into OlderFriend values (1510, 'Jordan', 9, 1304, 'Jordan', 12); insert into OlderFriend values (1510, 'Jordan', 9, 1468, 'Kris', 10); insert into OlderFriend values (1510, 'Jordan', 9, 1468, 'Kris', 11); insert into OlderFriend values (1510, 'John', 9, 1247, 'Alexis', 11); insert into OlderFriend select H1.ID as ID1, H1.name as name1, H1.grade as grade1, H2.ID as ID2, H2.name as name2, H2.grade as grade2 from Highschooler H1, Highschooler H2 where H1.grade >= 10; Check the resulting database by writing SQL queries to compute the number of tuples in the Friend table and OlderFriend view. Compare your results against ours.

*b.) Friend contains 68 tuples and OlderFriend contains 17 tuples

-------------------Here is the SQL file----------------------------

/* Create the schema for our tables */ create table Highschooler(ID int, name text, grade int); create table Friend(ID1 int, ID2 int); create table Likes(ID1 int, ID2 int);

/* Populate the tables with our data */ insert into Highschooler values (1510, 'Jordan', 9); insert into Highschooler values (1689, 'Gabriel', 9); insert into Highschooler values (1381, 'Tiffany', 9); insert into Highschooler values (1709, 'Cassandra', 9); insert into Highschooler values (1101, 'Haley', 10); insert into Highschooler values (1782, 'Andrew', 10); insert into Highschooler values (1468, 'Kris', 10); insert into Highschooler values (1641, 'Brittany', 10); insert into Highschooler values (1247, 'Alexis', 11); insert into Highschooler values (1316, 'Austin', 11); insert into Highschooler values (1911, 'Gabriel', 11); insert into Highschooler values (1501, 'Jessica', 11); insert into Highschooler values (1304, 'Jordan', 12); insert into Highschooler values (1025, 'John', 12); insert into Highschooler values (1934, 'Kyle', 12); insert into Highschooler values (1661, 'Logan', 12);

insert into Friend values (1510, 1381); insert into Friend values (1510, 1689); insert into Friend values (1689, 1709); insert into Friend values (1381, 1247); insert into Friend values (1709, 1247); insert into Friend values (1689, 1782); insert into Friend values (1782, 1468); insert into Friend values (1782, 1316); insert into Friend values (1782, 1304); insert into Friend values (1468, 1101); insert into Friend values (1468, 1641); insert into Friend values (1101, 1641); insert into Friend values (1247, 1911); insert into Friend values (1247, 1501); insert into Friend values (1911, 1501); insert into Friend values (1501, 1934); insert into Friend values (1316, 1934); insert into Friend values (1934, 1304); insert into Friend values (1304, 1661); insert into Friend values (1661, 1025); insert into Friend select ID2, ID1 from Friend;

insert into Likes values(1689, 1709); insert into Likes values(1709, 1689); insert into Likes values(1782, 1709); insert into Likes values(1911, 1247); insert into Likes values(1247, 1468); insert into Likes values(1641, 1468); insert into Likes values(1316, 1304); insert into Likes values(1501, 1934); insert into Likes values(1934, 1501); insert into Likes values(1025, 1101);

1381 Tiffany 9 1247 Alexis 11 1709 Cassandra 9 1247 Alexis 11 1782 Andrew 101304 Jodan 12 1381 Tiffany 9 1247 Alexis 11 1709 Cassandra 9 1247 Alexis 11 1782 Andrew 101304 Jodan 12