SQLServer数据库大作业10001

1、数据库认证大作业 2011-2012 （2）华东交通大学数据库认证课程大作业Northwind数据库综合应用设计方案专业班级：09软件工程（+电子商务）2班学院：软件学院学生姓名：毕文超学号:12分 数:学期：2011-2012 (2)任课教师：刘常昱Northwind数据库综合应用设计方案一、Northwind数据库的分析1）对Northwind数据库的各对象（包括数据库表、存储过程、视图、触发器 等）进行分析,描述各自的大致内容和实现的功能。表名：Categories （食品类别表）表结构：字段名称数据类型长度允许为空CategoryID(主键)int4否CategoryNamenvarc

2、har15否Descriptionntext是Pictureimage是主键：CategorylD主键约束名称：PK_Categories关系说明：该表主要用来保存食品种类信息的数据表名：CustomerCustomerDem。消费者表）表结构：字段名称数据类型长度允许为空CustomerID（主键）nchar5否CustomerTypeID（主键）nchar10否组合主键：CustomerlD、CustomerTypelD主键约束名称：PK_CustomerCustomerDemo外键：1 .歹!J CustomerTypelD 弓I用表 CustomerDemographics（Custo

3、merTypelD）2 .列 CustomerlD 引用表 Customers（CustomerlD）外键约束名称：FK_CustomerCustomerDem和 FK_CustomerCustomerDemo_Customers关系说明：该表主要用来保存消费者ID和消费者类别ID信息的数据。表名：CustomerDemographics（消费者类别说明表）表结构：字段名称数据类型长度允许为空CustomerTypeID（主键）nchar10否CustomerDescntext16是主键：CustomerTypeID主键约束名称：PK_CustomerDemographics关系说明：该表主要

4、用来存储消费者类别说明信息表名：Customers（消费者信息表）表结构：2 / 18数据库认证大作业 2011-2012 (2)字段名称数据类型长度允许为空CustomerID（主键）nchar5否CompanyNamenvarchar40否ContactNamenvarchar30是ContactTitlenvarchar30是Addressnvarchar60是Citynvarchar15是Regionnvarchar15是PostalCodenvarchar10是 JCountrynvarchar15是二Phonenvarchar24是Faxnvarchar24是 _主键：Custom

5、erID主键约束名称：PK_Customers关系说明：该表主要用来存储消费者详细信息的数据表。表名：Employees(雇员信息表)表结构：字段名称数据类型长度允许为空EmployeeID（主键）int4否LastNamenvarchar20否二FirstNamenvarchar10否Titlenvarchar30是TitleOfCourtesynvarchar25是BirthDatedatetime8是二HireDatedatetime8是_Addressnvarchar60是 =-Citynvarchar15是Regionnvarchar15是_7PostalCodenvarchar10

6、是_Countrynvarchar15是HomePhonenvarchar24是Extensionnvarchar4是Photoimage16是口Notesntext16是 JReportsToint4是PhotoPathnvarchar255是主键：EmployeelD主键约束名称：PK_Employees外键：1.歹!J ReportsTo 弓 1 用表 Employees(EmployeelD)外键约束名称FK_Employees_Employees关系说明：该表主要用来存储雇员的详细信息数据表。表名：EmployeeTerritories (雇员销售区域表)表结构：字段名称数据类型长度

7、允许为空EmployeeID(主键)int4否TerritoryID( 主键)nvarchar20否主键：EmployeeID、TerritoryID主键约束名称：PK_EmployeeTerritories外键：1 .歹!J EmployeeID 弓 1 用表 Employees(EmployeeID)2 .列 TerritoryID引用表 Territories (TerritoryID)外键约束名称：FK_EmployeeTerritories_Employees 和 FK_EmployeeTerritories_Territories关系说明：该表主要用来存储雇员ID和其对应的销售区域

8、ID。表名：Order Details (订单价格表)表结构：_字段名称口数据类型长度允许为空OrderID（主键）int4否ProductID（主键）int4否 1UnitPricemoney8否Quantitysamllint2否Discountreal4否主键：OrderID、ProductID主键约束名称：PK_Order_Details外键：1 .列 OrderID 引用表 Orders(OrderID)2 .列 ProductID 引用表 Products(ProductID)外键约束名称：FK_Order_Details_Orders 和 FK_Order_Details_Pro

9、ducts关系说明：该表主要用来存储订单价格信息的数据表1 组合、主键、聚集索引在 OrderID和ProductID 上定义。2 在OrderID上也启两个非聚集索引。3 在ProductID上也有两个非聚集索引。4 UnitPrice的 CHECK!勺束被定义为(UnitPrice >= 0)。5 Quantity 的 CHEC©勺束被定义为(Quantity > 0)。表级别的CHECK约束被定义为(Discount >= 0 和Discount < = 1)。表名：Orders (订单表)表结构：一字段名称数据类型长度允许为空OrderID（主键）in

10、t4否.1CustomerIDnchar5是EmployeeIDint4是OrderDatedatetime8是RequiredDatedatetime8是ShippedDatedatetime8是ShipViaint4是Freightmoney8是ShipNamenvarchar40是ShipAddressnvarchar60是ShipCitynvarchar15是ShipRegionnvarchar15是ShipPostalCodenvarchar10是ShipCountrynvarchar15是主键：OrderlD主键约束名称：PK_Orders外键：1 .列 CustomerlD 引用

11、表 Customers(CustomerlD)2 .歹!J EmployeelD 弓I用表 Employees(EmployeelD)3一 歹!J ShipVia 引用表 Shippers(ShipperID)外键约束名称FK_Orders_Customers, FK_Orders_Employees和 FK_Orders_Shippers关系说明：该表主要用来存储订单详细信息的数据1 在CustomerID上有两个非聚集索引。2 在EmployeeID上有两个非聚集索引。3 在ShipVia上也有一个非聚集索引。表名：Products (产品信息表)表结构：字段名称数据类型长度允许为空Pro

12、ductID（主键）int4否ProductNamenvarchar40否SupplierIDint4是CategoryIDint4是QuantityPerUnitnvarchar20是UnitPricemoney8是1UnitsInStocksamllint2是一UnitsOnOrdersamllint2是ReorderLevelsamllint2是Discontinuedbit1否 1主键：ProductID主键约束名称：PK_Products外键：1 .歹!J CategorylD 弓I用表 Categories(CategorylD)2 .列 SupplierlD 引用表 Suppli

13、ers(SupplierlD)外键约束名称FK_Products_Categories 和 FK_Products_Suppliers关系说明：该表主要用来存储产品详细信息的数据表。1 在SupplierID 上有两个非聚集索引。2 在CategoryID 上有两个非聚集索引。3 UnitPrice 的 CHECK勺束被定义为(UnitPrice >=)。4 UnitsInStock 的 CHEC©勺束被定义为(UnitsInStock >=)5 UnitsOnOrder 的 CHEC勺束被定义为(UnitsOnOrder >=)。6 ReorderLevel 的

14、CHEC©勺束被定义为(ReorderLevel >=)。表名：Region (区域表)表结构：8 / 18数据库认证大作业 2011-2012 (2)1 字段名称数据类型长度允许为空RegionID（主键）int4否RegionDescriptionnchar50否主键：RegionlD主键约束名称：PK_Region关系说明：该表主要用来存储区域信息数据。表名：Shippers （托运信息表）表结构：字段名称数据类型长度允许为空ShipperID（主键）int4否CompanyNamenvarchar40否Phonenvarchar24是，主键：ShipperID主键约束名

15、称：PK_Shippers关系说明：该表主要用来存储托运公司的详细信息数据。表名：Suppliers （供业商信息表）表结构：_字段名称数:据类型长度允许为空SupplierID（主键）int4否CompanyNamenvarchar40否ContactNamenvarchar30是ContactTitlenvarchar30是Addressnvarchar60是Citynvarchar15是一Regionnvarchar15是PostalCodenvarchar10是 一Countrynvarchar15是|Phonenvarchar24是 1Faxnvarchar24是HomePagent

16、ext16是主键：SupplierID主键约束名称：PK_Suppliers关系说明：该表主要用来存储供应商详细信息数据。表名：Territories (销售区域信息表)表结构：字段名称数据类型长度允许为空TerritoryID（ 主键）nvarchar20否1TerritoryDescriptionnchar50否RegionIDint4否 一主键：TerritoryID主键约束名称：PK_Territories外键：1.列 RegionID 引用表 Region(RegionID)外键约束名称FK_Territories_Region关系说明：该表用来存储销售区域详细说明信息的数据。存储过

17、程分析：1. CustOrderHist参数：消费者编号作用：用来显示指定消费者购买各种商品的总数。2. CustOrdersDetail参数：订单编号作用：用来显示指定订单编号的销售信息。3. CustOrdersOrders参数：消费者编号作用：用来显示指定消费者所有订单销售期限情况。4. Employee Sales by Country参数：起始时间、结束时间作用：用来显示在起始时间和结束时间之间的需要发货订单的负责人信息和订 单的详细信息。5. Sales by Year参数：起始时间、结束时间作用：用来查询在起始时间和结束时间之间发货的订单信息及所属年份(包括售价)。6.Sales

18、ByCategory参数：食品类别、年份返回值：返回指定食品名称、年份的销售食品名称和其售价。7. Ten Most Expensive Products作用：显示Products表中食品单价排在前10位的食品名称和食品单价。视图分析：1. Alphabetical list of products未过期食品的详细信息及其所属的类别。2. Category Sales for 1997显示种类食品在1997年的实际销售部。3. Current Product List显示未过期食品的信息。4. Customer and Suppliers by City所有消费者和供货商的信息。20 / 18

19、5. Invoices按照订单发货的详细信息6.Order Details Extended订单明细的详细信息。7. Order Subtotals每单的实际销售额8. Orders Qry有订单的客户的详细订单信息及客户信息9. Product Sales for 1997每种食品在1997实际销售额以及食品的详细信息10. Products Above Average Price单位售价大于所有食品平均售价的食品清11. Products by Category12. Quarterly Orders13. Sales by Category14. Sales Totals by Amou

20、nt未过期食品的存货情况(按照商品分类)1997年有订单的客户信息1997年订单食品的详细情况1997年每单销售额大于2500的订单的信息15. Summary of Sales by Quarter1997 年已发货订单的发货时间16. Summary of Sales by Year 1997年已发货订单的发货时间2)给出Northwind库的整体 ER关系图， 给出Employees和Customers表的数据库字典。口torrcrlDnc：h-45JCli U/ Wkanrmrtrd1b 40)CanliClN«-«w mOEmctTit 总EN：iiw3mAddi

21、es：rm,eriiar EOJGtRf0Qfrw,di.而RislaCixletwmd51由GzurtijIW1命前101Mm1 二 .言.户表Oida:：L土OiiJfilt)rMCnwIDn:hy5|Ml)Em'nyE 口irt小小r化而七忸由 WKieHeEliJie“ Shgfjja嶂dustrtSHiaht，通上工什rrcr<yS hiiMairEr'flid 卸 Mtljl由出簿em)的il吗宙G|占什大巾rwaicr削 151,jhaHeOi&ftnwdi£har|1b|iShiyr/C.-iCfflcFh'Oichailinji

22、ShjQmd/fw 即 l吗ifil 5|fK_Errocwi1Enp31M 町litLasiNm®nvar：har_3Jl!Ri5tHam/nYcrdh(ldTi%mW卜闻到Ti IrOlfDurtillJrwif.hW第 jiietnigHincDdteSHrwHWWdlB(0)附flVrtr.ih<i5iReqcnnvar 二 haf1 印 I1ab 宗冒 Co daKu帆m*WiB(5HnnpPhnnYTh*加lE%terbiLnrwa二 har：4|Photo闿白»NC*B4HiSKlRfipcwfaTomrPhotoPalh口 v ar : harS51E

23、F号。8：而工病FK. Infer Cetaite CidsnsR". Jr dare s-hpognXRegion ：地区表RegionlDintRegioriDescription richar(50JOi-dai二REk ：了单营菸n“ inirtdlk1$PirdbcllDirt<lk2.UfiitFficacan切MirrSly5scmWiPladutrs：产品参FrrdxjHDFlDducIMaiTientiwi_ 卜 &i 口 U的卬修2枇<lk2>L =lf 4i.ii.IZ1MtEusr制时卸UritmMachmr|U|UtitPmcEUi

24、iiMi 的位kMuFy imdlrlUnits-OnOrde«milril'FeciDBiLeEismalntDiK&nfeni-fldbtFK_Rjdxi_Si.cip(ii£*图一CustamErCushzimerDemo ：客尸类型表 1CustomarlDnch司司CustcmerT5ipelD ncharfllJ)S fk七"iSTf RShcoefiOirtLoirpan.门 etk n'/cicheit4OIFfiEchralcbfalJ-lSuixloit :面邺Territories ；地域表TeiritorylDnva

25、rthai(20)Teiritwy Descriptionnchai(50JRegiordDint匚ustomEr Demogra团ics:客户类型表2CustomerTiflpelD ncha<(10CustomBfDssc ntexlEmploeeTenikines ：员工部门表EmplojieelD HT erritorlD nvarchar(2Q)弓 wgIDintC5Man1中nv*ha(4CcniBctN-aniE皿塞廿总何1CoHtettfnienrvai-clJt 1AddrexinvaMGl： 1s胸市什。出1Regeniwapherhn户OWEnMchmtl 11Co

26、urwjiPfian*皿”卜.包1Fate由凸日曰冲HsrrfiFacpmCsteq即 D Cstegaiame Peiffpiiwi PeIlpcnaicheMlS hier 扁强上图Employees：员工表相应字段：EmployeelD:员工代号;LastName + FirstName:员工姓名;Title:头衔;TitleOfCourtesy:尊称;BirthDate:出生日期；HireDate:雇用日期;Address：家庭地址；City:所在城市；Region:所在地区；PostalCode邮编;Country:国家用；HomePhone：宅电;Extension:分机;Phot

27、o:手机;notes:照片；ReportsTo：上级;PhotoPath:照片Customers:客户表相应字段：CustomerID:客户 ID ;CompanyName:所在公司名称；ContactName:客户姓名；ContactTitle:客户头衔；Address：联系地址；City :所在城市；Region:所在地区；PostalCode邮编;Country:国家Phone:电话；Fax：传真Northwind 库的综合应用注：对于一个数据库而言，不仅仅是提供一个数据的存储位置，更多的是能够提供对 所存储数据的处理功能，满足业务需要。下面是一些在项目开发过程中经常使用的数 据库设计功

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

29、le(idintidentity(1,1),nidint)-定义表变量 declarePageLowerBoundint-定义此页的底码 declarePageUpperBoundint-定义此页的顶码 setPageLowerBound=(pageindex-1)*pagesize setPageUpperBound=PageLowerBound+pagesizesetrowcountPageUpperBound insertintoindextable(nid)selectgidfromTGongwenwherefariqi>dateadd(day,-365,getdate()ord

30、erbyfariqidescselectO.gid,O.mid,O.title,O.fadanwei,O.fariqifromTGongwenO,indextabletwhereO.gid=t.nidandt.id>PageLowerBoundandt.id<=PageUpperBoundorderbyt.id end setnocountoff以上存储过程运用了SQLSERVER的最新技术一一表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然，在这个过程中，您也可以把其中的表变量写成临时表：CREATETABLE#Temp 。但很明显，在 SQLSERVER中，用临时

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

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

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

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

35、另一个方法就是在你的服务器或者网络中使用安全目录。你可以限制访问这个目录的权限，这样就只有一小部分受限制的人能够访问你的备份文件。通过在安全目录上使用上述的技术，你就可以创建另一个级别的安全措施了。这仍然不会消除加密的需求，但是它提供了额外的安全措 施。直接备份到磁带备份的另一个选择就是直接备份到磁带上，以便在你的网络中保证备份文件的安全。这种方法减少了对你的备份文件的不正当访问的问题。用这种方式有一个大问题：我写入的大部分关于备份的内容都是首先写入磁盘的，以便在必要的时候能够快速重新存储，然后再为了长期的存储而归档到磁带上。这种方式 消除了你的备份落入坏人之手的机会，但是，不幸的是，它让其他的处理过程变得困难。加密备份如果你真的需要保证你的备份文件的安全，最好的方式就是在你创建备份文件的时候对其进行加密。 不幸的是，SQL Server中没有工具可以让你完成这个任务，但是看看以下的产品，它们可以让你创建各 种级别的密码和加密技术加密备份。Idera 的 SQLsafeQuest 的 SQL LiteSpeedRed-Gate 的 SQL Backup3)数据库使用中的自定义提示非常重要，它可以更加直观的把一些操作