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

1、COMP2311CourseDatabase Management SystemsOverviewCOMP2312Course DetailsTextbookDatabase System Concepts, McGraw Hill (5th Edition) A. Silberschatz, H. Korth, and S. Sudarshan. ReferenceDatabase Management Systems McGraw Hill (3rd Edition)Raghu Ramakrishnan and Johannes Gehrke. COMP2313Course Details

2、Grading Scheme:Assignment and Experiment 30%Final Exam 70%COMP2314Course OutlineER Model Relational Model and Relational Algebra SQL Functional Dependencies and Relational Database Design Storage and File Systems Tree and Hash Indexes Query Processing and Implementation of Relational Operators Query

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

4、cal role in almost all areasBanking: all transactionsAirline: reservation, schedulesUniversities: registration, gradesSales: customers, products, purchasesManufacturing: production, inventory, orders, supply chain供应链Human resources人力资源: employee records, salaries, tax deductionsCOMP2317What is a Dat

5、abase?A database can be of any size and of varying 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 informati

6、on filed by taxpayers.COMP2318What 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 “Student Name, ID, Age, Gen

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

8、护 and utilize ju:tlaz使用a database.在不引起混淆的情况下常常把数据库系统简称为数据库COMP23110What is a DBMS?DBMSInsert recordsDelete recordsUpdate recordsQuery recordsCOMP23111What is a DBMS?Commercial DBMSCompanyProductOracleOracle 8i, 9i, 10gIBMDB2, Universal ServerMicrosoftAccess, SQL ServerSybaseAdaptive ServerInformixDy

9、namic ServerWorlds 2nd largest software companyCEO, Larry Ellison, worlds 2nd richestWorlds 2nd largest after Informix acquisitionCOMP23112What is a DBMS?Commercial DBMSCompanyProductOracleOracle 8i, 9i, 10gIBMDB2, Universal ServerMicrosoftAccess, SQL ServerSybaseAdaptive ServerInformixDynamic Serve

10、rAccess comes with MS OfficeCEO, John Chen, grown up in HKAcquired by IBM in 2001COMP23113Why 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?COMP23114Drawback of File SystemData Redundancy 数据冗余 and Inco

11、nsistencyE.g., consider a bank applicationaddress of a customer in the file of “saving-accounts” andthe file of “checking-accounts”A good design of DBMS can avoid data redundancy and inconsistency.COMP23115Drawback of File SystemDifficulty in accessing dataNeed to write a new program to carry out ea

12、ch new taskIt is easy to obtain data with DBMSCOMP23116Drawback of File SystemIntegrity problems完整性E.g., consider a bank applicationThe balance cannot be below $1000The day of a month cannot exceed 31DBMS can check the integrityautomaticallyCOMP23117Drawback of File SystemAtomicity of updatesE.g., c

13、onsider a bank applicationWe 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 executedDBMS makes sure that Step 1 and Step 2 can be executed to

14、gether even with a crash (We call the execution is atomic.)COMP23118Drawback of File SystemConcurrent Access并发存取by multiple usersUncontrolled concurrent accesses can lead to inconsistenciesE.g., consider a bank applicationThere is an account shared by 2 customers A and BCustomers A and B withdraw $1

15、000 concurrentlyDBMS makes sure that the concurrentaccess cannot lead to this problemRead 5000Read 50005000 - 1000Write 40005000 - 1000Write 4000ABCOMP23119Drawback of File SystemSecurity ProblemsE.g., consider a bank applicationWe do not want system programmers to have permissions to read some data

16、 (e.g., Andy Laus saving account and Joey Yungs saving account)Need a lot of effort to re-write a program for this permission systemDBMS can enforce that different users havedifferent permissions to access differentparts of the dataCOMP23120Advantages of DBMSWith the use of DBMS, we have the followi

17、ng advantagesData independenceEfficient data accessData integrity and security Data administrationConcurrent access and crash recovery故障修复Overall: Reduced application development timeCOMP23121Data ModelsA data model is a collection of tools or concepts for describing data, the meaning of data, data

18、relationships and data constraintsObject-based logical models基于对象的逻辑模型Entity-Relationship Model (ER Model)idBorrowerCustomerLoanCustomer-nameCustomer-streetCustomer-cityLoan-numberamountCOMP23122Data Models Three different record - based logical models are usually used. They are: Hierarchical Model,

19、 Network Model,网状模型 Relational ModelRecord-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, Hierarchical Model,层次模型 object-oriented model,面向对象 objected-relation

20、al modelWe will focus on the dominant Relational model.A description of data in terms of a data model is called a schema.图表COMP23123Data AbstractionHide certain details of how data is stored and maintainedPhysical level: 物理层how and where data are actually stored, low level data structures are specif

21、ied 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 type of usersCOMP23124Data AbstractionView LevelLogical LevelPhysical LevelView

22、1View 2View 3View nCOMP23125Data AbstractionConceptual LevelPhysical arrangement of records by an index treeD43389C73334F89328A34455C23444E32399G29021COMP23126Data AbstractionA view when posting the grades to all studentsViews of a company with data on employees, departments, products, Payroll secti

23、on: view on employees, departments, salariesSales department: view on products, prices, sales, customersPurchasing department: view on parts, with pricingCOMP23127Data IndependencePhysical Data IndependenceChange of physical database schemas without change of conceptual database schemas or applicati

24、on programsLogical data independenceChange of conceptual schemas without change of external viewsCOMP23128What languages does the computer speak?Data Definition Language (DDL)资料定义语言，由CREATE、ALTER与DROP三个语法所组成A language that specifies data schemasData Manipulation Language (DML)数据操纵语言，以INSERT、UPDATE、D

25、ELETE三种指令为核心，分别代表插入、更新与删除A language to facilitate the retrieval, update of data in the databaseFor retrieval, we query the database with the query language, which is part of the DMLALTER 是负责数据库对象修改的指令，相较于 CREATE 需要定义完整的数据对象参数，ALTER 则是可依照要修改的幅度来决定使用的参数，因此使用上并不会太困难，例如：ALTER TABLE doc_exa ADD column_b

26、VARCHAR(20) NULL ; - 在資料表 doc_exa 中加入一個新的欄位，名稱為 column_b，資料型別為 varchar(20)，允許 NULL 值；ALTER TABLE doc_exb DROP COLUMN column_b ; - 在資料表 doc_exb 中移除 column_b 欄位。COMP23129目前大多数的DBMS都支持对数据库对象的DDL操作，部分数据库 (如 PostgreSQL) 可把DDL放在交易指令中，也就是它可以被撤回 (Rollback)COMP23130COMP23131People who deal with databasesData

27、base Administrator (DBA): Person(s) who has central control over the database and is responsible for the following tasksSchema definition/modificationStorage structure definition/modificationAuthorization of data accessIntegrity constraint specificationMonitoring performanceResponding to changes in

28、requirementsCOMP23132People who deal with databasesApplication ProgrammersEmbed DML calls in program written in a host language (e.g., Cobol, C, Java). (DML stands for data manipulation language)e.g., programs that generates payroll checks, transfer funds between accountsSophisticated usersForm requ

29、est in database query languageNave usersInvokes one of the permanent application programs that have been written previouslye.g. transfer transfer fund between accountsCOMP23133Structure of a DBMSQuery Optimization and ExecutionRelational OperatorsFiles and Access MethodsBuffer Management缓冲器Disk Spac

30、e ManagementCOMP23134HistoryThe 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 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).COMP23135HistoryIn late 1980s and 1990s,