物流运输公司数据库设计.doc

内蒙古科技大学课程设计内蒙古科技大学本科生课程设计论文题 目：物流运输公司数据库设计学生姓名：学 号：专 业：计算机班 级：13级4班指导教师： 2015 年 12 月 30 日 内蒙古科技大学课程设计任务书课程名称数据库原理课程设计设计题目物流运输公司数据库设计指导教师时间第17，18周一、教学要求1、培养学生运用所学课程数据库原理的理论知识和技能以及对数据库组织、管理和使用能力。2、掌握数据库应用系统的整个设计过程（包括需求分析、概念结构设计、逻辑结构设计与物理设计与实施）二、设计要求及成果根据数据库应用系统设计过程，通过需求分析和系统分析，进行数据库的概念结构设计、逻辑结构设计等工作：1、概念结构设计的ER图2、数据库逻辑结构设计是以表格的形式说明数据库中有几个表，每个表的表名以及每个表的各字段名，类型和约束（主、外键）；3、确定表之间的关联（一对一、一对多、多对多）4、运用数据库理论确定每个表至少属于3NF。5、编写课程设计说明书三、进度安排 需求分析（1天） ER模型设计（2天） 编写课程设计说明书和验收（2天）四、评分标准 1、数据库结构正确性（30%） 2、数据库结构复杂性，由题目等级决定（30%） 3、设计说明书完成的质量（30%） 4、出勤（10%） 5、成绩采用五级分制评定五、建议参考资料 1.SQL server 2012 从零开始学 清华大学出版社 编著：王英英、张少军、刘增杰 2.数据库系统教程 高等教育出版设 编著：施伯乐、丁宝康、汪卫摘要 随着时间的推移，数据库在各个领域都被广泛的运用。数据库技术已经成为现代信息技术的重要组成部分，是现代计算机信息系统和计算机应用系统的基础和核心。 我所做的是物流运输公司物数据库设计，目的是为了让物流运输公司更好的实行自动化管理，减少了投入的人力、精力，能让数据存储在计算机内，能够有组织的，统一管理公司的业务。我利用课本中的知识，通过需求分析，概念设计，逻辑设计，及数据库的实施和运行等阶段，为物流运输公司设计了一个小型数据库。关键词：SQL SERVER；数据库设计；物流运输公司目录摘要2目录3前言4第一章：系统分析及设计51.1 主要的需求51.2 数据库的E-R图51.2.1实体E-R模型设计51.2.2总E-R模型设计81.3 数据库的逻辑设计81.3.1基本表设计81.3.2数据约束分析11第二章：系统功能122.1 主要功能122.2 数据库的设计与实现132.2.1数据库的创建132.2.2数据表的创建132.2.3数据录入152.2.4基本查询172.2.5创建视图192.2.6 创建存储过程20第三章 结论21第四章 结束语21参考文献22 前言 这次课程设计就是对本学期所学的数据库原理及应用及SQL server 从零开始学的实践，这两门课既有较强的理论性，又有较强的实践性的专业基础课程，需要把理论知识和实际应用紧密结合起来。我的课设题目是“物流运输公司数据库设计”，通过对物流公司内部管理来实现数据库的功能。 这学期学习完数据库的理论知识，又学习了SQL语言的运用，最后用自己的电脑实践，用“物流运输公司数据库设计”来举例用理论来联系实践，了解并掌握了数据库管理系统的基本原理和数据库系统设计的方法，培养了我应用及设计数据库的能力。 通过亲身实践，我了解物流管理的结构，需要了解客户、公司、货物之间的关系，首先，它们被输入到数据库后，能够查询，修改和删除，然后通过建立键的关系，来建立表的联系，然后通过需求分析，了解了需求分析的过程和目的，建立数据字典，概念设计阶段要完成数据抽象与局部视图设计以及视图的集成。逻辑结构设计阶段要把E-R图转化为关系模式。最后是数据库的实施和运行。第一章：系统分析及设计1.1 主要的需求 物流运输公司A需要建立一个管理数据库存储以下信息：1.物流运输公司A中主要的实体有员工、订单、运单、车辆、客户。2.物流运输公司A有多名负责的不同工作的员工：货运员工和维修员工。3.每一笔订单包含订单编号、货物名称、送货日期、货物数量、客户编号。4.每一笔运单包含运单编号、订单编号、出车司机、签收日期、出车日期、回车日期。5.物流运输公司A还会为客户建立客户表，客户表包含客户编号、客户名称、客户性别、客户地址、联系电话。6.物流运输公司A会建立一个车辆表，包含：车辆编号、车牌号、车辆型号、最大载重、购买金钱。7.对于维修的车辆，还会建立一个维修记录表，记录维修编号、维修车牌号、开始维修时间、结束维修时间、维修费用、维修地点。1.2 数据库的E-R图1.2.1实体E-R模型设计1.客户E-R模型如图1.1所示。图1.1 客户E-R模型2.车辆E-R模型如图1.2所示。图1.2 车辆E-R模型3.订单信息E-R模型如图1.3所示图1.3 订单信息E-R模型4.运单信息E-R模型如图1.4所示。图1.4 运单信息E-R模型5. 维修员工E-R模型如图1.5所示图1.5 维修员工E-R模型6. 货运员工E-R模型如图1.6所示图1.6 货运员工E-R模型1.2.2总E-R模型设计物流运输系统总-图如图1.7所示。图1.7 总E-R模型1.3 数据库的逻辑设计1.3.1基本表设计关系设计包括转换后所得表和函数依赖关系，转换后如下：1.客户表如表1.1所示。表1.1 客户表数据项数据类型别名是否为空约束取值含义customer_idint客户编号Not null 主键customer_namevarchar(50)客户名称Not nullcustomer_phonevarchar(11)客户电话Not nullcustomer_sexchar(2)客户性别Not nullcustomer_adVarchar(20)客户地址Not null 2.车辆表如表1.2所示。表1.2 车辆表数据项数据类型别名是否为空约束取值含义car_idint车辆编号Not null主键car_licenceVarchar(20)车牌号Not null真实填写car_typeVarchar(50)车辆型号 nullcar_MaxWgtint最大载重Not nullcar_moneyvarchar(20)购买金钱 Null3.货运员工表如表1.3所示。表1.3 货运员工表数据项数据类型别名是否为空约束取值含义freightstaff_idint货运员工编号Not null主键freightstaff_namevarchar(20)货运员工姓名Not nullfreightstaff_phonevrachar(11)货运员工电话Not nullfreightstaff_sexchar(2)货运员工性别Not null4.维修员工表如表1.4所示。表1.4维修员工表数据项数据类型别名是否为空约束取值含义repairstaff_idint维修员工编号Not null主键repairstaff_nameVarchar(20)维修员工姓名Not nullrepairstaff_phoneVrachar(11)维修员工电话Not nullrepairstaff_sexchar(2)维修员工性别Not null 5.订单信息表如表1.5所示。表1.5 订单信息表数据项数据类型别名是否为空约束取值含义order_idint订单编号Not null主键gnamevarchar(20)货物名称Not nullgnumvarchar(20)货物数量nullsenddatedate送货日期NullsendcustomerVarchar(50)送货客户单位nullcustomer_idint客户编号null外键6.运单信息表如表1.6所示。表1.6 运单信息表数据项数据类型别名是否为空约束取值含义waybill_idint运单编号Not null主键order_idint订单编号Not nulldrivervarchar(20)出车司机Not nullrecivedatedate签收日期 nullchuchedatedate出车日期 nullhuichedatedate回车日期 null7.驾驶信息表如表1.7所示。表1.7 驾驶信息表数据项数据类型别名是否为空约束取值含义drive_idint驾驶编号Not null主键driverVarchar(20)驾驶司机Not nullcar_idint驾驶车辆编号Not null外键chuchedatedate出车日期 nullchucheluxianVarchar(20)出车路线 null 8.维修信息表如表1.8所示。表1.8 维修信息表数据项数据类型别名是否为空约束取值含义repair_idint维修编号Not null主键car_licenceVarchar(20)车牌号Not nullsongxiudatedate送修日期Not null检查约束songxiudatewanxiudatewanxiudatedate完修日期 nullrepairadressVarchar(50)维修地点 nullrepair-costint维修费用Not nullrepairstaff_idint维修员工编号Not null外键1.3.2数据约束分析1.客户信息表的主键是customer_id,每一个编号都是唯一的。2.货运员工信息表的主键是freightstaff_id,每一个编号都是唯一的。3.维修员工信息表的主键是repairstaff_id,每一个编号都是唯一的。4.驾驶信息表的主键是driver_id,每一个编号都是唯一的。5.维修信息表的主键是repair_id,每一个编号都是唯一的。6.客户的所有信息不允许为空。7.车辆信息表的主键是car_id,每一个车辆编号都是唯一的。8.订单表的主键是order_id,每一个订单编号都是唯一的。9.运单信息表的主键是waybill_id,每一个运单编号都是唯一的。10. 联系人电话(phone)不能为空值，取值范围为00000000000-99999999999，固定电话加地区号输入。11.订单一旦生成则无法删除。12.联系人姓名(contactname)不能为空值，不得超过20字。13.最大载重量(car_MaxWgt)不能为空值，数据为int。14.车牌号(car_licence)不能为空值，真实填写。15.车类型(car_type)不能为空值。第二章：系统功能2.1 主要功能货物运输管理系统功能：客户基本信息管理、员工基本信息管理、车辆基本信息管理、订单基本信息管理、运单基本信息管理。基础信息操作：员工信息、客户信息、车辆信息、运单订单信息的插入更新删除。数据查询：基本信息查询、按条件查询、多表信息查询。数据统计：详细统计订单的各项信息，统计维修记录中各个车辆的维修次数与维修费用之和。图2.1 系统功能图2.2 数据库的设计与实现2.2.1数据库的创建create database Transdbon primary( name= Transdb , filename=D:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATATransdb.mdf, size=5, maxsize=20, filegrowth= 1 )log on( name= Translog,filename=D:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATATranslog.ldf,size=2,maxsize=8,filegrowth=1)Go2.2.2数据表的创建-建立货运员工表create table freightstaff(freightstaff_id int primary key,freightstaff_name varchar(20)not null,freightstaff_sex char(2) not null ,freightstaff_phone varchar(11)not null);-建立维修员工表create table repairstaff(repairstaff_id int primary key,repairstaff_name varchar(20)not null,repairstaff_sex char(2) not null ,repairstaff_phone varchar(11)not null);-建立车辆信息表create table car(car_id int primary key,car_licence varchar(20)not null,car_type varchar(50),car_MaxWgt int not null,car_money varchar(20);-建立客户信息表create table customer(customer_id int primary key,customer_name varchar(20)not null,customer_sex char(2) not null ,customer_phone varchar(11)not null,customer_ad varchar(50)not null);-建立订单信息表create table dingdan(order_id int primary key not null,gname varchar(20) not null,gnum varchar(20),senddate date,customer_id int,sendcustomer varchar(20);-建立运单信息表create table waybill(waybill_id int primary key not null,order_id int not null,driver varchar(20) not null,recivedate date,chuchedate date,huichedate date,);-建立驾驶信息表create table drive(drive_id int primary key not null,driver varchar(20)not null,car_id int,chuchedate date,chucheluxian varchar(20);-建立维修信息表create table repair(repair_id int primary key not null,car_licence varchar(20)not null,songxiudate date,wanxiudate date,repairad varchar(50),repair_cost int,repairstaff_id int);2.2.3数据录入-插入货运员工数据insert freightstaff(freightstaff_id,freightstaff_name,freightstaff_phone,freightstaff_sex)values(1,李一一男),(2,张二二女),(3,王三三男),(4,赵四四男),(5,宋五五男),(6,江六六女)select *from freightstaff-插入维修员工数据Insert into repairstaff(repairstaff_id,repairstaff_name,repairstaff_phone,repairstaff_sex)values(1,李春男),(2,杨花女),(3,孙秋月男),(4,周满男),(5,张天男),(6,张繁星女)select *from repairstaff-插入客户信息数据insert into customer(customer_id,customer_name,customer_phone,customer_sex,customer_ad)values(1,张名男,包头市鑫达汽车销售有限公司),(2,王满女,包头长安福特有限公司),(3,孙天下男,包头日报印刷厂),(4,周心女,包头力帆摩托有限公司),(5,王想男,包头新光家具有限公司),(6,张事成女,包头远大纸业有限公司)select *from customer-插入车辆信息数据insert into car(car_id,car_licence,car_MaxWgt,car_money,car_type)values(1,蒙B-11111,21,190000,叉车),(2,蒙B-22222,31,200000,汽车),(3,蒙B-33333,21,210000,叉车),(4,蒙B-44444,31,220000,叉车),(5,蒙B-55555,19,230000,汽车),(6,蒙B-66666,19,240000,汽车)select *from car-插入驾驶信息数据insert into drive(drive_id,driver,car_id,chuchedate,chucheluxian)values(1,李一一,5,2015-04-25,包头-北京),(2,张二二,2,2015-07-15,包头-呼市),(3,宋五五,3,2015-05-25,包头-大同)select *from drive-插入维修信息数据Insert into repair(repair_id,car_licence,songxiudate,wanxiudate,repairad,repair_cost ,repairstaff_id)values(1,蒙B-11111,2015-03-25,2015-04-01,公司维修部,200,4),(3,蒙B-33333,2015-06-25,2015-07-01,公司维修部,100,1),(5,蒙B-55555,2015-03-25,2015-04-11,公司维修部,150,3)select *from repair-插入订单信息数据insert into dingdan(order_id,gname,gnum,senddate,sendcustomer,customer_id)values(111111111,双面纸,40件,2015-02-24,包头远大纸业有限公司,1),(222222221,床头,850个,2015-03-06,包头新光家具有限公司,3),(333333332,椅子,505把,2015-05-06,包头新光家具有限公司,2),(444444443,汽车,24辆,2015-04-06,包头长安福特有限公司,1)select *from dingdan-插入运单信息数据insert into waybill(waybill_id,order_id,driver,recivedate,chuchedate,huichedate)values(122222222,111111111,李一一,2015-05-27,2015-05-06,2015-06-08),(211111111,222222221,张二二,2015-07-27,2015-07-06,2015-08-08)select *from waybill2.2.4基本查询1.单表查询-单表查询select car_id,car_licencefrom car;select car_id,drive_id,driverfrom drive;select repair_id,car_licence,repairadfrom repair;2.多表查询-多表查询-子查询，表drive中存在chucheluxian=包头-北京，外层语句进行查询select *from carwhere exists(select car_id from drivewhere chucheluxian=包头-大同);-查询所有车辆总载重量select SUM(car_MaxWgt) as sum_maswgtfrom car;-查询出车路线为包头-北京的车辆编号、车牌号、购买金钱select car_id,car_licence,car_money from carwhere car_id=(select car_id from drive as drivewhere chucheluxian=包头-北京);-查询出车日期在2015-08-01前的车牌号,车辆编号和购买金钱select car_id,car_licence,car_money from carwhere car_id in(select car_id from drive as drivewhere chuchedate=2015-08-01);2.2.5创建视图-创建视图create view view_repairas select car_licence,songxiudate,wanxiudatefrom Transdb.dbo.repair;gouse Transdb;select *from view_repair;cre