第三题

题目：根据课件范式举例内容：创建学生表，院系表，学生分数表， 并举例中数据插入表中 。

使用3NF，消除非主属性对主键的部分依赖和传递依赖

1. 创建学生表并插入元素 ``` create table student( id VARCHAR(8), name VARCHAR(8), gender ENUM(‘男’, ‘女’), depart ENUM(‘计科系’, ‘法律系’) );

insert into student values(‘20200401’, ‘李小四’, ‘男’, ‘计科系’); insert into student values(‘20200402’, ‘张小兰’, ‘女’, ‘计科系’); insert into student values(‘20200403’, ‘王帅八’, ‘男’, ‘法律系’); insert into student values(‘20200404’, ‘李大四’, ‘男’, ‘法律系’);

![image.png](https://cdn.nlark.com/yuque/0/2021/png/12563649/1616829776437-07e869e4-75a3-44e2-b10e-5ad78159249a.png#align=left&display=inline&height=108&margin=%5Bobject%20Object%5D&name=image.png&originHeight=215&originWidth=428&size=17805&status=done&style=none&width=214)
2. 院系表

create table department(name ENUM(‘计科系’, ‘法律系’), chairman VARCHAR(8))； insert into department values(‘计科系’, ‘张宝’); insert into department values(‘法律系’, ‘刘德’);

![image.png](https://cdn.nlark.com/yuque/0/2021/png/12563649/1616830063209-26fa975a-94ea-4b19-a9bf-7580379cba38.png#align=left&display=inline&height=85&margin=%5Bobject%20Object%5D&name=image.png&originHeight=170&originWidth=436&size=10268&status=done&style=none&width=218)
3. 学生分数表

create table student_scores( student_id VARCHAR(8), subject ENUM(‘高等数学’, ‘大学英语’, ‘微机原理’, ‘线性代数’, ‘法律基础’), score INT ); insert into student_scores values(‘20200401’, ‘高等数学’, 89); insert into student_scores values(‘20200401’, ‘大学英语’, 89); insert into student_scores values(‘20200401’, ‘微机原理’, 89); insert into student_scores values(‘20200402’, ‘大学英语’, 89); insert into student_scores values(‘20200402’, ‘线性代数’, 89); insert into student_scores values(‘20200402’, ‘高等数学’, 89); insert into student_scores values(‘20200403’, ‘高等数学’, 89); insert into student_scores values(‘20200403’, ‘法律基础’, 98); insert into student_scores values(‘20200404’, ‘大学英语’, 90); insert into student_scores values(‘20200404’, ‘法律基础’, 99);

![image.png](https://cdn.nlark.com/yuque/0/2021/png/12563649/1616830671691-2a7ae113-9f4f-4c88-aceb-e221a54a0313.png#align=left&display=inline&height=299&margin=%5Bobject%20Object%5D&name=image.png&originHeight=338&originWidth=393&size=27908&status=done&style=none&width=348)<br />数值赋值错误，修改分数值：

UPDATE student_scores SET score=90 WHERE student_id=’20200401’ AND subject=’大学英语’; UPDATE student_scores SET score=99 WHERE student_id=’20200401’ AND subject=’微机原理’; UPDATE student_scores SET score=90 WHERE student_id=’20200402’ AND subject=’线性代数’; UPDATE student_scores SET score=99 WHERE student_id=’20200402’ AND subject=’高等数学’; ```

1. 听老师讲解后反思：

（1）分数可能会是小数，所以INT可以改为DECIMAL，FLOAT类型。
不建议用VARCHAR:
有的同学使用 VARCHAR(2)表示分数的类型，VARCHAR(2)在统计时可以自动转化成数值进行计算，不过转换过程中会浪费性能，所以这种方法没错但不是最优；
VARCHAR会造成误解，如果不小心存储了字符，就没办法转化为数字
（2）需要优化的地方
把每个命令写全。