1 / 14
Télécharger la présentation

Midterm Review

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. Midterm Review Lecture 14b

  2. 14 Lectures So Far • Introduction • The Relational Model • Disks and Files • Relational Algebra • File Org, Indexes • Relational Calculus • SQL • Sorting & Joining • Optimizing Queries • SQL II • The ER Model • ER -> Relational • Functional Dependencies • Normalization

  3. By Topic • Introduction – what are databases for? (1) • Relational Topics • The Relational Model – basic SQL, Keys, ICs (1) • Relational Algebra (1) • Relational Calculus (1) • SQL (2) • The ER Model (2) • Functional Dependenciesand Normalization (2) • Database Internals • Storing Data: Disks and Files (1) • File Organization and Indexes (1) • External Sorting and Join Algorithms (1) • Query Optimization (1)

  4. Introduction – what are databases for? (1) • Definitions of database, DBMS • Useful properties of database systems • A.C.I.D. • Data Modelling • Querying • Levels of Abstraction • Comparing Database Systems to... • WWW • File Systems • Data Processing Programs

  5. The Relational Model (1) • Tables: Rows and Columns • Basic SQL • Keys • ICs • Referential Integrity

  6. Relational Algebra (1) • Somewhat Procedural Query Language • Basic Ops: • Selection ( s ) Selects a subset of rows from relation (horizontal). • Projection ( p ) Retains only wanted columns from relation (vertical). • Cross-product(  ) Allows us to combine two relations. • Set-difference ( — ) Tuples in r1, but not in r2. • Union(  ) Tuples in r1 and/or in r2. • Other ops: • Intersection (  ) • Join ( ) – merge 2 tables based on common columns • Division ( / ) – used in “for all” queries

  7. Relational Calculus (1) • Formal logic as declarative Query Language • {S | SSailors  S.rating > 7  R(RReserves  R.sid = S.sid  R.bid = 103)} • Concentrated on Tuple Relational Calculus • Also talked about Domain Relational Calculus

  8. SQL (2) • Data Definition Language (DDL) • Data Manipulation Language (DML) • Range variables in Select clause • Expressions in Select, Where clauses • Set operators between queries: • Union, Intersect, Except/Minus • Set operators in nested queries: • In, Exists, Unique, <op> Any, <op> All • Aggregates: Count, Sum, Avg, Min, Max • Group By • Group By/Having • Other Features • Insert • Delete • Update • Null Values – Outer Joins • Views • Order By • Access Control • Integrity Constraints

  9. The ER Model (2) • A Visual Language for Modelling the Real World • Entities, Relationships, Attributes

  10. Functional Dependencies and Normalization (2) • We’ve just talked about this

  11. Storing Data: Disks and Files (1) • Laying out fields in records in pages in files • Buffer Management

  12. File Organization and Indexes (1) • Heap File vs Sorted File vs B-Tree Index vs Hash Index • What are advantages, disadvantages of each?

  13. External Sorting and Join Algorithms (1) • External Sorting • Sort a file of any size using only 3 pages of memory • Understand optimizations with more memory • Join Algorithms • Nested Loops • Indexed Nested Loops • Sort-Merge Join • Hash Join • What are tradeoffs for different algorithms

  14. Query Optimization • Query Plans – trees of operations • How to change plan to find more efficient one

More Related