Know the ways to make backups of a MySQL database and the benefits/drawbacks of each approach
Given a transaction log and a sample database in an inconsistent state, be able to recover the database to a consistent state at a specific point in time
Know the procedures required to recover a MySQL database to a specific point in time
Know the two types of transaction logs utilized by MySQL, their purposes and the differences between them
Know what a transaction is
Know the four properties of a transaction (ACID)
Know what a database log file contains and its role in disaster recovery
Know what the 3 classic concurrency problems are
Know the isolation levels and which concurrency problems they prevent
Know how the isolation levels are implemented using locks
Know the difference between a shared and exclusive lock
Know the typical levels of locking granularity
Know what deadlock is, what causes it and how to prevent it
Know the difference between pessimistic and optimistic concurrency control
Know how MySQL implements the isolation levels (using a combination of pessimistic and optimistic mechanisms)
Know the following terms: stored procedure, stored function, cursor
Know the difference between procedural and non-procedural languages, and be able to classify a given SQL language statement as procedural or non-procedural
Know the typical uses for each of these
Know the advantages and disadvantages of using each of these features
Know the two main techniques for creating stored queries in enterprise databases such as SQL Server (views, stored procedures); know the relative advantages of the two approaches
Be able to write a simple stored procedure that contains a SELECT that uses a parameter defined by the procedure
Know the US national laws we covered
Know what PCI DSS is and the requirements we discussed
Know what a database role is and the best practice for managing user permissions via roles
Know the GRANT statement and its capabilities
Know what an index is and why you would want to create one
Know what indexes are created automatically by the database
Know what an index contains
Know important statistics maintained by the database
For a given query and index, be able to predict the likelihood that a database engine would use the index, based on factors such as number of rows in the table, whether or not the index covers the query, whether columns in the query appear in the index, selectivity of the column, etc.
Know the difference between a unique and a non-unique index
Know the performance implications of indexes
Know definitions of the following terms: primary key, candidate key, foreign key
Know what normal forms are and their role in database design
Know the syntax of the following SQL statements: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE
Know the process of database design we have discussed in class
Know the characteristics of a good primary key
Know the tools at a database designer’s disposal for enforcing data integrity (both non-procedural and procedural)
Be able to recognize a database table that violates one of the normal forms, and fix the violation. (You do not need to know the normal forms.)
Given a Entity-Relationship database diagram, be able to convert the diagram to a relational database schema
Given a poorly designed relational schema, redesign it to fix its design flaws.