Keys to the Kingdom
This unique identifier in a relational table ensures that no two rows (tuples) have identical key values.
What is a Primary Key?
This abstract database schema level maps out entities, attributes, and relationships without relying on specific database software or physical storage.
What is the Conceptual Schema?
A database table satisfies this normal form if all column values are atomic and all repeating groups have been eliminated.
What is First Normal Form (1NF)?
Database normalization is primarily performed to minimize this issue, which wastes storage and leads to data anomalies.
What is Data Redundancy (or Data Duplication)?
SQL statements such as CREATE TABLE, ALTER TABLE, and DROP TABLE belong to this SQL functional category.
What is DDL (Data Definition Language)?
This type of key is formed by combining two or more attributes together to uniquely identify a record in a table.
What is a Composite Key (or Concatenated Key)?
In an Entity Relationship Diagram (ERD), this term specifies the maximum numerical relationship between two entity occurrences (such as 1:1, 1:N, or M:N).
What is Cardinality?
This functional dependency occurs when a non-key attribute depends on only part of a composite primary key rather than the entire key.
What is a Partial Dependency (or Partial-Key Dependency)?
Denormalization is the deliberate process of reintroducing data redundancy into a normalized schema to improve this key metric.
What is Query Performance (or Read/Retrieval Speed)?
SQL statements such as SELECT, INSERT, UPDATE, and DELETE belong to this SQL functional category.
What is DML (Data Manipulation Language)?
This database management feature prevents conflicts or data corruption when multiple users attempt to read or update the same record simultaneously.
What is Concurrency Control?
This property in an ERD specifies whether an entity instance must participate in a relationship (total) or may optionally participate (partial).
What is Modality (or Optionality / Participation)?
To move a table from 1NF to 2NF, you must identify and eliminate this specific dependency type.
What is a Partial Dependency?
Denormalization is most beneficial for this specific classification of database applications, where query reads heavily outnumber write operations.
What are Read-Intensive Applications (or Reporting / OLAP Systems)?
State the core functional difference between DDL and DML statements in SQL.
What is DDL defines and modifies database structure/schemas, whereas DML queries and manipulates the data stored within those structures?
Relational databases use this transactional principle to guarantee that a series of operations either all complete successfully or none are applied at all.
What is Atomicity (or Atomic Transaction Processing)?
This schema level defines the lowest-level data storage details, including data structures, indexes, and disk layout algorithms.
What is the Physical Schema (or Internal Schema)?
This dependency exists when a non-key attribute depends on another non-key attribute rather than directly on the primary key (A -> B -> C).
What is a Transitive Dependency?
This primary operational trade-off occurs when denormalizing a database: faster execution of complex SELECT queries comes at the expense of this major risk.
What is higher risk of data inconsistencies/anomalies (or increased cost/complexity for UPDATE, INSERT, and DELETE queries)?
This SQL sub-language category is used when a Database Administrator defines table constraints, primary keys, foreign keys, and field data types.
What is DDL (Data Definition Language)?
Identify two architectural limitations that relational databases encounter when attempting to scale for modern "Big Data" applications.
What are rigid schemas, unstructured data handling, horizontal scalability limitations, or object-relational impedance mismatch?
In Crow’s Foot ERD notation, a symbol consisting of a single vertical line followed by a circle next to an entity represents this precise cardinality and modality.
What is Optional participation with a maximum cardinality of one (Modality = 0 / Partial, Cardinality = 1)?
Describe the structural modification required to convert a 2NF table containing a transitive dependency (X -> Y) into 3NF.
What is removing non-key attribute Y from the main table, creating a new table containing X and Y with X as its primary key, and maintaining X as a foreign key in the original table?
Selecting an inappropriate data type for a key field (e.g., using INTEGER instead of VARCHAR for telephone numbers or postal codes) causes this specific table integrity failure.
What is loss of leading zeroes, inability to store alphanumeric characters, or data conversion errors?
Prompt: A database developer executes ALTER TABLE Employee ADD Salary DECIMAL(10,2);. Identify whether this statement is DDL or DML and justify why.
What is DDL, because it alters the underlying structural schema of the table rather than manipulating stored record rows?