|
|
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 rgravens@gmail.com.
Seminar Facts
|
Course Dates |
October 22-24 2013,
Tuesday-Thursday |
|
Start/End Times |
9-5PM, breakfast
will be served 8-9. |
|
Breakfast/Lunch/Snacks |
Will be provided |
|
Location |
4025 Kinross Lakes
Parkway Richfield, Oh 44286 |
|
Parking |
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 rgravens@gmail.com
requesting to be registered. |
|
Conference Hotel |
$189/night
+ tax Hilton
Akron/Fairlawn 3180
West Market Street Akron, OH 44333 (330) 867-5000 Mention North East
Ohio Oracle User Group to get the discounted group rate. The hotel is a 10
minute drive from the conference site. |
|
Cancellation Policy |
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 rgravens@gmail.com 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
Revised
Version
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.
TOPIC OUTLINE
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 Extend
n Image
relations
n Divide
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 Connectives
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 Essentiality
n SQL departures from the relational model
n What
remains to be done?
14. A relational approach to missing
information
n Preliminaries
n Vertical
and horizontal decomposition
n Varieties
of missing information
n Constraints and queries
n “Don’t
know” answers
DURATION
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.
OBJECTIVES
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
DOCUMENTATION
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.
¨¨¨¨¨