2026/2027 Elite Test Bank:
Modern Database
Management (UT Austin MIS
325/333K Standards)
PART 0: THE NAVIGATOR
● PART I: THE PRIMER (Executive Briefing & Cheat Sheet)
● PART II: THE ELITE TEST BANK
○ Questions 1–15: Foundational Syntax & Application (ERD Modeling,
Normalization, Pine Valley Architectures)
○ Questions 16–40: Professional Simulation (Advanced SQL, Transaction Integrity,
Physical Tuning, MIS 333K/325 Contexts)
○ Questions 41–66: Grandmaster Synthesis (SQL Server 2025, Oracle 23ai,
Vector Databases, Distributed SQL, HA/DR)
PART I: THE PRIMER
Mastering the intersection of rigorous relational theory and cloud-native AI integration is what
separates architects from mere operators. This document forges top-tier intuition, directly
translating academic mastery into 2026/2027 enterprise dominance.
● Normalization Hard Deck: 1NF (atomic domains), 2NF (full key dependency), 3NF (no
transitive dependencies), BCNF (every determinant is a candidate key).
● Concurrency Rules: Two-Phase Locking (2PL) ensures serializability but causes
blocking; Multi-Version Concurrency Control (MVCC) enables Snapshot Isolation where
readers never block writers.
● Vector Search (2026/2027): Use Cosine Similarity for text/NLP where document length
varies; use Euclidean Distance for absolute magnitude comparisons. Approximate
Nearest Neighbor (ANN) indexes like DiskANN trade minimal recall for massive latency
reduction.
● Distributed CAP Theorem: You cannot bypass network partitions. Choose between
synchronous replication (strong consistency, high latency) and asynchronous replication
(high availability, eventual consistency).
● Oracle 23ai Duality: JSON-Relational Duality Views require ETAGs for lock-free,
optimistic concurrency control over normalized tables.
PART II: THE ELITE TEST BANK
Q1: You are designing the initial conceptual data model for Pine Valley Furniture. A business
rule states that a customer can have multiple phone numbers, and a phone number can be
,assigned to multiple customers (e.g., corporate extensions). Which entity construct is MOST
APPROPRIATE for resolving this requirement in a relational schema? A) A multivalued attribute
attached directly to the Customer entity. B) A single composite attribute grouping all phone
numbers in one column. C) An associative entity linking Customer and Phone Number. D) A
weak entity solely dependent on the Customer primary key.
● The Answer: C (An associative entity linking Customer and Phone Number.)
● Distractor Analysis:
○ A is incorrect: Relational databases cannot natively store multivalued attributes
without violating First Normal Form (1NF).
○ B is incorrect: A composite attribute combines different concepts (e.g., Area Code,
Number), it does not allow for multiple distinct instances of the same concept.
○ D is incorrect: While it separates the data, a weak entity implies exclusive
ownership. Because the phone number is shared across multiple customers, it is a
many-to-many relationship requiring an associative entity.
The Mentor's Analysis: Novices often try to force many-to-many relationships into standard
foreign keys or weak entities. If a resource (like a corporate phone number) is shared, it exists
independently of a single customer. Creating an associative entity provides a logical container to
store intersection data and guarantees 1NF compliance. Professional Intuition: Always
resolve many-to-many cardinalities with an associative entity before moving to logical design.
Q2: A junior developer proposes storing a JSON document containing a customer's entire order
history in a single VARCHAR(MAX) column to "avoid complex joins." Which anomaly is MOST
LIKELY to occur during routine database operations? A) An insertion anomaly where a new
product cannot be added unless a customer orders it. B) A modification anomaly requiring the
parsing and rewriting of the entire JSON string to update a single product price. C) A deletion
anomaly where removing a customer deletes the overarching JSON schema. D) A normalization
anomaly where the primary key becomes a foreign key.
● The Answer: B (A modification anomaly requiring the parsing and rewriting of the entire
JSON string to update a single product price.)
● Distractor Analysis:
○ A is incorrect: Products would be stored in their own catalog, not inside the
customer JSON.
○ C is incorrect: Deleting the customer deletes their specific row, but the schema
remains intact.
○ D is incorrect: This is not a recognized anomaly term.
The Mentor's Analysis: Storing highly structured, relational data in a single JSON blob inside a
relational database destroys the engine's ability to enforce atomicity. If a product's price
changes, you must update every single JSON document containing that product. This is a
classic violation of normalization principles under the guise of "flexibility." Professional
Intuition: Use JSON for truly unstructured or highly variable data, never as a substitute for
standard normalization.
Q3: During a schema review, you notice a PROJECT table with the following attributes:
Project_ID (PK), Project_Name, Manager_ID, and Manager_Phone. Which normal form does
this table IMMEDIATELY violate? A) First Normal Form (1NF) B) Second Normal Form (2NF) C)
Third Normal Form (3NF) D) Boyce-Codd Normal Form (BCNF)
● The Answer: C (Third Normal Form (3NF))
● Distractor Analysis:
○ A is incorrect: There are no repeating groups or multivalued attributes indicated.
○ B is incorrect: The primary key (Project_ID) is a single attribute, meaning partial
, dependencies (which violate 2NF) are mathematically impossible.
○ D is incorrect: BCNF deals with overlapping candidate keys, which is not the
primary issue here.
The Mentor's Analysis: Third Normal Form mandates "the key, the whole key, and nothing but
the key." Manager_Phone depends on Manager_ID, not the primary key (Project_ID). This is a
transitive dependency. If the manager changes their phone number, you must update every
project row they manage. Professional Intuition: If an attribute describes a person or thing
other than the primary entity, it belongs in a different table.
Q4: In the Pine Valley Furniture database, the ORDER_LINE table contains Order_ID and
Product_ID as a composite primary key. A developer adds Product_Standard_Price to this table.
Which normalization rule is DIRECTLY violated? A) 1NF B) 2NF C) 3NF D) BCNF
● The Answer: B (2NF)
● Distractor Analysis:
○ A is incorrect: The data is atomic.
○ C is incorrect: 3NF deals with non-key to non-key dependencies. Here, the
dependency involves part of the primary key.
○ D is incorrect: BCNF is a stricter version of 3NF, not applicable here.
The Mentor's Analysis: Product_Standard_Price depends only on Product_ID, which is just
one part of the composite primary key. This is the exact definition of a partial functional
dependency, which violates Second Normal Form. The price belongs in the PRODUCT table.
Professional Intuition: Any time you have a composite key, fiercely audit non-key columns to
ensure they require the entire composite key to exist.
Q5: You are designing a metadata repository for a new enterprise data warehouse. Which piece
of information is LEAST likely to be found in the structural metadata? A) The data type and
maximum length of the Customer_Name attribute. B) The specific business rules governing the
calculation of a discount. C) The exact geographical address of a specific customer instance. D)
The source system from which the Order_Date is extracted.
● The Answer: C (The exact geographical address of a specific customer instance.)
● Distractor Analysis:
○ A is incorrect: Data types and lengths are standard structural metadata.
○ B is incorrect: Business rules are considered semantic or business metadata.
○ D is incorrect: Data lineage and source mapping are core operational metadata
components.
The Mentor's Analysis: Metadata is "data about data." It describes the context, rules,
constraints, and origins of the information. It does not contain the actual user data instances. A
specific customer's physical address is the data itself, not the metadata describing the data
structure. Professional Intuition: Never confuse the container's blueprint (metadata) with the
payload (data).
Q6: When executing a complex query in SQL Server 2025, you need to ensure that records
from the EMPLOYEE table are returned even if they do not have a matching record in the
DEPARTMENT table. Which operation is MOST APPROPRIATE? A) INNER JOIN B) NATURAL
JOIN C) LEFT OUTER JOIN D) CROSS JOIN
● The Answer: C (LEFT OUTER JOIN)
● Distractor Analysis:
○ A is incorrect: An inner join drops all unmatched rows.
○ B is incorrect: A natural join acts as an inner join based on identical column names
and drops unmatched rows.
○ D is incorrect: A cross join produces a Cartesian product, matching every employee
Modern Database
Management (UT Austin MIS
325/333K Standards)
PART 0: THE NAVIGATOR
● PART I: THE PRIMER (Executive Briefing & Cheat Sheet)
● PART II: THE ELITE TEST BANK
○ Questions 1–15: Foundational Syntax & Application (ERD Modeling,
Normalization, Pine Valley Architectures)
○ Questions 16–40: Professional Simulation (Advanced SQL, Transaction Integrity,
Physical Tuning, MIS 333K/325 Contexts)
○ Questions 41–66: Grandmaster Synthesis (SQL Server 2025, Oracle 23ai,
Vector Databases, Distributed SQL, HA/DR)
PART I: THE PRIMER
Mastering the intersection of rigorous relational theory and cloud-native AI integration is what
separates architects from mere operators. This document forges top-tier intuition, directly
translating academic mastery into 2026/2027 enterprise dominance.
● Normalization Hard Deck: 1NF (atomic domains), 2NF (full key dependency), 3NF (no
transitive dependencies), BCNF (every determinant is a candidate key).
● Concurrency Rules: Two-Phase Locking (2PL) ensures serializability but causes
blocking; Multi-Version Concurrency Control (MVCC) enables Snapshot Isolation where
readers never block writers.
● Vector Search (2026/2027): Use Cosine Similarity for text/NLP where document length
varies; use Euclidean Distance for absolute magnitude comparisons. Approximate
Nearest Neighbor (ANN) indexes like DiskANN trade minimal recall for massive latency
reduction.
● Distributed CAP Theorem: You cannot bypass network partitions. Choose between
synchronous replication (strong consistency, high latency) and asynchronous replication
(high availability, eventual consistency).
● Oracle 23ai Duality: JSON-Relational Duality Views require ETAGs for lock-free,
optimistic concurrency control over normalized tables.
PART II: THE ELITE TEST BANK
Q1: You are designing the initial conceptual data model for Pine Valley Furniture. A business
rule states that a customer can have multiple phone numbers, and a phone number can be
,assigned to multiple customers (e.g., corporate extensions). Which entity construct is MOST
APPROPRIATE for resolving this requirement in a relational schema? A) A multivalued attribute
attached directly to the Customer entity. B) A single composite attribute grouping all phone
numbers in one column. C) An associative entity linking Customer and Phone Number. D) A
weak entity solely dependent on the Customer primary key.
● The Answer: C (An associative entity linking Customer and Phone Number.)
● Distractor Analysis:
○ A is incorrect: Relational databases cannot natively store multivalued attributes
without violating First Normal Form (1NF).
○ B is incorrect: A composite attribute combines different concepts (e.g., Area Code,
Number), it does not allow for multiple distinct instances of the same concept.
○ D is incorrect: While it separates the data, a weak entity implies exclusive
ownership. Because the phone number is shared across multiple customers, it is a
many-to-many relationship requiring an associative entity.
The Mentor's Analysis: Novices often try to force many-to-many relationships into standard
foreign keys or weak entities. If a resource (like a corporate phone number) is shared, it exists
independently of a single customer. Creating an associative entity provides a logical container to
store intersection data and guarantees 1NF compliance. Professional Intuition: Always
resolve many-to-many cardinalities with an associative entity before moving to logical design.
Q2: A junior developer proposes storing a JSON document containing a customer's entire order
history in a single VARCHAR(MAX) column to "avoid complex joins." Which anomaly is MOST
LIKELY to occur during routine database operations? A) An insertion anomaly where a new
product cannot be added unless a customer orders it. B) A modification anomaly requiring the
parsing and rewriting of the entire JSON string to update a single product price. C) A deletion
anomaly where removing a customer deletes the overarching JSON schema. D) A normalization
anomaly where the primary key becomes a foreign key.
● The Answer: B (A modification anomaly requiring the parsing and rewriting of the entire
JSON string to update a single product price.)
● Distractor Analysis:
○ A is incorrect: Products would be stored in their own catalog, not inside the
customer JSON.
○ C is incorrect: Deleting the customer deletes their specific row, but the schema
remains intact.
○ D is incorrect: This is not a recognized anomaly term.
The Mentor's Analysis: Storing highly structured, relational data in a single JSON blob inside a
relational database destroys the engine's ability to enforce atomicity. If a product's price
changes, you must update every single JSON document containing that product. This is a
classic violation of normalization principles under the guise of "flexibility." Professional
Intuition: Use JSON for truly unstructured or highly variable data, never as a substitute for
standard normalization.
Q3: During a schema review, you notice a PROJECT table with the following attributes:
Project_ID (PK), Project_Name, Manager_ID, and Manager_Phone. Which normal form does
this table IMMEDIATELY violate? A) First Normal Form (1NF) B) Second Normal Form (2NF) C)
Third Normal Form (3NF) D) Boyce-Codd Normal Form (BCNF)
● The Answer: C (Third Normal Form (3NF))
● Distractor Analysis:
○ A is incorrect: There are no repeating groups or multivalued attributes indicated.
○ B is incorrect: The primary key (Project_ID) is a single attribute, meaning partial
, dependencies (which violate 2NF) are mathematically impossible.
○ D is incorrect: BCNF deals with overlapping candidate keys, which is not the
primary issue here.
The Mentor's Analysis: Third Normal Form mandates "the key, the whole key, and nothing but
the key." Manager_Phone depends on Manager_ID, not the primary key (Project_ID). This is a
transitive dependency. If the manager changes their phone number, you must update every
project row they manage. Professional Intuition: If an attribute describes a person or thing
other than the primary entity, it belongs in a different table.
Q4: In the Pine Valley Furniture database, the ORDER_LINE table contains Order_ID and
Product_ID as a composite primary key. A developer adds Product_Standard_Price to this table.
Which normalization rule is DIRECTLY violated? A) 1NF B) 2NF C) 3NF D) BCNF
● The Answer: B (2NF)
● Distractor Analysis:
○ A is incorrect: The data is atomic.
○ C is incorrect: 3NF deals with non-key to non-key dependencies. Here, the
dependency involves part of the primary key.
○ D is incorrect: BCNF is a stricter version of 3NF, not applicable here.
The Mentor's Analysis: Product_Standard_Price depends only on Product_ID, which is just
one part of the composite primary key. This is the exact definition of a partial functional
dependency, which violates Second Normal Form. The price belongs in the PRODUCT table.
Professional Intuition: Any time you have a composite key, fiercely audit non-key columns to
ensure they require the entire composite key to exist.
Q5: You are designing a metadata repository for a new enterprise data warehouse. Which piece
of information is LEAST likely to be found in the structural metadata? A) The data type and
maximum length of the Customer_Name attribute. B) The specific business rules governing the
calculation of a discount. C) The exact geographical address of a specific customer instance. D)
The source system from which the Order_Date is extracted.
● The Answer: C (The exact geographical address of a specific customer instance.)
● Distractor Analysis:
○ A is incorrect: Data types and lengths are standard structural metadata.
○ B is incorrect: Business rules are considered semantic or business metadata.
○ D is incorrect: Data lineage and source mapping are core operational metadata
components.
The Mentor's Analysis: Metadata is "data about data." It describes the context, rules,
constraints, and origins of the information. It does not contain the actual user data instances. A
specific customer's physical address is the data itself, not the metadata describing the data
structure. Professional Intuition: Never confuse the container's blueprint (metadata) with the
payload (data).
Q6: When executing a complex query in SQL Server 2025, you need to ensure that records
from the EMPLOYEE table are returned even if they do not have a matching record in the
DEPARTMENT table. Which operation is MOST APPROPRIATE? A) INNER JOIN B) NATURAL
JOIN C) LEFT OUTER JOIN D) CROSS JOIN
● The Answer: C (LEFT OUTER JOIN)
● Distractor Analysis:
○ A is incorrect: An inner join drops all unmatched rows.
○ B is incorrect: A natural join acts as an inner join based on identical column names
and drops unmatched rows.
○ D is incorrect: A cross join produces a Cartesian product, matching every employee
