数据库大作业报告

《数据库大作业报告》由会员分享，可在线阅读，更多相关《数据库大作业报告（32页珍藏版）》请在装配图网上搜索。

1、云南大学软件学院数据库期末大作业课程报告Final Course ReportSchool of Software, Yunnan University个人成绩序号学号姓名成绩12 学期: 2014-2015学年上学期 课程名称:数据库原理与实用技术实验 任课教师: 张璇、刘宇 实践题目: 实验10 数据库设计 小组长: 联系电话: 电子邮件: 完成提交时间：2014年 6月20日1.引言数据库说明UPS物流配送系统采用的是课程实验中使用的SQL Sever，使用的版本是2012版。设计的数据库的全局数据库名为UPS数据库。目的和作用将业务需求分析，系统设计中对信息的描述进一步分析并加以总计，

2、抽象出数据集合（数据库表）。对数据集合做进一步分析，确定集合之间的关系并最终形成数据库物理模型，以便开发人员建立物理数据库。数据库设计抽象数据对象分析系统主要业务分析UPS物流配送公司的主要业务是为用户配送货物；物流公司的组织结构分为转运中心和配送单位；转运中心主要负责车辆的维护、配送点、配送线路、配送价格的维护、权限的管理；配送点主要是接收客户订单，并联系转运中心将货物运送到目的地；配送点同时还负责货物的配送工作。按照业务及系统功能简单总结数据对象：l 订单l 路线信息l 运输工具l 发件人l 收件人l 员工l 转运中心数据库结构设计根据系统的分布式部署设计，数据库将部署到一部独立的计算机中

3、。根据前期的分析，由于此次作业中不涉及大量数据的存储。预留数据库空间3M，日增长约1M，日志空间2G，日增长10%。数据库逻辑设计一. 数据库表名汇总表名描述发件人用于存储发件人信息发件人_订单(关系表)发件人与订单之间的关系表收件人用于存储收件人信息收件人_订单(关系表)收件人与订单之间的关系表订单用于存储订单的详细信息客户表用于存储客户的基本信息情况员工登陆用于存储员工登录信息员工用于存储员工的基本情况货物用于存储货物的基本情况智慧标签用于存储货物的基本情况运输器用于存储运送货物的运输器信息路线信息_运输器(关系表)路线信息和运输器生成的关系表路线信息_中转站(关系表)路线信息和中转站生成

4、的关系表路线信息用于存储路线信息中转站用于存储中转站的基本信息智慧标签_中转站(关系表)智慧标签和中转站生成的关系表中转站_员工(关系表)中转站和员工生成的关系表智慧标签_员工(关系表)智慧标签和员工生成的关系表订单状态的信息用于存储订单状态的基本信息异常订单用于存储异常订单的信息总体关系表二. 数据库实体函数依赖与规范实体名函数依赖满足范式订单1. 订单号（主键）运单类型.始发地.目的地.费用.订单生成时间3NF发件人1. 发件人姓名，发件人电话（主键）发件人地址.发件人邮编2. 发件人地址发件人邮编2NF收件人1. 收件人姓名，收件人电话（主键）收件人地址.收件人邮编2. 收件人地址收件人

5、邮编2NF货物1. 订单号，标签ID(主键)类型.重量3NF员工1. 员工编号（主键）员工姓名.员工性别.员工年龄.员工联系电话.员工家庭住址.员工部门.员工职位2. 员工联系电话员工姓名.员工性别.员工年龄.员工家庭住址.员工部门.员工职位3. 员工联系电话员工部门员工职位3NF运输器1. 运输器编号（主键）运输器类型.出发地.目的地.装载时间.到达时间3NF路线信息1. 标签ID（主键）出发地.目的地.中转站1ID.中转站2ID.中转站3ID.中转站4ID3NF智慧标签1. 标签ID,订单号（主键）二维码.尺寸.重量2. 二维码尺寸.重量2NF员工登陆1. 员工登录名，员工编号员工登陆密码

6、3NF订单状态信息1. 订单号（主键）所在地.扫面时间.上一站.下一站.是否到达.是否正常3NF异常订单1. 订单号，标签ID(主键) 异常信息.异常时间.异常地点3NF中转站1. 中转站ID(主键) 中转站名.中转站地址.到达时间.出发时间2. 中转站地址中转站名2NF客户表3. 客户电话客户姓名.客户地址.客户邮编3NF三. 表结构设计l 订单l 客户l 智慧标签l 发件人l 收件人l 员工l 运输器l 货物l 员工登陆l 订单状态信息l 异常订单l 路线信息l 中转站l 收件人-订单l 发件人-订单l 运输器-员工l 智慧标签-员工l 智慧标签-中转站l 中转站-员工l 路线信息-中转站

7、l 路线信息-运输器四. 数据库表设计：将ER图转化为数据表五. 实验要求1.员工登陆 proc(username ,password分开判断并给出提示信息)createprocyg_denglu(yonghumingchar(20),koulingchar(20)asifexists(select*from员工登陆where员工登陆名=yonghumingand员工登陆密码=kouling)print登陆成功;elseifnotexists(select*from员工登陆where员工登陆名=yonghuming)print员工登陆名不存在;elseifexists(select*from员

8、工登陆where员工登陆名=yonghumingand员工登陆密码!=kouling)print员工登陆密码错误;execyg_dengluyonghuming=luchenyang,kouling=123456execyg_dengluyonghuming=wanger002,kouling=002execyg_dengluyonghuming=zhaoyi001,kouling=1234562.统计员工工作量 proc/view(全体员工和单个员工)统计全体员工工作量：createviewall_ygworkloadasselectcount(distinct订单号)as全体员工工作量fr

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

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

11、rupdateasifexists(select*fromdeleted,发件人where发件人.发件人姓名=deleted.发件人姓名and发件人.发件人电话=deleted.发件人电话)beginrollbackprint订单已生成,不能修改表中任何信息;end对收件人表：createtriggerlimit_收件人on收件人forupdateasifexists(select*fromdeleted,收件人where收件人.收件人姓名=deleted.收件人姓名and收件人.收件人电话=deleted.收件人电话)beginrollbackprint订单已生成,不能修改表中任何信息;en

12、d对订单号：createtriggerlimit_订单on订单forupdateasifexists(select*fromdeleted,订单where订单.订单号=deleted.订单号)beginrollbackprint订单已生成,不能修改表中任何信息;end尝试修改发件人表中信息，提示“订单已生成，不能修改表中任何信息”尝试修改收件人表中信息，提示“订单已生成，不能修改表中任何信息”尝试修改订单表中信息，提示“订单已生成，不能修改表中信息”6.一旦新建运单，就判断发件人是否在用户之中，若是则转用户表，显示用户信息，如不是，自动增加用户。 triggercreatetriggeradd

13、_useron发件人afterinsertasdeclare客户姓名varchar(30)declare客户电话varchar(20)declare客户地址varchar(40)declare客户邮编varchar(20)ifnotexists(select*frominserted,客户表whereinserted.发件人姓名=客户表.客户姓名andinserted.发件人电话=客户表.客户电话)beginselect客户姓名=inserted.发件人姓名,客户电话=inserted.发件人电话,客户地址=inserted.发件人地址,客户邮编=inserted.发件人邮编frominser

14、tedinsertinto客户表(客户姓名,客户电话,客户地址,客户邮编)values(客户姓名,客户电话,客户地址,客户邮编)endelseifexists(select*frominserted,客户表whereinserted.发件人姓名=客户表.客户姓名andinserted.发件人电话=客户表.客户电话)beginprint客户信息：select客户姓名,客户电话,客户地址,客户邮编from客户表,insertedwhereinserted.发件人姓名=客户表.客户姓名andinserted.发件人电话=客户表.客户电话end发件人表_订单客户表现在，我往发件人订单表中插入数据ins

15、ertinto发件人values(王五,湖南省常德市,642801)insertinto发件人_订单values(7492004,王五,)插入后的客户表自动添加了“王五”的数据现在我要插一条数据客户表中有，发件人表和发件人_订单表中没有。客户表中先插入赵六的数据insertinto发件人values(赵六,云南昆明,650500)insertinto发件人_订单values(7492005,赵六,)可以看到，显示出了客户表中赵六的信息。一. 实验小结卢晨阳：这次数据库的大作业项目中，我担任本组的组长，主要负责统筹整个小组的实验进度，实体的设定以及存储过程的编写，数据库具体操作，还有实验报告的撰

16、写。即使是上了一学期的数据库课程，但这些都是针对数据库的一些基本操作，没有深入了解到数据库的内部世界。SQL Sever的操作和编程在本次项目中都让我得到了很好的锻炼。在当初定实体时，对每一个可能要实现功能的内容要进行详细的考虑，每一个实体之间的关系是一对一，还是多对一，还是多对多，实体这样定下之后，管理上会不会出现问题，信息存储会不会有冗余，考虑问题需要广泛且深刻。同样，在存储过程的编写过程中，一些在平常实验中没有遇到过的问题频频出现，比如，数据插入时，建立外键数据的约束，数据的统一性，存储过程的使用，还有在其他系统中如何连接数据库，不同连接方法的差别之处等，解决也是经历了一些波折，自然，通

17、过自己的学习来解决遇到的问题，其中的收获不言而喻。再次通过自己实践，用PowerDesigner将画好的逻辑E-R图生成物理E-R图，并转入真实数据库中，了解生成原理也是一大收获。而通过这次团队合作，作为组长的我需要担负起这个责任，根据每位成员的实力，把他们放在相应的位置上，平衡好内部各组员之间的任务，在成员之间出现意见分歧时要尽可能好的处理好，让内部和谐共处，发挥团队合作的精神。总的来说，通过这次项目实践，是我锻炼了不少，重要的是让我对数据库的操作和应用有了更近一步的提升以及作为组长，如何合理分配组员们的任务，从而使得项目完成。段云涛：这次我在数据库大作业里，主要负责的是数据库ER图的设计和

18、绘制，以及物理图的绘制，数据库表格脚本生成和数据库的建立，以及实验报告的数据库表格属性列整理说明部分。通过这次数据库大作业，收获颇多。第一，我更加清楚地知道了数据库设计的流程和方式，以及再设计过程中要需要注意的，比如确定实体时，不必确定外键，只用考虑到两个实体之间的关系是一对多，多对一，还是一对一，或是多对多的。第二，我学会了power design的基本使用，以及用它绘制ER图，并学会运用cdm生成pdm再到生成SQL脚本的基本方法，了解了其生成的工作原理。第三，也是最重要的，就是积攒了更多关于数据库设计的经验，明白了一些之前在课程学习中不懂的问题，比如，外键与主键的名字可以不相同，但是必须

19、要保持数据类型及长度一样。一个表和另一个表之间的关系可以不止一个等等。数据库设计是基于经验的，因此，积攒经验十分重要的，并且，详细的了解客户的需求也很重要。第四，通过这次大作业，锻炼了我的团队协作能力，特别是从其他小组成员里看到了处理当分歧发生如何处理等等。第五，通过看其他小组成员写的触发器和其他数据库对象，我更加进一步明白了触发器以及其他数据库对象的作用，以及它们能够应用于哪些情况。总之，这次数据库大作业，让我增加了对数据库管理系统的了解和应用，积攒了数据库的设计经验，锻炼我的团队协作能力。附录.生成的数据库脚本/*=*/* DBMS name: Microsoft SQL Server 2

20、012 */* Created on: 2014/4/17 11:49:55 */*=*/if exists (select 1fromsys.sysreferences r join sys.sysobjects 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_路线信息goif exists (select 1fromsys.sysr

21、eferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(员工) and o.name = FK_员工_REFERENCE_智慧标签)alter table 员工 drop constraint FK_员工_REFERENCE_智慧标签goif exists (select 1fromsys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.f

22、keyid = object_id(智慧标签) and o.name = FK_智慧标签_REFERENCE_货物)alter table 智慧标签 drop constraint FK_智慧标签_REFERENCE_货物goif exists (select 1fromsys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(订单) and o.name = FK_订单_REFERENCE_发件人)alter table 订单 drop c

23、onstraint FK_订单_REFERENCE_发件人goif exists (select 1fromsys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(订单) and o.name = FK_订单_REFERENCE_收件人)alter table 订单 drop constraint FK_订单_REFERENCE_收件人goif exists (select 1fromsys.sysreferences r join sys

24、.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(订单状态信息) and o.name = FK_订单状态信息_REFERENCE_订单)alter table 订单状态信息 drop constraint FK_订单状态信息_REFERENCE_订单goif exists (select 1fromsys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid =

25、object_id(货物) and o.name = FK_货物_REFERENCE_订单)alter table 货物 drop constraint FK_货物_REFERENCE_订单goif exists (select 1fromsys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(路线信息) and o.name = FK_路线信息_REFERENCE_智慧标签)alter table 路线信息 drop constraint

26、 FK_路线信息_REFERENCE_智慧标签goif exists (select 1fromsys.sysreferences r join sys.sysobjects 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_路线信息goif exists (select 1from sysindexes where id = o

27、bject_id(中转站)and name = Relationship_9_FKandindid 0andindid 0andindid 0andindid 0andindid 0andindid 0andindid 0andindid 0andindid 0andindid 255) drop index 运输途径.Relationship_8_FKgoif exists (select 1from sysobjects where id = object_id(运输途径)and type = U) drop table 运输途径go/*=*/* Table: 中转站 */*=*/crea

28、te table 中转站 (中转站编号varchar(10) not null,标签ID varchar(20) not null,中转站名varchar(30) not null,中转站地址varchar(40) not null,到达时间2 datetime not null,出发时间datetime not null, constraint PK_中转站 primary key nonclustered (中转站编号)go/*=*/* Index: Relationship_9_FK */*=*/create index Relationship_9_FK on 中转站 (标签ID AS

29、C)go/*=*/* Table: 发件人 */*=*/create table 发件人 (发件人姓名varchar(30) not null,发件人电话varchar(20) not null,发件人地址varchar(40) not null,发件人邮编int not null, constraint PK_发件人 primary key nonclustered (发件人姓名, 发件人电话)go/*=*/* Table: 员工 */*=*/create table 员工 (员工编号int not null,标签ID varchar(20) not null,员工姓名varchar(30)

30、 not null,员工性别 char(4) not null,年龄int not null,联系电话varchar(20) not null,家庭住址varchar(40) not null,部门varchar(20) not null,职位varchar(20) not null, constraint PK_员工 primary key nonclustered (员工编号)go/*=*/* Index: Reference_9_FK */*=*/create index Reference_9_FK on 员工 (标签ID ASC)go/*=*/* Table: 异常订单 */*=*/

31、create table 异常订单 (物流号varchar(20) not null,异常信息varchar(60) not null,异常时间datetime not null,异常地点varchar(40) not null, constraint PK_异常订单 primary key nonclustered (物流号)go/*=*/* Table: 收件人 */*=*/create table 收件人 (收件人姓名varchar(30) not null,收件人电话varchar(20) not null,收件人地址varchar(40) not null,收件人邮编int not

32、null, constraint PK_收件人 primary key nonclustered (收件人姓名, 收件人电话)go/*=*/* Table: 智慧标签 */*=*/create table 智慧标签 (标签ID2 varchar(20) not null,货物_物流号varchar(20) not null,物流号3 varchar(20) not null,二维码 char(10) not null,尺寸2 varchar(30) not null,重量2 float not null, constraint PK_智慧标签 primary key nonclustered

33、(标签ID2)go/*=*/* Index: Reference_5_FK */*=*/create index Reference_5_FK on 智慧标签 (货物_物流号 ASC)go/*=*/* Table: 订单 */*=*/create table 订单 (物流号4 varchar(20) not null,收件人姓名varchar(30) not null,收件人电话varchar(20) not null,发件人姓名varchar(30) not null,发件人电话varchar(20) not null,类型2 varchar(20) not null,出发地2 varcha

34、r(40) not null,目的地2 varchar(40) not null,费用 money not null,订单生成时间datetime not null, constraint PK_订单 primary key nonclustered (物流号4)go/*=*/* Index: Reference_1_FK */*=*/create index Reference_1_FK on 订单 (发件人姓名 ASC,发件人电话 ASC)go/*=*/* Index: Reference_2_FK */*=*/create index Reference_2_FK on 订单 (收件人姓

35、名 ASC,收件人电话 ASC)go/*=*/* Table: 订单状态信息 */*=*/create table 订单状态信息 (物流号5 varchar(20) not null,标签ID3 varchar(20) not null,订单_物流号varchar(20) not null,所在地varchar(40) not null,扫描时间datetime not null,上一站varchar(40) not null,下一站varchar(40) not null,是否到达varchar(6) not null,是否正常varchar(6) not null, constraint

36、PK_订单状态信息 primary key nonclustered (物流号5, 标签ID3)go/*=*/* Index: Reference_3_FK */*=*/create index Reference_3_FK on 订单状态信息 (订单_物流号 ASC)go/*=*/* Table: 货物 */*=*/create table 货物 (物流号varchar(20) not null,订单_物流号varchar(20) not null,类型varchar(20) not null,尺寸varchar(30) not null,重量 float not null, constra

37、int PK_货物 primary key nonclustered (物流号)go/*=*/* Index: Reference_4_FK */*=*/create index Reference_4_FK on 货物 (订单_物流号 ASC)go/*=*/* Table: 路线信息 */*=*/create table 路线信息 (标签ID varchar(20) not null,智慧标_标签ID varchar(20) not null,出发地3 varchar(40) not null,中转站1id varchar(10) not null,中转站2id varchar(10) not null,中转站3id varchar(10) not null,中转站4Id varchar(10) not null,目的地varchar(40) not null, constraint PK_路线信息 primary key nonclustered (标签ID)go/*=*/* Index: Reference_6