代写数据库查询类作业，数据库选用SQLite，一个轻量级的数据库。
Requirement
Consider the following relational schema.
Student(SNO, SNAME)
Course(CNO, CNAME)
Prerequisite(CNO, PREREQ)
Professor(EID,PNAME, OFFICE, DEPT)
Enrollment(SNO, CNO, TERM, SECTION, MARK)
Schedule(CNO, TERM, SECTION, DAY, TIME, ROOM)
Class(CNO, TERM, SECTION, INSTRUCTOR)
The relation Professor stores information on professors. EID is the employee number of the professor. PNAME and OFFICE are the name of a professor and his/her office, respectively. DEPT indicates the department with which a professor is affiliated.
The relation Prerequisite stores the information on prerequisites of a course. For example a (“CS335”, “CS240”) tuple in Prerequisite indicates that CS240 is one of the prerequisites of CS335.
The Enrollment relation records who are the students enrolled in a class and the grade a student obtained for the course.
The Schedule relation stores the time and place a class is held while the Class relation records the instructor of a class.
All other relations should be self-explanatory.
For each relation scheme, the underlined attributes form the primary key.
You may assume the domain of DAY is {M, T, W, R, F}. The domain of TERM consists of strings of length 3, where the first character representing the term while the last two digits indicating the year, e.g. “F99” for Fall term 1999. The domain of MARK is the set of integers from 0 to 100. The domain of SECTION is a single integer from 1 to 9. The TIME attribute is defined on SQL Time domain. The ROOM domain are strings beginning with the building name. The domains of SNO, EID and INSTRUCTOR are integers. The domains of other attributes are character strings.
You are required to test your answers (ISQL statements) with an in-memory dbms called SQLite. If an ISQL statement is not tested or the statement cannot be compiled successfully, a zero MARK is automatically assigned to the query. The instructions on how to access SQLite and how to submit your assignment are given at the end of this assignment (Additional Assignment Information).
Answer each of the following queries, without any view, with an ISQL statement. Briefly explain the strategy behind the formulation of your ISQL statements. State clearly all (if any) reasonable assumptions you made in formulating your answer. In order to increase the readability of your queries use indentation where applicable. You are required to test your answers with the main-memory DBMS SQLite. The output should have no duplication.
Find rooms in Schedule relation that locate in the building MC and were not booked at 12:30pm in term F11. You may assume that all rooms in Schedule are available in every term. You may use time function in SQLITE.
A course is said to be advanced if there exists a prerequisite for the course. Find those advanced courses (CNO) which were taught by a Computer Science professor but some section of a prerequisite was taught by a non-Computer Science instructor. A professor taught a course exactly when the professor taught a class (a section of a course) in some term.
List department name (DEPT) and its departmental teaching average. The output is sorted on departmental teaching average. Given a department, the department teaching average is the average mark of all classes that were taught by a member with the department. We are only interested in department which has at least one faculty teaching a class in the Enrollment relation.
For each student enrolled in the class of CS348 in the year 2010, list the student name, the term and he difference of his/her mark from the average mark of the course. Note that the difference could be negative if the student’s mark is less than the average. Given a course, the average mark of the course is the average of all marks for students who enrolled in the course in the year 2010. The output should be sorted by SNO.
Find those courses (CNO) in Enrollment such that whenever a class of the course was offered in the year 2009, the class average was above 80%. Note that we are only interested in courses that were offered in the year 2009.
A student is considered to be good if whenever the student took a course with class (section) size greater than 20, the mark obtained by the student is greater than or equal to 85. Moreover, a good student must take at least 3 courses with class size greater than 20. Find students (SNO) and their average mark for those good students. The average mark is computed over all courses a student took. A student is said to take a course if the student enrolled in a section of the course with a non-null mark. We are only interested in those good students who took more than 10 courses (classes) with marks greater than 70.
How to test your queries
In order to test your queries you need to use SQLite. SQLite is one of the most popular open source relational main-memory database management systems. You can run your queries via the Linux server or you can download the interpreter executable to your own environment from www.sqlite.org. Since not every constructs are supported by SQLite, please refer to the system documentation in www.sqlite.org for more information on the language constructs. To run the queries in your own environment, you also need to download two more files (createschema.sql and populate.sql) from the course website. The interpreter executable and the two files should be in the same directory. To know more about SQLite or download the command prompt interpreter executable to your desktop or laptop, please go to www.sqlite.org.
The following assume you access the DBMS via the Linux server. After you logged in successfully into the server, type sqlite3 and then press Enter. By using this command the command prompt interpreter of SQLite will run and you will see something like the following messages:
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
Here you can type your “SQLite commands” and see the results.