Course Database Management Systems - Chapter 0: Overview of a DBMS - Nguyen Thanh Tung

Nội dung text: Course Database Management Systems - Chapter 0: Overview of a DBMS - Nguyen Thanh Tung

1. References [1] R. Elmasri, S. R. Navathe, Fundamentals of Database Systems- 4th Edition, Pearson- Addison Wesley, 2003. [2] H. G. Molina, J. D. Ullman, J. Widom, Database System Implementation, Prentice-Hall, 2000. [3] H. G. Molina, J. D. Ullman, J. Widom, Database Systems: The Complete Book, Prentice-Hall, 2002 [4] A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts –3rd Edition, McGraw-Hill, 1999. 2
2. Course outline  C0. Overview of a DBMS  C1. Disk Storage, Basic File Structures, and Hashing  C2. Indexing Structures for Files  C3. Algorithms for Query Processing and Optimization  C4. Introduction to Transaction Processing Concepts and Theory  C5. Concurrency Control Techniques  C6. Database Recovery Techniques 4
3. Three parts  Storage management: how secondary storage is used effectively to hold data and allow it to be accessed quickly  Query processing: how queries expressed in a very high-level language such as SQL can be executed efficiently  Transaction management: how to support transactions. 6
4. What is a DBMS?  The power of database comes from a body of knowledge and technology that has developed over several decades and is embodied in a specialized software called a database management system, or DBMS.  A DBMS is a powerful tool for creating and managing large amount of data efficiently and allowing it to persist over long periods of time safely. 8
5. Overview of a Database Management System  In Fig. 1.1. we can see an outline of a complete DBMS.  Single boxes represent system components  Double boxes represent in-memory data structures.  Solid lines indicate control and data flows  Dashed lines indicate data flow only.  At the top level, we suggest that there are two distinct sources of commands to the DBMS:  Conventional users and application programs that ask for data or modify data.  A DBA (database administrator): a person or persons responsible for the structure (schema) of the database. 10
6. Data-Definition Language Commands  The second kind of commands is the simpler to process and we can see its trail beginning at the upper right side of Fig.1.1.  E.g., DBA for a university registrar’s database might decide that there should be a relation with columns for a student, a course that student has taken and a grade for that student in that course. This structure and constraint information is all part of the schema of the database. It is entered by the DBA, who needs special authority to execute schema-altering commands.  These schema-altering DDL commands are parsed by a DDL processor and passed to the execution engine, which then goes through the index/file/record manager to alter the metadata, that is, the schema information for the database. 12
7. Answering the query  The query is parsed and optimized by a query compiler. The resulting query plan is passed to the execution engine.  The execution engine issues a sequence of requests for small pieces of data, typically tuples of a relation, to a resource manager that knows about data files, the format and size of records in those files and index files.  The requests for data are translated into pages and these requests are passed to buffer manager. Buffer manager’s task is to bring appropriate portions of the data from secondary storage to main-memory buffers.  Normally, the page or “disk blocks” is the unit of transfer between buffers and disk. The buffer manager communicates with a storage manager to get data from disk.  The storage manager might involve operating-system command, but more typically, DBMS issues commands directly to the disk controller. 14
8. Main-memory buffers and Buffer Manager  The data of a database normally resides in secondary storage (magnetic disk). However, to perform any operation on data, that data must be in main memory.  Buffer manager is responsible for partitioning the available main memory into buffers, which are page-sized regions into which disk blocks can be transferred.  All DBMS components that need information from the disk will interact with the buffers and the buffer manager, either directly or through the execution engine. 16
9. Transaction Processing  It’s normal to group one or more database operations into a transaction, which is a unit of work that must be executed atomically and in apparent isolation from other transactions.  Besides, a DBMS offers the guarantee of durability: that the work of a completed transaction will never be lost.  The transaction manager accepts transaction commands from an application, which tell the transaction manager:  when transactions begin and end  information about the expectations of the application. 18
10. Transaction processor’s tasks (cont.)  Concurrent control: Transactions must appear to execute in isolation. But in most systems, there will be many transactions executing at once. Thus, the scheduler (concurrency-control manager) must assure that the individual actions of multiple transactions are executed in such an order that the net effect is the same as if the transactions had been executed in their entirety, one-at-a-time.  A typical scheduler does its work by maintaining locks on certain pieces of the database. These locks prevent two transactions from accessing the same piece of data in ways that interact badly.  Locks are stored in a main-memory lock table. The scheduler affects the execution of queries and other database operations by forbidding the execution engine from accessing locked parts of the database. 20
11. The Query Processor  The part of DBMS that most affects the performance that the user sees is the query processor.  The query processor consists of two components: query compiler and execution engine.  The query compiler, translates the query into an internal form called a query plan. A query plan is a sequence of operations to be performed on the data. Often the operations in a query plan are “relational algebra” operations.  The query compiler consists of 3 major units: a query parser, a query preprocessor, and a query optimizer. 22
12. Execution Engine  The execution engine has the responsibility for executing each of the steps in the chosen query plan.  The execution engine interacts with most of the other components of the DBMS, either directly or through the buffers.  It must get 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 sure that all the database changes are properly logged. 24
13. Assignments  File structure  Query Processing  Oracle  In Oracle  Index  In SQL Server  Index in Oracle  Transaction  R-Tree/ Hilbert R-Tree  In Oracle  Bitmap index  In SQL Server  How to implement in Oracle  Recovery  Cache  In Oracle  In Oracle  In SQL Server  In MySQL  Distributed DBMS  Oracle 26
14. Assessment  Midterm: 50%  Preliminary tests in class: 10%  Test 1 (c. 1 + 2; w. 5): 5%  Test 2 (c. 3 p.2 + 4 + 5; w. 13): 5%  Midterm exam: 25%  20 questions in 60 mins: 0.5 point/question  Reviews: c.0-3  Individual assignment: 15%  Deadline for assignment submission: w. 14  Oral presentation for an added bonus (max 10%): w. 14  Final exam: 50%  45 questions in 120 mins: 0.25 point/question  Reviews: c. 0-6 Success in the course = { Midterm * 50% + Final exam * 50% 5.0 28}