实习日记2018-08-06 之 设计一套大学学生管理系统数据库

一、    整体要求

　　　　我们要设计一套大学学生管理系统数据库，用来管理学生的基本信息、学生的课业信息以及学生在学校除学习外的一些相关信息。本次数据库作业基于该系统来进行表结构的设计。要求表结构的设计　　尽量合理，数据库的操作尽量符合规范。进行表设计的过程中，可以借助于SQLYog自带的工具进行设计，但是更推荐全程用SQL语句来完成设计。

1.     信息

　　记录学生的学号、姓名、性别、出生日期、籍贯、班级、院系、学生卡号、每学期所选科目、科目成绩、学分、GPA、学生卡食堂消费情况、图书馆借书情况、信息中心上网记录、所在宿舍信息。

2.     表结构

　　根据信息要求，进行表结构设计，来记录要求中的所有学生信息。

3.     数据

　　表结构设计完成后，可以构造一部分数据插入数据库。构造的数据中必须包含以下相关数据：

　　　　1）  借阅红楼梦这本书的借书记录。

　　　　2）  微积分这门课挂科的记录。

　　　　3）  学生单月食堂消费满10次，但是消费总额不超过100元的记录

　　　　4）  至少包含20名学生

　　　　5）  至少包含3个院系

　　　　6）  至少包含3届学生，至少有两届学生包含多个学期的记录

　　　　7）  至少存在1次换宿舍的情形，且存在学生在同一宿舍

4.创建表

　　1）表结构基础要求：

CREATE TABLE test_timestamp(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8

以上几个属性每个表中必须要有！！！

　　2）具体表创建

CREATE TABLE students(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id INT(10) UNSIGNED NOT NULL,
  student_name VARCHAR(20) NOT NULL,
  sex VARCHAR(2) NOT NULL CHECK(sex LIKE '男' OR '女'),
  birthday TIMESTAMP NOT NULL,
  birth_place VARCHAR(20) NOT NULL,
  class int(10) NOT NULL REFERENCES classes(class_id),
  card_id INT(10) NOT NULL,
  get_credit INT(10) NOT NULL DEFAULT 0,
  gap INT(10) NOT NULL,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE classes(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  class_id INT(10) NOT NULL,
  class_name VARCHAR(10) NOT NULL,
  college_id INT(10) NOT NULL,
  time_of_enrollment TIMESTAMP NOT NULL,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE colleges(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  college_id INT(10) NOT NULL,
  college_name VARCHAR(20) NOT NULL,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE courses(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  course_id INT(10) NOT NULL,
  course_name VARCHAR(20) NOT NULL,
  learning_time VARCHAR(10) NOT NULL,
  time_of_start INT(10) NOT NULL REFERENCES term(term_id),
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE terms(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  term_id INT(10) NOT NULL,
  term_name VARCHAR(10) NOT NULL,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE college_term_courses( 
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    term_id INT(10) NOT NULL REFERENCES term(term_id), 
    college_id INT(10) NOT NULL REFERENCES colleges(college_id), 
    course_id INT (10) NOT NULL REFERENCES courses(courses_id), 
    credit INT(10) NOT NULL ,
    state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1', 
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (id) 
) ENGINE=INNODB DEFAULT CHARSET=utf8; 


CREATE TABLE student_courses(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  id_c INT(10) NOT NULL REFERENCES college_term_courses(id),
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE food_records(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  card_id INT(10) NOT NULL REFERENCES students(card_id),
  eating_time TIMESTAMP NOT NULL,
  money INT(10) NOT NULL,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE read_records(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  card_id INT(10) NOT NULL REFERENCES students(card_id),
  book_id INT(10) NOT NULL REFERENCES book(book_id),
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE books(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  book_id INT(10) NOT NULL,
  book_name VARCHAR(20) NOT NULL,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE net_records(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  card_id INT(10) NOT NULL REFERENCES students(card_id),
  net_time TIMESTAMP NOT NULL,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE dorms(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  dorm_id INT(10) NOT NULL,
  dorm_name INT(10) NOT NULL, 
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE student_dorms(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  dorm_id INT(10) NOT NULL,
  student_id INT(10) NOT NULL REFERENCES students(student_id),
  check_in_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  move_out_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  state TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8

 5.填入数据

 6.数据查询

1）    借阅红楼梦这本书的借书记录。
SELECT *
FROM books AS a JOIN read_records AS b
ON a.`book_id`=b.`book_id`
WHERE a.`book_name`='红楼梦'

2）    微积分这门课挂科的记录。
SELECT *
FROM student_courses AS a JOIN college_term_courses AS b
ON a.`id_c`=b.`id` 
WHERE grade<60 AND course_id = (SELECT course_id FROM courses WHERE course_name='微积分')
                
3）    学生单月食堂消费满10次，但是消费总额不超过100元的记录
SELECT student_name
FROM students
WHERE card_id IN(SELECT card_id
        FROM food_records
        GROUP BY card_id
        HAVING COUNT(eating_time)>=10 AND SUM(money)<=100)
        
4）    至少包含20名学生
SELECT *
FROM students
5）    至少包含3个院系
SELECT *
FROM colleges

6）    至少包含3届学生，至少有两届学生包含多个学期的记录
SELECT *
FROM classes

SELECT student_id,term_id
FROM student_courses AS a,college_term_courses AS b
WHERE a.`id_c`=b.`id` 


7）    至少存在1次换宿舍的情形，且存在学生在同一宿舍
SELECT student_id
FROM student_dorms
GROUP BY student_id
HAVING COUNT(dorm_id)>1

 二、数据库总结

问题总结：  

    1.表之间建立连接，创建关系表，
        如学期、学院和课程之间的关系表，要写出每个学院每学期上哪些课程，并设置每学期的课程状态，已过的学期课程置为2
        学生选课时从学院_学期_课程中选课
    2.日志
        如食堂、信息中心、图书馆借书记录
        记录下发生的时间、动作、人物及结果
    3.注意实体间的关系
        是一对多还是多对多
        根据实体间关系确定是否需要联系表
    4.注意数据冗余
        大部分时候可直接利用id，除非其id属性有特殊含义
        当两个属性有唯一关联时，可只写一个属性
        当每条记录内容皆无重复时，其他表引用时可考虑使用id代替其余属性
    5.慎用 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    6.表名、属性名要用小写名词，表名需用名词复数
    7.时间属性要用timestamp类型
    8.注意置状态位，防止记录冲突
    9.创建表时一定要加上id、state、createtime、updatetime属性，字符集为utf8，引擎为Innodb
    10.注意分析各内容间的联系，再创建表
    11.create table student_1 like student
        insert into student_1 select * from student
    12.引用外键时一定要保证数据类型及长度与原表中数据一致
    13.SQL查询时，尽量少使用子查询，多用join，当数据多时，子查询效率较慢

三、数据库创建第一版

 （未注意表与表、实体与实体间的关系，表名不够清晰明了，未注意数据的冗余！！！）

CREATE TABLE lst_students (
stu_id INT(10) NOT NULL,
stu_name VARCHAR(20) NOT NULL,
stu_sex VARCHAR(8) CHECK (stu_sex='male' OR stu_sex='female'),
stu_birthday date,
stu_native_place VARCHAR(30),
stu_class VARCHAR(20),
stu_institute VARCHAR(20),
stu_card VARCHAR(20) REFERENCES lst_card_eating(card_id),
stu_state TINYINT(1),
stu_createtime date,
stu_updatetime date,
PRIMARY KEY(stu_id)
);
CREATE TABLE lst_card_eating(
card_id INT(20),
eating_cost INT(20),
eating_times INT(10),
eating_state TINYINT(1),
eating_createtime date,
eating_updatetime date, 
PRIMARY KEY(card_id)
);
CREATE TABLE lst_card_reading (
card_id INT(20),
reading_book VARCHAR(20),
reading_state TINYINT(1),
reading_createtime date,
reading_updatetime date,
PRIMARY KEY(card_id)
);
CREATE TABLE lst_card_net (
card_id INT(20),
net_times INT(10),
net_state TINYINT(1),
net_createtime date,
net_updatetime date,
PRIMARY KEY(card_id)
);
CREATE TABLE lst_dorm (
stu_id INT(10) REFERENCES lst_students(stu_id),
dorm_id INT(10) NOT NULL,
dorm_state TINYINT(1),
dorm_createtime date,
dorm_updatetime date,
PRIMARY KEY(dorm_id,stu_id)
);
CREATE TABLE lst_course (
cou_id INT(10) NOT NULL,
cou_name VARCHAR(20) NOT NULL,
cou_credit INT(10) NOT NULL,
cou_state TINYINT(1),
cou_createtime date,
cou_updatetime date,
PRIMARY KEY(cou_id)
);
CREATE TABLE lst_study (
stu_id INT(10) REFERENCES lst_students(stu_id),
cou_id INT(10) REFERENCES lst_course(cou_id),
grade INT(10),
GPA INT (10),
study_state TINYINT(1),
study_createtime date,
study_updatetime date,
PRIMARY KEY(stu_id,cou_id)
);

完成时间2018-08-08，经历两版，历时2天