1 / 6

What Should a DBMS Do?

Discover the core functionalities of a DBMS including data storage, query processing, concurrency control, and transaction durability. Learn about optimizing SQL queries and transaction processing in a practical database architecture.

koren
Télécharger la présentation

What Should a DBMS Do?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What Should a DBMS Do? • Store large amounts of data • Process queries efficiently • Allow multiple users to access the database concurrently and safely. • Provide durability of the data. • How will we do all this??

  2. Generic Architecture Query update User/ Application Query compiler Query execution plan Execution engine Record, index requests Index/record mgr. Page commands Buffer manager Read/write pages Storage manager storage

  3. Query Optimization Goal: Declarative SQL query Imperative query execution plan: buyer  City=‘seattle’ phone>’5430000’ SELECT S.sname FROM Purchase P, Person Q WHERE P.buyer=Q.name AND Q.city=‘seattle’ AND Q.phone > ‘5430000’ (Simple Nested Loops) Buyer=name Person Purchase Plan:Tree of R.A. ops, with choice of alg for each op. Ideally: Want to find best plan. Practically: Avoid worst plans!

  4. Alternate Plans Find names of people who bought telephony products buyer buyer   Category=“telephony” Category=“telephony” (hash join) (hash join) prod=pname Buyer=name (hash join) (hash join) Product Person Buyer=name prod=pname Person Purchase Product Purchase But what if we’re only looking for Bob’s purchases?

  5. ACID Properties Atomicity:all actions of a transaction happen, or none happen. Consistency: if a transaction is consistent, and the database starts from a consistent state, then it will end in a consistent state. Isolation: the execution of one transaction is isolated from other transactions. Durability: if a transaction commits, its effects persist in the database.

  6. Problems with Transaction Processing Airline reservation system: Step 1: check if a seat is empty. Step 2: reserve the seat. Bad scenario: (but very common) Customer 1 - finds a seat empty Customer 2 - finds the same seat empty Customer 1 - reserves the seat. Customer 2 - reserves the seat. Customer 1 will not be happy; spends night in airport hotel.

More Related