SQL_数据库设计

工程学院,单击此处编辑母版文本样式,第二级,第三级,*,SQL,数据库设计与实现,计算机系,姑苏行,E-Mail,：,qingxingbing,课程地位,C#,SQL Server,XML,PSPM,ASP.NET,Oracle,OOAD,Computer Base,HTMLJavaScript,SQL Base,OOPJava,C,STB,JSP,/,Servlet,EJB/,WebService,WinForms,Struts/JSF,Testing/SQA,Linux,2,第一章,数据库的设计,课程内容回顾,数据库有哪些基本操作？,建库,建表,加约束,创建登录帐户,基本的数据操纵语句有哪些？语法是？,增（,INSERT,）,删（,DELETE,）,改（,UPDATE,）,查（,SELECT,）,常用的聚合函数有哪些？,表连接分为哪几种类型？,4,课程目标,了解设计数据库的基本步骤,熟练使用,T-SQL,实现建库、建表、加约束,掌握,T-SQL,编程，实现功能强大的查询,掌握创建索引、视图，快速访问数据库,掌握创建存储过程，实现复杂的业务规则,理解触发器的原理，实现高级的约束,5,本章目标,了解设计数据库的步骤,掌握如何绘制数据库的,E-R,图,理解数据库的规范化三大范式,6,为什么需要设计,数据库,2-1,修建茅屋需要设计吗？,修建大厦需要设计吗？,结论：当数据库比较复杂时我们需要设计数据库,7,为什么需要设计,数据库,2-2,良好的数据库设计：,节省数据的存储空间,能够保证数据的完整性,方便进行数据库应用系统的开发,糟糕的数据库设计：,数据冗余、存储空间浪费,内存空间浪费,数据更新和插入的异常,8,软件项目开发周期,需求分析阶段,：分析客户的业务和数据处理需求,;,概要设计阶段,：设计数据库的,E-R,模型图，确认需求信息,的正确和完整,;,详细设计阶段,：将,E-R,图转换为多张表，进行逻辑设计，,并应用数据库设计的三大范式进行审核,;,代码编写阶段：选择具体数据库进行物理实现，并编写,代码实现前端应用,;,软件测试阶段：,安装部署：,现实世界,建模,信息世界,数据库世界,模型转换,规范化,9,设计数据库的步骤,4-1,收集信息,：,与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务,BBS,论坛的基本功能：,用户注册和登录，后台数据库需要存放用户的注册信息和在线状态信息；,用户发贴，后台数据库需要存放贴子相关信息，如贴子内容、标题等；,论坛版块管理：后台数据库需要存放各个版块信息，如版主、版块名称、贴子数等；,10,设计数据库的步骤,4-2,标识对象,（实体,Entity,）,标识数据库要管理的关键对象或实体,实体一般是名词：,用户：论坛普通用户、各版块的版主。,用户发的主贴,用户发的跟贴（回贴）,版块：论坛的各个版块信息,11,设计数据库的步骤,4-3,论坛用户：,呢称,密码,电子邮件,生日,性别,用户的等级,备注信息,注册日期,状态,积分,主贴,发贴人,发贴表情,回复数量,标题,正文,发贴时间,点击数,状态：,最后回复时间,回贴,贴子编号,回贴人,回贴表情,标题,正文,回贴时间,点击数,版块,版块名称,版主,本版格言,点击率,发贴数,标识,每个实体的属性（,Attribute,）,12,设计数据库的步骤,4-4,标识对象之间的关系,（,Relationship,）,跟贴和主贴有主从关系：我们需要在跟贴对象中表明它是谁的跟贴；,版块和用户有关系：从用户对象中可以根据版块对象查出对应的版主用户的情况；,主贴和版块有主从关系：需要表明发贴是属于哪个版块的；,跟贴和版块有主从关系：需要表明跟贴是属于哪个版块的；,13,绘制,E-R,图,4-1,E-R,（,Entity,Relationship,）,实体关系图,符号,含义,实体，一般是名词,属性，一般是名词,关系，一般是动词,14,绘制,E-R,图,4-2,管理,bbsUser,（用户，版主）,出生日期,昵称,版块名称,版主,bbsSection,（版块）,15,绘制,E-R,图,4-3,映射基数,一对一,X,X,X,X,Y,Y,Y,Y,X,X,X,X,Y,Y,Y,Y,一对多,X,X,X,X,Y,Y,Y,多对一,X,X,X,X,Y,Y,Y,Y,多对多,客户,订单,产品,1,N,M,N,16,绘制,E-R,图,1,1,1,1,M,1,M,M,M,M,用户积分,性别,用户等级,备注信息,注册日期,版块名称,本版留言,发贴数,状态,密码,昵称,电子邮件,生日,论坛用户,（,BBSUser,）,管理,发表,发表,跟随,属于,属于,点击率,版主,标题,发贴人,贴子编号,正文,点击率,版块,（,BBSSection,）,发贴,（,BBSTopic,）,发贴人,正文,状态,贴子编号,所在版块,最后回复时间,发贴表情,回复数量,点击率,发贴时间,标题,M,跟贴,（,BBSReply,）,所在版块,发贴时间,最后回复时间,发贴表情,1,论坛,E-R,图,17,如何将,E-R,图转换为表,3-1,将各实体转换为对应的表，将各属性转换为各表对应的列,标识每个表的主键列，需要注意的是：没有主键的表添加,ID,编号列，它没有实际含义，用于做主键或外键，例如用户表中的“,UID”,列，版块表中添加“,SID”,列，发贴表和跟贴表中的“,TID”,列,在表之间建立主外键，体现实体之间的映射关系,18,如何将,E-R,图转换为表,3-2,UID,主键,TID,主键,RID,主键,SID,主键,19,如何将,E-R,图转换为表,添加各表之间的关系,20,数据规范化,仅有好的,RDBMS,并不足以避免数据冗余，必须在数据库的设计中创建好的表结构,常见数据库设计的问题有：信息重复，更新异常，插入异常，删除异常,Dr,E.F.codd,最初定义了规范化的三个级别，范式是具有,最小冗余,的表结构。这些范式是：,第一范式,(1st NF,First Normal,Fromate,),第二范式,(2nd NF,Second Normal,Fromate,),第三范式,(3rd NF,Third Normal,Fromate,),21,第一范式,(1st NF),B,uyer,ID,Country,City,1,1,4,2,中国,中国,日本,美国,北京,北京,东京,纽约,B,uyer,ID,Address,1,2,3,4,中国北京市,美国纽约市,英国利物浦,日本东京市,第一范式的目标是确保每列的,原子性,如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（,1NF,）,22,第二范式,(2nd NF),如果一个关系满足,1NF,，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（,2NF,）,第二范式要求每个表只描述一件事情,Order,s,字段,例子,订单编号,产品编号,订购日期,价 格,001,A001,2000-2-3,$29.00,Order,s,字段,例子,订单编号,订购日期,001,2000-2-3,Products,字段,例子,产品编号,价 格,A,001,$29.00,23,第三范式,(3rd NF),如果一个关系满足,2NF,，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（,3NF,）,Armstrong,公理定义的传递依赖：,ABC,是关系,R,的,3,个属性，如果,AB,且,BC,，可得出,AC,Order,s,字段,例子,订单编号,订购日期,顾客编号,001,2000-2-3,AB001,顾客姓名,Tony,Order,s,字段,例子,订单编号,订购日期,顾客编号,001,2000-2-3,AB001,24,规范化实例,5-1,假设某建筑公司要设计一个数据库。公司的业务规,则概括说明如下：,公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等,公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等,公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）,公司定期制定一个工资报表，如图,-,1,所示,25,规范化实例,5-2,工程号,工程名称,职工号,姓名,职务,小时工资率,工时,实发工资,A1,花园大厦,1001,齐光明,工程师,65,13,845.00,1002,李思岐,技术员,60,16,960.00,1004,葛宇宏,律师,60,19,1140.00,小计,2945.00,A2,立交桥,1001,齐光明,工程师,65,15,975.00,1003,鞠明亮,工人,55,17,935.00,小计,1910.00,A3,临江饭店,1002,李思岐,技术员,60,18,1080.00,1004,葛宇洪,技术员,60,14,840.00,小计,1920.00,图,-1,某公司的工资表,26,规范化实例,5-3,工程号,工程名称,职工号,姓名,职务,小时工资率,工时,A1,花园大厦,1001,齐光明,工程师,65,13,A1,花园大厦,1002,李思岐,技术员,60,16,A1,花园大厦,1001,齐光明,工程师,65,13,A1,花园大厦,1003,鞠明亮,工人,55,17,A3,临江饭店,1002,李思岐,技术员,60,18,A3,临江饭店,1004,葛宇洪,技术员,60,14,图,-2,某公司的项目工时表,27,规范化实例,5-4,1.,表中包含大量的冗余，可能会导致数据异常：,更新异常,例如，修改职工号,=1001,的职务，则必须修改所有职工号,=1001,的行,添加异常,若要增加一个新的职工时，首先必须给这名职工分配一个工程。或者为了添加一名新职工的数据，先给这名职工分配一个虚拟的工程。（因为主关键字不能为空）,删除异常,例如，,1001,号职工要辞职，则必须删除所有职工号,1001,的数据行。这样的删除操作，很可能丢失了其它有用的数据,28,规范化实例,5-5,2,采用这种方法设计表的结构，虽然很容易产生工资报表，但是每当一名职工分配一个工程时，都要重复输入大量的数据。这种重复的输入操作，很可能导致数据的不一致性。,29,一张表描述了多件事情，如图,-,3,所示。,应用范式规范化设计,工程号,工程名称,职工号,姓名,职务,小时工资率,工时,图,-,3,函数依赖图,工程信息,员工信息,项目工时信息,30,应用第二范式规范化,工程号,工程名称,职工号,姓名,职务,小时工资率,工程号,职工号,工时,图,-,4,应用第二范式,工程表,员工表,项目工时表,满足第三范式吗？,31,应用第三范式规范化,工程号,工程名称,职工号,姓名,职务,职务,小时工资率,工程号,职工号,工时,工程表,员工表,职务表,工程表,32,规范化和性能的关系,为满足某种商业目标，数据库性能比规范化数据库更重要,通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间,通过在给定的表中插入计算列（如成绩总分），以方便查询,进行规范化的同时，还需要综合考虑数据库的性能,。,33,总结,2-1,在需求分析阶段，设计数据库的一般步骤为：,收集信息,标识对象,标识每个对象的属性,标识对象之间的关系,在概要设计阶段和详细设计阶段，设计数据库的步骤为：,绘制,E-R,图,将,E-R,图转换为表格,应用三大范式规范化表格,34,总结,2-2,为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式 。,第一范式（,1NF,）的目标：确保每列的原子性。,第二范式（,2NF,）的目标：确保表中的每列，都和主键相关,第三范式（,3NF,）的目标：确保每列都和主键列直接相关，而不是间接相关,35,