实验四：完整性实验

 【实验目的】

掌握实体完整性、参照完整性和用户自定义完整性的定义的维护方法

【实验内容】

要实现这样一个功能：医生根据药品价表选择处方药品，录入数量和使用天数，系统根据医生选择的药品和录入的信息自动生成处方主表和处方明细表。功能涉及的表和属性字段分别为：

处方主表(处方日期,处方号,处方总金额)

处方明细表(处方日期,处方号,序号,药品代码,药品名称,药品规格,数量,单价,使用天数,处方明细唯一号)

药品价表(药品代码,药品规格,单价,开始时间,结束时间)

药品名称字典(药品代码,药品名称)

根据功能完成下列建表、完整性定义和验证的操作。建表完成后执行后面的插入语句。注意表的属性类型，要符合插入语句中数据的类型，才能正常插入数据：

1、建立表药品名称字典，药品代码为主码，主码通过列级约束条件进行定义。 

个人答案：

create table 药品名称字典
(药品代码 varchar2(100) primary key,
药品名称 varchar2(100)
);
INSERT INTO 药品名称字典 VALUES ('D00001','酮替芬分散片');
INSERT INTO 药品名称字典 VALUES ('D00002','地诺孕素片');
INSERT INTO 药品名称字典 VALUES ('D00003','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白');
INSERT INTO 药品名称字典 VALUES ('D00004','复方风湿宁片');

select * from 药品名称字典 for update;

 参考答案：

CREATE TABLE 药品名称字典
(药品代码 VARCHAR2(20) PRIMARY KEY,
 药品名称 VARCHAR2(50)
);
INSERT INTO 药品名称字典 VALUES ('D00001','酮替芬分散片');
INSERT INTO 药品名称字典 VALUES ('D00002','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白');
INSERT INTO 药品名称字典 VALUES ('D00003','地诺孕素片');
INSERT INTO 药品名称字典 VALUES ('D00004','复方风湿宁片');

2、建立表药品价表，药品代码和药品规格共同为主码,开始时间不能为空

个人答案：

create table 药品价表
(药品代码 varchar2(100),
药品规格 varchar2(100),
单价 number(10,3),
开始时间 date not null,
结束时间 date,
primary key (药品代码，药品规格)
);
INSERT INTO 药品价表 VALUES ('D00001','1mg*24',18,to_date('2022-01-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);
INSERT INTO 药品价表 VALUES ('D00002','2mg*28',36.5,to_date('2022-01-15 09:20:00','yyyy-mm-dd hh24:mi:ss'),to_date('2022-11-17 17:21:11','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO 药品价表 VALUES ('D00003','12.5mg',216,to_date('2022-04-07 08:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);
INSERT INTO 药品价表 VALUES ('D00004','0.21g*60',7.8,to_date('2022-06-19 08:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);

select * from 药品价表 for update;

 

参考答案：

CREATE TABLE 药品价表
(药品代码 VARCHAR2(20),
 药品规格 VARCHAR2(20),
 单价 NUMBER,
 开始时间 DATE NOT NULL,
 结束时间 DATE,
 PRIMARY KEY(药品代码,药品规格)
);
INSERT INTO 药品价表 VALUES ('D00001','1mg*24',18,to_date('2022-01-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);
INSERT INTO 药品价表 VALUES ('D00002','2mg*28',36.5,to_date('2022-01-15 09:20:00','yyyy-mm-dd hh24:mi:ss'),to_date('2022-11-17 17:21:11','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO 药品价表 VALUES ('D00003','12.5mg',216,to_date('2022-04-07 08:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);
INSERT INTO 药品价表 VALUES ('D00004','0.21g*60',7.8,to_date('2022-06-19 08:00:00','yyyy-mm-dd hh24:mi:ss'),NULL); 

 

3、建立处方明细表，处方日期、处方号、序号共同为主码；药品代码参照药品名称字典中的药品代码；药品代码和药品规格共同参照药品价表中的药品代码和药品规格；处方明细唯一号不允许重复和不允许为空；每个药品的使用天数至少1天并且不能超过30天。

个人答案：

create table 处方明细表
(处方日期 date,
处方号 number(30),
序号 number(30),
药品代码 varchar2(100),
药品名称 varchar2(100),
药品规格 varchar2(100),
数量 number(30),
单价 number(10,2),
使用天数 number(30)check(使用天数 >=1 and 使用天数 <=30),
处方明细唯一号 varchar2(100) unique not null,
primary key(处方日期,处方号,序号),
foreign key(药品代码) references 药品名称字典(药品代码),
foreign key(药品代码,药品规格) references 药品价表(药品代码,药品规格)
);

INSERT INTO 处方明细表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20220526100011');
INSERT INTO 处方明细表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,2,'D00002','地诺孕素片','2mg*28',1,36.5,7,'20220526100012');

select * from 药品价表 for update;

select * from 处方明细表 for update;

参考答案：

CREATE TABLE 处方明细表
(处方日期 DATE,
 处方号 NUMBER,
 序号 NUMBER,
 药品代码 VARCHAR2(20),
 药品名称 VARCHAR2(50),
 药品规格 VARCHAR2(20),
 数量 NUMBER,
 单价 NUMBER,
 使用天数 NUMBER,
 处方明细唯一号 VARCHAR2(20) UNIQUE NOT NULL,
 PRIMARY KEY(处方日期,处方号,序号),
 FOREIGN KEY (药品代码) REFERENCES 药品名称字典(药品代码),
 FOREIGN KEY (药品代码,药品规格) REFERENCES 药品价表(药品代码,药品规格),
 CHECK( 使用天数 >= 1 AND 使用天数 <= 30 )
);
INSERT INTO 处方明细表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20220526100011');
INSERT INTO 处方明细表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,2,'D00002','地诺孕素片','2mg*28',1,36.5,7,'20220526100012');

 

4、建立处方主表，处方日期、处方号共同为主码。

个人答案：

create table 处方主表
(处方日期 date,
处方号 number(30),
处方总金额 number(10,2),
primary key(处方日期,处方号)
);
INSERT INTO 处方主表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,54.5);

select * from 处方主表 for update;

 

 参考答案：

CREATE TABLE 处方主表
(处方日期 DATE,
 处方号 NUMBER,
 处方总金额 NUMBER,
 PRIMARY KEY(处方日期,处方号)
);
INSERT INTO 处方主表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,54.5);

 

5、修改完整性。在已建立好的表处方明细表中，添加参照完整性，处方明细表中的处方日期和处方号共同参照处方主表的处方日期和处方。

个人答案：

alter table 处方明细表 add foreign key(处方日期,处方号)references 处方主表(处方日期,处方号);
select * from 处方明细表 for update;

 

参考答案： 

ALTER TABLE 处方明细表 ADD FOREIGN KEY (处方日期,处方号) REFERENCES 处方主表(处方日期,处方号);

 

6、验证完整性一。执行下列语句，记录执行结果。

个人答案（运行成功）： 

INSERT INTO 处方主表 VALUES (to_date('2022-07-11','yyyy-mm-dd'),10002,432);
INSERT INTO 处方明细表 VALUES (to_date('2022-07-11','yyyy-mm-dd'),10002,1,'D00003','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白','12.5mg',2,216,2,'20220526100021');

 

7、验证完整性二。执行下列语句，记录执行结果。

个人答案（运行失败）：

select * from 处方明细表 for update;
select * from 处方主表 for update;
INSERT INTO 处方明细表 VALUES (to_date('2022-07-11','yyyy-mm-dd'),10003,1,'D00004','复方风湿宁片','0.21g*60',1,7.8,30,'20220526100031');
INSERT INTO 处方主表 VALUES (to_date('2022-07-11','yyyy-mm-dd'),10003,432);

参考答案（结果）：

执行失败:ORA-02291:违反完整性约束条件（system.SYS_C00575390）-未找到父项关键字

 

8、验证完整性三。执行下列删除语句，确定哪组语句能够正常执行。

个人答案：

DROP TABLE 药品名称字典;
DROP TABLE 药品价表;
DROP TABLE 处方主表;
DROP TABLE 处方明细表;