Source material for the final

• All lectures through Monday August 14th
• All required readings
• All required challenge probelms.
• All gradiance exercises
• The in-class and newsgroup discussion about privacy
• The AuctionBase programming project

Topic Outline

1. Basic motivation and database terminology

2. Relational model
   • Relations (tables), attributes (columns), tuples (rows)
   • Schema versus instance
   • Keys, null values

3. XML
   • Well-formed XML
   • DTDs and valid XML
   • ID and IDREF(S) attributes

4. Relational algebra
   • Basic operators: select, project, Cartesian product, union, difference, rename
   • Abbreviations: natural join, theta join, intersection

5. SQL
   • Data definition: create table, drop table
   • Data manipulation: select command
   • Subqueries, aggregates, duplicates, null values
   • Data modification: insert, delete, update

6. SQL + Programming (all at a high level)
   • embedded SQL
   • stored procedures
   • call-level interfaces
   • general database client programming principles

7. XML query languages & programming
   • XPath (as covered in lecture)
   • SAX & DOM (high level, not syntactic understanding)

8. Relational DB Redundancy & Normalization
   • Functional dependencies (FD's): motivation, definitions, rules
   • Closure of attribute set with respect to FD's
   • Design flaws: redundancy, update & deletion anomalies
   • Boyce-Codd Normal Form (BCNF): motivation, definition, decomposition algorithm
   • Multivalued dependencies (MVDs): motivation, definitions, rules
   • Fourth Normal Form (4NF): motivation, definition, decomposition algorithm
   • Additional material in textbook readings

9. UML
   • Classes
   • Associations, multiplicities, self-associations
   • Association classes
   • Subclasses
   • Aggregation and composition
   • Translating UML designs to relational schemas

10. Constraints and triggers
    • Non-null constraints
    • Key constraints
    • Referential integrity
    • Attribute-based constraints
    • Tuple-based constraints
    • General assertions
    • SQL-99 triggers

11. Transactions
    • Motivation: multi-user, crash recovery
    • ACID properties
    • Serializability
    • Transaction rollback
    • Isolation levels: read uncommitted, read committed, repeatable read, serializable

12. Indexes
    • Properties and uses of indexes
    • Index selection problem

13. Views
    • Creating and using views
    • Modifying views
    • Materialized views

14. Authorization
    • Privileges
    • Views and authorization
    • grant and revoke statements

15. Data warehousing and mining
    • OLTP vs. OLAP
    • Data warehousing architecture
    • Star schemas
    • Data cubes
    • SQL CUBE
    • Association rules, support & confidence

16. Data Security & Privacy
    SQL injection attacks
    • Authorization
    • Technical privacy solutions:
      • Authorization
      • Encryption
      • One-way functions
      • Statistical DBs & Privacy-preserving data mining
    • Class discussion on ethics & privacy.

17. Object-relational SQL
    • Type definitions
    • Nested structures
    • Distinct types
    • Methods
    • References
    • Ordering relationships