UMEH_Server/script.sql at master · UMHelper/UMEH_Server · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
create table if not exists umeh.comment
(
    id         int auto_increment
        constraint `PRIMARY`
        primary key,
    content    longtext      null,
    attendance float         null,
    pre        float         null,
    grade      float         null,
    hard       float         null,
    reward     float         null,
    recommend  float         null,
    assignment float         null,
    result     float         null,
    pub_time   datetime      null,
    upvote     int default 0 null,
    downvote   int default 0 null,
    teach      int           not null,
    constraint comment_id_uindex
        unique (id)
);

create index teaach
    on umeh.comment (teach);

create table if not exists umeh.course
(
    offering_unit         char(255)     null,
    offering_department   char(255)     null,
    new_code              char(255)     not null
        constraint `PRIMARY`
        primary key,
    old_code              char(255)     null,
    course_title_eng      char(255)     null,
    course_title_chi      char(255)     null,
    credits               int default 3 null,
    course_duration       char(255)     null,
    medium_of_instruction char(255)     null,
    constraint course_New_code_uindex
        unique (new_code)
);

create table if not exists umeh.prof
(
    name char(255) not null
        constraint `PRIMARY`
        primary key,
    constraint prof_name_uindex
        unique (name)
);

create table if not exists umeh.schedule
(
    id            int auto_increment
        constraint `PRIMARY`
        primary key,
    teach         int null,
    time_location int null,
    constraint schedule_id_uindex
        unique (id)
);

create index schedule_teach
    on umeh.schedule (teach);

create index schedule_time_location
    on umeh.schedule (time_location);

create table if not exists umeh.statistics
(
    name    char(255) not null
        constraint `PRIMARY`
        primary key,
    course  int       null,
    comment int       null,
    constraint statistics_name_uindex
        unique (name)
);

create table if not exists umeh.teach
(
    id         int auto_increment
        constraint `PRIMARY`
        primary key,
    course     char(255) null,
    prof       char(255) null,
    result     float     null,
    comments   int       null,
    attendance float     null,
    grade      float     null,
    hard       float     null,
    reward     float     null,
    constraint teach_id_uindex
        unique (id)
);

create index course
    on umeh.teach (course);

create index prof
    on umeh.teach (prof);

create table if not exists umeh.time_location
(
    id       int auto_increment
        constraint `PRIMARY`
        primary key,
    date     char(255) null,
    time     char(255) null,
    location char(255) null,
    constraint time_location_id_uindex
        unique (id)
);