数据库技术与应用1TheWorldsofDatabaseSystem.ppt

Technology and Application of Database (数据库技术与应用),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版 机械工业出版社 ) 或中译本数据库系统基础教程 沃尔曼，等著；岳丽华，龚育昌，等译 机械工业出版社 / ullman/fcdb.html 机械工业出版社/华章北京办事处：白涛（7折） 电话68995264Email： 北京市西城区百万庄南街1号413室 100037,Assessment (provisional 暂定),Total Mark = Regular grade(quiz 平时作业 随堂小测验 上机作业 安装MySQL，SQL查询截图，任意的数据库设计,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(数/理/化), 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 经济学( 理论/应用) 03 法学 04 教育学 05 文学(中/外语文学/新闻传播/艺术) 06 历史学 07 理学(数/理/化) 09 农 10 医 11 军事学 12 管理学 08 工学 力学，光学，仪器，材料，冶金，动力，电气，电子，信息通信，控制，建筑，土木，水利， 0812 计算机科学与技术（一级学科） 081201 计算机系统结构（二级学科） 081202 计算机软件与理论 081203 计算机应用技术 数据库,Knowledge in Computer Science,Knowledge in Computer Science,Why study ?,Knowledge + Thought = Behavior College/life time,学而优则仕， 仕而优则学,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,概述 关系数据模型 建模 ER图 关系代数 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发展 数据库管理系统的结构 本课程内容,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,一个数据库是由一个数据库管理系统(DBMS)所管理的一个数据集合。,1.1 The Evolution of Database Systems,数据管理技术具体就是指人们对数据进行收集、组织、存储、加工、传播和利用的一系列活动的总和，经历了人工管理、文件管理、数据库管理三个阶段。每一阶段的发展以数据存储冗余不断减小、数据独立性不断增强、数据操作更加方便和简单为标志，各有各的特点。,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)/数据表 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 海量数据: 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（数据共享）: 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. (难以统一/技术垄断考虑) 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(并发)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 ) 网状、层次模型 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(数据挖掘) applications Large multi-terabyte data warehouses(数据仓库) Emergence(出现) 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. 管理海量数据，长期安全保存,数据库管理系统(DBMS)是操纵和管理数据库的一组软件，它是数据库系统(DBS)的重要组成部分。 数据库管理系统的主要功能有 数据定义功能DD、 数据操纵功能DM、 数据库的运行管理和数据库的建立 以及维护等4个方面。,1.2 Overview of a DBMS,1.2 Overview of a DBMS,Application 1,Application 2,. . .,Application n,DBMS,database,Database Management System,手机微博 网页微博 门户登录,单线框 系统成分 双线框 内存中的数据结构 实线 控制和数据流 虚线 数据流 略,略,1.2 Overview of a DBMS,Two types of user: Conventional users and application programs.用户 A database administrator(DBA 数据库管理员): 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. (管理员更改数据库结构/模式设计),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(元数据): 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(统计): 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(并发控制) manager, or scheduler(调度器): responsible for assuring atomcity(原子性) and isolation(隔离性) of transactions. Logging and recovery manager: responsible for the durability(持久性) of transactions.,单线框 系统成分 双线框 内存中的数据结构 实线 控制和数据流 虚线 数据流,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(优化器): 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