A first course in Database Systems(1).ppt

1、A First Course in Database Systems,Department of computer science without allowing the action of one user to affect other users; without allowing simultaneous accesses to corrupt the data accidentally.,1.1 The Evolution of Database Systems,Database systems evolved from file systems.,Data redundancy

2、and inconsistency Multiple file formats, duplication of information in different files. Difficulty in accessing data Need to write a new program to carry out each new task. Data isolation multiple files and formats. Integrity problems Integrity constraints (e.g. account balance 0) become “buried” in

3、 program code rather than being stated explicitly. Hard to add new constraints or change existing ones.,1.1 The Evolution of Database Systems,Atomicity of updates Failures may leave database in an inconsistent state with partial updates carried out. Example: Transfer of funds from one account to ano

4、ther should either complete or not happen at all. Concurrent access by multiple users Concurrent accessed needed for performance. Uncontrolled concurrent accesses can lead to inconsistencies. Example: Two people reading a balance and updating it at the same time. Security problems Hard to provide us

5、er access to some, but not all, data.,1.1 The Evolution of Database Systems,Late 1960s and 1970s: Hard disks allow direct access to data Network and hierarchical data models in widespread use The early DBMS didnt support high-level query languages.,1.1 The Evolution of Database Systems,1970: Ted Cod

6、d defines the relational data model Would win the ACM Turing Award for this work IBM Research begins System R prototype Main idea: organize all the data in database as tables called relations. Queries could be expressed in a very high-level language to increase the efficiency of database programmers

7、.,1.1 The Evolution of Database Systems,1980s: Research relational prototypes evolve into commercial systems SQL becomes industrial standard Parallel and distributed database systems Object-oriented database systems,1990s: Large decision support and data-mining(数据挖掘) applications Large multi-terabyt

8、e data warehouses Emergence of Web commerce,2000s: XML and XQuery standards Automated database administration,1.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 long periods of time, safel

9、y.,1.2 Overview of a Database Management System,1.2 Overview of a Database Management System,Application 1,Application 2,. . .,Application n,DBMS,database,Database Management System,1.2 Overview of a Database Management System,Storage manager,Buffer manager,read/write pages,buffers,index/file/ recor

10、d manger,page commands,data, metadata, indexs,Execution engine,index, file, and record requests,Query compiler,Query plan,Queries, updates,User/application,Transaction manager,DDL compiler,DDL commands,Database administrator,Transaction commands,Logging and recovery,Concurrency control,Lock table,lo

11、g pages,metadata,Metadata, statistics,单线框 系统成分 双线框 内存中的数据结构 实线 控制和数据流 虚线 数据流,1.2 Overview of a Database Management System,Two types of user: Conventional users and application programs A database administrator(DBA) Three types of inputs to the DBMS: Queries: questions about the data two ways: SQL, A

12、PI Modifications: operations to modify the data Schema modifications: commands to change the schema of the database or create a new database by database administrators, DDL commands,1.2 Overview of a Database Management System,Query processor： Query compiler Translates the query into an internal for

13、m called a query plan. A query parser(分析器) A query preprocessor A query optimizer(优化器) Execution engine 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 with the log manager to make s

14、ure that all database changes are properly logged.,1.2 Overview of a Database Management System,Storage: A place where data is stored, contains data and metadata Data: the contents of the database itself. Metadata(元数据): the database schema that describes the structure of, and constraints on, the dat

15、abase. Indexes: data structures that support efficient access to the data. Indexes are part of the stored data, and a description of which attributes have indexes is part of the metadata.,1.2 Overview of a Database Management System,Storage manager and buffer manager: manages the reading and writing

16、 of information includes the storage manager and the buffer manager: storage manager: keeps track of the location of files on the disk and obtains the block or blocks containing a file on request from the buffer manager. block：4K-16K buffer manager: partition the available main memory into buffers.

17、page：1K-4K,1.2 Overview of a Database Management System,Transaction: a group of operations that must appear to have been executed together sequentially, as a unit. Properties: ACID Atomicity: either all of a transaction be executed or none of it is. Consistency: keep consistent state. Isolation: Whe

18、n 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 while the system fail, even if it fails immediately after the transaction completes.,1.2 Overview of a Database Manag

19、ement System,Transaction manager: be responsible for the integrity of the system Logging Concurrency control: lock Deadlock resolution,1.2 Overview of a Database Management System,Client/Server (C/S) architecture,DB Client,DB Client,DB Server,Java C/C+ PowerBuilder Delphi VB ,MS Access Sybase ASA My

20、SQL Oracle IBM DB2 ,sql request,sql request,reply,reply,Client: the process sending queries or other commands. Server: the process executing queries or other commands,1.3 Outline of Database-Systems Studies,Ideas related to database system can be divided into three broad categories： Design：How to bu

21、ild a useful database? Requirement analysis, data modeling, relational modeling. Programming：How to express queries and other operations on database? Iimplementation：How to build a DBMS?,1.3.1 Database Design,Chapter 2 and 3 cover design. Chapter 2: The Entity-Relationship Data Model Chapter 3: The

22、Relational Data Model,1.3.3 Example,Well build a marketing database system for a sale company (supermarket). It will manage all the following information: 1. Manage all departments information in the company (such as Shanghai sale department, JiangSu sale department). Also manage every salesman info

23、rmation 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 salesmen will act as the department manager in his department.,1.3.3 Example,2. Manage a group of customers: name, provi

24、nce, city, company name, phone number. 3. Manage all the products information: manufacturers (e.g. Chunlan, Hailer ), types (e.g. motorcycle, air conditioner ), specifications (e.g. MT125, RE1500 ), prices, descriptions. 4. Manage sales order which record each deal has been done. Notes: every sales

25、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 should have its quantity and unit price which will be used to calculate the total prices.,Relational Data Model： Customer (custid, name, prov, city, phone

26、, unit) Product (prodid, factory, type, spec, price, desc) Salesman (empid, idno, name, gender, phone, deptid) Department (deptid, name, headerid) Salesorder (orderno, signdate, empid, custid) Salesitem (orderno, lineno, prodid, singlecost, quantity),1.3.1 Database Design,Chapter 5 through 8 cover database programming. Chapters 5 R