I HAVE ATTACHED THREE IMAGES

1. QUESTION

2. SCREENSHOT OF FILES PROFESSOR AND MAJOR

3. SCREENSHOT OF BOOK AND CITES

BUYS FILE IS AT THE END IN NORMAL TEXT FORM, PLEASE FIND AND THANKS IN ADVANCE.

buys

1023 2012 1023 2014 1040 2002 1001 2002 1001 2007 1001 2009 1001 2011 1001 2013 1002 2001 1002 2002 1002 2007 1002 2011 1002 2012 1002 2013 1003 2002 1003 2007 1003 2011 1003 2012 1003 2013 1004 2006 1004 2007 1004 2008 1004 2011 1004 2012 1004 2013 1005 2007 1005 2011 1005 2012 1005 2013 1006 2006 1006 2007 1006 2008 1006 2011 1006 2012 1006 2013 1007 2001 1007 2002 1007 2003 1007 2007 1007 2008 1007 2009 1007 2010 1007 2011 1007 2012 1007 2013 1008 2007 1008 2011 1008 2012 1008 2013 1009 2001 1009 2002 1009 2011 1009 2012 1009 2013 1010 2001 1010 2002 1010 2003 1010 2011 1010 2012 1010 2013 1011 2002 1011 2011 1011 2012 1012 2011 1012 2012 1013 2001 1013 2011 1013 2012 1014 2008 1014 2011 1014 2012 1017 2001 1017 2002 1017 2003 1017 2008 1017 2012 1020 2001 1020 2012 1022 2014

1. In the following questions, use the files professor.txt, majors.txt, book.txt, cites.txt, and buys.txt that are provided for this assignment Consider the following relation schemas about professors and books. Professor(Pid. Pname) Major(Pid, Major) Book(BookNo. Title, Price) Cites(BookNo, CitedBookNo) Buys(Pid, BookNo The relation Major stores professors and their majors. A professor can have multiple majors but we also allow that a professor has no major. A tuple (b. c) in the relation Cites indicates that the book with book number b cites the book with book number c. Note that a book may cite multiple other books. Also, a book does not have to cited. The primary keys of the relations are the underlined attributes and we assume the following foreign keys: Attribute in Relation References Primary Key of Relation Pid in Major Pid in Professor BookNo in Cites BookNo in Book CitedBookNo in Cites BookNo in Book Pid in Buys Pid in Professor BookNo in Buys BookNo in Book Furthermore, assume the following domains for the attributes: Attribute Domain Pid INTEGER Pid INTEGER Pname VARCHAR(15) Pname VARCHAR(15) Major VARCHAR(15) Major VARCHAR(15) BookNo INTEGER BookNo INTEGER Title VARCHAR(30) Title VARCHAR(30) Price INTEGER Price INTEGER CitedBookNo INTEGER CitedBookNo INTEGER Write the following queries as RA (relation algebra) expressions in the standard RA notation. Then, for each such RA expression, write a SQL query (possibly using the WITH statement) that mimics this expression. (a) Find the bookno of each book that was not bought by all professors who major in CS. (b) Find the pid and pname of each professor who has at least two majors. (C) Find the pid of each professor who bought exactly one book. (d) Find the pid of each professor who not only bought books that cost less than $50. (e) Find the bookno and title of each book that was only bought by the professor with Pid = 1023 (f) Find the pid and pname of each professor who bought at least two books that cost less than $40. (g) Find the pid and pname of each professor who bought a book that cites another book. (h) Find the bookno of each book that is not cited by a book that cost more than $40. (1) Find the bookno and title of each book with the second to lowest price. (1) Find each pair (p; b) such that s is the pid of a professor who bought a book that does not cite the book with bookno b. Professor.txt: major 1007, 1001, Jean 1002, Maria 1003, Anna 1004, Chin 1865, John 1006, Ryan Catherine 1008, Emma 1009, Jan 1010, Linda 1011, Nick 1012, Eric 1013, Lisa 1014, Filip 1015, Dirk 1016, Mary 1017, Ellen 1020, Greg 1022, Qin 1023, Melanie 1840, Pam 1001 1001 1002 1002 1003 1004 1006 1007 1007 1008 1009 1010 1011 1011 1012 1013 1013 1014 1017 1022 1015 Math Physics CS Math Math CS CS CS Physics Physics Biology Biology CS Math CS CS Psychology Theater Anthropology CS Chemistry Cites: book 2012 2008 2ees 2een 2001 2eez 2ee3 2003 2003 2010 2010 2010 2eie 2010 2010 2010 2010 2010 2010 2e1e 2010 2001 2011 2012 2002 2007 2003 2001 2004 2002 2001 2002 2003 2004 2005 2006 2007 2008 2009 2011 2013 2014 2001 Databases 2002 Operatingsystems 2003 Networks 2004 AI 2005 DiscreteMathematics 2006 SQL 2007 ProgrammingLanguages 2008 Datascience 2009 Calculus 2010 Philosophy 2012 Geometry 2013 RealAnalysis 2011 Anthropology 2014 Topology 40 25 20 45 20 25 15 50 10 25 80 35 50 70 1. In the following questions, use the files professor.txt, majors.txt, book.txt, cites.txt, and buys.txt that are provided for this assignment Consider the following relation schemas about professors and books. Professor(Pid. Pname) Major(Pid, Major) Book(BookNo. Title, Price) Cites(BookNo, CitedBookNo) Buys(Pid, BookNo The relation Major stores professors and their majors. A professor can have multiple majors but we also allow that a professor has no major. A tuple (b. c) in the relation Cites indicates that the book with book number b cites the book with book number c. Note that a book may cite multiple other books. Also, a book does not have to cited. The primary keys of the relations are the underlined attributes and we assume the following foreign keys: Attribute in Relation References Primary Key of Relation Pid in Major Pid in Professor BookNo in Cites BookNo in Book CitedBookNo in Cites BookNo in Book Pid in Buys Pid in Professor BookNo in Buys BookNo in Book Furthermore, assume the following domains for the attributes: Attribute Domain Pid INTEGER Pid INTEGER Pname VARCHAR(15) Pname VARCHAR(15) Major VARCHAR(15) Major VARCHAR(15) BookNo INTEGER BookNo INTEGER Title VARCHAR(30) Title VARCHAR(30) Price INTEGER Price INTEGER CitedBookNo INTEGER CitedBookNo INTEGER Write the following queries as RA (relation algebra) expressions in the standard RA notation. Then, for each such RA expression, write a SQL query (possibly using the WITH statement) that mimics this expression. (a) Find the bookno of each book that was not bought by all professors who major in CS. (b) Find the pid and pname of each professor who has at least two majors. (C) Find the pid of each professor who bought exactly one book. (d) Find the pid of each professor who not only bought books that cost less than $50. (e) Find the bookno and title of each book that was only bought by the professor with Pid = 1023 (f) Find the pid and pname of each professor who bought at least two books that cost less than $40. (g) Find the pid and pname of each professor who bought a book that cites another book. (h) Find the bookno of each book that is not cited by a book that cost more than $40. (1) Find the bookno and title of each book with the second to lowest price. (1) Find each pair (p; b) such that s is the pid of a professor who bought a book that does not cite the book with bookno b. Professor.txt: major 1007, 1001, Jean 1002, Maria 1003, Anna 1004, Chin 1865, John 1006, Ryan Catherine 1008, Emma 1009, Jan 1010, Linda 1011, Nick 1012, Eric 1013, Lisa 1014, Filip 1015, Dirk 1016, Mary 1017, Ellen 1020, Greg 1022, Qin 1023, Melanie 1840, Pam 1001 1001 1002 1002 1003 1004 1006 1007 1007 1008 1009 1010 1011 1011 1012 1013 1013 1014 1017 1022 1015 Math Physics CS Math Math CS CS CS Physics Physics Biology Biology CS Math CS CS Psychology Theater Anthropology CS Chemistry Cites: book 2012 2008 2ees 2een 2001 2eez 2ee3 2003 2003 2010 2010 2010 2eie 2010 2010 2010 2010 2010 2010 2e1e 2010 2001 2011 2012 2002 2007 2003 2001 2004 2002 2001 2002 2003 2004 2005 2006 2007 2008 2009 2011 2013 2014 2001 Databases 2002 Operatingsystems 2003 Networks 2004 AI 2005 DiscreteMathematics 2006 SQL 2007 ProgrammingLanguages 2008 Datascience 2009 Calculus 2010 Philosophy 2012 Geometry 2013 RealAnalysis 2011 Anthropology 2014 Topology 40 25 20 45 20 25 15 50 10 25 80 35 50 70