The origin of student Achievement database design

Resigned in recent days, bored at home. One day in the Baidu know to see a problem, more interested in the study of a bit.

Source of the problem:

Http://zhidao.baidu.com/question/1495573046720461139.html?fr=qlquick&entry=qb_browse_word

Problem Description:

According to the XSCJ database, compile the stored procedure to complete some common business functions. Make the following changes in the existing XSCJ database.
A new column of "state" is added to the table XSB, indicating that the student is reading, "1" is reading (the default state), "2" means dropping out of probation, and "3" means dropping out of school.
Write several stored procedures to complete the following business functions:
(1) By inputting the student's number or name (both ways), display the result set of all the record of the students ' achievements.
(2) by inputting the student's number and the name of a course, the output parameters are returned to the students in the examination of the course.
(3) To complete the review of student status of the function. That is, in each academic year, check the number of each student's failing course, re-set the student's status, and list all students who are not in the normal reading state.
L If the number of failed courses exceeds half of all of its courses, when the "status" value is 1, it is modified to 2, indicating that the withdrawal probation, when the "status" value is 2 o'clock, change it to 3, indicating withdrawal.
L If the number of failed courses is no longer more than half of all of its courses, and the student is in the drop-out probation state, which is the "status" value of 2 o'clock, it is changed to 1, indicating that the health is reset to "normal read" status.
(4) by inputting the class number and the course number, the average grade, the highest achievement, the lowest achievement, the passing number, the number of failed, the pass rate and so on in a class of a course. If the pass rate of the course is less than 50%, the result is an increase of 10 points for each student in the course, and 5 points for 90 + students. For students who have exceeded 100 points after they have increased their scores, they will be treated by 100 points. Once the results have been adjusted, a new summary of the results needs to be presented.
Data Sheet CJB,XSB,KCB, listed directly in Chinese

When you see this problem, the person who asked the question did not give the database. To solve this problem, you need to go through the following steps: Designing a database, simulating data, writing a problem-solving stored procedure

The origin of student Achievement database design