Requirement： I. Query database with SQL Server. (30’, 2’ for each) 1. Create a table named Student using command Create Table. The table structure is as follows: Column Type Length Note ID Varchar 20 Student’s ID Name Varchar 10 Student’s Name Age Int Student’s Age Department Varchar 30 Student’s Dept. 2. Create a table named Course using command Create Table. The table structure is as follows: Column Type Length Note CourseID Varchar 15 Course’s ID CourseName Varchar 30 Course’s Name CourseBefore Varchar 15 Previous Co urse 3. Create a table named Choose using command Create Table. The table structure is as follows: Column Type Length Note ID Varchar 20 Student’s ID CourseID Varchar 15 Course’s ID Score Dec 5,2 Student’s Score 4. Insert 3 records into table Student using command Insert. ID Name Age Department 00001 ZhangSan 20 Computer Science 00002 LiSi 19 Computer Science 00003 WangWu 21 Computer Science 5. Insert 3 records into table Course using command Insert CourseID CourseName CourseBefore C1 Introduction to Computer － C2 PASCAL Programming Language C1 C3 Data Structure C2 6. Insert 7 records into table Choose using command Insert ID CourseID Score 00001 C1 95 00001 C2 80 00001 C3 84 00002 C1 80 00002 C2 85 00003 C1 78 00003 C3 70 7. Select the students’ ID and Name in Computer Science department using command select. 8. Select the students’ ID, Name, CourseName and Score using command select. 9. Select all students’ Information in descending order of the students’ ID. 10. Select every student’s average score. 11. Select the number of courses a student has chosen. 12. Select the number of students choosing a specific course. 13. Select the students’ ID who have chosen course C1 and got score over 80. 14. Select the students’ ID who have chosen course C2. 15. Select the average age of students in every department. II. Design a MIS for Computer Science college of SCUT. (45’) It is used to manage the information about courses and scores. It can record, modify, query, and get statistical data about the students’ and courses’ information. Students’ information includes: student’s ID, Name, Sex, Entrance Age, Entrance Year and Class. The Sex must be male or female. The Entrance Age is between 10 and 50. The length of student’s ID is 10. Courses’ information includes: course’s ID, Name, Teacher’s ID, Credit, Grade(which grade can take this course), Canceled Year(can be null). The length of course’s ID is 7. Only if a student’s grade is larger than the course’s Grade and he/she chose the course earlier than the course’s Canceled Year, the course can be choose. Teachers’ information includes: teacher’s ID (the length is 5), Name, Courses (that he/she can teach). Course choosing information includes: student’s ID, course’s ID, Teacher’s ID, Chosen year, Score. Student’s ID is a foreign key pointing to Students’ information. Course’s ID is a foreign key pointing to Courses’ information. Teacher’s ID is a foreign key pointing to Teachers’ information. If a student drops out, his/her course choosing information need to be deleted. 1. Information about student, course and course choosing can be modified. (6’, 2’ for each table) 2. Information about student, course and course choosing can be inserted and deleted. (6’, 2’ for each table) 3. Information about student and courses he/she chose can be queried based on a student’s ID or Name. If ID and Name are not given, show all students’ and their courses information. (12’) 4. The score of a course that a student chose can be queried based on a student’s Name (or ID) and a course’s Name (or ID). If no student’s and course’s ID or Name are given, show all scores of all students’ courses. (12’) 5. Information about course or course choosing can be queried based on course’s Name or ID. If no course’s Name or ID is given, show information about all courses or all course choosing. (7’) 6. Information about teacher or courses a teacher teaches can be queried based on a teacher’s Name or ID. If no teacher’s Name or ID is given, show information about all teachers or all courses they teach. (7’) 7. Average scores of a student, all students, students in the same class and a course can be queried. (15’) Authorities (6’) 1. A student can modify no information. 2. An administrator can modify information of students, courses and course choosing. But he/she can’t modify students’ score. 3. A teacher can modify students’ score. Use any databases, such as DBMS SQL Server, Oracle, or DB2, Use any programming languages, such as java, c++, asp.net, php. Use c/s or b/s Model. Submitted materials: 1. Program manual. (papers and electronic copy) 2. Source Code. (electronic copy) 3. Database design document, SQL commands creating tables or views. (papers and electronic copy)