CS121 Fall 2018 - Lecture Slides and Videos
Slides are made available as PDF files. All lectures are recorded in
.mp4 format. Each recording is around 25MB in size.
The previous year's lecture slides and recordings are available here.
- Lecture 1: 2018-10-05
[slides]
[recording]
-
Databases and DBMSes. Data models. Relational Model. Relations, relation schemas, tuples. Superkeys, candidate keys, primary keys, foreign keys.
- Lecture 2: 2018-10-10
[slides]
[recording]
-
Query languages, Data Manipulation Languages (DML). Relational algebra. Fundamental operators, additional operators.
- Lecture 3: 2018-10-12
[slides]
[recording]
-
Extended relational algebra operators: generalized projection, grouping and aggregation, outer joins. Null values. Handling null values in relational algebra. Database modification. Inserting, updating, deleting records.
- Lecture 4: 2018-10-15
[slides]
[recording]
-
Basic SQL DDL: tables, basic column types, primary keys. Basic SQL statements: insert, select, delete. Select statements: simple expressions in SELECT, FROM, WHERE.
- Lecture 5: 2018-10-17
[slides]
[recording]
-
SELECT: ordered results, aggregation, grouping. Subqueries in WHERE clause. Set operations. Subqueries in FROM clause. Derived relations. INSERT...SELECT. DELETE and WHERE clauses. Simple UPDATE statements.
- Lecture 6: 2018-10-19
[slides]
[recording]
-
Subqueries in FROM clause. Derived relations. INSERT...SELECT. DELETE and WHERE clauses. Simple UPDATE statements. NULL values. Predicates involving NULL and UNKNOWN. Alternate join syntax. Inner/cross/outer join. Theta join. Natural join. Outer joins and aggregates. Creating/using views. Updatable views and WITH CHECK OPTION.
- Lecture 7: 2018-10-22
[slides]
[recording]
-
Catalogs, schemas, tables. DB connection state. Primary key constraints. Unique constraints. Not-null constraints. CHECK constraints. Foreign key constraints and cascade operations.
- Lecture 8: 2018-10-24
[slides]
[recording]
-
Deferred constraint enforcement. Date and time SQL types: DATE, TIME, DATETIME, TIMESTAMP, INTERVAL. Large object "LOB" types: BLOB, CLOB, TEXT. Default values for columns. Schema alteration with ALTER TABLE. Global and local temporary tables.
- Lecture 9: 2018-10-26
[slides]
[recording]
-
Procedural SQL. User-defined functions. Stored procedures. Cursors. Conditions and condition handlers.
- Lecture 10: 2018-10-29
[slides]
[recording]
-
Triggers, materialized views, INSERT variants, SQL security.
- Lecture 11: 2018-10-31
[slides]
[recording]
-
Indexes, MySQL EXPLAIN command.
- Lecture 12: 2018-11-02
[slides]
[recording]
-
SQL query evaluation and plan optimization. Plan node implemenations: file scan, index scan, external-memory sorting, nested loop join, sort-merge join, hash join. Table statistics and database ANALYZE commands.
- Lecture 13: 2018-11-05
[slides]
[recording]
-
Midterm review.
- Lecture 14: 2018-11-07
[slides]
[recording]
-
Entity-relationship model.
- Lecture 15: 2018-11-09
[slides]
[recording]
-
Entity-relationship model II.
- Lecture 16: 2018-11-12
[slides]
[recording]
-
Ternary relationships and mapping cardinalities. Mapping E-R models to the relational model.
- Lecture 17: 2018-11-14
[slides]
[recording]
-
Generalization/specialization in the E-R model. Alternate schema mappings.
- Lecture 18: 2018-11-16
[slides]
[recording]
-
Normal forms. First Normal Form (1NF). Functional dependencies. Trivial functional dependencies. Closure of a set of functional dependencies. Boyce-Codd Normal Form (BCNF).
- Lecture 19: 2018-11-19
[slides]
[recording]
-
Functional Dependency Theory I
- Lecture 20: 2018-11-21
[slides]
[recording]
-
Functional and Multivalued Dependency Theory.
- Lecture 22: 2018-11-26
[slides]
[recording]
-
Alternate diagramming methods. Data warehouses.
- Lecture 23: 2018-11-28
[slides]
-
Data Warehousing II.
- Lecture 24: 2018-11-30
[slides]
-
Passwords. Trees and hierarchies.
- Lecture 27: 2018-12-07
[slides]
[recording]
-
Final exam review.