SQLServer数据库大作业1.doc

数据库认证大作业2011-2012（2）华东交通大学数据库认证课程大作业 Northwind数据库综合应用设计方案专业班级： 09软件工程（+电子商务）2班 学 院： 软件学院 学生姓名： 毕文超 学 号： 12 分 数： 学 期： 2011-2012（2） 任课教师： 刘常昱 Northwind数据库综合应用设计方案一、 Northwind数据库的分析1）对Northwind数据库的各对象（包括数据库表、存储过程、视图、触发器等）进行分析，描述各自的大致内容和实现的功能。表名：Categories(食品类别表)表结构：字段名称数据类型长度允许为空CategoryID(主键)int4否CategoryNamenvarchar15否Descriptionntext是Pictureimage是主键：CategoryID主键约束名称：PK_Categories关系说明：该表主要用来保存食品种类信息的数据。表名：CustomerCustomerDemo(消费者表)表结构：字段名称数据类型长度允许为空CustomerID(主键)nchar5否CustomerTypeID(主键)nchar10否组合主键：CustomerID、CustomerTypeID主键约束名称：PK_CustomerCustomerDemo外键：1.列 CustomerTypeID引用表CustomerDemographics(CustomerTypeID)2.列 CustomerID引用表Customers(CustomerID)外键约束名称：FK_CustomerCustomerDemo和FK_CustomerCustomerDemo_Customers关系说明：该表主要用来保存消费者ID和消费者类别ID信息的数据。表名：CustomerDemographics(消费者类别说明表)表结构：字段名称数据类型长度允许为空CustomerTypeID(主键)nchar10否CustomerDescntext16是主键：CustomerTypeID主键约束名称：PK_CustomerDemographics关系说明：该表主要用来存储消费者类别说明信息。表名：Customers(消费者信息表)表结构：字段名称数据类型长度允许为空CustomerID(主键)nchar5否CompanyNamenvarchar40否ContactNamenvarchar30是ContactTitlenvarchar30是Addressnvarchar60是Citynvarchar15是Regionnvarchar15是PostalCodenvarchar10是Countrynvarchar15是Phonenvarchar24是Faxnvarchar24是主键：CustomerID主键约束名称：PK_Customers关系说明：该表主要用来存储消费者详细信息的数据表。表名：Employees(雇员信息表)表结构：字段名称数据类型长度允许为空EmployeeID(主键)int4否LastNamenvarchar20否FirstNamenvarchar10否Titlenvarchar30是TitleOfCourtesynvarchar25是BirthDatedatetime8是HireDatedatetime8是Addressnvarchar60是Citynvarchar15是Regionnvarchar15是PostalCodenvarchar10是Countrynvarchar15是HomePhonenvarchar24是Extensionnvarchar4是Photoimage16是Notesntext16是ReportsToint4是PhotoPathnvarchar255是主键：EmployeeID主键约束名称：PK_Employees外键：1.列 ReportsTo引用表Employees(EmployeeID)外键约束名称FK_Employees_Employees关系说明：该表主要用来存储雇员的详细信息数据表。表名：EmployeeTerritories(雇员销售区域表)表结构：字段名称数据类型长度允许为空EmployeeID(主键)int4否TerritoryID(主键)nvarchar20否主键：EmployeeID、TerritoryID主键约束名称：PK_EmployeeTerritories外键：1.列 EmployeeID引用表Employees(EmployeeID)2.列 TerritoryID引用表Territories (TerritoryID)外键约束名称：FK_EmployeeTerritories_Employees和FK_EmployeeTerritories_Territories关系说明：该表主要用来存储雇员ID和其对应的销售区域ID。表名：Order Details(订单价格表)表结构：字段名称数据类型长度允许为空OrderID(主键)int4否ProductID(主键)int4否UnitPricemoney8否Quantitysamllint2否Discountreal4否主键：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 =)。4UnitsInStock 的 CHECK 约束被定义为 (UnitsInStock =)5UnitsOnOrder 的 CHECK 约束被定义为 (UnitsOnOrder =)。6ReorderLevel 的 CHECK 约束被定义为 (ReorderLevel =)。表名：Region(区域表)表结构：字段名称数据类型长度允许为空RegionID(主键)int4否RegionDescriptionnchar50否主键：RegionID主键约束名称：PK_Region关系说明：该表主要用来存储区域信息数据。表名：Shippers(托运信息表)表结构：字段名称数据类型长度允许为空ShipperID(主键)int4否CompanyNamenvarchar40否Phonenvarchar24是主键：ShipperID主键约束名称：PK_Shippers关系说明：该表主要用来存储托运公司的详细信息数据。表名：Suppliers(供应商信息表)表结构：字段名称数据类型长度允许为空SupplierID(主键)int4否CompanyNamenvarchar40否ContactNamenvarchar30是ContactTitlenvarchar30是Addressnvarchar60是Citynvarchar15是Regionnvarchar15是PostalCodenvarchar10是Countrynvarchar15是Phonenvarchar24是Faxnvarchar24是HomePagentext16是主键：SupplierID主键约束名称：PK_Suppliers关系说明：该表主要用来存储供应商详细信息数据。表名：Territories(销售区域信息表)表结构：字段名称数据类型长度允许为空TerritoryID(主键)nvarchar20否TerritoryDescriptionnchar50否RegionIDint4否主键：TerritoryID主键约束名称：PK_Territories外键：1.列 RegionID引用表Region(RegionID)外键约束名称FK_Territories_Region关系说明：该表用来存储销售区域详细说明信息的数据。存储过程分析：1CustOrderHist参数：消费者编号作用：用来显示指定消费者购买各种商品的总数。2CustOrdersDetail参数：订单编号作用：用来显示指定订单编号的销售信息。3CustOrdersOrders参数：消费者编号作用：用来显示指定消费者所有订单销售期限情况。4Employee Sales by Country参数：起始时间、结束时间作用：用来显示在起始时间和结束时间之间的需要发货订单的负责人信息和订单的详细信息。5Sales by Year参数：起始时间、结束时间作用：用来查询在起始时间和结束时间之间发货的订单信息及所属年份(包括售价)。6.SalesByCategory 参数：食品类别、年份 返回值：返回指定食品名称、年份的销售食品名称和其售价。7Ten Most Expensive Products作用：显示Products表中食品单价排在前10位的食品名称和食品单价。视图分析：1Alphabetical list of products未过期食品的详细信息及其所属的类别。2Category Sales for 1997显示种类食品在1997年的实际销售部。3Current Product List显示未过期食品的信息。4Customer and Suppliers by City所有消费者和供货商的信息。5Invoices 按照订单发货的详细信息。6Order Details Extended订单明细的详细信息。7Order Subtotals每单的实际销售额8Orders Qry 有订单的客户的详细订单信息及客户信息9Product Sales for 1997 每种食品在1997实际销售额以及食品的详细信息10Products Above Average Price单位售价大于所有食品平均售价的食品清单。11Products by Category未过期食品的存货情况（按照商品分类）12Quarterly Orders1997年有订单的客户信息13Sales by Category1997年订单食品的详细情况14Sales Totals by Amount1997年每单销售额大于2500的订单的信息15Summary of Sales by Quarter1997年已发货订单的发货时间16Summary of Sales by Year 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，存储过程能够得到所需要的该分页中的记录集合。 CREATE proce getProductbyPage(pagesize char(10)pageindex char(100) )assetnocountonbegindeclareindextabletable(idintidentity(1,1),nidint)-定义表变量declarePageLowerBoundint-定义此页的底码 declarePageUpperBoundint-定义此页的顶码 setPageLowerBound=(pageindex-1)*pagesize setPageUpperBound=PageLowerBound+pagesizesetrowcountPageUpperBoundinsertintoindextable(nid)selectgidfromTGongwenwherefariqidateadd(day,-365,getdate()orderbyfariqidescselectO.gid,O.mid,O.title,O.fadanwei,O.fariqifromTGongwenO,indextabletwhereO.gid=t.nidandt.idPageLowerBoundandt.id=PageUpperBoundorderbyt.idendsetnocountoff以上存储过程运用了SQLSERVER的最新技术表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然，在这个过程中，您也可以把其中的 表变量写成临时表：CREATETABLE#Temp。但很明显，在SQLSERVER中，用临时表是没有用表变量快的。2）数据库的安全是非常重要的一个工作，如果要对Northwind库进行安全性的设计，请给出你的方案。（提示：报告对存储过程视图等对象的加密、登录用户账户的管理、数据库的用户操作日志的管理等多个方面进行分析，给出具体的方法）随着数据安全需求的不断增加，不论以何种方式，都不要忽略对你的数据库备份文件的安全保证。在本地的SQL Server备份中，备份文件中的数据是以普通文本格式存储的，仅仅用文本编辑器就可以轻松阅读。根据表中使用的数据类型，一些数据比另外的一些数据更容易查看。 试试这个你几乎从来没有进行过的试验。对Northwind数据库进行备份，或者任何其它小型数据库，然后用任意的文本编辑器打开备份文件。你将会看到数据自身有一点难以理解，但是只要你看到存储过程的注释，然后通读一下文件，你就会看到你的备份文件的真正价值所在。如果你采取行动，将用户ID和/或密码存储在你的存储过程中，首先这可不是一个好主意，这个数据现在就可以被任何能够接触到备份数据库的人所访问。如果你有其他藏有秘密信息的文本数据类型，你也会让这些数据非常有意义了。备份密码SQL Server中的一个选项就是创建用密码创建备份。这是你在创建备份的时候可以使用的另一个选择，但是在企业版管理器或者SQL Server管理套件中，并没有提供这个选项。这里是一个使用密码选项备份的例子:backup database northwind to disk=c:northwind.bak with mediapassword = Backup2006这个过程需要密码来重新存储文件，但是使用文本编辑器，这些数据仍然是可以访问的。还有，重新存储不能使用GUI来完成，所以它必须通过T-SQL重新存储命令和密码一起完成任务。加密存储过程一种防止你的存储过程被用于查看的方法就是在创建你的存储过程的时候使用“带加密”的选项。这样的话，备份文件中的数据也是经过加密的了。要使用加密来创建一个存储过程，如下所示: createproceduredbo.testEncryptionwithencryptionasselect*FROMproducts 加密数据另一个选择就是在你把数据存储到你的数据库表中的时候，对数据进行加密。在SQL Server 2000中没有本地的方法来完成，但是有很多工具你可以使用:针对SQL Server的NetLib Encryptionizer使用XP_CRYPT加密SQL ServerSQL Server 2005中存在本地加密功能。看看微软的文章如何:加密一列数据，那里解释了这个过程。在你加密了数据库中的数据之后，当你创建备份的时候，数据仍然是经过加密的。保证文件系统的安全保卫你的备份文件的安全的另一个方法就是在你的服务器或者网络中使用安全目录。你可以限制访问这个目录的权限，这样就只有一小部分受限制的人能够访问你的备份文件。通过在安全目录上使用上述的技术，你就可以创建另一个级别的安全措施了。这仍然不会消除加密的需求，但是它提供了额外的安全措施。直接备份到磁带备份的另一个选择就是直接备份到磁带上，以便在你的网络中保证备份文件的安全。这种方法减少了对你的备份文件的不正当访问的问题。用这种方式有一个大问题:我写入的大部分关于备份的内容都是首先写入磁盘的，以便在必要的时候能够快速重新存储，然后再为了长期的存储而归档到磁带上。这种方式消除了你的备份落入坏人之手的机会，但是，不幸的是，它让其他的处理过程变得困难。加密备份如果你真的需要保证你的备份文件的安全，最好的方式就是在你创建备份文件的时候对其进行加密。不幸的是，SQL Server中没有工具可以让你完成这个任务，但是看看以下的产品，它们可以让你创建各种级别的密码和加密技术加密备份。Idera的 SQLsafeQuest的 SQL LiteSpeedRed-Gate的 SQL Backup3)数据库使用中的自定义提示非常重要，它可以更加直观的把一些操作不当的数据库行为提示给用户。利用 RAISEERROR语句完成如下的功能：首先自定义一个错误号为50512的用户自定义错误，错误的严重级别为10,错误的文本消息为Cant find the customer ID. ，另外消息中还要加上表名和输入的CustomerID, 并且当发生消息时将消息写入 Microsoft Windows NT 应用程序日志中。然后创建一个名为ExistCustomerID的存储过程，以用它来找出SQL SERVER中的northwind数据库的Customer表中，指定的CustomerID是否存在，