Lectures
Apr 11: Lecture 34, Availability, Replication & Advanced Topologies
You should have finished Reading 20 and be working on Project 6.2.
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 21 and continue Project 6.2.
Apr 9: Lecture 33, Database Performance Tips
You should have finished Project 6.1.
How does schema design affect database performance?
How does schema design affect query construction?
How does query design affect performance?
How do indexes affect query performance?
How might an "over-normalized" schema affect performance?
What are some rules of thumb when analyzing schemas and queries for performance issues?
Assignment: Reading 20 and Project 6.2.
Apr 6: Lecture 32, ORMs and the Object-Relational Impedance Mismatch
You should have finished Reading 19 and be working on Project 6.1 Hand in Quiz 19.
What problems does Neward assert in his rather infamous essay?
What is the schema ownership problem?
What is the dual-schema problem?
What choices do software engineers have when faced with such ORM "problems"?
Is the object-relational mismatch really a big deal in 2012?
Assignment: Finish Project 6.1.
Apr 4: Lecture 31, Database Programming w/ ORMs
You should have finished Reading 18. Hand in Quiz 18.
What is wrong with embedding lots of SQL in an application?
What are some alternatives to using SQL strings?
What is an ORM?
How might you implement your own ORM?
What is the so-called Object-Relational Impedance Mismatch?
What are the benefits and drawbacks of ORM tools?
Project 6.1 overview.
Assignment: Reading 19, Quiz 19 and Project 6.1.
Apr 2: No Class (Canceled)
Mar 30: No Class (E-Days)
Mar 28: Lecture 30, Fundamentals of Database Administration
You should have finished Reading 17.
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 18 and Quiz 18.
Mar 26: Lecture 29, MySQL Storage Engines, Overview of Performance
You should have finished Reading 16 and be finishing Project 6.0.
Project 6 schema tips.
What is a MySQL storage engine?
What are some common storage engines and their qualities?
What does the MySQL Storage Engine architecture mean?
What is a MySQL Storage Engine?
What are some common storage engines and their properties?
What are three primary influences of database performance?
Assignment: Reading 17.
Mar 23: Lecture 28, Stored Procedures
You should have finished Reading 15 and be working on Project 6.0. Hand in Quiz 15.
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 16.
Mar 21: Lecture 27, MySQL Users & Permissions, Stored Procedures
You should have installed an instance of MySQL on your own machine, or requested access to a hosted MySQL instance.
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 15, Quiz 15 and Project 6.0.
Mar 19: Lecture 26, 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 9: Midterms Returned, Reviewed
Assignment: No homework, enjoy the break.
Mar 7: Lecture 25, Storage Classes, Manifest Typing, Type Affinity and Basic Administration
You should have finished Reading 13.
What are SQLite storage classes?
How does SQLite implement manifest typing, and what does that mean?
What is SQLite's type affinity feature?
How can we perform basic administration with SQLite?
What are SQLite's PRAGMA commands?
Assignment: Reading 14, Quiz 14.
Mar 5: Lecture 24, Transactions & Locking
What is a deadlock?
What are database locks?
How does SQLite implement its locking mechanism?
What transaction types does SQLite support?
What do transactions have to do with locks?
How does SQLite implement locking?
What are the lock states that SQLite implements in its "coarse-grained" locking strategy?
Assignment: Reading 13.
Mar 2: Midterm Exam
You should be prepared to ace the midterm.
Assignment: No homework, yay!
Feb 29: Lecture 23, 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.
Feb 27: Lecture 22, Transactions and ACID
What is ACID?
What does it mean for a transaction to be "acidic?"
What is a transaction?
How do you start and end a transaction?
How do you roll back a transaction?
What are some syntax mechanisms that allow you to control what a transaction does in the event of a conflict?
Schema design wrap-up discussion.
Assignment: Reading 12 and Quiz 12.
Feb 24: Lecture 21, Indexes, Triggers, Views, Subqueries
You should have finished Reading 11 and Quiz 11.
What is an index?
How do you declare an index with SQL?
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: No homework!
Feb 22: Lecture 20, Schema Analysis
You should be finishing Project 5.0.
Project 5 discussion and schema analysis.
Assignment: Reading 11 and Quiz 11.
Feb 17: Lecture 19, JOINs in Relational Algebra & SQL, Relational Calculus
You should have finished Reading 10 and be working on Project 5.0. Hand in Quiz 10.
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.0.
Feb 15: Lecture 15, Relational Algebra
You should have finished Reading 9. Hand in Quiz 9
What is relational algebra, and why do we care?
What are the five primary relational operators?
Assignment: Reading 10, Quiz 10 and Project 5.0.
Feb 13: 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?
Feb 10: Lecture 13, Constraints, SQL and SQLite
You should have finished Reading 8. Hand in Quiz 8.
What are the four primary relational constraint types?
How do you implement such constrants in an RDBMS with SQL?
What behaviors can we "attach" to foreign key constraints?
Assignment: No homework, yay!
Feb 8: Lecture 12, SQL, More SQL, and SQLite
You should have finished Reading 7 and be finishing Project 4. Hand in Quiz 7.
Project 4.0 questions & tips.
How do you use WHERE and Boolean clauses in a SELECT statement?
How can you control the number of records that SELECT returns?
How do you check for a value that is NULL in a WHERE clause? (not with =)
What does SELECT DISTINCT do?
What are aliases?
What are some things to be aware of when using string literals in a query?
What does LIKE do? What are the string literal wildcard symbols?
What are aggregate functions and other common functions?
What does GROUP BY do?
What does HAVING do?
What does ORDER BY do?
What are JOINs? How do they work?
How do you use "dot-notation" in SQL?
What are inner joins, cross-joins, left outer and right outer JOINs?
Feb 6: Lecture 11, Database Design & SQL
You should have finished Reading 6 and be finishing Project 4. Hand in Quiz 6.
How do you select data from multiple tables?
What is a primary key?
What is a foreign key?
How would you design the database for a wine application given an ERD?
What SQL queries would you use to create the schema?
What SQL queries would you use to migrate data from the old table to the new tables?
Project 4.0 tips.
Feb 1: Lecture 10, Relational Model Operations & SQL, Intro to Database Design
You should have finished Reading 5. Hand in Quiz 5. Continue working on Project 4.0.
What is SQL? What are DDL, DML, DCL and TCL?
In what way is SQL a declarative language?
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?
What is CRUD?
How do you select records contained in a database?
How do you insert data into a database? Update? Delete?
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?
How would you design the database for a multi-user 'task list' application?
What SQL queries would you create when using such a database?
Jan 30: Lecture 9, The Relational Model
You should have finished Project 3.0.
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 5, Quiz 5, Project 4.0.
Jan 27: Lecture 8, ER Model Relationships, Constraints, EER & UML in Brief
You should be working on Project 3.0.
Where does the ER Model fit in the data abstraction stack?
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?
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?
Assignment: Finish Project 3.0.
Jan 25: Lecture 7, Intro to DB Design, ER Model, Entities, Relationships, Notation
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: Continue Project 3.0.
Jan 23: Lecture 6, Data Models, Introduction to the ER Model
You should have finished Project 2.0.
What are the problems associated with flat-file data models?
What criteria might you use to categorize or distinguish certain DBMSes from others?
What is a data model?
What is a schema?
What are the two main abstraction contexts for data models and schemas? (logical, physical)
Assignment: Reading 4, Quiz 4, Project 3.0.
Jan 20: Lecture 5, Ruby and Programming w/ Databases
You should have finished Reading 3 and be working on Project 2.0. Hand in Quiz 3.
How do you create and run a Ruby program?
How do you print something to the screen with Ruby?
How do you capture console input with Ruby?
What are "gems" and how do you install them?
How can you write a database-backed program with Ruby?
Assignment: Finish Project 2.0.
Jan 18: Lecture 4, Introduction to Ruby
You should have finished Reading 2 and be working on Project 2. Hand in Quiz 2.
Is SQL declarative or procedural?
What does a SELECT statement do?
What does an INSERT statement do?
How might you import flat-file data into SQLite?
Assignment: Reading 3, Quiz 3 and continue Project 2.0.
Jan 16: Lecture 3, History & Purpose, Databases from 5280 feet, Data Storage & I/O
You should have finished Project 1.0.
How have computers stored and retrieved persistent data in the past?
What database management systems were created in our history?
So... how does it all work? (top-down)
What are some of the common internal components of all DBMSes?
What are the different types of application architectures that involve DBMSes?
How does hard disk data storage work? (bottom-up)
Assignment: Reading 2, Quiz 2, Project 2.0.
Jan 13: Lecture 2, Motivation & Purpose
You should have finished Reading 1. Hand in Quiz 1.
How the heck did people come up with this 'DBMS' idea anyway?
What are some of the pains encountered when managing data?
Why should one use a DBMS? Why not?
Assignment: Finish Project 1.0.
Jan 11: 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.
