汽车公司管理系统数据库设计.doc

题 目：汽车公司管理系统数据库设计 姓 名： 第 四 组 专 业： 08机制5班 指导老师： 周志艳 完成日期： 2011年7月8日 目 录1. 系统需求分析阶段21.1 引言21.2 目标与任务21.2.1 需求分析阶段的目标21.2.2 需求分析阶段的任务21.2.3 需求分析阶段成果42. 概念设计阶段92.1 引言92.2 概念模型设计92.3 新系统流程133逻辑设计阶段143.1逻辑设计的任务和目标143.2数据组织143.2.1将E-R图转换为关系模型143.2.2模型优化153.2.3数据库模式定义153.2.4用户子模式设计163.3数据处理174物理设计阶段184.1物理设计阶段的目标与任务184.2数据存储方面184.确定数据库的存储结构194.3.1 数据存放技巧194.3.2 确定数据的存放位置205数据库实施阶段205.1建立数据库、数据表、视图、索引205.1.1 建立数据库205.1.2 建立数据表205.1.3 建立视图245.1.4 建立索引285.2数据入库285.3创建各个功能的存储过程296系统调试和测试307心得31附录1 数据库逻辑结构定义32附录2 存储过程定义37附录3 数据查看和存储过程功能的验证411. 系统需求分析阶段1.1 引言随着经济的发展和居民收入的不断提高，越来越多的人想拥有一辆自己的汽车，加之现在汽车行业的不断发展，汽车品牌和汽车车系的不断涌现，一个汽车公司往往都经营不同车系或者不同品牌的车，现在我们课程设计主要针对一个公司如何处理好顾客、员工、仓库、车等等实体之间的关系，使一个汽车公司利用数据库进行规范性地处理经营中所遇到的各种问题。1.2 目标与任务1.2.1 需求分析阶段的目标（1）了解目前汽车公司的现状以及SQL Server 2005的功能和特点。（2）通过走访学校临近的汽车4S店和汽车销售公司来了解其工作业务流程，并记录和处理相关的数据。（3）后期进行网上查询和数据的整合。1.2.2 需求分析阶段的任务 （1）处理对象：系统要处理的对象包括仓库基本信息、仓库管理员基本信息、车基本信息、订单基本信息、发动机基本信息、供应商基本信息、客户基本信息、销售员基本信息等八个方面，各个对象包括信息如下所示（详细的数据见于数据字典）：1仓库基本信息(storehouse)：包括仓库编号、仓库名称、仓管员名字、仓库联系电话、库存数量、仓库地址信息等方面，这样可以更好地了解仓库的运作。 2仓库管理员基本信息(WareAdmi)：包括 仓库管理员工作编号、仓库管理员姓名、仓库管理员性别、仓库管理员出生日期、仓库管理员所在部门、仓库管理员的籍贯、仓库管理员电话、仓库管理员的工资、仓库管理员的学历、仓库管理员任职日期、仓库管理员的业绩、仓库管理员的职称、仓库管理员管理的仓库号码信息等方面，可以方便仓库管理员信息的查询和更新； 3发动机基本信息(Power)：发动机基本信息包括发动机动力类型、发动机增压类型、发动机排量、发动机气门总数、发动机最大功率、发动机最大扭矩、发动机排放标准、发动机供油方式、发动机缸体材料、发动机技术信息等方面，每个数据结构中的数据项见数据字典，用于发动机信息的更新与维护 4客户基本信息(customers)：包括 客户姓名、客户编号、客户性别、客户联系电话、客户住址、客户地址邮编、客户月收入、客户公司单位、客户年龄、客户学历等数据项，可以方便销售人员对客户信息查询及更改；5供应商基本信息(supplier)：包括 供应商名称、供应商电话、供应商官网、供应商注册资金、供应商注册地、供应商生产能力、供应商股东结构、供应商生产场地、供应商产品、供应商生产设备等方面的信息； 6销售员基本信息(Sales)：销售员信息包括销售员工作编号、销售员姓名、销售员性别、销售员出生日期、销售员所在部门、销售员的籍贯、销售员电话、销售员的工资、销售员的学历、销售员任职日期、销售员的业绩、销售员的职称等方面信息。7 车基本信息(car)：车信息包括汽车编号、汽车速度、汽车刹车时间、汽车加速时间、汽车油耗、汽车转弯角速度、汽车载重、汽车总质量、汽车长度、汽车宽度、汽车高度、汽车轴距、汽车油箱容量、汽车气门总数、汽车结构、汽车颜色、汽车轴载质量、汽车轮距、汽车前悬、汽车后悬、汽车离地间隙等多方面的信息，主要是让客户更加深入地了解所需汽车的性能。8订单基本信息(order)：订单信息包括订单编号、下订时间、交货时间、客户编号、汽车编号、销售员工作编号、付款方式、数量、金额等信息，主要是让公司了解销售的具体数据。（2）处理功能要求系统主要完成一下几个功能：1客户基本信息查询与修改；2车信息查询与更新；3销售人员宿舍信息的查询与信息更新；4供应商基本信息的查询和修改；5订单基本信息查询和修改；6发动机基本性能的查询以及修改7仓库基本信息的查询以及修改8仓库管理员基本信息的更新以及查询和修改（3）安全性和完整性要求安全性先通过视图机制，不同的用户只能访问系统授权的视图，这样可提供系统数据一定程度上的安全性，再通过用户授权机制，欲用户登陆来识别用户级别，根据这个级别来分配用户权限，达到数据更高层次的安全保密功能。完整性要求用于描述仓库基本信息、仓库管理员基本信息、供应商基本信息、车基本信息、销售人员基本信息、订单基本信息、发动机基本信息、客户基本信息中数据项能否为null，以及一些用户自定义完整性（符合实际要求），详细完整性要求见于系统的逻辑设计阶段。1.2.3需求分析阶段成果（1）汽车公司管理系统业务流程图一、客户查询业务流程图二、客户购买汽车业务流程图三、公司采购汽车业务流程图四、仓库业务流程图五、车辆管理业务流程图（2）汽车公司管理系统数据流程图顶层数据车流程图：第一层数据流程图：第二层订单数据流程图第二层采购数据流程图六、数据字典（a）数据项：系统涉及的数据项有90项数据项编号数据项名数据项含义与其他数据项关系存储结构别名DI-1CusName客户姓名Char(5)姓名DI-2CusNum客户编号int编号DI-3CusSex客户性别Char(1)性别DI-4CusTel客户联系电话int电话DI-5CusAdd客户住址char住址DI-6CusPostcode客户地址邮编int邮编DI-7CusSalary客户月收入int工资DI-8CusCompany客户公司单位char单位DI-9CusAge客户年龄int年龄DI-10CusEdu客户学历Char(2)学历DI-11suppliername供应商名称char(20)名称DI-12suppliertel供应商电话int电话DI-13supplierweb供应商官网char网址DI-14suppliermoney供应商注册资金int注册资金DI-15supplierplace供应商注册地char(20)注册地DI-16supplierout供应商生产能力char(2)产量DI-17supplierstr供应商股东结构char(5)股东结构DI-18supplieradd供应商生产场地char(20)生产地DI-19supplierproduce供应商产品char(20)产品DI-20supplierequipment供应商生产设备char(20)设备DI-21Powertype发动机动力类型Char(5)动力DI-22Pretype发动机增压类型Char(5)增压DI-23Emission发动机排量Float排量DI-24Valvetotal发动机气门总数Int气门DI-25Maxpower发动机最大功率Int功率DI-26Maxtorque发动机最大扭矩Float扭矩DI-27Emistandards发动机排放标准Char排放DI-28Injectionway发动机供油方式Char(10)供油DI-29Cylmaterial发动机缸体材料Char(5)缸体DI-30Entechnologe发动机技术char技术DI-31OrdNum订单编号int编号DI-32OrdTime下订时间datetime下订时间DI-33OrdDealTime交货时间datetime交货时间DI-34CusNum客户编号int编号DI-35carnum汽车编号int编号DI-36SalesID销售员工作编号int工号DI-37OrdPayWay付款方式Char(10)付款DI-38OrdCarquantity数量int数量DI-39OrdMoney金额int金额DI-40WareAdminID仓库管理员工作编号int工号DI-41WareAdminName仓库管理员姓名char(10)姓名DI-42WareAdminSex仓库管理员性别char(2)性别DI-43WareAdminBirthday仓库管理员出生日期datetime日期DI-44WareAdminDepart仓库管理员所在部门char(10)部门DI-45WareAdminPlace仓库管理员的籍贯char(10)籍贯DI-46WareAdminPhone仓库管理员电话int电话DI-47WareAdminPay仓库管理员的工资money工资DI-48WareAdminEducation仓库管理员的学历char(10)学历DI-49WareAdminWork仓库管理员任职日期datetime日期DI-50WareAdminResults仓库管理员的业绩Char5)业绩DI-51WareAdminPosition仓库管理员的职称char(10)职称DI-1WareAdminNum管理员管理的仓库号码int仓库号DI-52Storehousenum仓库编号int编号DI-53Storehousename仓库名称Char(20)名称DI-54Storehousemann管理员名字Char（5）库管员DI-55Storehousetel联系电话int联系电话DI-56Inventoryquantit库存数量int库存量DI-57Storehouseaddre仓库地址char仓库地址DI-58SalesID销售员工作编号int工号DI-59SalesName销售员姓名char(10)姓名DI-60SalesSex销售员性别char(10)性别DI-61SalesBirthday销售员出生日期Datetime出生日期DI-62SalesDepart销售员所在部门char(10)部门DI-63SalesPlace销售员的籍贯char(10)籍贯DI-64SalesPhone销售员电话char(20)电话DI-65SalesPay销售员的工资money工资DI-66SalesEducation销售员的学历char(10)学历DI-67SalesWork销售员任职日期datetime任职日期DI-68SalesResults销售员的业绩char(10)业绩DI-69SalesPosition销售员的职称char(10)职称DI-70carnum汽车编号int编号DI-71CarSpeed汽车速度Int车速DI-72CarBrake汽车刹车时间datetime刹车DI-73CarSpeedup汽车加速时间datetime加速DI-74CarOil汽车油耗Float油耗DI-75CarSwerve汽车转弯角速度Float转角速DI-76CarLoad汽车载重int车载DI-77CarWeight汽车总质量int车重DI-78CarLength汽车长度int车长DI-79CarWidth汽车宽度int车宽DI-80CarHeight汽车高度int车高DI-81CarDis汽车轴距int轴距DI-82CarOilbox汽车油箱容量int油箱DI-83CarAirNo汽车气门总数int气门DI-84CarStr汽车结构Char（5）结构DI-85CarColour汽车颜色char(2)车色DI-86Caraxleload汽车轴载质量int轴载DI-87CarTread汽车轮距int轮距DI-88CarFOve汽车前悬int前悬DI-89CarROve汽车后悬int后悬DI-90CarRClr汽车离地间隙int地距（b）数据结构：表1.2 数据结构列表数据结构编号数据结构名数据结构含义组成DS-1Car汽车信息Carnum, CarSpeed, CarBrake, CarSpeedup, CarOil, CarSwerve, CarLoad, CarWeight, CarLength, CarWidth, CarHeight, CarDis, CarOilbox, CarAirNo, CarStr, CarColour, Caraxleload, CarTread, CarFOve, CarROve, CarRClrDS-2Work仓库管理信息Storehousenum, WareAdminIDDS-3WareAdmin仓库管理员信息WareAdminID, WareAdminName, WareAdminSex, WareAdminBirthday, WareAdminDepart, WareAdminPlace, WareAdminPhone, WareAdminPay, WareAdminEducation, WareAdminWork, WareAdminResults, WareAdminPositionDS-4Sales销售员信息SalesID, SalesName, SalesSex, SalesBirthday, SalesDepart, SalesPlace, SalesPhone, SalesPay, SalesEducation, SalesWork, SalesResults, SalesPositionDS-5Store仓库库存信息Storehousenum, carnum, intime, outtimeDS-6Storehouse仓库信息Storehousenum, Storehousename, Storehousemanna, Storehousetel, Inventoryquantity, StorehouseaddressDS-7Consist发动机与汽车匹配信息Enginenum, carnumDS-8Ord汽车订单信息OrdNum, OrdTime, OrdDealTime, CusNum, carnum, SalesID, OrdPayWay, OrdCarquantity, OrdMoneyDS-9Supply发动机订单信息Supnum, Supquantity, Supprice, Suptime, Suppayway, suppliernum, EnginenumDS-10Custom客户信息CusName, CusNum, CusSex, CusTel, CusAdd, CusPostcode, CusSalary, CusCompany, CusAge, CusEduDS-11Purchase汽车采购信息Pchnum, Pchquantity, Pchprice, Pchtime, Pchpayway, WareAdminID, suppliernum carnum,DS-12Engine发动机信息Enginenum, Powertype, Pretype, Emission, Valvetotal, Maxpower, Maxtorque, Emistandards, Injectionway , Cylmaterial, EntechnologeDS-13Supplier供应商信息Suppliernum, suppliername, suppliertel, supplierweb, suppliermoney, supplierplace supplierout,supplierstr, supplieradd, supplierproduce, supplierequipment2. 概念设计阶段2.1 引言概念设计阶段主要是将需求分析阶段得到的用户需求抽象为信息结构(概念模型)的过程,它是整个数据库设计的关键，包括概念模型设计和新系统流程两个阶段。2.2 概念模型设计（1）根据不同的对象，分别画出分ER图：（a）从数据流程图图抽象出的订单ER图：订单负责销售员管理构成订购车客户mnnnn11订单ER图1（b）从数据流程图图抽象出的采购ER图：采购供应商车仓库管理员交货日期单价n1采购ER图交货地点1采购单编号数量（c）从数据流程图图抽象出的汽车ER图：供应商供应车构成发动机供应汽车信息ER图nnn111（d）从数据流程图图抽象出的库存ER图：车库存储工作管理员管理车构成发动机11n1nn1库存ER图1n（2）各ER图中每个实体的属性如下所示：汽车：Car（Carnum, CarSpeed, CarBrake, CarSpeedup, CarOil, CarSwerve, CarLoad, CarWeight, CarLength, CarWidth, CarHeight, CarDis, CarOilbox, CarAirNo, CarStr, CarColour, Caraxleload, CarTread, CarFOve, CarROve, CarRClr）;仓库管理员：WareAdmin (WareAdminID，WareAdminName，WareAdminSex，WareAdminBirthday，WareAdminDepart，WareAdminPlace，WareAdminPhone，WareAdminPay，WareAdminEducation，WareAdminWork，WareAdminResults，WareAdminNum，WareAdminPosition);仓库：Storehouse（Storehousenum，Storehousename，Storehousemanna，Storehousetel，Inventoryquantity，Storehouseaddress）;订单：Ord（OrdNum，OrdTime，OrdDealTime，CusNum，carnum，SalesID，OrdPayWay，OrdCarquantity，OrdMoney）;发动机：Consist（Powertype，Pretype，Emission，Valvetotal，Maxpower，Maxtorque，Emistandards，Injectionway，Cylmaterial，Entechnologe）; 供应商：Supplier（Suppliername，Suppliertel，Supplierweb，Suppliermoney，Supplierplace，Supplierout，Supplierstr，Supplieradd，Supplierproduce，Supplierequipment）; 客户：Custom(CusName，CusNum，CusSex，CusTel，CusAdd，CusPostcode，CusSalary，CusCompany，CusAge,CusEdu); 销售员：Sales(SalesID，SalesName，SalesSex，SalesBirthday，SalesDepart，SalesPlace，SalesPhone，SalesPay，SalesEducation，SalesWork，SalesResults，SalesPosition);（3）合并各分图,消除属性冲突、命名冲突、结构冲突等三类冲突，得到初步E-R图，再消除不必要冗余，得到的基本E-R图如下所示：供应商采购车供应发动机构成构成存储仓库工作仓库管理员管理订单订购处理销售员客户管理1n1nnmnn11n=nnn11n1mn112.3 新系统流程新系统流程图：用户登录是否成功顾客员工员工信息顾客信息汽车信息仓库信息员工基本信息顾客基本信息汽车基本信息仓库基本信息管理层人员员工重要信息顾客重要信息汽车重要信息仓库重要信息YNYNY3. 逻辑设计阶段3.1逻辑设计的任务和目标以上的概念设计阶段是独立于任何一种数据模型的，但是逻辑设计阶段就与选用的DBMS产品发生关系了，系统逻辑设计的任务就是将概念设计阶段设计好的基本E-R图转换为选用DBMS产品所支持的数据模型相符合的逻辑结构。具体内容包括数据组织（将E-R图转换成关系模型、模型优化、数据库模式定义、用户子模式设计）、数据处理（画出系统功能模块图）两大任务。3.2数据组织3.2.1将E-R图转换为关系模型在上面的总E-R图中，实体供应商、发动机、车、仓库之间的关系有供应关系、构成有关系、存储关系、工作关系，而这些关系都包含数据结构，所以将其转化成独立关系模式；而供应商、车、仓库管理员之间又是多元联系，所以其也是必须转化成独立关系模式；此外，客户与订单、销售员与订单、车与订单之间皆是一对多关系，可以将其之间的联系与n端实体（即订单）合并，实现模型的优化。具体的基本E-R图向关系模型的转化如下：供应商：gongyingshang（suppliernum，suppliername，suppliertel，supplierweb，suppliermoney，supplierplace，supplierout，supplierstr，supplieradd，supplierproduce，supplierequipment）；发动机：fadongji（Enginenum，Powertype，Pretype，Emission，Valvetotal，Maxpower，Maxtorque，Emistandards，Injectionway，Cylmaterial，Entechnologe）；车：Car（CarNum，CarSpeed，CarBrake，CarSpeedup，CarOil，CarSwerve，CarWeight，CarLength，CarWidth，CarHeight，CarDis，CarOilbox，CarAirNo，CarStr，CarColour，CarTread，CarRClr，Prise）；仓库：Storehouse（Storehousenum，Storehousename，Storehousemanna，Storehousetel，Inventoryquantity，Storehouseaddress）；仓库管理员：WareAdmin（WareAdminID，WareAdminName，WareAdminSex，WareAdminBirthday，WareAdminDepart，WareAdminPlace，WareAdminPhone，WareAdminPay，WareAdminEducation，WareAdminWork，WareAdminResults，WareAdminPosition，）；客户：Custom（CusNum，CusName，CusSex，CusTel，CusAdd，CusPostcode，CusSalary，CusCompany，CusAge，CusEdu）；销售员：Sales（SalesID，SalesName，SalesSex，SalesBirthday，SalesDepart，SalesPlace，SalesPhone，SalesPay，SalesEducation，SalesWork，SalesResults，SalesPosition）；供应：Supply（Supnum，Supquantity，Supprice，Suptime，Suppayway，Suppliernum，Enginenum）；构成：Consist（Enginenum，carnum）；存储：Store（Storehousenum，carnum，Amount）；工作：Work（WareAdminID，Storehousenum）；采购：Purchase（Pchnum，Pchquantity，Pchprice，Pchtime，Pchpayway，WareAdminID，suppliernum，carnum）；订单：Ord（OrdNum，OrdTime，OrdDealTime，CusNum，CarNum，SalesID，OrdPayWay，OrdCarquantity，OrdMoney）；（注：标有直线下划线的为主属性，标有波浪线下划线的是外键属性，主属性与外键属性一起构成主码）3.2.2模型优化关系模式gongyingshang，fadongji，Car，Storehouse，WareAdmin，ustom，Sales，Supply，Consist，Store，Work，Purchase，Ord不存在非主属性对主属性的部分函数依赖，也不存在传递函数依赖，已经达到了3NF。3.2.3数据库模式定义表3.2.3数据库模式定义表编号逻辑结构(基本表)定义完整性和安全性T1gongyingshang（详见附录11）（详见附录11）T2fadongji（详见附录12）（详见附录12）T3Car（详见附录13）（详见附录13）T4Storehouse（详见附录14）（详见附录14）T5WareAdmin（详见附录15）（详见附录15）T6ustom（详见附录16）（详见附录16）T7Sales（详见附录17）（详见附录17）T8Supply（详见附录18）（详见附录18）T9Consist（详见附录19）（详见附录19）T10Store（详见附录110）（详见附录110）T11Work（详见附录111）（详见附录111）T12Purchase（详见附录112）（详见附录112）T13Ord（详见附录113）（详见附录113）3.2.4用户子模式设计表3.2.4用户子模式设计(View)列表编号用户子模式(View)作用(共性：提供数据保密和安全保护机制)V1Car_view1不能让客户看到车的全部信息V2Car_view2车的全部信息工作人员可以看到V3Custom_view2不能让工作人员看到客户过多的信息V4Purchase_view2不让顾客看到底价V5WareAdmin_view1不能让客户看到仓库管理员的工资等重要信息V6Sales_view1不能让客户看到销售员的工资等重要信息V7Sales_view2不能让工作人员看到其他销售员的工资等重要信息V8WareAdmin_view2不能让工作人员看到其他仓库管理员的工资等重要信息V9gongyingshang_view1只能让客户查询供应商最基本的信息V10gongyingshang_view2只能让工作有员查询供应商不是重要机密的信息V11fadongji_view1为了以后使用方便，为发动机建一个客户的视图V12fadongji_view2为了以后使用方便，为发动机建一个工作人员的视图V13Storehouse_view1为了以后使用方便，为车库建一个客户的视图V14Storehouse_view2为了以后使用方便，为车库建一个工作人员的视图V15Ord_view2订单给工作人员的视图3.3数据处理系统功能模块图：公司管理系统查询比较车信息系统车的库存信息系统顾客订单信息系统员工管理信息系统业绩查询系统采购信息系统4物理设计阶段4.1物理设计阶段的目标与任务数据库的物理设计就是为逻辑数据模型选取一个最合适应用要求的物理结构的过程，在这个阶段中要完成两大任务：（1）确定数据库的物理结构，在关系数据库中主要是存取方法和存储结构；（2）对物理结构进行评价，评价的重点是时间和空间效率。4.2确定关系模型的存取方法数据库系统是多用户共享的系统，对同一个关系要建立多条存取路径才能满足多用户的多种应用要求。为数据库中各基本表建立的索引如下：1. 由于基本表Car的主码CarNum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；2. 由于基本表Consist的主码Enginenum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；3. 由于基本表Custom的主码CusNum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；4. 由于基本表fadongji的主码Enginenum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；5. 由于基本表gongyingshang的主码suppliernum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；6. 由于基本表Ord的主码OrdNum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；7. 由于基本表Purchase 的主码Pchnum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；8. 由于基本表Sales的主码SalesID经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；9. 由于基本表Store的主码Storehousenum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；10. 由于基本表Storehouse的主码Storehousenum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；11. 由于基本表Supply的主码Supnum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；12. 由于基本表WareAdmin的主码WareAdminID经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索引；13. 由于基本表Work的主码Storehousenum经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在这个属性上建立唯一性索