Database flash cards: Journeyman level
57 Database terms at the Journeyman level, as flash cards.
Updated
Terms in this deck
- ACID Compliance
- Atomic, Consistent, Isolated, Durable
- ACID Properties
- Atomicity, consistency, isolation, and durability: four goals for reliable transactions.
- Alternate Key
- A candidate key not selected as the primary key.
- Associative Entity
- An entity representing a relationship and optionally storing attributes about that relationship.
- Atomicity
- The property that all work in a transaction succeeds together or none of it takes effect.
- Boyce-Codd Normal Form (BCNF)
- A normal form requiring every determinant of a nontrivial dependency to be a superkey.
- Candidate Key
- A minimal set of attributes that uniquely identifies every tuple in a relation.
- Clustered Index
- The index that decides the order rows are stored in; one per table.
- COMMIT
- A statement that successfully ends a transaction and makes its changes durable.
- Common Table Expression (CTE)
- A named query expression introduced by WITH for use within one statement.
- Composite Key
- A key made from two or more attributes.
- Consistency
- The property that a successful transaction preserves declared database rules.
- Correlated Subquery
- A subquery that refers to values from an outer query and is evaluated in that context.
- Delete Anomaly
- A design problem where deleting one fact unintentionally removes another fact.
- Denormalization
- Deliberately adding controlled redundancy to support a measured performance or reporting need.
- DENSE_RANK
- A window function assigning equal rank to peers without leaving gaps after ties.
- Determinant
- The attribute set on the left side of a functional dependency.
- Durability
- The property that committed changes survive a later failure.
- Entity Integrity
- The rule that every relation has a primary key whose values are unique and non-NULL.
- First Normal Form (1NF)
- The normal form requiring each attribute value to be a single value from its domain.
- Functional Dependency
- A rule where one set of attributes determines another set of attributes.
- Index
- An auxiliary data structure that helps an RDBMS locate rows without scanning every row.
- Insert Anomaly
- A design problem where one fact cannot be stored without supplying an unrelated fact.
- Isolation
- The property controlling how concurrent transactions can observe one another's work.
- Junction Table
- A table that represents a many-to-many relationship through foreign keys.
- LAG
- A window function returning a value from an earlier row in the window partition.
- LEAD
- A window function returning a value from a later row in the window partition.
- Natural Key
- A key formed from meaningful values that already belong to the modeled domain.
- Nonclustered Index
- A separate sorted lookup structure that points back to the rows.
- Normalization
- Organizing relations to reduce redundancy and prevent modification anomalies.
- OVER Clause
- The clause defining the row window used by a window function.
- Parameterized Query
- A query keeping data values separate from SQL text through parameters.
- PARTITION BY
- An OVER subclause dividing query rows into independent window-function groups.
- Prepared Statement
- A parsed statement template executed later with supplied parameter values.
- RANK
- A window function assigning equal rank to peers and leaving gaps after ties.
- READ COMMITTED
- An isolation level preventing a statement from reading uncommitted changes.
- READ UNCOMMITTED
- The isolation level allowing the broadest concurrency and permitting dirty reads.
- Recursive CTE
- A CTE that repeatedly refers to its own accumulated result until no new rows are produced.
- Referential Integrity
- The rule that foreign-key values must reference an existing allowed key or be NULL when permitted.
- REPEATABLE READ
- An isolation level preventing reread rows from changing during the transaction.
- ROLLBACK
- A statement that reverses a transaction's uncommitted changes.
- ROW_NUMBER
- A window function assigning consecutive numbers to rows within each window partition.
- SAVEPOINT
- A named point inside a transaction to which later work can be rolled back.
- Second Normal Form (2NF)
- First normal form with no non-key attribute dependent on only part of a candidate key.
- SERIALIZABLE
- The strongest standard isolation level, requiring results consistent with some serial order.
- SQL Injection
- A vulnerability where untrusted input changes the intended structure of a SQL statement.
- Stored Procedure
- A named, saved set of SQL statements with parameters.
- Superkey
- Any attribute set that uniquely identifies every tuple, including sets with unnecessary attributes.
- Surrogate Key
- A key introduced solely to identify rows and carrying no domain meaning.
- Third Normal Form (3NF)
- Second normal form with non-key attributes depending on keys rather than other non-key attributes.
- Transaction
- A logical unit of database work completed or reversed as a whole.
- Transaction Isolation Level
- A setting defining which effects of concurrent transactions may be observed.
- Trigger
- Code that runs automatically when a table's rows change.
- Unique Index
- An index that also prevents duplicate indexed value combinations.
- Update Anomaly
- A design problem where repeated copies of one fact can become inconsistent during an update.
- Window Frame
- The subset of rows around the current row used by a window function.
- Window Function
- A function calculating across related rows without collapsing them into one row per group.