SQLServer数据库大作业1.pdf

《SQLServer数据库大作业1.pdf》由会员分享，可在线阅读，更多相关《SQLServer数据库大作业1.pdf（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库认证大作业 2011-2012（2）华东交通大学数据库认证课程大作业NorthwindNorthwind 数据库综合应用设计方案数据库综合应用设计方案专业班级：09 软件工程（+电子商务）2 班学院：软件学院学生姓名：毕文超学号：12分数：学期：2011-2012（2）任课教师：刘常昱NorthwindNorthwind 数据库综合应用设计方案数据库综合应用设计方案一、一、NorthwindNorthwind 数据库的分析数据库的分析1）对Northwind 数据库的各对象（包括数据库表、存储过程、视图、触发器 等）进行分析，描述各自的大致内容和实现的功能。表名：表名：Categorie

2、sCategories(食品类别表)表结构：字段名称字段名称CategoryID(主键)CategoryNameDescriptionPicture主键：CategoryID主键约束名称：PK_Categories关系说明：该表主要用来保存食品种类信息的数据。表名：表名：CustomerCustomerDemoCustomerCustomerDemo(消费者表)表结构：字段名称字段名称数据类型数据类型1 1/1818数据类型数据类型intnvarcharntextimage长度长度415允许为空允许为空否否是是长度长度允许为空允许为空数据库认证大作业 2011-2012（2）CustomerI

3、D(主键)CustomerTypeID(主键)ncharnchar510否否组合主键：CustomerID、CustomerTypeID主键约束名称：PK_CustomerCustomerDemo外键：1.列 CustomerTypeID 引用表 CustomerDemographics(CustomerTypeID)2.列 CustomerID 引用表 Customers(CustomerID)外键约束名称：FK_CustomerCustomerDemo 和 FK_CustomerCustomerDemo_Customers关系说明：该表主要用来保存消费者 ID 和消费者类别 ID 信息的数

4、据。表名：表名：CustomerDemographicsCustomerDemographics(消费者类别说明表)表结构：字段名称字段名称CustomerTypeID(主键)CustomerDesc主键：CustomerTypeID主键约束名称：PK_CustomerDemographics关系说明：该表主要用来存储消费者类别说明信息。表名：表名：CustomersCustomers(消费者信息表)表结构：2 2/1818数据类型数据类型ncharntext长度长度1016允许为空允许为空否是数据库认证大作业 2011-2012（2）字段名称字段名称CustomerID(主键)Company

5、NameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFax主键：CustomerID数据类型数据类型ncharnvarcharnvarcharnvarcharnvarcharnvarcharnvarcharnvarcharnvarcharnvarcharnvarchar长度长度540303060151510152424允许为空允许为空否否是是是是是是是是是主键约束名称：PK_Customers关系说明：该表主要用来存储消费者详细信息的数据表。表名：表名：EmployeesEmployees(雇员信息表)表结构：字段

6、名称字段名称EmployeeID(主键)LastNameFirstNameTitleTitleOfCourtesyBirthDateHireDateAddressCityRegionPostalCodeCountryHomePhoneExtensionPhoto数据类型数据类型intnvarcharnvarcharnvarcharnvarchardatetimedatetimenvarcharnvarcharnvarcharnvarcharnvarcharnvarcharnvarcharimage3 3/1818长度长度42010302588601515101524416允许为空允许为空否否否

7、是是是是是是是是是是是是数据库认证大作业 2011-2012（2）NotesReportsToPhotoPath主键：EmployeeIDntextintnvarchar164255是是是主键约束名称：PK_Employees外键：1.列 ReportsTo 引用表 Employees(EmployeeID)外键约束名称FK_Employees_Employees关系说明：该表主要用来存储雇员的详细信息数据表。表名：表名：EmployeeTerritoriesEmployeeTerritories(雇员销售区域表)表结构：字段名称字段名称EmployeeID(主键)TerritoryID(主键

8、)数据类型数据类型intnvarchar长度长度420允许为空允许为空否否主键：EmployeeID、TerritoryID主键约束名称：PK_EmployeeTerritories外键：1.列 EmployeeID 引用表 Employees(EmployeeID)2.列 TerritoryID 引用表 Territories(TerritoryID)外键约束名称：FK_EmployeeTerritories_Employees和FK_EmployeeTerritories_Territories关系说明：该表主要用来存储雇员 ID 和其对应的销售区域 ID。4 4/1818数据库认证大作业

9、 2011-2012（2）表名：表名：Order DetailsOrder Details(订单价格表)表结构：字段名称字段名称OrderID(主键)ProductID(主键)UnitPriceQuantityDiscount数据类型数据类型intintmoneysamllintreal长度长度44824允许为空允许为空否否否否否主键：OrderID、ProductID主键约束名称：PK_Order_Details外键：1.列 OrderID 引用表 Orders(OrderID)2.列 ProductID 引用表 Products(ProductID)外键约束名称：FK_Order_Deta

10、ils_Orders 和 FK_Order_Details_Products关系说明：该表主要用来存储订单价格信息的数据表1组合、主键、聚集索引在 OrderIDOrderID 和 ProductIDProductID 上定义。2在 OrderIDOrderID 上也有两个非聚集索引。3在 ProductIDProductID 上也有两个非聚集索引。4UnitPriceUnitPrice 的 CHECK 约束被定义为(UnitPriceUnitPrice=0)。5QuantityQuantity 的 CHECK 约束被定义为(QuantityQuantity 0)。表级别的 CHECK 约束被

11、定义为(DiscountDiscount=0 和 DiscountDiscount=)。4 UnitsInStockUnitsInStock 的 CHECK 约束被定义为(UnitsInStockUnitsInStock=)5 UnitsOnOrderUnitsOnOrder 的 CHECK 约束被定义为(UnitsOnOrderUnitsOnOrder=)。6 ReorderLevelReorderLevel 的 CHECK 约束被定义为(ReorderLevelReorderLevel=)。表名：表名：RegionRegion(区域表)表结构：7 7/1818数据库认证大作业 2011-2

12、012（2）字段名称字段名称RegionID(主键)RegionDescription主键：RegionID数据类型数据类型intnchar长度长度450允许为空允许为空否否主键约束名称：PK_Region关系说明：该表主要用来存储区域信息数据。表名：表名：ShippersShippers(托运信息表)表结构：字段名称字段名称ShipperID(主键)CompanyNamePhone主键：ShipperID主键约束名称：PK_Shippers关系说明：该表主要用来存储托运公司的详细信息数据。表名：表名：SuppliersSuppliers(供应商信息表)表结构：字段名称字段名称Supplier

13、ID(主键)CompanyNameContactNameContactTitleAddressCityRegion数据类型数据类型intnvarcharnvarchar长度长度44024允许为空允许为空否否是数据类型数据类型intnvarcharnvarcharnvarcharnvarcharnvarcharnvarchar8 8/1818长度长度4403030601515允许为空允许为空否否是是是是是数据库认证大作业 2011-2012（2）PostalCodeCountryPhoneFaxHomePage主键：SupplierIDnvarcharnvarcharnvarcharnvarch

14、arntext1015242416是是是是是主键约束名称：PK_Suppliers关系说明：该表主要用来存储供应商详细信息数据。表名：表名：TerritoriesTerritories(销售区域信息表)表结构：字段名称字段名称TerritoryID(主键)TerritoryDescriptionRegionID主键：TerritoryID主键约束名称：PK_Territories外键：1.列 RegionID 引用表 Region(RegionID)外键约束名称FK_Territories_Region关系说明：该表用来存储销售区域详细说明信息的数据。存储过程分析：存储过程分析：1CustOr

15、derHist参数：消费者编号9 9/1818数据类型数据类型nvarcharncharint长度长度20504允许为空允许为空否否否数据库认证大作业 2011-2012（2）作用：用来显示指定消费者购买各种商品的总数。2CustOrdersDetail参数：订单编号作用：用来显示指定订单编号的销售信息。3CustOrdersOrders参数：消费者编号作用：用来显示指定消费者所有订单销售期限情况。4Employee Sales by Country参数：起始时间、结束时间作用：用来显示在起始时间和结束时间之间的需要发货订单的负责人信息和订单的详细信息。5Sales by Year参数：起始时

16、间、结束时间作用：用来查询在起始时间和结束时间之间发货的订单信息及所属年份(包括售价)。6.SalesByCategory参数：食品类别、年份返回值：返回指定食品名称、年份的销售食品名称和其售价。7Ten Most Expensive Products作用：显示 Products 表中食品单价排在前 10 位的食品名称和食品单价。视图分析：视图分析：1Alphabetical list of products 未过期食品的详细信息及其所属的类别。2Category Sales for 1997 显示种类食品在 1997 年的实际销售部。3Current Product List 显示未过期食品

17、的信息。4Customer and Suppliers by City 所有消费者和供货商的信息。1010/1818数据库认证大作业 2011-2012（2）5Invoices 按照订单发货的详细信息。6Order Details Extended 订单明细的详细信息。7Order Subtotals每单的实际销售额8Orders Qry有订单的客户的详细订单信息及客户信息9Product Sales for 1997 每种食品在 1997 实际销售额以及食品的详细信息10Products Above Average Price 单位售价大于所有食品平均售价的食品清单。11Products b

18、y Category未过期食品的存货情况（按照商品分类）12Quarterly Orders 1997 年有订单的客户信息13Sales by Category 1997 年订单食品的详细情况14Sales Totals by Amount 1997 年每单销售额大于 2500 的订单的信息15Summary of Sales by Quarter1997 年已发货订单的发货时间16Summary of Sales by Year 1997 年已发货订单的发货时间1111/1818数据库认证大作业 2011-2012（2）2）给出 Northwind 库的整体 ER 关系图，给出 Employ

19、ees 和 Customers 表的数据库字典。图一1212/1818数据库认证大作业 2011-2012（2）图二Employees：员工表相应字段：EmployeeID:员工代号;LastName+FirstName:员工姓名;Title:头衔;TitleOfCourtesy:尊称;BirthDate:出生日期;HireDate:雇用日期;Address：家庭地址；City:所在城市;Region:所在地区;PostalCode:邮编;Country:国家用；HomePhone：宅电;Extension:分机;Photo:手机;notes:照片；ReportsTo：上级;PhotoPath

20、:照片Customers:客户表相应字段：CustomerID:客户 ID；CompanyName:所在公司名称；ContactName:客户姓名；ContactTitle:客户头衔；Address：联系地址；City：所在城市;Region:所在地区;PostalCode:邮编;1313/1818数据库认证大作业 2011-2012（2）Country:国家Phone:电话；Fax：传真二、二、NorthwindNorthwind 库的综合应用库的综合应用注：对于一个数据库而言，不仅仅是提供一个数据的存储位置，更多的是能够提供对所存储数据的处理功能，满足业务需要。下面是一些在项目开发过程中经

21、常使用的数据库设计功能点，请给出设计的思路及具体代码。1）在应用程序中，对于数据库中提供的数据记录要进行显示，当记录比较多的时候，要进行分页显示。请设计一个存储过程，能够分页提供所需要的产品信息，存储过程名称为 getProductbyPage，存储过程的参数为每页显示的数据及记录条数pagesize 和要获取的是哪一个分页的数据库pageindex，存储过程能够得到所需要的该分页中的记录集合。CREATE proce getProductbyPage(pagesize char(10)pageindex char(100)assetnocountonbegindeclareindextabl

22、etable(idintidentity(1,1),nidint)-定义表变量declarePageLowerBoundint-定义此页的底码declarePageUpperBoundint-定义此页的顶码setPageLowerBound=(pageindex-1)*pagesizesetPageUpperBound=PageLowerBound+pagesizesetrowcountPageUpperBoundinsertintoindextable(nid)selectgidfromTGongwen1414/1818数据库认证大作业 2011-2012（2）wherefariqidate

23、add(day,-365,getdate()orderbyfariqidescselectO.gid,O.mid,O.title,O.fadanwei,O.fariqifromTGongwenO,indextabletwhereO.gid=t.nidandt.idPageLowerBoundandt.id=PageUpperBoundorderbyt.idendsetnocountoff以上存储过程运用了 SQLSERVER的最新技术表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然，在这个过程中，您也可以把其中的 表变量写成临时表：CREATETABLE#Temp。但很明显，在

24、SQLSERVER中，用临时表是没有用表变量快的。2）数据库的安全是非常重要的一个工作，如果要对Northwind 库进行安全性的设计，请给出你的方案。（提示：报告对存储过程视图等对象的加密、登录用户账户的管理、数据库的用户操作日志的管理等多个方面进行分析，给出具体的方法）随着数据安全需求的不断增加，不论以何种方式，都不要忽略对你的数据库备份文件的安全保证。在本地的 SQL Server 备份中，备份文件中的数据是以普通文本格式存储的，仅仅用文本编辑器就可以轻松阅读。根据表中使用的数据类型，一些数据比另外的一些数据更容易查看。试试这个你几乎从来没有进行过的试验。对 Northwind 数据库进

25、行备份，或者任何其它小型数据库，然后用任意的文本编辑器打开备份文件。你将会看到数据自身有一点难以理解，但是只要你看到存储过程的注释，然后通读一下文件，你就会看到你的备份文件的真正价值所在。如果你采取行动，将用户ID 和/或密码存储在你的存储过程中，首先这可不是一个好主意，这个数据现在就可以被任何能够接触到备份数据库的人所访问。如果你有其他藏有秘密信息的文本数据类型，你也会让这些数据非常有意义了。备份密码SQL Server 中的一个选项就是创建用密码创建备份。这是你在创建备份的时候可以使用的另一个选择，但是在企业版管理器或者 SQL Server 管理套件中，并没有提供这个选项。这里是一个使用

26、密码选项备份的例子:backup database northwind to disk=c:northwind.bak with mediapassword=Backup20061515/1818数据库认证大作业 2011-2012（2）这个过程需要密码来重新存储文件，但是使用文本编辑器，这些数据仍然是可以访问的。还有，重新存储不能使用 GUI 来完成，所以它必须通过 T-SQL 重新存储命令和密码一起完成任务。加密存储过程一种防止你的存储过程被用于查看的方法就是在创建你的存储过程的时候使用“带加密”的选项。这样的话，备份文件中的数据也是经过加密的了。要使用加密来创建一个存储过程，如下所示:c

27、reateproceduredbo.testEncryptionwithencryptionasselect*FROMproducts加密数据另一个选择就是在你把数据存储到你的数据库表中的时候，对数据进行加密。在 SQL Server 2000 中没有本地的方法来完成，但是有很多工具你可以使用:针对 SQL Server 的 NetLib Encryptionizer使用 XP_CRYPT 加密 SQL ServerSQL Server 2005 中存在本地加密功能。看看微软的文章如何:加密一列数据，那里解释了这个过程。在你加密了数据库中的数据之后，当你创建备份的时候，数据仍然是经过加密的。保

28、证文件系统的安全保卫你的备份文件的安全的另一个方法就是在你的服务器或者网络中使用安全目录。你可以限制访问这个目录的权限，这样就只有一小部分受限制的人能够访问你的备份文件。通过在安全目录上使用上述的技术，你就可以创建另一个级别的安全措施了。这仍然不会消除加密的需求，但是它提供了额外的安全措施。直接备份到磁带备份的另一个选择就是直接备份到磁带上，以便在你的网络中保证备份文件的安全。这种方法减少了对你的备份文件的不正当访问的问题。用这种方式有一个大问题:我写入的大部分关于备份的内容都是首先写入磁盘的，以便在必要的时候能够快速重新存储，然后再为了长期的存储而归档到磁带上。这种方式消除了你的备份落入坏人

29、之手的机会，但是，不幸的是，它让其他的处理过程变得困难。加密备份如果你真的需要保证你的备份文件的安全，最好的方式就是在你创建备份文件的时候对其进行加密。不幸的是，SQL Server 中没有工具可以让你完成这个任务，但是看看以下的产品，它们可以让你创建各种级别的密码和加密技术加密备份。1616/1818数据库认证大作业 2011-2012（2）Idera 的 SQLsafeQuest 的 SQL LiteSpeedRed-Gate 的 SQL Backup3)数据库使用中的自定义提示非常重要，它可以更加直观的把一些操作不当的数据库行为提示给用户。利用 RAISEERROR 语句完成如下的功能：

30、首先自定义一个错误号为50512 的用户自定义错误，错误的严重级别为 10,错误的文本消息为 Can t find the customer ID.，另外消息中还要加上表名和输入的CustomerID,并且当发生消息时将消息写入 Microsoft Windows NT 应用程序日志中。然后创建一个名为 ExistCustomerID 的存储过程，以用它来找出 SQL SERVER 中的 northwind 数据库的Customer 表中，指定的 CustomerID 是否存在，如果存在返回 0，如果不存在返回错误号50512，并将消息写入 Microsoft Windows NT 应用程序日

31、志中。写出定义错误消息和创建存储过程的语句。定义自定义错误消息EXEC sp_addmessagemsgnum=50512,severity=10,msgtext=Cant find the customer ID:%s at table%s.,with_log=true创建存储过程的语句如下USE NorthWindGOCREATE PROC ExistCustomerIDCustomerID char(5)ASSELECTCustomerIDFrom Customers WHERE CustomerID=CustomerIDIF ROWCOUNT=0BEGINRAISEERROR(5051

32、2,10,1,CustomerID,DBNAME)RETURNENDRETURN01717/1818数据库认证大作业 2011-2012（2）总结及分析总结及分析三、三、NorthwindNorthwind 库在设计过程中是否存在不足，从实际项目开发的需求中就行分析。库在设计过程中是否存在不足，从实际项目开发的需求中就行分析。注：从主外键的设计是否满足软件开发的需要、各数据库对象是否安全存储、是否存在必要的冗余等方面对 Northwind 进行分析。主键与外键在多表中的重复出现,不属于数据冗余，这个概念必须清楚，事实上有许多人还不清楚。非键字段的重复出现,才是数据冗余，但有时又有必要的冗余！高级冗余不是字段的重复出现，而是字段的派生出现。采用与表对象无关的字段作为主键，这样既可以符合实际软件开发中建模工具的特点，也能避免过多的使用联合主键，带来不必要的麻烦。Northwind 库缺少必要的数据冗余，如Products 表，在真实的电子商务 web 开发中商品的价格不可能只存放一个值，它需要存放一些特价或者短期的上调，在这一点上可以改进。必要冗余举例：Order Details 中的“UnitPrice”字段，一对OrderID、ProductID 就能确定 UnitPrice，但要知道 UnitPrice 随着时间有可能产生变化。1818/1818