Midterm Review

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