《数据库设计知识点总结.doc》由会员分享，可在线阅读，更多相关《数据库设计知识点总结.doc（12页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据库设计知识点总结数据库设计知识点总结数据库设计1概念1.1描述数据库设计是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效地存储数据。1.2数据库设计包含四部分需求分析概念结构设计逻辑结构设计物理结构设计1.3数据库设计的原则明白自己的系统为OLTP系统还是OLAP系统降低对数据库功能的依赖严格遵从数据库三范式适当的考虑数据分块尽量保证记录的唯一标识存在2概念结构设计2.1E-R图设计E-R图分为局部E-R图和全局E-R图E-R模型基本符号实体的表示：长方形联系的表示：菱形，如1:1、1:n(m:1)、(m:n)属性的表示：椭圆形2.2确定实体与属性的原
2、则：能作为属性的尽量作为属性而不要划为实体作为属性的数据元素与实体之间的联系只能是1:n的联系作为属性的数据项不能再用其他属性加以描述，也不能与其他实体或属性发生联系。3逻辑结构设计3.1概念是将基本E-R图转换为与选用DBMS产品所支持的数据模型相符合的逻辑结构的过程。3.2逻辑结构设计的过程分为将概念结构转换为现有DBMS支持的关系、网状或层次模型中的某一种数据模型从功能和性能要求上对转换的模型进行评价，看它是否满足用户要求对数据模型进行优化3.3逻辑结构设计的原则一个实体型转换为一个关系模型，实体的属性就是关系的属性，实体的键就是关系的键；一个联系转换为一个关系模式，与该联系相连的每个实
3、体型的键以及联系的属性都转换为关系的属性。4配置数据库连接4.1步骤1.打开powerdesigner，依次点击：database-connect2.点击connect，打开对话窗口：3.然后点击Configuer按钮，弹出数据源配置ConfiguerDataConnections对话框：4.把页签切换到第三个ConnetionProfiles页签中，如果连接存在，选中就行，如果不存在，点击addDataSource数据库图标进行新增，弹出ConnectionProfileDefinition对话框此对话框中需要输入以下信息Connectionprofilename:输入数据库连接文件名,它会
4、自动加上后缀名.dcp;Directory:数据库连接文件存放路径；可以任意;Connetctiontype:选择JDBC;DBMStype:数据库类型选择Oracle;Servername:服务器名称；也相当于对应PL/SQL登陆页面的数据库；Databasename:数据库名字；Username:登陆数据库名字；Password:密码JDBCDriverclass:驱动类；只要下拉框选择就行；JDBCDriverJarURL:访问的服务器路径JDBCconnectionfiles:驱动包；需要指向ojdbc14.jar或者其他驱动的包的按钮路径；5.点击TestConnection按钮进行
5、连接测试；测试连接是否成功；成功会弹出成功或者失败消息框，测试成功后。点击确定按钮，返回数据源配置ConfiguerDataConnections对话框，列表中就会多出一个.dcp文件，点击确定即可。5导出数据库5.1步骤导出数据库表方法如下：1.启动PowerDesigner2.菜单:File-ReverseEngineer-Database出来NewPhysicalDataModel对话框,DBMS选择ORACLEVersion10g选项，其他可以选择默认值，点击“确定”按钮，弹出DatabaseReverseEngineeringOptions对话框：3.然后在下拉框中选择我们之前建立的
6、数据源文件，并再次输入数据库UserID和password，点击Connection即可连接至数据库，并得取所有的数据库对象，在此我们选择所有的Table。6PowerDesigner6.1描述PowerDesigner是Sybase公司的CASE工具集，使用它可以方便地对管理信息系统进行分析设计，他几乎包括了数据库模型设计的全过程。利用PowerDesigner可以制作数据流程图、概念数据模型、物理数据模型，还可以为数据仓库制作结构模型，也能对团队设计模型进行控制。他可以与许多流行的数据库设计软件，例如PowerBuilder，Delphi，VB等相配合使缩短开发时间和使系统设计更优化。po
7、werdesigner是能进行数据库设计的强大的软件，是一款开发人员常用的数据库建模工具。使用它可以分别从概念数据模型(ConceptualDataModel)和物理数据模型(PhysicalDataModel)两个层次对数据库进行设计。在这里，概念数据模型描述的是独立于数据库管理系统(DBMS)的实体定义和实体关系定义；物理数据模型是在概念数据模型的基础上针对目标数据库管理系统的具体化。7导入数据库7.1方法将视图切换到physical模型下，连接数据库：单击databasedatabaseconnection,弹出下面窗口，进行数据库的连接。链接完成后单击toolsforwardengin
8、eerschemagenerate，将弹出下面的窗口，单击generate，即可将物理模型导入数据库。8实体8.1实体间的关系实体间的三种关系：1.标识关系（identifyingrelationship）；2.多对多关系(many-to-manyrelationship)；3.非标识关系（non-identifyingrelationship）。标识关系是指把实体1中的主键作为实体2中的外键，且作为实体2的主键，非标识关系是指把实体1中的主键作为实体2中的外键，但不作为实体2的主键，8.2概念Erwin中的实体有两种:独立实体和依赖实体。实体被指定作为独立实体，或依赖实体，取决于其键的获得方
9、式。独立实体由方角盒来指定，独立实体不依赖于模型中任何其它实体来标识；依赖实体被指定为圆角盒，依赖实体依存于模型中的其它实体，修改实体名8.3修改实体名称方法一：单击实体名，按F2键可以对实体名称进行修改；方法二：右键单击欲进行修改的实体,选择EntityProperties；8.4方法三：双击实体修改。列的增删方法一：右键单击所选实体tiue,在弹出的trbts窗口中添加境蛐薷氖粜裕?方法二：单击所选实体，按tb键也可以进行添加，删除或修改操作。扩展阅读：北大青鸟S2sql数据库设计和高级查询总结第一章数据库的设计良好的数据库设计1、节省数据库的存储空间2、能够保证数据的完整性3、方便进行数
10、据库应用系统的开发糟糕的数据库设计1、效率低下2、更新和检索数据时会出现许多问题设计数据库的步骤1、需求分析阶段分析客户的业务和数据处理需求收集信息标识对象(实体)标识每个对象需要存储的详细信息标识对象之间的关系2、概要设计阶段绘制数据库的E-R模型图(实体关系图)3、详细设计阶段将E-R图转换为多张表,进行逻辑设计,并应用数据库设计的三大规范进行设计4、代码编写阶段映射基数1一对一2一对多3多对一4多对多实体关系图矩形表示实体集椭圆形表示属性菱形表示关系集直线用来连接设计数据库问题1信息重复2更新异常3插入异常(无法表示某些信息)4删除异常(丢失有用的信息)数据库设计的三大规范理论一第一范式
11、的目标是确保每列的原子性二第二范式的目标是确保表中的每列都和主键相关三第三范式的目标是确保每列都和主键直接相关,而不是间接相关(不能传递依赖)规范化和性能的关系在必要的情况下允许适当的数据冗余第二章数据库的实现一建库uesmastergoifexists(select*fromsysdatabaseswherename=数据库名字)dropdatabase数据库名-判断这个数据库名字是否存在,如果存在删除createdatabase数据库名onprimary(name=,filename=,size=,建立数据库的主文件如果需要建立次要文件(ndf)用逗号隔开maxsize=,filegrow
12、th=)logon(name=,filename=,size=,建立日志文件如果建立多个日志文件在后面用逗号隔开maxsize=,filegrowth=)go-批处理二建表ues数据库名字goifexists(select*fromsysobjectswherename=表名)droptable表名-判断是否有这个名字的表createtable表名(字段名数据类型列的特征如stuidintidentity(1,1)notnull-identity表示是否为自动增长)go三加约束1主键约束(primarykeyconstraint)-constraint约束altertable表名addcons
13、traint约束名(PK_*)primarykey(约束的字段)2唯一约束(uniqueconstraint)altertable表名addconstraint约束名(uq_*)unique(约束的字段)3检查约束(checkconstraint)altertable表名addconstraint约束名(ck_*)check(约束的条件)4默认约束(defaultconstraint)altertable表名addconstraint约束名(df_*)default(默认值)for约束的字段5外建约束(foreignkeyconstraint)altertable表名addconstraint
14、约束名(fk_*)foreignkey(约束的字段)references主表的表名(主表的字段)总结createdatabase建库createtable建表addconstraint加约束dropdatabase删库droptable删表dropconstraint删约束usemasterselect*fromsysdatabases判断是否有库名select*fromsysobjects判断是否有表名完整性1实体完整性(保证数据是唯一的如主键唯一键标示列)2引用完整性(保证两表数据一致如外键)3域完整性(保证数据的准确性如检查约束默认约束非空)三层安全模型1登陆帐号-决定登陆服务windo
15、ws身份验证execsp_grantloginwindows域名域帐户sql身份验证execsp_addlogin帐户名,密码2数据库用户-访问数据库use数据库名goexecsp_grantdbaccess登陆帐户,(数据库用户)如果不写数据库用户默认为登陆帐号名3权限-在数据库里的操作use数据库名gogrant权限(增,删,改,查,建表(createtable)on表名to用户名第三章T-SQL编成使用变量一局部变量declare变量名类型赋值:1、set变量名=值2、select变量名=值二全局变量error最后一个T-SQL错误的错误号identity最后一次插入的标示值langua
16、ge当前使用的语言的名称max_connections可以创建的同时连接的最大数目rowcount受上一个sql语句影响的行数servername本地服务器的名称servicename该计算机上的sql服务的名称timeticks当前计算机上每刻度的微秒数transcount当前连接打开的事物数versionsqlserver的版本信息输出语句1、print局部变量或字符串2、select局部变量as自定义列名(查询语句的特殊应用)逻辑控制语句1、if(条件)begin-开头语句end-结束else-为可选begin-开头语句end-结束2、while(条件)语句break3、casewhen
17、条件1then结果1when条件2then结果2else其他结果end批处理语句以一条命令的方式来处理一组命令的过程称为批处理批处理的好处就是能够简化数据库的管理第四章高级查询一简单子查询select*from表1where字段1(子查询)它等于一个等值内连接将子查询和比较运算符联合使用，必须保证子查询返回的值不能多与一个二in和notin子查询in后面的子查询可以返回多条记录select*from表1where字段1in(notin)(子查询)三existsnotexists子查询ifexists(子查询)语句如果子查询的结果非空，则exists(子查询)将返回真(true),否则返回假(f
18、alse)第五章事务、索引和试图一事务：是一个整体，要么都成功，要么都失败事务时作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性1原子性(atomicity)事务是一个完整的操作。事务的各元素是不可分得2一致性(consistency)当事务完成时，数据必须处于一致状态3隔离性(isolation)对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其它事务4持久性(durability)事务完成之后，它对系统的影响是永久的简称ACID二如何创建事务开始事务：begintransaction提交事务：committransactio
19、n回滚(撤销)事务：rollbacktransaction事务的分类有3种1显示事务:用begintransaction明确指定事务的开始2隐式事务:通过设置setimplicit_transactionon语句,将隐式事务模式设置为打开3自动提交事务:这是sqlserver的默认模式它将每条单独的T-SQL语句视为一个事务.如果成功执行,则自动提交.如果错误,则自动回滚.在这里用的了全局变量errordeclareerrorsumintseterrorsum=0语句seterrorsum=errorsum+error三什么是索引索引,它是sqlserver编排数据的内部方法,相当于字典中的目
20、录索引页,数据库中存储索引的数据页通过索引可以大大提高数据库的检索速度,改善数据库性能加快查询的速度(通过平衡二叉树)增删改速度慢,所需要的空间大索引可分为3类1唯一索引:唯一索引不允许两行具有相同的索引值.创建了唯一约束,将自动创建唯一索引,为了最佳性能,建议使用主键的约束2主键索引:在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型.3聚集索引:在聚集索引中,表中各行的物理顺序与健值的逻辑(索引)顺序相同.非聚集索引:表中各行的物理顺序与键值的逻辑顺序不匹配.在sqlserver中,一个表只能创建一个聚集索引,但可以有多个非聚集索引,设置某列为主键,该列就默认
21、为聚集索引四建立索引createuniqueclusterednonclusteredindexix_(name)on表名(字段名)withfillfactor=301unique指定唯一索引可选2clusterednonclustered指定是聚集索引还是非聚集索引,可选3fillfactor表示填充因子,指定0-100的值,该值指示索引页填满的空间所占的比例(百分比越小,所留得空白空间越大)什么情况下可以建立索引1该列用于频繁搜索2该列用于对数据进行排序请不要对下面的列创建索引1列中仅包含几个不同的值2表中仅包含几行.五什么是试图试图使另一种查看数据库中一个或多个表中的数据的方法.它是一种
22、虚拟表试图通常用来进行以下三种操作1筛选表中的行2防止未经许可的用户访问敏感数据3将多个物理数据表抽象为一个逻辑数据表(降低数据库的复杂程度)好处1对最终用户的好处结果更容易理解获得数据更容易2对开发人员的好处限制数据检索更容易维护应用程序更方便六如何创建试图createviewview_(name)as(select语句)第六章存储过程一什么是存储过程存储过程(procedure)类似于java语言中的方法，它是sql语句和控制流语句的预编译集合。存储过程的优点1允许模块化程序设计2允许更快地执行3减少网络流量4可作为安全机制使用存储过程分为以下两类1系统存储过程2用户自定义的存储过程3扩展
23、存储过程(系统定义好的，以xp_开头)二常用的系统存储过程所有的系统存储过程的名称都以sp_开头sp_databases(列出服务器上的所有数据库)sp_helpdb(报告有关指定数据库或所有数据库的信息)sp_renamedb(更改数据库的名称)sp_tables(返回当前环境下可查询的对象的列表)sp_columns(返回某个表列的信息)sp_help(查看某个表的所有信息)sp_helpconstraint(查看某个表的约束)sp_stored_procedures(列出当前环境中的所有存储过程)sp_password(添加或修改登陆帐号的密码)sp_helptext(显示默认值、未加密
24、的存储过程、用户定义的存储过程、触发器或试图的实际文本)扩展参数execxp_cmdshelldos命令no_output三用户定义的存储过程1创建不带参数的存储过程createprocedure存储过程名参数1数据类型默认值output参数2数据类型默认值outputassql语句2创建步带参数的存储过程1输入参数可以在调用时向存储过程传递参数，此类参数可以用来在存储过程中传入值2输出参数(引用传递)如果希望返回值，则可以使用输出参数，输出参数后有output标记，执行存储过程后，将把返回值存放在输出参数中，可供其它T-SQL语句读取访问3创建带输出参数的存储过程如果希望调用存储过程后，返回
25、一个或多个值，这时需要使用输出(output)参数。使用输出参数创建存储过程时，在参数后面需要跟随output关键字，调用时也需要在变量后跟随output关键字四处理错误信息如果存储过程变得越来越复杂，则需要在存储过程中加入错误检查语句raiserror(msg_id|msg_str,severity,statewithoption,.n)例raiserror(及格线错误，请指定0-100之间的分数，统计中断退出,16,1)msg_id在sysmessages系统表中指定的用户定义错误信息msg_str用户定义的特定信息，最长255个字符severity与特定信息相关联，表示用户定义的严重性级别。用户可使用的级别为0-18级(一般下为16)19-25级是为sysadmin固定角色的成员预留的，并需要指定withlog选项20-为致命错误state表示错误的状态，是1-127的值option指示是否将错误记录到服务器错误日至中第 12 页 共 12 页