常用脚本(ORACLE常用脚本(1)文章出处:转载作者:不详发布时间:2006-01-061,查看表空间的名称及大小selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_sizefromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;2,查看表空间物理文件的名称及大小selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;3,查看回滚段名称及大小selectsegment_name,tablespace_name,r.status,(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id=v.usn(+)orderbysegment_name;4,查看控制文件selectnamefromv$controlfile;5,查看日志文件selectmemberfromv$logfile;6,查看表空间的使用情况selectsum(bytes)/(1024*1024)asfree_space,tablespace_namefromdba_free_spacegroupbytablespace_name;SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,(B.BYTES*100)/A.BYTES"%USED",(C.BYTES*100)/A.BYTES"%FREE"
FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREECWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;7,查看数据库库对象selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowner,object_type,status;8,查看数据库的版本SelectversionFROMProduct_component_versionWhereSUBSTR(PRODUCT,1,6)='Oracle';9,查看数据库的创建日期和归档方式SelectCreated,Log_Mode,Log_ModeFromV$Database;10,查看当前所有对象SQL>select*fromtab;11,建一个和a表结构一样的空表SQL>createtablebasselect*fromawhere1=2;SQL>createtableb(b1,b2,b3)asselecta1,a2,a3fromawhere1=2;12,察看数据库的大小,和空间使用情况SQL>coltablespaceformata20SQL>selectb.file_id文件ID,b.tablespace_name表空间,b.file_name物理文件名,b.bytes总字节数,(b.bytes-sum(nvl(a.bytes,0)))已使用,sum(nvl(a.bytes,0))剩余,sum(nvl(a.bytes,0))/(b.bytes)*100剩余百分比fromdba_free_spacea,dba_data_filesbwherea.file_id=b.file_idgroupbyb.tablespace_name,b.file_name,b.file_id,b.bytesorderbyb.tablespace_name/dba_free_space--表空间剩余空间状况
dba_data_files--数据文件空间占用情况13,查看现有回滚段及其状态SQL>colsegmentformata30SQL>SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROMDBA_ROLLBACK_SEGS;14,查看数据文件放置的路径SQL>colfile_nameformata50SQL>selecttablespace_name,file_id,bytes/1024/1024,file_namefromdba_data_filesorderbyfile_id;15,显示当前连接用户SQL>showuser16,把SQL*Plus当计算器SQL>select100*20fromdual;17,连接字符串SQL>select列1||列2from表1;SQL>selectconcat(列1,列2)from表1;18,查询当前日期SQL>selectto_char(sysdate,'yyyy-mm-dd,hh24:mi:ss')fromdual;19,用户间复制数据SQL>copyfromuser1touser2createtable2usingselect*fromtable1;20,视图中不能使用orderby,但可用groupby代替来达到排序目的SQL>createviewaasselectb1,b2frombgroupbyb1,b2;21,通过授权的方式来创建用户
SQL>grantconnect,resourcetotestidentifiedbytest;SQL>conntest/test常用脚本(ORACLE常用脚本(2)文章出处:转载作者:不详发布时间:2006-01-06一,ORACLE的表的分类:1,REGULARTABLE:普通表,ORACLE推荐的表,使用很方便,人为控制少.2,PARTITIONEDTABLE:分区表,人为控制记录的分布,将表的存储空间分为若干独立的分区,记录按一定的规则存储在分区里.适用于大型的表.二,建表1CREATETABLE表名(EMPNONUMBER(2),NAMEVARCHAR2(20))PCTFREE20PCTUSED50STORAGE(INITIAL200KNEXT200KMAXEXTENTS200PCTINCREASE0)TABLESPACE表空间名称[LOGGING|NOLOGGING]所有的对表的操作都要记入REDOLOG,ORACLE建议使用NOLOGGING;[CACHE|NOCACHE]:是否将数据按照一定的算法写入内存.2,关于PCTFREE和PCTUSEDA,行迁移和行链接B,PCTFREE:制止INSERT,为UPDATE留FREE空间C,PCTUSED:为恢复INSERT操作,而设定的.三,拷贝一个已经存在的表:CREATETABLE新表名STORAGE(..)TABLESPACE表空间ASSELECT*FROM老表名;当老表存在约束,触发的时候,不会拷过去.四,修改表的参数ALTERTABLE名称PCTFREE20PCTUSED50STOAGE(MAXEXTENTS1000);五,手工分配空间:ALTERTABLE名称ALLOCATEEXTENT(SIZE500KDATAFILE'..');1,SIZE选项,按照NEXT分配2,表所在表空间与所分配的数据文件所在的表空间必须一样.六,水线1,水线定义了表的数据在一个BLOCK中所达到的最高的位置.2,当有新的记录插入,水线增高
3,当删除记录时,水线不回落4,减少查询量七,如何回收空间:ALTERTABLE名称DEALLOCATEUNUSED[KEEP4[M|K]]1,当空间分配过大时,可以使用本命令2,如果没有加KEEP,回收到水线3,如果水线《MINEXTENTS的大小回收到MINEXTENTS所指定的大小八,TRUNCATE一个表TRUNCATETABLE表名,表空间截取MINEXTENT,同时水线重置.九,DROP一个表DROPTABLE表名[CASCADECONSTRAINTS]当一个表含有外键的时候,是不可以直接DROP的,加CASCADECONSRIANTS将外键等约束一并删掉.十,信息获取1,dba_object2dba_tables:建表的参数3DBA_SEGMENTS:组合查询的连接字段:DBA_TABLES的table_name+dba_ojbect的object_name+dba_segments的SEGMENT_NAME用户的管理一,ORACLE的安全域1,TABLESPACEQUOTAS:表空间的使用定额2,DEFAULTTABLESPACE:默认表空间3,TEMPORARYTABLESPACE:指定临时表空间.4,ACCOUNTLOCKING:用户锁5,RESOURCELIMITE:资源限制6,DIRECTPRIVILEGES:直接授权7,ROLEPRIVILEGES:角色授权先将应用中的用户划为不同的角色,二,创建用户时的清单:1,选择一个用户名称和检验机制:A,看到用户名,实际操作者是谁,业务中角色.2,选择合适的表空间:3,决定定额:4,口令的选择:5,临时表空间的选择:先建立一个临时表空间,然后在分配.不分配,使用SYSTEM表空间6,CREATEUSER7,授权:A,用户的工作职能B,用户的级别
三,用户的创建:1,命令:CREATEUSER名称IDENTIFIEDBY口令DEFAULTTABLESPACE默认表空间名TEMPOARAYTABLESPACE临时表空间名QUOTA15MON表空间名[PASSWORDEXPIRE]:当用户第一次登陆到ORACLE,创建时所指定的口令过期失效,强迫用户自己定义一个新口令.[ACCOUNTLOCK]:加用户锁QUOTAUNLIMITEDONTABLESPACE:不限制,有多少有多少.[PROFILE名称]:受PROFILE文件的限制.四,如何控制用户口令和用户锁1,强迫用户修改口令:ALTERUSER名称IDENTIFIEDBY新口令PASSWORDEXPIRE;2,给用户加锁:ALTERUSER名称ACCOUNT[LOCK|UNLOCK]3,注意事项:A,所有操作对当前连接无效B,1的操作适用于当用户忘记口令时.五,更改定额1,命令:ALTERUSER名称QUOTA0ON表空间名ALTERUSER名字QUOTA(数值)K|M|UNLIMITEDON表空间名;2,使用方法:A,控制用户数据增长B,当用户拥有一定的数据,而管理员不想让他在增加新的数据的时候.C,当将用户定额设为零的时候,用户不能创建新的数据,但原有数据仍可访问.六,DROP一个USER1,DROPUSER名称适合于删除一个新的用户2,DROPUSER名称CASCADE:删除一个用户,将用户的表,索引等都删除.3,对连接中的用户不好用.
七,信息获取:1,DBA_USERS:用户名,状态,加锁日期,默认表空间,临时表空间2,DBA_TS_QUOTAS:用户名,表空间名,定额.两个表的连接字段:USERNAMEGRANTCREATESESSIONTO用户名PROFILE的管理(资源文件)一,PROFILE的管理内容:1,CPU的时间
2,I/O的使用3,IDLETIME(空闲时间)4,CONNECTTIME(连接时间)5,并发会话数量6,口令机制:二,DEFAULTPROFILE:1,所有的用户创建时都会被指定这个PROFILE2,DEFAULTPROFILE的内容为空,无限制三,PROFILE的划分:1,CALL级LIMITE:对象是语句:当该语句资源使用溢出时:A,该语句终止B,事物回退C,SESSION连接保持2,SESSION级LIMITE:对象是:整个会话过程溢出时:连接终止四,如何管理一个PROFILE1,CREATEPROFILE2,分配给一个用户3,象开关一样打开限制.五,如何创建一个PROFILE:1,命令:CREATEPROFILE名称LIMITSESSION_PER_USER2CPU_PER_SESSION1000IDLE_TIME60CONNECT_TIME480六,限制参数:1,SESSION级LIMITE:CPU_PER_SESSION:定义了每个SESSION占用的CPU的时间:2,SESSION_PER_USER:每个用户的并发连接数3,CONNECT_TIME:一个连接的最长连接时间(分钟)4,LOGICAL_READS_PER_SESSION:一次读写的逻辑块的数量5,CALL级LIMITECPU_PER_CALL:每个语句占用的CPU时间LOGICAL_READS_PER_CALL:
(1/100秒)
七,分配给一个用户:CREATEUSER名称......PROFILE名称ALTERUSER名称PROFILE名称八,打开资源限制:1,RESOURCE_LIMT:资源文件中含有2,ALTERSYSTEMSETRESOURCE_LIMIT=TRUE;3,默认不打开九,修改PROFIE的内容:1,ALTERPROFILE名称参数新值2,对于当前连接修改不生效.DROP一个PROFILE1,DROPPROFILE名称删除一个新的尚未分配给用户的PROFILE,2,DROPPROFILE名称CASCADE3,注意事项A,一旦PROFILE被删除,用户被自动加载DEFAULTPROFILEB,对于当前连接无影响C,DEFAULTPROFILE不可以被删除信息获取:1,DBA_USERS:用户名,PROFILE2,DBA_PROFILES:PROFILE及各种限制参数的值每个用户的限制:PROFILE(关键字段)PROFILE的口令机制限制1,限制内容A,限制连续多少次登录失败,用户被加锁B,限制口令的生命周期C,限制口令的使用间隔2,限制生效的前提:A,RESOURCE_LIMIT:=TRUEBORACLE\RDBMS\ADMIN\UTLPWDMG.SQL3,如何创建口令机制:CREATEPROFILE名称SESSIONS_PER_USER.....password_life_time30failed_log_attempts3
password_reuse_time34,参数的含义:AFAILED_LOGIN_ATTEMPTS:当连续登陆失败次数达到该参数指定值时,用户加锁BPASSWORD_LOCK_TIME:加锁天数CPASSWORD_LIFE_TIME:口令的有效期(天)DPASSWORD_GRACE_TIME:口令修改的间隔期(天)EPASSWORD_REUSE_TIME:口令被修改后原有口令隔多少天被重新使用.FPASSWORD_REUSE_MAX:口令被修改后原有口令被修改多少次被重新使用常用脚本(ORACLE常用脚本(4)文章出处:转载作者:不详发布时间:2006-01-06rem这需要统计某个具体用户的"Table,index,column,constraits"rem================================================================rem全部表-列定义table_cols.txtsetlin110pages3000columntable_nameformata30columndata_typeformata12columndata_defaultformata8columncolumn_nameformata22columnCidformat999columnLenformat9999columnPrecformat99columnScaleformat99selectTABLE_NAME,COLUMN_ID"Cid",COLUMN_NAME,DATA_TYPE,DATA_LENGTH"Len",nvl(DATA_PRECISION,'-1')"Prec",nvl(DATA_SCALE,'-1')"Scale",NULLABLE,DATA_DEFAULTfromUSER_TAB_COLUMNS;rem========TAB=============select*fromtab;spooloffspooluser_indexes.txtrem================================================================rem全部索引定义user_index.txtcolumntable_nameformata22columnindex_nameformata28columnindex_typeformata7columncolumn_nameformata18column#format99columnInitformat999999;selecta.table_name,t.cache"C",a.index_name,
column_position"#",column_name,UNIQUENESS,a.INITIAL_EXTENT/1024"Init"fromuser_indexesa,user_ind_columnsc,user_tablestwherec.INDEX_NAME=a.INDEX_NAMEanda.table_name=t.table_nameorderbya.table_name,a.index_name,column_position;spooloffspooluser_Obj_Table_Index.txtrem================================================================rem用户对象,表和索引userObj_Table_Index.txtsetlin111pages333columntable_nameformata24columnindex_nameformata32columntablespace_namea12columnInitformat999999;rem由于用户要关心的是我自己的详细数据的存放位置,下面分别得出index,tablesselecttablespace_name,table_name,cache,initial_extent/1024"Init"fromuser_tablesorderbytablespace_name,table_name;selecttablespace_name,table_name,index_name,initial_extent/1024"Init"fromuser_indexesorderbytablespace_name,table_name,index_name;spooloffspooluser_constraints.txtrem================================================================rem全部表-列约束_user_constraints.txtcolumnCONSTRAINT_NAMEformata30columnTABLE_NAMEformata30columnr_CONSTRAINT_NAMEformata20selectCONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME,DELETE_RULEfromuser_constraintsorderbyCONSTRAINT_TYPE,TABLE_NAME;spooloffspoolc:\user_index1rebld.sqlrem================================================================rem重建全部索引remselect'alterindex'||index_name||'rebuild;'fromuser_indexesremwheretable_name='GWNEWS';select'alterindex'||INDEX_NAME||'rebuildtablespaceindexes;'from
user_indexes;spooloffrem@index1rebld.sqlspoolc:\user_sources.sqlrem================================================================rem全部代码,主要是procedurecolumnnameformata22;columntextformata77;breakonname;selecttext,namefromuser_source;spooloffrem===============EndofFile==================*************查看当前用户使用的操作****************************SELECTa.machine,a.terminal,a.osuser,a.username,b.sql_textfromv$sessiona,v$sqlareabwherea.sql_address=b.addressorderbyb.address***************************************************1.监控事例的等待selectevent,sum(decode(wait_Time,0,0,1))"Prev",sum(decode(wait_Time,0,1,0))"Curr",count(*)"Tot"fromv$session_Waitgroupbyeventorderby4;2.回滚段的争用情况selectname,waits,gets,waits/gets"Ratio"fromv$rollstata,v$rollnamebwherea.usn=b.usn;3.监控表空间的I/O比例selectdf.tablespace_namename,df.file_name"file",f.phyrdspyr,f.phyblkrdpbr,f.phywrtspyw,f.phyblkwrtpbwfromv$filestatf,dba_data_filesdfwheref.file#=df.file_idorderbydf.tablespace_name;4.监控文件系统的I/O比例
selectsubstr(a.file#,1,2)"#",substr(a.name,1,30)"Name",a.status,a.bytes,b.phyrds,b.phywrtsfromv$datafilea,v$filestatbwherea.file#=b.file#;5.在某个用户下找所有的索引selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_namefromuser_ind_columns,user_indexeswhereuser_ind_columns.index_name=user_indexes.index_nameanduser_ind_columns.table_name=user_indexes.table_nameorderbyuser_indexes.table_type,user_indexes.table_name,user_indexes.index_name,column_position;6.监控SGA的命中率selecta.value+b.value"logical_reads",c.value"phys_reads",round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"fromv$sysstata,v$sysstatb,v$sysstatcwherea.statistic#=38andb.statistic#=39andc.statistic#=40;7.监控SGA中字典缓冲区的命中率selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio",(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"fromv$rowcachewheregets+getmisses<>0groupbyparameter,gets,getmisses;8.监控SGA中共享缓存区的命中率,应该小于1%selectsum(pins)"TotalPins",sum(reloads)"TotalReloads",sum(reloads)/sum(pins)*100libcachefromv$librarycache;selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent"fromv$librarycache;9.显示所有数据库对象的类别和大小
selectcount(name)num_instances,type,sum(source_size)source_size,sum(parsed_size)parsed_size,sum(code_size)code_size,sum(error_size)error_size,sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size)size_requiredfromdba_object_sizegroupbytypeorderby2;10.监控SGA中重做日志缓存区的命中率,应该小于1%SELECTname,gets,misses,immediate_gets,immediate_misses,Decode(gets,0,0,misses/gets*100)ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100)ratio2FROMv$latchWHEREnameIN('redoallocation','redocopy');11.监控内存和硬盘的排序比率,最好使它小于.10,增加sort_area_sizeSELECTname,valueFROMv$sysstatWHEREnameIN('sorts(memory)','sorts(disk)');12.监控当前数据库谁在运行什么SQL语句SELECTosuser,username,sql_textfromv$sessiona,v$sqltextbwherea.sql_address=b.addressorderbyaddress,piece;13.监控字典缓冲区SELECT(SUM(PINS-RELOADS))/SUM(PINS)"LIBCACHE"FROMV$LIBRARYCACHE;SELECT(SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)"ROWCACHE"FROMV$ROWCACHE;SELECTSUM(PINS)"EXECUTIONS",SUM(RELOADS)"CACHEMISSESWHILEEXECUTING"FROMV$LIBRARYCACHE;后者除以前者,此比率小于1%,接近0%为好.SELECTSUM(GETS)"DICTIONARYGETS",SUM(GETMISSES)"DICTIONARYCACHEGETMISSES"FROMV$ROWCACHE14.找ORACLE字符集select*fromsys.props$wherename='NLS_CHARACTERSET';
15.监控MTSselectbusy/(busy+idle)"sharedserversbusy"fromv$dispatcher;此值大于0.5时,参数需加大selectsum(wait)/sum(totalq)"dispatcherwaits"fromv$queuewheretype='dispatcher';selectcount(*)fromv$dispatcher;selectservers_highwaterfromv$mts;servers_highwater接近mts_max_servers时,参数需加大16.碎片程度selecttablespace_name,count(tablespace_name)fromdba_free_spacegroupbytablespace_namehavingcount(tablespace_name)>10;altertablespacenamecoalesce;altertablenamedeallocateunused;createorreplaceviewts_blocks_vasselecttablespace_name,block_id,bytes,blocks,'freespace'segment_namefromdba_free_spaceunionallselecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;select*fromts_blocks_v;selecttablespace_name,sum(bytes),max(bytes),count(block_id)fromdba_free_spacegroupbytablespace_name;查看碎片程度高的表SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);17.表,索引的存储情况检查selectsegment_name,sum(bytes),count(*)ext_quanfromdba_extents
wheretablespace_name='&;tablespace_name'andsegment_type='TABLE'groupbytablespace_name,segment_name;selectsegment_name,count(*)fromdba_extentswheresegment_type='INDEX'andowner='&;owner'groupbysegment_name;18,找使用CPU多的用户session12是cpuusedbythissessionselecta.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100valuefromv$sessiona,v$processb,v$sesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;19,查看LockSELECTsn.username,m.sid,m.type,DECODE(m.lmode,0,'None',1,'Null',2,'RowShare',3,'RowExcl.',4,'Share',5,'S/RowExcl.',6,'Exclusive',lmode,ltrim(to_char(lmode,'990')))lmode,DECODE(m.request,0,'None',1,'Null',2,'RowShare',3,'RowExcl.',4,'Share',5,'S/RowExcl.',6,'Exclusive',request,ltrim(to_char(m.request,'990')))request,m.id1,m.id2FROMv$sessionsn,v$lockmWHERE(sn.sid=m.sidANDm.request!=0)OR(sn.sid=m.sidANDm.request=0ANDlmode!=4AND(id1,id2)IN(SELECTs.id1,s.id2FROMv$locksWHERErequest!=0
ANDs.id1=m.id1ANDs.id2=m.id2))ORDERBYid1,id2,m.request;
selectl.sid,s.serial#,s.username,s.terminal,decode(l.type,'RW','RW-RowWaitEnqueue','TM','TM-DMLEnqueue','TX','TX-TransEnqueue','UL','UL-User',l.type||'System')res,substr(t.name,1,10)tab,u.nameowner,l.id1,l.id2,decode(l.lmode,1,'NoLock',2,'RowShare',3,'RowExclusive',4,'Share',5,'ShrRowExcl',6,'Exclusive',null)lmode,decode(l.request,1,'NoLock',2,'RowShare',3,'RowExcl',4,'Share',5,'ShrRowExcl',6,'Exclusive',null)requestfromv$lockl,v$sessions,sys.user$u,sys.obj$twherel.sid=s.sidands.type!='BACKGROUND'andt.obj#=l.id1andu.user#=t.owner#--第二条语句比较有效.20,显示表空间的使用情况:coltsnameformata16justifycheading'Tablespace'colnfragsformat999,990justifycheading'Free|Frags'colmxfragformat999,999,990justifycheading'Largest|Frag(KB)'coltotsizformat999,999,990justifycheading'Total|(KB)'colavasizformat999,999,990justifycheading'Available|(KB)'colpctusdformat990justifycheading'Pct|Used'selecttotal.tablespace_nametsname,count(free.bytes)nfrags,nvl(max(free.bytes)/1024,0)mxfrag,
total.bytes/1024totsiz,nvl(sum(free.bytes)/1024,0)avasiz,(1-nvl(sum(free.bytes),0)/total.bytes)*100pctusdfromdba_data_filestotal,dba_free_spacefreewheretotal.tablespace_name=free.tablespace_name(+)groupbytotal.tablespace_name,total.bytes;
selecttextfromall_sourcewherename='DBMS_RANDOM'
andtype='PACKAGE'orderbyline;
数据库设计三大范式应用实例剖析文章出处:转载作者:不详发布时间:2005-12-18数据库的设计范式是数据库设计所需要满足的规范,满足这些规范的数据库是简洁的,结构明晰的,同时,不会发生插入(insert),删除(delete)和更新(update)操作异常.反之则是乱七八糟,不仅给数据库的编程人员制造麻烦,而且面目可憎,可能存储了大量不需要的冗余信息.设计范式是不是很难懂呢?非也,大学教材上给我们一堆数学公式我们当然看不懂,也记不住.所以我们很多人就根本不按照范式来设计数据库.实质上,设计范式用很形象,很简洁的话语就能说清楚,道明白.本文将对范式进行通俗地说明,并以笔者曾经设计的一个简单论坛的数据库为例来讲解怎样将这些范式应用于实际工程.范式说明第一范式(1NF):数据库表中的字段都是单一属性的,不可再分.这个单一属性由基本类型构成,包括整型,实数,字符型,逻辑型,日期型等.例如,如下的数据库表是符合第一范式的:字段1字段2字段3字段4
而这样的数据库表是不符合第一范式的:字段1字段2字段3.1字段3字段3.2字段4
很显然,在当前的任何关系数据库管理系统(DBMS)中,傻瓜也不可能做出不符合第一范式的数据库,因为这些DBMS不允许你把数据库表的一列再分成二列或多列.因此,你想在现有的DBMS中设计出不符合第一范式的数据库都是不可能的.第二范式(2NF):数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖(部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况),也即所有非关键字段都完全依赖于任意一组候选关键字.假定选课关系表为SelectCourse(,,年龄,课程名称,成绩,学分),关键字为组合关键字(,课程名称),因为存在如下决定关系:(,课程名称)→(,年龄,成绩,学分)这个数据库表不满足第二范式,因为存在如下决定关系:(课程名称)→(学分)()→(,年龄)即存在组合关键字中的字段决定非关键字的情况.由于不符合2NF,这个选课关系表会存在如下问题:(1)数据冗余:同一门课程由n个学生选修,"学分"就重复n-1次;同一个学生选修了m门课程,和年龄就重复了m-1次.(2)更新异常:若调整了某门课程的学分,数据表中所有行的"学分"值都要更新,否则会出现同一门课程学分不同的情况.(3)插入异常:假设要开设一门新的课程,暂时还没有人选修.这样,由
于还没有""关键字,课程名称和学分也无法记录入数据库.(4)删除异常:假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除.但是,与此同时,课程名称和学分信息也被删除了.很显然,这也会导致插入异常.把选课关系表SelectCourse改为如下三个表:学生:Student(,,年龄);课程:Course(课程名称,学分);选课关系:SelectCourse(,课程名称,成绩).这样的数据库表是符合第二范式的,消除了数据冗余,更新异常,插入异常和删除异常.另外,所有单关键字的数据库表都符合第二范式,因为不可能存在组合关键字.第三范式(3NF):在第二范式的基础上,数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式.所谓传递函数依赖,指的是如果存在"A→B→C"的决定关系,则C传递函数依赖于A.因此,满足第三范式的数据库表应该不存在如下依赖关系:关键字段→非关键字段x→非关键字段y假定学生关系表为Student(,,年龄,所在学院,地点,电话),关键字为单一关键字"",因为存在如下决定关系:()→(,年龄,所在,地点,电话)这个数据库是符合2NF的,但是不符合3NF,因为存在如下决定关系:()→(所在)→(地点,电话)即存在非关键字段"地点","电话"对关键字段"学号"的传递函数依赖.
它也会存在数据冗余,更新异常,插入异常和删除异常的情况,读者可自行分析得知.把学生关系表分为如下两个表:学生:(,,年龄,所在);:(,地点,电话).这样的数据库表是符合第三范式的,消除了数据冗余,更新异常,插入异常和删除异常.鲍依斯-科得范式(BCNF):在第三范式的基础上,数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合第三范式.假设仓库管理关系表为StorehouseManage(仓库ID,存储物品ID,管理员ID,数量),且有一个管理员只在一个仓库工作;一个仓库可以存储多种物品.这个数据库表中存在如下决定关系:(仓库ID,存储物品ID)→(管理员ID,数量)(管理员ID,存储物品ID)→(仓库ID,数量)所以,(仓库ID,存储物品ID)和(管理员ID,存储物品ID)都是StorehouseManage的候选关键字,表中的唯一非关键字段为数量,它是符合第三范式的.但是,由于存在如下决定关系:(仓库ID)→(管理员ID)(管理员ID)→(仓库ID)即存在关键字段决定关键字段的情况,所以其不符合BCNF范式.它会出现如下异常情况:(1)删除异常:当仓库被清空后,所有"存储物品ID"和"数量"信息被删除的同时,"仓库ID"和"管理员ID"信息也被删除了.(2)插入异常:
当仓库没有存储任何物品时,无法给仓库分配管理员.(3)更新异常:如果仓库换了管理员,则表中所有行的管理员ID都要修改.把仓库管理关系表分解为二个关系表:仓库管理:StorehouseManage(仓库ID,管理员ID);仓库:Storehouse(仓库ID,存储物品ID,数量).这样的数据库表是符合BCNF范式的,消除了删除异常,插入异常和更新异常.
