Lectures
Feb 22: Lecture 20, Indexes, Triggers, Views, Subqueries
You should have finished Project 5.0.
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: 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.
