数据库大作业报告Word文件下载.docx

1、系统主要业务分析UPS物流配送公司的主要业务是为用户配送货物；物流公司的组织结构分为转运中心和配送单位；转运中心主要负责车辆的维护、配送点、配送线路、配送价格的维护、权限的管理；配送点主要是接收客户订单，并联系转运中心将货物运送到目的地；配送点同时还负责货物的配送工作。按照业务及系统功能简单总结数据对象： 订单 路线信息 运输工具 发件人 收件人 员工 转运中心数据库结构设计根据系统的分布式部署设计，数据库将部署到一部独立的计算机中。根据前期的分析，由于此次作业中不涉及大量数据的存储。预留数据库空间3M，日增长约1M，日志空间2G，日增长10%。数据库逻辑设计一. 数据库表名汇总表名描述发件人

2、用于存储发件人信息发件人_订单（关系表）发件人与订单之间的关系表收件人用于存储收件人信息收件人_订单（关系表）收件人与订单之间的关系表订单用于存储订单的详细信息客户表用于存储客户的基本信息情况员工登陆用于存储员工登录信息员工用于存储员工的基本情况货物用于存储货物的基本情况智慧标签运输器用于存储运送货物的运输器信息路线信息_运输器（关系表）路线信息和运输器生成的关系表路线信息_中转站（关系表）路线信息和中转站生成的关系表路线信息用于存储路线信息中转站用于存储中转站的基本信息智慧标签_中转站（关系表）智慧标签和中转站生成的关系表中转站_员工（关系表）中转站和员工生成的关系表智慧标签_员工（关系表）

3、智慧标签和员工生成的关系表订单状态的信息用于存储订单状态的基本信息异常订单用于存储异常订单的信息总体关系表二. 数据库实体函数依赖与规范实体名函数依赖满足范式1. 订单号（主键）运单类型.始发地.目的地.费用.订单生成时间3NF1. 发件人姓名，发件人电话（主键）发件人地址.发件人邮编2. 发件人地址发件人邮编2NF1. 收件人姓名，收件人电话（主键）收件人地址.收件人邮编2. 收件人地址收件人邮编1. 订单号，标签ID（主键）类型.重量1. 员工编号（主键）员工姓名.员工性别.员工年龄.员工联系电话.员工家庭住址.员工部门.员工职位2. 员工联系电话员工姓名.员工性别.员工年龄.员工家庭住址

4、.员工部门.员工职位3. 员工联系电话员工部门员工职位1. 运输器编号（主键）运输器类型.出发地.目的地.装载时间.到达时间1. 标签ID（主键）出发地.目的地.中转站1ID.中转站2ID.中转站3ID.中转站4ID1. 标签ID,订单号（主键）二维码.尺寸.重量2. 二维码尺寸.重量1. 员工登录名，员工编号员工登陆密码订单状态信息1. 订单号（主键）所在地.扫面时间.上一站.下一站.是否到达.是否正常1. 订单号，标签ID（主键） 异常信息.异常时间.异常地点1. 中转站ID（主键） 中转站名.中转站地址.到达时间.出发时间2. 中转站地址中转站名3. 客户电话客户姓名.客户地址.客户邮编

5、三. 表结构设计 客户 智慧标签 运输器 货物 员工登陆 订单状态信息 异常订单 中转站 收件人-订单 发件人-订单 运输器-员工 智慧标签-员工 智慧标签-中转站 中转站-员工 路线信息-中转站 路线信息-运输器四. 数据库表设计：将ER图转化为数据表五. 实验要求1.员工登陆 proc（username ,password分开判断并给出提示信息）createprocyg_denglu（yonghumingchar（20）,koulingchar（20）asifexists（select*from员工登陆where员工登陆名=yonghumingand员工登陆密码=kouling）print

6、登陆成功;elseifnotexistswhere员工登陆名=yonghuming）员工登陆名不存在elseifexistswhere员工登陆名=yonghumingand员工登陆密码!=kouling）员工登陆密码错误execyg_dengluyonghuming=luchenyang,kouling=yonghuming=wanger002,kouling=002zhaoyi0012.统计员工工作量 proc/view（全体员工和单个员工）统计全体员工工作量：createviewall_ygworkloadselectcount（distinct订单号）as全体员工工作量from智慧标签_

7、员工select*fromall_ygworkload查看单个员工工作量createviewevery_ygworkloadselect员工编号,count（标签ID）as工作量groupby员工编号select*fromevery_ygworkload3.统计中转站的运送情况（正常包裹 AND 异常报告的数量等） 对于异常情况查看详情（交通，损坏，丢失）订单状态信息表异常订单表创建视图查询正常包裹数量createview正常包裹数量selectcount（订单号）as正常包裹数量from订单状态信息where是否正常=是创建视图查询异常报告数量createview异常包裹数量selectco

8、unt（订单号）as异常包裹数量否创建视图查看异常订单详情createview异常订单详情select*from异常订单4.用户查看运送状态（列表显示时间、地点，员工、中转站）创建视图查看订单运送状态，时间，地点，员工，中转站createview查看订单状态信息select*from订单状态信息5.trigger运单一旦开始派送，则不允许修改运单号、发件人、收件人。由于数据库设计的原因，我们需要多个触发器完成这项工作对发件人表：createtriggerlimit_发件人on发件人forupdateifexists（select*fromdeleted,发件人where发件人.发件人姓名=de

9、leted.发件人姓名and发件人.发件人电话=deleted.发件人电话）beginrollback print订单已生成,不能修改表中任何信息end对收件人表：createtriggerlimit_收件人on收件人ifexists（select*fromdeleted,收件人where收件人.收件人姓名=deleted.收件人姓名and收件人.收件人电话=deleted.收件人电话）对订单号：createtriggerlimit_订单on订单ifexists（select*fromdeleted,订单where订单.订单号=deleted.订单号）尝试修改发件人表中信息，提示“订单已生成，

10、不能修改表中任何信息”尝试修改收件人表中信息，提示“订单已生成，不能修改表中任何信息”尝试修改订单表中信息，提示“订单已生成，不能修改表中信息”6.一旦新建运单，就判断发件人是否在用户之中，若是则转用户表，显示用户信息，如不是，自动增加用户。 triggercreatetriggeradd_userafterinsertdeclare客户姓名varchar（30）declare客户电话varchar（20）declare客户地址varchar（40）declare客户邮编varchar（20）ifnotexists（select*frominserted,客户表whereinserted.发件

11、人姓名=客户表.客户姓名andinserted.发件人电话=客户表.客户电话）select客户姓名=inserted.发件人姓名,客户电话=inserted.发件人电话,客户地址=inserted.发件人地址,客户邮编=inserted.发件人邮编frominsertedinsertinto客户表（客户姓名,客户电话,客户地址,客户邮编）values（客户姓名,客户电话,客户地址,客户邮编）elseifexists（客户信息：select客户姓名,客户电话,客户地址,客户邮编from客户表,inserted发件人表_订单现在，我往发件人订单表中插入数据insertinto发件人values（王

12、五,湖南省常德市insertinto发件人_订单values（插入后的客户表自动添加了“王五”的数据现在我要插一条数据客户表中有，发件人表和发件人_订单表中没有。客户表中先插入赵六的数据赵六云南昆明可以看到，显示出了客户表中赵六的信息。一. 实验小结卢晨阳：这次数据库的大作业项目中，我担任本组的组长，主要负责统筹整个小组的实验进度，实体的设定以及存储过程的编写，数据库具体操作，还有实验报告的撰写。即使是上了一学期的数据库课程，但这些都是针对数据库的一些基本操作，没有深入了解到数据库的内部世界。SQL Sever的操作和编程在本次项目中都让我得到了很好的锻炼。在当初定实体时，对每一个可能要实现功

13、能的内容要进行详细的考虑，每一个实体之间的关系是一对一，还是多对一，还是多对多，实体这样定下之后，管理上会不会出现问题，信息存储会不会有冗余，考虑问题需要广泛且深刻。同样，在存储过程的编写过程中，一些在平常实验中没有遇到过的问题频频出现，比如，数据插入时，建立外键数据的约束，数据的统一性，存储过程的使用，还有在其他系统中如何连接数据库，不同连接方法的差别之处等，解决也是经历了一些波折，自然，通过自己的学习来解决遇到的问题，其中的收获不言而喻。再次通过自己实践，用PowerDesigner将画好的逻辑E-R图生成物理E-R图，并转入真实数据库中，了解生成原理也是一大收获。而通过这次团队合作，作为

14、组长的我需要担负起这个责任，根据每位成员的实力，把他们放在相应的位置上，平衡好内部各组员之间的任务，在成员之间出现意见分歧时要尽可能好的处理好，让内部和谐共处，发挥团队合作的精神。总的来说，通过这次项目实践，是我锻炼了不少，重要的是让我对数据库的操作和应用有了更近一步的提升以及作为组长，如何合理分配组员们的任务，从而使得项目完成。段云涛：这次我在数据库大作业里，主要负责的是数据库ER图的设计和绘制，以及物理图的绘制，数据库表格脚本生成和数据库的建立，以及实验报告的数据库表格属性列整理说明部分。通过这次数据库大作业，收获颇多。第一，我更加清楚地知道了数据库设计的流程和方式，以及再设计过程中要需要

15、注意的，比如确定实体时，不必确定外键，只用考虑到两个实体之间的关系是一对多，多对一，还是一对一，或是多对多的。第二，我学会了power design的基本使用，以及用它绘制ER图，并学会运用cdm生成pdm再到生成SQL脚本的基本方法，了解了其生成的工作原理。第三，也是最重要的，就是积攒了更多关于数据库设计的经验，明白了一些之前在课程学习中不懂的问题，比如，外键与主键的名字可以不相同，但是必须要保持数据类型及长度一样。一个表和另一个表之间的关系可以不止一个等等。数据库设计是基于经验的，因此，积攒经验十分重要的，并且，详细的了解客户的需求也很重要。第四，通过这次大作业，锻炼了我的团队协作能力，特

16、别是从其他小组成员里看到了处理当分歧发生如何处理等等。第五，通过看其他小组成员写的触发器和其他数据库对象，我更加进一步明白了触发器以及其他数据库对象的作用，以及它们能够应用于哪些情况。总之，这次数据库大作业，让我增加了对数据库管理系统的了解和应用，积攒了数据库的设计经验，锻炼我的团队协作能力。附录.生成的数据库脚本/*=*/* DBMS name: Microsoft SQL Server 2012 */* Created on: 2014/4/17 11:49:55 */if exists （select 1fromsys.sysreferences r join sys.sysobject

17、s o on （o.id = r.constid and o.type = F where r.fkeyid = object_id（中转站） and o.name = FK_中转站_RELATIONS_路线信息alter table 中转站 drop constraint FK_中转站_RELATIONS_路线信息go员工FK_员工_REFERENCE_智慧标签alter table 员工 drop constraint FK_员工_REFERENCE_智慧标签智慧标签FK_智慧标签_REFERENCE_货物alter table 智慧标签 drop constraint FK_智慧标签_R

18、EFERENCE_货物订单FK_订单_REFERENCE_发件人alter table 订单 drop constraint FK_订单_REFERENCE_发件人FK_订单_REFERENCE_收件人 drop constraint FK_订单_REFERENCE_收件人订单状态信息FK_订单状态信息_REFERENCE_订单alter table 订单状态信息 drop constraint FK_订单状态信息_REFERENCE_订单货物FK_货物_REFERENCE_订单alter table 货物 drop constraint FK_货物_REFERENCE_订单路线信息FK_路线

19、信息_REFERENCE_智慧标签alter table 路线信息 drop constraint FK_路线信息_REFERENCE_智慧标签运输途径FK_运输途径_RELATIONS_路线信息alter table 运输途径 drop constraint FK_运输途径_RELATIONS_路线信息from sysindexes where id = object_id（and name = Relationship_9_FKandindid 0andindid 255） drop index 中转站.Relationship_9_FKfrom sysobjectsand type = U drop table 中转站发件人 drop table 发件人Reference_9_FK drop index 员工.Reference_9_FK drop table 员工异常订单 drop table 异常订单from sysobjects收件人 drop table 收件人Reference_5_FK drop index 智慧标签.Reference_5_FK wher