SQL | Tutorial

Database Normalization

The process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.

There are three common forms of database normalization: 1st, 2nd, and 3rd normal form. These regard tables:

• 1NF — Each column contains an atomic value. No repeating groups of columns.
• 2NF — 1NF plus all columns depend on the table’s Primary Key. (Single-column Primary Key.)
• 3NF — 2NF plus columns have no transitive dependencies. That is, no one column can be derived from another column (through the pk).

Table Relationships (data.model.example)

Tables must be normalized to ensure data integrity survives deletions. This necessitates Junction Table(s), even if such are only internal, auto-generated by a sophisticated RDBMS through (SQL) table definition statements, constraints and/or functions.

• A Junction Table resolves a Many-to-Many (M:M) relationship into many (rows of) M:1>-<1:M relationships, and contains only the Foreign Keys (fk) of the constituent tables.

• One-to-many (1:M) relationships require the Primary Key (pk) of the one table as a Foreign Key (fk) in the many table.

  -- Create a TABLE 
  CREATE TABLE foos (
  foo_id int NOT NULL AUTO_INCREMENT,
  thing_1 text NOT NULL,
  thing_2 int,
  PRIMARY KEY (foo_id)
  ); 
  
  -- Add a PRIMARY KEY to foos TABLE
  ALTER TABLE foos ADD PRIMARY KEY (foo_id)
  -- Add it as a Foreign Key at some other TABLE 
  FOREIGN KEY (fk_foo_id) RENCES foos (foo_id)
  
  -- Add FOREIGN KEY per ALTER
  ALTER TABLE this
  ADD FOREIGN KEY (other_id) RENCES others (other_id); 
  
  -- Create a FOREIGN KEY during TABLE creation
  FOREIGN KEY (other_id) RENCES others (other_id)
  -- ... better way (name it)
  CONSTRAINT fk_other_this FOREIGN KEY (other_id) RENCES others (other_id)
  
  -- Add a COMMENT to an object
  COMMENT ON objTYPE obj_name IS '...';
  -- E.g., 
  COMMENT ON TABLE customers IS 'List of active customers';
  

SQL JOIN Types

AND vs. WHERE clause @ INNER vs. LEFT JOIN

• Does not matter for INNER joins
• Matters for OUTER joins
  • WHERE clause: After joining. Records will be filtered after JOIN has taken place.
  • ON clause - Before joining. Records (from right table) will be filtered before joining. This may end up as NULL in the result (since OUTER JOIN).

SQLite

• CREATE TABLE + Constraints
  • FOREIGN KEY support (enable)
• PRAGMA SQL Extension