NYC CERTIFIED IT ADMINISTRATOR (DATABASE) LATEST EXAM
2026/2027 PRACTICE QUESTIONS & STUDY GUIDE COMPLETE
ACCURATE EXAM ACTUAL QUESTIONS AND CORRECT DETAILED
ANSWERS WITH RATIONALES (100% CORRECT VERIFIED
ANSWERS) NEWEST UPDATED VERSION 2026 EDITION
|GUARANTEED PASS A+ |FULL REVISED EXAM
Which of the following best describes the primary purpose of a database
management system (DBMS)?
A. To manage the operating system's file storage
B. To provide an interface for users to browse the internet
C. To efficiently store, retrieve, and manage data while ensuring security and
integrity
D. To compile programming code into executable files
C. To efficiently store, retrieve, and manage data while ensuring security and
integrity CORRECT ANSWER
Rationale: A DBMS is specialized software designed to store, retrieve, define,
and manage data in a database. It enforces security, concurrency, and integrity
constraints. Options A, B, and D describe functions of an OS, web browser, and
compiler respectively.
Which database model organizes data in tables consisting of rows and columns
with predefined relationships?
A. Hierarchical model
B. Network model
C. Relational model
D. Object-oriented model
C. Relational model CORRECT ANSWER
,Rationale: The relational model, introduced by E.F. Cod, structures data into
relations (tables) with tuples (rows) and attributes (columns). Hierarchical (A)
uses tree structures, network (B) uses graphs with record types, and object-
oriented (D) integrates object-oriented programming concepts.
In the context of ACID properties, which property ensures that a transaction is
treated as a single, indivisible unit?
A. Consistency
B. Isolation
C. Durability
D. Atomicity
D. Atomicity CORRECT ANSWER
Rationale: Atomicity guarantees that each transaction is "all or nothing" – if
any part fails, the entire transaction fails and the database state is unchanged.
Consistency ensures valid state transitions, isolation handles concurrency, and
durability guarantees persistence after commit.
Which SQL statement is used to retrieve data from a database?
A. INSERT
B. UPDATE
C. SELECT
D. DELETE
C. SELECT CORRECT ANSWER
Rationale: SELECT is the DML command for querying and retrieving data.
INSERT adds new rows, UPDATE modifies existing rows, and DELETE
removes rows.
A database administrator needs to remove all rows from a table but keep the table
structure intact for future use. Which command should they use?
,A. DROP TABLE
B. DELETE * FROM table name
C. TRUNCATE TABLE
D. ALTER TABLE
C. TRUNCATE TABLE CORRECT ANSWER
Rationale: TRUNCATE TABLE quickly removes all rows, resets storage (like
auto-increment counters), and cannot be rolled back in some DBMS without
transaction logging. DROP TABLE removes the table entirely. DELETE can
also remove all rows but is slower and logged per row. ALTER modifies
structure.
Which normal form requires that every non-key attribute is functionally dependent
on the whole primary key and not just part of it?
A. First normal form (1NF)
B. Second normal form (2NF)
C. Third normal form (3NF)
D. Boyce-Cod normal form (BCNF)
B. Second normal form (2NF) CORRECT ANSWER
Rationale: 2NF eliminates partial dependencies – non-key attributes must
depend on the entire primary key. 1NF ensures atomic values. 3NF removes
transitive dependencies. BCNF is a stricter version of 3NF.
Which type of index physically reorders the data rows according to the index key?
A. Non-clustered index
B. Hash index
C. Bitmap index
D. Clustered index
D. Clustered index CORRECT ANSWER
, Rationale: A clustered index determines the physical order of data rows in a
table. A table can have only one clustered index. Non-clustered (A) maintains a
separate structure pointing to data rows. Hash (B) is for equality lookups.
Bitmap (C) is used for low-cardinality columns.
Which isolation level in SQL Server prevents dirty reads, non-repeatable reads,
and phantom reads?
A. READ UNCOMMITTED
B. READ COMMITTED
C. REPEATABLE READ
D. SERIALIZABLE
D. SERIALIZABLE CORRECT ANSWER
Rationale: SERIALIZABLE is the highest isolation level, ensuring complete
isolation where transactions appear as if executed sequentially. It prevents all
concurrency anomalies including phantoms. READ UNCOMMITTED allows
dirty reads; READ COMMITTED prevents dirty reads only; REPEATABLE
READ prevents dirty and non-repeatable reads but allows phantoms.
What is the purpose of a database view?
A. To store large binary objects
B. To provide a virtual table based on the result of a SELECT query
C. To physically partition data across multiple disks
D. To back up the transaction log
B. To provide a virtual table based on the result of a SELECT query CORRECT
ANSWER
Rationale: A view is a stored query that acts as a virtual table, offering security,
simplicity, and data abstraction. It does not store data physically (except
materialized views). Options A, C, and D are unrelated.
2026/2027 PRACTICE QUESTIONS & STUDY GUIDE COMPLETE
ACCURATE EXAM ACTUAL QUESTIONS AND CORRECT DETAILED
ANSWERS WITH RATIONALES (100% CORRECT VERIFIED
ANSWERS) NEWEST UPDATED VERSION 2026 EDITION
|GUARANTEED PASS A+ |FULL REVISED EXAM
Which of the following best describes the primary purpose of a database
management system (DBMS)?
A. To manage the operating system's file storage
B. To provide an interface for users to browse the internet
C. To efficiently store, retrieve, and manage data while ensuring security and
integrity
D. To compile programming code into executable files
C. To efficiently store, retrieve, and manage data while ensuring security and
integrity CORRECT ANSWER
Rationale: A DBMS is specialized software designed to store, retrieve, define,
and manage data in a database. It enforces security, concurrency, and integrity
constraints. Options A, B, and D describe functions of an OS, web browser, and
compiler respectively.
Which database model organizes data in tables consisting of rows and columns
with predefined relationships?
A. Hierarchical model
B. Network model
C. Relational model
D. Object-oriented model
C. Relational model CORRECT ANSWER
,Rationale: The relational model, introduced by E.F. Cod, structures data into
relations (tables) with tuples (rows) and attributes (columns). Hierarchical (A)
uses tree structures, network (B) uses graphs with record types, and object-
oriented (D) integrates object-oriented programming concepts.
In the context of ACID properties, which property ensures that a transaction is
treated as a single, indivisible unit?
A. Consistency
B. Isolation
C. Durability
D. Atomicity
D. Atomicity CORRECT ANSWER
Rationale: Atomicity guarantees that each transaction is "all or nothing" – if
any part fails, the entire transaction fails and the database state is unchanged.
Consistency ensures valid state transitions, isolation handles concurrency, and
durability guarantees persistence after commit.
Which SQL statement is used to retrieve data from a database?
A. INSERT
B. UPDATE
C. SELECT
D. DELETE
C. SELECT CORRECT ANSWER
Rationale: SELECT is the DML command for querying and retrieving data.
INSERT adds new rows, UPDATE modifies existing rows, and DELETE
removes rows.
A database administrator needs to remove all rows from a table but keep the table
structure intact for future use. Which command should they use?
,A. DROP TABLE
B. DELETE * FROM table name
C. TRUNCATE TABLE
D. ALTER TABLE
C. TRUNCATE TABLE CORRECT ANSWER
Rationale: TRUNCATE TABLE quickly removes all rows, resets storage (like
auto-increment counters), and cannot be rolled back in some DBMS without
transaction logging. DROP TABLE removes the table entirely. DELETE can
also remove all rows but is slower and logged per row. ALTER modifies
structure.
Which normal form requires that every non-key attribute is functionally dependent
on the whole primary key and not just part of it?
A. First normal form (1NF)
B. Second normal form (2NF)
C. Third normal form (3NF)
D. Boyce-Cod normal form (BCNF)
B. Second normal form (2NF) CORRECT ANSWER
Rationale: 2NF eliminates partial dependencies – non-key attributes must
depend on the entire primary key. 1NF ensures atomic values. 3NF removes
transitive dependencies. BCNF is a stricter version of 3NF.
Which type of index physically reorders the data rows according to the index key?
A. Non-clustered index
B. Hash index
C. Bitmap index
D. Clustered index
D. Clustered index CORRECT ANSWER
, Rationale: A clustered index determines the physical order of data rows in a
table. A table can have only one clustered index. Non-clustered (A) maintains a
separate structure pointing to data rows. Hash (B) is for equality lookups.
Bitmap (C) is used for low-cardinality columns.
Which isolation level in SQL Server prevents dirty reads, non-repeatable reads,
and phantom reads?
A. READ UNCOMMITTED
B. READ COMMITTED
C. REPEATABLE READ
D. SERIALIZABLE
D. SERIALIZABLE CORRECT ANSWER
Rationale: SERIALIZABLE is the highest isolation level, ensuring complete
isolation where transactions appear as if executed sequentially. It prevents all
concurrency anomalies including phantoms. READ UNCOMMITTED allows
dirty reads; READ COMMITTED prevents dirty reads only; REPEATABLE
READ prevents dirty and non-repeatable reads but allows phantoms.
What is the purpose of a database view?
A. To store large binary objects
B. To provide a virtual table based on the result of a SELECT query
C. To physically partition data across multiple disks
D. To back up the transaction log
B. To provide a virtual table based on the result of a SELECT query CORRECT
ANSWER
Rationale: A view is a stored query that acts as a virtual table, offering security,
simplicity, and data abstraction. It does not store data physically (except
materialized views). Options A, C, and D are unrelated.
