《数据库系统(英)(第三版)》全册配套精品完整课件.ppt

1、数据库系统(英)(第三版)全册配套精品完整课件Department of computer science & technologyDepartment of computer science & technology NJUST NJUSTDatabase Systems数据库系统数据库系统Database Systemsl 2.52.5学分上课、学分上课、 0.5 0.5学分上机学分上机 ，共，共3 3学分。学分。 l 学习目的：掌握数据库系统的基本概念、原理学习目的：掌握数据库系统的基本概念、原理和方法；学会有效地使用数据库管理系统，完和方法；学会有效地使用数据库管理系统，完成数据库的设

2、计和对数据库的编程。成数据库的设计和对数据库的编程。 l 学习方法：理论结合实践。学习方法：理论结合实践。 l 考核方式及要求：考核方式及要求：l作业（作业（2020）：按时认真完成作业。）：按时认真完成作业。l实验（实验（1010）：完成上机实验并提交实验报）：完成上机实验并提交实验报告。告。l闭卷考试（闭卷考试（7070）：英文试卷。）：英文试卷。Database SystemslReference booksReference booksJeffrey D.Ullman, Jennifer Widom. Jeffrey D.Ullman, Jennifer Widom. 数据库系统基础教

3、程数据库系统基础教程( (原书第原书第3 3版版). ). 岳丽华岳丽华等译等译. . 机械工业出版社机械工业出版社. 2009. 2009.Abraham Silberschatz, Henry F.Korth, Abraham Silberschatz, Henry F.Korth, S . S u d a r s h a n . D a t a b a s e S y s t e m S . S u d a r s h a n . D a t a b a s e S y s t e m Concepts (Fourth Edition). Concepts (Fourth Edition

4、). 机械工业机械工业出版社出版社. . http:/www-http:/www-db.stanford.edu/ullman/fcdb.htmldb.stanford.edu/ullman/fcdb.html1.1. The Worlds of Database SystemThe Worlds of Database System2.2. The Relational Model of DataThe Relational Model of Data3.3. * *Design Theory for Relational Design Theory for Relational Datab

5、asesDatabases4.4. High-Level Database ModelsHigh-Level Database Models5.5. Algebraic and Logical Query Algebraic and Logical Query Language Language 6.6. * *The Database Language SQLThe Database Language SQL7.7. Constraints and TriggersConstraints and Triggers8.8. Views and IndexesViews and Indexes9

6、.9. SQL in a Server EnvironmentSQL in a Server Environment10.10.Advanced topics in Relational Advanced topics in Relational DatabasesDatabasesA First Course in Database Systems1 The Worlds of Database SystemslThe Evolution of Database The Evolution of Database Systems Systems lOverview of a DBMS Ove

7、rview of a DBMS lOutline of Database-System Outline of Database-System StudiesStudies1.1 The Evolution of Database Systemsl Whats a database? Whats a database?In essence, a database is a In essence, a database is a collection of information that collection of information that exists over a long peri

8、od of exists over a long period of time, often many years.time, often many years.In common parlance(In common parlance(说法说法), a ), a database is a collection of database is a collection of data that is managed by a data that is managed by a database management system database management system (DBMS

9、).(DBMS).1.1 The Evolution of Database SystemslDatabase Applications:Database Applications:Banking: all transactions(Banking: all transactions(交易交易) )Airlines: reservations(Airlines: reservations(预定预定), ), schedules(schedules(航班表航班表) )Universities: registration, gradesUniversities: registration, gra

10、desSales: customers, products, ordersSales: customers, products, ordersManufacturing: production, Manufacturing: production, inventory(inventory(库存库存), orders, supply ), orders, supply chainchainHuman resources: employee records, Human resources: employee records, salaries, tax deductionssalaries, t

11、ax deductionsl Databases touch all aspects Databases touch all aspects of our lives.of our lives.1.1 The Evolution of Database Systemsl A DBMS is expected to:A DBMS is expected to:Data Definition Language (DDL): create Data Definition Language (DDL): create databases and specify their schema(databas

12、es and specify their schema(模模式式).). A schema is a group of A schema is a group of collections of sentences that are collections of sentences that are expressed by DDL to describe the expressed by DDL to describe the logical structure of the database logical structure of the database pletely.Data Qu

13、ery Language and Data Data Query Language and Data Manipulation Language: queryManipulation Language: query（查询）（查询） and and modifymodify（更新）（更新） the data based on the the data based on the schema of the database.schema of the database.Support the storage of very large Support the storage of very lar

14、ge amounts of data: many terabytes(1012 amounts of data: many terabytes(1012 bytes) or more, over a long period of bytes) or more, over a long period of time, allowing efficient access.time, allowing efficient access.1.1 The Evolution of Database SystemslA DBMS is expected to:A DBMS is expected to:E

15、nable durability(Enable durability(持久持久): the ): the recovery of the database in the recovery of the database in the face of failures(face of failures(故障故障), errors of ), errors of many kinds, or intentional misuse.many kinds, or intentional misuse.Support Data sharingSupport Data sharing（共享）（共享）: :

16、 control access to data from many control access to data from many users at once; without allowing users at once; without allowing the action of one user to affect the action of one user to affect other users(isolation other users(isolation 隔离性隔离性); ); without actions on the data to be without actio

17、ns on the data to be performed partially but not performed partially but not completely(atomicity completely(atomicity 原子性原子性).).1.1 The Evolution of Database SystemslDatabase systems evolved from file Database systems evolved from file systems.systems. Data redundancy(Data redundancy(冗余冗余) and ) an

18、d inconsistencyinconsistencyMultiple file formats, duplication of Multiple file formats, duplication of information in different files.information in different files.Difficulty in accessing data Difficulty in accessing data Need to write a new program to carry out Need to write a new program to carr

19、y out each new task.each new task.1.1 The Evolution of Database SystemslDatabase systems evolved from file Database systems evolved from file systems.systems. Integrity(Integrity(完整性完整性) problems) problemsIntegrity constraints(Integrity constraints(约束约束) (e.g. ) (e.g. account balance 0) become “buri

20、ed(account balance 0) become “buried(埋埋入入)” in program code rather than being )” in program code rather than being stated explicitly(stated explicitly(明确地明确地).).Hard to add new constraints or change Hard to add new constraints or change existing ones.existing ones.Atomicity of updatesAtomicity of up

21、datesFailures(Failures(故障故障) may leave database in an ) may leave database in an inconsistent state with partial updates inconsistent state with partial updates carried out.carried out.Example: Transfer of funds(Example: Transfer of funds(资金资金) from one ) from one account to another should either co

22、mplete account to another should either complete or not happen at all.or not happen at all.1.1 The Evolution of Database SystemslDatabase systems evolved from file Database systems evolved from file systems.systems. Concurrent(Concurrent(并发并发) access by multiple ) access by multiple usersusersUncont

23、rolled concurrent accesses can lead Uncontrolled concurrent accesses can lead to inconsistencies.to inconsistencies.Example: Two people reading a balance and Example: Two people reading a balance and updating it at the same time.updating it at the same time.Security problemsSecurity problemsHard to

24、provide user access to some, but Hard to provide user access to some, but not all, data.not all, data.1.1 The Evolution of Database Systemsl Late 1960s and 1970s:Late 1960s and 1970s:Hard disks allow direct access to dataHard disks allow direct access to dataNetwork and hierarchical data models in N

25、etwork and hierarchical data models in widespread usewidespread useThe early DBMS didnt support high-level The early DBMS didnt support high-level query languages.query languages.1.1 The Evolution of Database Systemsl 1970:1970:Ted Codd defines the relational data modelTed Codd defines the relationa

26、l data modelReceived the ACM Turing Award in 1981Received the ACM Turing Award in 1981IBM Research begins System R prototypeIBM Research begins System R prototypeMain idea: organize all the data in Main idea: organize all the data in database database as tables called relations. as tables called rel

27、ations.Queries could be expressed in a very high-Queries could be expressed in a very high-level language to increase the efficiency level language to increase the efficiency of database programmers.of database programmers.1.1 The Evolution of Database Systemsl 1980s:1980s:Research relational protot

28、ypes evolve Research relational prototypes evolve into commercial(into commercial(商用的商用的) systems) systemsSQL becomes industrial standardSQL becomes industrial standardParallel and distributed(Parallel and distributed(分布式分布式) database ) database systemssystemsObject-oriented database systemsObject-o

29、riented database systemsl 1990s:1990s:Large decision support and data-mining(Large decision support and data-mining(数据挖数据挖掘掘) applications) applicationsLarge multi-terabyte data warehouses(Large multi-terabyte data warehouses(数据仓库数据仓库) )Emergence(Emergence(出现出现) of Web commerce() of Web commerce(商务商

30、务) )l 2000s:2000s:XML(XML(可扩展模型语言可扩展模型语言) and XQuery standards) and XQuery standards1.2 Overview of a Database Management System lA DBMS is a powerful tool for A DBMS is a powerful tool for creating and managing large creating and managing large amounts of data efficiently and amounts of data effici

31、ently and allowing it to persist over long allowing it to persist over long periods of time, safely.periods of time, safely.1.2 Overview of a Database Management System 1.2 Overview of a Database Management System . . . .DBMSDBMSdatabasedatabaseDatabase Management SystemDatabase Management Systemrea

32、d/write pagesread/write pagesStorage managerStorage managerStorageStorageindex, file, andindex, file, andrecord requestsrecord requestsindex/file/index/file/record mangerrecord mangerDDL commandsDDL commandsQueriesQueries, , updatesupdatesQuery compilerQuery compilerUser/applicationUser/applicationD

33、DL compilerDDL compilerDatabase administratorDatabase administratorLockLocktabletableLogging and Logging and recoveryrecoveryConcurrencyConcurrencycontrolcontrolloglogpagespagespage commandspage commandsBuffer managerBuffer managerbuffersbuffersdata,data,metadata,metadata,indexsindexsmetadatametadat

34、aQuery planQuery planExecution Execution engineengineMetadata,Metadata,statisticsstatistics单线框单线框 系统成分系统成分双线框双线框 内存中的数据结内存中的数据结构构实线实线 控制和数据流控制和数据流虚线虚线 数据流数据流 TransactionTransaction manager managerTransactioTransaction commandsn commands1.2 Overview of a Database Management System l Two types of user

35、:Two types of user:Conventional users and application Conventional users and application programs.programs.A database administrator(DBA): a person A database administrator(DBA): a person or persons responsible for the or persons responsible for the structure or schema of the database.structure or sc

36、hema of the database.l Three kinds of commands to the DBMS:Three kinds of commands to the DBMS:Queries , updates : commands to affect Queries , updates : commands to affect the content of the database or the content of the database or extract(extract(提取提取) data from the database. ) data from the dat

37、abase. Transaction commands: commands to tell Transaction commands: commands to tell the transaction manager when the transaction manager when transactions begin and end.transactions begin and end.DDL commands: commands to change the DDL commands: commands to change the schema of the database or cre

38、ate a new schema of the database or create a new database by DBA.database by DBA.1.2 Overview of a Database Management System lStorage and buffer manager: Storage and buffer manager: includes the storage manager includes the storage manager and the buffer manager:and the buffer manager:storage manag

39、er: controls the storage manager: controls the placement of data on disk and its placement of data on disk and its movement between disk and main movement between disk and main memory, keeps track(memory, keeps track(跟踪跟踪) of the ) of the location of files on the disk and location of files on the di

40、sk and obtains the block or blocks obtains the block or blocks containing a file on request from containing a file on request from the buffer manager.the buffer manager.buffer manager: partition(buffer manager: partition(分割分割) ) the available main memory into the available main memory into buffers.b

41、uffers.1.2 Overview of a Database Management System l The kinds of information:The kinds of information:Data: the contents of the database Data: the contents of the database itself.itself.Metadata(Metadata(元数据元数据): the database ): the database schema(schema(模式模式) that describes the ) that describes

42、the structure of, and constraints(structure of, and constraints(约束约束) on, ) on, the database.the database.Indexes(Indexes(索引索引): data structures that ): data structures that support efficient access to the data.support efficient access to the data.Statistics(Statistics(统计统计): information gathered ):

43、 information gathered and stored by the DBMS about data and stored by the DBMS about data properties.properties.Log(Log(日志日志) records: information about ) records: information about recent changes to the database, these recent changes to the database, these support durability(support durability(持久性持

44、久性) of the ) of the database.database. read/write pagesread/write pagesStorage managerStorage managerStorageStorageindex, file, andindex, file, andrecord requestsrecord requestsindex/file/index/file/record mangerrecord mangerDDL commandsDDL commandsQueriesQueries, , updatesupdatesQuery compilerQuery

45、 compilerUser/applicationUser/applicationDDL compilerDDL compilerDatabase administratorDatabase administratorLockLocktabletableLogging and Logging and recoveryrecoveryConcurrencyConcurrencycontrolcontrolloglogpagespagespage commandspage commandsBuffer managerBuffer managerbuffersbuffersdata,data,met

46、adata,metadata,indexsindexsmetadatametadataQuery planQuery planExecution Execution engineengineMetadata,Metadata,statisticsstatistics单线框单线框 系统成分系统成分双线框双线框 内存中的数据结内存中的数据结构构实线实线 控制和数据流控制和数据流虚线虚线 数据流数据流 TransactionTransaction manager managerTransactioTransaction commandsn commands1.2 Overview of a Data

47、base Management System lTransaction Processing:Transaction Processing:Transaction(Transaction(事务事务): a group of ): a group of operations that must appear to operations that must appear to have been executed together have been executed together sequentially, as a unit.sequentially, as a unit.Transact

48、ion manager: Transaction manager: Concurrency-control(Concurrency-control(并发控制并发控制) manager, ) manager, or scheduler(or scheduler(调度器调度器): responsible for ): responsible for assuring atomcity(assuring atomcity(原子性原子性) and ) and isolation(isolation(隔离性隔离性) of transactions. ) of transactions. Lock.Loc

49、k.Logging and recovery manager: Logging and recovery manager: responsible for the durability(responsible for the durability(持久性持久性) ) of transactions. Logging.of transactions. Logging.1.2 Overview of a Database Management System lProperties: ACIDProperties: ACIDAtomicity: either all of a Atomicity:

50、either all of a transaction be executed or none of transaction be executed or none of it is.it is.Consistency(Consistency(一致性一致性): keep ): keep consistent state.consistent state.Isolation(Isolation(隔离性隔离性): When two or ): When two or more transactions run concurrently, more transactions run concurre