To register your interest for this event, please enter your details at our Event Registration page

C. J. Date is an independent author, lecturer, researcher, and consultant of international renown, 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 some 850,000 copies and is used by several hundred colleges and universities worldwide.

In this event, he will present "SQL and Relational Theory: How to Write Accurate SQL Code" where 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.

DURATION

Abbreviated (two-day) version covering the topics.

WHO SHOULD ATTEND

· Database application designers and implementers
· Information modelers and database designers
· Data and database administrators
· Computer science professors specializing in database matters
· DBMS designers, implementers, and other vendor personnel
· Database consultants
· 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:

· Have a solid understanding of relational theory
· Appreciate how relational principles provide SQL's logical underpinnings
· Understand the breadth and depth of those principles
· Know how to formulate complex SQL code with confidence that it's correct
· Generally, be able to use SQL relationally

DOCUMENTATION

Attendees will receive a workbook containing copies of the speaker's slides.

TOPIC OUTLINE

1. Setting the scene

Codd’s relational model
SQL terminology vs. relational terminology
Model vs. implementation
Properties of relations
Base vs. derived relations
Relations vs. relvars
The Third Manifesto and Tutorial D
Wittgenstein’s dictum

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
“Possibly nondeterministic” expressions
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
A column naming discipline

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
Implications and ramifications

5. Base relvars, base tables

Data definition
Updating is set level
Relational assignment
D_INSERT, I_DELETE, and other shorthands
Candidate and foreign keys
Predicates and propositions
The Closed World Assumption

6. SQL and relational algebra I: The original operators

Importance of closure
Relation type inference rules
Attribute renaming
Restriction, projection, join
Union, intersection, difference
Primitive operators
WITH and complex expressions
What expressions mean
Evaluating SQL expressions
Expression optimization

7. SQL and relational algebra II: Additional operators

Exclusive union
Semijoin and semidifference
Extend
Image relations
Divide
Aggregation and summarization
Relation valued attributes
“What if” queries
What about ORDER BY?
Recursive queries

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

Natural language is often ambiguous
Propositions and predicates
Connectives
Truth functional completeness
Quantification: EXISTS, FORALL, UNIQUE
Range variables and correlation names
Calculus expressions
Queries and constraints
SQL support
Transforming expressions
Relational completeness

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
Dot qualification
Range variables
Table, row, and scalar subqueries
“Possibly nondeterministic” expressions
Empty set issues
A BNF grammar for SQL

13. The relational model

Why databases must be relational
Theory is practical
The relational model defined
What a database really is
The relational model vs. others
Essentiality
What remains to be done?
The future of SQL

14. A relational approach to missing information

Preliminaries
Vertical and horizontal decomposition
Varieties of missing information
Constraints and queries
“Don’t know” answers
The Principle of Orthogonal Design
Remarks on physical design

MORE INFORMATION

Click here for more information on C.J. Date or this presentation.

REGISTRATION

To register your interest for this event, please enter your details at our Event Registration page.

PRICING

• NZOUG Member price - $495 + GST ($569.25 GST Inclusive)
• NZOUG Non-Member price - $695 + GST ($799.25 GST Inclusive) includes complementary NZOUG Single Contact Membership

To pay for this event, please make payment at our Payment page.