Course Notes - Fall 2024ΒΆ
Contents:
- Introduction to Relational Databases
- Example Database
- Relational Model and Algebra
- Normalization
- Overview
- Why some data models can be bad?
- Normalization Overview
- Functional Dependency
- Keys and functional dependencies
- Functional Dependencies
- Superkey: Unique Attributes
- Keys
- Functional Dependency Inference
- Functional Dependency Inference Rules
- Closure of a set of attributes
- Closure Example
- Keys revisited
- Simplifying the set of functional dependencies
- Closure of a set of functional dependencies (revisited)
- Basis of Functional Dependencies
- Minimal Set of Functional Dependencies
- Finding a minimal basis
- Normalization
- Normal Forms
- Boyce-Codd Normal Form (BCNF)
- BCNF
- Prime attribute
- Third Normal Form (3NF)
- BCNF and 3NF relations
- Decomposition
- Decomposition Properties
- Projecting functional dependencies
- Projecting functional dependencies (Algorithm)
- Dependency Preserving
- Chase test for lossless decomposition
- Chase Algorithm Examples
- Objectives for Decomposition Methods
- Decomposition into BCNF
- Notes about BCNF Decomposition
- 3NF decomposition algorithm
- 3NF Decomposition Examples
- Fourth Normal Form (4NF)
- Multi-valued dependencies
- Inference rules
- Fourth Normal Form
- 4NF decomposition
- Summary
- Entity-Relationship (ER) Models
- Overview
- ER Data Models
- Entity Classes
- Relationships
- Weak Entities
- Subclasses
- Design Rules and Guidelines
- Mapping ER to Relational Model
- Entities
- Weak Entities
- Example
- Converting relationships to ER model
- One-to-many relationships
- One-to-one relationships
- Many-to-many relationships
- Attributes of relationships
- Subclasses
- Option 1: store only unique information in each relation
- Option 2: map each entity to a separate relation
- Option 3: Combine all the information in a single relation
- Example of conversion to relational data model
- SQL - Part 1: Basics
- Overview
- SQL as a database language
- General Comments
- Control Flow
- Main Syntax: Bag Semantics and Duplicate Removal
- SQL - SELECT statement
- SQL - WHERE statement
- FROM Clause
- Example Queries
- Set and Bag Operations
- AGGREGATES
- GROUP BY
- GROUP BY - HAVING
- ORDER BY
- LIMIT
- FULL SQL SYNTAX
- Common Errors When Writing SQL Queries
- SQL - Part 2: Advanced Features
- SQL - Part 3: Data Definition and Manipulation
- Overview
- Transactions
- Transactions - Atomicity
- Insert statement
- Insert results of a query to a table
- Delete statement
- Update statement
- Foreign keys
- Constraint checking
- Other constraints
- Assertions
- Assertion Examples
- Triggers
- Transactions - Isolation
- More on Transactions - Serializability
- Dirty Read
- SQL Levels of isolation
- SQL - Procedural Programming
- SQL - Other Features
- Overview
- Triggers
- Views
- Views (not anonymous)
- Using views in queries
- Why use views?
- Why not use views?
- Indexing
- Access Structure
- Schema
- Search path
- Security
- Role creation and inheritance
- Database Objects
- Privileges
- Grant option
- Grant diagrams
- System Tables
- Case Statements in SELECT
- Group by extended
- Window Functions
- Group by with Filter
- Recursive Queries
- SQL - Embedded SQL Programming
- SQL - Object-Relational Frameworks
- SQL - Object-Relational Extensions
- Secondary Storage and Indexing
- Overview
- Secondary Storage
- Disk Access
- Data Organization
- Reading a disk page
- A high end disk example
- Reading a page
- Reading a page
- Disk scheduling
- Solid State Drives
- Reliability - RAID
- Tuple storage on disk
- Tuple Addresses
- Indices as Secondary Access Methods
- Dense vs. Sparse Indices
- Dense Index Example
- Sparse Index Example
- Multi-level Indices
- Other types of indices
- B-trees
- Searching in B-trees
- B-trees with duplicate values
- Insertion
- Deletion
- B-tree example
- B-trees vs. R-trees
- Bitmaps and Inverted Indices
- Primary vs Secondary Indices
- Hashing
- Extensible hashing
- Linear hashing
- Query Processing
- Overview
- Disk Access Process (Overly Simplified)
- Cost and Resources
- Iterator Interface
- Iterator Example
- Operator classes
- One pass algorithms
- Duplicate removal
- Group by
- Set and bag operators
- External Sorting
- Multi-step external sorting
- Example for external sort
- Multi-step version of Step 2
- Sort based duplicate removal
- Sort based projection
- Hash based projection
- Hash based projection
- Set operations
- Selections
- Access Paths
- Complex Conditions
- Nested loop join
- Block nested loop join
- Index nested loop join
- Sort-merge join
- Hash join
- Summary
- Query Optimization
- Overview
- Query Parsing and Rewriting
- Query Trees
- Query Rewriting Rules (Algebraic Equivalences)
- Cost-based Query Optimization
- Database Statistics
- Cardinality estimation
- Size estimation for equality
- Size estimation for joins
- Size estimation for ranges
- Histograms
- Selectivity of Boolean Conditions
- Cardinality Estimation Summary
- Cardinality Estimation Examples
- Index Selectivity
- Cost-Based Query Optimization
- Join orderings
- Join ordering example
- Transaction Management - Concurrency
- Overview
- Abstracting Transactions
- Serial Schedules
- Schedule Equivalence
- Conflict Ordering
- Serializable schedules
- Algorithm to check serializability
- Example
- Serializable schedules and Concurrency Control
- Locking based concurrency control schemes
- Lock Protocols
- Two phase locking protocol
- Proving 2PL produces serializable schedules
- Deadlocks
- Deadlock prevention
- Deadlock detection
- Lock types
- Two Phase Locking with S/X Locks
- Update locks
- Locking scheduler
- Locking table
- Releasing locks
- Committing Transactions
- Strict Two Phase Locking (Strict 2PL)
- Lock granularity
- Multi-version Concurrency Control
- Summary
- Transaction Management - Durability