Page 1 of 135
NYC Certified IT Administrator (Database) Exam COMPLETE
QUESTIONS AND DETAILED SOLUTIONS LATEST UPDATE THIS
YEAR-JUST RELEASED
NYC Certified IT Administrator (Database) Exam — Summarized Coverage
• SQL Server installation, configuration, patching, and environment maintenance
• Database architecture, relational design, and schema management
• Data modeling principles (normalization, relationships, keys, constraints)
• Backup and restore strategies (full, differential, transaction log backups)
• Disaster recovery planning and business continuity procedures
• High availability solutions (failover clustering, replication, Always On availability groups)
• Performance monitoring and optimization (CPU, memory, disk I/O, query tuning)
• Indexing strategies and query execution plan analysis
• Security administration (logins, roles, permissions, authentication methods)
• Encryption techniques (TDE, data masking, secure connections)
• Database troubleshooting, incident response, and root cause analysis
• ETL concepts and data integration processes (SSIS fundamentals)
• Reporting systems and data extraction (SSRS basics and usage)
• Data warehousing concepts (star schema, snowflake schema, fact/dimension tables)
• Windows Server environment basics supporting SQL Server infrastructure
• Storage management and capacity planning for database systems
• Monitoring tools, alerts, and health check procedures
• Automation using SQL jobs, scripts, and maintenance plans
• Data integrity, ACID properties, and transaction management
• Database migration and deployment across dev, test, and production environments
• Documentation standards, change control, and technical reporting
• Compliance, governance, and data privacy/security policies
• Multi-user concurrency control and locking mechanisms
• Real-world scenario-based problem solving (outages, scaling, recovery, performance
issues)
NYC Certified IT Administrator (Database) Exam
1.
, Page 2 of 135
What is the primary purpose of SQL Server installation during database setup?
A. To design user interfaces
B. To deploy database engine services for data storage and management
C. To create operating system users
D. To configure network printers
Answer: B
Rationale: SQL Server installation sets up the database engine to manage data.
2.
Which SQL Server component is responsible for processing queries and managing data storage?
A. SQL Server Agent
B. Database Engine
C. SSRS
D. SSIS
, Page 3 of 135
Answer: B
Rationale: The Database Engine handles query processing and storage.
3.
What is the main purpose of database normalization?
A. Increase redundancy
B. Reduce data redundancy and improve integrity
C. Increase storage size
D. Duplicate tables for backup
Answer: B
Rationale: Normalization organizes data efficiently and reduces duplication.
4.
, Page 4 of 135
Which SQL concept enforces uniqueness of a column in a table?
A. Foreign Key
B. Primary Key
C. Trigger
D. View
Answer: B
Rationale: Primary keys uniquely identify records.
5.
What is the purpose of a foreign key in relational databases?
A. Encrypt data
B. Link two tables together
C. Delete records automatically
D. Store backups
NYC Certified IT Administrator (Database) Exam COMPLETE
QUESTIONS AND DETAILED SOLUTIONS LATEST UPDATE THIS
YEAR-JUST RELEASED
NYC Certified IT Administrator (Database) Exam — Summarized Coverage
• SQL Server installation, configuration, patching, and environment maintenance
• Database architecture, relational design, and schema management
• Data modeling principles (normalization, relationships, keys, constraints)
• Backup and restore strategies (full, differential, transaction log backups)
• Disaster recovery planning and business continuity procedures
• High availability solutions (failover clustering, replication, Always On availability groups)
• Performance monitoring and optimization (CPU, memory, disk I/O, query tuning)
• Indexing strategies and query execution plan analysis
• Security administration (logins, roles, permissions, authentication methods)
• Encryption techniques (TDE, data masking, secure connections)
• Database troubleshooting, incident response, and root cause analysis
• ETL concepts and data integration processes (SSIS fundamentals)
• Reporting systems and data extraction (SSRS basics and usage)
• Data warehousing concepts (star schema, snowflake schema, fact/dimension tables)
• Windows Server environment basics supporting SQL Server infrastructure
• Storage management and capacity planning for database systems
• Monitoring tools, alerts, and health check procedures
• Automation using SQL jobs, scripts, and maintenance plans
• Data integrity, ACID properties, and transaction management
• Database migration and deployment across dev, test, and production environments
• Documentation standards, change control, and technical reporting
• Compliance, governance, and data privacy/security policies
• Multi-user concurrency control and locking mechanisms
• Real-world scenario-based problem solving (outages, scaling, recovery, performance
issues)
NYC Certified IT Administrator (Database) Exam
1.
, Page 2 of 135
What is the primary purpose of SQL Server installation during database setup?
A. To design user interfaces
B. To deploy database engine services for data storage and management
C. To create operating system users
D. To configure network printers
Answer: B
Rationale: SQL Server installation sets up the database engine to manage data.
2.
Which SQL Server component is responsible for processing queries and managing data storage?
A. SQL Server Agent
B. Database Engine
C. SSRS
D. SSIS
, Page 3 of 135
Answer: B
Rationale: The Database Engine handles query processing and storage.
3.
What is the main purpose of database normalization?
A. Increase redundancy
B. Reduce data redundancy and improve integrity
C. Increase storage size
D. Duplicate tables for backup
Answer: B
Rationale: Normalization organizes data efficiently and reduces duplication.
4.
, Page 4 of 135
Which SQL concept enforces uniqueness of a column in a table?
A. Foreign Key
B. Primary Key
C. Trigger
D. View
Answer: B
Rationale: Primary keys uniquely identify records.
5.
What is the purpose of a foreign key in relational databases?
A. Encrypt data
B. Link two tables together
C. Delete records automatically
D. Store backups
