SQLServer数据库大作业1.docx
- 文档编号:29935536
- 上传时间:2023-08-03
- 格式:DOCX
- 页数:20
- 大小:52.37KB
SQLServer数据库大作业1.docx
《SQLServer数据库大作业1.docx》由会员分享,可在线阅读,更多相关《SQLServer数据库大作业1.docx(20页珍藏版)》请在冰豆网上搜索。
SQLServer数据库大作业1
华东交通大学
《数据库认证》课程大作业
——Northwind数据库综合应用设计方案
专业班级:
09软件工程(+电子商务)2班学院:
软件学院
学生姓名:
毕文超学号:
12
分数:
学期:
2011-2012
(2)
任课教师:
刘常昱
Northwind数据库综合应用设计方案
一、Northwind数据库的分析
1)对Northwind数据库的各对象(包括数据库表、存储过程、视图、触发器等)进行分析,描述各自的大致内容和实现的功能。
表名:
Categories(食品类别表)
表结构:
字段名称
数据类型
长度
允许为空
CategoryID(主键)
int
4
否
CategoryName
nvarchar
15
否
Description
ntext
是
Picture
image
是
主键:
CategoryID
主键约束名称:
PK_Categories
关系说明:
该表主要用来保存食品种类信息的数据。
表名:
CustomerCustomerDemo(消费者表)
表结构:
字段名称
数据类型
长度
允许为空
CustomerID(主键)
nchar
5
否
CustomerTypeID(主键)
nchar
10
否
组合主键:
CustomerID、CustomerTypeID
主键约束名称:
PK_CustomerCustomerDemo
外键:
1.列CustomerTypeID引用表CustomerDemographics(CustomerTypeID)
2.列CustomerID引用表Customers(CustomerID)
外键约束名称:
FK_CustomerCustomerDemo和FK_CustomerCustomerDemo_Customers
关系说明:
该表主要用来保存消费者ID和消费者类别ID信息的数据。
表名:
CustomerDemographics(消费者类别说明表)
表结构:
字段名称
数据类型
长度
允许为空
CustomerTypeID(主键)
nchar
10
否
CustomerDesc
ntext
16
是
主键:
CustomerTypeID
主键约束名称:
PK_CustomerDemographics
关系说明:
该表主要用来存储消费者类别说明信息。
表名:
Customers(消费者信息表)
表结构:
字段名称
数据类型
长度
允许为空
CustomerID(主键)
nchar
5
否
CompanyName
nvarchar
40
否
ContactName
nvarchar
30
是
ContactTitle
nvarchar
30
是
Address
nvarchar
60
是
City
nvarchar
15
是
Region
nvarchar
15
是
PostalCode
nvarchar
10
是
Country
nvarchar
15
是
Phone
nvarchar
24
是
Fax
nvarchar
24
是
主键:
CustomerID
主键约束名称:
PK_Customers
关系说明:
该表主要用来存储消费者详细信息的数据表。
表名:
Employees(雇员信息表)
表结构:
字段名称
数据类型
长度
允许为空
EmployeeID(主键)
int
4
否
LastName
nvarchar
20
否
FirstName
nvarchar
10
否
Title
nvarchar
30
是
TitleOfCourtesy
nvarchar
25
是
BirthDate
datetime
8
是
HireDate
datetime
8
是
Address
nvarchar
60
是
City
nvarchar
15
是
Region
nvarchar
15
是
PostalCode
nvarchar
10
是
Country
nvarchar
15
是
HomePhone
nvarchar
24
是
Extension
nvarchar
4
是
Photo
image
16
是
Notes
ntext
16
是
ReportsTo
int
4
是
PhotoPath
nvarchar
255
是
主键:
EmployeeID
主键约束名称:
PK_Employees
外键:
1.列ReportsTo引用表Employees(EmployeeID)
外键约束名称
FK_Employees_Employees
关系说明:
该表主要用来存储雇员的详细信息数据表。
表名:
EmployeeTerritories(雇员销售区域表)
表结构:
字段名称
数据类型
长度
允许为空
EmployeeID(主键)
int
4
否
TerritoryID(主键)
nvarchar
20
否
主键:
EmployeeID、TerritoryID
主键约束名称:
PK_EmployeeTerritories
外键:
1.列EmployeeID引用表Employees(EmployeeID)
2.列TerritoryID引用表Territories(TerritoryID)
外键约束名称:
FK_EmployeeTerritories_Employees和FK_EmployeeTerritories_Territories
关系说明:
该表主要用来存储雇员ID和其对应的销售区域ID。
表名:
OrderDetails(订单价格表)
表结构:
字段名称
数据类型
长度
允许为空
OrderID(主键)
int
4
否
ProductID(主键)
int
4
否
UnitPrice
money
8
否
Quantity
samllint
2
否
Discount
real
4
否
主键:
OrderID、ProductID
主键约束名称:
PK_Order_Details
外键:
1.列OrderID引用表Orders(OrderID)
2.列ProductID引用表Products(ProductID)
外键约束名称:
FK_Order_Details_Orders和FK_Order_Details_Products
关系说明:
该表主要用来存储订单价格信息的数据表
1 组合、主键、聚集索引在OrderID和ProductID上定义。
2 在OrderID上也有两个非聚集索引。
3 在ProductID上也有两个非聚集索引。
4 UnitPrice的CHECK约束被定义为(UnitPrice>=0)。
5 Quantity的CHECK约束被定义为(Quantity>0)。
表级别的CHECK约束被定义为(Discount>=0和Discount<=1)。
表名:
Orders(订单表)
表结构:
字段名称
数据类型
长度
允许为空
OrderID(主键)
int
4
否
CustomerID
nchar
5
是
EmployeeID
int
4
是
OrderDate
datetime
8
是
RequiredDate
datetime
8
是
ShippedDate
datetime
8
是
ShipVia
int
4
是
Freight
money
8
是
ShipName
nvarchar
40
是
ShipAddress
nvarchar
60
是
ShipCity
nvarchar
15
是
ShipRegion
nvarchar
15
是
ShipPostalCode
nvarchar
10
是
ShipCountry
nvarchar
15
是
主键:
OrderID
主键约束名称:
PK_Orders
外键:
1.列CustomerID引用表Customers(CustomerID)
2.列EmployeeID引用表Employees(EmployeeID)
3..列ShipVia引用表Shippers(ShipperID)
外键约束名称
FK_Orders_Customers,FK_Orders_Employees和FK_Orders_Shippers
关系说明:
该表主要用来存储订单详细信息的数据
1 在CustomerID上有两个非聚集索引。
2 在EmployeeID上有两个非聚集索引。
3 在ShipVia上也有一个非聚集索引。
表名:
Products(产品信息表)
表结构:
字段名称
数据类型
长度
允许为空
ProductID(主键)
int
4
否
ProductName
nvarchar
40
否
SupplierID
int
4
是
CategoryID
int
4
是
QuantityPerUnit
nvarchar
20
是
UnitPrice
money
8
是
UnitsInStock
samllint
2
是
UnitsOnOrder
samllint
2
是
ReorderLevel
samllint
2
是
Discontinued
bit
1
否
主键:
ProductID
主键约束名称:
PK_Products
外键:
1.列CategoryID引用表Categories(CategoryID)
2.列SupplierID引用表Suppliers(SupplierID)
外键约束名称
FK_Products_Categories和FK_Products_Suppliers
关系说明:
该表主要用来存储产品详细信息的数据表。
1 在SupplierID上有两个非聚集索引。
2 在CategoryID上有两个非聚集索引。
3 UnitPrice的CHECK约束被定义为(UnitPrice>=)。
4 UnitsInStock的CHECK约束被定义为(UnitsInStock>=)
5 UnitsOnOrder的CHECK约束被定义为(UnitsOnOrder>=)。
6 ReorderLevel的CHECK约束被定义为(ReorderLevel>=)。
表名:
Region(区域表)
表结构:
字段名称
数据类型
长度
允许为空
RegionID(主键)
int
4
否
RegionDescription
nchar
50
否
主键:
RegionID
主键约束名称:
PK_Region
关系说明:
该表主要用来存储区域信息数据。
表名:
Shippers(托运信息表)
表结构:
字段名称
数据类型
长度
允许为空
ShipperID(主键)
int
4
否
CompanyName
nvarchar
40
否
Phone
nvarchar
24
是
主键:
ShipperID
主键约束名称:
PK_Shippers
关系说明:
该表主要用来存储托运公司的详细信息数据。
表名:
Suppliers(供应商信息表)
表结构:
字段名称
数据类型
长度
允许为空
SupplierID(主键)
int
4
否
CompanyName
nvarchar
40
否
ContactName
nvarchar
30
是
ContactTitle
nvarchar
30
是
Address
nvarchar
60
是
City
nvarchar
15
是
Region
nvarchar
15
是
PostalCode
nvarchar
10
是
Country
nvarchar
15
是
Phone
nvarchar
24
是
Fax
nvarchar
24
是
HomePage
ntext
16
是
主键:
SupplierID
主键约束名称:
PK_Suppliers
关系说明:
该表主要用来存储供应商详细信息数据。
表名:
Territories(销售区域信息表)
表结构:
字段名称
数据类型
长度
允许为空
TerritoryID(主键)
nvarchar
20
否
TerritoryDescription
nchar
50
否
RegionID
int
4
否
主键:
TerritoryID
主键约束名称:
PK_Territories
外键:
1.列RegionID引用表Region(RegionID)
外键约束名称
FK_Territories_Region
关系说明:
该表用来存储销售区域详细说明信息的数据。
存储过程分析:
1.CustOrderHist
参数:
消费者编号
作用:
用来显示指定消费者购买各种商品的总数。
2.CustOrdersDetail
参数:
订单编号
作用:
用来显示指定订单编号的销售信息。
3.CustOrdersOrders
参数:
消费者编号
作用:
用来显示指定消费者所有订单销售期限情况。
4.EmployeeSalesbyCountry
参数:
起始时间、结束时间
作用:
用来显示在起始时间和结束时间之间的需要发货订单的负责人信息和订单的详细信息。
5.SalesbyYear
参数:
起始时间、结束时间
作用:
用来查询在起始时间和结束时间之间发货的订单信息及所属年份(包括售价)。
6.SalesByCategory
参数:
食品类别、年份
返回值:
返回指定食品名称、年份的销售食品名称和其售价。
7.TenMostExpensiveProducts
作用:
显示Products表中食品单价排在前10位的食品名称和食品单价。
视图分析:
1.Alphabeticallistofproducts未过期食品的详细信息及其所属的类别。
2.CategorySalesfor1997显示种类食品在1997年的实际销售部。
3.CurrentProductList显示未过期食品的信息。
4.CustomerandSuppliersbyCity所有消费者和供货商的信息。
5.Invoices按照订单发货的详细信息。
6.OrderDetailsExtended订单明细的详细信息。
7.OrderSubtotals 每单的实际销售额
8.OrdersQry 有订单的客户的详细订单信息及客户信息
9.ProductSalesfor1997每种食品在1997实际销售额以及食品的详细信息
10.ProductsAboveAveragePrice单位售价大于所有食品平均售价的食品清单。
11.ProductsbyCategory 未过期食品的存货情况(按照商品分类)
12.QuarterlyOrders 1997年有订单的客户信息
13.SalesbyCategory 1997年订单食品的详细情况
14.SalesTotalsbyAmount 1997年每单销售额大于2500的订单的信息
15.SummaryofSalesbyQuarter1997年已发货订单的发货时间
16.SummaryofSalesbyYear 1997年已发货订单的发货时间
2)给出Northwind库的整体ER关系图,给出Employees和Customers表的数据库字典。
图一
图二
Employees:
员工表
相应字段:
EmployeeID:
员工代号;
LastName+FirstName:
员工姓名;
Title:
头衔;
TitleOfCourtesy:
尊称;
BirthDate:
出生日期;
HireDate:
雇用日期;
Address:
家庭地址;
City:
所在城市;
Region:
所在地区;
PostalCode:
邮编;
Country:
国家用;
HomePhone:
宅电;
Extension:
分机;
Photo:
手机;
notes:
照片;
ReportsTo:
上级;
PhotoPath:
照片
Customers:
客户表
相应字段:
CustomerID:
客户ID;
CompanyName:
所在公司名称;
ContactName:
客户姓名;
ContactTitle:
客户头衔;
Address:
联系地址;
City:
所在城市;
Region:
所在地区;
PostalCode:
邮编;
Country:
国家
Phone:
电话;
Fax:
传真
二、Northwind库的综合应用
注:
对于一个数据库而言,不仅仅是提供一个数据的存储位置,更多的是能够提供对所存储数据的处理功能,满足业务需要。
下面是一些在项目开发过程中经常使用的数据库设计功能点,请给出设计的思路及具体代码。
1)在应用程序中,对于数据库中提供的数据记录要进行显示,当记录比较多的时候,要进行分页显示。
请设计一个存储过程,能够分页提供所需要的产品信息,存储过程名称为getProductbyPage,存储过程的参数为每页显示的数据及记录条数pagesize和要获取的是哪一个分页的数据库pageindex,存储过程能够得到所需要的该分页中的记录集合。
CREATEprocegetProductbyPage
(@pagesizechar(10)
@pageindexchar(100)
)
as
setnocounton
begin
declare@indextabletable(idintidentity(1,1),nidint)--定义表变量
declare@PageLowerBoundint--定义此页的底码
declare@PageUpperBoundint--定义此页的顶码
set@PageLowerBound=(@pageindex-1)*@pagesize
set@PageUpperBound=@PageLowerBound+@pagesize
setrowcount@PageUpperBound
insertinto@indextable(nid)selectgidfromTGongwen
wherefariqi>dateadd(day,-365,getdate())orderbyfariqidesc
selectO.gid,O.mid,O.title,O.fadanwei,O.fariqifromTGongwenO,@indextablet
whereO.gid=t.nidandt.id>@PageLowerBound
andt.id<=@PageUpperBoundorderbyt.id
end
setnocountoff
以上存储过程运用了SQLSERVER的最新技术――表变量。
应该说这个存储过程也是一个非常优秀的分页存储过程。
当然,在这个过程中,您也可以把其中的表变量写成临时表:
CREATETABLE#Temp。
但很明显,在SQLSERVER中,用临时表是没有用表变量快的。
2)数据库的安全是非常重要的一个工作,如果要对Northwind库进行安全性的设计,请给出你的方案。
(提示:
报告对存储过程视图等对象的加密、登录用户账户的管理、数据库的用户操作日志的管理等多个方面进行分析,给出具体的方法)
随着数据安全需求的不断增加,不论以何种方式,都不要忽略对你的数据库备份文件的安全保证。
在本地的SQLServer备份中,备份文件中的数据是以普通文本格式存储的,仅仅用文本编辑器就可以轻松阅读。
根据表中使用的数据类型,一些数据比另外的一些数据更容易查看。
试试这个你几乎从来没有进行过的试验。
对Northwind数据库进行备份,或者任何其它小型数据库,然后用任意的文本编辑器打开备份文件。
你将会看到数据自身有一点难以理解,但是只要你看到存储过程的注释,然后通读一下文件,你就会看到你的备份文件的真正价值所在。
如果你采取行动,将用户ID和/或密码存储在你的存储过程中,首先这可不是一个好主意,这个数据现在就可以被任何能够接触到备份数据库的人所访问。
如果你有其他藏有秘密信息的文本数据类型,你也会让这些数据非常有意义了。
备份密码
SQLServer中的一个选项就是创建用密码创建备份。
这是你在创建备份的时候可以使用的另一个选择,但是在企业版管理器或者SQLServer管理套件中,并没有提供这个选项。
这里是一个使用密码选项备份的例子:
backupdatabasenorthwindtodisk=’c:
northwind.bak’withmediapassword=’Backup2006’
这个过程需要密码来重新存储文件,但是使用文本编辑器,这些数据仍然是可以访问的。
还有,重新存储不能使用GUI来完成,所以它必须通过T-SQL重新存储命令和密码一起完成任务。
加密存储过程
一种防止你的存储过程被用于查看的方法就是在创建你的存储过程的时候使用“带加密”的选项。
这样的话,备份文件中的数据也是经过加密的了。
要使用加密来创建一个存储过程,如下所示:
createproceduredbo.testEncryption
withencryption
as
select*FROMproducts
加密数据
另一个选择就是在你把数据存储到你的数据库表中的时候,对数据进行加密。
在SQLServer2000中没有本地的方法来完成,但是有很多工具你可以使用:
针对SQLServer的NetLibEncryptionizer
使用XP_CRYPT加密SQLServer
SQLServer2005中存在本地加密功能。
看看微软的文章<>如何:
加密一列数据,那里解释了这个过程。
在你加密了数据库中的数据之后,当你创建备份的时候,数据仍然是经过加密的。
保证文件系统的安全
保卫你的备份文件的安全的另一个方法就是在你的服务器或者网络中使用安全目录。
你可以限制访问这个目录的权限,这样就只有一小部分受限制的人能够访问你的备份文件。
通过在安全目录上使用上述的技术,你就可以创建另一
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer 数据库 作业