數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用1TheWorldsofDatabaseSystem.ppt

Technology and Application of Database (數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用),Xiao Chen (肖晨),Technology and Application of Database,Xiao Chen Ph.D. School of Computer Science, BUPT ,Teaching material,Jeffrey D.Ullman, Jennifer Widom.A First Course In Database Systems (英文影印版.第3版 機(jī)械工業(yè)出版社 ) 或中譯本數(shù)據(jù)庫(kù)系統(tǒng)基礎(chǔ)教程 沃爾曼，等著；岳麗華，龔育昌，等譯 機(jī)械工業(yè)出版社 / ullman/fcdb.html 機(jī)械工業(yè)出版社/華章北京辦事處：白濤（7折） 電話68995264Email： 北京市西城區(qū)百萬(wàn)莊南街1號(hào)413室 100037,Assessment (provisional 暫定),Total Mark = Regular grade(quiz 平時(shí)作業(yè) 隨堂小測(cè)驗(yàn) 上機(jī)作業(yè) 安裝MySQL，SQL查詢截圖，任意的數(shù)據(jù)庫(kù)設(shè)計(jì),About your major,About the tree of knowledge,Root of knowledge tree 01 philosophy, 02 economy, 03 science of law, 04 education, 05 literature(中國(guó)/外語(yǔ)文學(xué)/新聞傳播/藝術(shù)), 06 history 07 science(數(shù)/理/化), 09 agricultural, 10 medical science, 11 military(軍事學(xué)), 12 management science 08 engineering mechanical(力學(xué)), optical(光學(xué)), instruments(儀器), materials (材料), metallurgy(冶金), power(動(dòng)力), electrical(電氣), electronic (電子), information communication(信息通信), control (控制), construction(建筑), civil engineering(土木), water conservancy(水利) 0802 mechanical engineering(機(jī)械) 0812 computer science and technology 081201 computer architecture 081202 computer software & theory: data structure & alg. 081203 computer applications: multimedia, DB.,By ministry of education,為了更好的理解，還是看中文吧,多媒體技術(shù)在知識(shí)領(lǐng)域的位置,科學(xué)門類 01 哲學(xué) 02 經(jīng)濟(jì)學(xué)( 理論/應(yīng)用) 03 法學(xué) 04 教育學(xué) 05 文學(xué)(中/外語(yǔ)文學(xué)/新聞傳播/藝術(shù)) 06 歷史學(xué) 07 理學(xué)(數(shù)/理/化) 09 農(nóng) 10 醫(yī) 11 軍事學(xué) 12 管理學(xué) 08 工學(xué) 力學(xué)，光學(xué)，儀器，材料，冶金，動(dòng)力，電氣，電子，信息通信，控制，建筑，土木，水利， 0812 計(jì)算機(jī)科學(xué)與技術(shù)（一級(jí)學(xué)科） 081201 計(jì)算機(jī)系統(tǒng)結(jié)構(gòu)（二級(jí)學(xué)科） 081202 計(jì)算機(jī)軟件與理論 081203 計(jì)算機(jī)應(yīng)用技術(shù) 數(shù)據(jù)庫(kù),Knowledge in Computer Science,Knowledge in Computer Science,Why study ?,Knowledge + Thought = Behavior College/life time,學(xué)而優(yōu)則仕， 仕而優(yōu)則學(xué),Max. happiness S.t. limited time,A First Course in Database Systems,The Worlds of Database System The Relational Model of Data() Design Theory for Relational Databases() High-Level Database Models(ER圖) Algebraic and Logical Query Language The Database Language SQL () Constraints and Triggers Views and Indexes SQL in a Server Environment Advanced topics in Relational Databases,概述 關(guān)系數(shù)據(jù)模型 建模 ER圖 關(guān)系代數(shù) SQL,1 The Worlds of Database Systems,1.1 The Evolution of Database Systems 1.2 Overview of a DBMS 1.3 Outline of Database-System Studies,DB發(fā)展 數(shù)據(jù)庫(kù)管理系統(tǒng)的結(jié)構(gòu) 本課程內(nèi)容,1 The Worlds of Database Systems,History Taking lens as a mirror and you can dress, Taking person as a mirror and you can balance the pros and cons(利弊), take history as a mirror and you will understand why dynasties rise and fall.,1.1 The Evolution of Database Systems,Whats a database? (*) In essence, a database is a collection of information that exists over a long period of time, often many years. In common parlance(p:lns說(shuō)法), a database is a collection of data that is managed by a database management system (DBMS cs245 c.f DB cs145). Data+ DBMS + user/DBA + hardware,一個(gè)數(shù)據(jù)庫(kù)是由一個(gè)數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)所管理的一個(gè)數(shù)據(jù)集合。,1.1 The Evolution of Database Systems,數(shù)據(jù)管理技術(shù)具體就是指人們對(duì)數(shù)據(jù)進(jìn)行收集、組織、存儲(chǔ)、加工、傳播和利用的一系列活動(dòng)的總和，經(jīng)歷了人工管理、文件管理、數(shù)據(jù)庫(kù)管理三個(gè)階段。每一階段的發(fā)展以數(shù)據(jù)存儲(chǔ)冗余不斷減小、數(shù)據(jù)獨(dú)立性不斷增強(qiáng)、數(shù)據(jù)操作更加方便和簡(jiǎn)單為標(biāo)志，各有各的特點(diǎn)。,1.1 The Evolution of Database Systems,Database Applications (Where need DB ?): Banking: all transactions(交易) Airlines: reservations(預(yù)定), schedules(航班) Universities: registration, grades Sales: customers, products, orders(c.f 電商) Manufacturing: production, inventory(庫(kù)存), orders, supply chain Human resources: employee records, salaries, tax deductions,1.1 The Evolution of Database Systems,An example customer (custid, name, city, phone)/數(shù)據(jù)表 Find the IDs and phone numbers of all customer named “張三”/找到張三的信息 SELECT custid, phone FROM customer WHERE name = 張三;,1.1 The Evolution of Database Systems,A DBMS is expected to DBMS的功能: Data Definition Language (DDL): create databases and specify their schema(模式:DBA管理員的工作). Data Query Language 查詢語(yǔ)言and Data Manipulation Language控制語(yǔ)言: query（查詢） and modify（更新） the data based on the schema of the database. Support the storage of very large amounts of data 海量數(shù)據(jù): many terabytes(1012 bytes) or more, over a long period of time, allowing efficient access. ,A DBMS is expected to: Enable durability(持久存儲(chǔ)): the recovery of the database in the face of failures(故障), errors of many kinds, or intentional misuse(故意濫用). Support Data sharing（數(shù)據(jù)共享）: control access to data from many users at once; without allowing the action of one user to affect other users(isolation/隔離); without actions on the data to be performed partially but not completely(atomicity/原子).,1.1 The Evolution of Database Systems,Database systems evolved from file systems 1. Data redundancy(冗余)and inconsistency(不一致) Multiple file formats, duplication of information in different files. (難以統(tǒng)一/技術(shù)壟斷考慮) 2. Difficulty in accessing data (訪問(wèn)困難) Need to write a new program to carry out each new task.,1.1 The Evolution of Database Systems,Database evolved from file systems (cont.) 3. Integrity(完整性) problems Integrity constraints(約束) (e.g. account balance 0) become “buried(埋入)” in program code rather than being stated explicitly(明確地).Hard to add new constraints or change existing ones. 4. Atomicity of updates(原子性操作) Failures may leave database in an inconsistent state with partial updates carried out. Example: Transfer of funds(資金) from one account to another should either complete or not happen at all.,1.1 The Evolution of Database Systems,Database evolved from file systems (cont.) 5.Concurrent(并發(fā))access by multiple users Uncontrolled concurrent accesses can lead to inconsistencies. Example: Two people reading a balance and updating it at the same time. 6. Security problems Hard to provide user access to some, but not all, data. (不同用戶的不同權(quán)限),1.1 The Evolution of Database Systems,Late 1960s and 1970s: (skip) Hard disks allow direct access to data 直接訪問(wèn) Network and hierarchical data models in widespread use. (Network model was standardized in late 1960s ) 網(wǎng)狀、層次模型 The early DBMS didnt support high-level query languages. 早期語(yǔ)言,1.1 The Evolution of Database Systems,1970: Ted Codd defines the relational data model Received the ACM Turing Award in 1981 IBM Research begins System R prototype(1973) Main idea: organize all the data in database as tables called relations.(關(guān)系:第二章內(nèi)容) Queries could be expressed in a very high-level language to increase the efficiency of database programmers.,1.1 The Evolution of Database Systems,1980s: Research relational prototypes evolve into commercial(商用的) systems SQL becomes industrial standard Parallel and distributed(分布式) database systems Object-oriented database systems 1990s: Large decision support and data-mining(數(shù)據(jù)挖掘) applications Large multi-terabyte data warehouses(數(shù)據(jù)倉(cāng)庫(kù)) Emergence(出現(xiàn)) of Web commerce 2000s: XML(可擴(kuò)展模型語(yǔ)言) and XQuery standards,1.1 The Evolution of Database Systems,1.2 Overview of a Database Management System (DBMS),A DBMS is a powerful tool for creating and managing large amounts of data efficiently and allowing it to persist over long periods of time, safely. 管理海量數(shù)據(jù)，長(zhǎng)期安全保存,數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)是操縱和管理數(shù)據(jù)庫(kù)的一組軟件，它是數(shù)據(jù)庫(kù)系統(tǒng)(DBS)的重要組成部分。 數(shù)據(jù)庫(kù)管理系統(tǒng)的主要功能有 數(shù)據(jù)定義功能DD、 數(shù)據(jù)操縱功能DM、 數(shù)據(jù)庫(kù)的運(yùn)行管理和數(shù)據(jù)庫(kù)的建立 以及維護(hù)等4個(gè)方面。,1.2 Overview of a DBMS,1.2 Overview of a DBMS,Application 1,Application 2,. . .,Application n,DBMS,database,Database Management System,手機(jī)微博 網(wǎng)頁(yè)微博 門戶登錄,單線框 系統(tǒng)成分 雙線框 內(nèi)存中的數(shù)據(jù)結(jié)構(gòu) 實(shí)線 控制和數(shù)據(jù)流 虛線 數(shù)據(jù)流 略,略,1.2 Overview of a DBMS,Two types of user: Conventional users and application programs.用戶 A database administrator(DBA 數(shù)據(jù)庫(kù)管理員): a person or persons responsible for the structure or schema of the database. Three kinds of commands to the DBMS: Queries , updates : commands to extract(提取) data from the database or affect the content of the database .(查詢、更新) Transaction commands: commands to tell the transaction manager when transactions begin and end. (事務(wù)) DDL commands: commands to change the schema of the database or create a new database by DBA. (管理員更改數(shù)據(jù)庫(kù)結(jié)構(gòu)/模式設(shè)計(jì)),1.2 Overview of a DBMS,Storage and buffer manager: (skip) includes the storage manager and the buffer manager: storage manager: controls the placement of data on disk and its movement between disk and main memory, keeps track(跟蹤) of the location of files on the disk and obtains the block or blocks containing a file on request from the buffer manager. buffer* manager: partition(分割) the available main memory into buffers.,User/application ,略,1.2 Overview of a DBMS,The kinds of information: Data: the contents of the database itself. Metadata(元數(shù)據(jù)): the database schema(模式) that describes the structure of, and constraints(約束) on, the database. (e.g 淘寶手機(jī)類目商品最低價(jià)格) Log(日志) records: information about recent changes to the database, these support durability(持久性) of the database. Statistics(統(tǒng)計(jì)): information gathered and stored by the DBMS about data properties. Indexes(索引): data structures that support efficient access to the data.,1.2 Overview of a DBMS,Transaction Processing: Transaction(事務(wù)): a group of operations that must appear to have been executed together sequentially, as a unit. Transaction manager: Concurrency-control(并發(fā)控制) manager, or scheduler(調(diào)度器): responsible for assuring atomcity(原子性) and isolation(隔離性) of transactions. Logging and recovery manager: responsible for the durability(持久性) of transactions.,單線框 系統(tǒng)成分 雙線框 內(nèi)存中的數(shù)據(jù)結(jié)構(gòu) 實(shí)線 控制和數(shù)據(jù)流 虛線 數(shù)據(jù)流,1.2 Overview of a DBMS,Properties: ACID (*) Atomicity(原子性): either all of a transaction be executed or none of it is. Consistency(一致性): keep consistent state. Isolation(隔離性): When two or more transactions run concurrently, their effects must be isolated from one another. Durability(持久性): If a transaction has completed its work, its effect should not get lost while the system fail, even if it fails immediately after the transaction completes.,1.2 Overview of a DBMS,Query processor：(skip) Query compiler(編譯器): Translates the query into an internal form called a query plan. Query parser(分析器): build a tree structure. Query preprocessor(預(yù)處理器): perform the initial query plan Query optimizer(優(yōu)化器): Execution engine: It executes each of the steps in the chosen query plan. It gets the data from the database into buffers in order to manipulate that data. It needs to interact(交流) with the scheduler(調(diào)度器) to avoid accessing data that is locked, and with the log manager to make sure that all database changes are properly logged.,1.2 Overview of a DBMS,Client/Serv