Notes for Domain 8
Edit me

Databases

relational –> two-dimensional tables (rows aka tuple + columns aka attributes)

keys –> subset of fields, uniquely id records

  • primary key –> unique value in each row; ONE PER TABLE
  • candidate key –> any column w/ unique values
  • foreign key –> key that matches a primary key in another table

cardinality = # of rows

degree = # of columns

DB integrity

  • referential –> every foreign key matches a primary key
  • semantic –> each column value is consistent w/ the attribute data type
  • entity –> each row has a unique non-null primary key

normalization –> data is logically concise, organised & consistent; removes duplicates

  • 1NF
  • 2NF
  • 3NF

DB view –> results of a query, used to provide a constrained user interface

TRANSACTIONS

  • ensure data integrity
  • set of SQL instructions that succeed/fail as a group
  • commit/rollback
  • required characteristics - ACID:
    • Atomicity - “all or nothing”: one fails, transaction fails
    • Consistency -
    • Isolation - separate from each other
    • Durability - once commited, must be preserved (transaction logs)

CONCURRENCY –> aka edit control - preventive sec mech that makes certain that info stored in db is always correct (Integrity + Availab protected)

  • uses the LOCK feature, to block the element

no concurrency:

  • LOST UPDATES –>two diff processes make updates unaware of each other
  • DIRTY READS –> record from an unsuccessfully committed transaction is read

Open Database Connectivity (ODBC) –> proxy btw app - backend db, more freedom for programmers to interact w/ db

. .
REPLICATION cpoy data btw live mirrors of a single db; backup or HA/redundancy; BOTH visible to users
SHADOWING copy data from live db to a backup, read-only db; USERS don’t see the shadow
Tags: domain_8