Back to CompTIA DataSys+ (DataSys+)

Database Deployment

Plan, design, implement, test, and deploy database systems with proper documentation and validation (16% of exam).

Encompasses gathering requirements, designing database architecture, and creating documentation like data dictionaries and entity relationship diagrams (ERDs). Covers validating schemas, performing stress tests, managing version control, and testing database connectivity. Includes installing, configuring, provisioning, and validating scalability during deployment phases.
5 minutes 5 Questions

In the context of CompTIA DataSys+, Database Deployment refers to the systematic process of releasing database changes, updates, or new instances into a target environment, typically moving from development to testing, staging, and finally production. The primary goal is to implement changes while …

Concepts covered: Database configuration, Deployment automation, Requirements gathering, Database architecture design, Data dictionaries, Entity Relationship Diagrams (ERDs), Normalization principles, Denormalization strategies, Schema design, Database sizing and capacity planning, Data modeling, Logical vs physical design, Schema validation, Database stress testing, Version control for databases, Database connectivity testing, Unit testing for databases, Integration testing, Data migration testing, Performance baseline testing, Database installation, Database provisioning, Scalability validation, Horizontal scaling, Vertical scaling, Blue-green deployments, Database rollback procedures

Test mode:
Start practice test
DataSys+ - Database Deployment Example Questions

Test your knowledge of Database Deployment

Question 1

A global manufacturing corporation is migrating their production scheduling database from SAP HANA 2.0 to Amazon Aurora PostgreSQL, transferring 11.3 million work order records across 56 tables. Post-migration validation confirms matching row counts, successful constraint verification, and all triggers recompiled properly. Four weeks after production cutover, the production planning team reports that 7.8% of multi-stage assembly operations are showing incorrect material availability calculations, causing assembly line delays. Investigation reveals that SAP HANA stored decimal precision using its proprietary DECIMAL type with dynamic scale adjustment based on computational context, while Aurora PostgreSQL uses fixed-scale NUMERIC types. The legacy system had a recursive common table expression (CTE) that calculated bill-of-materials requirements by traversing assembly hierarchies up to 12 levels deep, with each level multiplying component quantities by parent requirements and accumulating fractional values. The HANA database automatically extended decimal precision during these recursive multiplications to prevent intermediate rounding, storing temporary results with up to 38 decimal places before final rounding. Aurora PostgreSQL's NUMERIC(18,4) configuration rounds at each recursive iteration, causing precision loss to compound through deep hierarchy levels. When a finished product requires 0.00087 kg of a raw material through a 9-level assembly chain, the accumulated rounding differences result in material shortage flags. The migration successfully converted all stored procedures, CTEs executed with zero compilation errors, and data profiling of 15,000 sample records showed complete numeric field population. Schema validation passed all referential integrity checks. Which validation methodology would have most effectively identified this recursive calculation precision degradation affecting material requirements planning?

Correct Answer: Execute the complete bill-of-materials explosion process on a representative sample of 500 complex assemblies with deep hierarchies, comparing leaf-node material quantity calculations between source and target systems with tolerance thresholds of 0.0001 units to detect cumulative precision drift

The correct approach is to execute the complete bill-of-materials explosion process on a representative sample of complex assemblies with deep hierarchies, comparing leaf-node material quantity calculations between source and target systems with precise tolerance thresholds.

This is the most effective validation methodology because:

  1. Targets the actual business logic: The problem involves recursive calculations through bill-of-materials hierarchies up to 12 levels deep. Testing the complete explosion process exercises the exact computational path where precision loss accumulates.

  2. Detects cumulative precision drift: By comparing leaf-node calculations (the final material requirements), this approach captures the compounding effect of rounding errors through multiple recursion levels. A 9-level assembly chain that results in 0.00087 kg requirements would show discrepancies at the leaf nodes.

  3. Uses appropriate tolerance thresholds: Setting tolerance at 0.0001 units is granular enough to detect the precision degradation described (where NUMERIC(18,4) rounds at each iteration versus HANA's dynamic precision up to 38 decimal places).

  4. Focuses on complex assemblies: Testing 500 assemblies with deep hierarchies specifically targets the scenarios most affected by the issue, rather than simple flat structures.

  5. Validates end-to-end computation: This tests the actual business outcome (material requirements) rather than intermediate technical metrics.

Why the other approaches fall short:

Validating that precision and scale parameters match exactly would only confirm static schema definitions. Since HANA uses dynamic scale adjustment during computation while PostgreSQL uses fixed-scale NUMERIC, the schema comparison wouldn't reveal how these systems behave differently during recursive calculations. The problem isn't in the stored data format but in how intermediate calculations are handled.

Statistical sampling with 5% of work orders and standard deviation analysis examines final aggregated totals but wouldn't necessarily expose the precision loss in deep hierarchies. The 7.8% affected operations suggests this is a specific issue with certain types of assemblies (those with deep hierarchies and small fractional quantities), not a random distribution that statistical sampling would effectively detect. Aggregate totals might mask individual component discrepancies.

Comparing execution plans and intermediate result row counts focuses on query optimization and structural completeness rather than computational precision. Matching row counts at each recursion level confirms the hierarchy traversal works correctly but doesn't validate the numerical precision of calculated quantities. The scenario explicitly states that CTEs executed with zero errors and row counts matched - the issue is subtle numerical precision degradation, not structural problems.

Question 2

A digital advertising agency operates a campaign performance tracking database with a consolidated table containing: CampaignImpressionID (PK), AdvertiserID, AdvertiserCompanyName, AdvertiserIndustrySegment, AdvertiserPrimaryContactEmail, CreativeID, CreativeName, CreativeAssetFileSize, CreativeDesignerID, DesignerFullName, DesignerStudioAffiliation, DesignerYearsOfExperience, PlatformID, PlatformName, PlatformCostPerImpression, PlatformMinimumBudget, PublisherID, PublisherWebsiteDomain, PublisherTrafficRanking, PublisherContentCategory, ImpressionTimestamp, ViewDuration, ClickThroughOccurred. The database stores 28 million impression records and ingests 450,000 new impressions daily. The DBA identifies that AdvertiserIndustrySegment and AdvertiserPrimaryContactEmail both depend on AdvertiserCompanyName, CreativeAssetFileSize depends on CreativeID, DesignerStudioAffiliation and DesignerYearsOfExperience depend on DesignerFullName (which itself depends on CreativeDesignerID), PlatformCostPerImpression and PlatformMinimumBudget depend on PlatformName (which depends on PlatformID), and PublisherTrafficRanking along with PublisherContentCategory depend on PublisherWebsiteDomain. When advertisers rebrand their company name (occurring 65 times annually), their industry segment and contact email require updates across approximately 185,000 impression records per advertiser. When advertising platforms adjust their pricing models during quarterly rate revisions (happening 48 times per year), cost-per-impression data needs correction across roughly 520,000 impressions per platform. When publishers experience significant traffic ranking changes due to algorithm updates (occurring 280 times monthly), their ranking metadata must be refreshed across an average of 42,000 impressions per publisher. The analytics team generates daily dashboards examining creative designer performance metrics, platform cost efficiency trends, and publisher content category engagement patterns for client reporting. Which database decomposition strategy would most comprehensively eliminate the nested transitive dependency chains while preserving real-time analytics capabilities for campaign optimization and client billing accuracy?

Correct Answer: Decompose into separate relations for Advertisers (AdvertiserID, AdvertiserCompanyName, AdvertiserIndustrySegment, AdvertiserPrimaryContactEmail), Creatives (CreativeID, CreativeName, CreativeAssetFileSize, CreativeDesignerID), Designers (CreativeDesignerID, DesignerFullName, DesignerStudioAffiliation, DesignerYearsOfExperience), Platforms (PlatformID, PlatformName, PlatformCostPerImpression, PlatformMinimumBudget), Publishers (PublisherID, PublisherWebsiteDomain, PublisherTrafficRanking, PublisherContentCategory), and a core Impressions fact table (CampaignImpressionID, AdvertiserID, CreativeID, PlatformID, PublisherID, ImpressionTimestamp, ViewDuration, ClickThroughOccurred) with foreign key references to dimensional entities

The correct answer comprehensively addresses all nested transitive dependencies identified in the scenario while maintaining optimal database design principles.

This solution eliminates ALL transitive dependency chains:

  1. Advertiser dependencies: By creating a separate Advertisers table with AdvertiserID as the key, it removes the transitive dependency where AdvertiserIndustrySegment and AdvertiserPrimaryContactEmail depend on AdvertiserCompanyName, which depends on AdvertiserID. When advertisers rebrand (65 times annually affecting 185,000 records each), updates only occur once in the Advertisers table instead of across millions of impression records.

  2. Designer dependencies: Creating a separate Designers table with CreativeDesignerID as the key eliminates the nested transitive dependency where DesignerStudioAffiliation and DesignerYearsOfExperience depend on DesignerFullName, which itself depends on CreativeDesignerID. This is proper 3NF normalization.

  3. Platform dependencies: A separate Platforms table with PlatformID as the key removes the transitive dependency where PlatformCostPerImpression and PlatformMinimumBudget depend on PlatformName, which depends on PlatformID. When platforms adjust pricing quarterly (48 times per year affecting 520,000 impressions each), updates occur once per platform.

  4. Publisher dependencies: A separate Publishers table with PublisherID as the key eliminates dependencies where PublisherTrafficRanking and PublisherContentCategory depend on PublisherWebsiteDomain. When traffic rankings change (280 times monthly affecting 42,000 impressions each), updates are centralized.

  5. Creative dependencies: A separate Creatives table properly isolates CreativeAssetFileSize's dependency on CreativeID.

The core Impressions fact table retains only the primary key, foreign keys to dimensional tables, and true impression-level attributes (timestamp, duration, click-through). This star schema design supports real-time analytics while ensuring data integrity and update efficiency.

Why other answers are incorrect:

The second answer fails to fully normalize by keeping attributes like AdvertiserPrimaryContactEmail, CreativeAssetFileSize, PlatformMinimumBudget, and PublisherTrafficRanking in the Impressions table. This selective denormalization perpetuates update anomalies and doesn't eliminate the transitive dependencies.

The third answer consolidates designer attributes within the CreativeAssets table, failing to resolve the nested transitive dependency chain. DesignerStudioAffiliation and DesignerYearsOfExperience still depend on DesignerFullName, violating 3NF. This means designer information updates would require changes across multiple creative records.

The fourth answer uses natural keys (DesignerFullName, PlatformName, PublisherWebsiteDomain) instead of surrogate keys and doesn't properly separate the DesignerProfiles table with CreativeDesignerID. It also fails to include CreativeDesignerID in the Creatives table, breaking the referential chain. Natural keys in the Impressions table create join inefficiencies and don't resolve the core transitive dependencies. Additionally, omitting PlatformID and PublisherID as surrogate keys complicates foreign key relationships and indexing strategies essential for the 450,000 daily impression ingests.

Question 3

A distributed e-learning platform's database administrator manages a student assessment system processing exam results for 890,000 enrolled learners across 45 countries. On Thursday at 2:47 PM, an academic operations coordinator executes a transaction to recalculate grade point averages in the STUDENT_TRANSCRIPTS table, update course completion percentages in the PROGRESS_TRACKING table, and modify certification eligibility flags in the CREDENTIAL_AWARDS table. At 3:12 PM, the coordinator discovers that the GPA calculation used a 4.0 scale formula instead of the required 5.0 scale formula for European institutions, affecting 178,000 students across 12 countries and potentially invalidating their professional certifications. The transaction was committed at 3:09 PM. The database operates in FULL recovery model with transaction log backups occurring every 20 minutes, and the most recent log backup completed at 3:00 PM. Between the erroneous commit at 3:09 PM and discovery at 3:12 PM, the system has processed and committed 23 new course completions, 67 grade appeals that modified historical records, 41 certificate issuances, and 156 enrollment status changes. The accreditation board requires all grade calculations to be audit-ready by 5:00 PM for an ongoing institutional review. A full database backup exists from 4:00 AM today. The platform's learning management system continuously reads from these tables to display student dashboards to 34,000 currently active users. What recovery approach should the administrator prioritize to restore accurate GPA calculations while preserving the legitimate post-commit academic transactions?

Correct Answer: Perform a point-in-time restore to 3:09 PM in a separate staging database, extract the affected 178,000 student records with correct pre-error values, manually reapply the 67 grade appeals and 23 course completions using transaction log analysis, then merge the corrected GPA data back to production using UPDATE statements joined on student identifiers while preserving all post-error certificate issuances and enrollment changes

The optimal recovery approach prioritizes preserving legitimate post-commit transactions while correcting the erroneous GPA calculations with minimal system disruption.

The correct solution involves performing a point-in-time restore to 3:09 PM (just before the error was committed) in a separate staging database. This approach allows the administrator to:

  1. Extract the 178,000 affected student records with their correct pre-error GPA values from the staging environment
  2. Analyze the transaction log to identify the 67 grade appeals and 23 course completions that occurred after 3:09 PM
  3. Manually reapply these legitimate transactions to ensure no valid academic data is lost
  4. Use targeted UPDATE statements with JOIN operations on student identifiers to merge only the corrected GPA data back into production
  5. Preserve all 41 certificate issuances and 156 enrollment changes that occurred post-commit

This method meets the critical 5:00 PM deadline while maintaining continuous service for 34,000 active users, as the production database remains online throughout the correction process.

Why the other approaches are less suitable:

Restoring to 3:00 PM and attempting to replay filtered transactions introduces significant complexity and risk. Filtering out a single transaction while replaying others from transaction logs is extremely error-prone, especially when dealing with interdependent operations across three tables (STUDENT_TRANSCRIPTS, PROGRESS_TRACKING, and CREDENTIAL_AWARDS). This approach also requires more time than available before the 5:00 PM deadline.

Restoring the full 4:00 AM backup to a parallel instance and applying logs up to 3:09 PM requires processing nearly 11 hours of transaction logs, which is time-prohibitive given the tight deadline. Additionally, using MERGE statements for entire table imports creates unnecessary complexity and increases the risk of data conflicts with the 34,000 active user sessions.

Creating a tail-log backup and working from the 3:00 PM backup sounds procedurally correct but adds unnecessary steps. The tail-log backup is primarily useful for disaster recovery scenarios, not targeted data corrections. Moreover, generating and executing corrective UPDATE statements directly in production without first validating them in a staging environment poses unacceptable risk for an audit-critical system, especially when dealing with 178,000 affected records across international boundaries.

More Database Deployment questions
789 questions (total)
Start 100 question test