数据库管理系统概述英文版课件：1 introduction to databases.pptx

1、CourseDatabase Management Systems Overview,COMP231,1,Course Details,COMP231,2,TextbookDatabase System Concepts,McGraw Hill(5th Edition)A.Silberschatz,H.Korth,and S.Sudarshan.,Reference,Database Management Systems McGraw Hill(3rd Edition)Raghu Ramakrishnan and Johannes Gehrke.,Course Details,COMP231,

2、3,Grading Scheme:Assignment and Experiment 30%Final Exam 70%,Course Outline,COMP231,4,ER ModelRelational Model and Relational AlgebraSQLFunctional Dependencies and Relational Database DesignStorage and File SystemsTree and Hash IndexesQuery Processing and Implementation of Relational OperatorsQuery

3、OptimizationTransactionsConcurrency Control ProtocolsDatabase Recovery,What is a Database?,COMP231,5,A collection of data,typically describing the activities of one or more related organizations数据库指的是以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗 余度的特点、是与应用程序彼此独立的数据集合,What is a Database?,COMP231,6,Databases play a cr

4、itical role in almost all areas,Banking:all transactions Airline:reservation,schedules Universities:registration,gradesSales:customers,products,purchasesManufacturing:production,inventory,orders,supply chain供应链Human resources人力资源:employee records,salaries,tax deductions,What is a Database?,COMP231,7

5、,A database can be of any size and ofvarying complexity.,For example,the list of names and address of friendsThe book catalog目录 of a large library may contain half a million recordsA database of much greater size and complexity is maintained by the government to keep track of the tax information fil

6、ed by taxpayers.,What is a Database?,A schema is the definition of a database.It defines the meaning of data.An instance of a database is the collection of data in the database at a particular point of time(snap-shot).For example,in the above,the schema is“StudentName,ID,Age,Gender,Entrance Year,Gra

7、de”.The remaining rows in the table make up an instance of the database.,COMP231,8,What is a DBMS?数据库管理系统（DBMS）,COMP231,9,DBMS Database Management System数据库管理系统（Database Management System）是位于用户与操作系统之间的一层数据管理软件。A DBMS is a collection of software programs to enable users to create,maintain维护 and utili

8、ze ju:tlaz使用a database.在不引起混淆的情况下常常把数据库系统简称为数据库,What is a DBMS?,DBMSInsert recordsDelete recordsUpdate recordsQuery records,COMP231,10,Commercial DBMS,What is a DBMS1.?Worlds 2nd largest software,company2.CEO,Larry Ellison,worlds 2nd,richest,Worlds 2nd largest after Informix acquisition,COMP231,11,W

9、hat is a DBMS?,Commercial DBMS,Access comes with MS Office,John Chen,grown up in HK,Acquired by IBM in 2001,COMP231,12,Why do we need a DBMS?,To reduce application development timeSuppose we are given a collection of raw files which occupy 500GB,What are the drawbacks?,COMP231,13,Drawback of File Sy

10、stem,Data Redundancy 数据冗余 and InconsistencyE.g.,consider a bank application,address of a customer inthe file of“saving-accounts”and,the file of“checking-accounts”A good design of DBMS can avoid data redundancy and inconsistency.,COMP231,14,Drawback of File System,Difficulty in accessing dataNeed to

11、write a new program to carry out each new task,It is easy to obtain data with DBMS,COMP231,15,Drawback of File System,Integrity problems完整性E.g.,consider a bank application,The balance cannot be below$1000 The day of a month cannot exceed 31,DBMS can check the integrity automatically,COMP231,16,COMP2

12、31,17,Drawback of File System,4.Atomicity of updates,E.g.,consider a bank application,We want to transfer$100 from account A to account BSteps:,Step 1:We deduct$100 from account A(A-100),Step 2:Then,we increment$100 in account B(B+100)If the system crashes at Step 1,then Step 2 cannot be executed,DB

13、MS makes sure that Step 1 and Step 2can be executed together even with a crash(We call the execution is atomic.),18,Drawback of File System,5.Concurrent Access并发存取by multiple users,Uncontrolled concurrent accesses can lead to inconsistenciesE.g.,consider a bank application,There is an account shared

14、 by 2 customers A and B Customers A and B withdraw$1000 concurrently,DBMS makes sure that the concurrent access cannot lead to this problem,Read 5000,Read 5000,Write 4000COMP231,5000-10005000-1000,Write 4000,A,B,COMP231,19,Drawback of File System,Security ProblemsE.g.,consider a bank applicationWe d

15、o not want system programmers to have permissions to read some data(e.g.,Andy Laus saving account and Joey Yungs saving account)Need a lot of effort to re-write a program for this permission system,DBMS can enforce that different users have different permissions to access different parts of the data

16、,Advantages of DBMS,COMP231,20,With the use of DBMS,we have the following advantages,Data independence Efficient data accessData integrity and security Data administrationConcurrent access and crash recovery故障修复,Overall:Reduced application development time,Data Models,A data model is a collection of

17、 tools or concepts for describing data,the meaning of data,data relationships and data constraintsObject-based logical models基于对象的逻辑模型Entity-Relationship Model(ER Model),id,Borrower,Customer,Loan,Customer-name,Customer-street,Customer-city,Loan-number,amount,COMP231,21,Data Models Three different re

18、cord-based logical models are usually used.They are:Hierarchical Model,Network Model,网状模型 Relational Model,Record-based Logical ModelsRelational Model关系模型,Main concept:relation,basically a table with rows and columns.A column is also called a field or attributeOther models such as the Network Model,

19、Hierarchical Model,层次模型 object-oriented model,面向对象 objected-relational modelWe will focus on the dominant Relational model.A description of data in terms of a data model is called a schema.图表,COMP231,22,Data Abstraction,COMP231,23,Hide certain details of how data is stored and maintained,Physical le

20、vel:物理层how and where data are actually stored,low level data structures are specified at this levelConceptual level(logical level):逻辑层 describes what data should be stored in the database,and relationship and semantics of the dataView level:视图层 Relevant partial view of the database to be particular

21、type of users,Data Abstraction,Logical LevelPhysical Level,View LevelView 1View 2View 3,View n,COMP231,24,COMP231,25,Data Abstraction,Conceptual Level,Physical arrangement of records by an index treeD43389,C73334,F89328,A34455,C23444,E32399,G29021,Data Abstraction,A view when posting the grades to a

22、ll students,Views of a company with data on employees,departments,products,Payroll section:view on employees,departments,salaries Sales department:view on products,prices,sales,customers Purchasing department:view on parts,with pricing,COMP231,26,Data Independence,COMP231,27,Physical Data Independen

23、ceChange of physical database schemas without change of conceptual database schemas or application programsLogical data independenceChange of conceptual schemas without change of external views,What languages does the computer speak?,COMP231,28,Data Definition Language(DDL)资料定义语言，由CREATE、ALTER与 DROP

24、三个语法所组成,A language that specifies data schemas,Data Manipulation Language(DML)数据操纵语言，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除,A language to facilitate the retrieval,update of data in the database,For retrieval,we query the database with the query language,which is part of the DML,COMP231,29,ALTER 是

25、负责数据库对象修改的指令，相较于 CREATE 需要定义完整的数据对象参数，ALTER 则是可依照要修改的幅度来决定使用的参数，因此使用上并不会太困难，例如：ALTER TABLE doc_exa ADD column_b VARCHAR(20)NULL;-在資料表 doc_exa 中加入一個新的欄位，名稱為 column_b，資料型別為 varchar(20)，允許 NULL 值；ALTER TABLE doc_exb DROP COLUMN column_b;-在資料表 doc_exb 中移除column_b 欄位。,目前大多数的DBMS都支持对数据库对象的DDL操作，部分数据库(如 Po

26、stgreSQL)可把DDL放在交易指令中，也就是它可以被撤回(Rollback),COMP231,30,People who deal with databases,COMP231,31,Database Administrator(DBA):Person(s)who has central control over the database and is responsible for the following tasks,Schema definition/modificationStorage structure definition/modification Authorizati

27、on of data accessIntegrity constraint specification Monitoring performanceResponding to changes in requirements,People who deal with databases,COMP231,32,Application ProgrammersEmbed DML calls in program written in a host language(e.g.,Cobol,C,Java).(DML stands for data manipulation language),e.g.,p

28、rograms that generates payroll checks,transfer funds between accounts,Sophisticated usersForm request in database query languageNave usersInvokes one of the permanent application programs that have been written previouslye.g.transfer transfer fund between accounts,Structure of a DBMS,Query Optimizat

29、ion and ExecutionRelational Operators Files and Access MethodsBuffer Management缓冲器 Disk Space Management,COMP231,33,History,COMP231,34,The first DBMS was designed by Bachman at GE in early 1960sIn 1970 Codd at IBM proposed a new data representation framework called the relational data model.The SQL

30、query for relational databases,developed as part of IBMs System R project,was standardized in the late 1980s.The current standard,SQL-92,was adopted by ANSI(American National Standards Institute)and ISO(International Standards Organization).,History,COMP231,35,In late 1980s and 1990s,several vendors

31、(e.g.,IBMs DB2,Oracle 8)have extended their systems with the ability to store new data types such as images and text.Specialized systems developed for data warehouses,consolidating data from several databases.Entering the Internet Age,a new markup language XML is proposed for data access through a W

32、eb browser.As more and more data are collected,companies are also interested to mine useful information from their data.,ACID，是指数据库管理系统（DBMS）在写入/更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）,COMP231,36,原子性：一个事务（transaction）中

33、的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。,COMP231,37,一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。,COMP231,38,隔离性：当两个或者多个事务并发访问（此处访问指查询和修改的操作）数据库的同一数据时所表现出的相互关系。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。,COMP231,39,持久性：在事务完成以后，该事务对数据库所作的更改便持久地保存在数据库之中，并且是完全的