Database Administration: Master Database Management for Optimal Performance

Question 1

To ensure database security, which of these is a recommended best practice?

Accepted Answer

Enforcing strong password policies and regular password changes

Answer

Allowing all users unlimited access

Answer

Storing sensitive data without encryption

Answer

Relying solely on backups for security

Question 2

Question:

Explain the principle of least privilege in database security. Provide a specific example of how it can be implemented in practice.

Accepted Answer

The principle of least privilege grants users only the permissions necessary to perform their tasks. This reduces the risk of unauthorized access to sensitive data.

Answer

The principle of least privilege requires that all users have the same level of access to all data.

Answer

The principle of least privilege is not applicable to database security.

Question 3

Which database design principle prioritizes minimizing data duplication while maintaining data accuracy?

Accepted Answer

Normalization

Answer

Denormalization

Answer

Aggregation

Answer

Data Warehousing

Question 4

Within a database management system, what is the primary function of a data dictionary?

Accepted Answer

Store and manage user-defined data types and constraints.

Answer

Centralize all data within the database.

Answer

Maintain data integrity and consistency.

Answer

Optimize database performance.

Question 5

In database administration, which activity focuses on identifying and addressing performance bottlenecks to optimize database efficiency?

Accepted Answer

Performance monitoring and tuning

Answer

Database backup and recovery

Answer

Database security and data integrity

Question 6

Which advantage is gained by creating indexes on database tables?

Accepted Answer

Quicker data retrieval due to fewer disk accesses

Answer

Enforcing unique constraints to ensure data integrity

Answer

Reducing storage space by eliminating duplicate data

Answer

Enhancing data security by encrypting sensitive information

Question 7

What is the main purpose of a database schema?

Accepted Answer

To define the structure and relationships of data within a database

Answer

To store actual data in the database

Answer

To control user access to the database

Answer

To document the database design process

Question 8

What is the distinction between a full database backup and an incremental backup?

Accepted Answer

A full backup captures all data in the database, while an incremental backup captures only changes since the last backup.

Answer

An incremental backup captures all data in the database, while a full backup captures only changes since the last backup.

Answer

There is no difference between a full backup and an incremental backup.

Answer

A full backup is faster than an incremental backup.

Question 9

Which metric is commonly used to measure the responsiveness of a database system to user queries?

Accepted Answer

Query response time

Answer

Database throughput

Answer

Disk space utilization

Answer

CPU utilization

Question 10

What is the primary purpose of a database log?

Accepted Answer

To record and track changes made to the database

Answer

To store historical data for analysis

Answer

To provide a backup of the database in case of data loss

Answer

To enhance the performance of database queries

Question 11

What is a database trigger and its primary function?

Accepted Answer

A database trigger is a stored procedure that executes automatically when specific events occur in the database.

Answer

A database trigger is a function that returns a value based on input data.

Answer

A database trigger is a constraint that enforces data integrity.

Answer

A database trigger is an index that speeds up query performance.

Question 12

What is the primary goal of database normalization?

Accepted Answer

To eliminate data redundancy, ensure data integrity, and improve database efficiency

Answer

To increase database performance by reducing the size of the database

Answer

To make it easier to design and maintain the database

Answer

To enhance the flexibility and scalability of the database

Question 13

Core Responsibility of a Database Administrator

Which of the following is NOT a primary responsibility of a database administrator?

Accepted Answer

Application software development

Answer

Database creation

Answer

Performance monitoring

Answer

Backup and recovery

Question 14

Database Performance Monitoring

Which tool is typically used to monitor and optimize database performance?

Accepted Answer

Performance Monitor

Answer

Query Optimizer

Answer

Backup Utility

Answer

Recovery Manager

Question 15

Purpose of Database Indexes

What is the primary purpose of creating an index on a database table?

Accepted Answer

To improve query performance

Answer

To enforce data integrity

Answer

To reduce storage space

Answer

To facilitate data backup

Question 16

Database Backup Types

Which of the following is NOT a type of database backup?

Accepted Answer

Partial backup

Answer

Full backup

Answer

Incremental backup

Answer

Differential backup

Question 17

Difference Between Primary Key and Foreign Key

What is the key difference between a primary key and a foreign key?

Accepted Answer

A primary key identifies a unique row, while a foreign key references a primary key in another table

Answer

A foreign key identifies a unique row, while a primary key references a foreign key in another table

Answer

Both keys are used to enforce data integrity

Answer

There is no difference between a primary key and a foreign key

Question 18

PostgreSQL Database Creation

Which command is used to create a new database in PostgreSQL?

Accepted Answer

CREATE DATABASE

Answer

NEW DATABASE

Answer

ADD DATABASE

Answer

MAKE DATABASE

Question 19

Purpose of Transaction Log

What is the main purpose of a transaction log in a database management system?

Accepted Answer

To record changes made to the database, enabling recovery in case of failures

Answer

To provide a complete backup of the database

Answer

To enhance query performance

Answer

To enforce data integrity

Question 20

Database Schema Definition

What is the primary purpose of a database schema?

Accepted Answer

To define the structure, constraints, and relationships within a database

Answer

To store data in the database

Answer

To manage user access to the database

Answer

To optimize query performance

Question 21

Which of the following is **not** a primary responsibility of a database administrator (DBA)?

Accepted Answer

Software design and development

Answer

Database creation and maintenance

Answer

Backup and recovery management

Answer

Performance monitoring and optimization

Question 22

What is the primary function of a transaction log in a database management system?

Accepted Answer

To temporarily store data changes before they are committed to the database.

Answer

To record all database operations for audit and compliance purposes.

Answer

To serve as a backup copy of the database in case of data loss.

Answer

To enhance database performance by reducing the number of disk writes.

Question 23

Which type of database backup captures only the changes made to the database since the last backup?

Accepted Answer

Incremental backup

Answer

Full backup

Answer

Differential backup

Answer

Online backup

Question 24

What is the primary purpose of database normalization?

Accepted Answer

To eliminate data redundancy, improve data integrity, and enhance data consistency.

Answer

To increase database size and improve performance.

Answer

To simplify database design and reduce complexity.

Answer

To facilitate efficient data retrieval and processing.

Question 25

What is the primary role of a database recovery manager in a database system?

Accepted Answer

To restore the database to a consistent state after a system failure or data loss.

Answer

To manage user access and permissions to the database.

Answer

To monitor database performance and identify potential bottlenecks.

Answer

To create and maintain database backups.

Question 26

What is the primary objective of performance monitoring in a database system?

Accepted Answer

To identify and resolve performance bottlenecks and improve overall system efficiency.

Answer

To ensure continuous database availability and reliability.

Answer

To predict future database requirements and plan for future growth.

Answer

To optimize database design by identifying areas for improvement.

Question 27

What is the term for the process of creating a physical copy of a database for the purpose of disaster recovery?

Accepted Answer

Database backup

Answer

Database mirroring

Answer

Database replication

Answer

Database archiving

Question 28

Which of the following is not a primary responsibility of a database administrator?

Accepted Answer

Software development

Answer

Database creation and maintenance

Answer

Data backup and recovery

Answer

Performance monitoring and optimization

Question 29

What is the primary purpose of a transaction log in a database system?

Accepted Answer

To record data modifications for recovery purposes

Answer

To create periodic backups of the database

Answer

To enhance database performance

Answer

To manage user access and permissions

Question 30

Which backup strategy involves creating multiple copies of the database at specific intervals?

Accepted Answer

Incremental backup

Answer

Full backup

Answer

Differential backup

Answer

Log backup

Question 31

Which metric is commonly used to measure the responsiveness of a database system?

Accepted Answer

Response time

Answer

Throughput

Answer

Memory usage

Answer

Disk I/O

Question 32

Which command is used to restore a database from a backup file in PostgreSQL?

Accepted Answer

pg_restore

Answer

RESTORE DATABASE

Answer

IMPORT DATABASE

Answer

RECOVER DATABASE

Question 33

What security measure involves encrypting data before storing it in the database?

Accepted Answer

Encryption

Answer

Authentication

Answer

Authorization

Answer

Data masking

Question 34

To minimize the risk of data loss and maintain data integrity, which of the following actions is essential?

Accepted Answer

Regular data backups

Answer

Monthly backups only

Answer

No data backups

Question 35

Which of these is **least** important when creating a database disaster recovery plan?

Accepted Answer

Database performance

Answer

Recovery Point Objective (RPO)

Answer

Recovery Time Objective (RTO)

Answer

Data backup strategy

Question 36

Which strategies are commonly used to optimize database queries and reduce their execution time during performance tuning?

Accepted Answer

Optimizing indexes

Accepted Answer

Simplifying query structure

Answer

Upgrading hardware

Answer

Denormalizing data

Question 37

What is the primary purpose of transaction logs in ensuring data integrity and durability in database systems?

Accepted Answer

Recording database modifications for recovery and data consistency

Answer

Enhancing data retrieval performance

Answer

Required only for large databases with high transaction volumes

Answer

Not necessary in NoSQL databases due to alternative data integrity mechanisms

Question 38

In database management, what is the main purpose of a database schema?

Accepted Answer

Defines data types and relationships between data elements.

Answer

Stores the actual data in the database.

Answer

Controls user permissions and access privileges.

Question 39

Which ethical concern is paramount for database administrators?

Accepted Answer

Protecting data from unauthorized access

Answer

Ensuring data accuracy

Answer

Performing regular data backups

Answer

Optimizing database performance

Question 40

Which of the following is NOT a technique for managing data quality in database systems?

Accepted Answer

Data mining

Answer

Data validation

Answer

Data cleansing

Answer

Data standardization

Question 41

What is the primary purpose of database concurrency control, and how does it contribute to maintaining data integrity and preventing conflicts when multiple users access the database simultaneously?

Accepted Answer

Ensures data integrity by maintaining database consistency during concurrent updates.

Answer

Prevents conflicts by granting exclusive access to a single user at a time.

Answer

Enhances database performance by optimizing data retrieval and update operations.

Question 42

Which technique supports multiple languages and their cultural conventions (locales) in database systems?

Accepted Answer

Unicode

Answer

N-tier architecture

Answer

Data replication

Answer

Language-specific databases

Question 43

How does data partitioning enhance performance and scalability in cloud database systems?

Accepted Answer

Allows parallel query processing across multiple servers, reducing execution time.

Answer

Eliminates the need for indexing, reducing overhead and improving query speed.

Answer

Only benefits small databases, as larger databases require excessive partitioning.

Answer

Requires significant upfront effort and may not provide noticeable performance gains.

Question 44

Which of the following is a unique challenge associated with cloud-based database services?

Accepted Answer

Vendor lock-in

Answer

Cost-effectiveness

Answer

Scalability

Answer

Reliability

Question 45

Which of the following is the primary benefit of regular database maintenance?

Accepted Answer

Ensuring database performance and data integrity

Answer

Enhancing database security and data privacy

Answer

Facilitating database data analytics and reporting

Question 46

In the context of data warehouses, which of the following data integration approaches is commonly employed?

Accepted Answer

Extract, Transform, Load (ETL)

Answer

Online Analytical Processing (OLAP)

Answer

Object-Relational Mapping (ORM)

Answer

Entity-Relationship Modeling (ERM)

Question 47

Which of the following is NOT a popular database management system (DBMS)?

Accepted Answer

Android Studio

Answer

PostgreSQL

Answer

Oracle

Answer

Microsoft SQL Server

Question 48

What is the primary function of a database administrator (DBA)?

Accepted Answer

Design, implement, and maintain database systems.

Answer

Write SQL queries to retrieve data.

Answer

Develop software applications that interact with databases.

Answer

Manage and optimize database performance.

Question 49

Which of the following backup techniques ensures that the database remains online during the backup process?

Accepted Answer

Hot backup

Answer

Cold backup

Answer

Incremental backup

Answer

Differential backup

Question 50

What is the purpose of an index in a relational database management system?

Accepted Answer

Faster data retrieval by reducing search time.

Answer

Enforce referential integrity by ensuring unique values.

Answer

Reduce database size by storing only essential data.

Answer

Provide a visual representation of the database structure.

Question 51

Which SQL command is used to create a new table in a database?

Accepted Answer

CREATE TABLE

Answer

ADD TABLE

Answer

NEW TABLE

Answer

GENERATE TABLE

Question 52

What is the distinction between a primary key and a foreign key in a relational database?

Accepted Answer

A primary key uniquely identifies each record, and a foreign key references a primary key in another table.

Answer

A foreign key uniquely identifies each record, and a primary key references a foreign key in another table.

Answer

There is no difference between a primary key and a foreign key.

Answer

A primary key can have null values, while a foreign key cannot.

Question 53

Which of the following normalization forms ensures that each attribute in a relation is directly dependent on the primary key?

Accepted Answer

Boyce-Codd normal form (BCNF)

Answer

First normal form (1NF)

Answer

Second normal form (2NF)

Answer

Third normal form (3NF)

Question 54

What is the fundamental purpose of a database backup?

Accepted Answer

To safeguard the database against data loss in case of system failures

Answer

To enhance database performance

Answer

To permanently store historical data

Answer

To reduce the size of the database

Question 55

What is the primary function of an index in a database system?

Accepted Answer

To enhance query execution speed

Answer

To store duplicate data records

Answer

To reduce the database's size

Answer

To maintain referential integrity

Question 56

Which database model follows a hierarchical data structure?

Accepted Answer

Hierarchical

Answer

Relational

Answer

Network

Answer

Object-oriented

Question 57

Explain the distinction between a primary key and a foreign key.

Accepted Answer

A primary key uniquely identifies a row, while a foreign key references the primary key in another table

Answer

A foreign key uniquely identifies a row, while a primary key references the foreign key in another table

Answer

Both primary keys and foreign keys uniquely identify rows

Answer

There is no distinction between a primary key and a foreign key

Question 58

Which database management system is renowned for its support of distributed and scalable applications?

Accepted Answer

Oracle Database

Answer

MySQL

Answer

Microsoft SQL Server

Answer

PostgreSQL

Question 59

What is the primary objective of database normalization?

Accepted Answer

Eliminating data redundancy and inconsistencies

Answer

Improving database performance

Answer

Reducing database size

Answer

Facilitating data backup and recovery

Question 60

Explain the difference between logical and physical database design.

Accepted Answer

Logical design focuses on data representation, while physical design focuses on data storage implementation

Answer

Physical design focuses on data representation, while logical design focuses on data storage implementation

Answer

There is no difference between logical and physical database design

Answer

Logical design is more detailed than physical design

Question 61

What is the most common cause of data integrity problems in database systems?

Accepted Answer

Human mistakes

Answer

Hardware failures

Answer

Software bugs

Question 62

When resolving a database performance issue, you suspect data fragmentation is the cause. Which action directly addresses this issue?

Accepted Answer

Identify and defragment fragmented tables.

Answer

Increase the database server's RAM.

Answer

Rebuild all database indexes.

Answer

Increase the database buffer pool size.

Question 63

Which of the following is a core responsibility of a database administrator?

Accepted Answer

Maintaining database integrity, performance, and security

Answer

Managing operating systems

Answer

Designing database models

Answer

Developing software applications

Question 64

Which tool category is primarily used for database structure management?

Accepted Answer

Database Management Systems (DBMS) with SQL support

Answer

Text editors

Answer

Spreadsheets