DataSys+ Practice Questions
Prepare for DS0-001 with more than an answer.
- Exam fee
- $390 USD
- Level
- Intermediate
- Valid for
- 3 years
Domains covered on the exam 5
- Database Fundamentals24%
- Database Deployment16%
- Database Management and Maintenance25%
- Data and Database Security23%
- Business Continuity12%
- 1
A database administrator needs to create a permanent, virtual table based on a complex query that joins several large tables. This virtual table will be used by the business intelligence team for generating daily reports. The data in the underlying tables changes infrequently (only during a nightly batch process), but the reporting queries need to be as fast as possible. Which database object should the administrator create?
Show answer details
Correct answer: D
A materialized view is the ideal solution. Unlike a standard view, which re-executes the underlying query every time it's accessed, a materialized view physically stores the result set of the query. This makes querying the materialized view extremely fast, as it's like querying a regular table. Since the underlying data only changes nightly, the materialized view can be refreshed once after the batch process completes, ensuring the BI team has fast access to up-to-date data for their reports.
- 2
When designing a relational database schema, the process of organizing columns and tables to minimize data redundancy is known as ________.
Show answer details
Correct answer: B
Normalization is the formal process in relational database design of organizing tables to reduce data redundancy and improve data integrity. It involves dividing larger tables into smaller, well-structured tables and defining relationships between them. The goal is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
- 3
True or False: In a Platform as a Service (PaaS) cloud model for databases, the customer is responsible for managing operating system patches and database software updates.
Show answer details
Correct answer: B
The statement is false. In a PaaS model, the cloud provider manages the underlying infrastructure, including the physical hardware, networking, operating system, and the database software itself. The customer is responsible for their own data, applications, and configuring the database service, but the core patching and updating of the OS and database engine are handled by the provider. This is a key benefit of PaaS.
- 4
A hospital needs to store and query electronic health records (EHR). The data must be stored in a structured format, and all transactions (e.g., updating a patient's allergy information) must strictly adhere to ACID principles to ensure data integrity and patient safety. Additionally, complex reports joining patient, visit, and billing information are required. Which database model is the most appropriate choice?
Show answer details
Correct answer: A
A relational database model is the most suitable choice for this scenario. The requirements for structured data, strict ACID compliance for transactional integrity, and the need for complex joins across multiple entities (patients, visits, billing) are all core strengths of relational database management systems (RDBMS) like SQL Server, PostgreSQL, or Oracle.
- 5
A database is experiencing severe blocking issues during peak business hours. The DBA identifies a long-running reporting query that is placing shared locks on several large tables, preventing concurrent write operations. The business cannot tolerate the reports being inaccurate or out-of-date. Which transaction isolation level should be set for the reporting query's connection to prevent blocking while ensuring data accuracy?
Show answer details
Correct answer: C
Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation are the best solutions. In these row-versioning based isolation levels, reading transactions do not take shared locks on data. Instead, they read a transactionally consistent snapshot of the data as it existed at the start of the transaction. This means the reporting query can run without blocking the write operations, and the write operations won't block the report. The report gets an accurate, consistent view of the data without causing concurrency problems.
- 6
Over the weekend, a company’s transaction database was moved to an upgraded server. All validations performed after the migration indicated that the database was functioning as expected. However, on Monday morning, multiple users reported that the corporate reporting application was not working.Which of the following are the most likely causes? (Choose two.) A.The access permissions for the service account used by the reporting application were not changed.B.The new database server has its own reporting system, so the old one is not needed.C.The reporting jobs that could not process during the database migration have locked the application.D.The reporting application’s mapping to the database location was not updated.E.The database server is not permitted to fulfill requests from a reporting application.F.The reporting application cannot keep up with the new, faster response from the database.
Show answer details
Correct answer: A, D
- 7
A DBA is reviewing the following logs to determine the current data backup plan for a primary data server:Which of the following best describes this backup plan? A.Monthly full, daily differentialB.Daily differentialC.Daily fullD.Weekly full, daily incremental
Show answer details
Correct answer: D
- 8
A database administrator needs to ensure that a newly installed corporate business intelligence application can access the company’s transactional data. Which of the following tasks should the administrator perform first? A.Create a new service account exclusively for the business intelligence application.B.Build a separate data warehouse customized to the business intelligence application's specifications.C.Set up a nightly FTP data transfer from the database server to the business intelligence application server.D.Send the business intelligence administrator the approved TNS names file to configure the data mapping.E.Open a new port on the database server exclusively for the business intelligence application.
Show answer details
Correct answer: A
- 9
A financial services company is deploying a new PostgreSQL database cluster for its high-frequency trading application. The primary requirements are zero data loss (RPO=0) upon primary node failure and automatic failover within 30 seconds (RTO<30s). The solution must also allow read-only queries to be distributed across secondary nodes. Which deployment architecture should the database architect implement?
Show answer details
Correct answer: C
This architecture meets all requirements. Synchronous streaming replication ensures that a transaction is not considered committed until it has been written to both the primary and the synchronous standby, guaranteeing an RPO of zero. High-availability management tools like Patroni or Stolon use a distributed consensus store (like etcd or Consul) to manage the cluster state, handle leader election, and trigger automatic failover well within the 30-second RTO. They also facilitate routing read queries to the standby nodes.
- 10
A database administrator is tasked with migrating an on-premises 10TB data warehouse from a legacy system to a cloud platform. A key requirement is to minimize the initial migration downtime. The on-premises network has a stable but limited upload bandwidth of 1 Gbps. Which data transfer method should be used for the initial bulk data load?
Show answer details
Correct answer: B
Transferring 10TB over a 1 Gbps connection would take approximately 24 hours, leading to significant downtime. Offline data transfer appliance services (like AWS Snowball or Azure Data Box) are designed for this exact scenario. The company can load the 10TB of data onto a physical appliance, ship it to the cloud provider, and have the data loaded directly into their cloud environment. This method is much faster for the initial bulk load than a network transfer over a limited connection, thus minimizing the initial migration downtime. Subsequent changes can be synchronized over the network.
