You should have finished Project 8.0.
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 attributes do all of the fancy, funny-named key-value / distributable databases share?
What is MongoDB?
What does it mean to be "schema-less?"
(MongoDB demo, to be continued)
Assignment: Reading 39 and Quiz 39.
You should be working on Project 8.0.
What are Hibernate and iBatis? How do they map objects to relations?
Why is it best to leverage tools when using Java-based ORMs?
What are ADO.net, the ADO Entity Framework, and LINQ?
How does ADO.net fit the pattern of most low-level database APIs?
SQL Server in a nutshell
What is an OODB? How is it different from an RDBMS?
Assignment: Finish Project 8.0.
You should have finished Project 7.0.
What are some things to think about while designing a database schema when given a specification?
How might you design a schema for a typical Web application?
Where should binary data (photos, audio, video, etc) be stored? Why?
Assignment: Project 8.0.
You should be working on Project 7.0.
You should have finished Reading 35.
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: Project 7.0.
You should have finished Project 6.3.
When should you optimize?
How might you optimize a database server itself?
How can you optimize a MySQL server instance?
What are some common performance issues at the database level?
How might you optimize a database?
Assignment: Reading 35.
You should have finished Reading 33 and be working on Project 6.3.
Hack my database server
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 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: Finish Project 6.3.
You should have finished Reading 32 and be working on Project 6.3. Hand in Quiz 32.
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?
Project examples
Assignment: Reading 33, continue Project 6.3.
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 32, Quiz 32 and Project 6.3.
You should have finished Reading 29.
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: (none).
You should have finished Reading 28.
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 29.
You should have finished Reading 28.
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 29, Quiz 29 and Project 6.3.
You should have finished Project 6.2.
What is the ActiveRecord design pattern?
How might you implement the ActiveRecord pattern yourself?
What is the ActiveRecord Ruby library and how does it work?
What schema conventions does AR depend on?
How do you declare associations with ActiveRecord?
What are some other handy features of ActiveRecord?
How might you design a db-backed application in 30 minutes?
Assignment: Reading 28.
You should have finished Project 6.1.
What is the so-called Object-Relational Impedance Mismatch?
What are the benefits and drawbacks of ORM tools?
Project database design review.
Assignment: Project 6.2.
You should have finished Reading 25 and Project 6.0. Hand in Quiz 25.
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?
Assignment: Project 6.1.
You should have finished Reading 24. Hand in Quiz 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.
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.) and Ruby on your own machine, or request a remote development environment.
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.
You should be working on Project 5.1.
More subquery examples.
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 is SQL injection and how do you do it?
How do parameterized queries prevent SQL injection attacks?
Assignment: Finish Project 5.1.
You should have finished Reading 20 and Project 5.0.
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: Project 5.1.
You should have finished Project 4.2.
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?
Assignment: Reading 20 and Project 5.0.
You should be working on Project 4.2.
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?
Assignment: Finish Project 4.2.
You should be working on Project 4.2
What is relational algebra, and why do we care?
What are the five primary relational operators?
What is relational calculus? Why do we care?
Assignment: Reading 18 and Quiz 18.
You should have finished Project 4.1.
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?
Assignment: Project 4.2.
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.1.
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.0.
You should have finished Reading 13 and Project 3. Hand in Quiz 13.
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?
You should have finished Reading 12 and be working on 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: Reading 13, Quiz 13. Finish Project 3.0.
You should have finished Reading 11. 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, Project 3.
You should have finished Reading 10 and Project 2.3. 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.
You should have finished Reading 9 and Project 2.2. 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, Project 2.3.
Why is it 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.2.
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.
You should have finished Reading 6 and Project 2.0. Hand in 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.
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.
You should have finished Reading 4 and Project 1.1. 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?
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?
What are some of the different components of a DBMS?
Why should one use a DBMS? Why not?
So... how does it all work? (high-level)
Assignment: Reading 3, Quiz 3, Project 1.1.
You should have finished Reading 1 and Project 1.0. Hand in Quiz 1.
How the heck did people come up with this 'DBMS' idea anyway?
You should be present for the first day of class (of course).
What is data? What is a database?
What is a DBMS?
Projects introduction.
Assignment: Reading 1, Quiz 1, Project 1.0.
Our challenge: Numerous new database management systems have emerged that depart from the relational model. Are they better than RDBMSes? In what way? Is the relational model dead? What makes these new databases different? How do we program with them? Is it fun?