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

课 程 设 计题 目 客户信息管理系统学 院 计算机科学与信息工程学院 专 业 计算机科学与技术班 级 2010计算机 1班学生姓名 刘小燕 指导教师 康世瀛2012 年 6 月 10 日重庆工商大学课程设计成绩评定表 指导教师评定成绩:指导教师签名:年 月 日目 录一、系统分析 (41. 系统目标(42. 系统功能分析(4 二、需求分析 (51、 数据字典 (52、 数据流图 (6 三、概念结构设计 (71、 目的与任务 (72、 实体属性 (73、 E-R 实体模型图 (8四、逻辑结构设计 (9五、物理结构设计(9六、数据库实施 (161、建表插入记录结果 (162、查询操作 (183、定义视图(264、定义游标 (305、定义存储过程 (346、定义触发器 (36七、 系统优缺点分析(38八、 参考文献(38一. 系统分析(一系统目标:1、 系统的开发背景:为某从事商品销售的企业单位设计一个客户信息管理系统,方便单位部门更 好的管理客户信息,通过对客户详细资料的深入分析,了解客户的爱好习性,从 而从最大程度满足客户需求,从而提高企业竞争能力。2、 系统功能的基本要求:1 增删改查客户信息;2 可以进行总汇,反映每月、年的客户数量, ;3 可以统计各个客户的消费总金额,当累计消费超过某个数值时,可以让 该客户成为 VIP 客户,在以后的交易中可以给予相关优惠政策;可以计算各个客 户在每个月、每年的消费总金额,消费金额前几名企业部门给予客户相关奖励, 通过此类优惠策略,构建客户与企业部门友好和谐的交易关系,同时也能达到双 赢的目的。3、数据库要求:在数据库中至少应该包含下列数据表:1 销售员工表;2 客户信息表;3 商品库存表;4 客户交易明细表;(二系统功能分析:客户信息管理系统的主要功能是对客户信息进行录入、删除、修改、浏览、 查找和排序等操作,用户可以对文件中存储的客户信息进行查找和浏览。具体功 能如下:新增客户:当有新客户时,向客户信息表里面添加一条新的记录。 修改客户:当有客户的信息如联系方式需要修改时,可更新客户资料。 盘存管理:系统的使用用户可以根据各自的权限查询各自权限范围的各种 记录。二、需求分析(1该企业单位的组织机构及各部门的业务活动情况采购部:负责批发采购商品,并把采购发票交予商品入库员,负责及时准确 更新商品库。商品验收员:负责核对商品采购订单,核对无误后产品入库。销售员工:负责与客户进行交易,记录与客户的交易详细记录,有新增客户 时要负责把新客户添加入客户信息表, 有优惠活动时要负责与客户一起进行各项 优惠活动,当客户累计消费金额达到规定时,负责把客户升级为 VIP 客户。 人事部门:制订、 修改公司各项人力资源管理制度和管理办法, 建立制度化、 规范化、科学化的人力资源管理体系。 组织建立绩效管理体系,制订相关方案; 牵头组织公司各部门进行绩效考核并予以指导和监督, 协助总经理室对各部门负 责人的考核; 做好考核结果的汇总、 审核和归档管理等工作。 根据岗位需求状况 和人力资源规划, 制定招聘计划, 做好招聘前的准备、 招聘实施和招聘后的手续 完备等工作,也即有新员工时,要为员工表里加入一条记录;有辞职员工,在员 工表里删除相应记录。(2数据字典 (3数据流程图 (DFD1 增添员工数据流图 2增添客户及客户交易数据流图 三、概念结构设计(1目的与任务模型是对现实世界的模拟和抽象, 概念模型是对现实世界的第一层抽象。 是用户 与数据库设计人员之间进行交流的语言。 概念结构设计是将需求分析后得到的用 户需求抽象为反映用户观点的概念模型。根据需求分析,可看出有五个实体:客户,员工,商品,销售明细。(2对每个实体定义的属性如下:客户:客户姓名 , 客户编号 , 出生日期 , 联系电话 , 性别 , 地址 , 客户级别 ,VIP 号 员工:员工编号 , 员工姓名 , 性别 , 联系电话 , 部门 商品:商品名称 , 商品编号 , 商品价格 销售明细:发票编号 , 商品编号 , 销售员编号 , 客户编号 , 销售数量 , 销售价格 , 销 售日期 (3E-R实体模型图分析1客户实体 E-R 图如下: 2员工实体 E-R 图 2销售明细 E-R 图 四、 逻辑结构设计(1任务与目的逻辑结构设计的任务是把概念结构设计阶段设计好的基本 E-R 图转换为与 选用 DBMS 产品所支持的数据模型相符合的逻辑结构。(2 E-R 图向关系模型的转换, 实现将实体型和实体间的联系转换为关系模式。 把 E-R 图转换为关系模型如下:客户(客户姓名 , 客户编号 , 出生日期 , 联系电话 , 性别 , 地址 , 客户级别 ,VIP 号 员工(员工编号 , 员工姓名 , 性别 , 联系电话 , 部门商品(商品名称 , 商品编号 , 商品价格销售明细(发票编号 , 商品编号 , 销售员编号 , 客户编号 , 销售数量 , 销售价格 , 销售日 期注:每个关系模式的主键码都用下划线标出,外键用波浪线标出。五、物理结构设计1、对以上 E-R 图所需的实体以及联系需要的表的设计如下:1客户基本信息表 2 员工基本信息表 3商品基本信息表4 销售明细表 5还应设计一个用户表,进入系统时应验证身份,以确保安全性 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 NULLON PRIMARY-向客户基本信息表内插入 10条记录insert into Customer values(C,何淋 ,1965-6-15, 男 , 上海市北京路 8号 , 一般客户 ,insert into Customer values(C,陈易 ,1975-4-15, 男 , 上海市南京路 18号 ,VIP 客户 ,VIP00001insert into Customer values(C,陈璐希 ,1970-2-15, 女 , 南京市青海路 18号 , 一般客户 ,insert into Customer values(C,汤诗 ,1968-4-25, 女 , 南昌市青山路 100号 , 一般客户 ,insert into Customer values(C,谢炜 ,1977-6-12, 女, 上海市福州路 135号 , 一般客户 ,insert into Customer values(C,高源 ,1980-12-13, 男 , 南昌市中山路 1号 ,VIP 客户 ,VIP00002insert into Customer values(C,刘诗一 ,1985-3-27, 女 , 上海市九江路 88号 ,VIP 客户 ,VIP00003insert into Customer values(C,安源 ,1979-5-21, 男 , 深圳市阳关大道 10号 , 一般客户 ,insert into Customer values(C,陈诗诗 ,1981-7-17, 女 , 上海市浦东大道 6号 , 一般客户 ,insert into Customer values(C,但衍 ,1983-9-19, 男 , 江西财经大学 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 NULLON PRIMARY-插入 5条员工基本信息记录insert into Staff values(S,张良 , 男 , 南昌市 阳明路 99号 , 业务科 insert into Staff values(S,张廊 , 男 , 南昌市高新开发区 12号 , 财务科 insert into Staff values(S,徐诺 , 女 , 上海市 九江路 12号 , 业务科 insert into Staff values(S,瞿颖 , 女 , 南昌市 青石路 22号 , 业务科 insert into Staff values(S,张娜 , 女 , 南昌市 阳明路 12号 , 业务科 -建立商品基本信息表CREATE TABLE dbo.Product (productNo char (8 primary key,productName varchar (40 NOT NULL ,price numeric (7,2 NOT NULLON PRIMARY-插入 5条商品基本信息记录insert into Product values(P,17寸显示器 ,550insert into Product values(P,120GB硬盘 ,180insert into Product values(P,3.5寸软驱 ,340insert into Product values(P,键盘 ,58insert into Product values(P,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 NULLON PRIMARY-插入 20条销售明细记录insert into SellDetail values(I,P,S,C,8,520,2009-2-2 4insert into SellDetail values(I,P,S,C,4,180,2009-2-2 6insert into SellDetail values(I,P,S,C,1,340,2009-8-2 4insert into SellDetail values(I,P,S,C,2,58,2009-10-2 4insert into SellDetail values(I,P,S,C,1,88,2009-11-2 3insert into SellDetail values(I,P,S,C,2,550,2009-12-11insert into SellDetail values(I,P,S,C,5,520,2009-12-24insert into SellDetailvalues(I,P,S,C,8,520,2010-2-2 4insert into SellDetail values(I,P,S,C,8,88,2010-3-2 insert into SellDetail values(I,P,S,C,3,530,2010-4-2 4insert into SellDetail values(I,P,S,C,5,180,2010-5-2 insert into SellDetail values(I,P,S,C,2,58,2010-6-24 insert into SellDetail values(I,P,S,C,1,520,2010-7-2 insert into SellDetail values(I,P,S,C,2,520,2010-7-2 1insert into SellDetail values(I,P,S,C,5,88,2010-7-24 insert into SellDetail values(I,P,S,C,3,180,2010-8-2 2insert into SellDetail values(I,P,S,C,8,58,2010-8-27 insert into SellDetail values(I,P,S,C,3,520,2010-12-24insert into SellDetail values(I,P,S,C,8,160,2011-2-2 4insert into SellDetail values(I,P,S,C,1,540,2011-4-1 6-建立用户表CREATE TABLE dbo.User (User_ID char (8 primary key,User_level varchar (20 NOT NULL ,password char (8 NOT NULLON PRIMARY-插入 15条用户记录insert into User_check values(C,一般客户 ,w insert into User_check values(C,VIP客户 ,w insert into User_check values(C,一般客户 ,w insert into User_check values(C,一般客户 ,w insert into User_check values(C,一般客户 ,w insert into User_check values(C,VIP客户 ,w insert into User_check values(C,VIP客户 ,w insert into User_check values(C,一般客户 ,w insert into User_check values(C,一般客户 ,w insert into User_check values(C,VIP客户 ,winsert into User_check values(S,业务科职员 ,w insert into User_check values(S,财务科职员 ,w insert into User_check values(S,业务科职员 ,w insert into User_check values(S,业务科职员 ,w insert into User_check values(S,业务科职员 ,w六、数据库实施1、建表插入记录结果如下:1客户基本信息表 2员工基本信息表 3商品基本信息表 4销售明细表 4用户表 2、查询操作2-1对客户表的各种查询select * from Customer查询所有的客户信息 select customerName,addressfrom Customerwhere customerNo=C查询编号为 C的客户的姓名和地址 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 Product where productName=键盘 查询商品名为键盘的商品编号 2-4 对销售明细表的查询select * from SellDetail查询所有的销售明细信息 select * from SellDetailwhere productNo=P查询商品编号为 P的销售明细 select * from SellDetailwhere salerNo=S查询业务员编号为 S的业务员的销售明细 select * from SellDetailwhere quantity*Sell_price2000查询一次性购物金额大于 2000 的销售明细select * from SellDetailwhere CustomerNo=C查询编号为 C的客户的购物记录 select * from SellDetailwhere year(Sell_date=2009查询 2009 年的销售商明细select a.customerNo,a.customerName,b.invoiceNo,ductName,b.quantity,b.Sell_price,b. Sell_Date from Customer a,SellDetail b,Product c wherea.customerNo=b.customerNo and ductNo=ductNo andb.customerNo=C查询客户编号为 C的的客户名称、 购物发票编号、 所购商品名称、 数量、 单价和购物日期 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 SellDetailgroup by customerNohaving sum(quantity*Sell_price2000 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 Customer where sex=男 select * from cust_sex_view 建立男客户的视图,显示男客户的基本信息3-2定义员工表的视图create view staff_dept_view as select * from Staff where department=业务科 select * from staff_dept_view 建立员工视图,显示业务科的所有员工3-3定义商品表的视图create view prodt_price_view as select * from Product where price between 100 and 300select * from prodt_price_view建立商品单价的视图,显示价格在 100 300 间的商品 3-4建立商品明细表的视图create view Detail_prodt_view as select * from SellDetailwhere productNo=Pselect * from Detail_prodt_view建立一个商品明细表的视图,显示商品编号为 P 的商品销售明细create view Detail_saler_view as select * from SellDetailwhere salerNo=Sselect * from Detail_saler_view建立一个商品明细表的视图,显示编号为 S 的销售员的销售情况Create view Detail_cust_view asSelect a.customerNo,a.customerName,b.invoiceNo,ductName,b.quantity,b.Sell_price,b.Sell_Datefrom Customer a,SellDetail b,Product cWhere a.customerNo=b.customerNo and ductNo=ductNo and b.customerNo=Cselect * from Detail_cust_view查询客户编号为 C的的客户名称、 购物发票编号、 所购商品名称、 数量、 单价和购物日期 create view Detail_consume_order asselect 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 总金额 DESCselect * from Detail_consume_order建立一个销售明细的视图,显示消费总金额排名前 8名的客户 create view Detail_consume_year asselect sum(quantity *Sell_price 总金额from SellDetailwhere year(Sell_date=2009select * from Detail_consume_year建立一个销售明细的视图,显示 2009 年度交易总金额3-5 建立用户表的视图create view user_cust_countas select count(* 客户数 from User_checkwhere User_level=一般客户 or User_level=VIP客户 select * from user_cust_count建立一个用户表的视图,显示用户级别为客户(包括一般客户和 VIP 客户的 总数目4、定义游标4-1 定义客户表上的游标declare cur_cust cursor forselect * from Customerwhere cust_level=VIP客户 order by customerNoopen cur_custselect cursor内数据条数 =cursor_rowsfetch next from cur_custwhile (fetch_status-1beginselect cursor读取状态 =fetch_statusfetch next from cur_custendclose cur_custdeallocate cur_cust利用游标选取客户级别为 VIP 的客户的所有字段,并逐行显示游标中的信息 4-2定义销售明细表上的游标declare cur_cust_detail cursor forselect a.customerNo,a.customerName,b.invoiceNo,ductName,b.quantity,b.Sell_price,b. Sell_Datefrom Customer a,SellDetail b,Product cwhere a.customerNo=b.customerNo and ductNo=ductNo and b.customerNo=Copen cur_cust_detailselect cursor内数据条数 =cursor_rowsfetch next from cur_cust_detailwhile (fetch_status-1beginselect cursor读取状态 =fetch_statusfetch next from cur_cust_detailendclose cur_cust_detaildeallocate cur_cust_detail利用游标选取编号为 C的客户的客户名称、购物发票编号、所购商品名 称、数量、单价和购物日期,并逐行显示游标中的信息 declare cur_detail_top5 cursor forselect top 5 b.customerNo,a.customerName,sum(quantity *Sell_price 总金额 from Customer a,SellDetail bwhere a.customerNo=b.customerNogroup by b.customerNo,a.customerNameorder by 总金额 DESCopen cur_detail_top5select cursor内数据条数 =cursor_rowsfetch next from cur_detail_top5while (fetch_status-1beginselect cursor读取状态 =fetch_statusfetch next from cur_detail_top5endclose cur_detail_top5deallocate cur_detail_top5利用游标选取消费前 5 名的客户的编号、姓名和消费总金额 5、定义存储过程5-1定义客户表上的存储过程create procedure customer_search c_no char(8asselect * from Customerwhere customerNo=c_noexec customer_search C 带输入参数的存储过程,根据客户编号查询该客户的基本信息create procedure VIPcustomer_searchasselect * from Customerwhere cust_level=VIP客户 exec VIPcustomer_search建立存储过程,显示 VIP 客户的基本信息5-2定义商品表上的存储过程create procedure product_searchasselect productNo,productName from Productwhere productName=17寸显示器 exec product_search建立存储过程,显示商品名为 17 寸显示器的商品编号、商品名5-3定义销售明细表上的存储过程create procedure detail_product_search cust_no char(8 as selecta.customerNo,a.customerName,b.invoiceNo,ductName,b.quantity,b.Sell_price,b.Sell_Date from Customer a,SellDetail b,Product cwhere a.customerNo=b.customerNo and ductNo=ductNo and b.customerNo=cust_noexec detail_product_search C建立一个带输入参数的存储过程, 根据输入的客户编号查询客户名称、 购物发票 编号、所购商品名称、数量、单价和购物日期create procedure detail_consume_top5asselect top 5 b.customerNo,a.customerName,sum(quantity *Sell_price 总金额 from Customer a,SellDetail bwhere a.customerNo=b.customerNogroup by b.customerNo,a.customerNameorder by 总金额 DESCexec detail_consume_top5建立一个存储过程,查询消费前五名的客户6、定义触发器 6-1 定义客户表上的触发器 6-1-1仅允许dbo用户可以删除Customer表中的数据 create trigger Tr_cust o