Business Rules and E-R Modeling – Exam Review Flashcards

A comprehensive set of Q&A flashcards covering key terms and concepts from the lecture on business rules, data naming, and Entity-Relationship modeling. Use these to test understanding of entities, attributes, relationships, cardinality, and the role of business rules in database design.

What is the primary purpose of business rules in data modeling?

To state constraints that govern how data is created, stored, and manipulated in an information system.

Which three basic components make up an Entity-Relationship (E-R) model?

Entities, attributes, and relationships.

Name any three of Embarcadero’s “Seven Deadly Sins of Database Design.”

Poor documentation, lack of normalization, improper storage of reference data, not using foreign keys, not using primary keys, (plus two others: ignoring naming standards, and inadequate testing).

Why is conceptual data modeling important for organizations?

It ensures data integrity, provides a stable foundation for information systems, and facilitates communication among designers, programmers, and end users.

What is a strong entity type?

An entity type that can exist independently of other entities and has its own unique identifier.

What defines a weak entity type?

An entity type that depends on a strong entity for its existence and is identified by a partial identifier combined with the owner’s identifier.

Give an example of an associative entity in the lecture notes.

CERTIFICATE (representing the relationship between EMPLOYEE and COURSE with attributes such as Certificate Number and Date Completed).

What are required attributes?

Attributes that must have a value for every instance of an entity.

How do optional attributes differ from required attributes?

Optional attributes may be left null for some entity instances.

What is a composite attribute?

An attribute that can be broken into meaningful component parts, such as Address into Street, City, State, and Postal Code.

Define a multivalued attribute.

An attribute that can have multiple values for a single entity instance, e.g., Skill for EMPLOYEE.

What is a derived attribute?

An attribute whose value can be calculated from other stored attributes, such as Years Employed from Date Employed.

What is an identifier (primary key) in the context of entity types?

An attribute or set of attributes that uniquely distinguishes each instance of an entity type.

Explain minimum cardinality in a relationship.

The minimum number of instances of one entity that must be related to each instance of another entity (0 for optional, 1 for mandatory).

What symbol represents ‘many’ in maximum cardinality using crow’s-foot notation?

A three-pronged crow’s foot at the end of the relationship line.

What is the degree of a relationship?

The number of entity types that participate in the relationship (unary, binary, ternary, etc.).

Describe a unary relationship with an example from the notes.

A relationship among instances of one entity type, e.g., EMPLOYEE "Manages" EMPLOYEE.

When should a relationship be converted into an associative entity? (Give one condition.)

When the relationship has attributes of its own or when all participating cardinalities are ‘many.’

List two benefits of capturing business rules in a centralized repository.

High maintainability and the ability to automate enforcement across multiple applications.

State two characteristics of a good business rule.

Declarative and precise (other valid answers: atomic, consistent, expressible, distinct, business-oriented).

What guideline helps decide if something should be modeled as an attribute or as a separate entity?

If the data has multiple values or is shared by multiple instances, model it as a separate entity.

How does time stamping support regulatory compliance?

It records when data values were valid or changed, preserving an accurate history for audits.

What does the term ‘bill-of-materials’ illustrate in ER modeling?

A many-to-many unary relationship where an ITEM can have components that are also ITEMs.

Why convert ternary relationships to associative entities in most tools?

To accurately represent participation constraints and because many CASE tools cannot handle ternary relationships directly.

Give an example of mandatory participation in both directions between two entities.

DEPARTMENT ‘Has Workers’ EMPLOYEE: each department must have at least one employee, and each employee must belong to exactly one department.

What naming convention is recommended for entity types?

Use singular nouns in capital letters that are specific, concise, and meaningful to the organization.

Why should data object names avoid technical jargon?

Because names should relate to business meaning, making them understandable to end users and maintainable by businesspeople.

What SQL object can combine data from multiple tables to simplify user queries, as used in the ‘Orders for Customers’ example?

A database view (virtual table).

How does the ‘Orders for Customers’ view help end users?

By presenting CUSTOMER and ORDER data in a single virtual table, making queries about order counts or status easier to write.

In cardinality constraints, what does a minimum of zero indicate?

That participation of the entity in the relationship is optional.

What does a weak entity’s partial identifier signify?

It uniquely identifies the weak entity only in combination with the strong entity’s identifier.

Why is documenting attribute value changes important for identifiers?

Because non-intelligent identifiers should never change; if they must, the entity instance should be deleted and recreated.

What is an integrity constraint in the context of databases?

A rule that maintains valid data values and relationships, often derived from business rules.

Describe the ‘Is Qualified’ relationship between PROFESSOR and COURSE.

Each professor must be qualified for at least one course, and each course must have at least two qualified professors.

What is the purpose of the SCHEDULE associative entity?

To record which professor teaches which course in a specific semester, with a unique identifier combining Professor, Course, and Semester (or a surrogate ScheduleID).

Give an example of a specific maximum cardinality constraint from the notes.

An employee may work on at most five projects simultaneously in the ‘IS ASSIGNED TO’ relationship.

What is the Business Rules Paradigm’s stance on rule ownership and maintenance?

Businesspeople, not technicians, should be able to define and modify business rules stored centrally and enforced across systems.

List the five recommended steps for creating a data object name.

Prepare a definition, remove insignificant words, arrange words meaningfully, assign standard abbreviations, and ensure uniqueness.

Why avoid multiple definitions for a single entity type such as STUDENT?

To prevent confusion and promote data integrity; use one general definition with status attributes for specific cases.

What are the two main advantages of using many-to-many relationships for historical data?

They allow multiple associations over time and facilitate adding time stamps for each association.

Explain optional one cardinality using the PERSON ‘Is Married To’ relationship.

Each person may be married to zero or one other person; participation is optional in both directions.

What does a crow’s-foot with a bar indicate in Visio notation?

Mandatory many participation (minimum 1, maximum many).

Why might you convert a one-to-many relationship to many-to-many in a future-proof design?

To accommodate potential historical tracking or regulatory requirements without restructuring the data model later.

Which guideline helps choose strong verbs for relationship names?

Use present-tense transitive verbs that describe the action clearly (e.g., Assigned To, Supplies, Teaches).

How should you document mutually exclusive relationships in definitions?

Explain that an entity instance can participate in only one of the mutually exclusive relationships (e.g., Plays On vs. Works On for a student).

What is the effect of converting a many-to-many relationship into an associative entity on cardinality?

It creates two one-to-many relationships, each with the ‘many’ side terminating at the associative entity.

Why are CASE tools like Microsoft Visio sometimes insufficient for full rule representation?

They may lack notation for every entity, attribute, or relationship type, requiring supplemental annotations.