CSE 344 Final: CSE 344 UW Final Exam Solution 11 Spring
Document Summary
Consider a social network database, about people and their relationships. Here person. pid is a key, and relationship. pid1 and relationship. pid2 are foreign keys; rel is a string representing the relation type, and can be friend or enemy. Note that the relationship is not necessarily symmetric: if alice is friend with bob, this does not imply that bob is friend with alice. (a) (10 points) write the sql statements that de ne the relational schema for this database. Assume that pid"s are integers, and name and rel are character strings. Solution: create table person( pid int primary key, name varchar(20)); create table relationship( pid1 int references person, rel varchar(20), pid2 int references person); June 8, 2011 (b) (10 points) write a sql query that computes, for each person, the total number of their friends. Your query should return results containing the pid, the name, and the count. Note that your query must return exactly one answer for every person in person.
