数据库系统原理英文PPT课件.pptx

《数据库系统原理英文PPT课件.pptx》由会员分享，可在线阅读，更多相关《数据库系统原理英文PPT课件.pptx（70页珍藏版）》请在三一文库上搜索。

1、February 20111Main Parts in This ChapterDatabase System ApplicationsPurpose of Database SystemsView of DataDatabase Languages Relational DatabaseDatabase DesignData Storage and Querying Transaction Management Database ArchitecturesDatabase Users and AdministratorsHistory of Database SystemsAppendix:

2、Data,Information,Knowledge,etc.第1页/共70页February 201121.0 DB,DBMS,DBSDefinitions in the textbookDatabase(DB)a collection of interrelated data,stored in systems as files Database management system(DBMS)DB,or a collection of interrelated data set of programs to access the data in DBDatabase system(DBS)

3、having the same definition as DBMS in the textbookthe term DBS and DBMS are used interchangeably in the textbook第2页/共70页February 201131.0 DB,DBMS,DBS(cont.)DBMS provides an environment that is both convenient and efficient for store and retrieve information definition of structures for storage of in

4、formationdata manipulation mechanismsdata safety mechanismsDefinitions in some other textbooksDatabase(DB)a collection of interrelated data,stored in systems as files第3页/共70页February 201141.0 DB,DBMS,DBS(cont.)Definitions in some other textbooks(cont.)Database management system(DBMS)a system/mechani

5、sm to manage data in DB or:set of programs to access the data in DBDatabase system(DBS)DB+DBMS+Users/AdministersDatabase application system DB+DBMS+Application programs+Users/Administers第4页/共70页DBMSApplication program 1DB fileDB query language e.g.SQLUsersDBMSDBDBS/DBASApplication program 1第5页/共70页F

6、ebruary 20116Databases and DBS touch all aspects of our livesBanking:all transactionsAirlines:reservations,schedulesUniversities:registration,gradesSales:customers,products,purchasesManufacturing:production,inventory,orders,supply chainHuman resources:employee records,salaries,tax deductions1.2 Purp

7、ose of Database Systems第6页/共70页February 20117In the early days,data management applications were built on top of file systemsE.g.Saving-bank enterprisea number of application programs to access data filesA program to debit（将.记入借方）or credit（将.记入贷方）an account(帐户）A program to add a new accountA program

8、 to find the balance（收付差额，余额）of an accountA program to generate monthly statements1.2 Purpose of Database Systems(cont.)第7页/共70页February 20118Data management on the basis of file systemsExample customer_record=in Customer_FileFS access needs file name,item name,length of record,length of item,positi

9、on of item in the record,storage structure,access method,.Demerit application programs are not independent of the file system(or operating system)dependency among application programs and data(程序与数据的非独立/依存性）1.2 Purpose of Database Systems(cont.)第8页/共70页Why DBS neededsecondary storage (e.g.disk,tape)

10、and data on it (address:cylinder,track,sector)memory controllers(i.e.disk controller),on the basis of secondary storages physical structures,such as storage structures,access method access programs(I)direct accessthrough disk controller(I)access through OS file system(II)access through data base sys

11、tem(DBS)(III)access programs(II)access programs (III)I/O system:driversfile system:files logical structures and access methodsDBMS:relational tables logical structures and access methods system calls related to file management DBS API 第9页/共70页Select NameFrom Student Where S#=100DBSlogicalstructures:

12、relations/tuplesDBMSDB userLogical address:#RecordRecord1Recordkblock1OS:Logical fileRecordiRecordmBlock nPhysical address：platter/track/sectorblock1blockiblockkPhysical File on diskDB filesblockOS：file system and I/O subsystem view of data model view of file第10页/共70页February 201111Drawbacks of usin

13、g file systems to store datadata redundancy（冗余）and inconsistency（不一致性）multiple file formats,duplication（副本）of information in different filesdifficulty in data accessneed to write a new program to carry out each new taskdata isolation（独立性）multiple files and formats1.2 Purpose of Database Systems(cont

14、)第11页/共70页February 201112integrity（完整性）problemsdata values should satisfy consistency constraintsintegrity constraints (e.g.customer_age 0)become part of program codehard to add new constraints or change existing onesatomicity problemsfailures may leave database in an inconsistent state with partia

15、l updates carried oute.g.transfer of funds from one account to another should either complete or not happen at all1.2 Purpose of Database Systems(cont.)第12页/共70页February 201113concurrent access anomaliesuncontrolled concurrent accesses can lead to inconsistenciese.g.two persons read an account and u

16、pdate it at the same timesecurity problemsDatabase systems offer solutions to all the above problems1.2 Purpose of Database Systems(cont.)第13页/共70页February 2011141.3 View of DataBasic concepts in DBS theoryview of data(数据视图),data abstractdata model,data schema(模式),schema instancelogical/physical iso

17、lation Data View data features from some viewpoints e.g.about student,his name,sex,age,.To simplify and“divide and conquer”the design of complex data application systems describes/design application data in three levels,and obtains three types of schema第14页/共70页第15页/共70页February 201116View leveldesc

18、ribe data from how the data items in DB are used by different usersseveral views for one datum description resultsexternal schema（外模式）=view,set of viewsdescription procedure/Logical DB designview abstractionmerits:application programs are programmed according to views.Views can also hide information

19、e.g.,salary)for security purposes.第16页/共70页February 201117E.g.Banking Application Areas in Fig.6.25 in page 240from more than one viewpoints,view integration view1:customer view2:customer view3:customer view4:loan view5:account view6:.第17页/共70页February 201118Logical levelimplementation-oriented,des

20、cribes how the data items are stored in DBS(e.g.as relational tables),and what relationships exist among those data:table customername:string;street:string;city:integer;description resultslogical schema(逻辑模式）,e.g.relational tables description procedure/Logical DB designlogical abstractionMerits:hidi

21、ng physical implementation details第18页/共70页February 201119Physical leveldescribes how the data(e.g.,customer record/table)are actually stored in files(or in secondary storage)description resultsphysical/internal schema(物理模式，内模式）i.e.storage structure and access methods,such as index,physical blocks,a

22、ccess methods for secondary memory,etc.description procedure/Physical DB designphysical abstraction第19页/共70页February 201120An instance of the database(数据库实例)the collection of information stored in the database at a particular moment(P7 in the textbook)the actual content of the database at a particul

23、ar point in timeSchemathe structure of the databaseoverall design of the database?(P7 in the textbook)physical schemadatabase design at the physical levellogical schemadatabase design at the logical level第20页/共70页February 201121Data descriptions/abstractions in three levels must obey three types of

24、specification,i.e.three types of data modelsDefinition of data model(P8)a collection of conceptual tools for describingdata data relationshipsdata semanticsdata constraints程序=数据结构+算法 integrity constraints第21页/共70页February 201122Another definition of data modelDBS中对数据组织与管理/使用方式的抽象描述，包括数据组织的语法定义，如数据项、

25、数据项间的联系数据组织的语义定义，如完整性约束数据操作 (note:only in some data models,e.g.relational data model)Relational model(关系模型)using a collection of tables to represent both data and the relationships among those dataa set of operations on tables are also defined(2.2/2.3/2.4)第22页/共70页February 201123Entity-Relationship

26、model(实体-联系模型)using the entity and the relationship to model the object and the association among objectsNetwork model and hierarchical modelonly used for early DBSOther models object-oriented model(Chapter 9)semi-structured data models,e.g.XML(Chapter 10)第23页/共70页February 201124nData model,data sch

27、ema(of data model),instance of schema E.g.nrelational data model:R=nrelational data schema:customer=n instance of schema:nsimilar to data types declaration,variables of data types,and values of variablesne.g.第24页/共70页February 201125nData models can be classified as nobject-based model(基于对象的模型)nrecor

28、d-based model(基于记录的模型)nphysical data model(物理数据模型)nFor more details about category of data models,refer to Appendix B第25页/共70页February 2011263种数据抽象、3种模式DBS中2种数据独立性概念/逻辑模式内模式间的映射 与physical data independence外模式概念模式间的映射 与logical data independence nLogical data independencenwhen DBSs logical schemas at

29、logical level(DBMS-oriented)change,application-oriented external schema or application programs do not changene.g.customer=is extended into customer=第26页/共70页用户A1宿主语言+DML用户A2用户B1用户B2 外部视图A 外部视图B 概念/逻辑视图外模式概念/逻辑模式内模式DBMS外模式/概念模式映射A外模式/概念模式映射B概念模式/内模式映射宿主语言+DML宿主语言+DML宿主语言+DML第27页/共70页February 201128

30、Physical data independence(P7)application programs do not depend on DBSs physical schema,i.e.when physical schema(e.g.index,block-size)change,application programs remain unchangedthe ability to modify the physical schema without changing the external/logical schemas,on which application programs dep

31、end (cont.)第28页/共70页February 2011291.4 Database LanguageDatabase Languages as(human-machine)interfacesdata manipulation language,DML data definition language,DDLData manipulation language DML is the language that enables users to access or manipulate the data as organized by the appropriate data mod

32、elTypes of DBS accessretrieve,insert,delete,modify第29页/共70页February 201130Two types of DMLprocedural(过程性)user specifies what data is required and how to get those data nonprocedural(说明性，declarative)user specifies what data is required without specifying how to get those dataQuery(查询）a statement requ

33、esting the retrieval of informationDML also known as query language第30页/共70页February 201131SQL is the most widely used non-procedural query language,others are QE,DatalogExamplefind the name of the customer with customer-id 192-83-7465 select customer.customer-namefrom customerwhere customer.custome

34、r-id =192-83-7465第31页/共70页February 201132DDL is used for specifying the database schema and additional properties of the dataE.g.create table account(account-number char(10),balance integer)DDL can also be used to define integrity constraints in DBdomain integrity,referential integrity,assertions,au

35、thorization,etc.The structures/schemas of the database defined by DDL is stored in the data dictionary/directory meta data,i.e.,data about data,is stored第32页/共70页February 201133nData storage and definition languagena subset of DDL,defining physical schemas,i.e.,storage structure and access methods,w

36、hich are usually hidden form users 第33页/共70页February 201134nApplication programs generally access databases in two methodsnapplication program interfaces(API,e.g.ODBC,JDBC,ADO,),which allow SQL queries to be sent to a databasenembed DML call within host language(宿主语言,such as C and Java)第34页/共70页Febr

37、uary 201135C applicationprogramsJava applicationprogramsOther language applicationprograms ODBCJDBCDBSDDL+DMLAPI(cont.)第35页/共70页February 2011361.5 Relational DatabasenRefer to chapter 2第36页/共70页February 2011371.6 Database Designn从保持data independence（数据无关性/独立性）角度出发，根据data models所定义的数据规范形式，在view、logic

38、al、physical三个层次，采用三种data abstraction方法，通过DB概念设计、DBS逻辑设计、DBS物理设计三个阶段，构造 面向具体应用领域的DBS的 external schema、logical schema、internal schema的集合,从而得到conceptual DBS、logical DBS、physical DBS的设计结果nconceptual designnlogical designnphysical design第37页/共70页第38页/共70页February 201139Banking Enterprisethe bank is organ

39、ized into brancheseach branch is described by the city is located in,the name and the assets a bank customer is illustrated by his customer_id and name,the street and city the bank offers two types of accounts,which are depicted by account_number and balance the savings account:an interest rate the

40、checking account:overdraft第39页/共70页February 201140the bank provides its customers with loans,which are depicted by loan_number,balance_name,and amountBank employees are identified by their employee_id,telephone_number,employee_ street,employee_ cityConceptual DB designE-R diagram第40页/共70页February 20

41、1141第41页/共70页February 201142Logical DB designdatabase design in E-R model usually converted to design in the relational model,i.e the relational tables and views the converted relational tables are then normalized(规范化)第42页/共70页February 201143Attributes(a)The customer table A Sample Relational Databa

42、se schema instances第43页/共70页February 201144A Sample Relational Database第44页/共70页February 201145Fig 1.6 DBS=User/DBA +DBMS +DBDBMSquery processing storage managerDB filesapplication data data dictionary/directory indicesstatistic data1.11 DBS Architecture第45页/共70页 Fig.1.6DBS ArchitectureuserDBMSDB第46

43、页/共70页February 201147DBAS(Database Application Systems)DB+DBMS+Application programs+Users/DBA 实现数据处理功能DBSDB +DBMS+User/DBA 实现数据管理功能信息/数据管理（management）数据的分类、收集、组织、编码、存储、检索和维护 信息/数据处理（processing）对数据进行分类、收集、组织、存储，进而从已有数据出发，抽取或推导出新的数据/信息 1.11 Database Application System第47页/共70页February 201148 信息/数据处理与信

44、息/数据管理的区别数据管理侧重于对数据如何进行组织和存储，并根据用户需要提供对数据的访问支持数据处理除了具有数据管理功能外，还可以对通过数据管理得到的数据进行进一步深加工，从中获取新的数据、信息例如，在一个商品零售系统中，可以利用数据库系统的数据管理功能存储各种商品基本信息（如商品数量、销售额等）并为各类用户提供对各类商品信息的查询功能。当从数据库系统中得到这些商品信息后，可以采用一些统计分析工具、数据挖掘（Data Mining）工具进一步进行分析处理，得到有关那些商品属于热销商品、热销商品销售销售额等新信息。这种统计分析属于数据处理范畴 1.11 Database Application

45、System(cont.)第48页/共70页February 2011491.11 Database Application System ArchitecturenIn DBAS,the application programs are responsible for information or data processing,e.g.data miningnTwo-tier architecturene.g.client programs using ODBC/JDBC to communicate with a databaseThree-tier architecturene.g.w

46、eb-based applications,and applications built using“middleware”第49页/共70页February 201150Two-tier and three-tier architectures1.11 Database Application System Architecture(cont.)第50页/共70页February 201151In DBS,DBMS provides data management mechanisms for users,that is,data storage and data queryingDBMS

47、consists of two modulesstorage managerquery processorStorage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the systemThe storage manager is responsible for translating DML statements int

48、o low-level file commandsefficient storing,retrieving and updating of data1.8 Data Storage and Querying第51页/共70页February 201152The modules in the storage manager are authorization and integrity manager,transaction manager,file manager,and buffer managerThe query processor includesDDL interpreter DML

49、 compilerQuery optimizationQuery evaluation engine1.8 Data Storage and Querying(cont.)第52页/共70页C,Pascal programs词法/语法/语义分析中间代码生成(中间)代码优化目标代码生成程 序 编 译 /编译器程序执行/OS进程管理&进程调度并发控制死锁处理SQL query扫描和语法/语义分析查询优化(优化后)查询执行计划查询代码生成查询处理/DBMSprocess/thread事务、存储处理/DBMS Chapter13,14 Chapter15,16,17 transaction关系代数表达

50、式&查询树查询计划执行的代码事务管理目标程序代码(14)&事务调度并发控制死锁处理恢复技术(13)(15)(16)(17)第53页/共70页February 201154A transaction is a collection of operations that performs a single logical function in a database applicationE.g.Transaction T1:transfer$50 from account_A to account_B T1:read(account_A);account_A:=account_A-50;writ