DS0-001 Practice Questions

Protecting physical database appliances from damage within a server room primarily involves mitigating environmental threats. Overheating is a significant risk to electronic components, and cooling systems (e.g., HVAC, CRAC units) are essential for maintaining an optimal operating temperature, thereby preventing thermal damage and ensuring hardware longevity. Fire is another catastrophic threat; fire suppression systems (such as clean agent or pre-action sprinkler systems) are designed to extinguish fires quickly without damaging the electronic equipment, directly protecting the appliances from destruction.

A. Biometric access systems: This is a physical access control measure to prevent unauthorized personnel entry, not to protect against environmental damage like fire or heat.

B. Database control systems: These are software-based tools for managing database access and operations (logical security), which do not protect the physical hardware appliance.

D. Camera systems: Cameras are a detective and deterrent control for surveillance purposes; they record events but do not actively prevent physical damage from environmental hazards.

E. Key card systems: Like biometrics, this is a physical access control mechanism that restricts entry but offers no protection against environmental threats such as overheating.

1. National Institute of Standards and Technology (NIST). (2020). Security and Privacy Controls for Information Systems and Organizations (Special Publication 800-53

Revision 5).

Control PE-13 (Fire Protection): This control explicitly details the requirement for fire protection measures

including detection and suppression systems

to "prevent and mitigate damage to the system and facility." (Page 298).

Control PE-14 (Temperature and Humidity Controls): This control mandates maintaining temperature and humidity levels "within acceptable levels for the system" to prevent physical damage from environmental conditions. (Page 299).

DOI: https://doi.org/10.6028/NIST.SP.800-53r5

2. Massachusetts Institute of Technology (MIT) Information Systems and Technology (IS&T). (n.d.). Secure Hosting Environment.

The documentation for MIT's secure data center environment explicitly lists "Temperature and humidity controlled environment" and "Fire detection and suppression systems" as key features for protecting hosted equipment. This demonstrates the university-level best practice for physical hardware protection. (Section: "Features").

3. Stanford University IT. (n.d.). Data Center Services.

Stanford's description of its data center facilities highlights "Environmental Controls" as a core service

which includes "temperature and humidity monitoring" and "fire suppression systems" to ensure the physical safety and operational integrity of server hardware. (Section: "Features").

An Entity-Relationship Diagram (ERD) is a specialized, structured diagram used in database design to model entities, their attributes, and the relationships between them. A UML (Unified Modeling Language) tool is the most appropriate choice as it is specifically designed for creating and managing such formal diagrams. These tools provide the correct notations (e.g., for entities, relationships, cardinality) and often include features for validation, version control, and even generating SQL Data Definition Language (DDL) from the model. This makes them far superior to general-purpose applications for this specific database administration task.

A. Word processor: A word processor is designed for text documents and lacks the specialized features, constraints, and notations needed for accurate and efficient ERD creation.

B. Spreadsheet: A spreadsheet is optimized for organizing and calculating tabular data; it is not a diagramming tool and is unsuitable for modeling database structures.

D. HTML editor: An HTML editor is used for coding web pages. It has no functionality relevant to database design or creating visual schema models like ERDs.

---

1. Garcia-Molina

H.

Ullman

J. D.

& Widom

J. (2009). Database Systems: The Complete Book (2nd ed.). Pearson Prentice Hall. In Chapter 4

Section 4.5

"Unified Modeling Language

" the text discusses how UML

particularly its class diagrams

is a common and powerful tool used for database design

stating

"There are many other notations for database design... Perhaps the most common today are... UML..." (p. 148).

2. Elmasri

R.

& Navathe

S. B. (2017). Fundamentals of Database Systems (7th ed.). Pearson. Chapter 9

Section 9.1

discusses relational database design using ER-to-Relational mapping

a process best supported by CASE (Computer-Aided Software Engineering) tools. The text notes

"A variety of CASE tools are available for designing databases... These tools often use a variation of the ER or EER notation..." (p. 291). Modern UML tools are a primary category of such CASE tools.

3. Object Management Group. (2017). OMG Unified Modeling Language (OMG UML)

Version 2.5.1. OMG Document Number formal/2017-12-05. Section 11

"Structured Classifiers

" details the components of class diagrams

which are the UML equivalent used for modeling data structures like those in an ERD. This specification underpins the functionality of UML tools. (Available at https://www.omg.org/spec/UML/2.5.1/PDF)

In a typical database-deployment lifecycle, the first operational step is provisioning (creating the instance). The next phase is to bring that fresh instance to the latest supported software level, which is done through patch sets or minor-release updates. Vendor documentation groups this activity under “upgrading/patching,” where the DBA applies the most recent security and bug-fix patches immediately after creation. Therefore, the phase concerned with ensuring a new database has the newest patches is the upgrading phase.

A. Importing – Refers to loading data into an existing database; it does not change the database software level.

C. Provisioning – Creates and configures the initial database instance but intentionally precedes the patch/upgrade step.

D. Modifying – Pertains to changing database objects or configurations after deployment, not to applying software patches.

1. Oracle Corporation. “Patching and Upgrading Database Deployments

” Oracle Database Cloud Service Admin Guide 19c

Section 6.1

pp. 6-1–6-3.

2. Oracle Enterprise Manager 13c

Database Lifecycle Management Guide

Chap. 2 “Provisioning and Patching Overview

” para. “Upgrade/Patch Phase.”

3. PostgreSQL Global Development Group. “Minor Release Updates (Upgrading)

” PostgreSQL 15 Documentation

Sec. 18.6.

4. IBM. “Installing Fix Packs (Upgrading) for Db2 11.5

” IBM Knowledge Center

Tasks > Maintain > Upgrade

Steps 1-3.

5. Stanford University CS145 Course Notes

“Database Administration Lifecycle

” slide set “Deployment and Maintenance

” slide 17 (“Provision → Upgrade/Patch → Data Load”).

When Microsoft SQL Server is installed, it creates several system databases that are essential for its operation. Among the options provided, master and model are two of these default system databases. The master database contains all the system-level information for the SQL Server instance, including metadata about all other databases, login accounts, and system configuration settings. The model database serves as the template for any new user-defined databases created on the instance; its contents are copied to form the new database's initial structure and objects.

A. Root: 'Root' is not a standard database in SQL Server. The term typically refers to a superuser account or the top-level directory in a file system.

C. Log: A transaction log is a critical component (a file) of every database that records all transactions, but it is not a standalone database instance itself.

E. View: A view is a database object that represents a stored query as a virtual table. It is not a complete database.

F. Index: An index is a database object associated with a table or view that improves query performance. It is not a database.

1. Microsoft Corporation. (2023). System Databases - SQL Server. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/databases/system-databases. (This document explicitly lists and describes the system databases

including master and model.)

2. Microsoft Corporation. (2023). master Database - SQL Server. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/databases/master-database. (See the first paragraph: "The master database records all the system-level information for a SQL Server system.")

3. Microsoft Corporation. (2023). model Database - SQL Server. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/databases/model-database. (See the first paragraph: "The model database is used as the template for all databases created on an instance of SQL Server.")

The most appropriate and professional action is to adhere to the established backup retention policy. This policy is designed to balance the need for data recovery with storage costs and compliance requirements. By deleting only the backup files that are older than the period specified in the policy, the administrator ensures that all necessary recovery points are preserved while safely reclaiming disk space. This is a standard, repeatable, and defensible procedure for managing backup lifecycle and storage capacity.

A. Move all the backup files to external disks.

This only relocates the problem without addressing the lifecycle management of old backups. It is also a best practice to store backups on separate media from the start, not as a reaction.

B. Delete all the backup files containing data that is rated as classified.

This is dangerous and counterintuitive. Classified data is often the most critical to protect and recover, making its backups essential. Deleting them would likely violate security and compliance policies.

D. Delete all the backup files except for the most recent one.

This is extremely risky as it eliminates the ability for point-in-time recovery. If the most recent backup is corrupt or an issue went unnoticed before it was taken, all recovery options are lost.

1. Microsoft SQL Server Documentation: In the documentation for Backup and Restore of SQL Server Databases

maintenance plans are described. A key task is the "Maintenance Cleanup Task

" which "removes files related to maintenance plans

including database backup files... You can configure this task to delete files based on a specified age." This directly supports deleting files based on a retention policy (age).

Source: Microsoft Docs

"Maintenance Cleanup Task". Section: "Options".

2. Oracle Database Documentation: The Oracle Recovery Manager (RMAN) tool uses a configurable retention policy to determine which backups are no longer needed. The CONFIGURE RETENTION POLICY command sets the rules for obsolescence. The DELETE OBSOLETE command then removes backup files that are no longer required to meet this policy.

Source: Oracle Database Backup and Recovery User's Guide

19c. Chapter 10

"Configuring the RMAN Environment: Advanced Topics". Section: "Configuring a Backup Retention Policy".

3. PostgreSQL Documentation: The official documentation discusses strategies for backup and recovery

emphasizing the importance of a planned policy. It notes

"Whatever your backup and recovery plan is

test it... Consider how long you need to retain your backup archives." This highlights that retention duration is a core component of a proper backup strategy.

Source: PostgreSQL 14 Documentation. Chapter 26

"Backup and Restore". Section 26.1

"SQL Dump".

4. University Courseware: In database administration courses

backup and recovery strategies are a core topic. Course materials emphasize creating policies that define backup frequency

type

and retention period to ensure business continuity while managing storage resources effectively.

Source: Cornell University

CS 5320

"Introduction to Database Systems". Lecture notes on "Database Recovery

" which discuss the necessity of policies for managing archival logs and backups.

The SQL statement in option B, Select EmpId where EmpId=90030 and DeptId=34, contains a syntax error. A valid SELECT statement that retrieves data from a table must include a FROM clause to specify the source table. This statement is missing the FROM clause, making it syntactically incorrect and unexecutable by any standard SQL database management system. The basic structure of a query requires specifying what to select (SELECT), from where to select it (FROM), and optionally, the conditions for selection (WHERE).

A. This is a valid SQL query. It correctly uses the SELECT, FROM, and WHERE clauses to retrieve a specific employee's ID from the employee table.

C. This is a valid SQL query. The asterisk () is a wildcard that correctly selects all columns from the employee table for the specified EmpId.

D. This is a valid SQL query. It correctly uses the SELECT and FROM clauses to retrieve all values from the EmpId column in the employee table.

1. Ullman

J. D.

& Widom

J. (n.d.). CS145 Introduction to Databases

Class Notes on SQL. Stanford University. Retrieved from https://infolab.stanford.edu/~ullman/fcdb/slides/slides-sql1.pdf. On page 4

the basic form of an SQL query is defined as SELECT FROM WHERE

explicitly showing the FROM clause as a required component.

2. PostgreSQL Documentation. (n.d.). 7.1. Overview. In PostgreSQL 16 Documentation. Retrieved from https://www.postgresql.org/docs/current/tutorial-select.html. The introductory example SELECT FROM weather; and subsequent explanations establish the FROM clause as fundamental for specifying the target table.

3. Stonebraker

M.

& Hellerstein

J. (2005). 8. Relational Algebra and SQL. In 6.830 Database Systems

Fall 2005. Massachusetts Institute of Technology: MIT OpenCourseWare. Retrieved from https://ocw.mit.edu/courses/6-830-database-systems-fall-2005/resources/lec8/. Slide 20 presents the canonical SELECT-FROM-WHERE statement

reinforcing the necessity of the FROM clause.

The Durability property of ACID (Atomicity, Consistency, Isolation, Durability) ensures that once a transaction has been successfully committed, its changes are permanently recorded and will survive any subsequent system failure, such as a power outage or crash. The database management system guarantees that the results of committed transactions are written to non-volatile storage (e.g., a hard disk). This directly addresses the administrator's concern about the persistence of transactions in the event of a system failure.

B. Isolation: This property ensures that concurrent transactions do not interfere with each other, making them appear to execute serially. It addresses concurrency, not system failure recovery.

C. Atomicity: This property ensures that a transaction is an "all-or-nothing" operation. Either all of its operations are completed successfully, or none of them are. It prevents partial updates.

D. Consistency: This property ensures that a transaction brings the database from one valid state to another, preserving all predefined integrity constraints. It is about data correctness, not persistence after a crash.

1. Haerder

T.

& Reuter

A. (1983). Principles of Transaction-Oriented Database Recovery. ACM Computing Surveys

15(4)

287–317.

Page 290

Section 2.4: "Once a transaction is committed

its effects are permanent in the database... This property is called durability. The effects of a committed transaction must not be abrogated by any type of failure."

DOI: https://doi.org/10.1145/289.291

2. Halevy

A.

& Franklin

M. (2005). Transaction Processing. In P. A. Bernstein

U. Dayal

& D. B. Lomet (Eds.)

Principles of Transaction Processing for the Systems Professional (2nd ed.). Morgan Kaufmann.

Chapter 1

Section 1.2

"The ACID Properties": "Durability means that once a transaction commits

its effects are permanent. The changes must not be lost in a failure."

3. MIT OpenCourseWare. (2010). 6.830 Database Systems

Lecture 17: Crash Recovery. Massachusetts Institute of Technology.

Slide 4

"ACID Properties": Defines Durability as: "Effects of a committed transaction are permanent

must not be lost by a crash."

Available at: https://ocw.mit.edu/courses/6-830-database-systems-fall-2010/resources/mit6830f10lec17/

Object storage is a data storage architecture that manages data as distinct units called objects. It is designed to be accessed programmatically over a network using HTTP-based protocols. The primary interface for interacting with object storage systems, such as Amazon S3 or Azure Blob Storage, is a RESTful (Representational State Transfer) API. This allows users and applications to perform create, read, update, and delete (CRUD) operations on data objects using standard HTTP verbs (e.g., GET, PUT, POST, DELETE) directed at specific API endpoints, making it ideal for cloud-native applications.

A. Network file: This storage is accessed using network file-sharing protocols like NFS or SMB, which present a hierarchical file system, not a REST API for data retrieval.

C. Ephemeral: This describes the non-persistent nature of storage (it is lost when an instance stops), not its access method. It is typically a form of block storage.

D. iBlock: This refers to block storage, which is presented to an operating system as a raw disk volume and is accessed via protocols like iSCSI, not a REST API.

1. Amazon Web Services (AWS) Documentation: In the Amazon S3 User Guide

the section "How Amazon S3 works" explicitly states

"Amazon S3 is an object storage service... You can access your data through the Amazon S3 console

AWS SDKs

or the Amazon S3 REST API." This confirms that object storage uses a REST API.

Source: Amazon S3 User Guide

"What is Amazon S3? -> How Amazon S3 works".

2. Microsoft Azure Documentation: The documentation for Azure's object storage solution states

"Azure Blob storage is Microsoft's object storage solution for the cloud... Access objects from anywhere in the world via HTTP or HTTPS. Developers can use the Blob storage REST API or an Azure SDK to write code."

Source: Microsoft Azure Docs

"Introduction to Azure Blob storage -> Blob storage resources -> Blob storage REST API".

3. University of California

Berkeley Courseware: In the lecture notes for CS 162: Operating Systems and Systems Programming

the distinction between storage types is made clear. Object stores like S3 are described as having a "put/get/delete interface (REST API)

" contrasting with block storage (like EBS) and file storage (like NFS).

Source: UC Berkeley

EECS

CS 162

Lecture 22: "Cloud Computing"

Slide 34

"Cloud Storage: Object Stores".

4. Armbrust

M.

et al. (2010). A view of cloud computing. Communications of the ACM

53(4)

50-58. This foundational academic paper on cloud computing discusses storage systems

identifying services like Amazon S3 as providing "a bucket-based storage system with a REST API."

DOI: https://doi.org/10.1145/1721654.1721672

Section 3.1

Paragraph 3.