I85PREP
⌘ K
GBDS study materials
GBDS study resources

GBDS practice questions

One question at a time. Build your GBDS understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Business Analysis and Requirements

In a business analysis context, what does a requirement's 'traceability' primarily enable?

  1. Ranking stakeholders by their level of influence over the solution
  2. Documenting the link between a requirement, its source, and the design and test artifacts that satisfy it Correct answer.
  3. Measuring the performance of the software once it is deployed to production
  4. Estimating the total cost of a project before the business case is approved

Explanation

Traceability records the relationships between a requirement and its origin (stakeholder, regulation, or goal) and the downstream artifacts such as design, build, and test cases that realize it. This supports impact analysis when requirements change and provides evidence that each requirement has been delivered.

Question 2Business Analysis and Requirements

A business analyst is deciding whether to model a process using a flow diagram or a written procedure. Which consideration most directly favors a visual process model?

  1. The documentation must be legally binding and signed by an executive
  2. The process has no variation and involves a single actor
  3. The organization requires that all documentation be stored in a database
  4. The audience needs to see sequence, decision points, and handoffs between roles at a glance Correct answer.

Explanation

Visual process models are chosen when clarity of sequence, branching logic, and role handoffs matters to the audience. A diagram communicates flow more efficiently than prose when multiple paths and participants are involved.

Question 3Business Analysis and Requirements

Which of the following best describes the purpose of a 'business case' in business analysis?

  1. To list every user story that the delivery team will build
  2. To justify the investment by comparing expected benefits with costs and risks Correct answer.
  3. To record the minutes of stakeholder interviews
  4. To define the database schema for the proposed system

Explanation

A business case establishes whether an initiative is worth undertaking by weighing anticipated benefits against costs, risks, and alternatives. It supports a go/no-go decision before significant investment is committed.

Question 4Business Analysis and Requirements

During elicitation, a stakeholder describes how a task 'should work' rather than how it is performed today. What type of requirement is the stakeholder expressing?

  1. A regulatory compliance obligation
  2. A current-state observation of existing practice
  3. A non-functional constraint on system availability
  4. A future-state requirement describing a desired capability Correct answer.

Explanation

Statements about how work should be done describe the desired future state and therefore belong to requirements rather than current-state analysis. Current-state descriptions capture what happens now, which is a different artifact.

Question 5Business Analysis and Requirements

What is the primary role of a 'definition of ready' in an agile delivery context?

  1. To set shared criteria a backlog item must meet before the team accepts it into a sprint Correct answer.
  2. To list the stakeholders who approved the release
  3. To record the acceptance tests that prove a completed story works
  4. To document the retrospective actions agreed at the end of a sprint

Explanation

A definition of ready is an agreed checklist ensuring a backlog item is sufficiently understood, estimated, and testable before work begins. It reduces mid-sprint surprises caused by unclear or incomplete stories.

Question 6Business Analysis and Requirements

A business analyst must choose between a 'current-state' and a 'future-state' process description. Which situation calls for a current-state model first?

  1. Estimating the benefits of an already-approved transformation
  2. Designing a brand-new service with no existing process
  3. Diagnosing where delays and rework originate in today's operation Correct answer.
  4. Selecting a vendor for a system that has not yet been scoped

Explanation

A current-state model is most valuable when the goal is to understand existing pain points such as delays, bottlenecks, and rework. It provides the baseline against which improvements can be diagnosed.

Question 7Business Analysis and Requirements

Which statement accurately characterizes a 'non-functional requirement'?

  1. It describes a discrete function the user can invoke in the interface
  2. It specifies a quality attribute or constraint such as performance, security, or usability Correct answer.
  3. It captures the reason a stakeholder wants a feature
  4. It records the priority assigned to a user story

Explanation

Non-functional requirements define quality attributes and constraints that shape how well functions are delivered, such as response time, security posture, or accessibility. They are often expressed as measurable targets so they can be verified.

Question 8Business Analysis and Requirements

What distinguishes 'validation' from 'verification' of requirements?

  1. Validation happens once at kickoff; verification happens only after release
  2. Validation is performed only by developers; verification is performed only by business stakeholders
  3. Validation concerns cost; verification concerns schedule
  4. Validation confirms the requirements describe the right solution; verification confirms they are correctly specified Correct answer.

Explanation

Validation asks whether the requirement reflects a real stakeholder need and the right solution, while verification checks that the requirement is complete, consistent, and correctly written. Both are quality activities but answer different questions.

Question 9Business Analysis and Requirements

A stakeholder says the new portal 'must be fast'. Which revision best turns this into a testable requirement?

  1. Users shall perceive the portal as quick and intuitive
  2. Search results shall be returned within two seconds for 95 percent of queries under normal load Correct answer.
  3. The portal shall have a modern, responsive user interface
  4. The portal shall be faster than the legacy system

Explanation

A testable requirement states a measurable condition, including the metric, threshold, and conditions of measurement. Specifying a two-second response for 95 percent of queries under normal load allows objective verification.

Question 10Business Analysis and Requirements

A business analyst is preparing for a workshop with executives who have limited time and conflicting priorities. Which facilitation approach is most appropriate?

  1. Run an open-ended brainstorming session with no agenda to surface all ideas
  2. Invite every operational user to maximize the number of perspectives
  3. Distribute pre-reading, focus the session on decisions needed, and capture open items for follow-up Correct answer.
  4. Record the session silently and circulate a transcript afterward

Explanation

When time is scarce and priorities conflict, effective facilitation front-loads context through pre-reading and concentrates live discussion on decisions. Open items are parked and followed up so the session stays focused.

Question 11Business Analysis and Requirements

A stakeholder repeatedly requests a feature that is outside the agreed project scope. What is the most appropriate business analysis response?

  1. Quietly add the feature to the backlog and let the team decide
  2. Log the request, assess its impact and value, and route it through the agreed change control process Correct answer.
  3. Reject the request without discussion to protect the baseline
  4. Implement the feature immediately to preserve the relationship

Explanation

Out-of-scope requests should be captured, evaluated for value and impact, and decided through the established change control mechanism. This preserves the baseline while giving the request fair consideration.

Question 12Business Analysis and Requirements

Which technique is most useful for prioritizing a large set of requirements when stakeholders must make explicit trade-offs among many items?

  1. MoSCoW prioritization Correct answer.
  2. Swimlane diagramming
  3. Entity-relationship modeling
  4. Data dictionary authoring

Explanation

MoSCoW sorts requirements into Must, Should, Could, and Won't categories, forcing stakeholders to make explicit trade-offs about what is essential. It is designed for prioritization rather than modeling or documentation.

Question 13Business Analysis and Requirements

In a requirements workshop, two departments claim ownership of the same approval step. What is the most effective way for the business analyst to resolve the conflict?

  1. Escalate immediately to the project sponsor without gathering facts
  2. Facilitate a discussion to clarify decision rights and document the agreed ownership Correct answer.
  3. Assign ownership to the department that spoke first
  4. Defer the decision by leaving the approval step undefined

Explanation

Conflicting ownership is best resolved by clarifying decision rights with the parties involved and documenting the outcome. Premature escalation or deferral leaves the requirement ambiguous and risks rework.

Question 14Business Analysis and Requirements

Why is a 'glossary' valuable in a large requirements effort?

  1. It replaces the need for a data dictionary in all cases
  2. It lists the technical architecture of the solution
  3. It gives stakeholders and the team a single agreed definition of domain terms used across artifacts Correct answer.
  4. It records the approval history of each requirement

Explanation

A glossary establishes shared definitions so terms are used consistently across requirements, models, and tests. This reduces ambiguity and miscommunication between business and technical audiences.

Question 15Business Analysis and Requirements

A business analyst is deciding how much detail to include in requirements for a team using short iterations. Which approach aligns best with agile practice?

  1. Document only verbal agreements to avoid overhead
  2. Write requirements once and never revise them
  3. Fully specify every requirement before any development begins
  4. Elaborate requirements just in time, at the level of detail needed for the next iteration Correct answer.

Explanation

Agile practice favors progressive elaboration, detailing requirements just in time as the team approaches the work. This avoids the waste of over-specifying items that may change or never be built.

Question 16Business Analysis and Requirements

What is the main purpose of a 'context diagram' in business analysis?

  1. To show the solution as a single process and identify the external entities that interact with it Correct answer.
  2. To rank requirements by business value
  3. To model the database entities and their attributes
  4. To decompose internal processes into detailed sub-steps

Explanation

A context diagram frames the scope by depicting the solution as one process and the external actors or systems that exchange information with it. It clarifies boundaries before deeper decomposition.

Question 17Business Analysis and Requirements

A stakeholder provides a requirement that contradicts an approved regulatory rule. What should the business analyst do?

  1. Rewrite the regulation to accommodate the requirement
  2. Flag the conflict, document the regulatory constraint, and resolve the requirement with the stakeholder Correct answer.
  3. Ignore the regulation because it was not mentioned in the project charter
  4. Accept the requirement because the stakeholder is the domain expert

Explanation

Regulatory constraints are mandatory and take precedence over conflicting stakeholder preferences. The analyst should surface the conflict, document the constraint, and work with the stakeholder to reach a compliant requirement.

Question 18Business Analysis and Requirements

Which characteristic of a well-written requirement ensures it can be tested objectively?

  1. Brevity
  2. Anonymity
  3. Ambiguity
  4. Verifiability Correct answer.

Explanation

Verifiability means the requirement is stated so that a test or inspection can determine whether it has been met. Without measurable, observable criteria, a requirement cannot be objectively confirmed.

Question 19Business Analysis and Requirements

A project team is selecting a technique to understand how customers actually navigate a purchase journey, including emotions and pain points. Which technique fits best?

  1. State transition diagram
  2. Decision table
  3. Customer journey mapping Correct answer.
  4. CRUD matrix

Explanation

Customer journey mapping visualizes the end-to-end experience, including touchpoints, emotions, and pain points across stages. It is designed for experience-oriented analysis rather than system logic.

Question 20Business Analysis and Requirements

Why should a business analyst distinguish 'assumptions' from 'constraints' in a requirements package?

  1. Assumptions are always false, while constraints are always optional
  2. Assumptions are believed true but unverified, while constraints are imposed limits that cannot be changed Correct answer.
  3. Assumptions apply only to budgets, while constraints apply only to schedules
  4. Assumptions are technical, while constraints are business-related

Explanation

Assumptions are statements accepted as true without proof and must be validated, whereas constraints are externally imposed boundaries such as regulations, budgets, or platforms. Treating them separately clarifies risk and decision-making.

Question 21Business Analysis and Requirements

A business analyst observes that a proposed solution meets stated requirements but users still struggle to complete a key task. Which analysis gap does this most likely reveal?

  1. The change control board met too frequently
  2. The requirements captured functional behavior but under-specified usability and user-context needs Correct answer.
  3. The project charter was never signed
  4. The solution failed unit testing

Explanation

A solution that satisfies functional requirements yet frustrates users typically reflects insufficient attention to usability and the real work context. Non-functional and user-experience requirements should have captured those needs.

Question 22Business Analysis and Requirements

A business analyst must reconcile two stakeholder groups whose requirements directly conflict due to different incentive structures. Which approach best supports a durable resolution?

  1. Surface the underlying interests behind each position and negotiate criteria both groups can accept Correct answer.
  2. Choose the requirement from the group with the larger budget
  3. Average the two requirements to produce a compromise specification
  4. Escalate both requirements unchanged and let the sponsor pick arbitrarily

Explanation

Positions often mask underlying interests; uncovering those interests allows the analyst to find solutions that address both parties' real needs. This produces more durable agreements than splitting differences or appealing to power alone.

Question 23Business Analysis and Requirements

When is a 'decision table' preferable to a narrative description of business rules?

  1. When multiple conditions combine to determine different outcomes and completeness must be verified Correct answer.
  2. When the primary goal is to show the emotional experience of a user
  3. When the process has a single linear path with no branches
  4. When the requirement concerns a quality attribute such as availability

Explanation

Decision tables systematically enumerate combinations of conditions and their resulting actions, making gaps and overlaps visible. This is valuable when complex, interacting rules determine outcomes.

Question 24Business Analysis and Requirements

A business analyst inherits a requirements baseline with poor traceability. What is the most significant risk this creates during a late-stage change?

  1. Stakeholders will refuse to attend future workshops
  2. The solution will fail security certification
  3. The team cannot reliably determine which designs and tests are affected by the change Correct answer.
  4. The project will automatically exceed its budget

Explanation

Without traceability links, a change cannot be reliably propagated to the design elements and test cases that depend on the affected requirement. This raises the risk of incomplete updates and undetected defects.

Question 25Business Analysis and Requirements

A sponsor asks the business analyst to recommend whether to build a custom solution or buy a commercial product. Which analysis most directly supports this decision?

  1. A retrospective of the previous project's delivery velocity
  2. A detailed entity-relationship model of the proposed database
  3. A comparison of each option against the prioritized requirements, costs, risks, and fit with the operating model Correct answer.
  4. A glossary of domain terminology

Explanation

A build-versus-buy decision requires evaluating each option against prioritized needs, total cost, risk, and organizational fit. This structured comparison gives the sponsor a defensible basis for choice.

Question 26Business Analysis and Requirements

A business analyst is defining a requirement for how a customer's identity is verified before an account is unlocked. Which categorization places this requirement correctly?

  1. A business rule, because it constrains who may access an account
  2. A functional requirement, because it describes behavior the solution must perform Correct answer.
  3. A non-functional requirement, because it concerns security
  4. A project constraint, because it limits what the team can deliver

Explanation

Functional requirements describe what the solution must do: specific behaviors, calculations, and processing the system performs in response to an event. Verifying identity before unlocking an account is behavior the solution executes, so it belongs in the functional category. Security qualities such as response time or encryption strength would instead be non-functional.

Question 27Business Analysis and Requirements

A business analyst is planning elicitation for a replacement of a legacy billing system. Which activity best reduces the risk that undocumented legacy behavior is lost?

  1. Interviewing only the project sponsor about desired outcomes
  2. Reverse-engineering the current system and observing how staff actually use it Correct answer.
  3. Reviewing the existing system's documentation and user manuals
  4. Sending a survey asking users to list every feature they want

Explanation

Legacy behavior that matters is often undocumented and embedded in workarounds, so examining the system itself and observing real use surfaces rules that documentation omits. This is the core purpose of current-state analysis before defining the future state. Documents and sponsor interviews are useful inputs but cannot reveal tacit operational practice.

Question 28Business Analysis and Requirements

A business analyst is drafting acceptance criteria for a requirement that a refund be processed within a stated period. Which formulation is the strongest acceptance criterion?

  1. Refunds should be handled promptly and without unnecessary delay
  2. Refunds must be processed quickly enough to satisfy customers
  3. The system shall support refund processing for all eligible transactions
  4. A refund request submitted before 17:00 on a business day is credited within two business days Correct answer.

Explanation

A strong acceptance criterion is specific, measurable, and objectively verifiable so that testing can produce a clear pass or fail. Stating the trigger, the condition, and the observable outcome with a defined time window achieves this. Vague terms such as promptly or quickly cannot be tested consistently.

Question 29Business Analysis and Requirements

A business analyst is using a model to show how a customer moves through order placement, payment, and fulfillment, including decision points where an order may be declined. Which model type is most appropriate?

  1. A process model depicting the flow of activities, gateways, and events Correct answer.
  2. A state machine diagram showing the permitted statuses of an order
  3. A use-case diagram showing actors and their goals
  4. An entity-relationship diagram showing data entities and their relationships

Explanation

Process models such as flowcharts or BPMN represent the sequence of activities, decision gateways, and events across a business process, which matches the need to show movement and branching. The decision points where an order may be declined are gateways in the flow. Data, actor-goal, and status models answer different questions.

Question 30Business Analysis and Requirements

During requirements analysis, a business analyst finds that two approved requirements cannot both hold: one mandates that all customer records be purged after 12 months, and another mandates retention for 7 years for audit. Which action best reflects sound requirements management?

  1. Record both requirements and defer the conflict to the development team to resolve during build
  2. Merge the two requirements into a single statement that leaves the retention period unspecified
  3. Select the longer retention period because audit obligations usually outweigh other concerns
  4. Log the conflict, trace both requirements to their sources, and escalate to the owners to decide which governs Correct answer.

Explanation

Conflicting requirements must be made visible and resolved by the people who own the underlying need, with traceability showing where each requirement originated. The analyst's role is to surface and facilitate the decision, not to choose the outcome unilaterally. Deferring to developers or blurring the requirement leaves an untestable ambiguity.

Question 31Data Modeling and Database Design

In a relational schema, which property must hold for a candidate key?

  1. It must be a single attribute rather than a composite of several attributes
  2. It must be unique and minimal, with no proper subset that is also unique Correct answer.
  3. It must reference a primary key in another relation to enforce referential integrity
  4. It must be declared as the primary key of the relation at creation time

Explanation

A candidate key is a minimal superkey: it uniquely identifies each tuple, and removing any attribute destroys that uniqueness. Minimality is what distinguishes a candidate key from an arbitrary superkey. A relation may have several candidate keys, only one of which becomes the primary key.

Question 32Data Modeling and Database Design

A relation stores OrderID and ProductID together, and the pair uniquely identifies each row, but no single attribute does. What is this pair called?

  1. A partial dependency
  2. A foreign key
  3. A composite key Correct answer.
  4. A surrogate key

Explanation

A composite key is a key made of two or more attributes that together provide uniqueness. Neither OrderID nor ProductID alone is unique, so the combination is required. A partial dependency is a normalization concept, not a key type.

Question 33Data Modeling and Database Design

Which statement best describes the purpose of a foreign key constraint?

  1. It prevents deletion of the referenced relation while any query is running
  2. It ensures that values in the referencing column match an existing key value in the referenced relation Correct answer.
  3. It automatically creates an index on the referenced column and enforces uniqueness
  4. It guarantees that every value in the referencing column is distinct across rows

Explanation

A foreign key enforces referential integrity: each non-null value in the referencing attribute must appear as a key value in the referenced relation. This prevents orphaned rows that point to nonexistent parents. Uniqueness is the job of a primary or unique constraint, not the foreign key.

Question 34Data Modeling and Database Design

In an entity-relationship model, what does the cardinality ratio of a relationship express?

  1. The order in which entities must be created during schema deployment
  2. The number of attributes attached to each participating entity type
  3. The number of indexes required to support joins on the relationship
  4. The number of entity instances that can be associated with one instance of another entity type Correct answer.

Explanation

Cardinality describes how many instances of one entity type may relate to a single instance of another, typically expressed as 1:1, 1:N, or M:N. It constrains the structure of the relationship, not the attributes or physical storage. Participation constraints (total or partial) are a separate concept.

Question 35Data Modeling and Database Design

Which normal form eliminates transitive dependencies of non-key attributes on the primary key?

  1. Third normal form Correct answer.
  2. Boyce-Codd normal form only
  3. First normal form
  4. Second normal form

Explanation

Third normal form requires that every non-key attribute depend nontrivially on the key and nothing but the key, removing transitive dependencies. Second normal form removes partial dependencies on part of a composite key. Boyce-Codd normal form is stricter still, dealing with certain candidate-key anomalies.

Question 36Data Modeling and Database Design

A table has columns (StudentID, CourseID, InstructorName), where each course is taught by exactly one instructor and InstructorName depends only on CourseID. Which normalization problem does this illustrate?

  1. A partial dependency on StudentID only
  2. A violation of domain atomicity in first normal form
  3. A transitive dependency from the composite key through CourseID Correct answer.
  4. A multivalued dependency requiring fourth normal form

Explanation

The composite key is (StudentID, CourseID), and InstructorName depends on CourseID alone, which is not the whole key. Because CourseID is part of the key, this is a transitive dependency that violates third normal form; the instructor should be moved to a course relation.

Question 37Data Modeling and Database Design

Which ER construct is most appropriate when an entity type has no key attributes of its own and exists only in the context of another entity type?

  1. A weak entity type with a partial key and identifying relationship Correct answer.
  2. A generalization hierarchy with a disjoint constraint
  3. A strong entity type with a surrogate primary key
  4. An associative entity type resolving a many-to-many relationship

Explanation

A weak entity type lacks a key of its own and is identified through an identifying relationship with an owner entity type, using a partial key plus the owner's key. It is drawn with a double rectangle and the relationship with a double diamond. Surrogate keys would make it strong instead.

Question 38Data Modeling and Database Design

Converting a many-to-many relationship between two entity types into relations typically produces which result?

  1. A new relation whose key is the combination of the keys of both entity types Correct answer.
  2. A foreign key added to one of the two entity relations
  3. A merged relation combining all attributes of both entity types
  4. A deletion of both entity relations in favor of a single view

Explanation

A many-to-many relationship cannot be represented by a foreign key in either relation without redundancy. The standard mapping creates a separate junction relation containing the keys of both participants, whose composite key is their combination, plus any relationship attributes.

Question 39Data Modeling and Database Design

A designer stores a repeating list of phone numbers in a single column as comma-separated text. Which design principle does this violate?

  1. First normal form's requirement for atomic attribute values Correct answer.
  2. Third normal form's prohibition of transitive dependencies
  3. The requirement that every relation have a surrogate key
  4. Referential integrity of the phone attribute

Explanation

First normal form requires each attribute value to be atomic, with no repeating groups or sets encoded inside one cell. Storing a delimited list makes querying, indexing, and enforcing uniqueness on individual numbers difficult. The correct design is a separate phone relation keyed by the owner's identifier and the number.

Question 40Data Modeling and Database Design

Which best describes the difference between a conceptual data model and a physical data model?

  1. The conceptual model captures business entities and relationships independent of technology, while the physical model specifies storage structures, types, and indexes Correct answer.
  2. The conceptual model is produced after the physical model to document it
  3. The conceptual model applies only to NoSQL systems, while the physical model applies to relational systems
  4. The conceptual model names tables and columns, while the physical model names entities and relationships

Explanation

Conceptual modeling focuses on business meaning: entities, relationships, and constraints, without committing to a DBMS. Physical modeling translates that into concrete tables, data types, indexes, partitioning, and storage options for a specific platform. Logical modeling sits between them.

Question 41Data Modeling and Database Design

A relation has a composite primary key (A, B) and a non-key attribute C that depends on B alone. Which action moves the schema toward third normal form?

  1. Declare C as part of the primary key so the dependency becomes full
  2. Create an index on B to speed up lookups of C
  3. Add a surrogate key to the original relation and keep C where it is
  4. Move C into a new relation keyed by B and remove it from the original relation Correct answer.

Explanation

Because C depends on only part of the composite key, the relation violates second normal form. Decomposing so that C lives in a relation keyed by B removes the partial dependency and also satisfies third normal form for that attribute. Indexes and surrogate keys do not change the dependency structure.

Question 42Data Modeling and Database Design

In crow's foot notation, an entity with a single vertical bar and a crow's foot on the relationship line indicates what?

  1. One or many instances Correct answer.
  2. Zero or one instance
  3. Exactly one instance
  4. Zero or many instances

Explanation

In crow's foot notation, the inner symbol gives the minimum and the outer symbol the maximum. A bar means mandatory (minimum one) and a crow's foot means many, so the combination reads as one or many. A circle would indicate an optional minimum of zero.

Question 43Data Modeling and Database Design

Which anomaly is directly prevented by placing a relation in third normal form rather than second normal form?

  1. Insertion anomaly caused by storing a fact about a non-key attribute that depends transitively on the key Correct answer.
  2. Loss of referential integrity when a parent row is deleted
  3. Duplicate rows caused by the absence of any key
  4. Inability to store a multi-valued attribute in a single column

Explanation

Transitive dependencies allow the same fact, such as a department name tied to a department code, to be repeated across many rows. That repetition causes update, insertion, and deletion anomalies. Third normal form removes these by separating the transitively dependent attributes into their own relation.

Question 44Data Modeling and Database Design

A table stores CustomerID, CustomerName, and CustomerName depends only on CustomerID, while the table's primary key is (OrderID, CustomerID). Which statement is true?

  1. CustomerName violates first normal form because it is not atomic
  2. CustomerName fully depends on the whole composite key
  3. CustomerName is transitively dependent on OrderID and the table violates third normal form
  4. CustomerName is partially dependent on CustomerID and the table violates second normal form Correct answer.

Explanation

The primary key is composite, and CustomerName depends on only CustomerID, one component of that key. This is a partial dependency, which violates second normal form. The fix is to place customer attributes in a separate relation keyed by CustomerID.

Question 45Data Modeling and Database Design

Which of these best characterizes a surrogate key compared with a natural key?

  1. It is derived from business data and carries meaning to users
  2. It must always be a composite of all business identifiers
  3. It cannot be used as a primary key in a relational table
  4. It is system-generated with no business meaning and is stable against business changes Correct answer.

Explanation

A surrogate key is an artificially generated identifier, such as an auto-increment integer or UUID, with no inherent business meaning. Because business attributes can change, surrogate keys tend to remain stable and simplify foreign key references. Natural keys come from real-world data and may require rework when business rules change.

Question 46Data Modeling and Database Design

In a generalization hierarchy in an ER model, what does a disjoint constraint specify?

  1. Subtypes must share all attributes of the supertype
  2. An entity instance may belong to at most one subtype Correct answer.
  3. Every supertype instance must belong to some subtype
  4. An entity instance may belong to more than one subtype simultaneously

Explanation

A disjoint constraint means the subtypes do not overlap, so an instance of the supertype can be a member of at most one subtype. An overlapping constraint permits membership in several subtypes at once. Completeness, whether every supertype instance must belong to a subtype, is a separate constraint.

Question 47Data Modeling and Database Design

A logical schema has a relation R(A, B, C) with functional dependencies A to B and B to C, where A is the primary key. Which normal form does R currently satisfy at best?

  1. Boyce-Codd normal form
  2. First normal form only
  3. Second normal form but not third normal form Correct answer.
  4. Third normal form but not Boyce-Codd normal form

Explanation

A is a single-attribute key, so there can be no partial dependency and R is in second normal form. However, B to C is a transitive dependency of the non-key attribute C on the key through B, so third normal form is violated. Decomposing into (A, B) and (B, C) resolves it.

Question 48Data Modeling and Database Design

Which statement about Boyce-Codd normal form is accurate?

  1. It is weaker than third normal form and permits some transitive dependencies
  2. It eliminates the need for foreign key constraints in the schema
  3. It requires that every non-trivial functional dependency have a superkey as its determinant Correct answer.
  4. It applies only to relations with composite primary keys

Explanation

BCNF strengthens third normal form by requiring every non-trivial functional dependency to have a superkey on the left side. This removes anomalies involving overlapping candidate keys that third normal form can allow. It does not affect referential integrity, which foreign keys enforce separately.

Question 49Data Modeling and Database Design

A team must record that each employee can be assigned to several projects, and each project can have several employees, with the assignment date stored per pairing. Which design correctly models this?

  1. Create an Assignment table with EmployeeID, ProjectID, and AssignmentDate, keyed by EmployeeID and ProjectID Correct answer.
  2. Add an EmployeeID foreign key column to the Project table
  3. Create a single table combining all Employee and Project attributes with no key
  4. Add a ProjectID foreign key column to the Employee table

Explanation

A many-to-many relationship with attributes on the relationship itself maps to a junction relation containing both foreign keys plus the relationship attribute. The composite of the two foreign keys forms the key, and AssignmentDate is a non-key attribute. Placing a foreign key in either entity table would allow only one association per row.

Question 50Data Modeling and Database Design

Which scenario most clearly indicates that denormalization is justified in a reporting schema?

  1. A developer prefers fewer tables when writing SQL
  2. The schema has not yet been reviewed by the data modeling team
  3. A frequently executed join across several large tables dominates query cost, and the duplicated data is controlled by refresh jobs Correct answer.
  4. The database engine does not support primary keys

Explanation

Denormalization trades redundancy for read performance and is justified when measured join cost is significant and the duplicated data can be kept consistent, for example through scheduled loads in a warehouse. It should be a deliberate, documented decision based on workload evidence.