数据库原理实践报告
学生学号：
学生姓名：
学生所在班级：
指导教师：
2013至 2014 年第一学期
实践任务
一、编程练习
假设有职工工资表：
R(职工号，，基本工资，附加工资，房租，水电，应发工资，税款，实发工资)
缴纳个人所得税的规定如下：
个人收入>5000，税率20%
个人收入>2000，税率10%
个人收入>800，税率5%
个人收入<800，免税
工资的计算方法：
（1）应发工资＝基本工资＋附加工资
（2）采取分段计算税款的算法，超出5000部分收20%，2000～5000之间部分收10%，以此类推。
（3）实发工资＝应发工资－税款
试编写一个简单SQL脚本程序，创建工资表并完成计算实发工资的任务。
二、案例设计
自主选择一种系统，完成需求分析、概念设计、逻辑结构设计、规化（3NF）及数据库的创建。
1．需求分析（业务规则）
生产管理系统的业务规则如下：
（1）一件产品可以由多个零件组成，一个零件可以组装多件不同的产品。
（2）一件产品可以使用多种材料，一种材料可以用于多件不同的产品。
（3）一个零件可以消耗多种材料，一种材料可以用于多个不同的零件。
（4）一个仓库可以存放多种材料，一种材料可以存放在多个仓库中。
2．概念结构设计（E-R图）
（1）实体集：工程、零件、供应商。
（2）联系集：产品实体与零件实体之间是多对多联系“组装”，产品实体与材料实体之间是多对多联系“使用”，零件实体与材料实体之间是多对多联系“消耗”，仓库实体与材料实体之间是一对多联系“存储”。
（3）局部E-R图
（4）全局E-R 图
产品与材料之间的使用联系可由产品与零件之间的组装联系以及零件与材料之间的消耗联系推导出来，属于冗余联系，消除后得到全局E-R 图。
3．逻辑结构设计（关系模式） （1）实体集转换为关系模式 产品（产品号，产品名，价格） 零件（零件号，零件名，价格） 材料（材料号，材料名，价格）
仓库（仓库号，仓库名，地址）
（2）联系集转换为关系模式
组装（产品号，零件号，零件数）
消耗（零件号，材料号，消耗量）
存储（材料号，仓库号，存储量）
4．规化设计（3NF）
上述7个关系中均不存在非主属性对候选码的部分函数依赖和传递函数依赖，因此均已达到3NF要求，不需要进一步分解。
5．创建数据库（SQL脚本）
CREATE DATABASE 生产管理
GO
USE 生产管理
CREATE TABLE 产品
( 产品号 INT,
产品名 CHAR(10),
价格 INT,
PRIMARY KEY(产品号)
)
CREATE TABLE 零件
( 零件号 INT,
零件名 CHAR(10),
价格 INT,
PRIMARY KEY(零件号)
)
CREATE TABLE 材料
( 材料号 INT,
材料名 CHAR(10),
价格 INT,
PRIMARY KEY(材料号)
)
CREATE TABLE 仓库
( 仓库号 INT,
仓库名 CHAR(10),
地址 CHAR(10),
PRIMARY KEY(仓库号)
)
CREATE TABLE 组装
( 产品号 INT,
零件号 INT,
零件数 INT,
PRIMARY KEY (产品号,零件号),
FOREIGN KEY (产品号) REFERENCES 产品(产品号),
FOREIGN KEY (零件号) REFERENCES 零件(零件号)
)
CREATE TABLE 消耗
( 零件号 INT,
材料号 INT,
消耗量 INT,
PRIMARY KEY (零件号,材料号),
FOREIGN KEY (零件号) REFERENCES 零件(零件号),
FOREIGN KEY (材料号) REFERENCES 材料(材料号)
)
CREATE TABLE 存储
( 材料号 INT,
仓库号 INT,
存储量 INT,
PRIMARY KEY (材料号,仓库号),
FOREIGN KEY (材料号) REFERENCES 材料(材料号),
FOREIGN KEY (仓库号) REFERENCES 仓库(仓库号)
)
GO
6．SQL编程
说明：根据所选系统，设计功能，分别用存储过程、触发器和游标完成。
（1）存储过程
功能：根据指定仓库号和材料号查询指定仓库中指定材料的数量。
USE 生产管理
GO
CREATE PROCEDURE STORE sid INT,mid INT
AS
IF EXISTS(SELECT 仓库号 FROM 仓库 WHERE 仓库号=sid) AND EXISTS(SELECT 材料号FROM 材料 WHERE 材料号=mid)
BEGIN
DECLARE s INT
SELECT s=存储量 FROM 存储 WHERE 仓库号=sid AND 材料号=mid
RETURN s
END
ELSE
RETURN -1
GO
测试脚本：
USE 生产管理
GO
INSERT INTO 产品 VALUES(1,'自行车',300)
INSERT INTO 零件 VALUES(1,'链条',20)
INSERT INTO 材料 VALUES(1,'钢',3000)
INSERT INTO 仓库 VALUES(1,'原料库','一分厂')
INSERT INTO 组装 VALUES(1,1,1)
INSERT INTO 消耗 VALUES(1,1,1)
INSERT INTO 存储 VALUES(1,1,1)
GO
DECLARE r INT
EXECUTE r=STORE 1,1
PRINT r
EXECUTE r=STORE 1,2
PRINT r
EXECUTE r=STORE 2,1
PRINT r
GO
（2）触发器和游标（也可以分开设计功能）
USE 生产管理
GO
CREATE TRIGGER S ON 存储 FOR UPDATE
AS
IF 0>ANY(SELECT 存储量 FROM inserted)
BEGIN
DECLARE SC CURSOR FOR
SELECT 仓库号,材料号 FROM inserted WHERE 存储量<0
DECLARE sid INT,mid INT,s INT
OPEN SC
FETCH NEXT FROM SC INTO sid,mid
WHILE FETCH_STATUS=0
BEGIN
SELECT s=存储量 FROM deleted WHERE 仓库号=sid AND 材料号=mid UPDATE 存储 SET 存储量=s WHERE 仓库号=sid AND 材料号=mid
PRINT STR(sid,3)+'号仓库'+STR(mid,3)+'号材料库存不足!'
FETCH NEXT FROM SC INTO sid,mid
END
CLOSE SC
DEALLOCATE SC
END
GO
测试脚本：
USE 生产管理
GO
INSERT INTO 材料 VALUES(2,'铜',5000)
INSERT INTO 材料 VALUES(3,'煤',1000)
INSERT INTO 存储 VALUES(2,1,1)
INSERT INTO 存储 VALUES(3,1,1)
GO
UPDATE 存储 SET 存储量=存储量-10
GO
SELECT * FROM 存储
GO
实践总结。