Part $1$: University SQL $(40$ points$)$

Consider a database consisting of the following five tables. Attributes that are underlined have been

designated the primary key of their respective tables, and fields with identical names in different

tables can be safely assumed to be foreign keys. The Project Name and Course Name fields are

specified as UNIQUE, and all fields are required except for Major, which may be NULL $($undeclared$).$

Students$($SID$,$ Name, Major$)$

Projects$($PID$,$ P$\_$Name$)$

Courses$($CID$,$ C$\_$Name$)$

Members$($ID$,$ SID$)$

Enrollments$(,$ SID$)$

Write each of the following queries in a separate file named UniversityQuery$[$N$].$sql$,$ where $[$ N $]$ is

the number of the query as enumerated. Each query is worth $10$ points. Fields must be selected in

the order they are given at the beginning of each problem statement and results must be sorted as

specified at the end of each problem statement. We have not provided you with any sample data for

this part of the assignment, so it is suggested that you devise a way to test your scripts to ensure

that they are correct. There are no restrictions on the methods you use to complete the query:

nested queries, views, and set operations are all legal. In all instances, do not have duplicate rows

in your results.

Create a view called StudentPairs with two columns, SID$1$ and SID$2.$ The contents of this view should be all pairs of SIDs of two students who are enrolled in at least one common class but are not already partners on any project. You should report each student pair exactly once. The lower ID should be SID$1,$ and the higher ID should be SID$2.$ The contents of the view do not need to be sorted, and you do not need to drop the view. You must NOT create views other than the StudentPairs view.