Database Administration Systems Flashcards

Question 1

What is a database management system, and what does it do?

Accepted Answer

A database management system, or DBMS, is software that lets users and applications create, store, retrieve, update, and control access to data in a database. It sits between the stored data and the programs that use it, handling storage details, concurrency, and integrity rules so that applications do not have to manage files directly.

Question 2

What is the difference between a database and a database management system?

Accepted Answer

A database is the organized collection of related data itself, while a database management system is the software that creates, manages, and accesses that collection. People often use the terms loosely, but the distinction matters when discussing licensing, installation, and administration tasks, since administrators work with the DBMS to look after the database.

Question 3

What is a relational database, and how are its data organized?

Accepted Answer

A relational database stores data in tables, where rows represent records and columns represent attributes. Tables relate through shared key values, such as a customer identifier appearing in both a customers table and an orders table. SQL is the standard language used to define, query, and modify this data.

Question 4

What role does a database administrator play within an organization?

Accepted Answer

A database administrator, or DBA, is responsible for keeping database systems available, secure, reliable, and performing well. Typical duties include installing and configuring the DBMS, creating and managing user accounts, planning backups, tuning performance, and coordinating with developers and system administrators. The role balances technical maintenance with planning for growth and change.

Question 5

What is a schema, and why do administrators care about it?

Accepted Answer

A schema is the formal description of a database's structure, including its tables, columns, data types, keys, and constraints. Administrators care about schemas because the structure determines how data can be stored, how queries perform, and how changes must be planned. Changes on a live system need careful scheduling and testing.

Question 6

What is a primary key, and what property must it have?

Accepted Answer

A primary key is a column or set of columns that uniquely identifies each row in a table. Its values must be unique and cannot be null, which allows the DBMS and other tables to refer to a specific record reliably. Foreign keys in other tables reference these values.

Question 7

What does ACID stand for, and why does it matter for transactions?

Accepted Answer

ACID stands for atomicity, consistency, isolation, and durability, the four properties that reliable transactions guarantee. Atomicity means a transaction completes fully or not at all, consistency means data moves between valid states, isolation keeps concurrent transactions from interfering, and durability ensures committed changes survive failures. Administrators depend on these guarantees for financial records.

Question 8

What is normalization, and what problem does it solve?

Accepted Answer

Normalization is the process of organizing tables and columns to reduce redundancy and avoid anomalies during inserts, updates, and deletes. By separating data into related tables according to defined normal forms, each fact is stored in one place. Poorly normalized designs can cause inconsistent copies of the same information, which is hard to correct.

Question 9

What should an administrator check before installing a database server?

Accepted Answer

Before installing a database server, an administrator should confirm that hardware and the operating system meet the vendor's requirements, including processor, memory, storage, and supported versions. They should also plan storage layout, network access, time synchronization, and the service account. Skipping this planning can lead to performance problems or rework.

Question 10

Why is it useful to separate the database software from the data files?

Accepted Answer

Keeping the database software on separate storage from the data files makes upgrades, patches, and reinstallations simpler, because the data can remain untouched. It allows administrators to size and protect data volumes independently, and to place them on storage suited to their access patterns. This reduces the risk of accidental data loss during maintenance.

Question 11

What is a configuration parameter, and how do administrators typically set one?

Accepted Answer

A configuration parameter is a setting that controls how a database system behaves, such as memory allocation, connection limits, logging, or file locations. Administrators usually set parameters through a configuration file or a management command, and many parameters take effect only after a restart. Changes should be recorded and tested in a nonproduction environment.

Question 12

What is a database instance, and how does it differ from a database?

Accepted Answer

A database instance is the running set of background processes and memory structures that manages one or more databases. The instance can be started and stopped, while the database is the stored files that hold the data. Stopping the instance does not delete the files, so the data remains on disk for later use.

Question 13

Why should administrators create dedicated accounts for applications instead of sharing one login?

Accepted Answer

Dedicated accounts give each application only the privileges it needs, which limits damage if one application is compromised or misbehaves. Separate accounts also make it possible to audit which application performed a given action. Shared logins hide that trail, make password changes disruptive, and tempt developers to grant broader access than necessary.

Question 14

What is a character set and collation, and why must they be chosen carefully?

Accepted Answer

A character set defines which characters can be stored, such as Latin letters, accented characters, or emoji, while a collation defines how those characters are compared and sorted. Choosing them carefully at setup matters because changing them later can be difficult and may require converting existing data. Mismatched client settings can cause garbled text.

Question 15

What is a connection pool, and what problem does it address?

Accepted Answer

A connection pool is a group of reusable database connections that applications borrow and return rather than opening a new connection for every request. Opening connections is expensive in time and memory, so pooling reduces overhead. Pool size needs careful tuning, since too many connections can overwhelm the server.

Question 16

What is a rollback, and when would an administrator use one?

Accepted Answer

A rollback undoes the changes made by an uncommitted transaction, returning the data to the state it had before the transaction began. Rollbacks are used when an operation fails partway through or a change proves incorrect before commit. Once a transaction is committed, reversing it requires restoring data or running compensating changes instead.

Question 17

What is the principle of least privilege in database security?

Accepted Answer

The principle of least privilege means that every user, role, or application receives only the permissions needed to perform its assigned tasks. Applying it reduces the damage a compromised account can cause and limits accidental changes to sensitive data. Administrators review granted permissions regularly, because access needs change as people move between roles.

Question 18

What is the difference between authentication and authorization?

Accepted Answer

Authentication verifies who a user or application is, typically through a password, certificate, or other credential. Authorization decides what that verified identity is allowed to do, such as reading a table or granting privileges to others. A user can pass authentication and still be denied access by authorization rules.

Question 19

What are database roles, and why are they useful?

Accepted Answer

A database role is a named group of privileges that can be granted to users or to other roles. Roles simplify administration because permissions are assigned to the role, then the role is given to each person who needs them. When someone changes jobs, administrators can swap roles instead of editing many permissions.

Question 20

What is the purpose of granting privileges with the GRANT and REVOKE statements?

Accepted Answer

GRANT gives a user or role specific privileges on database objects, such as permission to select, insert, update, or delete rows in a table. REVOKE removes privileges already granted, which is how administrators correct excess access or respond to a security concern. Together they provide an auditable way to manage permissions through SQL.

Question 21

Why is encryption used for data both at rest and in transit?

Accepted Answer

Encryption at rest protects stored data on disks and backups, so a stolen drive or copied file cannot be read without the keys. Encryption in transit protects data moving between clients, applications, and servers, so someone intercepting network traffic cannot read it. Using both covers the main points where data can be exposed.

Question 22

What is SQL injection, and how can developers reduce its risk?

Accepted Answer

SQL injection is an attack in which malicious input changes the structure of a query, allowing an attacker to read, modify, or delete data they should not access. Developers reduce the risk by using parameterized queries or prepared statements, validating input, and avoiding string concatenation when building SQL. Limiting account privileges also reduces harm.

Question 23

What is auditing in database administration, and what should it record?

Accepted Answer

Auditing is the practice of recording selected database activity, such as logins, privilege changes, schema modifications, and access to sensitive tables. Useful audit records identify who performed an action, what was done, when it occurred, and from where. Audit logs should be protected from tampering and reviewed, since they support investigations and compliance checks.

Question 24

Why should default accounts and sample databases be removed or secured after installation?

Accepted Answer

Default accounts often have well-known names and passwords, and sample databases may contain unnecessary features or exposed components, so attackers commonly check for them. Removing or securing these items shrinks the attack surface before a system goes live. Administrators should also change default passwords and disable accounts that the environment does not need.

Question 25

What is a backup, and why is it essential for databases?

Accepted Answer

A backup is a copy of database data and the information needed to restore it, kept separately from the live system. Backups are essential because hardware failures, software bugs, human errors, and malicious activity can damage or destroy data. Without a usable backup, recovery may be limited to whatever survives on the damaged system.

Question 26

What is the difference between a full backup and an incremental backup?

Accepted Answer

A full backup copies the entire database at one point in time, giving a complete, self-contained restore set. An incremental backup copies only the data changed since the last backup of any type, which saves time and storage. Restoring from incrementals requires the last full backup plus every incremental taken afterward, in order.

Question 27

What is a recovery point objective, and how does it differ from a recovery time objective?

Accepted Answer

A recovery point objective, or RPO, is the maximum amount of data loss that an organization can tolerate. A recovery time objective, or RTO, is the maximum acceptable time to restore service after an outage. RPO concerns how much recent data may be lost, while RTO concerns how long the system may be unavailable.

Question 28

Why must backups be tested by performing restores?

Accepted Answer

A backup that has never been restored is only a hope, because corruption, missing files, or incompatible software versions often appear only at restore time. Regular test restores confirm that the backup set is complete, readable, and usable within the recovery time objective. Test results also show whether the documented procedure is accurate.

Question 29

What is point-in-time recovery, and what does it require?

Accepted Answer

Point-in-time recovery restores a database to the state it had at a chosen moment, such as just before an accidental deletion. It requires a full backup taken earlier, plus transaction logs covering changes up to the target time. Without those logs, recovery can only return to the last backup.

Question 30

What should a backup plan include beyond the backup schedule itself?

Accepted Answer

A backup plan should define retention periods, storage locations, encryption requirements, and who is authorized to start or approve restores. It should specify off-site or separate copies so that a single disaster cannot destroy both the live data and every backup. Documentation of procedures and contacts keeps recovery from depending on one person's memory.

Question 31

What is a hot backup, and how does it differ from a cold backup?

Accepted Answer

A hot backup is taken while the database remains online and accepting activity, usually with the help of the DBMS to keep the copy consistent. A cold backup is taken after the database has been shut down, so no changes occur during the copy. Hot backups reduce downtime, while cold backups need a maintenance window.

Question 32

Why should an administrator document a restore procedure before an incident occurs?

Accepted Answer

A documented restore procedure lets staff act quickly and consistently during a stressful outage, when memory of steps is unreliable. It records exact commands, required credentials, file locations, and the order of steps, which reduces the chance of mistakes such as restoring the wrong copy. It also helps new staff.

Question 33

What metrics do administrators commonly monitor to assess database performance?

Accepted Answer

Administrators commonly monitor query response time, throughput measured in transactions or queries per second, CPU and memory use, disk input and output rates, and the number of active connections. Lock waits, replication lag, and cache hit ratios can reveal bottlenecks. Trends matter more than single readings, since a gradual change signals a growing problem.

Question 34

What is an execution plan, and why do administrators examine it?

Accepted Answer

An execution plan is the sequence of steps the DBMS chooses to run a query, such as which indexes to use. Administrators examine plans to find why a query is slow, such as a full table scan. Plans can change as data grows, so review them periodically.

Question 35

What is an index, and what trade-off does it involve?

Accepted Answer

An index is a data structure that lets the DBMS locate rows matching a condition without scanning an entire table. Indexes speed up many reads, but each one must be updated when data changes, which slows inserts, updates, and deletes and consumes additional storage. Choosing indexes means balancing read speed against write cost.

Question 36

Why do administrators refresh table statistics regularly?

Accepted Answer

The query optimizer relies on table statistics, such as row counts and value distributions, to estimate how much work each candidate plan will require. When data changes, outdated statistics can lead the optimizer to choose poor plans, such as scanning a large table for a few rows. Refreshing statistics helps the optimizer decide well.

Question 37

What is index fragmentation, and how can administrators address it?

Accepted Answer

Index fragmentation occurs when index pages become disordered or sparsely filled after many inserts, updates, and deletes, which can slow reads and waste storage. Administrators can address it by rebuilding or reorganizing indexes, depending on the amount of fragmentation. Scheduling this work during low-activity periods reduces its impact on users.

Question 38

What is the purpose of a database maintenance window?

Accepted Answer

A maintenance window is a scheduled period when the database can be taken offline or run with reduced load so that disruptive tasks can be performed safely. Typical tasks include applying patches, rebuilding indexes, running integrity checks, and upgrading software. Agreeing on windows with stakeholders in advance avoids surprises and helps set realistic expectations.

Question 39

Why is it important to monitor disk space and log growth?

Accepted Answer

A database that runs out of disk space can stop accepting writes, which may halt applications and cause data integrity problems. Transaction logs and temporary files can grow quickly during heavy workloads or long-running transactions. Alerts set at sensible thresholds give administrators time to add capacity or clean up before an outage occurs.

Question 40

How do administrators decide which maintenance tasks to prioritize?

Accepted Answer

Administrators usually prioritize tasks by their impact on availability, data integrity, security, and performance, with the most serious risks handled first. Monitoring data and vendor guidance help show which issues are current and which are merely cosmetic. Documenting each decision creates a record that supports future planning and review.

Database Administration Systems Flashcards

1Core Database Concepts

2Installation and Configuration

3Security and Access Control

4Backup and Recovery

5Performance Monitoring and Maintenance

Learning Resources