D330 Data Systems Administration - Set 1 - Part 3

Correct Answer: Foreign key values always reference existing primary key rows

Referential integrity ensures relationships remain valid: a foreign key must either match an existing primary key value or be NULL, preventing orphaned records.

Correct Answer: Deleting a parent row automatically deletes related child rows

ON DELETE CASCADE automatically removes dependent child rows when the referenced parent row is deleted, maintaining referential integrity without manual cleanup.

Correct Answer: A marker indicating the absence of a value

NULL represents unknown or missing data. It is distinct from zero or an empty string and requires special handling with IS NULL rather than equality comparison.

Correct Answer: COUNT()

COUNT() returns the number of rows matching the query. SUM() totals values, AVG() computes the mean, and MAX() returns the largest value.

Correct Answer: Groups rows sharing a value so aggregates can be computed per group

GROUP BY collects rows with the same values in specified columns into groups, allowing aggregate functions such as COUNT and SUM to be applied to each group.

Correct Answer: HAVING filters groups after aggregation; WHERE filters rows before

WHERE filters individual rows before grouping, while HAVING filters the grouped results after aggregate functions have been applied.

Correct Answer: A primary key made up of two or more columns

A composite key uses a combination of two or more columns to uniquely identify a row, used when no single column is sufficient for uniqueness.

Correct Answer: Modeling entities, attributes, and relationships in a database design

An ERD visually models the entities in a system, their attributes, and the relationships between them, guiding logical and physical database design.

Correct Answer: DELETE removes selected rows and can be rolled back; TRUNCATE removes all rows quickly

DELETE removes rows individually with optional WHERE filtering and full logging, while TRUNCATE rapidly removes all rows with minimal logging and typically cannot be rolled back in the same way.

Correct Answer: Removes the table structure and all its data permanently

DROP TABLE permanently removes the table definition along with all its data, indexes, and constraints from the database.