數(shù)據(jù)庫原理及其應用技術英文課件system databases

1、Database Systems數(shù)據(jù)庫系統(tǒng)Database Systems學習目的：掌握數(shù)據(jù)庫系統(tǒng)的基本概念、原理和方法；學會有效地使用數(shù)據(jù)庫管理系統(tǒng)，完成數(shù)據(jù)庫的設計和對數(shù)據(jù)庫的編程。 學習方法：理論結合實踐。 考核方式及要求：平時及作業(yè)（15）：按時認真完成作業(yè)。實驗（25）：完成上機實驗并提交實驗報告。閉卷考試（60）：英文試卷。Database SystemsReference booksJeffrey D.Ullman, Jennifer Widom. 數(shù)據(jù)庫系統(tǒng)基礎教程(原書第3版). 岳麗華等譯. 機械工業(yè)出版社. 2009. The Worlds of Database Sys

2、temThe Relational Model of Data*Design Theory for Relational DatabasesHigh-Level Database ModelsAlgebraic and Logical Query Language *The Database Language SQLConstraints and TriggersViews and IndexesSQL in a Server EnvironmentAdvanced topics in Relational DatabasesA First Course in Database Systems

3、1 The Worlds of Database SystemsThe Evolution of Database Systems Overview of a DBMS Outline of Database-System Studies1.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 parla

4、nce(說法), a database is a collection of data that is managed by a database management system (DBMS).1.1 The Evolution of Database SystemsDatabase Applications:Banking: all transactions(交易)Airlines: reservations(預定), schedules(航班表)Universities: registration, gradesSales: customers, products, ordersMan

5、ufacturing: production, inventory(庫存), orders, supply chainHuman resources: employee records, salaries, tax deductions Databases touch all aspects of our lives.1.1 The Evolution of Database SystemsA DBMS is expected to:Data Definition Language (DDL): create databases and specify their schema(模式). A

6、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

7、data: many terabytes(1012 bytes) or more, over a long period of time, allowing efficient access.1.1 The Evolution of Database SystemsA 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

8、（共享）: 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 SystemsDatabase systems evolved from file systems. Data re

9、dundancy(冗余) and inconsistencyMultiple 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 SystemsDatabase systems evolved from file systems. Integrity(完整性) problemsIntegrity con

10、straints(約束) (e.g. account balance 0) e “buried(埋入)” in program code rather than being stated explicitly(明確地).Hard to add new constraints or change existing ones.Atomicity of updatesFailures(故障) may leave database in an inconsistent state with partial updates carried out.Example: Transfer of funds(資

11、金) from one account to another should either complete or not happen at all.1.1 The Evolution of Database SystemsDatabase systems evolved from file systems. Concurrent(并發(fā)) access by multiple usersUncontrolled concurrent accesses can lead to inconsistencies.Example: Two people reading a balance and up

12、dating it at the same time.Security problemsHard to provide user access to some, but not all, data.1.1 The Evolution of Database SystemsLate 1960s and 1970s:Hard disks allow direct access to dataNetwork and hierarchical data models in widespread useThe early DBMS didnt support high-level query langu

13、ages.1.1 The Evolution of Database Systems1970:Ted Codd defines the relational data modelReceived the ACM Turing Award in 1981IBM Research begins System R prototypeMain idea: organize all the data in database as tables called relations.Queries could be expressed in a very high-level language to incr

14、ease the efficiency of database programmers.1.1 The Evolution of Database Systems1980s:Research relational prototypes evolve into commercial(商用的) systemsSQL es industrial standardParallel and distributed(分布式) database systemsObject-oriented database systems1990s:Large decision support and data-minin

15、g(數(shù)據(jù)挖掘) applicationsLarge multi-terabyte data warehouses(數(shù)據(jù)倉庫)Emergence(出現(xiàn)) of Web commerce(商務)2000s:XML(可擴展模型語言) and XQuery standards1.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 lon

16、g periods of time, safely.1.2 Overview of a Database Management System 1.2 Overview of a Database Management System Application 1Application 2.Application nDBMSdatabaseDatabase Management Systemread/write pagesStorage managerStorageindex, file, andrecord requestsindex/file/record mangerDDL commandsQ

17、ueries, updatesQuery compilerUser/applicationDDL compilerDatabase administratorLocktableLogging and recoveryConcurrencycontrollogpagespage commandsBuffer managerbuffersdata,metadata,indexsmetadataQuery planExecution engineMetadata,statistics單線框 系統(tǒng)成分雙線框 內(nèi)存中的數(shù)據(jù)結構實線 控制和數(shù)據(jù)流虛線 數(shù)據(jù)流 Transaction managerTran

18、saction commands1.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 af

19、fect 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 Sys

20、tem 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

21、 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(元數(shù)據(jù)): the database schema(模式) that describes the structure of, and constraints(約束) on

22、, the database.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. read/write pagesSt

23、orage managerStorageindex, file, andrecord requestsindex/file/record mangerDDL commandsQueries, updatesQuery compilerUser/applicationDDL compilerDatabase administratorLocktableLogging and recoveryConcurrencycontrollogpagespage commandsBuffer managerbuffersdata,metadata,indexsmetadataQuery planExecut

24、ion engineMetadata,statistics單線框 系統(tǒng)成分雙線框 內(nèi)存中的數(shù)據(jù)結構實線 控制和數(shù)據(jù)流虛線 數(shù)據(jù)流 Transaction managerTransaction commands1.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 manag

25、er: Concurrency-control(并發(fā)控制) 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: ACIDAtomicity:

26、 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 w

27、hile 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(預處理器): p

28、erform 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

29、with the log manager to make sure that all database changes are properly logged. read/write pagesStorage managerStorageindex, file, andrecord requestsindex/file/record mangerDDL commandsQueries, updatesQuery compilerUser/applicationDDL compilerDatabase administratorLocktableLogging and recoveryConcu

30、rrencycontrollogpagespage commandsBuffer managerbuffersdata,metadata,indexsmetadataQuery planExecution engineMetadata,statistics單線框 系統(tǒng)成分雙線框 內(nèi)存中的數(shù)據(jù)結構實線 控制和數(shù)據(jù)流虛線 數(shù)據(jù)流 Transaction managerTransaction commands1.2 Overview of a Database Management System Client/Server (C/S) architectureDB ClientDB ClientDB

31、 ServerJavaC/C+PowerBuilderDelphiVBMS AccessMS SQL ServerSybase MySQLOracleIBM DB2sql requestsql requestreplyreplyClient: the process sending queries or other commands.Server: the process executing queries or other commands1.3 Outline of Database-System StudiesThree types of ideas related to databas

32、e systems:Design：How to build a useful database? Requirement analysis, data modeling, relational modeling.Programming：How to express queries and other operations on database? Implementation：How to build a DBMS?1.3.1 Database DesignChapter 2, 3 and 4 cover design. Chapter 2: The Relational Model of D

33、ataChapter 3: Design Theory for Relational DatabasesChapter 4: High-Level Database Models 1.3.2 Database ProgrammingChapter 5 through 10 cover database programming.Chapters 5: Algebraic and Logical Query Language Chapters 6: The Database Language SQLChapters 7: Constraints and TriggersChapters 8: Vi

34、ews and IndexesChapters 9: SQL in a Server EnvironmentChapters 10: Advanced topics in Relational Databases1.3.3 An example of database application Well build a marketing database system for a sale company(supermarket). It will manage all the following information: 1. Manage all department informatio

35、n 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 sa

36、lesmen 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 condition

37、er ), 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 shoul

38、d have its quantity and unit price which will be used to calculate the total prices. 為某商品銷售公司構作一個銷售業(yè)務數(shù)據(jù)庫系統(tǒng)，該系統(tǒng)至少應管理以下內(nèi)容：公司分為多個下屬部門(Department)，如“江蘇銷售部”，“上海銷售部”等。每個部門有若干銷售員(Salesman)，每個銷售員有唯一的員工號(如“S0025”)和身份證號，也包含姓名、性別、出生日期、電話等信息。一個銷售員僅屬于一個部門，且每個部門有一個銷售員擔任部門經(jīng)理。 。 系統(tǒng)應管理客戶信息(Customer)，包含：名稱、省、市、單位名稱、電

39、話等。 系統(tǒng)應管理所有銷售的商品(Product)：制造商(如“春蘭”、“海爾”)、種類(如“摩托車”、“空調機”)、規(guī)格(如“MT125”型摩托車、“RE1500”型空調)、價格、功能及性能描述。 該系統(tǒng)用銷售訂單(Sales order)表示銷售業(yè)務。每個訂單有一個訂單號和簽訂日期，且對應一個客戶和一個銷售員。一個訂單至少銷售一種產(chǎn)品，并可銷售多種產(chǎn)品，且每種產(chǎn)品應確定其銷售數(shù)量和單價，以計算銷售金額。1.3.3 An example of database applicationDepartmentSalesmanCustomerSalesorderProductSalesitemsales forruns foras headerworks forunit-ofcontainsdeptidnameempididnon