《数据库认证》大作业软件测试结果.docx
《《数据库认证》大作业软件测试结果.docx》由会员分享，可在线阅读，更多相关《《数据库认证》大作业软件测试结果.docx（28页珍藏版）》请在冰豆网上搜索。
《数据库认证》大作业软件测试结果
华东交通大学
《数据库认证》课程大作业
——Northwind数据库综合应用设计方案
专业班级：
学院：
学生姓名：
学号：
分数：
学期：
2011-2012
（2）
任课教师：
刘常昱
Northwind数据库综合应用设计方案
一、Northwind数据库的分析
1）对Northwind数据库的各对象（包括数据库表、存储过程、视图、触发器等）进行分析，描述各自的大致内容和实现的功能。
1、创建数据库
createdatabasestudent--创建数据库：
student
onprimary
（
name=student,
filename='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\student.mdf',
size=10,
maxsize=unlimited,
filegrowth=5）
logon
（
name=student_log,
filename='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\student_log.LDF
size=5,
maxsize=100,
filegrowth=10%）
2、建表
createtableStudentInformation--学生信息表
（cStuIdchar（8）notnullconstraintpkStuIdprimarykey,--学生学号，设置为主键，使用了主关键字约束
cStuNamechar（8）notnull,--学生姓名
cSexchar
（2）check（cSex='男'orcSex='女'）,--性别，性别只能取男或者女，使用了检查约束
cStuNativechar（20）,--籍贯
sStuBirthdaysmalldatetime,--出生日期
cDepartmentIdchar（6）notnullreferencesDepartment（cDepartmentId）,--院系编号,设置为外键，使用了外关键字约束
cSpecialityIdchar（8）notnullreferencesSpeciality（cSpecialityId）,--专业编号,设置为外键，使用了外关键字约束
cClassNumchar（4）,--班号
sEnterTimesmalldatetime,--入学时间
vStuAdrressvarchar（40）,--学生家庭地址
vStuTelvarchar（20）--学生电话号码
）
createtableCourse--课程信息表
（cCourseIdchar（10）notnullconstraintpkCourseIdprimarykey,--课程编号，设置为主键，使用了主关键字约束
cSpecialityIdchar（8）notnullreferencesSpeciality（cSpecialityId）,--专业编号,设置为外键，使用了外关键字约束
cCourseNamechar（20）notnullunique,--课程名称，使用unique进行唯一性约束
cCourseTypeIdchar（5）notnullreferencesCourseType（cCourseTypeId）,--课程类型编号,设置为外键，使用了外关键字约束
iLectureint,--授课学时
tSemestertinyint,--开课学期
fCreditfloat,--课程学分
）
…….
3、视图
（1）创建视图
--建立学院代码为‘610000’教师的视图，包括教师的教师编号、姓名、性别和出生年月.使用withcheckoption可选项，是对视图进行更新操作（增加、删除、修改）时，要保证更新操作的行满足视图定义中的条件表达式
createviewTeacherInformation_view
as
selectcTeacherId,cTeacherName,sTeacherBirthday
fromTeacherInformationwherecDepartmentId='610000'
withcheckoption
select*fromTeacherInformation_view
--建立课程1（上课编号）的学生花名册的视图，该花名册包括学生的学号、姓名、专业名称和这门课程的成绩
createviewCourse1_StudentInformation_view
as
selectStudent_Course.cStuId,cStuName,cSpecialityName,fStuScorefrom
StudentInformation,Speciality,Student_Course
whereStudent_Course.sTeacherCourseId=1andStudent_Course.cStuId=StudentInformation.cStuId
andStudentInformation.cSpecialityId=Speciality.cSpecialityId
withcheckoption
select*fromCourse1_StudentInformation_view
（2）修改视图
--修改视图TeacherInformation_view，使其包括教师的职称这一列
alterviewTeacherInformation_view
as
selectcTeacherId,cTeacherName,cSex,sTeacherBirthday,cProfessionName
fromTeacherInformation,ProfessionwherecDepartmentId='610000'
andTeacherInformation.tProfessionId=Profession.tProfessionId
select*fromTeacherInformation_view
（3）删除视图
--删除视图TeacherInformation_view
dropviewTeacherInformation_view
4、存储过程
（1）创建和执行存储过程
--创建一个无参的存储过程，完成的功能是在表StudentInformation、Course、Student_Course、Teacher_Course中查询以下字段：
班级、学号、姓名、性别、课程名称、分数
createprocedureStuScoreInfo
as
begin
selectStudentInformation.cClassNumas班级,StudentInformation.cStuIdas学号,cStuNameas姓名,cSexas性别,cCourseNameas课程名称,fStuScoreas分数
fromStudentInformation,Course,Student_Course,Teacher_Course
whereStudentInformation.cStuId=Student_Course.cStuIdandStudent_Course.sTeacherCourseId=Teacher_Course.sTeacherCourseId
andTeacher_Course.cCourseId=Course.cCourseId
End
…..
（2）删除存储过程
--删除存储过程StuScoreInfo
dropprocedureStuScoreInfo
--删除存储过程Stu_Age
dropprocedureStu_Age
--删除存储过程list_student_department
dropprocedurelist_student_department
5、触发器
（1）--创建一个after触发器，要求实现以下功能：
在StudentInformation表上创建一个删除类型的触发器Stu_Delete,当在StudentInformation表中删除某一条记录后，触发该触发器，在Student_Course表中删除与此学号对应的记录
createtriggerStu_Delete
onStudentInformation
fordelete
as
begin
declare@StuIdchar（8）
select@StuId=cStuIdfromdeleted
deletefromStudent_CoursewherecStuId=@StuId
end
--创建了Stu_Delete触发器之后，输入以下语句
deletefromStudentInformationwherecStuId='02080001'
deletefromStudentInformationwherecStuId='02080002'
--删除Stu_Delete触发器
droptriggerStu_Delete
（2）--创建一个insteadof触发器，要求实现以下功能，在Student_Course表上创建一个删除类型的触发器NotAllowDelete,当在Student_Course表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息
createtriggerNotAllowDelete
onStudent_Course
insteadofdelete
as
begin
print'本表中的数据不允许被删除！
不能执行删除操作！
'
end
--创建了NotAllowDelete触发器之后，执行以下语句
deletefromStudent_CoursewherecStuId='02080002'
--删除NotAllowDelete触发器
droptriggerNotAllowDelete
（3）--创建一个after触发器，要求实现以下功能：
在Student_Course表上创建一个插入、更新类型的触发器ScoreCheck,当在fStuScore字段中插入或修改考试分数后，触发该触发器，检查分数是否在0-100之间
createtriggerScoreCheck
onStudent_Course
forinsert,update
as
ifupdate（fStuScore）
begin
declare@ScoreValuereal
select@ScoreValue=（selectfStuScorefrominserted）
if@ScoreValue>100or@ScoreValue<0
print'输入的分数有误，请确认输入的考试分数！
'
end
--创建了ScoreCheck触发器之后，输入以下语句
insertintoStudent_Course
values（'02080002','5','-50'）
insertintoStudent_Course
values（'02080003','7','105'）
--删除ScoreCheck触发器
droptriggerScoreCheck
6、事务
--事务的使用。
删除学号为02080001的学生的信息
declare@tran_namevarchar（20）
select@tran_name='my_tran_delete'
begintran@tran_name
deletefromStudentInformationwherecStuId='02080001'
deletefromStudent_CoursewherecStuId='02080001'
committran@tran_name
--事务的回滚,删除学号为02080002的学生的信息，让事务回滚到保存点savepoint
declare@tran_namevarchar（20）
select@tran_name='mytran'
begintran@tran_name
deletefromStudentInformationwherecStuId='02080002'
savetransave_point
deletefromStudent_CoursewherecStuId='02080002'
if@@error=0
begin
rollbacktransave_point
committran@tran_name
end
else
committran@tran_name
2）给出Northwind库的整体ER关系图，给出Employees和Customers表的数据库字典。
图1、图2为 sql2000示例数据库Northwind的ER图，使用Powerdesigner反向工程方法获得
图
（1）
图
（2）
具体字段的含义如下：
① Categories:
种类表
相应字段:
CategoryID:
类型ID；
CategoryName:
类型名;
Description:
类型说明;
Picture:
产品样本
② CustomerCustomerDemo:
客户类型表1
相应字段：
CustomerID：
客户ID；
CustomerTypeID：
客户类型ID
③ CustomerDemographics:
客户类型表2
相应字段：
CustomerTypeID：
客户类型ID；
CustomerDesc：
客户描述
④ Customers:
客户表
相应字段：
CustomerID:
客户ID；
CompanyName:
所在公司名称；
ContactName:
客户姓名；
ContactTitle:
客户头衔；
Address：
联系地址；
City：
所在城市;
Region:
所在地区;
PostalCode:
邮编;
Country:
国家
Phone:
电话；
Fax：
传真
⑤ Employees：
员工表
相应字段：
EmployeeID:
员工代号;
LastName+FirstName:
员工姓名;
Title:
头衔;
TitleOfCourtesy:
尊称;
BirthDate:
出生日期;
HireDate:
雇用日期;
Address：
家庭地址；
City:
所在城市;
Region:
所在地区;
PostalCode:
邮编;
Country:
国家用；
HomePhone：
宅电;
Extension:
分机;
Photo:
手机;
notes:
照片；
ReportsTo：
上级;
PhotoPath:
照片
⑥ EmployeeTerritories：
员工部门表
相应字段：
EmployeeID:
员工编号；
TerritoryID：
部门代号
⑦ OrderDetails：
订单明细表
相应字段：
OrderID:
订单编号；
ProductID：
产品编号；
UnitPrice：
单价;
Quantity:
订购数量；
Discount:
折扣
⑧ Orders:
订单表
相应字段：
OrderID:
订单编号；
CustomerID:
客户编号;
EmployeeID:
员工编号；
OrderDate：
订购日期；
RequiredDate：
预计到达日期;
ShippedDate:
发货日期;
ShipVia:
运货商;
Freight:
运费;
ShipName:
货主姓名；
ShipAddress:
货主地址
ShipCity:
货主所在城市；
ShipRegion：
货主所在地区；
ShipPostalCode：
货主邮编;
ShipCountry:
货主所在国家
⑨ Products:
产品表
相应字段：
ProductID:
产品ID；
ProductName：
产品名称；
SupplierID：
供应商ID；
CategoryID：
类型ID；
QuantityPerUnit：
数量；
UnitPrice：
单价;
UnitsInStock：
库存数量；
UnitsOnOrder：
订购量；
ReorderLevel：
再次订购量;
Discontinued：
中止
⑩ Region:
地区表
相应字段：
RegionID：
地区ID；
RegionDescription：
地区描述
⑪ Shippers:
运货商
相应字段：
ShipperID:
运货商ID；
CompanyName：
公司名称;
Phone:
联系电话
⑫ Suppliers：
供应商表
相应字段：
ShipperID：
供应商ID；
CompanyName：
供应商姓名；
Phone；联系电话
⑬ Territories：
地域表
相应字段：
TerritoryID:
地域编号；
TerritoryDescription：
地域描述；
RegionID：
地区编号
数据库名：
Northwind
Suppliers
序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
字段说明
1
SupplierID
int
4
0
√
√
2
CompanyName
nvarchar
80
0
√
3
ContactName
nvarchar
60
0
√
4
ContactTitle
nvarchar
60
0
√
5
Address
nvarchar
120
0
√
6
City
nvarchar
30
0
√
7
Region
nvarchar
30
0
√
8
PostalCode
nvarchar
20
0
√
9
Country
nvarchar
30
0
√
10
Phone
nvarchar
48
0
√
11
Fax
nvarchar
48
0
√
12
HomePage
ntext
16
0
√
Region
序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
字段说明
1
RegionID
int
4
0
√
2
RegionDescription
nchar
100
0
Products
序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
字段说明
1
ProductID
int
4
0
√
√
2
ProductName
nvarchar
80
0
3
SupplierID
int
4
0
√
4
CategoryID
int
4
0
√
√
5
QuantityPerUnit
nvarchar
40
0
√
6
UnitPrice
money
8
4
√
（0）
7
UnitsInStock
smallint
2
0
√
（0）
8
UnitsOnOrder
smallint
2
0
√
（0）
9
ReorderLevel
smallint
2
0
√
（0）
10
Discontinued
bit
1
0
（0）
Orders
序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
字段说明
1
OrderID
int
4
0
√
√
2
CustomerID
nchar
10
0
√
√
3
EmployeeID
int
4
0
√
4
OrderDate
datetime
8
3
√
5
RequiredDate
datetime
8
3
√
6
ShippedDate
datetime
8
3
√
7
ShipVia
int
4
0
√
8
Freight
money
8
4
√
（0）
9
ShipName
nvarchar
80
0
√
10
ShipAddress
nvarchar
120
0
√
11
ShipCity
nvarchar
30
0
√
12
ShipRegion
nvarchar
30
0
√
13
ShipPostalCode
nvarchar
20
0
√
14
ShipCountry
nvarchar
30
0
√
Employees
序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
字段说明
1
EmployeeID
int
4
0
√
√
2
LastName
nvarchar
40
0
√
3
FirstName
nvarchar
20
0
4
Title
nvarchar
60
0
√
5
TitleOfCourtesy
nvarchar
50
0
√
6
BirthDate
datetime
8
3
√
7
HireDate
datetime
8
3
√
8
Address
nvarchar
120
0
√
9
City
nvarchar
30
0
√
10
Region
nvarchar
30
0
√
11
PostalCode
nvarchar
20
0
√
升级会员 