student dictionary

最新推荐文章于 2023-02-20 15:01:28 发布

李小胖向前冲

最新推荐文章于 2023-02-20 15:01:28 发布

阅读量321

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/jdj17/article/details/117425644

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

学生学科字典

要求：

需要解决的问题

1、编码长度改变

如一级学科变成3位

2、编码方式改变

增加4级学科、将门类与一级学科合并

3、代码有效期改变

如某个二级学科从2010年10月1日开始不再使用

4、代码被覆盖或替换

如081202（计算机软件与理论）改变为软件工程，新增081204（计算机软件与理论）

5、要求保留原信息

在引用数据字典的表中的信息，要能查询到某人在2010年10月1日前的学科为计算机软件与理论，之后依然不变

但籍贯会发生变化（四川->重庆）（1997）

6、提交一个RAR压缩包

建立数据库和学科大类表、各级学科表和学生信息表，代码如下：


create database assignment___;

create table subjects___(
    id varchar(20) not null primary key,
    name varchar(50) not null,
    startdate date not null,
    enddate date);
create table subject1___(
    realid varchar(20) not null,
    id varchar(20) not null primary key,
    name varchar(50) not null,
    level int not null,
    fatherid varchar(20) not null foreign key references subjects(id),
    startdate date not null,
    enddate date);
create table subject2___(
    realid varchar(20) not null,
    id varchar(20) not null primary key,
    name varchar(50) not null,
    level int not null,
    fatherid varchar(20) not null foreign key references subject1___(id),
    startdate date not null,
    enddate date);
create table subject3___(
    realid varchar(20) not null,
    id varchar(20) not null primary key,
    name varchar(50) not null,
    level int not null,
    fatherid varchar(20) not null foreign key references subject2___(id),
    startdate date not null,
    enddate date);
create table subject4___(
    realid varchar(20) not null,
    id varchar(20) not null primary key,
    name varchar(50) not null,
    level int not null,
    fatherid varchar(20) not null foreign key references subject3___(id),
    startdate date not null,
    enddate date);
create table students___( 
    id varchar(20) not null primary key,
    name varchar(20) not null,
    hometown varchar(20) not null,
    majorid varchar(20) not null foreign key references subject2___(id),
    admissionDate date not null,
    graduateDate date);

简图如下：

插入数据

INSERT INTO subjects___ VALUES ('07','理学','1960-1-1',null);
INSERT INTO subjects___ VALUES ('08','工学','1945-6-6',null);
INSERT INTO subjects___ VALUES ('09','农学','1949-10-1',null);
INSERT INTO subject1___ VALUES ('0811','11','控制科学与工程',1,'08','1945-6-6',null);
INSERT INTO subject1___ VALUES ('0812','12','计算机科学与技术',1,'08','1960-11-17',null);
INSERT INTO subject1___ VALUES ('0813','13','建筑学',1,'08','1955-6-18',null);
INSERT INTO subject2___ VALUES ('081201','01','计算机系统结构',2,'12','1966-5-12',null);
INSERT INTO subject2___ VALUES ('081202','02','计算机软件与理论',2,'12','1970-3-3',null);
INSERT INTO subject2___ VALUES ('081203','03','计算机应用技术',2,'12','1960-11-17',null);
INSERT INTO subject3___ VALUES ('08120201','01','嵌入式软件',3,'02','1971-2-22',null);
INSERT INTO students___ VALUES ('20190666','袁鸡科','广州','03','2019-9-1','2023-7-1');
INSERT INTO students___ VALUES ('20190667','王逸凡','四川','02','2019-9-1','2023-7-1');
INSERT INTO students___ VALUES ('20190668','露露','广州','01','2016-9-1','2020-7-1');


注意majorid对应的是subject2的id，即他们学的都是计算机科学与技术专业大类。

注意majorid对应的是subject2的id，即他们学的都是计算机科学与技术专业大类。

初始建表时我只考虑了籍贯包括学生所在城市，为了简化数据库中不必要的信息，这里把籍贯拆分成省和市，事实上这已经能满足绝大部分实际需求。

update students___ set province = '广东' where name='袁鸡科' or name='露露';
update students___ set province = '四川' where name='王逸凡';
update students___ set hometown = '重庆' where name='王逸凡';
select * from subjects___;
select * from subject1___;
select * from subject2___;
select * from subject3___;
select * from students___;

问题1：编码长度改变

比如把一级学科“工学”的id变长，从原来的'08'变为'088'。即：

update subjects___ set id='088' where name='工学';
select * from subjects___;

得到subjects_ 和 subject1_ 表：

我们发现subject1___表中的realid和fatherid并没有改变，更别提 subject2 等等的表了。

如何在改变一个学科表的列id时同时改变它的下级学科表？

我们不妨考虑使用触发器

Create Trigger ttsubjects       

　　On    subjects___                            --在subjects___表中创建触发器       

　　for Update                          --为什么事件触发     

　　As                                      --事件触发后所要做的事情       

　   if Update(id)                  

　　begin

      Update subject1___           

　　Set realid=concat(i.id,s1.id)     

　　From subject1___ s1, Deleted   d ,Inserted i      --Deleted和Inserted临时表           

　　Where s1.fatherid=d.id  
       end

成功得到realid改变之后的结果，这里注意外键fatherid和主键id不会因此改,因为它们和其他表或本表存在约束，我们只需要改变表层的realid即可，这也是这个设计的精妙之处。

改进：我们发现一个bug，也就是说在第一次改了subjects.id之后subjects.id不等于subject1.fatherid，会导致之后改变subjects.id时触发器失效。这不是说明我们方法不行，只是说明我们没做的更彻底——把stubjects表中也加入初始值和id一样的realid，不改变主键id的值。代码如下：

ALTER TABLE subjects___
ADD realid varchar(20);
UPDATE subjects___ SET realid = id;--加入realid列


Create Trigger ttsubjects       

　　On    subjects___                            --在subjects___表中创建触发器       

　　for Update                          --为什么事件触发     

　　As                                      --事件触发后所要做的事情       

　   if Update(realid)                  

　　begin

      Update subject1___           

　　Set realid=concat(i.realid,s1.id)     

　　From subject1___ s1, Deleted   d ,Inserted i      --Deleted和Inserted临时表           

　　Where s1.fatherid=d.id  
       end

update subjects___ set realid='088' where name='工学';
select * from subjects___;
select * from subject1___;

同理，我们可以就像数据结构树一样间接递归使得改一个学科表的数据就可以使子学科表相应数据都改变。

就单纯的加入重复性的这个代码：


Create Trigger tt1subjects       

　　On    subject1___                            --在subjects___表中创建触发器       

　　for Update                          --为什么事件触发     

　　As                                      --事件触发后所要做的事情       

　   if Update(realid)                  

　　begin

      Update subject2___           

　　Set realid=concat(i.realid,s1.id)     

　　From subject2___ s1, Deleted   d ,Inserted i      --Deleted和Inserted临时表           

　　Where s1.fatherid=d.id  
       end

就创建了subject1和subject2中的触发器。

此时我们把工学对应的realid置回初始值08后再进行下面操作：

update subjects___ set realid='088' where name='工学';
select * from subjects___;
select * from subject1___;
select * from subject2___;

可以得到：

上图表明改变subjects表的realid递归地改变了subject1,subject2表的realid，但是没改变subject3的，为此我们再创建一个subject2和subject3的触发器即可，是不是很像数据结构中的树（考虑到元组）或链表（只考虑表）？


Create Trigger tt2subjects       

　　On    subject2___                            --在subjects___表中创建触发器       

　　for Update                          --为什么事件触发     

　　As                                      --事件触发后所要做的事情       

　   if Update(realid)                  

　　begin

      Update subject3___           

　　Set realid=concat(i.realid,s1.id)     

　　From subject3___ s1, Deleted   d ,Inserted i      --Deleted和Inserted临时表           

　　Where s1.fatherid=d.id  
       end

update subjects___ set realid='089' where name='工学';
select * from subjects___;
select * from subject1___;
select * from subject2___;
select * from subject3___;

问题2.编码方式改变

增加4级学科就是插入数据到subject4即可，

INSERT INTO subject4___ VALUES ('0812020101','01','嵌入式软件开发',4,'01','1971-2-23',null);

看到realid不断变长，有可能超出数据类型的范围，我们可以用问题一触发器方法对多个表的对应属性一起改变类型扩大容量，也可以一个个alter手动操作。

对于门类和一级学科合并，我们希望仍然保留一级学科信息，只不过它在整个设计中与其他表失去了约束等联系。我们是在合并学科，所以很自然的可以认为subject1和subjects存在的约束失效，而subject2和subject1的约束改为subject2和subjects的约束。一级学科subject1表中的外键确保了表间数据的关系的完整，但合并之前应该把它删掉，这种设计增大了工作量。至于subject2对于subject1的外键，我们只要把它改到subjects表上即可。

首先查询subject1的外键约束名

exec sp_helpconstraint subject1___

删掉外键约束

alter table subject1___ drop constraint FK__subject1___fathe__59E54FE7

添加合并日期到enddate中:

代码如下：

update subject1___ set enddate='2021-1-1' where name='计算机科学与技术';

同样的把subject2和subject1间的外键约束删掉，代码略。

但是我们去掉约束会导致触发器失效等等一系列问题，所以这不是理想的学科合并方式，不可取。

新思路：只改变被合并一级学科和其子学科的level属性，添加它的enddate属性，其他不变。要同时改变子学科属性，只需要添加递归触发器即可。代码略去。

问题3.代码有效期改变

直接用下列语句即可：

update subject1___ set enddate='2021-1-1' where name='计算机科学与技术';

问题4.代码被覆盖或替换

如081202（计算机软件与理论）改变为软件工程，新增081204（计算机软件与理论）

update subject2___ set name='软件工程' where name='计算机软件与理论';
INSERT INTO subject2___ VALUES ('081204','04','计算机软件与理论',2,'12','2021-5-1',null);

得到新的subject2表。

5、要求保留原信息

一个学生在毕业之后他的专业名发生改变，那么学生信息不变。

这是我们之前建立的学生表。我们看到我们最初建立的学生表中没有学生的专业名而只有专业id。

我们加入专业名：

ALTER TABLE students___
ADD majorname varchar(20);
UPDATE students___ SET majorname =subject2___.name from subject2___ where majorid=subject2___.id;

得到下表：

要达到上述要求，只需要在改专业名称时加上以下语句即可：


UPDATE students___ SET majorname =subject2___.name from subject2___ where majorid=subject2___.id and graduateDate>getdate();

这样只会保证毕业前的专业名改变会影响学生信息。

注意这里建立的学生表的专业只选取了二级学科，如果想要更全面的信息，则需要在表中插入三级学科、四级学科等等，如果一个学生的子学科中有信息，那么可以省略父级学科中的信息。

籍贯发生变化比如四川和重庆的例子：

在该例子中，重庆升级为省级的直辖市，那么体现在表中应该是province从四川改为重庆，hometown同样也设为重庆（市）。

UPDATE students___ SET province ='重庆' where province='四川' and hometown='重庆';

修改成功，可见操作还是非常简单的。

李小胖向前冲

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
student dictionary

学生学科字典要求：需要解决的问题1、编码长度改变如一级学科变成3位2、编码方式改变增加4级学科、将门类与一级学科合并3、代码有效期改变如某个二级学科从2010年10月1日开始不再使用4、代码被覆盖或替换如081202（计算机软件与理论）改变为软件工程，新增081204（计算机软件与理论）5、要求保留原信息在引用数据字典的表中的信息，要能查询到某人在2010年10月1日前的学科为计算机软件与理论，之后依然不变但籍贯会发生变化（四川->重庆）.
复制链接

扫一扫

专栏目录