Download DATA MANAGEMENT FOUNDATIONS 2026 CORE DATABASE SKILLS BLUEPRINT and more Exams Relational Database Management Systems (RDBMS) in PDF only on Docsity!
DATA MANAGEMENT FOUNDATIONS 2026
CORE DATABASE SKILLS BLUEPRINT
โ Database administrator (DBA). Answer: Secures the DB, manages access, and enforces availability procedures. โ Authorization. Answer: Limits user access to specific tables, columns, or rows. โ Business rules. Answer: Rules that keep data consistent with structural and policy constraints. โ Query processor. Answer: Parses queries, optimizes plans, and returns results. โ Storage manager. Answer: Executes low-level file operations and uses indexes to locate data. โ Transaction manager. Answer: Ensures ACID execution and recovery from failures. โ CRUD: INSERT. Answer: Adds new rows to a table.
โ CRUD: SELECT. Answer: Retrieves data from a table. โ CRUD: UPDATE. Answer: Modifies existing rows in a table. โ CRUD: DELETE. Answer: Removes rows from a table. โ CREATE TABLE. Answer: Defines a new table with columns and data types. โ DROP TABLE. Answer: Deletes a table and all its rows. โ ALTER TABLE. Answer: Adds, drops, or modifies columns or constraints of a table. โ INT. Answer: Data type for integers. โ DECIMAL(M,D). Answer: Data type for fixed-precision numbers. โ VARCHAR(N). Answer: Data type for variable-length text up to N chars. โ DATE. Answer: Data type for year, month, day.
โ DTL. Answer: Manages transactions. โ Literals. Answer: Explicit values: strings, numbers, binary. โ Identifiers. Answer: Names of DB objects (tables, columns, etc.). โ Comments. Answer: Statements ignored by the DB parser. โ Unary minus. Answer: Reverses the sign of a numeric value. โ Arithmetic operators. Answer: +, - , *, /, % (mod), ^ (power). โ Comparison operators. Answer: =, !=, <, <=, > for value comparison. โ BETWEEN. Answer: Tests inclusive range: value BETWEEN a AND b. โ LIKE. Answer: Pattern match using % (any chars) and _ (single char). โ ORDER BY. Answer: Sorts result rows; DESC reverses order.
โ Aggregate: COUNT. Answer: Counts rows in the set. โ Aggregate: MIN. Answer: Minimum value in the set. โ Aggregate: MAX. Answer: Maximum value in the set. โ Aggregate: SUM. Answer: Sums numeric values in the set. โ Aggregate: AVG. Answer: Mean of numeric values in the set. โ GROUP BY. Answer: Groups rows so aggregates compute per group. โ HAVING. Answer: Filters groups after GROUP BY. โ Join (definition). Answer: Combines rows from two tables in one result. โ INNER JOIN. Answer: Returns only matching rows from both tables.
โ Primary key. Answer: Uniquely identifies each row; stable, simple, meaningless. โ Composite primary key. Answer: Primary key made of multiple columns. โ Auto-increment column. Answer: Numeric column that auto- generates increasing values. โ Common PK mistakes. Answer: Inserting values into auto- increment PKs or omitting non-auto PKs. โ Foreign key. Answer: Column(s) referencing a primary key in another table. โ Foreign key constraint. Answer: Prevents operations that break referential integrity. โ RI action: RESTRICT. Answer: Rejects violating inserts/updates/deletes. โ RI action: SET NULL. Answer: Sets invalid foreign keys to NULL.
โ RI action: SET DEFAULT. Answer: Sets invalid foreign keys to the default. โ RI action: CASCADE. Answer: Propagates PK changes/deletes to FKs. โ Constraint. Answer: Rule enforced by the DB on allowable values. โ ALTER TABLE ... ADD/DROP constraint. Answer: Modifies constraints after table creation. โ TRUNCATE. Answer: Deletes all rows from a table efficiently. โ MERGE. Answer: Inserts/updates target table using a source result. โ Analysis phase. Answer: Requirements as entities, relationships, attributes; DB-agnostic. โ Logical design. Answer: Maps ER model to tables, keys, columns for a specific DB. โ Physical design. Answer: Indexes and storage organization; affects speed not results.
โ Supertype/Subtype. Answer: Model specialization; subtype IsA supertype. โ Partition (subtypes). Answer: Mutually exclusive subtypes of a supertype. โ Functional dependency. Answer: Column A depends on B if B determines A. โ Redundancy. Answer: Repetition of related values in a table. โ Candidate key. Answer: Unique and minimal column set; one chosen as primary key. โ Non-key column. Answer: Column not part of any candidate key. โ Third Normal Form. Answer: If non-key A depends on B, then B must be unique. โ Boyce-Codd Normal Form. Answer: If A depends on B, then B must be unique; stronger than 3NF.
โ Trivial dependency. Answer: A depends on B when A โ B. โ Normalization. Answer: Decompose tables to remove redundancy. โ Denormalization. Answer: Merge tables to reintroduce redundancy for performance. โ Table structure: heap. Answer: No row order; fastest bulk inserts. โ Table structure: sorted. Answer: Rows stored in order of a sort column. โ Table structure: hash. Answer: Rows assigned to buckets by a hash function. โ Table cluster. Answer: Interleaves related tables in same storage area. โ Table scan. Answer: Reads table blocks directly without using an index. โ Index scan. Answer: Traverses index to find relevant table blocks.
โ MySQL command-line client. Answer: Text interface that returns codes for SQL errors. โ Entity type. Answer: A set of similar things (e.g., all employees in a company). โ Relationship type. Answer: A set of related entity pairs (e.g., Employee-Manages-Department). โ Attribute type. Answer: A set of values (e.g., all salaries). โ Entity instance. Answer: A specific thing (e.g., employee Sam Snead). โ Relationship instance. Answer: A specific relation between entities (e.g., "Maria manages Sales"). โ Attribute instance. Answer: A specific value (e.g., salary $35,000). โ Cardinality maxima. Answer: Maximum number of entity instances that can participate in a relationship.
โ Cardinality minima. Answer: Minimum number of entity instances that can participate in a relationship. โ Crow's foot notation. Answer: Circle = 0, bar = 1, three lines = many; shows cardinality. โ Strong entity. Answer: Entity that can be uniquely identified without relying on another. โ Weak entity. Answer: Entity that depends on another for identification. โ Subtype entity. Answer: A subset of another entity type (supertype). โ Supertype entity. Answer: A broader type that can be specialized into subtypes. โ Partition of subtypes. Answer: Mutually exclusive subtype entities of a supertype. โ Intangible entity. Answer: Documented in ER model but not tracked with data in the DB.
โ RI action: SET DEFAULT. Answer: Sets invalid FK values to the default when PK changes/deletes. โ RI action: CASCADE. Answer: Propagates PK changes/deletes to related FKs. โ Column constraint. Answer: Rule applied at column level (e.g., NOT NULL, UNIQUE). โ Table constraint. Answer: Rule applied across multiple columns (e.g., PK, FK). โ ALTER TABLE ... ADD/DROP constraint. Answer: Used to add or drop constraints after table creation. โ TRUNCATE. Answer: Removes all rows from a table; faster than DELETE without WHERE. โ MERGE. Answer: Combines data from a source table into a target (insert/update). โ SQL function: ABS(n). Answer: Returns the absolute value of n.
โ SQL function: LOWER(s). Answer: Returns lowercase version of string s. โ SQL function: TRIM(s). Answer: Removes leading and trailing spaces from string s. โ SQL function: HOUR(t). Answer: Extracts the hour from a time value. โ SQL function: MINUTE(t). Answer: Extracts the minute from a time value. โ SQL function: SECOND(t). Answer: Extracts the second from a time value. โ API for SQL. Answer: Interfaces allowing general-purpose programming languages to run SQL. โ MySQL Command-Line Client. Answer: Text interface for running SQL; returns codes for errors and syntax issues. โ Database design phase: Analysis. Answer: Define requirements independent of any DBMS. Use entities, relationships, attributes.
โ Logical design step 7. Answer: Implement attributes as columns. โ Logical design step 8. Answer: Apply normal forms to reduce redundancy. โ BIGINT data type. Answer: 8-byte integer. Signed range: - 2^63 to 2^63 - 1. Unsigned range: 0 to 2^64 - 1. โ SQL operator: +. Answer: Adds two numeric values (e.g., 4 + 3 = 7). โ SQL operator: - (unary). Answer: Reverses the sign of a value (e.g.,
- (-2) = 2). โ SQL operator: - (binary). Answer: Subtracts one value from another (e.g., 11 - 5 = 6). โ SQL operator: *. Answer: Multiplies two values (e.g., 3 * 5 = 15). โ SQL operator: /. Answer: Divides one value by another (e.g., 4 / 2 = 2).
โ SQL operator: % (modulo). Answer: Returns remainder of division (e.g., 5 % 2 = 1). โ SQL operator: ^. Answer: Raises a number to a power (e.g., 5^2 = 25). โ SQL comparison operator: =. Answer: Tests equality (e.g., 1 = 2 โ FALSE). โ SQL comparison operator: !=. Answer: Tests inequality (e.g., 1 != 2 โ TRUE). โ SQL comparison operator: <. Answer: Tests less than (e.g., 2 < 2 โ FALSE). โ SQL comparison operator: <=. Answer: Tests less than or equal (e.g., 2 <= 2 โ TRUE). โ SQL comparison operator: >. Answer: Tests greater than (e.g., '2019- 08 - 13' > '2021- 08 - 13' โ FALSE). โ UPDATE statement. Answer: Modifies existing rows in a table. Uses SET clause and optional WHERE. Without WHERE, updates all rows.
