数据库课程设计客户信息管理系统.doc

学 号： 2010131114课 程 设 计题 目客户信息管理系统学 院计算机科学与信息工程学院专 业计算机科学与技术班 级2010计算机1班学生姓名刘小燕指导教师康世瀛2012年6月10日重庆工商大学课程设计成绩评定表 学院： 计信学院 班级： 10计算机一班 学生姓名：刘小燕 学号： 2010131114项目分值优秀(100>x90)良好(90>x80)中等(80>x70)及格(70>x60)不及格(x<60)评分参考标准参考标准参考标准参考标准参考标准学习态度15学习态度认真，科学作风严谨，严格保证设计时间并按任务书中规定的进度开展各项工作学习态度比较认真，科学作风良好，能按期圆满完成任务书规定的任务学习态度尚好，遵守组织纪律，基本保证设计时间，按期完成各项工作学习态度尚可，能遵守组织纪律，能按期完成任务学习马虎，纪律涣散，工作作风不严谨,不能保证设计时间和进度技术水平与实际能力25设计合理、理论分析与计算正确，实验数据准确，有很强的实际动手能力、经济分析能力和计算机应用能力，文献查阅能力强、引用合理、调查调研非常合理、可信设计合理、理论分析与计算正确，实验数据比较准确，有较强的实际动手能力、经济分析能力和计算机应用能力，文献引用、调查调研比较合理、可信设计合理，理论分析与计算基本正确，实验数据比较准确，有一定的实际动手能力，主要文献引用、调查调研比较可信设计基本合理，理论分析与计算无大错，实验数据无大错设计不合理，理论分析与计算有原则错误，实验数据不可靠，实际动手能力差，文献引用、调查调研有较大的问题创新10有重大改进或独特见解，有一定实用价值有较大改进或新颖的见解，实用性尚可有一定改进或新的见解有一定见解观念陈旧论文(计算书、图纸)撰写质量50结构严谨，逻辑性强，层次清晰，语言准确，文字流畅，完全符合规范化要求，书写工整或用计算机打印成文；图纸非常工整、清晰结构合理，符合逻辑，文章层次分明，语言准确，文字流畅，符合规范化要求，书写工整或用计算机打印成文；图纸工整、清晰结构合理，层次较为分明，文理通顺，基本达到规范化要求，书写比较工整；图纸比较工整、清晰结构基本合理，逻辑基本清楚，文字尚通顺，勉强达到规范化要求；图纸比较工整内容空泛，结构混乱，文字表达不清，错别字较多，达不到规范化要求；图纸不工整或不清晰指导教师评定成绩：指导教师签名： 年 月 日目 录一、系统分析 (4)1系统目标(4)2系统功能分析(4)二、需求分析 (5)1、数据字典(5)2、数据流图(6)三、概念结构设计 (7)1、目的与任务(7)2、实体属性(7)3、E-R实体模型图(8)四、逻辑结构设计(9)五、物理结构设计(9)六、数据库实施 (16)1、建表插入记录结果(16)2、查询操作 (18)3、定义视图(26)4、定义游标 (30)5、定义存储过程(34)6、定义触发器(36)七、系统优缺点分析(38)八、参考文献(38)一 系统分析（一）系统目标：1、 系统的开发背景：为某从事商品销售的企业单位设计一个客户信息管理系统，方便单位部门更好的管理客户信息，通过对客户详细资料的深入分析，了解客户的爱好习性，从而从最大程度满足客户需求，从而提高企业竞争能力。2、 系统功能的基本要求：1) 增删改查客户信息；2) 可以进行总汇，反映每月、年的客户数量，；3) 可以统计各个客户的消费总金额，当累计消费超过某个数值时，可以让该客户成为VIP客户，在以后的交易中可以给予相关优惠政策；可以计算各个客户在每个月、每年的消费总金额，消费金额前几名企业部门给予客户相关奖励，通过此类优惠策略，构建客户与企业部门友好和谐的交易关系，同时也能达到双赢的目的。 3、数据库要求：在数据库中至少应该包含下列数据表：1) 销售员工表；2) 客户信息表；3) 商品库存表；4) 客户交易明细表；（二）系统功能分析：客户信息管理系统的主要功能是对客户信息进行录入、删除、修改、浏览、查找和排序等操作，用户可以对文件中存储的客户信息进行查找和浏览。具体功能如下： 新增客户：当有新客户时，向客户信息表里面添加一条新的记录。 修改客户：当有客户的信息如联系方式需要修改时，可更新客户资料。 盘存管理：系统的使用用户可以根据各自的权限查询各自权限范围的各种记录。二、需求分析（1）该企业单位的组织机构及各部门的业务活动情况 采购部：负责批发采购商品，并把采购发票交予商品入库员，负责及时准确更新商品库。 商品验收员：负责核对商品采购订单，核对无误后产品入库。 销售员工：负责与客户进行交易，记录与客户的交易详细记录，有新增客户时要负责把新客户添加入客户信息表，有优惠活动时要负责与客户一起进行各项优惠活动，当客户累计消费金额达到规定时，负责把客户升级为VIP客户。 人事部门：制订、修改公司各项人力资源管理制度和管理办法，建立制度化、规范化、科学化的人力资源管理体系。 组织建立绩效管理体系，制订相关方案；牵头组织公司各部门进行绩效考核并予以指导和监督，协助总经理室对各部门负责人的考核；做好考核结果的汇总、审核和归档管理等工作。根据岗位需求状况和人力资源规划，制定招聘计划，做好招聘前的准备、招聘实施和招聘后的手续完备等工作，也即有新员工时，要为员工表里加入一条记录；有辞职员工，在员工表里删除相应记录。（2）数据字典数据项名数据项含义数据类型长度取值范围客户名称区分不同客户varchar40所以汉字或英语客户编号唯一标识客户char8所有数字或英语客户出生日期可以计算客户年龄datatime客户电话公司联系客户varchar20客户性别区分客户男女char2男、女客户地址公司联系客户varchar40所有汉字、英语、数字客户级别判断客户是否为VIPvarchar10一般客户、VIP客户客户VIP号客户获取优惠VIP号varchar10所有数字、英语员工姓名区分不同员工varchar25所以汉字或英语员工编号唯一标识员工char8所有数字或英语员工性别区分员工男女char2男、女员工电话用于联系员工varchar20员工所属部门分配任务varchar30所有汉字、英语、数字商品名称区分不同商品varchar40所有汉字、英语、数字商品编号唯一标识商品char8所有数字或英语商品价格商品出售价格numeric所有数字发票编号唯一标识一笔成功交易char12商品编号交易成功的商品编号char8所有英语、数字业务员编号交易成功的业务员编号char8所有数字或英语交易客户编号交易成功的客户编号char8所有数字或英语成交数量每一笔订单成交商品数量int所有数字成交单价成交时商品单价numeric所有数字订单日期商品交易日期datetime用户身份表明登录者的身份级别varchar20所以汉字用户编号登录系统时的用户名varchar8所有汉字、英语、数字密码登录系统时使用的密码varchar8（3）数据流程图(DFD) 1)增添员工数据流图添加记录招聘考核人力资源管理部门 员工表 2）增添客户及客户交易数据流图商品交易销售员工添加记录 客户交易表 三、概念结构设计（1）目的与任务模型是对现实世界的模拟和抽象，概念模型是对现实世界的第一层抽象。是用户与数据库设计人员之间进行交流的语言。概念结构设计是将需求分析后得到的用户需求抽象为反映用户观点的概念模型。根据需求分析，可看出有五个实体：客户，员工，商品，销售明细。(2) 对每个实体定义的属性如下：客户：客户姓名,客户编号,出生日期,联系电话,性别,地址,客户级别,VIP号员工：员工编号,员工姓名,性别,联系电话,部门商品：商品名称,商品编号,商品价格销售明细：发票编号,商品编号,销售员编号,客户编号,销售数量,销售价格,销售日期(3)E-R实体模型图分析1）客户实体E-R图如下：客户编号客户姓名附加优惠VIP编号VIP客户升级VIP客户级别客户出生日期客户客户地址客户性别客户电话2）员工实体E-R图部门员工编号商品名称商品商品价格商品编号销售员工电话员工员工姓名员工性别2） 销售明细E-R图发票编号销售单价销售对象（即客户编号）销售日期销售数量业务员编号商品编号销售明细4、 逻辑结构设计（1） 任务与目的逻辑结构设计的任务是把概念结构设计阶段设计好的基本E-R图转换为与选用DBMS产品所支持的数据模型相符合的逻辑结构。（2）E-R图向关系模型的转换，实现将实体型和实体间的联系转换为关系模式。 把E-R图转换为关系模型如下：客户（客户姓名,客户编号,出生日期,联系电话,性别,地址,客户级别,VIP号）员工（员工编号,员工姓名,性别,联系电话,部门）商品（商品名称,商品编号,商品价格）销售明细（发票编号,商品编号,销售员编号,客户编号,销售数量,销售价格,销售日期）注：每个关系模式的主键码都用下划线标出，外键用波浪线标出。5、 物理结构设计1、 对以上E-R图所需的实体以及联系需要的表的设计如下：1） 客户基本信息表属性含义属性名数据类型客户编号customerNoChar(8)客户姓名customerNameVarchar(40)出生日期birthdaydatetime性别sexChar(2)联系电话telephoneVarchar(20)地址addressVarchar(40)客户级别cust_levelVarchar(10)VIP编号VIP_noVarchar(10)2) 员工基本信息表属性含义属性名数据类型员工编号staffNoChar(8)员工姓名staffNameVarchar(25)员工性别sexChar(2)联系方式telephoneVarchar(20)所属部门departmentVarchar(30)3） 商品基本信息表属性含义属性名数据类型商品编号productNoChar(8)商品名称productNameVarchar(40)商品价格priceNumeric(7,2)4) 销售明细表属性含义属性名数据类型发票编号invoiceNoChar(12)商品编号productNoChar(8)销售员编号salerNoChar(8)客户编号customerNoChar(8)销售数量quantityint销售价格Sell_priceNumeric(7,2)销售日期Sell_datedatetime5） 还应设计一个用户表，进入系统时应验证身份，以确保安全性属性含义属性名数据类型用户编号User_IDChar(8)用户身份User_levelChar(20)密码passwordChar(8)2、 具体SQL计划（1） 建立所有表并向相关表中插入若干记录-建立客户基本信息表CREATE TABLE dbo.Customer ( customerNo char (8) primary key, customerName varchar (40) NOT NULL , birthday datetime NOT NULL , sex char (2) CHECK(sex IN(男,女), telephone varchar (20) NOT NULL , address varchar (40) NOT NULL , cust_level varchar (10) CHECK (cust_level IN(一般客户,VIP客户), VIP_no varchar (10) NULL )ON PRIMARY-向客户基本信息表内插入10条记录insert into Customer values(C2001001,何淋,1965-6-15,男,13817605008,上海市北京路8号,一般客户,)insert into Customer values(C2003007,陈易,1975-4-15,男,13509405008,上海市南京路18号,VIP客户,VIP00001)insert into Customer values(C2003099,陈璐希,1970-2-15,女,13800605008,南京市青海路18号,一般客户,)insert into Customer values(C2005009,汤诗,1968-4-25,女,13617605027,南昌市青山路100号,一般客户,)insert into Customer values(C2006045,谢炜,1977-6-12,女,13817605364,上海市福州路135号,一般客户,)insert into Customer values(C2006098,高源,1980-12-13,男,13640505008,南昌市中山路1号,VIP客户,VIP00002)insert into Customer values(C2007001,刘诗一,1985-3-27,女,13823605008,上海市九江路88号,VIP客户,VIP00003)insert into Customer values(C2007011,安源,1979-5-21,男,13227605008,深圳市阳关大道10号,一般客户,)insert into Customer values(C2008001,陈诗诗,1981-7-17,女,13452505008,上海市浦东大道6号,一般客户,)insert into Customer values(C2011003,但衍,1983-9-19,男,18217605008,江西财经大学5栋1-1室,VIP客户,VIP00004)-建立员工基本信息表CREATE TABLE dbo.Staff (staffNo char (8) primary key, staffName varchar (25) NOT NULL , sex char (2) CHECK(sex IN(男,女), telephone varchar (20) NOT NULL ,address varchar (40) NOT NULL , department varchar (30) NOT NULL) ON PRIMARY-插入5条员工基本信息记录insert into Staff values(S2002003,张良,男,13320387869,南昌市阳明路99号,业务科)insert into Staff values(S2005011,张廊,男,13320387888,南昌市高新开发区12号,财务科)insert into Staff values(S2006016,徐诺,女,13320387333,上海市九江路12号,业务科)insert into Staff values(S2007012,瞿颖,女,13320386254,南昌市青石路22号,业务科)insert into Staff values(S2009004,张娜,女,13320382347,南昌市阳明路12号,业务科)-建立商品基本信息表CREATE TABLE dbo.Product (productNo char (8) primary key,productName varchar (40) NOT NULL ,price numeric (7,2) NOT NULL) ON PRIMARY-插入5条商品基本信息记录insert into Product values(P2008002,17寸显示器,550)insert into Product values(P2009003,120GB硬盘,180)insert into Product values(P2009011,3.5寸软驱,340)insert into Product values(P2010023,键盘,58)insert into Product values(P2011001,VGA显示卡,88)-建立销售明细表CREATE TABLE dbo.SellDetail (invoiceNo char (12) primary key, productNo char (8) NOT NULL , salerNo char (8) NOT NULL , customerNo char (8) NOT NULL ,quantity int NOT NULL ,Sell_price numeric(7, 2) NOT NULL, Sell_date datetime NOT NULL) ON PRIMARY-插入20条销售明细记录insert into SellDetail values(I0000000001,P2008002,S2002003,C2003007,8,520,2009-2-24)insert into SellDetail values(I0000000002,P2009003,S2005011,C2001001,4,180,2009-2-26)insert into SellDetail values(I0000000003,P2009011,S2006016,C2003099,1,340,2009-8-24)insert into SellDetail values(I0000000004,P2010023,S2007012,C2005009,2,58,2009-10-24)insert into SellDetail values(I0000000005,P2011001,S2009004,C2006045,1,88,2009-11-23)insert into SellDetail values(I0000000006,P2008002,S2009004,C2006098,2,550,2009-12-11)insert into SellDetail values(I0000000007,P2008002,S2002003,C2007001,5,520,2009-12-24)insert into SellDetail values(I0000000008,P2011001,S2005011,C2007011,8,520,2010-2-24)insert into SellDetail values(I0000000009,P2009011,S2006016,C2008001,8,88,2010-3-2)insert into SellDetail values(I0000000010,P2008002,S2002003,C2011003,3,530,2010-4-24)insert into SellDetail values(I0000000011,P2009003,S2005011,C2011003,5,180,2010-5-2)insert into SellDetail values(I0000000012,P2010023,S2002003,C2003007,2,58,2010-6-24)insert into SellDetail values(I0000000013,P2008002,S2006016,C2006045,1,520,2010-7-2)insert into SellDetail values(I0000000014,P2008002,S2002003,C2006098,2,520,2010-7-21)insert into SellDetail values(I0000000015,P2011001,S2005011,C2003007,5,88,2010-7-24)insert into SellDetail values(I0000000016,P2009003,S2009004,C2008001,3,180,2010-8-22)insert into SellDetail values(I0000000017,P2010023,S2002003,C2005009,8,58,2010-8-27)insert into SellDetail values(I0000000018,P2008002,S2009004,C2003099,3,520,2010-12-24)insert into SellDetail values(I0000000019,P2009003,S2002003,C2003007,8,160,2011-2-24)insert into SellDetail values(I0000000020,P2008002,S2002003,C2003007,1,540,2011-4-16)-建立用户表CREATE TABLE dbo.User (User_ID char (8) primary key, User_level varchar (20) NOT NULL , password char (8) NOT NULL) ON PRIMARY-插入15条用户记录insert into User_check values(C2001001,一般客户,w0000000)insert into User_check values(C2003007,VIP客户,w0000001)insert into User_check values(C2003099,一般客户,w0000110)insert into User_check values(C2005009,一般客户,w0023000)insert into User_check values(C2006045,一般客户,w0009800)insert into User_check values(C2006098,VIP客户,w0030000)insert into User_check values(C2007001,VIP客户,w4400000)insert into User_check values(C2007011,一般客户,w0000065)insert into User_check values(C2008001,一般客户,w0970000)insert into User_check values(C2011003,VIP客户,w0888000)insert into User_check values(S2002003,业务科职员,w0055500)insert into User_check values(S2005011,财务科职员,w0066600)insert into User_check values(S2006016,业务科职员,w0033300)insert into User_check values(S2007012,业务科职员,w0012300)insert into User_check values(S2009004,业务科职员,w0034500)六、数据库实施1、建表插入记录结果如下：1)客户基本信息表2） 员工基本信息表3） 商品基本信息表4） 销售明细表4） 用户表2、 查询操作2-1对客户表的各种查询select * from Customer查询所有的客户信息select customerName,addressfrom Customerwhere customerNo=C2003007查询编号为C2003007的客户的姓名和地址select * from Customerwhere cust_level=VIP客户查询所有的VIP客户的基本信息2-2对员工表的查询操作select * from Staff查询所有的员工信息select * from Staffwhere department=业务科查询业务科员工的基本信息2-3对商品表的查询操作 select * from Product查询所有的商品信息select productNo,productName from Productwhere productName=键盘查询商品名为键盘的商品编号2-4 对销售明细表的查询select * from SellDetail查询所有的销售明细信息select * from SellDetailwhere productNo=P2008002查询商品编号为P2008002的销售明细select * from SellDetailwhere salerNo=S2002003查询业务员编号为S2002003的业务员的销售明细select * from SellDetailwhere quantity*Sell_price>2000查询一次性购物金额大于2000的销售明细select * from SellDetailwhere CustomerNo=C2003007查询编号为C2003007的客户的购物记录select * from SellDetailwhere year(Sell_date)=2009查询2009年的销售商明细select a.customerNo,a.customerName,b.invoiceNo,c.productName,b.quantity,b.Sell_price,b.Sell_Date from Customer a,SellDetail b,Product c where a.customerNo=b.customerNo and b.productNo=c.productNo and b.customerNo=C2003007查询客户编号为C2003007的的客户名称、购物发票编号、所购商品名称、数量、单价和购物日期select b.customerNo,a.customerName,sum(quantity *Sell_price) 总金额from Customer a,SellDetail bwhere a.customerNo=b.customerNogroup by b.customerNo,a.customerNameorder by 总金额DESC在销售明细里面查询每位客户的累计消费总金额，并按照消费金额降序排列，同时可找出消费最高的客户update Customer set cust_level=VIP客户from Customer a,( select customerNo,sum(quantity*Sell_price)总消费 from SellDetail group by customerNo having sum(quantity*Sell_price)>2000) bwhere a.customerNo=b.customerNoselect * from Customerwhere cust_level=VIP客户把所有累计消费金额大于2000的客户升级为VIP客户更新前的更新后新增一位VIP客户select top 8 b.customerNo,a.customerName,sum(quantity *Sell_price) 总金额from Customer a,SellDetail bwhere a.customerNo=b.customerNogroup by b.customerNo,a.customerNameorder by 总金额DESC查询消费总金额排名前8名的客户2-5 对用户表的查询操作select * from User_check查询所有的用户，一共有15个用户select count(*) 客户数 from User_checkwhere User_level=一般客户or User_level=VIP客户查询用户表里面级别为客户的数目，包括一般用户和VIP用户3、 定义视图3-1 定义客户表的视图create view cust_VIP_view as select * from Customerwhere cust_level=VIP客户select * from cust_VIP_view建立VIP客户的视图，显示VIP客户的基本信息create view cust_sex_view as select * from Customerwhere sex=男select * from cust_sex_view建立男客户的视图，显示男客户的基本信息3-2定义员工表的视图create view staff_dept_view as select * from Staffwhere department=业务科select * from staff_dept_view建立员工视图，显示业务科的所有员工3-3定义商品表的视图create view prodt_price_view as select * from Productwhere price between 100 and 300select * from prodt_price_view建立商品单价的视图，显示价格在100300间的商品3-4建立商品明细表的视图create view Detail_prodt_view as select * from SellDe