數(shù)據(jù)庫(kù)管理系統(tǒng)概述英文版課件：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數(shù)據(jù)庫(kù)指的是以一定方式儲(chǔ)存在一起、能為多個(gè)用戶共享、具有盡可能小的冗余度的特點(diǎn)、是與應(yīng)用程序彼此獨(dú)立的數(shù)據(jù)集合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供應(yīng)鏈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?數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）DBMS Database Management System數(shù)據(jù)庫(kù)管理系統(tǒng)（Database Management System）是位于用戶與操作系統(tǒng)之間的一層數(shù)據(jù)管理軟件。A DBMS is a collection of software programs to enable users to create, maintain維

8、護(hù) and utilize ju:tlaz使用a database.在不引起混淆的情況下常常把數(shù)據(jù)庫(kù)系統(tǒng)簡(jiǎn)稱為數(shù)據(jù)庫(kù)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 數(shù)據(jù)冗余 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并發(fā)存取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故障修復(fù)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基于對(duì)象的邏輯模型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,網(wǎng)狀模型 Relational ModelRecord-based Logical ModelsRelational Model關(guān)系模型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,面向?qū)ο?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)資料定義語(yǔ)言，由CREATE、ALTER與DROP三個(gè)語(yǔ)法所組成A language that specifies data schemasData Manipulation Language (DML)數(shù)據(jù)操縱語(yǔ)言，以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 是負(fù)責(zé)數(shù)據(jù)庫(kù)對(duì)象修改的指令，相較于 CREATE 需要定義完整的數(shù)據(jù)對(duì)象參數(shù)，ALTER 則是可依照要修改的幅度來(lái)決定使用的參數(shù)，因此使用上并不會(huì)太困難，例如：ALTER TABLE doc_exa ADD column_b

26、VARCHAR(20) NULL ; - 在資料表 doc_exa 中加入一個(gè)新的欄位，名稱為 column_b，資料型別為 varchar(20)，允許 NULL 值；ALTER TABLE doc_exb DROP COLUMN column_b ; - 在資料表 doc_exb 中移除 column_b 欄位。COMP23129目前大多數(shù)的DBMS都支持對(duì)數(shù)據(jù)庫(kù)對(duì)象的DDL操作，部分?jǐn)?shù)據(jù)庫(kù) (如 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,