数据库系统基础教程

1、会计学1数据库系统基础教程数据库系统基础教程23数 据 库 系 统 基 础 教 程数 据 库 系 统 基 础 教 程56数据库数据库最终用户最终用户应用系统应用系统应用开发工具应用开发工具DBMS操作系统操作系统数据库管理员数据库管理员DBA数据库系统构成数据库系统构成应用程序员应用程序员789定长记录定长记录10accountNOBalanceType123451000.00Savings678902846.92Checking属性属性元组元组1112数据数据元数据元数据存储存储管理程序管理程序查询查询处理程序处理程序事务事务管理程序管理程序模式更新模式更新更新更新查询查询131415161

2、71820212223242526想法想法需求需求ODLE / R关系关系RDBMSOODBMS2728293031记录结构类型记录结构类型32333435NNN136373839MoviesStarsStars-inlenghtfilmTypetitleyearnameaddress40MoviesStarsStars-inStudiosPresidentsRunsMoviesStudiosOwns41StarsMoviesContractsStudios一个制片公司与一位特定的影星签约来演一部特定的电影一个制片公司与一位特定的影星签约来演一部特定的电影 42Sequel-ofMoviesO

3、riginalSequelStarsMoviesContractsStudiosStudio of starProducing studio43StarsMoviesContractsStudiossalary4445464748495051525354MoviesStudiosOwns55CrewsUnit-ofStudiosnumbernameaddr56575859616263646566SnoSnoSnameSnameSsexSsexSageSagesdeptsdept9500195001张三张三男男2525CSCS9500295002李四李四女女2424CSCS9610196101王

4、五王五男男2323MAMA9600196001赵六赵六男男2323CSCS关系关系( 表表 )属性属性(列、字段列、字段)元组元组(行、记录行、记录)域域(string,男，女男，女)Student ( sno, sname, ssex, sage, sdept )676869707172737475767778798081828384858687888990919293titleyearlengthfilmTypestudioNamestarNameStar Wars1977124colorFoxCarrie FisherStar Wars1977124colorFoxMark Hamill

5、Star Wars1977124colorFoxHarrison FordMighty Ducks1991104colorDisneyEmilio EstevezWaynes World199195colorParamountDana CarveyWaynes World199295colorParamountMike Meyers冗余冗余!更新复杂更新复杂!删除异常删除异常!94959697snocnogradesname完全依赖完全依赖非完全依赖非完全依赖98snosdeptsnamedept_manager99sctstcsct3NF，sctBCNF100101BC C CF FB BF

6、 FA AF F102103namestreetcitytitleyearC.Fisher123 maple St.HollywoodStar Wars1977C.Fisher5 Locust Ln.MailbuStar Wars1977C.Fisher123 maple St.HollywoodEmpire Strikes Back1980C.Fisher5 Locust Ln.MailbuEmpire Strikes Back1980C.Fisher123 maple St.HollywoodReturn of the Jedi1983C.Fisher5 Locust Ln.MailbuR

7、eturn of the Jedi1983104XYZta1aiai+1ajaj+1anua1aibi+1bjbj+1bnva1aiai+1ajbj+1bnwa1aibi+1bjaj+1an105106XYZta1aiai+1ajaj+1anwa1aibi+1bjaj+1anua1aibi+1bjbj+1bnva1aiai+1ajbj+1bn107namestreetcitytitleyearC.Fisher123 maple St.HollywoodStar Wars1977C.Fisher123 maple St.HollywoodEmpire Strikes Back1980C.Fish

8、er123 maple St.HollywoodReturn of the Jedi1983C.Fisher5 Locust Ln.MailbuStar Wars1977C.Fisher5 Locust Ln.MailbuEmpire Strikes Back1980C.Fisher5 Locust Ln.MailbuReturn of the Jedi1983nametitle,year1081091101983Return of the JediHollywood123 maple St.C.Fisher1980Empire Strikes BackHollywood123 maple S

9、t.C.Fisher1983Return of the JediMailbu5 Locust Ln.C.Fisher1980Empire Strikes BackMailbu5 Locust Ln.C.Fisher1977Star WarsMailbu5 Locust Ln.C.Fisher1977Star WarsHollywood123 maple St.C.Fisheryeartitlecitystreetnamenametitle,yearnamestreet,cityAll Keyis BCNFisnt 4NFR1(name,title,year)R2(name,street,cit

10、y)All Keyis 4NF111特性特性3NFBCNF4NF消除消除 FD 引起的冗余引起的冗余大部分大部分是是是是消除消除 MVD 引起的冗余引起的冗余否否否否是是保持保持 FD是是可能可能可能可能保持保持 MVD可能可能可能可能可能可能112114115116117118119120121122123124125126127128129130131132133134135136137138139140141143144145146147148149SelectWhereFrom1501511521531541551561571581591601611621631641651661671

11、68169170171172173174175176177178179180181182183184185186187188189数值数值190191192193194195196197子查询子查询多行一列多行一列198199200201202203204205206207208209这门课他没选这门课他没选这样的课是不存在的这样的课是不存在的210211212213214215216217218219数据库修改包括插入数据、修改数据、删除数据数据库修改包括插入数据、修改数据、删除数据数据删除（数据删除（Delete） 只能对整个元组操作，不能只删除某些属性上的值只能对整个元组操作，不能只删除

12、某些属性上的值 只能对一个关系起作用，若要从多个关系中删除元组，只能对一个关系起作用，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令则必须对每个关系分别执行删除命令Delete From r Where P 从关系从关系 r 中删除满足中删除满足P的元组，只是删除数据，而不的元组，只是删除数据，而不是定义是定义220221222223先计算先计算avg，再做，再做Update224225226227avg不是原不是原始属性始属性228229230231232233234235236237238240241242243244245246Create Table MovieStar(n

13、ame char(30),address Varchar(255),gender char(1),birthday Date,Primary Key(name);247248Create Table MovieStar(name char(30) Primary Key,ID char(18) Unique,address Varchar(255),gender char(1),birthday Date);249Create Table MovieStar(name char(30),ID char(18),address Varchar(255),gender char(1),birthd

14、ay Date,Primary Key (name),Unique (ID) );250z参照参照&被参照、主表被参照、主表&从表、主键从表、主键&外键外键z外键的外键的值值不允许参照不存在的主键的不允许参照不存在的主键的值值z主键与外键的相容主键与外键的相容y类型类型y属性名可以不同属性名可以不同y外键允许外键允许Nullz反映反映实体间实体间存在的约束存在的约束251z属性级属性级sno char(10) References student(sno),cno char(10) References course(cno),.z表级表级Foreign Key sno

15、 References student(sno),Foreign Key cno References course(cno),.252253254255256257258z属性级属性级sno char(10) References student(sno)On Delete Set NullOn Update Cascade,.z表级表级Foreign Key sno References student(sno)On Delete Set NullOn Update Cascade,.25926026126226326426526626726826927027127227327427527

16、6277278WHEN Sweatshirt THEN SET type = Shirt WHEN Baseball Cap THEN SET type = Hat WHEN Visor THEN SET type = HatELSE SET type = UNKNOWN END CASE;END279Update employeeSet dept_id = new_dept_numWhere dept_id=old_dept_num;Delete From departmentWhere dept_id=old_dept_num;ENDExecute update_dept 123,212,

17、cs,98001280281282名称名称激活的时机激活的时机事件事件新值新值/修改后的值修改后的值旧值旧值/修改前的值修改前的值执行程序的条件执行程序的条件每修改一个元组便激活每修改一个元组便激活283名称名称激活的时机激活的时机事件事件新值新值/修改后的值修改后的值旧值旧值/修改前的值修改前的值执行程序的条件执行程序的条件每修改一个元组便激活每修改一个元组便激活28428528628828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232

18、3324325326327328329330331332333334335337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399Create Row Type StarType(name Char(30),address AddressType);Create Row Typ

19、e StarsInType(star REF(StarType),movie REF(MovieType);Create Table Movie OF TYPE MovieType;Create Table MovieStar OF TYPE StarType;Create Table StarsIn OF TYPE StarsInType;400401402403404405407408409410411412413414415416417418419420421422网络网络LDBLDBMSGDBMSCM全局数据字典全局数据字典LDBCMGDBMSLDBMS全局数据字典全局数据字典LDBC

20、MGDBMSLDBMS全局数据字典全局数据字典DDBMS的结构的结构423424425426TC1TCnTMnTM1事务管理器事务协调器427428429430431432433434SQL RemoteASA 联机用户ASA 远程移动用户ASA/ASE企业数据ASA的远程工作组(办事处 1)总部SQLRemoteSQLRemoteSQLRemote复制服务器ASA的远程工作组(办事处 2)435nAdaptive Server Enterprise /IQReplication AgentsReplication ServerDirectCONNECTOmniCONNECTnDB2nAS/4

21、00nORACLEnODBC/DRDAnAdaptive Server EnterprisenAdaptive Server AnywherenOraclenInformixnMicrosoft SQL ServernOS/390 DB2nReplication Toolkit for MVS移动用户复制点Adaptive Server AnywhereSQL RemoteSybase Enterprise Event Broker主点436Replication Agents for AReplication ServerDirectConnect for ADatabase AData S

22、erver AReplication ServerReplication Agents for BDatabase BData Server BDirectConnect for B437438439440441443444445446447448449450451452453454455OLTP系统系统RDBMSSybaseVSAMSAP/ERP5-10 年年过去过去详细数据详细数据当前当前详细数据详细数据轻度轻度汇总数据汇总数据高度高度汇总数据汇总数据数据集市数据集市分析型分析型CRM业务指标分析业务指标分析数据仓库数据仓库/决策分析系统决策分析系统EXCEL456457458459460

23、461462463464465466467468469470471472473474475CustSalesLocationSalesProdSalesTimeSalesSalestime idproduct idlocation idcustomer idsales revenueunits soldProductproduct idmakemodelTimetime iddateyearquartermonthweekLocationlocation idregiondistrictstoreCustomercustomer idcategorygroupSales factSales m

24、easuresTime dimensionAttributes of the time dimension476477478479480481lDesignlWarehouse ArchitectlManagelSybase ASIQlIntegratelInformatica lEnterprise ConnectlReplication ServerlVisualizelBriolCognoslAdministerlWarehouse Control Center482483484485486487488489490491492493494z桌面级工具桌面级工具yBrioQuery (RO

25、LAP)yBusiness Objects (ROLAP)yCognos Impromptu (ROLAP)yCognos PowerPlay (MOLAP)z服务器级服务器级OLAPyArbor Software Essbase (MOLAP)yMicroStrategy DSS Agent (ROLAP )yOracles Express (hybrid MOLAP/ROLAP)495zSQL3对聚集的扩展对聚集的扩展(在在Group By中扩展中扩展)SP(S#,P#,QTY)S#P#QTYS1P1300S1P2200S2P1300S2P2400S3P2200S4P2200Select

26、sum(QTY) as TOTQTYFromSP;Select S#,Sum(QTY) as TOTQTYFromSPGroup By S#;.496zSQL3对聚集的扩展对聚集的扩展(在在Group By中扩展中扩展)yGrouping Sets：支持多个分组同时运算：支持多个分组同时运算Select S#,P#,SUN(QTY) as TOTQTYFrom SPGroup By Grouping Sets (S#) , (P#)S#P#TOTQTYS1Null500S2Null 700S3Null 200S4Null 200NullP1600NullP21000497zSQL3对聚集的扩展

27、对聚集的扩展(在在Group By中扩展中扩展)yRollup：上卷：上卷Select S#,P#,SUN(QTY) as TOTQTYFrom SPGroup By Rollup (S# , P#)等同于等同于Group By Grouping Sets (S#, P#) , (S#),( )S#P#TOTQTYS1P1300S1P2200S2P1300S2P2400S3P2200S4P2200S1Null500S2Null 700S3Null 200S4Null 200NullNull1600498zSQL3对聚集的扩展对聚集的扩展(在在Group By中扩展中扩展)y Cube：立方体（

28、交叉表）：立方体（交叉表）Select S#,P#,SUN(QTY) as TOTQTYFrom SPGroup By Cube (S# , P#)等同于等同于Group By Grouping Sets (S#, P#) , (S#) , (P#) , ( )S#P#TOTQTYS1P1300S1P2200S2P1300S2P2400S3P2200S4P2200S1Null500S2Null 700S3Null 200S4Null 200NullP1600NullP21000NullNull1600499z数据挖掘数据挖掘（Data Mining）y探测型的数据分析探测型的数据分析y发现信息、发现知识发现信息、发现知识y基于基于人工智能人工智能、机器学习机器学习、统计学统计学y由计算机自动智能地分析数据，获取信息，由计算机自动智能地分析数据，获取信息，作出预测或帮助决策作出预测或帮助决策y需要需要