1. select student.sname, avg(takes.grade) from student, takes where student.sid = takes.sid group by student.sid, student.sname select student.sname from student where 3.0 < all (select takes.sid from takes where student.sid = takes.sid) select name from student where sid not in (select takes.sid from takes, courses where takes.cid = courses.cid and courses.dept <> student.dept) 3. a. Customer --> Department b. AB --> C, B-->D, C --> A In ABCD: B+ = BD Decompose into: BD and BAC In BAC: C+ = AC Decompose into: AC and BC The complete decomposition is: BD, AC, BC Alternative decomposition: In ABCD: C+ = CA Decomose into: CA and CBD In CBD: B+ = BD Decompose into: BD and BC The complete decomposition is: CA, BD, BC (same as above). 4. /db/website[document/author/text()="John Smith"]/url for $r in /db let $a = distinct-values( for $w in $r/website, $x in $w/document/auhtor/text() where count(distinct-values($w/document[author/text()=$x]/word/text())) > 5000 return $x ) for $z in $a return $z CREATE TABLE WEBSITE ( URL varchar(50) PRIMARY KEY ) CREATE TABLE DOCUMENT ( DOCID varchar(50) PRIMARY KEY URL varchar(50) REFERENCES WEBSITE(URL) AUTHOR varchar(50) ) CREATE TABLE WORD ( DOCID varchar(50) REFERENCES DOCUMENT(DOCID) WORD varchar(100) ) CREATE TABLE HREF ( DOCID varchar(50) REFERENCES DOCUMENT(DOCID) URL varchar(100) )