70-461 Training Kit Chapter 1, Lesson 1: Foundation

SQL

• based on set theory & predicate logic
• Declarative language not procedural.
• Is 3-valued predicate logic: true, false, unknown

Relational Model

• not equal Relationship
• based on 2 mathematics branch: set theory and predicate logic
• All attribute must has a name
• All attribute names must be unique
• No ordering for column or row

T-SQL

• is a dialect extension of SQL base on ISO standard.
• Based on multiset theory (may include duplicates).
• Allows column ordering based on the order that column is create/defined

Recommendation

•  to use standard syntax such as CAST (standard), CONVERT(T-SQL specific)
• Your code and knowledge more portable.

A Relation:

• Is a like a Tables in SQL
• include a heading and a body
• Heading of a relation is a set of attributes
• Body of a relation is set or rows(tuples)

Tuples:

    like rows

A Set:

• consider as a whole (interact with a set as a whole not row by row, RBAR)
• no order
• no duplicate

A Predicate:

• a proposition either true or false
• for filter
• enforce data integrity
• define data model (???)

An attribute

• identify a name and a type

Relational thinking

• Avoid thinking iterative (cursor, loop).
• Think in set.

When use order by clause

• The result is considered as a cursor, not relational.

NULL

• Missing
• NULL doesn't equal blank
• NULL doesn't equal NULL
• Need to handle with care.
• Is a mark for a missing value - not a value itself.

Using correct Terminology

Fields and Records

• Are physical
• Field <> column
• Record <> row

Tables are logical

• Have logical rows and columns.

Geeks speak: Not NULL value, said NULL mark instead

Credits: Microsoft 70-461 Training Kit