[INF3703] – QUESTIONS AND ANSWERS | VERIFIED AND WELL DETAILED ANSWERS | PLUS
RATIONALES | GUARANTEED PASS | LATEST EXAM UPDATE
Core Domains: - Database Design and Development - Advanced SQL and Query Optimization
- Database Administration and Security - Transaction Management and Concurrency Control
- Data Warehousing and Business Intelligence - Distributed Databases and Cloud
Architectures - NoSQL Systems and Big Data Infrastructure - Object-Relational Database
Systems
Introduction: The purpose of this comprehensive assessment is to evaluate advanced
competency in database design, implementation, and management frameworks
corresponding to the master curriculum. It rigorously assesses critical skills across conceptual
data modeling, complex query tuning, high-availability architecture deployment, and
transactional integrity validation. The structure features meticulously structured multiple-
choice and complex scenario-based items designed to test real-world problem-solving
capacities and engineering decision-making under realistic infrastructure constraints.
Candidates are expected to synthesize theoretical knowledge of internal database mechanics
with industry-standard compliance and security protocols to resolve modern data
engineering challenges.
Section One: Questions 1–100
Question 1
An organization needs to design an enterprise database that enforces strict structural
constraints while minimizing redundancy. During data modeling, a candidate key is selected
as the primary key. If a non-key attribute is functionally dependent on a subset of a
composite primary key, which normal form is violated?
A. First Normal Form (1NF) B. Second Normal Form (2NF) C. Third Normal Form (3NF) D.
Boyce-Codd Normal Form (BCNF)
B. Second Normal Form (2NF) Explanation: Second Normal Form (2NF) requires that
the schema is in 1NF and contains no partial dependencies, meaning every non-prime
attribute must be fully functionally dependent on the entire primary key, not a proper subset
of it.
Question 2
A database engineer notices that a heavy analytical query executed against an OLTP
database is causing severe lock contention. The query calculates aggregate financial data
across millions of rows and does not require real-time accuracy. Which isolation level should
be applied to this query session to eliminate read locks while preventing the query from
blocking concurrent writes?
A. Read Uncommitted B. Read Committed C. Repeatable Read D. Serializable
, A. Read Uncommitted Explanation: Read Uncommitted isolation permits dirty reads
and does not acquire read locks or honor exclusive locks for reading, making it ideal for non-
critical analytical queries on OLTP systems where performance and non-blocking behavior
are prioritized over perfect consistency.
Question 3
A healthcare organization stores patient medical records in a relational database system. A
new regulatory standard requires that any access to highly sensitive genomic data must be
cryptographically masked at rest, and even database administrators should not have visibility
into the raw cleartext values without explicit application-tier multi-factor clearance. Which
technology provides this specific capability natively at the database layer?
A. Transparent Data Encryption (TDE) B. Transport Layer Security (TLS) C. Always Encrypted
with secure enclaves D. Role-Based Access Control (RBAC)
C. Always Encrypted with secure enclaves Explanation: Always Encrypted ensures
that sensitive data is encrypted inside the client application before being passed to the
database engine, thereby protecting it from high-privileged users like DBAs, while secure
enclaves allow processing on the encrypted data without exposing it.
Question 4
Consider a relational database table named "Shipments" with millions of rows. The column
"Status" has only three distinct values: 'Pending', 'In Transit', and 'Delivered'. A query
frequently filters rows where Status = 'Pending' (which accounts for less than 0.5% of the
total dataset). Which index type is most efficient for optimizing this specific query while
saving storage space?
A. Clustered B-Tree Index B. Non-Clustered B-Tree Index C. Bitmap Index D. Filtered / Partial
Index
D. Filtered / Partial Index Explanation: A Filtered (or Partial) Index applies a WHERE
clause to the index definition itself. Because it only indexes rows matching Status = 'Pending',
it remains exceptionally compact and highly performant for this specific skew in data
distribution.
Question 5
During a system crash, the database management system must guarantee that all
modifications made by committed transactions are completely written to persistent storage,
even if the hardware power fails immediately after the commit confirmation. Which
properties of the ACID paradigm directly addresses this requirement?
A. Atomicity B. Consistency C. Isolation D. Durability
, D. Durability Explanation: Durability guarantees that once a transaction commits, its
survival is guaranteed through permanent logs or non-volatile storage mechanisms,
rendering its modifications resilient against subsequent system failures or crashes.
Question 6
A database administrator is evaluating a distributed financial system spanning three distinct
geographic regions. According to the CAP theorem, if the system prioritizes strict consistency
(C) and network partition tolerance (P), how must the system behave during an active fiber
line cut between two regions?
A. It must accept writes in all regions and resolve conflicts asynchronously. B. It must reject
writes or return errors in partitions that cannot achieve a quorum. C. It must automatically
drop partition tolerance and operate as a localized database. D. It must trade consistency for
availability by allowing stale reads across all nodes.
B. It must reject writes or return errors in partitions that cannot achieve a quorum.
Explanation: The CAP theorem states that a system can guarantee at most two of the three
properties. If Consistency (C) and Partition Tolerance (P) are selected, the system must
sacrifice Availability (A) during a partition, requiring it to reject updates that cannot be safely
agreed upon by a consistent quorum.
Question 7
A software engineering team is building a high-throughput logging application that accepts
unstructured and rapidly changing JSON telemetry structures from IoT sensors. The system
needs sub-millisecond writes and horizontal scalability across a cluster of commodity
servers. Which database paradigm best fits this workload?
A. Relational Database Management System (RDBMS) B. Document-oriented NoSQL
Database C. Graph Database D. Object-Oriented Database (OODBMS)
B. Document-oriented NoSQL Database Explanation: Document-oriented NoSQL
databases store semi-structured data as JSON-like documents. They offer flexible schemas to
handle changing IoT telemetry payloads and scale horizontally via sharding to satisfy high-
throughput write constraints.
Question 8
A complex enterprise query involves joining a large fact table containing 50 million rows with
a small dimension table containing only 10 rows. The query optimizer decides to copy the
entire small dimension table to memory on all processing nodes to avoid expensive data
shuffling across the network during the join phase. What is the name of this join strategy?
A. Hash Match Join B. Nested Loops Join C. Broadcast / Broadcast-Loop Join D. Merge Join
, C. Broadcast / Broadcast-Loop Join Explanation: A broadcast join (or map-side join)
copies a small table across all distributed cluster nodes so that a large distributed table can
be joined locally without moving its large partitions over the network network fabric.
Question 9
A database analyst is investigating a performance degradation issue. They find that a multi-
table query relies heavily on a view that references three other views nested deep within
each other. The execution plan reveals that the database engine evaluates the full query tree
from scratch for every execution. Which strategy would provide the greatest latency
reduction for this workload?
A. Convert the base views into inline table-valued functions. B. Implement database sharding
based on the primary key. C. Materialize the outermost view with a physical index. D.
Rebuild all clustered indexes on the underlying tables.
C. Materialize the outermost view with a physical index. Explanation: Materializing a
view (creating an indexed view) computes the result set of the view expression and
physically stores it in the database just like a table, removing the overhead of evaluating
complex nested view definitions on every run.
Question 10
A database transaction reads the value of a row, modifies it, and prepares to write it back.
Simultaneously, a concurrent transaction updates the exact same row and commits. When
the first transaction attempts to commit, it overwrites the changes made by the second
transaction without evaluating them. What specific concurrency anomaly has occurred?
A. Dirty Read B. Non-Repeatable Read C. Phantom Read D. Lost Update
D. Lost Update Explanation: A Lost Update occurs when two transactions select the
same row, calculate a new value based on the original data, and then attempt to write
updates concurrently, resulting in one transaction overwriting the other's committed data
without incorporating its changes.
Question 11
An engineer is defining an enterprise entity-relationship model. They encounter a scenario
where a "Project" entity can exist independently, but an "Assignment" entity cannot exist
without being tied directly to a parent "Project" entity. If the parent project is deleted, all
related assignments must automatically be purged. How is the "Assignment" entity classified
in this relational model?
A. Strong Entity B. Weak Entity C. Associative Entity D. Recursive Entity
B. Weak Entity Explanation: A weak entity is an entity that cannot be uniquely
identified by its own attributes alone and depends on the existence of a parent (or
RATIONALES | GUARANTEED PASS | LATEST EXAM UPDATE
Core Domains: - Database Design and Development - Advanced SQL and Query Optimization
- Database Administration and Security - Transaction Management and Concurrency Control
- Data Warehousing and Business Intelligence - Distributed Databases and Cloud
Architectures - NoSQL Systems and Big Data Infrastructure - Object-Relational Database
Systems
Introduction: The purpose of this comprehensive assessment is to evaluate advanced
competency in database design, implementation, and management frameworks
corresponding to the master curriculum. It rigorously assesses critical skills across conceptual
data modeling, complex query tuning, high-availability architecture deployment, and
transactional integrity validation. The structure features meticulously structured multiple-
choice and complex scenario-based items designed to test real-world problem-solving
capacities and engineering decision-making under realistic infrastructure constraints.
Candidates are expected to synthesize theoretical knowledge of internal database mechanics
with industry-standard compliance and security protocols to resolve modern data
engineering challenges.
Section One: Questions 1–100
Question 1
An organization needs to design an enterprise database that enforces strict structural
constraints while minimizing redundancy. During data modeling, a candidate key is selected
as the primary key. If a non-key attribute is functionally dependent on a subset of a
composite primary key, which normal form is violated?
A. First Normal Form (1NF) B. Second Normal Form (2NF) C. Third Normal Form (3NF) D.
Boyce-Codd Normal Form (BCNF)
B. Second Normal Form (2NF) Explanation: Second Normal Form (2NF) requires that
the schema is in 1NF and contains no partial dependencies, meaning every non-prime
attribute must be fully functionally dependent on the entire primary key, not a proper subset
of it.
Question 2
A database engineer notices that a heavy analytical query executed against an OLTP
database is causing severe lock contention. The query calculates aggregate financial data
across millions of rows and does not require real-time accuracy. Which isolation level should
be applied to this query session to eliminate read locks while preventing the query from
blocking concurrent writes?
A. Read Uncommitted B. Read Committed C. Repeatable Read D. Serializable
, A. Read Uncommitted Explanation: Read Uncommitted isolation permits dirty reads
and does not acquire read locks or honor exclusive locks for reading, making it ideal for non-
critical analytical queries on OLTP systems where performance and non-blocking behavior
are prioritized over perfect consistency.
Question 3
A healthcare organization stores patient medical records in a relational database system. A
new regulatory standard requires that any access to highly sensitive genomic data must be
cryptographically masked at rest, and even database administrators should not have visibility
into the raw cleartext values without explicit application-tier multi-factor clearance. Which
technology provides this specific capability natively at the database layer?
A. Transparent Data Encryption (TDE) B. Transport Layer Security (TLS) C. Always Encrypted
with secure enclaves D. Role-Based Access Control (RBAC)
C. Always Encrypted with secure enclaves Explanation: Always Encrypted ensures
that sensitive data is encrypted inside the client application before being passed to the
database engine, thereby protecting it from high-privileged users like DBAs, while secure
enclaves allow processing on the encrypted data without exposing it.
Question 4
Consider a relational database table named "Shipments" with millions of rows. The column
"Status" has only three distinct values: 'Pending', 'In Transit', and 'Delivered'. A query
frequently filters rows where Status = 'Pending' (which accounts for less than 0.5% of the
total dataset). Which index type is most efficient for optimizing this specific query while
saving storage space?
A. Clustered B-Tree Index B. Non-Clustered B-Tree Index C. Bitmap Index D. Filtered / Partial
Index
D. Filtered / Partial Index Explanation: A Filtered (or Partial) Index applies a WHERE
clause to the index definition itself. Because it only indexes rows matching Status = 'Pending',
it remains exceptionally compact and highly performant for this specific skew in data
distribution.
Question 5
During a system crash, the database management system must guarantee that all
modifications made by committed transactions are completely written to persistent storage,
even if the hardware power fails immediately after the commit confirmation. Which
properties of the ACID paradigm directly addresses this requirement?
A. Atomicity B. Consistency C. Isolation D. Durability
, D. Durability Explanation: Durability guarantees that once a transaction commits, its
survival is guaranteed through permanent logs or non-volatile storage mechanisms,
rendering its modifications resilient against subsequent system failures or crashes.
Question 6
A database administrator is evaluating a distributed financial system spanning three distinct
geographic regions. According to the CAP theorem, if the system prioritizes strict consistency
(C) and network partition tolerance (P), how must the system behave during an active fiber
line cut between two regions?
A. It must accept writes in all regions and resolve conflicts asynchronously. B. It must reject
writes or return errors in partitions that cannot achieve a quorum. C. It must automatically
drop partition tolerance and operate as a localized database. D. It must trade consistency for
availability by allowing stale reads across all nodes.
B. It must reject writes or return errors in partitions that cannot achieve a quorum.
Explanation: The CAP theorem states that a system can guarantee at most two of the three
properties. If Consistency (C) and Partition Tolerance (P) are selected, the system must
sacrifice Availability (A) during a partition, requiring it to reject updates that cannot be safely
agreed upon by a consistent quorum.
Question 7
A software engineering team is building a high-throughput logging application that accepts
unstructured and rapidly changing JSON telemetry structures from IoT sensors. The system
needs sub-millisecond writes and horizontal scalability across a cluster of commodity
servers. Which database paradigm best fits this workload?
A. Relational Database Management System (RDBMS) B. Document-oriented NoSQL
Database C. Graph Database D. Object-Oriented Database (OODBMS)
B. Document-oriented NoSQL Database Explanation: Document-oriented NoSQL
databases store semi-structured data as JSON-like documents. They offer flexible schemas to
handle changing IoT telemetry payloads and scale horizontally via sharding to satisfy high-
throughput write constraints.
Question 8
A complex enterprise query involves joining a large fact table containing 50 million rows with
a small dimension table containing only 10 rows. The query optimizer decides to copy the
entire small dimension table to memory on all processing nodes to avoid expensive data
shuffling across the network during the join phase. What is the name of this join strategy?
A. Hash Match Join B. Nested Loops Join C. Broadcast / Broadcast-Loop Join D. Merge Join
, C. Broadcast / Broadcast-Loop Join Explanation: A broadcast join (or map-side join)
copies a small table across all distributed cluster nodes so that a large distributed table can
be joined locally without moving its large partitions over the network network fabric.
Question 9
A database analyst is investigating a performance degradation issue. They find that a multi-
table query relies heavily on a view that references three other views nested deep within
each other. The execution plan reveals that the database engine evaluates the full query tree
from scratch for every execution. Which strategy would provide the greatest latency
reduction for this workload?
A. Convert the base views into inline table-valued functions. B. Implement database sharding
based on the primary key. C. Materialize the outermost view with a physical index. D.
Rebuild all clustered indexes on the underlying tables.
C. Materialize the outermost view with a physical index. Explanation: Materializing a
view (creating an indexed view) computes the result set of the view expression and
physically stores it in the database just like a table, removing the overhead of evaluating
complex nested view definitions on every run.
Question 10
A database transaction reads the value of a row, modifies it, and prepares to write it back.
Simultaneously, a concurrent transaction updates the exact same row and commits. When
the first transaction attempts to commit, it overwrites the changes made by the second
transaction without evaluating them. What specific concurrency anomaly has occurred?
A. Dirty Read B. Non-Repeatable Read C. Phantom Read D. Lost Update
D. Lost Update Explanation: A Lost Update occurs when two transactions select the
same row, calculate a new value based on the original data, and then attempt to write
updates concurrently, resulting in one transaction overwriting the other's committed data
without incorporating its changes.
Question 11
An engineer is defining an enterprise entity-relationship model. They encounter a scenario
where a "Project" entity can exist independently, but an "Assignment" entity cannot exist
without being tied directly to a parent "Project" entity. If the parent project is deleted, all
related assignments must automatically be purged. How is the "Assignment" entity classified
in this relational model?
A. Strong Entity B. Weak Entity C. Associative Entity D. Recursive Entity
B. Weak Entity Explanation: A weak entity is an entity that cannot be uniquely
identified by its own attributes alone and depends on the existence of a parent (or
