Database Management · Intermediate level

Intermediate level · Module 3 of 6 · 15 guided hours

Views, Constraints and Transactions

Protect data quality with constraints, simplify access with views and keep multi-step changes consistent with transactions.

Learning objectives

  • Enforce rules with constraints and defaults
  • Create views for reporting and controlled access
  • Use transactions and understand ACID and isolation

Topics

  • NOT NULL, UNIQUE, CHECK, DEFAULT
  • foreign key actions
  • views and their uses
  • transactions: BEGIN, COMMIT, ROLLBACK
  • ACID properties
  • isolation and concurrency basics

Lessons

  1. Lesson 1 Constraints and Defaults

    Let the database enforce rules with NOT NULL, UNIQUE, CHECK, defaults and foreign key actions.

    CHECK constraintUNIQUE across columnsDEFAULTON DELETE RESTRICT / CASCADE

  2. Lesson 2 Views

    Save queries as views to simplify reporting and control what different users can see.

    CREATE VIEWreporting viewsecurity through viewsmaterialised views (overview)

  3. Lesson 3 Transactions and ACID

    Group related changes into transactions and understand atomicity, consistency, isolation and durability.

    transactionatomicityisolationdurability

Practical activity

Add constraints that stop duplicate registrations, create reporting views for managers and write a transaction that records a distribution and its items together.

Expected outcomes

  • Data that cannot become invalid
  • Consistent multi-step changes

Assessment

Practical: attempt a set of invalid operations that must all be rejected, and demonstrate a rolled-back transaction.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.