| DATE: | OUTLINES: | SLIDES: | DEMOS: | NOTES: | DESCRIPTION: | |
| 0.x | 0.x | Database Systems (Overview) | ||||
| 1.x | 1.x | Relational Algebra: Relations, Attributes, Tuples Relational Algebra: Superkeys, Primary Keys, Foreign Keys |
||||
| 1.x | 1.x | Relational Algebra: Selections, Projections, Renames,
Assignments Relational Algebra: Unions, Intersections, Set Differences |
||||
| 1.x | 1.x | Relational Algebra: Cartesian Products, Set
Quotients Relational Algebra: Inner-Joins, Outer-Joins |
||||
| 1.x | 1.x | Relational Algebra: Insertions, Deletions, Updates | ||||
| 2.x | 2.x | Basic SQL: Table Creation (CREATE) Basic SQL: Attribute Data Types, NULL values Basic SQL: Primary Keys (PRIMARY KEY) Basic SQL: Foreign Keys (FOREIGN KEY) |
||||
| 2.x | 2.x | Basic SQL: Tuple Insertion (INSERT INTO) Basic SQL: Tuple Deletion (DELETE FROM) Basic SQL: Attribute Changes (ALTER TABLE) Basic SQL: Table Deletion (DROP TABLE) |
||||
| 2.x | 2.x | Basic SQL: Single-Relation Queries (SELECT FROM WHERE
ORDER BY) Basic SQL: Many-Relation Queries (SELECT FROM WHERE ORDER BY) Basic SQL: Pattern Matching in Queries ( LIKE, %, _ ) |
||||
| 2.x | 2.x | Basic SQL: Relation Upkeep (INSERT, DELETE, UPDATE) | ||||
| 3.x | 3.x | Intermediate SQL: Aggregate Functions (COUNT, AVG,
SUM, MIN, MAX) Intermediate SQL: Grouping (GROUP BY, HAVING) Intermediate SQL: String Handling (||, +, UPPER, LOWER, TRIM) |
||||
| 3.x | 3.x | Intermediate SQL: Set-Combined Queries (UNION, UNION
ALL) Intermediate SQL: Set-Combined Queries (INTERSECT, EXCEPT) Intermediate SQL: Nested WHERE Subqueries |
||||
| 3.x | 3.x | Intermediate SQL: Nested FROM Subqueries Intermediate SQL: Nested SELECT Subqueries Intermediate SQL: Nested WITH Subqueries |
||||
| 3.x | 3.x | Intermediate SQL: Cross-Joins (CROSS JOIN) Intermediate SQL: Inner-Joins (INNER JOIN) Intermediate SQL: Outer-Joins ([LEFT/RIGHT/FULL] OUTER JOIN) Intermediate SQL: Self-Joins |
||||
| 3.x | 3.x | Intermediate SQL: Views (CREATE VIEW, DROP VIEW) Intermediate SQL: Constraints (NOT NULL, UNIQUE) |
||||
| 4.x | 4.x | ER Modeling: Introduction ER Modeling: Strong Entity Sets, Attributes, Keys, Relationships |
||||
| 4.x | 4.x | ER Modeling: One-to-One, One-to-Many, Many-to-Many
Relationships ER Modeling: Weak Entity Sets |
||||
| 4.x | 4.x | ER Modeling: Ternary Relationships ER Modeling: Quaternary Relationships |
||||
| 4.x | 4.x | ER Modeling: Fan Traps ER Modeling: Chasm Traps |
||||
| 4.x | 4.x | ER Modeling: 1ER --> 2ER Mappings ER Modeling: 2ER --> Table Mappings |
||||
| 5.x | 5.x | Relational Design Theory: Relations, Attributes,
Tuples (revisited) Relational Design Theory: Superkeys, Candidate Keys, Primary Keys |
||||
| 5.x | 5.x | Relational Design Theory: Functional Dependencies
(FD's), FD Diagrams Relational Design Theory: Armstrong's Axioms, Armstrong's Implications |
||||
| 5.x | 5.x | Relational Design Theory: Trivial FD's, Simple
FD's Relational Design Theory: Redundant FD's |
||||
5.x |
5.x |
Relational Design Theory: Closures of Attribute
Sets & FD Sets Relational Design Theory: Minimal Covers of FD Sets |
||||
| 5.x | 5.x | Relational Design Theory: Extraneous Attributes Relational Design Theory: Ideal Covers of FD Sets |
||||
| 6.x | 6.x | Normalization Theory: Atomic Tuple Values Normalization Theory: 1st Normal Form (1NF) |
||||
| 6.x | 6.x | Normalization Theory: Partial FD's Normalization Theory: 2nd Normal Form (2NF) |
||||
| 6.x | 6.x | Normalization Theory: Transitive FD's Normalization Theory: 3rd Normal Form (3NF) |
||||
| 6.x | 6.x | Normalization Theory: Non-trivial Superkey FD's Normalization Theory: Boyce-Codd Normal Form (BCNF) |
||||
| 6.x | 6.x | Normalization Theory: Lossless Joins, FD
Preservation Normalization Theory: Achieving 3NF, Lossless Joins & FD Preservation |
||||
| 7.x | 7.x | Advanced SQL: Ranking Fcn's (RANK, DENSE_RANK,
ROW_NUMBER) Advanced SQL: Ranking Fcn's (CUME_DIST, PERCENT_RANK, TOP, LIMIT) |
||||
| 7.x | 7.x | Advanced SQL: Window Fcn's (LEAD, LAG, FIRST_VALUE,
LAST_VALUE) Advanced SQL: Window Fcn's (MIN, MAX, COUNT, SUM, AVG) |
||||
| 7.x | 7.x | Advanced SQL: Named Windows (WINDOW) Advanced SQL: Conditional Expr's (CASE, WHEN, ELSE, COALESCE, NULLIF) Advanced SQL: Efficient Grouping (GROUPING SETS, ROLLUP, CUBE) |
||||
| 7.x | 7.x | Advanced SQL: Acyclic Recursive Queries (WITH RECURSIVE / CONNECT BY) | ||||
| 8.x | 8.x | System-Lvl SQL: Transactions (COMMIT, ROLLBACK) System-Lvl SQL: Cursors (DECLARE CURSOR, OPEN, FETCH, CLOSE) |
||||
| 8.x | 8.x | System-Lvl SQL: Stored Procedures (CREATE
PROCEDURE) System-Lvl SQL: Triggers (CREATE TRIGGER) |
||||
| 8.x | 8.x | System-Lvl SQL: Materialized Views (CREATE
MATERIALIZED VIEW) System-Lvl SQL: Indexes (CREATE INDEX, DROP INDEX) |
||||
| 9.x | 9.x | Data Warehousing | ||||
Best viewed at 1280x720 (720p) screen resolution or higher.