Lectures
May 2: Lecture 36, NoSQL, Distributed DBs & Cloud-Hosted DBs 
You should have read Bigtable: A Distributed Storage System for Structured Data.
What does it mean to be a "NoSQL" database?
What are some common models and architectures used in NoSQL databases?
What are some popular implementations?
What are some common hosting options for database servers?
Assignment: No homework, yay! (but check out the links in the slides).
Apr 27: Lecture 35, Bigtable
You should have read Designing the Star Schema.
What is Bigtable?
What data model does Bitable employ?
How does Bigtable work?
How is Bigtable different from an RDBMS?
Assignment: Read Bigtable: A Distributed Storage System for Structured Data.
Apr 25: Lecture 34, Data Warehousing & Friends
You should have finished Reading 33.
How is an OLTP database different than an OLAP database?
What is meant by Business Intelligence? Decision Support System? ERP?
What is ETL, really?
What is a Data Warehouse? Where did they come from and why?
What is a data cube?
What is a star schema?
What is a snowflake schema?
Why are data warehouse schemas usually non-normalized?
Assignment: Read Designing the Star Schema.
Apr 22: Lecture 33, Object-Oriented Databases
You should have finished Project 6.2.
How might you implement your own OODB? What problems arise?
How does db4o allow you to store your objects? How does it store them?
What is an OODB? How is it different from an RDBMS?
Assignment: Reading 33.
Apr 20: No Class
You should be working on Project 6.2.
Apr 18: Lecture 32, Analyzing Query Performance
You should have finished Reading 31 and be working on Project 6.2.
What is the relationship between database design and query performance?
How might you find slow queries and analyze them?
How do indexes affect query performance?
How might an "over-normalized" schema affect performance?
What are some simple tools used to analyze queries with MySQL?
Assignment: Reading 32 and finish Project 6.2.
Apr 15: Lecture 31, Basic Security/Exploits, Disaster Recovery, Replication & Advanced Topologies
You should have finished Reading 30 and be working on Project 6.2.
What specific steps should be taken to secure your MySQL installation?
What are the vulnerability points for data while transmitted to/from your db?
What are some methods of backing up MySQL, and DBMSes in general?
What are the benefits and drawbacks of these different methods?
How do you restore databases from backups created from those different methods?
When tables get corrupt or fragmented, what should you do?
What is meant by high-availability?
What are the differences between scaling vertically (up) vs. horizontally (out) ?
What is load-balancing?
What are two main options for scaling database systems?
What is database replication?
How is replication different from redundancy?
What is a common architecture for master-slave replication in a high-availability environment?
What are some other advanced replication topologies?
Assignment: Reading 31 and continue Project 6.2.
Apr 13: Lecture 30, Basic Database Optimization
You should have finished Reading 29 and Project 6.1. Hand in Quiz 29.
How might you decide how you optimize?
Why should you measure performance before and after an attempted optimization?
How might you optimize a MySQL server instance through configuration?
What are some common performance issues at the database level?
Why is memory configuration so important for performance?
Assignment: Reading 30 and Project 6.2.
Apr 11: Lecture 29, MySQL Functions, Administration Basics
You should have finished Reading 28.
What is a MySQL function? How do you create and use one?
What are the different MySQL configuration scopes?
How do you run multiple server instances on one machine?
What are some basic administrative tasks and what commands / programs are at your disposal?
What are the different MySQL log files and what are they for?
Assignment: Reading 29, Quiz 29 and Project 6.1.
Apr 8: Lecture 28, MySQL Storage Engines & the ACID Principle
What does the MySQL Storage Engine architecture mean?
What is a MySQL Storage Engine?
What are some common storage engines and their properties?
How can you implement a fulltext search engine with MySQL?
What is the ACID principle?
How is MySQL ACID-compliant?
Assignment: Reading 28.
Apr 6: Lecture 27, Ruby Crash Course
What does an actual ActiveRecord-extending class look like?
What is Ruby's OO model like? (examples)
What is Ruby's approach to typing? (duck typing)
How do you define a class in Ruby? (def MyClass)
How do you work with the Strings, Array and ActiveRecord APIs?
No homework, yay!
Apr 4: Lecture 26, Database Programming w/ ORMs
You should have finished Reading 25 and Project 6.0. Hand in Quiz 25.
What is the so-called Object-Relational Impedance Mismatch?
What are the benefits and drawbacks of ORM tools?
Project database design review.
No homework, yay!
Mar 28: Lecture 25, Database Programming w/ Higher-Level Languages
You should have finished Reading 24.
How do you program w/ relational databases and higher-level languages?
Examples: Java, Perl, Python, PHP, Ruby
What common pattern do we see regardless of language?
How might you begin to encapsulate your database code?
When is the difference between 'low-level' programming vs. programming w/ higher-level concepts?
How might you persist objects to a relational database?
Assignment: Reading 25, Quiz 25 and Project 6.0.
Mar 25: Lecture 24, MySQL Users & Permissions, Stored Procedures
You should have finished Reading 23. Hand in Quiz 23.
What is the difference between a database user and an application user?
Should typical applications modify MySQL users and permissions? (no!)
How does the MySQL permissions system work? Where is the data stored?
What do GRANT and REVOKE do?
What is a stored procedure?
How are stored procedure declarations different from traditional function headers?
What are IN parameters and OUT parameters?
How might you use stored procedures to abstract SQL out of your application code?
What are the benefits and drawbacks of stored procedures?
Assignment: Reading 24 (no quiz).
Mar 23: No Class 
You should have access to an instance of MySQL.
Assignment: Reading 23 and Quiz 23.
Mar 21: Lecture 23, Intro to Client-Server DBMSes (MySQL)
You should have had a nice spring break.
What is MySQL?
What does it mean to be a client-server RDBMS?
What executable programs are part of the MySQL installation?
How is MySQL different from SQLite?
How does MySQL manage database credentials (users, passwords, etc)?
What does mysql do? How is it different from mysqld?
How do you create a database in MySQL?
Assignment: Install MySQL 5.x (community ed.) on your own machine, or request a remote development environment.
Mar 11: Midterms Returned, Reviewed
You should be prepared to ace the midterm.
Assignment: No homework, yay!
Mar 9: Midterm Exam
You should be prepared to ace the midterm.
Assignment: No homework, yay!
Mar 7: Lecture 22, Midterm Review
You should have reviewed the questions listed on this site.
What questions do you have about the midterm?
Which questions listed on this site are giving you a problem?
How might you ace the midterm?
Assignment: Study for the midterm.
Mar 4: Lecture 21, Correlated Subqueries, Database Programming
You should have finished Reading 20.
What is a correlated subquery?
Why might a correlated subquery take a long time to execute?
What is the typical (painful) pattern when managing SQL queries programmatically?
How might you ask a user for input and use it in a query?
What happens with your SQL query strings as an application's features become non-trivial? Why is this a problem?
Assignment: Start reviewing for the midterm.
Mar 2: Lecture 20, Contraints, Indexes & Triggers, Views, Subqueries
You should have finished Project 5.1.
What is an index?
How do you declare an index with SQL?
What is a constraint?
What are primary key, not null, default, unique, and check constraints and how do you declare them?
What is a foreign key constraint?
What is the difference between a foreign key and a foreign key constraint?
How do you declare a foreign key constraint and how can you determine the constraint's behavior?
What is a trigger? What are some ways to declare one?
What is a view? Why might you create one?
What is a subquery?
What are the two contexts in which subqueries are typically used?
How can you use IN / NOT IN, and ANY / ALL in conjunction with subqueries?
Assignment: Reading 20.
Feb 28: Lecture 19, JOINs in Relational Algebra & SQL, Relational Calculus
You should be finishing Project 5.1.
What is a JOIN?
What are some types of JOINs?
What are JOINs used for?
How do you write a typical inner join?
How do you write a left/right outer join?
What is relational calculus? Why do we care?
Assignment: Finish Project 5.1.
Feb 25: Lecture 18, Relational Algebra, Relational Calculus
You should have finished Project 5.0.
What is relational algebra, and why do we care?
What are the five primary relational operators?
Assignment: Reading 18, Quiz 18 and Project 5.1.
Feb 23: Lecture 17, Aggregate Functions, Constraints and Indexes
You should be finishing Project 4.2.
What are ORDER BY, DISTINCT and LIKE used for?
What are some ways to search for strings?
What is the \ character used for in a string?
What is an aggregate function and what are some common ones?
How does a GROUP BY clause affect the result of an aggregate function?
What is the point of using HAVING with GROUP BY?
What are some examples of RDBMS constraints?
What is an index? How can an index improve the performance of SELECT?
Assignment: Project 5.0.
Feb 21: No Class, Yay!
You should be finishing Project 4.2, Reading 15 and Quiz 15.
Feb 18: Lecture 16, Intro to Programming w/ Databases
You should have finished Project 4.1.
Why should I be proficient with both a tool and the command line?
What is the most common architecture for database-driven applications?
What does the SQLite C API model look look like?
What are connections and statements?
How are queries executed?
What kinds of statements does the C API support?
How do you handle errors raised by the C API?
How can you extend SQL to include your own custom functions?
How do API functions trigger different lock states?
How do you handle a SQLITE_BUSY error code?
Assignment: Project 4.2 and finish Reading 15 and Quiz 15.
Feb 16: Lecture 15, Review, Transactions & Locking
You should have finished Reading 14 and Project 4.0. Hand in Quiz 14.
What have we done? What have we learned? What's next?
What is a transaction?
What conflict-resolution features does SQLite support?
What are database locks?
What transaction types does SQLite support?
What do transactions have to do with locks?
Assignment: Reading 15, Quiz 15 and Project 4.1.
Feb 14: Lecture 14, Normalization & Practical Database Design
You should have had a blissful weekend not thinking about databases.
What is normalization?
What is 1NF, and how do you get a schema into 1NF?
What is a functional dependency?
What is 2NF, and how do you get a schema into 2NF?
What is 3NF, and how do you get a schema into 3NF?
What is denormalization? What's the point?
Assignment: Reading 14, Quiz 14 and Project 4.0.
Feb 11: Lecture 13, Database Design & SQL
You should have finished Reading 12 and be finishing Project 3. Hand in Quiz 12.
How would you design the database for a multi-user 'task list' application?
What SQL queries would you create when using such a database?
Assignment: No homework, yay!
Feb 9: Lecture 12, Database Design & SQL
You should have finished Reading 11 and be working on Project 3. Hand in Quiz 11.
What are some 'rules' when designing a database schema from an ER diagram?
What are some postmodern best practices when designing a database schema?
How would you design the database for a simple 'task list' application?
What SQL queries would you create when using such a database?
Assignment: Reading 12, Quiz 12.
Feb 7: Lecture 11, More SQL and SQLite
You should have finished Reading 10. Hand in Quiz 10.
How do you select data from multiple tables?
What is a primary key?
What is a foreign key?
Project 2.2, 2.3 review.
Assignment: Reading 11, Quiz 11 and Project 3.
Feb 4: Lecture 10, SQL and SQLite
You should have finished Reading 9 and Project 2.3. Hand in Quiz 9.
What is SQL? What are DDL, DML, DCL and TCL?
What is really happening when you run SQLite?
How do you create a database with traditional SQL? How do you do it with SQLite?
How do you create a table in SQL?
How do you declare fields and datatypes in SQL?
What datatypes does SQLite support?
How do you select records contained in a database?
How do you insert data into a database? Update? Delete?
What is CRUD?
Assignment: Reading 10, Quiz 10.
Feb 2: Lecture 9, The Relational Model
You should have finished Project 2.2.
Why is a relational database called a relational database?
What is a relation?
What is a relvar? What is a tuple?
What does NULL mean?
What are some common constraints?
What are the four primary operations on a relation?
Assignment: Reading 9, Quiz 9, Project 2.3.
Jan 31: Lecture 8, EER Model, UML
You should have finished Project 2.1. Hand in Quiz 7.
What is crows-foot notation and what is it used for?
What is the EER Model and what does it provide?
How do you represent inheritance in an ER diagram?
In what way are relationships inherited in the EER Model?
What is UML?
Project review, ERD exercises.
Assignment: Project 2.2.
Jan 28: Lecture 7, ER Model, Relationships, Constraints
You should have finished Reading 6 and Project 2.0. Hand in Quiz 5 and Quiz 6.
What is a relationship? A binary relationship? Ternary? Recursive?
What is a weak entity?
Can relationships have attributes?
How might you refactor a ternary relationship into multiple binary relationships?
What is a constraint?
What are the two primary constraint types?
What are the three primary cardinality ratios and what do they mean?
Where does the ER Model fit in the data abstraction stack?
Assignment: Quiz 7 and Project 2.1.
Jan 26: Lecture 6, Database Design Process, ER Model Intro
What steps are involved in designing a DB?
What is the ER Model?
What is an entity? What is an attribute?
What are the main types of attributes?
What is an entity type? What is an entity set?
What is a key?
Assignment: Reading 6, Quiz 6, Project 2.0.
Jan 24: Lecture 5, Data Storage & I/O, SQLite
You should have finished Reading 4. Hand in Quiz 4.
How does hard disk data storage work?
What is SQLite's architecture?
What are SQLite storage classes?
How does SQLite implement manifest typing, and what does that mean?
What is SQLite's type affinity feature?
What is SQLite's file format like?
Jan 21: No Lecture
You should be working on Reading 4 and finished Project 1.1.
Jan 19: Lecture 4, Data Models, Schemas & DBMS Components
You should have finished Reading 3 and be working on Project 1.1. Hand in Quiz 3.
What is a data model?
What is a schema?
What are the two main abstraction contexts for data models and schemas?
Is SQL declarative or procedural?
What are some of the common internal components of all DBMSes?
What are the different types of application architectures that involve DBMSes?
What criteria might you use to categorize or distinguish certain DBMSes from others?
Jan 17: Lecture 3, History & Purpose, Databases from 5280 feet
Why should one use a DBMS? Why not?
So... how does it all work? (high-level)
Assignment: Reading 3, Quiz 3, Project 1.1.
Jan 14: Lecture 2, History & Purpose
You should have finished Reading 1 and Project 1.0. Hand in Quiz 1.
Project 1 review.
What are some important characteristics of SQLite?
How is SQLite different from other DBMSes?
How the heck did people come up with this 'DBMS' idea anyway?
What are some of the pains encountered when managing data?
No homework, yay!
Jan 12: Lecture 1, Introduction
You should have purchased the required texts and be prepared for class.
What is data? What is a database?
What is a DBMS?
Projects introduction.
Assignment: Reading 1, Quiz 1, Project 1.0.
