🗂️

SQL: Database Design & Normalization

32 cards · 🇺🇸 English

Schema design, keys, and normal forms for relational databases.

Study all 32 cards for free

Flashcards, quizzes, pixel-art games and a daily review that remembers for you.

Study this deck free

Try the first 10 cards

1 / 10
Question

Database normalization

Knew itLearning

Tap the card for the answer · swipe right if you knew it, left if not

Already have an account? Sign in

Cards

  1. 1Database normalization
    The process of structuring a relational database according to normal forms to reduce data redundancy and improve data integrity
  2. 2Insertion anomaly
    When certain facts cannot be recorded unless other unrelated data is also recorded, often requiring null values
  3. 3Update anomaly
    When the same information appears on multiple rows, updates may be incomplete, leaving the database in an inconsistent state
  4. 4Deletion anomaly
    When deleting data representing one fact necessitates deleting data representing a completely different fact
  5. 5First Normal Form (1NF)
    Each field contains a single value (scalar columns), no duplicate rows, and no nested records or sets of values
  6. 6Second Normal Form (2NF)
    A table in 1NF where every non-prime attribute has a full functional dependency on each candidate key (attributes depend on the whole key)
  7. 7Third Normal Form (3NF)
    A table in 2NF where every non-trivial functional dependency either begins with a superkey or ends with a prime attribute (no transitive dependencies)
  8. 8Boyce-Codd Normal Form (BCNF)
    A stricter form of 3NF where every non-trivial functional dependency begins with a superkey
  9. 9Transitive functional dependency
    When an attribute depends on another non-key attribute, which in turn depends on the primary key (e.g., A → B → C)
  10. 10Fourth Normal Form (4NF)
    A table in BCNF where every non-trivial multivalued dependency begins with a superkey
  11. 11Fifth Normal Form (5NF)
    A table where every join dependency has only superkey components, preventing lossless decomposition issues
  12. 12Sixth Normal Form (6NF)
    A table where the row contains the primary key and at most one other attribute
  13. 13Primary key
    A column or set of columns that uniquely identifies each row in a table
  14. 14Composite key
    A primary key consisting of two or more columns that together uniquely identify a row
  15. 15Candidate key
    A column or set of columns that could serve as a primary key (minimal superkey)
  16. 16Superkey
    A set of one or more columns that can uniquely identify a row in a table
  17. 17Functional dependency
    A relationship where one attribute's value determines another attribute's value (A → B)
  18. 18Multivalued dependency
    When one attribute in a table determines a set of values for another attribute, independent of other attributes
  19. 19Data redundancy
    The unnecessary duplication of data within a database
  20. 20Data integrity
    The accuracy, consistency, and reliability of data stored in a database
  21. 21Denormalization
    Intentionally introducing redundancy into a database design to improve query performance
  22. 22Domain-Key Normal Form (DKNF)
    A normal form where every constraint is a consequence of domain constraints and key constraints
  23. 23Elementary Key Normal Form (EKNF)
    A normal form that falls between 3NF and BCNF, intended to capture qualities of both while avoiding their problems
  24. 24Essential Tuple Normal Form (ETNF)
    A normal form introduced in 2012 where every join dependency has a superkey component
  25. 25Normal forms beyond 4NF primarily used for
    Academic interest, as the problems they solve rarely appear in practice
  26. 26Join dependency
    A constraint that requires a relation to be equal to the join of its projections
  27. 27Main benefit of normalization
    Reducing redundancy, preventing anomalies, and allowing the database structure to be extended without major changes
  28. 28Columnar storage in databases
    An internal 6NF representation used in data warehouses where data is stored by column rather than by row
  29. 29Where is 6NF particularly useful?
    In data warehouses for fast query on large data volumes, not in OLTP systems due to table proliferation
  30. 30Lossless decomposition
    Breaking a table into multiple tables in a way that the original table can be reconstructed by joining them without data loss

And 2 more cards. Study the whole deck free

Articles about this deck

More Programming decks

See all
Study all 32 cards free