Introduction to Database Systems - CSE 414

terms and themes

• SQL
  • SQL joins
  • SQL aggregate functions
  • SQL grouping
  • SQL logic
  • SQL subqueries
  • SQL execution order - FWGHOS
• Syntax and semantics
• Data models
  • Relational data model
• Monotonicity
• Relational algebra
• Database design
  • Entity relationship diagrams - ER diagrams
  • Functional dependencies
  • SQL constraints
  • Closures
  • Boyce-Codd Normal Form - BCNF
  • Database keys
• Database concurrency control
  • Databases transactions
    • Transaction isolation levels
  • ACID properties
  • Transaction schedules
    • Locking scheduler
• Database cost estimation
  • Cardinality estimation
• Database indexes
• Parallel DBMS scaling - OLTP, OLAP
• Parallel DBMS operations
• Parallel DBMS storage and partitioning - unpartitioned, block partitioning, range partitioning, hash partitioning
• Parallel DBMS architectures - shared-memory, shared-disk, shared-nothing
• Distributed file system (DFS)
• MapReduce
• Apache Spark
• Semi-structured data models

lectures

• 240925 CSE 414 lecture 1 - DBS, data models, SQL
• 240927 CSE 414 lecture 2 - SQL, relational algebra (RA), creating tables
• 240930 CSE 414 lecture 3 - foreign keys, joins
• 241002 CSE 414 lecture 4 - joins, aggregates
• 241004 CSE 414 lecture 5 - grouping
• 241007 CSE 414 lecture 6 - aggregates, joins, witnessing problem, subqueries
• 241009 CSE 414 lecture 7 - subqueries
• 241011 CSE 414 lecture 8 - subqueries, monotonicity
• 241014 CSE 414 lecture 9 - relational algebra
• 241016 CSE 414 lecture 10 - subquery RA, database design, ER diagrams
• 241018 CSE 414 lecture 11 - ER diagrams, multiplicity, constraints
• 241021 CSE 414 lecture 12 - functional dependencies
• 241023 CSE 414 lecture 13 - closures, superkeys, BCNF
• 241026 CSE 414 lecture 14 - BCNF, decomposition, transactions
• 241029 CSE 414 lecture 15 - transaction modeling, schedules, serializability
• 241030 CSE 414 lecture 16 - precedence graphs, scheduling, locks
• 241104 CSE 414 lecture 17 - strict 2PL, growing and shrinking phases
• 241105 CSE 414 lecture 18 - isolation levels
• 241107 CSE 414 lecture 19 - cost estimation
• 241108 CSE 414 lecture 20 - cardinality estimation
• 241113 CSE 414 lecture 21 - hash joins, tuning, indexes
• 241115 CSE 414 lecture 22 - storage, indexes
• 241118 CSE 414 lecture 23 - parallel DBMSs, scaling, partitioning
• 241120 CSE 414 lecture 24 - partitioning
• 241122 CSE 414 lecture 25 - broadcast joins, skew handling, MapReduce, Spark
• 241125 CSE 414 lecture 26 - MapReduce, Spark, semi-structured data

sections

• 240926 CSE 414 section 1 - SQLite
• 241003 CSE 414 section 2 - logic, joins types, group by, having, SQL execution order
• 241010 CSE 414 section 3 - subqueries, monotonicity

information

• 6 late days (24 hrs), 2 max per assignment

related

list from [[]] and !outgoing([[]])