Class Notes

Links & learning objectives

Lecture 1 

You will gain a clear idea of  the course, its objective, grading policies, and staff. Also, you will gain a historical view of data management systems over time, focusing on relational technology.

Lecture 2 

You will gain insight into key data management technologies - query processing, indexing, query optimization, transactions and why they are important. 

This lecture provides an overview of the components involved in a database system and only serves as a way to provide a glance at the various concepts. The lecture does not correspond to a single chapter in the textbook - you can use the slides linked above as a reference to review what the lecture covered.

We will go into further details of some of these concepts in the process of this course and at the end of the course, these will be the topics we hope you will achieve mastery at.

Links & learning objectives

Lectures 3 & 4 

Over 2 lectures, you will learn how to model data at the conceptual level, and what are good designs. You will gain full knowledge of the entity-relational model.

ER Concepts Template 

Links & learning objectives

Lecture 5 

Oct 8 Recording

Basics of relational model, how data is stored in relations, key constraints, referential integrity constraints, updates in presence of constraints, ER Diagrams to relational representation

Lecture 6

Preview of creating tables with constraints in SQL, indexes, functional dependencies, Boyce-Codd Normal Form and redundancies. You will gain insight into when relational design is good and when it is not. You will have a clear understanding of what it means for relations to store data redundantly and why that is not good, and how we can normalize the relations to remove redundancy.

Discussion Practice Question with Solution

Links & learning objectives

Lecture 7

Introduction, relational algebra operators (select, project, rename, set operators, joins), RelaX

Relax

Sample Relax Data

Links & learning objectives

Lecture 8

This lecture introduces SQL Basics.

(Select, From, Where, Ordering, Union, Aggregations, Group By, Null in SQL, Subqueries, Different kinds of Join, Insert, Delete, Update etc)

Lecture 9

This lecture introduces some advanced SQL topics including views, constraints and triggers.

Lecture 10
SQL extensions - functions, procedures, language constructs, recursive queries.

Recording of lecture on 10.31

Recording of lecture on 11.5

Links & learning objectives

Lecture 14-15

Accessing SQL From a Programming Language, Spatial Data, Semi Structured Data and Object Orientation 

Links & learning objectives

Lecture 16

Data Analytics, 11

Links & learning objectives

Lecture Indexing

Indexing

Recording of lecture on Nov_26_Part1

Recording of lecture on Nov_26_Part2

Links & learning objectives

Lecture Transaction: 

Transaction

Links & learning objectives

Lecture Optimization

Storage, Cost, Query Optimizations

Links & learning objectives

slides