The North East Ohio Oracle’s User Group is proud to present “SQL and Relational Theory: How to Write Accurate SQL” a Chris Date Master Class. Class space is limited. Registrations are being handled by e-mail to firstname.lastname@example.org.
October 22-24 2013, Tuesday-Thursday
9-5PM, breakfast will be served 8-9.
Will be provided
4025 Kinross Lakes Parkway
Richfield, Oh 44286
Free, immediately next to venue
Early Registration (before September 22nd)
$550 Non member
$525 NEOOUG Member in good standing
$500 Groups of 5 or more (must pay with check)
Registration (after September 22nd)
$600 Non Member
$575 NEOOUG member in good standing
$550 Groups of 5 or more (must pay with check)
How to register
Send an email to email@example.com requesting to be registered.
$189/night + tax
3180 West Market Street
Akron, OH 44333
Mention North East Ohio Oracle User Group to get the discounted group rate.
The hotel is a 10 minute drive from the conference site.
Participants will receive a refund of 50% of the registration fee if notice is provided in writing before 22-Sep-13. Cancellations must be e-mailed to firstname.lastname@example.org with subject “Date Seminar Cancellation”. After 22-Sep-13 no refunds will be given. Substitutions for a registrant are permitted and must be made in writing. Conference no-shows will not receive a refund.
S Q L a n d R e l a t i o n a l T h e o r y :
H o w t o W r i t e A c c u r a t e S Q L C o d e
a Chris Date Master Class
Chris Date is the world’s best known relational advocate. In this seminar, he shows you how to write SQL code that’s logically correct; how to avoid various SQL traps and pitfalls; and, more generally, how to use SQL as if it were a true relational language.
I didn’t know there was so much I didn’t know!
- attendee at a recent offering of this seminar
ABOUT THIS SEMINAR
SQL is ubiquitous. But SQL is complicated, difficult, and error prone (much more so than SQL advocates would have you believe), and testing can never be exhaustive. So to have any hope of writing correct SQL, you must follow some discipline. What discipline? Answer: The discipline of using SQL relationally. But what does this mean? Isn’t SQL relational anyway?
Well, of course SQL is the standard language for use with relational databases—but that doesn’t make it relational! The sad truth is, SQL departs from relational theory in all too many ways; duplicate rows and nulls provide two obvious examples, but they’re not the only ones. Thus, systems based on SQL give you rope to hang yourself, as it were. So if you don’t want to hang yourself, you need to understand relational theory (what it is and why); you need to know about SQL’s departures from that theory; and you need to know how to avoid the problems they can cause. In a word, you need to use SQL relationally. Then you can behave as if SQL truly were relational, and you can enjoy the benefits of working with what is, in effect, a truly relational system.
Of course, a seminar like this wouldn’t be needed if everyone already used SQL relationally—but they don’t. On the contrary, there’s a huge amount of bad practice to be observed in current SQL usage. Such practice is even recommended in textbooks and other publications, by writers who really ought to know better; in fact, a review of the literature in this regard is a pretty dispiriting exercise. The relational model first saw the light of day in 1969—yet here we are, over 40 years later, and it still doesn’t seem to be very well understood by the database community at large. Partly for such reasons, this seminar uses the relational model itself as an organizing principle; it discusses various features of the model in depth, and shows in every case how best to use SQL to implement the feature in question. Note: Classroom exercises are an integral part of the seminar, and attendee discussion and interaction are encouraged.
1. Setting the scene
n Codd’s relational model
n SQL terminology vs. relational terminology
n Model vs. implementation
n Properties of relations
n Base vs. derived relations
n Relations vs. relvars
n The Third Manifesto and Tutorial D
n Wittgenstein’s dictum
2. Types and domains
n Domains are types
n Types and operators
n System vs. user defined types
n Scalar vs. nonscalar types
n Scalar types in SQL
n SQL type checking and coercion
n “Possibly nondeterministic” expressions
n SQL row and table types
3. Tuples and relations, rows and tables
n What’s a tuple?
n Rows in SQL
n What’s a relation?
n Relations are n-dimensional
n Relational comparisons
n TABLE_DUM and TABLE_DEE
n Tables in SQL
n A column naming discipline
4. No duplicates, no nulls
n What’s wrong with duplicates?
n Avoiding duplicates in SQL
n What’s wrong with nulls?
n Avoiding nulls in SQL
n A remark on outer join
n Implications and ramifications
5. Base relvars, base tables
n Data definition
n Updating is set level
n Relational assignment
n D_INSERT, I_DELETE, and other shorthands
n Candidate and foreign keys
n Predicates and propositions
n The Closed World Assumption
6. SQL and relational algebra I: The original operators
n Importance of closure
n Relation type inference rules
n Attribute renaming
n Restriction, projection, join
n Union, intersection, difference
n Primitive operators
n WITH and complex expressions
n What expressions mean
n Evaluating SQL expressions
n Expression optimization
7. SQL and relational algebra II: Additional operators
n Exclusive union
n Semijoin and semidifference
n Image relations
n Aggregation and summarization
n Relation valued attributes
n “What if” queries
n What about ORDER BY?
n Recursive queries
8. SQL and constraints
n Type constraints
n Type constraints in SQL
n Database constraints
n Database constraints in SQL
n The role of transactions
n Immediate vs. deferred checking
n Multiple assignment
n Constraints vs. predicates
n The Golden Rule
n Correctness vs. consistency
9. SQL and views
n Views are relvars
n The Principle of Interchangeability
n Views and predicates
n Retrieval operations
n Views and constraints
n Updating operations
n What are views really for?
n Views and snapshots
10. SQL and logic I: Relational calculus
n Natural language is often ambiguous
n Propositions and predicates
n Truth functional completeness
n Quantification: EXISTS, FORALL, UNIQUE
n Range variables and correlation names
n Calculus expressions
n Queries and constraints
n SQL support
n Transforming expressions
n Relational completeness
11. SQL and logic II: Using logic to write SQL code
n Important identities
n SQL and implication
n SQL and FORALL
n Correlated subqueries
n Naming subexpressions
n Dealing with ambiguity
n Using COUNT
n ALL or ANY comparisons
n GROUP BY and HAVING
12. Further SQL topics
n Explicit tables
n Dot qualification
n Range variables
n Table, row, and scalar subqueries
n “Possibly nondeterministic” expressions
n Empty set issues
n A BNF grammar for SQL
13. The relational model
n Why databases must be relational
n Theory is practical
n The relational model defined
n What a database really is
n The relational model vs. others
n SQL departures from the relational model
n What remains to be done?
14. A relational approach to missing information
n Vertical and horizontal decomposition
n Varieties of missing information
n Constraints and queries
n “Don’t know” answers
Three days (18 classroom hours).
WHO SHOULD ATTEND
n Database application designers and implementers
n Information modelers and database designers
n Data and database administrators
n Computer science professors specializing in database matters
n DBMS designers, implementers, and other vendor personnel
n Database consultants
n People responsible for DBMS product evaluation and acquisition
The seminar is not meant for beginners: Attendees will be expected to have at least an elementary familiarity with database concepts in general and the SQL language in particular. Attendees will also be expected to attempt a number of pencil and paper exercises in class. Solutions to those exercises will be discussed in class as well.
On completion of this seminar, attendees will:
n Have a solid understanding of relational theory
n Appreciate how that theory provides SQL’s logical underpinnings
n Understand the breadth and depth of that theory
n Know how to formulate complex SQL code with confidence that it’s correct
n Generally, be able to use SQL relationally
Attendees will receive a workbook containing copies of the speaker’s slides.
SPEAKER: Chris Date
C. J. Date is an independent author, lecturer, researcher, and consultant, specializing in relational database technology. He is best known for his book An Introduction to Database Systems (eighth edition, Addison-Wesley, 2004), which has sold well over 850,000 copies and is used by several hundred colleges and universities worldwide. He is also the author of many other books on database management, including most recently:
n From Morgan Kaufmann: Temporal Data and the Relational Model (coauthored with Hugh Darwen and Nikos A. Lorentzos, 2003)
n From Addison-Wesley: Databases, Types, and the Relational Model: The Third Manifesto (coauthored with Hugh Darwen, 2006)
n From Apress: Date on Database: Writings 2000-2006 (2006) and The Relational Database Dictionary, Extended Edition (2008)
n From Trafford: Logic and Databases: The Roots of Relational Theory (2007) and Database Explorations: Essays on The Third Manifesto and Related Topics (coauthored with Hugh Darwen, 2010)
n From Ventus: Go Faster! The TransRelationaltm Approach to DBMS Implementation (2002,2011)
n From O’Reilly: SQL and Relational Theory: How to Write Accurate SQL Code (2nd edition, 2012); Database Design and Relational Theory: Normal Forms and All That Jazz (2012); View Updating and Relational Theory: Solving the View Update Problem (2013); and Relational Theory for Computer Professionals: What Relational Databases Are Really All About (2013)
Mr. Date was inducted into the Computing Industry Hall of Fame in 2004. He enjoys a reputation that is second to none for his ability to communicate complex technical subjects in a clear and understandable fashion.