数据库之完整性规则

1.利用Query Analyzer完成以下操作:

⑴ 向在实验四中所定义的数据表增加“备注”列,其数据类型为字符型,并查看新增列的值。

⑵ 对上述数据表增加主码约束条件,并观察在数据表中存在数据的情况下主码约束是否创建成功,然后再次执行实验四中实验内容3.2的操作,并观察记录执行结果。

⑶ 删除上述数据表中的数据,然后再删除该数据表,对这两个操作进行比较。

1.1
alter table 数据表 add 备注 char(4);
select 备注 from 数据表
结果:
NULL
NULL
NULL
NULL

1.2
alter table 数据表 add primary key(施工单位名称,日期,结算金额);
无法在表 '数据表' 中可为 Null 的列上定义 PRIMARY KEY 约束。
insert into 数据表(施工单位名称,日期,结算金额)
(select 施工单位名称,结算日期,SUM(结算金额)
from 预算结算入帐表
group by 施工单位名称,结算日期) 
结果:
作业公司作业二队        2016-05-26    21300.00    NULL
作业公司作业三队        2016-05-26    10600.00    NULL
作业公司作业一队        2016-05-26    11900.00    NULL
作业公司作业三队        2016-06-01    11300.00    NULL
作业公司作业二队        2016-05-26    21300.00    NULL
作业公司作业三队        2016-05-26    10600.00    NULL
作业公司作业一队        2016-05-26    11900.00    NULL
作业公司作业三队        2016-06-01    11300.00    NULL

1.3
truncate table 数据表
drop table 数据表 

2.利用Query Analyzer完成以下任务:

⑴ 对实验三中所定义的6个数据表增加主码约束条件,并观察在数据表中存在数据的情况下主码约束是否创建成功,然后执行以下2个操作,观察并记录实体完整性的检查和违约处理。

① insert  into  材料费表  values('zy2016001','wm004',100,10)

② insert  into  材料费表  values('zy2016002',NULL,200,10)

注:“材料费表”根据自己所命名的表名进行相应的替换。

⑵ 对实验三中所定义的6个数据表增加相应的参照完整性约束,并观察在数据表中存在数据的情况下参照完整性约束是否创建成功,然后执行以下操作,观察并记录参照完整性的检查和违约处理。

① 将(y007   油井   112203002)插入到油水井表。

② insert  into  材料费表  values('zy2016007','wm006',100,10)

③ 将作业项目编号zy2016001的施工单位修改为“作业公司作业五队”。

④ 将单位代码表中的(112202002  采油二矿二队)删除,查看油水井表和作业项目表中的数据有何变化。

⑤ 将物码表中的(wm004 材料四 袋)修改为(wm04 材料四 袋)。

⑥ 撤销上述成功的更新操作。

⑶ 对实验三中所定义的6个数据表按以下要求增加相应的完整性约束条件,并观察在数据表中存在数据的情况下完整性约束是否创建成功。

① 单位代码表的单位名称不能取空值、且取值唯一。

② 油水井表的井别只允许取“油井”或“水井”,单位代码不能取空值。

③ 物码表的名称规格不能取空值、且取值唯一,计量单位不能取空值。

④ 材料费表的消耗数量不能取空值,单价不能取空值。

⑤ 对作业项目表根据实际应用的要求定义适当的用户定义的完整性约束条件。

2.1
alter table 材料费表 
alter column 单据号 char(10) not null; 
alter table 材料费表
alter column 物码 char(5) not null; 
alter table  预算结算入帐表
alter column 单据号 char(10) not null; 
alter table 施工单位表 
alter column 施工单位名称 char(20) not null; 
alter table 物码表 
alter column 物码 char(5) not null;
alter table 物码表 
alter column 名称规格 char(10) not null;
alter table 单位代码表 
alter column 单位代码 char(20) not null;
alter table 油水井表 
alter column 井号 char(10) not null;

alter table 材料费表 add primary key(单据号,物码);
alter table 单位代码表 add primary key(单位代码);
alter table 施工单位表 add primary key(施工单位名称);
alter table 物码表 add primary key(物码);
alter table 油水井表 add primary key(井号);
alter table 预算结算入帐表 add primary key(单据号);

insert  into  材料费表  values('zy2016001','wm004',100,10)
结果:
消息 2627,级别 14,状态 1,第 1 行
违反了 PRIMARY KEY 约束 'PK__材料费表__9E8E740B08EA5793'。不能在对象 'dbo.材料费表' 中插入重复键。
语句已终止。
insert  into  材料费表  values('zy2016002',NULL,200,10)
结果:
消息 515,级别 16,状态 2,第 1 行
不能将值 NULL 插入列 '物码',表 'zyxm.dbo.材料费表';列不允许有 Null 值。INSERT 失败。
语句已终止。

2.2
alter table 材料费表 add foreign key(单据号) references 预算结算入帐表(单据号);
alter table 单位代码表 add foreign key(单位代码) references 预算结算入帐表(单位代码);
alter table 预算结算入帐表 add foreign key(井号) references 油水井表(井号);
alter table 预算结算入帐表 add foreign key(施工单位名称) references 施工单位表(施工单位名称);
alter table 预算结算入帐表 add foreign key(单位代码) references 单位代码表(单位代码);

begin transaction
insert into 油水井表 values('y007','油井','112203002');
insert  into  材料费表  values('zy2016007','wm006',100,10);
update 预算结算入帐表
set 施工单位名称='作业公司作业五队'
where 单据号='zy2016001';
delete from 单位代码表
where 单位代码='112202002' and 单位名称='采油二矿二队';
update 物码表
set 物码='wm04' 
where 物码='wm004' and 名称规格='材料四';
rollback;
结果:
消息 547,级别 16,状态 0,第 3 行
INSERT 语句与 FOREIGN KEY 约束"FK__材料费表__单据号__1920BF5C"冲突。该冲突发生于数据库"zyxm",表"dbo.预算结算入帐表", column '单据号'。
语句已终止。
消息 547,级别 16,状态 0,第 4 行
UPDATE 语句与 FOREIGN KEY 约束"FK__预算结算入帐表__施工单位名称__1CF15040"冲突。该冲突发生于数据库"zyxm",表"dbo.施工单位表", column '施工单位名称'。
语句已终止。
消息 547,级别 16,状态 0,第 7 行
DELETE 语句与 REFERENCE 约束"FK__预算结算入帐表__单位代码__1DE57479"冲突。该冲突发生于数据库"zyxm",表"dbo.预算结算入帐表", column '单位代码'。
语句已终止。

2.3
alter table 单位代码表
alter column 单位名称 char(20) not null;
alter table 单位代码表
add constraint danwei_unique unique(单位名称);

alter table 油水井表
add constraint jingbie check(井别 in('油井','水井'));
alter table 油水井表
alter column 单位代码 char(20) not null;

alter table 物码表
alter column 名称规格 char(10) not null;
alter table 物码表
add constraint guige_unique unique(名称规格);
alter table 物码表
alter column 计量单位 char(2) not null;

alter table 材料费表
alter column 消耗数量 int not null;
alter table 材料费表
alter column 单价 money not null;

alter table 预算结算入帐表
alter column 预算金额 money not null;

3.利用Query Analyzer完成以下操作:

⑴ 定义一个视图,用于保存作业项目表和材料费表的全部列。

⑵ 查询上面定义的视图,可任意组合查询条件,构造出2个查询。

⑶ 定义一个反映作业项目预算状态的视图,并向该视图插入('zy2016008','112202002','y005',10000,'张三', '2016-07-02'),查看作业项目表的数据有何变化。

⑷ 撤销上述成功的更新操作。

begin transaction
go
create view 项目_材料 as 
select 预算结算入帐表.*,物码,消耗数量,单价
from 材料费表,预算结算入帐表
where 材料费表.单据号=预算结算入帐表.单据号

go
select 消耗数量 from 项目_材料 where 单据号='zy2016001' and 物码='wm001';
select * from 项目_材料 where 物码='wm002';

go
create view 项目预算 as
select 单据号,单位代码,井号,预算金额,预算人,预算日期
from 预算结算入帐表
insert into
项目预算 values('zy2016008','112202002','y005',10000,'张三', '2016-07-02');
rollback;
结果:
200

zy2016001     112201001               y001          10000.00    张三          2016-05-01    2016-05-04    2016-05-25    作业公司作业一队        堵漏          7000.00    2500.00    1000.00    1400.00    11900.00    李四          2016-05-26    11900.00    王五          2016-05-28    wm002    200    10.00
zy2016002     112201002               y003          11000.00    张三          2016-05-01    2016-05-04    2016-05-23    作业公司作业二队        检泵          6000.00    1500.00    1000.00    2400.00    10900.00    李四          2016-05-26    10900.00    王五          2016-05-28    wm002    200    10.00
zy2016003     112201002               s001          10500.00    张三          2016-05-01    2016-05-06    2016-05-23    作业公司作业二队        调剖          6500.00    2000.00    500.00    1400.00    10400.00    李四          2016-05-26    10400.00    王五          2016-05-28    wm002    200    10.00
zy2016004     112202001               s002          12000.00    张三          2016-05-01    2016-05-04    2016-05-24    作业公司作业三队        解堵          6000.00    2000.00    1000.00    1600.00    10600.00    李四          2016-05-26    10400.00    赵六          2016-05-28    wm002    200    10.00
zy2016005     112202002               y005          12000.00    张三          2016-05-01    2016-05-04    2016-05-28    作业公司作业三队        防砂          7000.00    1000.00    2000.00    1300.00    11300.00    李四          2016-06-01    NULL    NULL    NULL    wm002    200    10.00

错误


You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注