Database Management Flashcards

In a code enforcement database, Role-Based Access Control (RBAC) primarily assigns permissions to which entity?

Answer: User roles, which are then assigned to users

RBAC grants permissions to roles that represent job functions, and those roles are then assigned to individual users for easier access management.

Which database concept describes storing the same piece of data in multiple places, risking inconsistency?

Answer: Data redundancy

Data redundancy occurs when the same data is stored in multiple locations, which can lead to inconsistencies when one copy is updated but another is not.

A city needs to migrate its permit database to a new server with zero data loss. Which approach is most appropriate?

Answer: Use database replication to sync, then cut over

Database replication continuously syncs data to the new server, allowing a precise cutover with minimal downtime and no data loss.

Which type of SQL JOIN returns all rows from both tables, with NULLs where no match exists on either side?

Answer: FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables, filling in NULLs where no corresponding row exists in the other table.

A code enforcement agency needs to ensure sensitive inspection data is unreadable if the database files are stolen. Which measure should be implemented?

Answer: Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) encrypts database files at rest so stolen physical storage media cannot be read without the encryption keys.

In an ICC permit management system, which database design approach stores historical snapshots of record changes over time?

Answer: Slowly Changing Dimension (SCD) Type 2

SCD Type 2 preserves historical data by adding new rows for each change rather than overwriting existing records, maintaining a full audit trail.

Which SQL command removes all rows from a table quickly without logging individual row deletions and without removing the table structure?

Answer: TRUNCATE TABLE

TRUNCATE TABLE removes all rows in bulk with minimal logging and resets any identity counters, but leaves the table structure intact.