admin 管理员组文章数量: 887019
2023年12月22日发(作者：什么是局部变量什么是全局变量)
《数据库系统原理》实验报告
学 生 实 验 报 告
(理工类)
课程名称:数据库系统原理
专业班级: 14软件工程1班
学生学号: 1412101055 学生姓名: 孟祥辉
所属院部: 软件工程学院 指导教师: 麻春艳
20 15 ——20 16 学年 第 二 学期 金陵科技学院教务处制
实验报告书写要求
实验报告原则上要求学生手写,要求书写工整。若因课程特点需打印的,要遵照以下字体、字号、间距等的具体要求。纸张一律采用A4的纸张。
实验报告书写说明
实验报告中一至四项内容为必填项,包括实验目的与要求;实验仪器与设备;实验内容与过程;实验结果与分析。各院部可根据学科特点与实验具体要求增加项目。
填写注意事项
(1)细致观察,及时、准确、如实记录。
(2)准确说明,层次清晰。
(3)尽量采用专用术语来说明事物。
(4)外文、符号、公式要准确,应使用统一规定的名词与符号。
《数据库系统原理》实验报告
(5)应独立完成实验报告的书写,严禁抄袭、复印,一经发现,以零分论处。
实验报告批改说明
实验报告的批改要及时、认真、仔细,一律用红色笔批改。实验报告的批改成绩采用百分制,具体评分标准由各院部自行制定。
实验报告装订要求
实验批改完毕后,任课老师将每门课程的每个实验项目的实验报告以自然班为单位、按学号升序排列,装订成册,并附上一份该门课程的实验大纲。
《数据库系统原理》实验报告
实验项目名称:数据库定义与操作语言 实验学时: 2
同组学生姓名: 孟陈、陈晓雪、季佰军
实验地点: 1318
实验日期: 5、19 实验成绩:
批改教师: 批改时间:
《数据库系统原理》实验报告
一、实验目的
1、理解与掌握数据库DDL语言,能够熟练地使用SQL DDL语句创建、修改与删除数据库、模式与基本表。
2、掌握SQL册亨徐设计基本规范,熟练运用SQL语言实现数据基本查询,包括单表查询、分组统计查询与连接查询
3、掌握SQL嵌套查询与集合查询等,
各种高级查询的设计方法等、
4、熟悉数据库的数据更新操作,能够使用sql语句对数据库进行数据的插入、修改、删除操作。
5、熟悉sql语言有关系图的操作,能够熟练使用sql语言来创建需要的视图,定义数据库外模式,并能使用所创建的视图实现数据管理。
6、掌握所以设计原则与技巧,能够创建合适的索引以提高数据库查询、统计分析效率。
二、实验内容与要求
1、理解与掌握SQL DDL语句的语法,特别就是各种参数的具体含义与使用方法;使用sql语句创建、修改与删除数据库、模式与基本表。掌握sql语句常见语法错误的调试方法。
2、针对TPC-H数据库设计各种单表查询sql语句、分组统计查询语句;设计单个表针对自身的连接查询,涉及多个表的连接查询。理解与掌握sql查询语句各个子句的特点与作用,按照sql程序设计规范写出具体的sql查询语句,并调试通过。
3、针对TPC-H数据库,证券分析用户查询要求,设计各种嵌套查询与集合查询。
4、针对TPC-H数据库设计单元主唱入、批量数据插入、修改数据与删除数据的sql语句。理解与掌握insert、update、delete语法结构的各个组成成分,结合嵌套sql子查询,分别设计几个不同形式的插入、修改与删除数据的语句,并调试成功。
5、针对给定的数据库模式,以及相应的应用要求,创建视图与带WITH CHECK
OPTION的视图,并验证视图WITH CHECK OPTION选项的有效性。理解与掌握试图消解执行原理,掌握可更新视图与不可更新视图的区别。
6、针对给定的数据库模式与具体应用需求,创建唯一索引、函数索引、复合索引等;修改索引;删除索引。设计相应的sql查询验证索引有效性,学习利用EXPLAIN命令分析sql查询就是否使用了所创建的索引,并能够分析其原因,执行sql查询并估算索引提高查询效率的百分比,要求实验数据达到10万条记录以上的数据量,以便验证所以效果、
三、实验过程
1、数据库定义实验
（1）定义数据库
采用中文字符集创建名为TCHP的数据库。
CREATE
DATABASE TPCH ENCODING=’GBK’;
（2）定义模式
在数据库TPCH中创建名为SALES的模式。
Create SCHEMA Sales;
（3）定义基本表
在TPCH数据库的Sales模式中创建8个基本表。
《数据库系统原理》实验报告
/*设置当前会话的搜索路径为sales模式、public模式,基本表就会自动创建在sales模式下。*/
SET SEARCH_PATH TO Sales,
Public;
CREATE TABLE Region(
regionkey INTEGER PRIMARY
KEY,
name CHAR(25),
comment VARCHAR(152));
CREATE TABLE
Nation(
nationkey INTEGER PRIMARY KEY,
name CHAR(25),
address VARCHAR(40),
regionkey INTEGER REFERENCES REGION(REGIONKEY),
comment VARCHAR(152));
CREATE
TABLE
Supplier(
suppkey INTEGER PRIMARY KEY,
name CHAR(25),
address VARCHAR(40),
nationkey INTEGER REFERENCES Nation(nationkey),
phone CHAR(15),
acctbal REAL,
comment VARCHAR(101));
CREATE TABLE Part(
partkey INTEGER PRIMARY KEY,
name VARCHAR(55),
mfgr CHAR(25), /*制造厂*/
brand CHAR(10),
type VARCHAR (25),
size INTEGER,
container CHAR(10),
retailprice REAL,
comment VARCHAR(23));
CREATE TABLE PartSupp(
partkey INTEGER REFERENCES Part(partkey),
suppkey INTEGER REFERENCES Supplier(suppkey),
availqty INTEGER,
supplycost REAL ,
comment varchar(199),
PRIMARY KEY (parkey,suppkey));
CREATE TABLE Costomer(
custkey INTEGER PRIMARY KEY,
name VARCHAR(25),
address VARCHAR(40),
nationkey INTEGER REFERENCES Nation(nationkey),
phone CHAR(15),
《数据库系统原理》实验报告
acctbal REAL,
mktsegment CHAR(10),
comment VARCHAR(117));
CREATE TABLE Orders(
orderkey INTEGER PRIMARY KEY,
custkey INTEGER REFERENCES Customer(custkey),
orderstatus CHAR(1),
totalprice REAL,
orderdate DATE,
orderpriority INTEGER,
comment VARCHAR(79));
CREATE TABLE Lineitem(
orderkey INTEGER REFERENCES Order(orderkey),
partkey INTEGER REFERENCES Part(partkey),
suppkey INTEGER REFERENCES Supplier(suppkey),
linenumber INTEGER,
quantity REAL,
extendedprice REAL,
discount REAL,
tax REAL,
returnflag CHAR(1),
linestatus CHAR(1),
shipinstruct CHAR(25),
shipmode CHAR(10),
comment VARCHAR(44),
PRIMARY KEY(orderkey,linenumber),
FOREIGN KEY(Partkey,suppkey) REFERENCES PartSupp(partkey,suppkey));
2、数据基本查询
(1)单表查询(实现投影操作)
查询供应商的名称、地址与联系电话。
SELECTE name,address,phone FROMSupplier;
(2)单表查询(实现选择操作)
查询最近一周内提交的总价大于1000元的订单的编号、顾客编号等订单的所有信息。
SELECT *FROM Sales、Orders WHERE CURRENT_DATE-orderdata<7 AND
totalprice >1000;
(3)不带分组过滤条件的分组统计查询
统计每个顾客的订购金额
SELECT C、custkey ,SUM(O、totalprice)
FROM customer C,Orders O
WHERE C、custkey=O、custkey
GROUP BY C、custkey;
（4）带分组过滤条件的分组统计查询
查询订单平均金额超过1000元的顾客编号及其姓名
《数据库系统原理》实验报告
SELECT C、custkey,MAX(C、name)
FROM Customer C,Orders O
WHERE C、custkey=O、custkey
GROUP BY C、custkey;
HAVING AVG(O、totalprice)>1000;
（5）表单自身连接查询
查询与“金仓集团”在同一个国家的供应商编号、名称与地址信息。
SELECT F、suppkey,F、name,F、address
FROM Supplier F,Supplier S
WHERE F、nationkey=S、nationkey AND S、name='金仓集团';
（6）两表连接查询(普通连接)
查询供应价格大于零售价格的零件名、制造商名、零售价格与供应价格。
SELECT P、name,P、mfgr,P、retailprice,PS、supplycost
FROM Part P,Partsupp PS
WHERE P、retailprice>PS、supplycost;
（7）两表连接查询(自然连接)
查询供应价格大于零售价格的零件名、制造商名、零售价格与供应价格。
SELECT P、name,P、mfgr,P、retailprice,PS、supplycost
FROM Part P,Partsupp PS
WHERE P、partkey=PS、partkey AND
P、retailprice>PS、supplycost;
(8)三表连接查询
查询顾客“苏举库”订购的订单编号、总价及其订购的零件编号、数量与明细价格。
SELECT O、orderkey,O、totalprice,L、partkey,L、quantity,L、extendedprice
FROM Custom C,Orders O,Lineitem L
WHERE C、custkey=O、custkey AND O、orderkey=L、orderkey AND C、name='苏举库';
3、数据高级查询实验
(1)IN嵌套查询
查询订购了“海大”制造的“船舶模拟驾驶舱”的顾客。
SELECT custkey,name
FROM Customer
WHERE custkey IN ( SELECT O、custkey
FROM Orders O,Lineitme L,PartSupp PS,Part P
WHERE O、orderkey=L、orderkey AND
L、partkey=PS、partkey AND
L、suppkey=PS、suppkey AND
PS、partkey=P、partkey AND
P、mfgr='海大' AND P、name='船舶模拟驾驶舱');
SELECT custkey,name
FROM Customer
WHERE cuskey IN ( SELECT O、custkey
FROM Orders O,Lineitem L,Part P
《数据库系统原理》实验报告
WHERE O、orderkey=L、orderkey AND
L、partkey=P、partkey AND
p、mfgr='海大' AND P、name='船舶模拟驾驶舱');
(2)单层EXISTS嵌套查询
查询没有购买过“海大”制造的“船舶模拟驾驶舱”的顾客。
SELECT custkey,name
FROM Customer
WHERE NOT EXISTS( SELECT O、custkey
FROM Orders O,Lineitem L,PartSupp PS,Part P
WHERE C、cuskey=O、custkey AND
O、orderkey=L、orderkey AND
L、partkey=PS、partkey AND
L、suppkey=PS、suppkey AND
PS、partkey=P、partkey AND
p、mfgr='海大' AND P、name='船舶模拟驾驶舱');
(3)双层EXISTS嵌套查询
查询至少购买过顾客“张三”购买过的全部零件的顾客姓名。
SELECT CA、name
FROM Customer CA
WHERE NOT EXISTS
(SELECT *
FROM Customer CB,Oders OB,Lineitem LB
WHERE CB、custkey=OB、custkey AND
OB、orderkey=LB、orderkey AND
CB、name='张三' AND
NOT EXISTS(SELECT *
FROM Orders OC,Lineitem LC
WHERE CA、custkey=LC、custkey AND
OC、orderkey=LC、orderkey AND
LB、suppkey=LC、suppkey AND
LB、partkey=LC、partkey));
(4)FROM子句中的嵌套查询
查询订单平均金额超过1万元的顾客中的中国籍顾客信息。
SELECT C、*
FROM Customer C,(SELECT custkey
FROM Orders
GROUP BY custkey
HAVING AVG(totalprice)>10000) B,Nation N
WHERE C、custkey=B、custkey AND
C、nationkey=N、nationkey AND N、name='中国';
(5)集合查询(交)
查询顾客“张三”与“李四”都订购过的全部零件的信息。
SELECT P、*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P
《数据库系统原理》实验报告
WHERE C、custkey=O、custkey AND O、orderkey=L、orderkey AND
L、suppkey=PS、suppkey AND L、partkey=PS、partkey AND
PS、partkey=P、partkey AND C、name='李四';
INTERSECTION
SELECT P、*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P
WHERE C、cuskey=O、custkey AND
O、orderkey=L、orderkey AND
L、partkey=PS、partkey AND
L、suppkey=PS、suppkey AND
PS、partkey=P、partkey AND
C、name='李四';
(6)集合查询(并)
查询顾客“张三”与“李四”订购的全部零件的信息。
SELECT P、*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P
WHERE C、cuskey=O、custkey AND
O、orderkey=L、orderkey AND
L、partkey=PS、partkey AND
L、suppkey=PS、suppkey AND
PS、partkey=P、partkey AND
C、name='张三';
UNION
SELECT P、*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P
WHERE C、cuskey=O、custkey AND
O、orderkey=L、orderkey AND
L、partkey=PS、partkey AND
L、suppkey=PS、suppkey AND
PS、partkey=P、partkey AND
C、name='李四';
(7)集合查询(差)
顾客“张三”订购过而“李四”没订购过的零件的信息。
SELECT P、*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P
WHERE C、cuskey=O、custkey AND
O、orderkey=L、orderkey AND
L、partkey=PS、partkey AND
L、suppkey=PS、suppkey AND
PS、partkey=P、partkey AND
C、name='张三';
EXCEPT
SELECT P、*
FROM Customer C,Orders O,Lineitem L,PartSupp PS,Part P
《数据库系统原理》实验报告
WHERE C、cuskey=O、custkey AND
O、orderkey=L、orderkey AND
L、partkey=PS、partkey AND
L、suppkey=PS、suppkey AND
PS、partkey=P、partkey AND
C、name='李四';
4、数据更新实验
(1)INSERT基本语句(插入全部列的数据)
插入一条顾客记录,要求每列都给一个合理的值。
INSERT INTO Customer
VALUES (30,'张三','北京市',40,'',0、00,'Northeast','VIP Customer');
(2)INSERT基本语句(插入部分列的数据)
插入一条订单记录,给出必要的几个字段值。
INSERT INTO Lineitem(orderkey,Linenumber,partkey,suppkey,quantity,shipdate)
VALUES(862,ROUND(RANDOM()*100,0,479,1,10,'2012-3-6');
/*RANDOM()函数为随机小数生成函数,ROUND()为四舍五入函数*/
(3)批量数据INSERT语句
① 创建一个新的顾客表,把所有中国籍顾客插入到新的顾客表中。
CREATE TABLE NewCustmoer AS SELECT * FROM Customer WITH NO DATA;
/*WITH NO DATA子句使得SELECT查询只生成一个结果模式,不查询出实际数据*/
INSERT INTO NewCustomer /*批量插入SELECT 语句查询结果到NewCustomer表中*/
SELECT C、*
FROM Costomer C,Nation N
WHERE C、nationkey=N、nationkey AND N、name='中国';
② 创建一个顾客购物统计表,记录每个顾客及其购物总数与总价等信息。
CREATE TABLE ShoppingStat
(custkey INTEGER,
quantity REAL,
totalprice REAL);
INSERT INTO ShoppingStat
SELECT C、custkey,Sum(L、quantity),Sum(O、totalprice) /*对分组后的数据求总与*/
FROM Customer C,Order O,Lineitem L
WHERE C、custkey=O、custkey AND O、orderkey=L、orderkey
GROUP BY C、custkey
③ 倍增零件表的数据,多次重复执行,直到总记录数达到50万为止。
INSERT INTO Part
SELECT partkey+(SELECT COUNT(*) FROM Part),
name,mfgr,brand,type,size,container,retailprice,comment
FROM Part;
(4)UPDATE语句(插入部分记录的部分列值)
“金仓集团”供应的所有零件的供应成本价下降10%。
《数据库系统原理》实验报告
UPDATE PartSupp
SET supplycost=supplycost*0、9
WHERE suppkey=(SELECT suppkey /*找出要修改的那些记录*/
FROM Supplier
WHERE name='金仓集团');
(5)UPDATE语句(利用一个表中的数据修改另外一个表中的数据)
利用Part表中的零售价格来修改Lineitem中的extendedprice,其中extendedprice=Part、retailprice*quantity。
UPDATE Lineitem L
SET L、extendedprice=P、retailprice*L、quantity
FROM Part P
WHERE L、partkey=P、partkey;
/*Lineitem表也可以直接与Part表相连接,而不需通过PartSupp连接*/
(6)DELETE基本语句(删除给定条件的所有记录)
删除顾客张三的所有订单记录。
DELECT FROM Lineitem /*先删除张三的订单明细记录*/
WHERE orderkey IN(SELECT orderkey
FROM Order O,Customer C
WHERE O、custkey=C、custkey AND C、name='张三');
DELECT FROM Order /*再删除张三的订单记录*/
WHERE custkey=(SELECT custkey
FROM Customer
WHERE name='张三');
5、 视图
（1）创建视图(省略视图列名)
创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp1,要求列出供应零件的编号、零件名称、可用数量、零售价格、供应价格与备注等信息。
CREATE VIEW V_DLMU_PARTSUPP1 AS /*由SELECT子句目标列组成视图属性*/
SELECT P、partkey,P、name,PS、availqty,P、retailprice,PS、supplycost,P、comment
FROM Part P,PartSupp PS,Supplier S
WHERE P、partkey=PS、partkey AND S、suppkey=PS、suppkey AND S、name='海大汽配';
（2）创建视图(不能省略列名的情况)
创建一个视图V_CustAvgOrder,按顾客统计平均每个订单的购买金额与零件数量,要求输出 顾客编号、姓名,平均购买金额与平均购买零件数量。
CREATE VIEW V_CustAvgOrder(custkey,cname,avgprice,avgquantity) AS
SELECT C、custkey,MAX(C、name),AVG(O、totalprice),AVG(L、quantity)
FROM Customer C,Orders O,Lineitem L
WHERE C、custkey=O、custkey AND L、orderkey=O、orderkey
GROUP BY C、custkey;
（3）创建视图(WITH CHECK OPTION)
使用WITH CHECK OPTION,创建一个“海大汽配”供应商供应的零件视图
《数据库系统原理》实验报告
V_DLMU_PartSupp2,要求列出供应零件的编号、可用数量与供应价格等信息。然后通过该视图分别增加、删除与修改一条“海大汽配”零件供应记录,验证WITH
CHECK OPTION就是否起作用。
CREATE VIEW V_DLMU_PartSupp2
AS
SELECT partkey,suppkey,availqty,supplycost
FROM PartSupp
WHERE suppkey=(SELECT suppkey
FROM Supplier
WHERE name='海大汽配')
WITH CHECK OPTION;
INSERT INTO V_DLMU_PartSupp2
VALUES (58889,5048,704,77760);
UPADTE V_DLMU_PartSupp2
SET supplycost=12
WHERE suppkey=58889;
DELETE FROM V_DLMU_PartSupp2
WHERE suppkey=58889;
（4）可更新的视图(行列子集视图)
使用WITH CHECK OPTION,创建一个“海大汽配”供应商供应的零件视图V_DLMU_PartSupp4,要求列出供应零件的编号、可用数量与供应价格等信息。然后通过该视图分别增加、删除与修改一条“海大汽配”零件供应记录,验证该视图就是否就是可更新的,并比较上述“(3)创建视图”实验任务与本任务结果有何异同。
CREATE VIEW V_DLMU_PartSupp3
AS
SELECT partkey,suppkey,availqty,supplycost
FROM PartSupp
WHERE suppkey=(SELECT suppkey
FROM Supplier
WHERE name='海大汽配');
INSERT INTO V_DLUM_PartSupp3
VALUES(58889,5048,704,77760);
UPDATE V_DLMU_PartSupp3
SET supplycost=12
WHERE suppkey=58889;
DELETE FROM V_DLMU_PartSupp3
WHERE suppkey=58889;
(5)可更新的视图
INSERT INTO V_CustAvgOrder
VALUES(100000,NULL,20,2000);
（6）删除视图(RESTRICT/CASCADE)
创建顾客订购零件明细视图V_CustOrd,要求列出顾客编号、姓名、购买零件数、金额,然后在该视图的基础上,在创建(2)的视图V_CustAvgOrder,然后使用
《数据库系统原理》实验报告
RESTRICT选项与CASCADE选项删除视图V_CustOrd。
CREATE VIEW V_CustOrd(custkey,cname,qty,extprice)
AS
SELECT C、custkey,C、name,L、quantity,L、extendedprice
FROM Customer C,Order O,Lineitem L
WHERE C、custkey=O、custkey AND O、orderkey=L、orderkey;
CREATE VIEW V_CustAvgOrder(custkey,cname,avgqty,avgprice)
AS
SELECT custkey,MAX(cname),AVG(qty),AVG(extprice)
FROM V_CustOrd /*在视图V_CustOrd上再创建视图*/
GROUP BY custkey;
DROP VIEW V_CustOrd RESTRICT;
DROP VIEW V_CustOrd CASCADE;
6、 索引
（1）创建唯一索引
在零件表的零件名称字段上创建唯一索引。
CREATE UNIQUE INDEX Idx_part_name ON Part(name);
（2）创建函数索引(对某个属性的函数创建索引,称为函数索引)
在零件表的零件名称字段上创建一个零件名称长度的函数索引。
CREATE INDEX Idx_part_name_fun ON Part(LENGTH(name));
(3)创建复合索引(对两个及两个以上的属性创建索引,称为复合索引)
在零件表的制造商与品牌两个字段上创建一个复合索引。
CREATE UNIQUE INDEX Idx_part_mfgr_brand ON Part(mfgr,brand);
（4）*创建聚簇索引
在零件表的制造商字段上创建一个聚簇索引。
CREATE UNIQUE INDEX Idx_part_mfgr ON Part(mfgr);
CLUSTER Idx_part_mfgr ON Part;
（5）创建Hash索引
零件表的名称字段上创建一个Hash索引。
CREATE INDEX Idx_part_name_hash ON Part USING HASH(name);
（6）修改索引名称
修改零件表的名称字段上的索引名。
ALTER INDEX Idx_part_name_hash RENAME TO Idx_part_name_hash_new;
(7)分析某个SQL查询语句执行时就是否使用了索引
EXPLAIN SELECT * FROM part WHERE name='零件';
（8）*验证索引效率
创建一个函数TestIndex,自动计算sql查询执行的时间。
CREATE FUNCTION TestIndex(p_part_name CHAR(55)) RETURN INTEGER
AS /*自定义函数TestIndex():输入参数为零件名称,返回SQL查询的执行时间*/
DECLARE
begintime TIMESTAMP;
endtime TIMESTAMP;
durationtime INTEGER;
《数据库系统原理》实验报告
BEGN
SELECT CLOCK_TIMESTAMP() INTO begintime; /*记录查询执行的开始时间*/
PERFORM *FROM Part WHERE name=p_partname; /*执行SQL查询,不保存查询结果*/
SELECT CLOCK_TIMESTAMP() INTO endtime;
SELECT DATEDIFF(‘ms’,begintime,endtime) INTO durationtime;
RETURN durationtime; /*计算并返回查询执行时间,时间单位为毫秒ms*/
END;
/*查瞧当零件表Part数据模型比较小,并且无索引时的执行时间*/
SELECT TestIndex(‘零件名称’);
INSERT INTO Part /*不断倍增零件表的数据,直到50万条记录*/
SELECT partkey+(SELECT COUNT(*) FROM Part),
Name,mfgr,brand,type,size,container,retailprice,comment
FRPM Part;
/*查瞧当零件表Part数据模型比较大,但无索引时的执行时间*/
SELECT TestIndex(‘零件名称’);
CREATE INDEX part_name ON Part(name); /*在零件表的零件名称字段上创建索引*/
/*查瞧零件表Part数据规模比较大,有索引时的执行时间*/
SELECT TestIndex();
四、实验心得
通过本次实验,我知道只有正确理解数据库模式结构,才能正确设计数据库查询。连接查询就是数据库sql查询中最重要的查询,连接查询的设计要特别注意,不同的查询表达,其查询执行的性能会有很大差别。正确地设计与执行数据更新语句,确保正确地录入数据与更新数据,才能保证查询的数据正确。当数据更新失败时,一个主要原因就是更新数据时违反了完整性约束。
实验项目名称:安全性语言实验 实验学时: 2
同组学生姓名: 孟陈、陈晓雪、季佰军
实验地点: 1318
实验日期: 5、26 实验成绩:
批改教师: 批改时间:
一、 实验目的
1、 掌握自主存取控制缺陷的定义与维护方法。
2、 掌握数据库审计的设置与管理方法,以便监控数据库操作,维护数据库安全。
二、 实验内容与要求
《数据库系统原理》实验报告
1、 定义用户、角色,分配权限给用户、角色,回收权限,以相应的用户名登录数据库验证权限分配就是否正确。选择一个应用场景,使用自主存取控制机制设置权限分配。可以采用两种方案。
方案一:采用SYSTEM超级用户登录数据库,完成所有权限分配工作,然后用相应用户名登录数据库已验证权限分配正确性;
方案二:采用SYSTEM用户登录数据库创建3个部门经理用户,并分配相应的权限,然后分别用3个经理用户名登录数据库,创建相应部门的USER、ROLE,并分配相应权限。
2、打开数据库审计开关。以具有审计权限的用户登录数据库,设置审计权限,然后以普通用户登录数据库,执行相应的数据操纵sql语句,验证相应审计设置就是否生效,最后在一具有审计权限的用户登录数据库,查瞧就是否存在相应的审计信息。
三、实验过程
1、自主存取控制实验
(1)创建用户
1 为采购、销售与客户管理等3个部门的经理创建用户标识,要求具有创建用○户或角色的权利。
CREATE USER David WITH CREATEROLE PASSWORD '123456';
CREATE USER Tom WITH CREATEROLE PASSWORD '123456';
CREATE USER Kathy WITH CREATEROLE PASSWORD '123456';
2为采购、销售与客户管理等3个部门的职员创建用户标识与用户口令。
○CREATE USER Jeffery WITH PASSWORD '123456';
CREATE USER Jane WITH PASSWORD '123456';
CREATE USER Mike WITH PASSWORD '123456';
(2)创建角色并分配权限
1为各个部门分别创建一个查询角色,并分配相应的查询权限。
○CREATE ROLE PurchaseQueryRole;
GRANT SELECT ON TABLE Part TO PurchaseQueryRole;
GRANT SELECT ON TABLE Supplier TO PurchaseQueryRole;
GRANT SELECT ON TABLE PartSupp TO PurchaseQueryRole;
CREATE ROLE SaleQueryRole;
GRANT SELECT ON TABLE Order TO SaleQueryRole;
GRANT SELECT ON TABLE Lineitem TO SaleQueryRole;
CREATE ROLE CustomerQueryRole;
GRANT SELECT ON TABLE Customer TO CustomerQueryRole;
GRANT SELECT ON TABLE Nation TO CustomerQueryRole;
GRANT SELECT ON TABLE Region TO CustomerQueryRole;
2为各个部门分别创建一个职员角色,对本部门信息具有查瞧、插入权限。
○CREATE ROLE PurchaseEmployeeRole;
《数据库系统原理》实验报告
GRANT SELECT,INSERT ON TABLE Part TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON TABLE Supplier TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON TABLE PartSupp TO PurchaseEmployeeRole;
CREATE ROLE SaleEmployeeRole;
GRANT SELECT,INSERT ON TABLE Order TO SaleEmployeeRole;
GRANT SELECT,INSERT ON TABLE Lineitem TO SaleEmployeeRole;
CREATE ROLE CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE Customer TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE Nation TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE Region TO CustomerEmployeeRole;
3为各个部门创建一个经理角色,相应角色对本部门的信息具有完全控制权限,○对其她部门的信息具有查询权。经理有权给本部门资源分配权限。
CREATE ROLE PurchaseManagerRole WITH CREATEROLE;
GRANT ALL ON TABLE Part TO PurchaseManagerRole;
GRANT ALL ON TABLE Supplier TO PurchaseManagerRole;
GRANT ALL ON TABLE PartSupp TO PurchaseManagerRole;
GRANT SaleQueryRole TO PurchaseManagerRole;
GRANT CustomerQueryRole TO PurchaseManagerRole;
CREATE ROLE SaleManagerRole WITH CREATEROLE;
GRANT ALL ON TABLE Order TO SaleManagerRole
GRANT ALL ON TABLE Lineitem TO SaleManagerRole
GRANT SaleQueryRole TO SaleManagerRole
GRANT PurchaseQueryRole TO SaleManagerRole
CREATE ROLE CustomerManagerRole WITH CREATEROLE;
GRANT ALL ON TABLE Customer TO CustomerManagerRole
GRANT ALL ON TABLE Nation TO CustomerManagerRole
GRANT ALL ON TABLE Region TO CustomerManagerRole
GRANT SaleQueryRole TO CustomerManagerRole
GRANT PurchaseQueryRole TO CustomerManagerRole
(3)给用户分配权限
1给部门经理分配权限。
○GRANT PurchaseManagerRole TO David WITH ADMIN OPTION;
GRANT SaleManagerRole TO Tom WITH ADMIN OPTION;
GRANT CustomerManagerRole TO Kathy WITH ADMIN OPTION;
2给各部门职员分配权限
○GRANT PurchaseEmployeeRole TO Jeffery;
GRANT SaleEmployeeRole TO Jane;
GRANT CustomerEmployeeRole TO Mike;
(4)回收角色或用户权限
1收回客户经理角色的销售信息查瞧权限。
○
《数据库系统原理》实验报告
REVOKE SaleQueryRole FROM CustomerManagerRole;
2回收MIKE的客户部门职员权限。
○REVOKE CustomerEmployeeRole FROM Mike;
(5)验证权限分配正确性
1以David用户名登录数据库,验证采购部门经理的权限
○SELECT * FROM Part;
DELETE * FROM Order;
2回收MIKE的客户部门职员权限
○SELECT * FROM Customer;
SELECT * FROM Part;
2、审计实验
(1)审计开关
1显示当前审计开关状态
○SHOW AUDIT_TRAIL;
2打开审计开关
○SET AUDIT_TRAIL TO ON;
(2)数据库操作审计
1对客户信息表上的删除操作设置审计。
○AUDIT DELETE ON Sales、Customer BY ACCESS;
2以普通用户登录,执行sql语句。
○DELETE Sales、Customer WHERE custkey=1011;
3查瞧数据库对象审计信息,验证审计设置就是否生效。
○SELECT * FROM SYS_AUDIT_OBJECT;
(3)语句级审计
1对表定义的更改语句ALTER设置审计
○AUDIT ALTER TABLE BY ACCESS;
2查瞧所有数据库所有语句级审计设置,验证审计设置就是否生效
○SELECT * FROM SYS_STMT_AUDIT_OPTS;
3以普通用户登录,执行sql语句,验证审计设置就是否生效
○ALTER TABLE Customer ADD COLUMN tt INT;
4查瞧所有审计信息
○SELECT * FROM SYS_AUDIT_TRAIL;
《数据库系统原理》实验报告
四、实验心得
通过本次实验,知道了定义用户、角色,分配权限给用户、角色,回收权限,并以相应的用户名登陆数据库验证权限分配就是否正确的方法。并且知道了数据库审计的目的与方法。做实验的同时,对sql语句有了更熟练的运用。
《数据库系统原理》实验报告
实验项目名称:完整性语言实验 实验学时: 2
同组学生姓名: 孟陈、陈晓雪、季佰军
实验地点: 1318
实验日期: 6、2 实验成绩:
批改教师: 批改时间:
一、 实验目的
1、 掌握实体完整性的定义与维护方法;
2、 掌握参照完整性的定义与维护方法;
3、 掌握用户自定义完整性的定义与维护方法;
二、 实验内容与要求
1、定义实体完整性,删除实体完整性。能够写出两种方式定义实体完整性的SQL语句:创建表时定义实体完整性、创建表后定义实体完整性。设计SQL语句验证完整性约束就是否起作用。
2、定义参照完整性,定义参照完整性的违规处理,删除参照完整性。写出两种方式定义参照完整性的SQL语句:创建表时定义参照完整性、创建表后定义参照完整性。
3、针对具体应用语义,选择NULL/NOT NULL、DEFAULT、UNIQUE、CHECK等,定义属性上的约束条件。
三、实验过程
1、实体完整性实验
(1)创建表时定义实体完整性(列级实体完整性)
定义供应商表的实体完整性。
CREATE TABLE Supplier(
suppkey INSERT CONSTRAINT PK_supplier PRIMARY KEY,
name CHAR(25),
address VARCHAR(40),
nationkey INSERT,
phone CHAR(15),
acctbal REAL,
comment VARCHAR(101));
(2)创建表时定义实体完整性(表级实体完整性)
定义供应商表的实体完整性。
《数据库系统原理》实验报告
CREATE TABLE Supplier(
suppkey INSERT,
name CHAR(25),
address VARCHAR(40),
nationkey INSERT,
phone CHAR(15),
acctbal REAL,
comment VARCHAR(101),
CONSTRAINT PK_supplier PRIMARY KEY(suppkey));
(3)创建表后定义实体完整性
定义供应商表。
CREATE TABLE Supplier(
suppkey INSERT,
name CHAR(25),
address VARCHAR(40),
nationkey INSERT,
phone CHAR(15),
acctbal REAL,
comment VARCHAR(101));
ALTER TABLE Supplier /*再修改供应商表,增加实体完整性*/
ADD CONSTRAINT PK_Supplier PRIMARY KEY(suppkey);
(4)定义实体完整性(主码由多个属性组成)
定义供应关系表的实体完整性。
CREATE TABLE PartSupp(
partkey INTEGER,
suppkey INTEGER,
availqty INTEGER,
supplycost REAL,
comment VARCHAR(199),
PRIMARY KEy (partkey,suppkey));
/*主码由多个属性组成,实体完整性必须定义在表级*/
(5)有多个候选码时定义实体完整性
定义国家表的实体完整性,其中nationkey与name都就是候选码,选择nationkey作为主码,name上定义唯一性约束。
CREATE TABLE nation (
nationkey INTEGERCONSTRAINT PK_nation PRIMARY KEY,
name CHAR (25) UNIQUE,
regionkey INSERT,
comment VARCHAR(152));
(6)删除实体完整性
删除国家实体的主码。
ALTER TABLE nation DROP CONSTRAINT PK_nation;
《数据库系统原理》实验报告
(7)增加两条相同记录,验证实体完整性就是否起作用
/*插入两条主码相同的记录就会违反实体完整性约束*/
INSERT INTO Supplier(suppkey,name,address,nationkey,phone,acctbal,comment)
VALUES(11,'test1','test1',101,'12345678',0、0,'test1');
INSERT INTO Supplier(suppkey,name,address,nationkey,phone,acctbal,comment)
VALUES(11,'test2','test2',102,'23456789',0、0,'test2');
2、参照完整性实验
(1)创建表时定义参照完整性
先定义地区表的实体完整性,再定义国家表上的参照完整性。
CREATE TABLE region(
regionkey IINTEGER PRIMARY KEY,
name CHAR(25),
comment VARCHAR(152));
CREATE TABLE nation(
nationkey IINTEGER PRIMARY KEY,
name CHAR(25),
regionkey INTEGER REFERENCES Region(regionkey),/*列级参照完整性*/
comment VARCHAR(152));
或者
CREATE TABLE nation(
nationkey INTEGER PRIMARY KEY,
name CHAR(25),
regionkey INTEGER,
comment VARCHAR(152),
CONSTRAINT FK_Nation_regionkey FOREIGN KEY (regionkey)
REFERENCES Region(regionkey));/*表级参照完整性*/
(2)创建表后定义参照完整性
定义国家表的参照完整性。
CREATE TABLE nation(
nationkey INTEGER PRIMARY KEY,
name CHAR(25),
regionkey INTEGER,
comment VARCHAR(152));
ALTER TABLE Nation
FOREIGN KEY (regionkey)REFERENCES Region(regionkey);
(3)定义参照完整性(外码由多个属性组成)
定义订单项目表的参照完整性。
CREATE TABLE PartSupp(
partkey INTEGER,
suppkey INTEGER,
availqty INTEGER,
supplycost REAL,
comment VARCHAR(199),
PRIMARY KEy (partkey,suppkey));
《数据库系统原理》实验报告
CREATE TABLE Lineitem(
orderkey INTEGER REFERENCES Orders(orderkey),
partkey INTEGER REFERENCES Part(partkey),
suppkey INTEGER REFERENCES Supplier(suppkey),
linenumber INTEGER,
quantity REAL,
extendedprice REAL,
discount REAL,
tax REAL,
returnflag CHAR(1),
linestatus CHAR(1),
shipdate DATE,
commitdate DATE,
receiptdate DATE,
shipinstruct CHAR(25),
shipmode CHAR(10),
comment VARCHAR(44),
PRIMARY KEY (orderkey,linenumber),
FOREIGN KEY (partkey,suppkey) REFERENCES PartSupp(partkey,suppkey));
(4)定义参照完整性的违约处理
定义国家表的参照完整性,当删除或修改被参照表时,设置参照表中相应记录的值为空值。
CREATE TABLE nation(
nationkey INTEGER PRIMARY KEY,
name CHAR(25),
regionkey INTEGER,
comment VARCHAR(152),
CONSTRAINT FK_Nation_regionkey FOREIGN KEY (regionkey)
REFERENCES Region(regionkey) ON DELETE SET NULL ON UPDATE SET
NULL);
(5)删除参照完整性
删除国家表的外码
ALTER TABLE nation DROP CONSTRAINT FK_Nation_regionkey;
(6)插入一条国家纪录,验证参照完整性就是否起作用
/*插入一条国家纪录,如果‘1001’号地区记录不存在,违反参照完整性约束。*/
INSERT INTONation(nationkey,name,regionkey,comment)
VALUES(1001,'nation1',1001,'comment1');
3、用户自定义完整性实验
(1) 定义属性NULL/NOT NULL约束
定义地区表各属性的NULL/NOT NULL属性、
CREATE TABLE region(
regionkey INTEGER NOT NULL PRIMARY KEY,
name CHAR (25) NOT NULL,
comment VARCHAR(152) NULl);
《数据库系统原理》实验报告
(2) 定义属性DEFAULT约束
定义国家表的regionkey的缺省属性值为0值,表示其她地区。
CREATE TABLE nation(
nationkey INTEGER PRIMARY KEY,
name CHAR(25),
regionkey INTEGER DEFAULT 0,
comment VARCHAR(152),
CONSTRAINT FK_Nation_regionkey FOREIGN KEY(regionkey)
REFERENCES Region(region));
(3)定义属性UNIQUE约束
定义国家表的名称属性必须唯一的完整性约束。
CREATE TABLE nation(
nationkey INTEGER PRIMARY KEY,
name CHAR (25) UNIQUE,
regionkey INTEGER,
comment VARCHAR (152));
(4)使用CHECK
使用CHECK定义订单项目表中某些属性应该满足的约束。
CREATE TABLE Lineitem(
orderkey INTEGER REFERENCES Orders(orderkey),
partkey INTEGER REFERENCES Part(partkey),
suppkey INTEGER REFERENCES Supplier(suppkey),
linenumber INTEGER,
quantity REAL,
extendedprice REAL,
discount REAL,
tax REAL,
returnflag CHAR(1),
linestatus CHAR(1),
shipdate DATE,
commitdate DATE,
receiptdate DATE,
shipinstruct CHAR(25),
shipmode CHAR(10),
comment VARCHAR(44),
PRIMARY KEY (orderkey,linenumber),
FOREIGN KEY (partkey,suppkey) REFERENCES PartSupp(partkey,suppkey),
CHECK (shipdate < receiptdate), /*装运日期<签收日期*/
CHECK (returnflag IN ('A','R','N'))); /*退货标记为A或R或N*/
(5)修改Lineitem的一条记录验证就是否违反CHECK约束
UPDATE sales、Lineitem SET shipdate='2015-01-05',receiptdate='2015-01-01'
WHERE orderkey=5005 AND linenumber=1;
四、实验心得
《数据库系统原理》实验报告
通过本次实验,我掌握了实体完整性的定义与维护方法,动手操作了定义实体完整性,删除实体完整性,并且可以设计SQL语句验证完整性约束就是否起作用。同时,掌握了参照完整性的定义与维护方法,动手体验了定义参照完整性,并且操作了定义参照完整性的违规处理以及删除参照完整性。除此之外,还掌握了用户自定义完整性的定义与维护。此次实验使我学习了完整性相关知识,更促使我进一步学习。
实验项目名称:触发器实验 实验学时: 2
同组学生姓名: 孟陈、陈晓雪、季佰军
实验地点: 1318
实验日期: 6、9 实验成绩:
批改教师: 批改时间:
《数据库系统原理》实验报告
一、实验目的
掌握数据库触发器的设计与使用方法
二、实验内容与要求
定义BEFORE触发器与AFTER触发器。能够理解不同类型触发器的作用与执行原理,验证触发器的有效性。
三、实验过程
1、AFTER触发器
(1)在Lineitem表上定义一个UPDATE触发器,当修改订单明细(即修改订单明细价格extendedprice、折扣discount、税率tax)时,自动修改订单Orders的TotalPrice,以保持数据一致性。
total price=totalprice+extendedprice*(1-discount)*(1+tax))
CREATE OR REPLACE TRIGGER TRI_Price_UPDATE
AFTER UPDATE OF extendedprice,discount,tax ON Lineitem
FOR EACH ROW
AS
DECLARE
L_valuediff REAL
BEGIN
/*订单明细修改后,计算订单含税折扣价总价的修正值*/
L_valuediff=NEW、extendedprice*(1-NEW、discount)*(1+NEW、tax)-
OLD、extendedprice*(1-OLD、discount)*(1+OLD、tax);
/*更新订单的含税折扣总价*/
UPDATE Orders SET totalprice=totalprice+L_valuediff
WHERE orderkey=NEW、orderkey;
END’
(2)在Lineitem表上定义一个INSERT触发器,当增加一项订单明细时,自动修改订单Orders的TotalPrice,以保持数据一致性。
CREATE OR REPLACE TRIGGER TRI_Lineitem_Price_INSERT
AFTER INSERT ON Lineitem
FOR EACH ROW
AS
DECLARE
L_valuediff REAL
BEGIN
L_valuediff=NEW、extendedprice*(1-NEW、discount)*(1+NEW、tax);
/*增加订单明细项后,计算订单含税折扣价总价的修正值*/
UPDATE Orders SET totalprice=totalprice+L_valuediff
/*更新订单的含税折扣价总价*/
WHERE orderkey=NEW、orderkey;
END;
《数据库系统原理》实验报告
(3)在Lineitem表上定义一个DELETE触发器,当删除一项订单明细时,自动修改订单Orders的TotalPrice,以保持数据一致性。
CREATE OR REPLACE TRIGGER TRI_Lineitem_Price_DELETE
AFTER DELETE ON Lineitem
FOR EACH ROW
AS
DECLARE
L_valuediff REAL;
BEGIN
L_valuediff=-OLD、extendedprice*(1-OLD、discount)*(1+OLD、tax);
/*删除订单明细项后,计算订单含税折扣价总家的修正值*/
UPDATE Orders SET totalprice=totalprice+L_valuediff
/*更新订单的含税折扣价总价*/
WHERE orderkey=NEW、orderkey;
END;
(4)验证触发器TRI_Lineitem_Price_UPDATE。
/*查瞧1854号订单的含税折扣价总价totalprice*/
SELECT totalprice
FROM Orders
WHERE orderkey=1854;
/*激活触发器,修改1854号订单第一个明细项的税率,该税率增加0、5%*/
UPDATE Lineitem SET tax=tax+0、005
WHERE orderkey=1854 AND linenumber=1;
/*再次查瞧1854号订单的含税折扣总价totalprice就是否有变化,如有变化,则就是触发器起作用了,否则触发器没有起作用*/
SELECT totalprice
FROM Orders
WHERE orderkey=1854;
2、BEFORE触发器
(1)在Lineitem表上定义一个BEFORE_UPDATE触发器,当修改订单明细中的数量(quality)时,先检查供应表PartSupp中的可用数量availqty就是否足够、
CREAT ORREPLACE TRIGGER TRI_Lineitem_Quality_UPDATE
BEFORE UPDATE OF quality ON Lineitem
FOR EACH ROW
AS
DECLARE
L_valuediff INSERT;
L_availqty INSERT;
BEGIN
/*计算订单明细项修改时,订购数量的变化值*/
L_valuediff=NEW、quantity-OLD、quantity;
/*查询当前订单明细项对应零件供应记录中的可用数量*/
SELECT availqty INTO L_availqty
FROM PartSupp
《数据库系统原理》实验报告
WHERE partkey=NEW、partkey AND suppkey=NEW、suppkey;
IF(L_availqty-L_valuediff >=0) THEN
BEGIN
/*如果可用数量可以满足订单订购数量,则提示ENOUGH*/
RAISE NOTICE ‘Available quantity is ENOUGH‘;
/*修改当前订单明细项对应零件供应记录中的可用数量*/
UPDATE PartSupp
SET availqty=availqty-L_valuediff
WHERE partkey=NEW、partkey AND suppkey=NEW、suppkey;
END;
ELSE
/*如果可用数量不能满足订单订购数量,则更新过程异常中断*/
RAISE EXCEPTION ‘Available quantity is NOT ENOUGH’;
END IF;
END;
(2)在Lineitem表上定义一个BEFORE INSERT触发器,当插入订单明细,先检查供应表PartSupp中的可用数量availqty就是否足够。
CREATE OR REPLACE TRIGGER TRI_Lineitem_Quantity_UPDATE
BEFORE INSERT ON Lineitem
FOR EACH ROW
AS
DECLARE
L_valuediff INSERT;
L_aavailqty INSERT;
BEGIN
L_valuediff=NEW、quantity;/*获得插入订单明细项的订购数量*/
/*查询当前订单明细项对应零件供应记录中的可用数量*/
SELECT availqty INTO L_availqty
FROM PartSupp
WHERE partkey=NEW、partkey AND suppkey=NEW、suppkey;
IF (L_availqty-L_valuediff >=0) THEN
BEGIN
/*如果可用数量可以满足订单订购数量,则提示ENOUGH*/
RAISE NOTICE’Availqty quantity is ENOUGH’;
/*修改当前订单明细项对应零件供应记录中的可用数量*/
UPDATE PartSupp
SET availqty=availqty-L_valuediff
WHERE partkey=NEW、part AND suppkey=NEW、suppkey;
END;
ELSE
/*如果可用数量不能满足订单订购数量,则插入过程异常中断*/
RAISE EXCEPTION ‘Available quantity is NOT ENOUGH’;
END IF;
END;
《数据库系统原理》实验报告
(3)在Lineitem表上定义一个BEFORE DELETE触发器,当删除订单明细时,该订单明细项订购的数量要归还对应的零件供应记录。
CREATE OR REPLACE TRIGGER TRI_Lineitem_Quantity_UPDATE
BEFORE DELETE ON Lineitem
FOR EACH ROW
AS
DECLARE
L_valuediff INSERT;
L_availqty INSERT;
BEGIN
/*获得删除订单明细项的订购数量*/
L_valuediff=-OLD、quantity;
/*修改当前订单明细项对应零件供应记录中的可用数量*/
UPDATE PartSupp
SET availqty=availqty-L_valuediff
WHERE partkey=NEW、partkey AND suppkey=NEW、suppkey;
END;
(4)验证触发器TRI_Lineitem_Quantity_UPDATE。
/*查瞧1854号订单第1个明细项的零件与供应商编号、订购数量、可用数量*/
SELECT L、partkey,L、suppkey,L、quantity,PS、availqty
FROM Lineitem L,PartSupp PS
WHERE L、partkey=PS、partkey AND L、suppkey=PS、suppkey AND
L、orderkey=1854 AND L、linenumber=1;
/*激活触发器,修改1854号订单第1个明细项的订购数量*/
UPDATE Lineitem SET quantity=quantity+5;
WHERE orderkey=1854 AND lineitem=1;
/*再次查瞧1854号订单第1个明细项的相关信息,以验证触发器就是否起作用*、
SELECT L、partkey,L、suppkey,L、quantity,PS、availqty
FROM Lineitem L,PartSupp PS
WHERE L、partkey=PS、partkey AND L、suppkey=PS、suppkey AND
L、orderkey=1854 AND L、Linenumber=1;
3、
删除触发器
删除触发器TRI_Lineitem_Price_UPDATE。
DROP TRIGGER TRI_Lineitem_Price_UPDATE ON Lineitem;
四、实验心得
通过本次实验,进一步了解了触发器的定义,初步掌握了数据库触发器的设计与使用方法,成功动手设置了BEFORE触发器与AFTER触发器。实验过程中,由于基础知识薄弱与实践经验匮乏,遇到了不少难题,好在有老师与同学的帮助克服了困难。经过实践,更激励了我们继续努力学习,打好基础。
《数据库系统原理》实验报告
实验项目名称:数据库设计实验 实验学时: 4
同组学生姓名: 孟陈、陈晓雪、季佰军
实验地点: 1318
实验日期: 6、16 实验成绩:
批改教师: 批改时间:
一、实验目的与要求
掌握数据库设计基本方法及数据库设计工具。
二、实验内容及过程
掌握数据库设计基本步骤,包括数据库概念结构设计、逻辑结构设计,物理结构设计,
数据库魔石SQL语句生成。能够使用数据库设计工具进行数据库设计。
三、实验结果与分析
设计一个采购、销售与客户管理应用数据库。其中,一个供应商可以供应多种零件,一种零件也可以有多个供应商。一个客户订单可以订购多种供应商供应的零件。客户与供应商都分属不同的国家,而国家按世界五大洲八大洋划分地区。请利用PowerDesigner或者ERwin等数据库设计工具设计该数据库。
（1）数据库概念结构设计
① 零件Part:零件编号partkey、零件名称name、零件制造商mfgr、品牌brand、类型type、大小Size、零售价格retailprice、包装container、备注comment。主码:零件编号partkey。
create table part (
partkey int primary key,
name char(20),
mfgr char(100),
brand char(20),
type char(10),
Sizee int,
retailprice int,
comment varchar(150),
container char(20));
② 供应商Supplier:供应商编号suppkey、客户名称name、地址address、国籍nation、电话phone、备注comment等。主码:供应商编号suppkey。
create table supplier
(
suppkey int primary key,
name char(20),
《数据库系统原理》实验报告
address varchar(50),
nationkey int,
foreign key(nationkey) references nation(nationkey),
phone char(20),
comment varchar(200)
);
③ 客户Customer:客户编号custkey、客户名称name、地址address、电话phone、国籍nation、备注comment。主码:客户编号custkey。
create table customer (
orderkey int primary key,
status char(20) check ( status in ( "success" or " processing")),
totalprice int,
orderdate DATE,
comment varchar(200),
orderpriority int check ( orderpriority> 0 and orderpriority <3 ),
shippriority int check ( shippriority> 0 and shippriority <3 ),
clerk int
);
④ 订单Order:订单编号orderkey、订单状态status、订单总价totalprice、订单日期order-date、订单优先级orderpriority、记账员clerk、运送优先级视频priority、备注comment。主码:订单编号orderkey。
⑤ 订单项Lineitem:订单项编号linenumber、所订零件号partkey、所订零件供应商号suppkey、零件数量quantity、零件总价extendedprice、折扣discount、税率tax、退货标记returnflag等。主码:订单项编号linenumber。
create table lineitem(
linenumber int primary key,
partkey int,
foreign key(partkey) references part(partkey),
suppkey int,
foreign key(suppkey) references supplier(suppkey),
quantity int,
extendedprice long,
discount long,
tax float,
returnflag char(1) check ( returnflag in ('N' or 'Y'))
);
⑥ 国家Nation:国家编号nationkey、国家名称name、所属地区region、备注comment。主码:国家编号nationkey。
create table nation(
nationkey int primary key,
name char(15),
regionkey int,
foreign key(regionkey) references region(regionkey),
comments varchar(200)
《数据库系统原理》实验报告
);
⑦ 地区Region:地区编号regionkey、地区名称name、备注comment。主码:地区编号regionkey。
create table region
(
regionkey int primary key,
name char(20),
comments varchar(200)
);
根据实际语义,分析实体之间的联系,确定实体之间一对一,一对多与多对多联系。
实体-联系图(E-R图)如下:
OrderPartmPurchase1Customer11mBelong to1NationmBelong tompPartSuppn1IncludeLineitem1mBelong to1RegionSupplierm
（2）数据库逻辑结构设计
按照数据库设计原理中概念结构转化成逻辑结构的规则,每个实体转换成一个关系,多
对多的联系也转换成一个关系。因此,根据上述E-R图设计数据库逻辑结构(参见实
验1、1数据库定义中的图2TPC-H数据库模式图)。
(3)数据库物理结构设计
数据库物理结构首先根据逻辑结构自动转换生成,然后根据应用需求设计数据库的索引
结构、存储结构。
(4)数据库模式SQL语句参见实验1、1数据库定义。
四、实验总结
通过本次实验我基本掌握了数据库设计的基本步骤,包括数据库概念结构设计、逻辑结构设计、物理结构设计与数据库模式sql语句生成。并且学会了使用数据库设计的工具。
《数据库系统原理》实验报告
实验项目名称:数据库设计实验 实验学时: 4
同组学生姓名: 孟陈、陈晓雪、季佰军
实验地点: 1318
实验日期: 6、23 实验成绩:
批改教师: 批改时间:
一、实验目的与要求
1．掌握数据库PL/SQL编程语言,以及数据库存储过程的设计与使用方法;
2．掌握数据库PL/SQL编程语言以及数据库自定义函数的设计与使用方法;
3．掌握PL/SQL游标的设计、定义与使用方法,理解PL/SQL游标按行操作与SQL按结
果集操作的区别与联系。
二、实验内容及过程
1．存储过程定义,存储过程运行,存储过程更名,存储过程删除,存储过程的参数
传递。掌握数据库PL/SQL编程语言与编程规范,规范设计存储过程;
2．自定义函数定义,自定义函数运行,自定义函数更名,自定义函数删除,自定义函
数的参数传递。掌握PL/SQL与编程规范,规范设计自定义函数;
3．游标定义、游标使用。掌握各种类型游标的特点、区别与联系。
三、实验结果与分析
6、1 存储过程实验
(1)无参数的存储过程
① 定义一个存储过程,更新所有订单的(含税折扣价)总价
CREATE OR REPLACE PROCEDURE Proc_CalTotalPrice()
AS
BEGIN
UPDATE Orders SET totalprice =
(SELECT SUM(extendedprice * (1-discount)*(1+tax))
FROM Lineitem
WHERE Orders、orderkey=Lineitem、orderkey);
END;
② 执行存储过程Proc_CalTotalPrice()
CALL Proc_CalTotalPrice();
（2） 有参数的存储过程
① 定义一个存储过程,更新给定订单的(含税折扣价)总价。
CREATE OR REPLACE PROCEDURE Proc_CalTotalPrice4Order(okey
《数据库系统原理》实验报告
INTEGER)
AS
BEGIN
UPDATE Orders SET totalprice =
(SELECT SUM(extendedprice * (1-discount)*(1+tax))
FROM Lineitem
WHERE Orders、orderkey=Lineitem、orderkey AND
Lineitem、orderkey=okey);
END;
② 执行存储过程。
CALL Proc_CalTotalPrice4Order(5365);
(3)有局部变量的存储过程
① 定义一个存储过程,更新某个顾客的所有订单的(含税折扣价)总价。
CREATE OR REPLACE PROCEDURE
Proc_CalTotalPrice4Customer(p_custname
CHAR(25))
AS
DECLARE
L_custkey INTEGER;
BEGIN
SELECT custkey INTO L_custkey
FROM Customer
WHERE name=TRIM(p_custname);
UPDATE Orders SET totalprice=
(SELECT SUM(extendedprice * (1-discount)*(1+tax))
FROM Lineitem
WHERE Orders、orderkey=Lineitem、orderkey AND
Orders、custkey=L_custkey);
END;
② 执行存储过程Proc_CalTotalPrice4 Customer()。
CALL Proc_CalTotalPrice4Customer(‘陆皆宇’);
③ 查瞧存储过程执行结果
SELECT*FROM Orders
WHERE custkey=(SELECT custkey FROM Customer WHERE name=’陈凯峰‘);
(4)有输出参数的存储过程
① 定义一个存储过程,更新某个顾客的所有订单的(含税折扣价)总价。
CREATE OR REPLACE PROCEDURE
Proc_CalTotalPrice4Customer2(p_custname
CHAR(25),OUT p_totalprice REAL)
AS
DECLARE
L_custkey INTEGER;
BEGIN
《数据库系统原理》实验报告
SELECT custkey INTO L_custkey
FROM Customer
WHERE name=TRIM(p_custname);
RAISE NOTICE’custkey is %’,L_custkey;
UPDATE Orders SET totalprice =
(SELECT SUM(extendedprice * (1-discount)*(1+tax))
FROM Lineitem
WHERE Orders、orderkey=
Lineitem、orderkey AND Orders、custkey=L_custkey);
SELECT SUM(totalprice)INTO p_totalprice
FROM Orders WHERE custkey =L_custkey;
END;
② 执行存储过程Proc_CalTotalPrice4 Customer2()
CALL Proc_CalTotalPrice4Customer2(‘陈凯峰’,null);
③ 查瞧存储过程执行结果
SELECT SUM(totalprice)
FROM Orders
WHERE custkey=(SELECT custkey
FROM Customer
WHERE name=’陆皆宇‘);
(5)修改存储过程
① 修改存储过程名Proc_CalTotalPrice4Order为CalTotalPrice4Order
ALTER PROCEDURE Proc_CalTotalPrice4Order RENAME TO
CalTotalPrice4Order;
② 编译存储过程CalTotalPrice4Order
ALTER PROCEDURE CalTotalPrice4Order (okey INTEGER)COMPILE;
(6)删除存储过程
删除存储过程CalTotalPrice4Order。
DROP PROCEDURE CalTotalPrice4Order;
6、2 自定义函数实验
(1) 无参数的自定义函数
① 定义一个自定义函数,更新所有订单的(含税折扣价)总价,并返回所有订单的总
价之与。
CREATE OR REPLACE FUNCTION FUN_CalTotalPrice()
RETURN REAL
AS
DECLARE
res REAL;
BEGIN
UPDATE Orders SET totalprice =
(SELECT SUM(extendedprice * (1-discount)*(1+tax))
FROM Lineitem
《数据库系统原理》实验报告
WHERE Orders、orderkey=Lineitem、orderkey);
SELECT SUM(toyalprice) INTO res
FROM Orders;
RETURN res;
END;
② 执行自定义函数FUN_CalTotalPrice()。
SELECT TotalPrice();
(2)有参数的自定义函数
① 定义一个自定义函数,更新并返回给定订单的总价
CREATE OR REPLACE FUNCTION FUN_CalTotalPrice4Order(p_okey
INTEGER)
RETURN REAL
AS
DECLARE
res REAL;
BEGIN UPDATE Orders SET totalprice =
(SELECT SUM(extendedprice * (1-discount)*(1+tax))
FROM Lineitem
WHERE Orders、orderkey=Lineitem、orderkey AND Lineitem、orderkey=p_okey);
SELECT SUM(toyalprice) INTO res
FROM Orders;
RETURN res;
END;
② 执行自定义函数FUN_CalTotalPrice4Order()
CALL Fun_CalTotalPrice4Order(5365);
(3)有局部变量的自定义函数
① 定义一个自定义函数,计算并返回某个顾客的所有订单的总价
CREATE OR REPLACE FUNCTION FUN_CalTotalPrice4Customer(p_custname
CHAR(25))
RETURN REAL
AS
DECLARE
L_custkey INTEGER;
res REAL;
BEGIN
SELECT cuskey INTO L_custkey
FROM Customer
WHERE name = trim(p_custname);
RAISE NOTICE'cueskey is %',L_custkey;
UPDATE Orders SET totalprice =
(SELECT SUM(extendedprice * (1-discount)*(1+tax))
FROM Lineitem
WHERE Orders、orderkey=Lineitem、orderkey AND
《数据库系统原理》实验报告
Orders、custkey=L_custkey);
SELECT SUM(toyalprice) INTO res
FROM Orders;
WHERE custkey=L_custkey;
RETURN res;
END;
② 执行自定义函数FUN_CalTotalPrice4Customer()
SELECT FUN_CalTotalPrice4Customer('陆皆宇');
(4)有输出参数的自定义函数
① 定义一个自定义函数,计算并返回某个顾客的所有订单的总价
CREATE OR REPLACE FUNCTION
FUN_CalTotalPrice4Customer2(p_custname
CHAR(25),OUT p_totalprice REAL)
RETURN REAL
AS
DECLARE
L_custkey INTEGER;
res REAL;
BEGIN
SELECT cuskey INTO L_custkey
FROM Customer
WHERE name = trim(p_custname);
RAISE NOTICE'cueskey is %',L_custkey;
UPDATE Orders SET totalprice =
(SELECT SUM(extendedprice * (1-discount)*(1+tax))
FROM Lineitem
WHERE Orders、orderkey=Lineitem、orderkey AND
Orders、custkey=L_custkey);
SELECT SUM(toyalprice) INTO p_totalprice
FROM Orders WHERE custkey=L_custkey;
Res := p_totalprice;
RETURN res;
END;
② 执行自定义函数FUN_CalTotalPrice4Customer2()。
SELECT FUN_CalTotalPrice4Customer2('陆皆宇',null);
(5)修改自定义函数
① 修改自定义函数名FUN_CalTotalPrice4Order 为CalTotalPrice4Order
ALTER FUNCTIONFUN_CalTotalPrice4Order RENAME TO
CalTotalPrice4Order ;
② 编译自定义函数CalTotalPrice4Order
ALTER FUNCTIONFUN_CalTotalPrice4Order(okey INTEGER) COMPILE;
(6)删除自定义函数
删除自定义函数CalTotalPrice4Order。
《数据库系统原理》实验报告
DROP FUNCTION CalTotalPrice4Order ;
6、3游标实验
(1)普通游标
① 定义一个存储过程,用游标实现计算所有订单的总价。
CREATE OR REPLACE PROCEDURE ProcCursor_CalTotalPrice()
AS
L_orderkey INTEGER;
L_totalprice REAL;
CURSOR mycursor FOR
SELECT orderkey,totalprice
FROM Orders;
BEGIN
OPEN mycursor;
LOOP
FETCH mycursor INTO L_orderkey,L_totalprice;
IF mycursor%NOTFOUND THEN
EXIT;
END IF;
SELECT SUM(extendedprice * (1-discount)*(1+tax))INTO
L_totalprice
FROM Lineitem
WHERE orderkey=L_orderkey ;
UPDATE Orders SET totalprice=L_totalprice
WHERE orderkey=L_orderkey;
END LOOP;
CLOSE mycuesor;
END;
② 执行存储过程ProcCursor_CalTotalPrice()
CALL ProcCursor_CalTotalPrice()
(2)REFCURSOR类型游标
① 定义一个存储过程,用游标实验计算所有订单的总价。
CREATE OR REPLACE PROCEDURE ProcRefCursor_CalTotalPrice()
AS
DECLARE
L_orderkey INTEGER;
L_totalprice REAL;
mycuesor REFCURSOR;
BEGIN
OPEN mycursor FOR SELECT orderkey,totalprice FROM Order;
LOOP
FETCH mycursor INTO L_orderkey,L_totalprice;
IF mycursor%NOTFOUND THEN
EXIT;
END IF;
《数据库系统原理》实验报告
SELECT SUM(extendedprice * (1-discount)*(1+tax))INTO
L_totalprice
FROM Lineitem
WHERE orderkey=L_orderkey ;
UPDATE Orders SET totalprice=L_totalprice
WHERE orderkey=L_orderkey;
END LOOP;
CLOSE mycursor;
END;
② 执行存储过程ProcRefCursor_CalTotalPrice()
CALL ProcRefCursor_CalTotalPrice();
(3)记录变量与游标
① 定义一个存储过程,用游标实现计算所有订单的总价。
CREATE OR REPLACE PROCEDURE ProcRefCursor_CalTotalPrice()
AS
DECLARE
L_totalprice REAL;
res RECORD;
CURSOR mycuesor FOR
SELECT orderkey,totalprice
FROM Orders;
BEGIN
OPEN mycursor
LOOP
FETCH mycursor INTO res;
IF mycursor%NOTFOUND THEN EXIT;
END IF;
SELECT SUM(extendedprice * (1-discount)*(1+tax))INTO
L_totalprice
FROM Lineitem
WHERE orderkey=res、orderkey ;
UPDATE Orders SET totalprice=L_totalprice
WHERE orderkey=res、orderkey;
END LOOP;
CLOSE mycursor;
END;
② 执行存储过程ProcRefCursor_CalTotalPrice()
CALL ProcRefCursor_CalTotalPrice();
(4)带参数的游标
① 定义一个存储过程,用游标实现计算指定国家的用户订单的总价。
CREATE OR REPLACE PROCEDURE
ProcRefCursor_CalTotalPrice(p_nationname
CHAR(20))
AS
《数据库系统原理》实验报告
DECLARE
L_totalprice REAL;
res RECORD;
CURSOR mycuesor (c_nationname CHAR(20))FOR
SELECT O、orderkey,O、totalprice
FROM Orders O,Customer O,Nation N
WHERE O、custkey=C、custkey AND c、nationkey=N、nationkey
AND
TRIM(N、name)=TRIM(c_nationname);
BEGIN
OPEN mycursor (p_nationname);
LOOP
FETCH mycursor INTO res;
IF mycursor%NOTFOUND THEN
EXIT;
END IF;
SELECT SUM(extendedprice * (1-discount)*(1+tax))INTO
L_totalprice
FROM Lineitem
WHERE orderkey=res、orderkey ;
UPDATE Orders SET totalprice=L_totalprice
WHERE orderkey=res、orderkey;
END LOOP;
CLOSE mycursor;
END;
② 执行存储过程ProcParaCursor_CalTotalPrice()
CALL ProcParaCursor_CalTotalPrice('中国');
五、实验总结
1、REFCURSOR类型的游标定义一个游标应用变量,只就是再打开该类型游标时才指定具体的SELECT语句以便产生游标的结果集。因此,REFCURSOR实质上就是定义了一个动态游标,可以灵活方便地根据程序运行时情况的变动设置游标的SELECT查询结果集。
2、从任务(1)可以瞧出,游标可以实现对数据库记录逐条处理,而不就是整个结果集一起处理,因此,游标就是在PL/SQL语言中实现过程化处理的核心功能。
3、从任务(3)瞧出,记录对于游标结果记录的处理很方便,通过记录变量可以直接访问记录的每个属性,而无需为记录的每个属性定义相应的变量。
版权声明：本文标题：《数据库系统原理》实验报告 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/free/1703242505h443740.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论