数据库系统基础教程DatabaseSystems.ppt

Department of computer science & technology NJUST,Database Systems 数据库系统,Database Systems,2.5学分上课、 0.5学分上机 ，共3学分。 学习目的：掌握数据库系统的基本概念、原理和方法；学会有效地使用数据库管理系统，完成数据库的设计和对数据库的编程。 学习方法：理论结合实践。 考核方式及要求： 作业（20）：按时认真完成作业。 实验（10）：完成上机实验并提交实验报告。 闭卷考试（70）：英文试卷。,Database Systems,Reference books Jeffrey D.Ullman, Jennifer Widom. 数据库系统基础教程(原书第3版). 岳丽华等译. 机械工业出版社. 2009. Abraham Silberschatz, Henry F.Korth, S.Sudarshan. Database System Concepts (Fourth Edition). 机械工业出版社. /ullman/fcdb.html,The Worlds of Database System The Relational Model of Data *Design Theory for Relational Databases High-Level Database Models 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,A First Course in Database Systems,1 The Worlds of Database Systems,The Evolution of Database Systems Overview of a DBMS Outline of Database-System Studies,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(说法), a database is a collection of data that is managed by a database management system (DBMS).,1.1 The Evolution of Database Systems,Database Applications: Banking: all transactions(交易) Airlines: reservations(预定), schedules(航班表) Universities: registration, grades Sales: customers, products, orders Manufacturing: production, inventory(库存), orders, supply chain Human resources: employee records, salaries, tax deductions Databases touch all aspects of our lives.,1.1 The Evolution of Database Systems,A DBMS is expected to: Data Definition Language (DDL): create databases and specify their schema(模式). A schema is a group of collections of sentences that are expressed by DDL to describe the logical structure of the database completely. 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.,1.1 The Evolution of Database Systems,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.,Data redundancy(冗余) and inconsistency Multiple file formats, duplication of information in different files. Difficulty in accessing data Need to write a new program to carry out each new task.,1.1 The Evolution of Database Systems,Database systems evolved from file systems.,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. 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 systems evolved from file systems.,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. Security problems Hard to provide user access to some, but not all, data.,1.1 The Evolution of Database Systems,Late 1960s and 1970s: Hard disks allow direct access to data Network and hierarchical data models in widespread use 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 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.2 Overview of a Database Management System,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.,1.2 Overview of a Database Management System,1.2 Overview of a Database Management System,Application 1,Application 2,. . .,Application n,DBMS,database,Database Management System,单线框 系统成分 双线框 内存中的数据结构 实线 控制和数据流 虚线 数据流,1.2 Overview of a Database Management System,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 affect the content of the database or extract(提取) data from 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 Database Management System,Storage and buffer manager: 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.,1.2 Overview of a Database Management System,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. Indexes(索引): data structures that support efficient access to the data. Statistics(统计): information gathered and stored by the DBMS about data properties. Log(日志) records: information about recent changes to the database, these support durability(持久性) of the database.,单线框 系统成分 双线框 内存中的数据结构 实线 控制和数据流 虚线 数据流,1.2 Overview of a Database Management System,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. Lock. Logging and recovery manager: responsible for the durability(持久性) of transactions. Logging.,1.2 Overview of a Database Management System,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 Database Management System,Query processor： 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 Database Management System,Client/Server (C/S) architecture,DB Client,DB Client,DB Server,Java C/C+ PowerBuilder Delphi VB ,MS Access MS SQL Server Sybase MySQL Oracle IBM DB2 ,Client: the process sending queries or other commands. Server: the process executing queries or other commands,1.3 Outline of Database-System Studies,Three types of ideas related to database systems: Design：How to build a useful database? Requirement analysis, data modeling, relational modeling. Programming：How to express queries and other operations on database? Iimplementation：How to build a DBMS?,1.3.1 Database Design,Chapter 2, 3 and 4 cover design. Chapter 2: The Relational Model of Data Chapter 3: Design Theory for Relational Databases Chapter 4: High-Level Database Models,1.3.2 Database Programming,Chapter 5 through 10 cover database programming. Chapters 5: Algebraic and Logical Query Language Chapters 6: The Database Language SQL Chapters 7: Constraints and Triggers Chapters 8: Views and Indexes Chapters 9: SQL in a Server Environment Chapters 10: Advanced topics in Relational Databases,1.3.3 An example of database application,Well build a marketing database system for a sale company. It will manage all the following information: 1. Manage all department information in the company (such as “Shanghai sale department”, “JiangSu sale department”). Also manage every salesman information in those departments including exclusive(唯一的) employee number, ID card number, and some private information (such as name, gender, birthday and phone number). By the way one of salesmen will act as the department manager in his department.,1.3.3 An example of database application,2. Manage a group of customers: name, province(省), city, company name, phone number. 3.Manage all the product information: manufacturers (e.g. Chunlan, Hailer ), types (e.g. motorcycle, air conditioner ), specifications (e.g. “MT125“, “RE1500“ ), prices, descriptions. 4. Manage sales order which record each deal(交易) has been done. Notes: every sales order contains an unique order No. , sign date, a corresponding customer, a salesman, and at least one kind of products. Any product in the order should have its quantity and unit price which will be used to calculate the total prices.,为某商品销售公司构作一个销售业务数据库系统，该系统至少应管理以下内容： 公司分为多个下属部门(Department)，如“江苏销售部”，“上海销售部”等。每个部门有若干销售员(Salesman)，每个销售员有唯一的员工号(如“S0025”)和身份证号，也包含姓名、性别、出生日期、电话等信息。一个销售员仅属于一个部门，且每个部门有一个销售员担任部门经理。 。 系统应管理客户信息(Customer)，包含：名称、省、市、单位名称、电话等。 系统应管理所有销售的商品(Product)：制造商(如“春兰”、“海尔”)、种类(如“摩托车”、“空调机”)、规格(如“MT125”型摩托车、“RE1500”型空调)、价格、功能及性能描述。 该系统用销售订单(Sales order)表示销售业务。每个订单有一个订单号和签订日期，且对应一个客户和一个销售员。一个订单至少销售一种产品，并可销售多种产品，且每种产品应确定其销售数量和单价，以计算销售金额。,1.3.3