Skip to content
academia.sh
tr
Log in

Course Beginner

Data Modeling and Relational Theory

By the end of this course

Start course

01

The Relational Model

The rationale for the move from a file to a database, the relation–row–column–domain concepts, key types, integrity constraints, the three-valued logic of null values, and data type selection.

  1. 01 What Is a Database The search, concurrency, and integrity problems created by keeping data in a file, and which of these a database management system solves and with what guarantees.
  2. 02 The Relational Model The definition of relation, row, column, and domain; where row order and row repetition stand in the model, and where a SQL table departs from a relation.
  3. 03 Keys Definitions of superkey, candidate key, primary key, alternate key, and foreign key; the choice between a natural and a surrogate key, and the criteria for choosing a key.
  4. 04 Integrity Constraints The definitions of domain, entity, and referential integrity; a foreign key's delete and update actions; the timing of constraint checking and the case for writing a constraint into the schema.
  5. 05 Null Values Why null is not a value, the truth table of three-valued logic, how filters and NOT IN behave with nulls, how a uniqueness constraint treats a null, and ways to avoid null values.
  6. 06 Data Types The choice among integer, decimal, and floating-point numbers; text length and collation; storing dates alongside a time zone; binary data, truth values, and enumeration patterns.

02

Normalization

The concept of functional dependency, first, second, and third normal forms, Boyce–Codd and beyond, the measured trade-off of denormalization, and common schema design patterns.

  1. 01 Functional Dependency The definition and notation of functional dependency, dependency arising from meaning rather than from data, the types of dependency, Armstrong's axioms, and finding keys through attribute closure.
  2. 02 Normal Forms The three types of update anomaly; the definitions of first, second, and third normal form; and query output showing which anomaly each step removes.
  3. 03 Advanced Normal Forms The definition of Boyce–Codd normal form and how it differs from third normal form, a decomposition that loses dependency preservation, multivalued dependency and fourth normal form, and normalization's stopping point.
  4. 04 Denormalization Measuring the read cost of a normalized schema, the write cost of denormalization, the requirement that a derived column cover every write path, and the criteria for a denormalization decision.
  5. 05 Schema Design Patterns Translating relationship cardinalities into a schema, storing hierarchical data, subtype and time-dimension patterns, and common anti-patterns led by the key-value table.

03

Data Processing Approaches

The separation of transactional and analytical workloads, row- and column-oriented storage, the ACID properties, and the criteria for choosing between relational and non-relational systems.

  1. 01 Transactional and Analytical Workloads The difference in access pattern between transactional and analytical workloads, row- and column-oriented storage's effect on the bytes read, the covering index, and the star schema.
  2. 02 ACID Properties The concept of a transaction and the definition of the atomicity, consistency, isolation, and durability guarantees; a runnable demonstration of rollback and durability; the client's responsibility in the face of an error.
  3. 03 Relational vs. Non-Relational Selection What non-relational data model families give up and what they give in return, document storage's measured trade-off, the schema flexibility fallacy, and the decision criteria for the choice.

Start typing to search.

↑↓ ↵ Esc navigate · open · close