数据库系统原理英文学习教案

1、会计学1数据库系统原理数据库系统原理(yunl)英文英文第一页，共70页。February 20112nDatabase Users and AdministratorsnHistory of Database SystemsnAppendix: Data, Information, Knowledge, etc. 第1页/共70页第二页，共70页。February 20113nDatabase system (DBS)nhaving the same definition as DBMS in the textbooknthe term DBS and DBMS are used inter

2、changeably in the textbook第2页/共70页第三页，共70页。February 20114ntextbooksnDatabase(DB) a collection of interrelated data , stored in systems as files第3页/共70页第四页，共70页。February 20115nDatabase application system DB + DBMS + Application programs + Users/Administers第4页/共70页第五页，共70页。DBMSApplication program 1DB

3、fileDB query language e.g. SQLUsersDBMSDBDBS/DBASApplication program 1第5页/共70页第六页，共70页。February 20117records, salaries, tax deductions第6页/共70页第七页，共70页。February 20118nA program to add a new accountnA program to find the balance（收付差额，余额） of an accountnA program to generate monthly statements第7页/共70页第八

4、页，共70页。February 20119structure, access method, .nDemeritn application programs are not independent of the file system ( or operating system)n dependency among application programs and data (程序(chngx)与数据的非独立/依存性）第8页/共70页第九页，共70页。secondary storage (e.g. disk, tape)and data on it (address: cylinder, tr

5、ack, 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 system(DBS)(III)access program

6、s(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页第十页，共70页。Select NameFrom Student Where S#=100DBSlogicalstructures:relations/tupl

7、esDBMSDB 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页第十一页，共70页。February 201112carry out each new

8、taskndata isolation（独立性）nmultiple files and formats第11页/共70页第十二页，共70页。February 201113carried outne.g. transfer of funds from one account to another should either complete or not happen at all第12页/共70页第十三页，共70页。February 201114第13页/共70页第十四页，共70页。February 201115n e.g. about student, his name, sex, age,

9、 .nTo simplify and “divide and conquer” the design of complex data application systems ndescribes/design application data in three levels, and obtains three types of schema第14页/共70页第十五页，共70页。第15页/共70页第十六页，共70页。February 201117designnview abstractionnmerits: application programs are programmed accordi

10、ng to views. Views can also hide information (e.g., salary) for security purposes. 第16页/共70页第十七页，共70页。February 201118 view4: loanview5: account view6: .第17页/共70页第十八页，共70页。February 201119ndescription resultsnlogical schema (逻辑(lu j)模式）, e.g. relational tables ndescription procedure/Logical DB designn

11、logical abstractionnMerits: hiding physical implementation details第18页/共70页第十九页，共70页。February 201120methods, such as index, physical blocks, access methods for secondary memory, etc. ndescription procedure/Physical DB designnphysical abstraction第19页/共70页第二十页，共70页。February 201121(P7 in the textbook)n

12、physical schemandatabase design at the physical levelnlogical schemandatabase design at the logical level第20页/共70页第二十一页，共70页。February 201122ndata relationshipsndata semanticsndata constraintsn程序(chngx) = 数据结构 + 算法integrity constraints第21页/共70页第二十二页，共70页。February 201123nRelational model (关系模型)nusing

13、a collection of tables torepresent both data and the relationships among those datana set of operations on tables are also defined (2.2/ 2.3/2.4)n第22页/共70页第二十三页，共70页。February 201124nOther models nobject-oriented model (Chapter 9)nsemi-structured data models, e.g. XML (Chapter 10)第23页/共70页第二十四页，共70页。

14、February 201125nData model , data schema (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页第二十五页，共70页。February 201126nData

15、models can be classified as nobject-based model (基于(jy)对象的模型)nrecord-based model (基于(jy)记录的模型)nphysical data model (物理数据模型)nFor more details about category of data models, refer to Appendix B第25页/共70页第二十六页，共70页。February 201127nLogical data independencenwhen DBSs logical schemas at logical level (DBM

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

17、共70页第二十八页，共70页。February 201129 (cont.)第28页/共70页第二十九页，共70页。February 201130nTypes of DBS accessnretrieve , insert, delete, modify第29页/共70页第三十页，共70页。February 201131na statement requesting the retrieval of informationnDML also known as query language第30页/共70页第三十一页，共70页。February 201132where customer.cust

18、omer-id= 192-83-7465第31页/共70页第三十二页，共70页。February 201133integrity constraints in DBndomain integrity, referential integrity, assertions, authorization, etc.nThe structures /schemas of the database defined by DDL is stored in the data dictionary/directorynmeta data, i.e., data about data, is stored第32

19、页/共70页第三十三页，共70页。February 201134nData storage and definition languagena subset of DDL, defining physical schemas, i.e., storage structure and access methods, which are usually hidden form users 第33页/共70页第三十四页，共70页。February 201135nApplication programs generally access databases in two methodsnapplica

20、tion program interfaces (API, e.g. ODBC, JDBC, ADO, ), which allow SQL queries to be sent to a databasenembed DML call within host language (宿主(szh)语言, such as C and Java)第34页/共70页第三十五页，共70页。February 201136C applicationprogramsJava applicationprogramsOther language applicationprograms ODBCJDBCDBSDDL

21、+ DMLAPI(cont.)第35页/共70页第三十六页，共70页。February 2011371.5 Relational DatabasenRefer to chapter 2第36页/共70页第三十七页，共70页。February 2011381.6 Database Design从保持data independence（数据无关性/独立性）角度出发，根据data models所定义的数据规范形式，在view、logical 、 physical三个层次，采用三种data abstraction方法，通过DB概念设计、DBS逻辑设计、 DBS物理设计三个阶段，构造(guzo) 面向具

22、体应用领域的DBS的 external schema、logical schema 、internal schema的集合, 从而得到conceptual DBS 、logical DBS 、physical DBS的设计结果conceptual designlogical designphysical design第37页/共70页第三十八页，共70页。第38页/共70页第三十九页，共70页。February 201140 the savings account: an interest rate the checking account: overdraft第39页/共70页第四十页，共7

23、0页。February 201141第40页/共70页第四十一页，共70页。February 201142第41页/共70页第四十二页，共70页。February 201143第42页/共70页第四十三页，共70页。February 201144Attributes(a)The customer table A Sample Relational Database schema instances第43页/共70页第四十四页，共70页。February 201145A Sample Relational Database第44页/共70页第四十五页，共70页。February 201146ni

24、ndicesnstatistic data1.11 DBS Architecture第45页/共70页第四十六页，共70页。 Fig. 1.6DBS ArchitectureuserDBMSDB第46页/共70页第四十七页，共70页。February 201148n/management）n数据的分类、收集、组织(zzh)、编码、存储、检索和维护n信息/数据处理（processing）n对数据进行分类、收集、组织(zzh)、存储，进而从已有数据出发，抽取或推导出新的数据/信息1.11 Database Application System第47页/共70页第四十八页，共70页。February

25、 201149可以利用数据库系统的数据管理功能存储各种商品基本信息（如商品数量、销售额等）并为各类用户提供对各类商品信息的查询功能。当从数据库系统中得到这些商品信息后，可以采用一些统计分析工具、数据挖掘（Data Mining）工具进一步进行分析处理，得到有关那些商品属于热销商品、热销商品销售销售额等新信息。这种统计分析属于数据处理范畴1.11 Database Application System(cont.)第48页/共70页第四十九页，共70页。February 2011501.11 Database Application System ArchitecturenIn DBAS, th

26、e 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. web-based applications, and applications built using “middleware”第49页/共70页第五十页，共70页。Februa

27、ry 201151Two-tier and three-tier architectures1.11 Database Application System Architecture (cont.)第50页/共70页第五十一页，共70页。February 201152between the low-level data stored in the database and the application programs and queries submitted to the systemnThe storage manager is responsible for ntranslating

28、 DML statements into low-level file commandsnefficient storing, retrieving and updating of data1.8 Data Storage and Querying第51页/共70页第五十二页，共70页。February 201153nQuery optimizationnQuery evaluation engine1.8 Data Storage and Querying (cont.)第52页/共70页第五十三页，共70页。C, Pascal programs词法词法/语法语法(yf)/语义分析语义分析中

29、间中间(zhngjin)代码生成代码生成(中间中间(zhngjin)代码优化代码优化目标代码生成目标代码生成程程 序序 编编 译译 / 编译器编译器程序执行程序执行 / OS进程管理&进程调度并发控制死锁处理SQL query扫描和语法扫描和语法/语义分析语义分析查询优化查询优化(优化后)查询执行计划查询代码生成查询代码生成查询处理查询处理 / DBMSprocess / thread事务、存储处理事务、存储处理 / DBMS Chapter13, 14 Chapter15,16, 17 transaction关系代数表达式&查询树查询计划执行的代码查询计划执行的代码事务管理目

30、标程序代码(14)&事务调度并发控制死锁处理恢复技术(13)(15)(16)(17)第53页/共70页第五十四页，共70页。February 201155read (account_ B );account_ B := account_ B + 50; write (account_ B );nintegrity constraint total amount of account_A and account_B remains unchanged before and after the transaction execution 1.9 Transaction Management

31、第54页/共70页第五十五页，共70页。February 201156power failures and operating system crashes) and transaction failures, by means of concurrency and control failure recovery.ncontrols the interaction among the concurrent transactions, to ensure the consistency of the database1.9 Transaction Management第55页/共70页第五十六

32、页，共70页。February 201157and submit these queries to the query processor第56页/共70页第五十七页，共70页。February 201158nschema and physical organization modificationngranting of authorization for data accessnroutine maintenancen 第57页/共70页第五十八页，共70页。February 201159seen in context and convey meaning to people. Computer process data without any understanding of what the data represents第58页/共70页第五十九页，共70页。February 201160第59页/共70页第六十页，共70页。February 201161ninformation, esp. facts or numbers , collected fo