School system

Experiment
I. Query database with SQL Server.
1) Create a table named Student using command Create Table. The table structure is as follows:
Column
Type
Length
Note

ID
Varchar
20
Students ID

Name
Varchar
10
Students Name

Age
Int

Students Age

Department
Varchar
30
Students Dept.

2) Create a table named Course using command Create Table. The table structure is as follows:
Column
Type
Length
Note

CourseID
Varchar
15
Courses ID

CourseName
Varchar
30
Courses Name

CourseBefore
Varchar
15
Previous Course

3) Create a table named Choose using command Create Table. The table structure is as follows:
Column
Type
Length
Note

ID
Varchar
20
Students ID

CourseID
Varchar
15
Courses ID

Score
Dec
5,2
Students 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 students 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.
Materials to be submitted: 1) Report; 2) source code

II. Design a MIS for Computer Science college of SCUT.
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: students 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 students ID is 10.
Courses information includes: courses ID, Name, Teachers ID, Credit, Grade(which grade can take this course), Canceled Year(can be null). The length of courses ID is 7. Only if a students grade is larger than the courses Grade and he/she chose the course earlier than the courses Canceled Year, the course can be choose.
Teachers information includes: teachers ID (the length is 5), Name, Courses (that he/she can teach).
Course choosing information includes: students ID, courses ID, Teachers ID, Chosen year, Score. Students ID is a foreign key pointing to Students information. Courses ID is a foreign key pointing to Courses information. Teachers 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 students ID or Name. If ID and Name are not given, show all students and their courses information.
4. The score of a course that a student chose can be queried based on a students Name (or ID) and a courses Name (or ID). If no students and courses ID or Name are given, show all scores of all students courses.
5. Information about course or course choosing can be queried based on courses Name or ID. If no courses Name or ID is given, show information about all courses or all course choosing.
6. Information about teacher or courses a teacher teaches can be queried based on a teachers Name or ID. If no teachers Name or ID is given, show information about all teachers or all courses they teach.
7. Average scores of a student, all students, students in the same class and a course can be queried. Authorities
8. A student can modify no information.
9. An administrator can modify information of students, courses and course choosing. But he/she cant modify students score.
10. 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.