Tony Rogerson's ramblings on SQL Server

One of the pioneers of the Relational Database, Chris Date is giving a 2 day seminar in Edinburgh (13th and 14th May 2010) based around his new book "SQL and Relational Theory - How to Write Accurate SQL Code" which if you don't already have I'd say is a must buy.

When I first saw this and what he will cover I thought, oh yer - this is going to cost the earth, well it doesn't - its £750 for the two days and there are discounts available for multiple bookings, being a member of the UK SQL Server User Group or members of the academic community.

More information here: http://www.justsql.co.uk/chris_date/cjd_edin_may_2010.htm.

The person you need to contact is Peter Robson (rde@justsql.com), if you are a member of the UK SQL Server User Group just say so and mention my name.

If you are interested - talk to Peter this week because he needs to formulate numbers by the end of March, you may miss out otherwise.

The detail of what Chris Date will cover is shown below, note this will be technology neutral, what no T-SQL you ask; don't worry - SQL Server uses ANSI 92 SQL so the majority of syntax he shows will work, but frankly this is more about thinking Relationally than being a code monkey.

1. Setting the scene

• Codd's relational model
  Model vs. implementation
  Properties of relations
  Base vs. derived relations
  Relations vs. relvars
  Values vs. variables

2. Types and domains

• Domains are types
  Types and operators
  System vs. user defined types
  Scalar vs. nonscalar types
  Scalar types in SQL
  SQL type checking and coercion
  SQL collations
  SQL row and table types

3. Tuples and relations, rows and tables

• What's a tuple?
  Rows in SQL
  What's a relation?
  Relations are n-dimensional
  Relational comparisons
  TABLE_DUM and TABLE_DEE
  Tables in SQL
  SQL column naming

4. No duplicates, no nulls

• What's wrong with duplicates?
  Avoiding duplicates in SQL
  What's wrong with nulls?
  Avoiding nulls in SQL
  A remark on outer join

5. Base relvars, base tables

• Data definition
  INSERT, DELETE, UPDATE
  Updating is set level
  Relational assignment
  Candidate and foreign keys
  Tables and predicates
  Tables vs. types

6. SQL and relational algebra I:
The original operators

• Result types
  Restriction, projection, join
  Union, intersection, difference
  WITH and complex expressions
  What expressions mean
  Evaluating SQL expressions
  Optimization
  Importance of column names

7. SQL and relational algebra II:
Additional operators

• Semijoin and semidifference
  Extend
  Image relations
  Divide
  Aggregation and summarization
  Grouping and ungrouping
  "What if" queries
  What about ORDER BY?

8. SQL and constraints

• Type constraints
  Type constraints in SQL
  Database constraints
  Database constraints in SQL
  The role of transactions
  Immediate vs. deferred checking
  Multiple assignment
  Constraints vs. predicates
  The Golden Rule
  Correctness vs. consistency

9. SQL and views

• Views are relvars
  The Principle of Interchangeability
  Views and predicates
  Retrieval operations
  Views and constraints
  Updating operations
  What are views really for?
  Views and snapshots

10. SQL and logic I:
Relational calculus

• Propositions and predicates
  Quantification: EXISTS, FORALL, UNIQUE
  Range variables and correlation
  Calculus expressions
  SQL support
  Transforming expressions

11. SQL and logic II:
Using logic to write SQL code

• Important identities
  SQL and implication
  SQL and FORALL
  Correlated subqueries
  Naming subexpressions
  Dealing with ambiguity
  Using COUNT
  ALL or ANY comparisons
  GROUP BY and HAVING

12. Further SQL topics

• Explicit tables
  Range variables
  Table, row, and scalar subqueries
  "Possibly nondeterministic" expressions
  Cursor operations
  Empty set issues
  A BNF grammar

13. The relational model

• The relational model vs. others
  The relational model defined
  What remains to be done?
  The future of SQL

14. Database design theory

• The place of design theory
  FDs and BCNF
  JDs and 5NF
  6NF
  Normalization is not a panacea
  But don't denormalize!
  The Principle of Orthogonal Design
  Remarks on physical design