1、nTan Chang-gengTan Chang-gengnSchool of Software,Central South University n TCG-CSU1.2Database Systems ReferenceslCMU SSD7 E-resourcelDatabase system concepts(5th ed.)by Silberschatz,Korth,Sudarshan,2005,Higher Education Press.ISBN 7-04-019245-4l数据库系统概念数据库系统概念（上书中译本）（上书中译本），原著：原著：Silberschatz，杨冬青译，机
2、械工业出版社,ISBN 7-111-19687-2lDatabase Systems A practical Approach to Design,Implementation,and Management(Thomas Connolly,Carolyn Begg,Person Education Inc.),ISBN 7-5053-8784-7nInstructor:Tan Changgeng.Email:cgtan163 QQ:327414529TCG-CSU1.4Database SystemsnCertificate in Computer ProgramminglCourses 1
3、through 5nCertificate in Software Systems DevelopmentlCourses 1 through 10SSD Curriculum OverviewTCG-CSU1.5Database Systems1:Introduction to Information Systems2:Introduction to Computer Systems3:Object Oriented Programming and Design5:Data Structuresand Algorithms6:System LevelProgramming10:Softwar
4、eProject Organizationand Management9:Software Specification,Testand Maintenance8:Networks and DistributedComputing4:User Centered Design and Testing7:Database SystemsTCG-CSU1.6Database Systemsn10 Course SequencenRooted in the expertise of Carnegie Mellon Universitys School of Computer SciencenApplie
5、d,industry-oriented skillsnSoftware Development DisciplinenCS trains scientists/researchersnFocus on TheorynHigher MathematicsnSoftware Developers are not researchersnApplied,industry oriented skillsnFocus on foundational skillsTCG-CSU1.7Database SystemslBecome familiar with fundamental DBMS concept
6、s and what a database system is lLearn to use SQL(structured query language)lLearn to design database systemslLearn to design and manage transaction(事务)lLearn to improve query performancelGain exposure to future trends in databases TCG-CSU1.8Database SystemsStudents successfully completing SSD7 will
7、 be able to PRODUCE(contd)lDatabase designs that support a given applicationlData models using E-R diagrams lSound schema designs using normalization（使用规范化方法设计出合理的模式）lWeb-based database applications using SQL and JSP/Servlets TCG-CSU1.9Database SystemsStudents successfully completing SSD7 will be ab
8、le to USElIndex structures of a DBMS to improve performance lThe transaction features of a DBMS to achieve fault recovery(故障恢复)and concurrency control(并发控制)lKey relational operations to manipulate data(操纵数据)lSQL DDL to model data,constraints,and views(视图)lSQL DML to write complex queries TCG-CSU1.10
9、Database SystemsStudents will be able to knowledgeably DISCUSSlThe basic concepts of object-relational and object-oriented database management systems lThe basic concepts and application of data warehousing and data mining(datacubes,OLAP)lThe basic functions and application of multimedia databases l
10、The basic issues of database privacy and security lThe DBMS offerings of the most important vendors TCG-CSU1.11Database SystemsDatabaseProgDistributed SysOSUIData Struct&Algorithms数据库的地位数据库的地位TCG-CSU1.12Database SystemsnIntroductionnRelational ModelnSQLnEntity-Relationship ModelnRelational-Database
11、DesignnTransactionsnConcurrency Control nRecovery System（Integrity and Security）nDatabase System ArchitecturesnTCG-CSU1.13Database SystemsnPurpose of Database SystemsnView of DatanDatabase LanguagesnRelational DatabasesnDatabase DesignnObject-based and semistructured databasesnData Storage and Query
12、ingnTransaction ManagementnDatabase ArchitecturenDatabase Users and AdministratorsnOverall StructurenHistory of Database SystemsTCG-CSU1.14Database SystemsnDBMS contains information about a particular enterpriselCollection of interrelated datalSet of programs to access the data lAn environment that
13、is both convenient and efficient to usenDatabase Applications?lBanking:all transactionslAirlines:reservations,scheduleslUniversities:registration,gradeslSales:customers,products,purchaseslOnline retailers:order tracking,customized recommendationslManufacturing:production,inventory,orders,supply chai
14、nlHuman resources:employee records,salaries,tax deductions TCG-CSU1.15Database SystemsnPurchases using your credit card 信用卡lA credit card database lThe checkout assistantlA credit card readerlCheck your creditlAdd the purchases to databaselMonthly statements to each cardholderTCG-CSU1.16Database Sys
15、temsnIn the early days,database applications were built directly on top of file systemsnDrawbacks of using file systems to store data?TCG-CSU1.17Database SystemsnLimitations of the File-Based ApproachlData redundancy and inconsistency数据冗余与非一致性4Multiple file formats,duplication of information in diff
16、erent files4文件格式不相容，数据冗余lProgram-data dependence 程序依赖于数据4Need to write a new program to carry out each new tasklData isolation multiple files and formats4数据孤立；文件格式不相容lIntegrity problems完整性问题4Integrity constraints (e.g.account balance 0)become“buried”in program code rather than being stated explicitl
17、y4Hard to add new constraints or change existing onesTCG-CSU1.18Database SystemsnDrawbacks of using file systems(cont.)lAtomicity of updates原子更新原子更新4Failures may leave database in an inconsistent state with partial updates carried out4Example:Transfer of funds from one account to another should eith
18、er complete or not happen at alllConcurrent access by multiple users并发访问并发访问4Concurrent accessed needed for performance4Uncontrolled concurrent accesses can lead to inconsistencies Example:Two people reading a balance and updating it at the same time(sell ticket)lSecurity problems4Hard to provide us
19、er access to some,but not all,datanDatabase systems offer solutions to all the above problemsTCG-CSU1.19Database SystemsnData:data are raw facts that describe people,objects,and events nDatabase:An integrated collection of related data constitutes a Database(DB).相关数据 集成lrelated data,the data represe
20、nts logically coherent facts about some aspects of the real world that are required by an application.lIntegrated,the data for multiple applications is stored together and manipulated in a uniform way on a secondary storage such as a magnetic or an optical disk.TCG-CSU1.20Database SystemsnA Database
21、 Management System(DBMS)is a collection of programs that controls a database.lGeneral-purpose software system lData Definition Language(DDL)lData Manipulation Language(DML)lControlled access to the database4 a security system(authorized users)安全性4 an integrity system(consistency of stored data)完整性4
22、a concurrency control system(shared access)并发控制4 a recovery control system(when H/S failure)恢复4 a user-accessible catalog(descriptions of data)数据字典TCG-CSU1.21Database Systemsna Database System consists of lan application specific database 数据库lthe DBMS that maintains that database DBMSlthe applicatio
23、n software that manipulates the database 应用程序 数据库系统的个体含义是指一个具体的数据库管理系统软件和用它建立起来的数据库；它的学科含义是指研究、开发、建立、维护和应用数据库系统所涉及的理论、方法、技术所构成的学科。在这一含义下，数据库系统是软件研究领域的一个重要分支，常称为数据库领域。TCG-CSU1.22Database SystemsTCG-CSU1.23Database SystemsnDBMSs embody the three distinguishing characteristics of the database approach:
24、data abstraction,reliability,and efficiency.n数据抽象 可靠性 高效 TCG-CSU1.24Database SystemsnData Abstractionlprovide users with high-level,conceptual representations of the dataa table in relational DBMSs 提供高层、概念的数据表示lhide storage details that are not of interest to most database users 隐藏存储细节lprogram-data
25、independence 数据独立性TCG-CSU1.25Database SystemsnReliability.DBMSs provide high reliability lEnforcing integrity constraints 完整性约束4Integrity constraints reflect the meaning(or,the semantics)of the data and of the application lEnsuring data consistency despite hardware or software failures.数据一致性4Tansact
26、ions Within an application program,one or more query-and-update requests to the DBMS can be logically grouped together in order to perform a task.A group of such requests is called a transaction.事务 4Examples:a customer pays with a credit card,withdraw money from ATMTCG-CSU1.26Database SystemsnEffici
27、ency：DBMSs support both efficient space utilization and efficient access to data.lStoring each data item only once 减少冗余lEnhancing the performance of queries by means of optimizations and the use of access methods to data based on their values 查询优化lDecreasing response time of transactions by allowing
28、 multiple users to access the database concurrently 并发提高效率 TCG-CSU1.27Database SystemsTCG-CSU1.28Database SystemsFile1File1File2File2FilenFilenProg1Prog1Prog2Prog2PrognPrognFile SystemFile SystemTCG-CSU1.29Database SystemsDatabaseDatabaseProg1Prog1Prog2Prog2PrognPrognDBMSDBMSTCG-CSU1.30Database Syst
29、ems 人工管理 文件系统 数据库系统 应用背景 科学计算 科学计算、管理 大规模管理 硬件背景 无直接存取存储设备 磁盘、磁鼓 大容量磁盘 软件背景 没有操作系统 有文件系统 有数据库管理系统 背 景 处理方式 批处理 联机实时处理 批处理 联机实时处理,分布处理批处理 数据的管理者 人 文件系统 数据库管理系统 数据面向的对象 某一应用程序 某一应用程序 整个应用系统 数据的共享程度 无共享,冗余度极大 共享性差,冗余度大 共享性高，冗余度小 数据的独立性 不独立，完全依赖于程序 独立性差 具有高度的物理独立性和逻辑独立性 数据的结构化 无结构 记录内有结构，整体无结构 整体结构化，用数据
30、模型描述 特 点 数据控制能力 应用程序自己控制 应用程序自己控制 由数据库管理系统提供数据安全性、完整性、并发控制和恢复能力 Compare the different phases of data management (Table1)TCG-CSU1.31Database SystemsnAdvantages 1lControl of data redundancylData consistencylMore information from the same amount of datalSharing of datalImproved data integritylImproved
31、securitylEnforcement of standardslEconomy of scaleTCG-CSU1.32Database SystemsnAdvantages 2lBalance of conflicting requirementslImproved data accessibility and responsivenesslIncreased productivitylImproved maintenance through data independencelIncreased concurrencylImproved backup and recovery servi
32、cesTCG-CSU1.33Database SystemsnDisadvantages lComplexitylSizelCost of DBMSslAdditional hardware costslCost of conversionlPerformancelHigher impact of a failureTCG-CSU1.34Database SystemsnThe data has a simple structure and its size is small.nThe application,although simple and unchanging,has a speci
33、al purpose.nConcurrent access to data by multiple users is not required.TCG-CSU1.35Database SystemsnDatanDatabasenDBMS(Database Management Systems)nDBS(Database Systems)TCG-CSU1.36Database SystemsnDatabase SystemsnConceptslData(数据):4Data are raw facts that describe people,objects,and events.Or 数据实际上
34、就是描述事物的符号记录。数据实际上就是描述事物的符号记录。4例如：文字、图形、图象、声音、学生的档案记录等。例如：文字、图形、图象、声音、学生的档案记录等。4数据的形式本身并不能完全表达其内容，需要经过语义解释。数据的形式本身并不能完全表达其内容，需要经过语义解释。数据与其语义是不可分的。数据与其语义是不可分的。lInformation(信息):4一般把信息理解为关于现实世界事物存在方式或运动状态的反一般把信息理解为关于现实世界事物存在方式或运动状态的反映映.TCG-CSU1.37Database SystemslDifference between Data and Information4
35、数据是信息的符号表示或称为载体数据是信息的符号表示或称为载体;信息则信息则是数据的内涵，是对数据语义的解释；是数据的内涵，是对数据语义的解释；4数据表示了信息，而信息只有通过数据形数据表示了信息，而信息只有通过数据形式才能被计算机理解和接受。式才能被计算机理解和接受。TCG-CSU1.38Database SystemslDatabase4An integrated collection of related data constitutes a Database(DB).4Keep data long time4Large quantities of data 4Logically rela
36、ted4Structured data4ShareTCG-CSU1.39Database SystemslDatabase Management Systems(DBMS)4A Database Management System(DBMS)is a collection of programs that controls a database.4It enables users to define,create,maintain,and control access to the database.4We can use it not only to create and maintain
37、multiple databases but also to implement database systems for different applications.TCG-CSU1.40Database SystemslDatabase Systems 4A database system is developed to support the operations of a specific organization or a specific set of applications.4a Database System consists of 1)An application spe
38、cific database 2)The DBMS that maintains that database 3)The application software that manipulates the database 4The figure 1-4 illustrates a simplified database-system architecture TCG-CSU1.41Database SystemsFigure 1-4 database-system architecture TCG-CSU1.42Database SystemsnRoles in the Database E
39、nvironmentlData and Database Administrators(DA and DBA)lDatabase Designers 4Logical Database Designers4Physical Database DesignerslApplication DeveloperslEnd-Users4Nave users4Sophisticated usersTCG-CSU1.43Database SystemsThe architecture of a database systems is greatly influenced by the underlying
40、computer system on which the database is running:nCentralizednClient-servernParallel(multi-processor)nDistributed TCG-CSU1.44Database SystemsUsers are differentiated by the way they expect to interact with the systemnApplication programmers interact with system through DML callsnSophisticated users
41、form requests in a database query languagenSpecialized users write specialized database applications that do not fit into the traditional data processing frameworknNave users invoke one of the permanent application programs that have been written previouslylExamples,people accessing database over th
42、e web,bank tellers,clerical staffTCG-CSU1.45Database SystemsnCoordinates all the activities of the database system;the database administrator has a good understanding of the enterprises information resources and needs.nDatabase administrators duties include:lSchema definition模式定义lStorage structure a
43、nd access method definitionlSchema and physical organization modificationlGranting user authority to access the databaselSpecifying integrity constraintslActing as liaison with userslMonitoring performance and responding to changes in requirementsTCG-CSU1.46Database SystemsTCG-CSU1.47Database System
44、snDBA should be able to change database storage structures without affecting the users views.nInternal structure of database should be unaffected by changes to physical aspects of storage.nDBA should be able to change conceptual structure of database without affecting all users.TCG-CSU1.48Database S
45、ystemsTCG-CSU1.49Database SystemsnExternal Level外部层外部层lUsers view of the database.用户视图用户视图lDescribes that part of database that is relevant to a particular user.nConceptual Level概念层概念层lCommunity view of the database.总体视图总体视图 lDescribes what data is stored in database and relationships among the data
46、.nInternal Level内部层内部层lPhysical representation of the database on the computer.lDescribes how the data is stored in the database.TCG-CSU1.50Database SystemsTCG-CSU1.51Database SystemsnLogical Data IndependencelRefers to immunity of external schemas to changes in conceptual schema.概念模式变化不会影响外模式概念模式变化
47、不会影响外模式4Conceptual schema changes(e.g.addition/removal of entities).4Should not require changes to external schema or rewrites of application programs.nPhysical Data IndependencelRefers to immunity of conceptual schema to changes in the internal schema.4Internal schema changes(e.g.using different fi
48、le organizations,storage structures/devices).4Should not require change to conceptual or external schemas.TCG-CSU1.52Database SystemsTCG-CSU1.53Database SystemsAn architecture for a database system TCG-CSU1.54Database Systems现实世界Conceptual model转换internal modelabstractView 1View 2abstractFigure 1-5
49、three data modelsTCG-CSU1.55Database SystemsnPhysical level:describes how a record(e.g.,customer)is stored.type customer=recordcustomer_id:string;customer_name:string;customer_street:string;customer_city:integer;end;Logical level:describes data stored in database,and the relationships among the data
50、.nView level:application programs hide details of data types.Views can also hide information(such as an employees salary)for security purposes.TCG-CSU1.56Database SystemsnSimilar to variables and types in programming languagesnSchema the logical structure of the database lExample:The database consis
