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

Technology and Application of Database (數(shù)據(jù)庫技術與應用),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版 機械工業(yè)出版社 ) 或中譯本數(shù)據(jù)庫系統(tǒng)基礎教程 沃爾曼，等著；岳麗華，龔育昌，等譯 機械工業(yè)出版社 / ullman/fcdb.html 機械工業(yè)出版社/華章北京辦事處：白濤（7折） 電話68995264Email： 北京市西城區(qū)百萬莊南街1號413室 100037,Assessment (provisional 暫定),Total Mark = Regular grade(quiz 平時作業(yè) 隨堂小測驗 上機作業(yè) 安裝MySQL，SQL查詢截圖，任意的數(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(中國/外語文學/新聞傳播/藝術), 06 history 07 science(數(shù)/理/化), 09 agricultural, 10 medical science, 11 military(軍事學), 12 management science 08 engineering mechanical(力學), optical(光學), instruments(儀器), materials (材料), metallurgy(冶金), power(動力), electrical(電氣), electronic (電子), information communication(信息通信), control (控制), construction(建筑), civil engineering(土木), water conservancy(水利) 0802 mechanical engineering(機械) 0812 computer science and technology 081201 computer architecture 081202 computer software & theory: data structure & alg. 081203 computer applications: multimedia, DB.,By ministry of education,為了更好的理解，還是看中文吧,多媒體技術在知識領域的位置,科學門類 01 哲學 02 經(jīng)濟學( 理論/應用) 03 法學 04 教育學 05 文學(中/外語文學/新聞傳播/藝術) 06 歷史學 07 理學(數(shù)/理/化) 09 農 10 醫(yī) 11 軍事學 12 管理學 08 工學 力學，光學，儀器，材料，冶金，動力，電氣，電子，信息通信，控制，建筑，土木，水利， 0812 計算機科學與技術（一級學科） 081201 計算機系統(tǒng)結構（二級學科） 081202 計算機軟件與理論 081203 計算機應用技術 數(shù)據(jù)庫,Knowledge in Computer Science,Knowledge in Computer Science,Why study ?,Knowledge + Thought = Behavior College/life time,學而優(yōu)則仕， 仕而優(yōu)則學,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,概述 關系數(shù)據(jù)模型 建模 ER圖 關系代數(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ù)庫管理系統(tǒng)的結構 本課程內容,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說法), 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,一個數(shù)據(jù)庫是由一個數(shù)據(jù)庫管理系統(tǒng)(DBMS)所管理的一個數(shù)據(jù)集合。,1.1 The Evolution of Database Systems,數(shù)據(jù)管理技術具體就是指人們對數(shù)據(jù)進行收集、組織、存儲、加工、傳播和利用的一系列活動的總和，經(jīng)歷了人工管理、文件管理、數(shù)據(jù)庫管理三個階段。每一階段的發(fā)展以數(shù)據(jù)存儲冗余不斷減小、數(shù)據(jù)獨立性不斷增強、數(shù)據(jù)操作更加方便和簡單為標志，各有各的特點。,1.1 The Evolution of Database Systems,Database Applications (Where need DB ?): Banking: all transactions(交易) Airlines: reservations(預定), schedules(航班) Universities: registration, grades Sales: customers, products, orders(c.f 電商) Manufacturing: production, inventory(庫存), 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 查詢語言and Data Manipulation Language控制語言: 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(持久存儲): 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)一/技術壟斷考慮) 2. Difficulty in accessing data (訪問困難) 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. (不同用戶的不同權限),1.1 The Evolution of Database Systems,Late 1960s and 1970s: (skip) Hard disks allow direct access to data 直接訪問 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. 早期語言,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.(關系:第二章內容) 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ù)倉庫) Emergence(出現(xiàn)) of Web commerce 2000s: XML(可擴展模型語言) 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ù)，長期安全保存,數(shù)據(jù)庫管理系統(tǒng)(DBMS)是操縱和管理數(shù)據(jù)庫的一組軟件，它是數(shù)據(jù)庫系統(tǒng)(DBS)的重要組成部分。 數(shù)據(jù)庫管理系統(tǒng)的主要功能有 數(shù)據(jù)定義功能DD、 數(shù)據(jù)操縱功能DM、 數(shù)據(jù)庫的運行管理和數(shù)據(jù)庫的建立 以及維護等4個方面。,1.2 Overview of a DBMS,1.2 Overview of a DBMS,Application 1,Application 2,. . .,Application n,DBMS,database,Database Management System,手機微博 網(wǎng)頁微博 門戶登錄,單線框 系統(tǒng)成分 雙線框 內存中的數(shù)據(jù)結構 實線 控制和數(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ù)庫管理員): 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. (事務) DDL commands: commands to change the schema of the database or create a new database by DBA. (管理員更改數(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 淘寶手機類目商品最低價格) Log(日志) records: information about recent changes to the database, these support durability(持久性) of the database. Statistics(統(tǒng)計): 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(事務): a group of operations that must appear to have been executed together sequentially, as a unit. Transaction manager: Concurrency-control(并發(fā)控制) manager, or scheduler(調度器): responsible for assuring atomcity(原子性) and isolation(隔離性) of transactions. Logging and recovery manager: responsible for the durability(持久性) of transactions.,單線框 系統(tǒng)成分 雙線框 內存中的數(shù)據(jù)結構 實線 控制和數(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(預處理器): 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(調度器) 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