Business Rules

Put simply, what do you do to find business rules?

Extract the nouns and determine the relationships between them

When finding business rules, after you have identified the nouns, what do you do next?

Find the relationship between them (cardinality)

Use context clues to find what the business wants:

“We want…”
“We need…”
“We only…”

What two business rules can you often pull out of one sentence describing something at a business?

1. The entity

2. The cardinality

Example: "We'd like to keep track of the star actors appearing in each movie"
Rule 1: Record stars in each movie
Rule 2: Many-to-many relationship between movies and actors (a movie can have many actors, an actor can star in many movies)

What kind of clue is in the following business rule sentence?

“Not all of our movies have star actors.”

Minimum cardinality

The minimum number of actors in each movie is zero, not one.

The circle on the ACTOR side is the key symbol; it means zero is allowed. The crow's foot next to it means many is the maximum. Together they read "zero or more." If every movie were required to have at least one actor, you would replace the circle with a straight line, making it "one or more."

What business rule clues can be found in the following sentence? What type of rule is it?

“Customers like to know each actor’s real name and date of birth.”

The attributes of the entity

Structural rule (tells what data should be stored)

What business rule clues can be found in the following sentence? What does it mean structurally?

"We track only actors who appear in the movies in our inventory."

An actor must be linked to at least one of the theater’s movies.

Orphan actors can’t be present in the database without a movie connection.

What kind of business rule clue can be found in the following sentence? What kind of rule is it?

"We rent videos only to people who have joined our video club"

Rentals can only be given to/associated with a club member

Relationship rule

What kind of business rule clue can be found in the following sentence?

"A customer may check out multiple video tapes at any given time,"

Cardinality

One member can check out many videos (one-to-many)

What kind of business rule can be found in the following sentence?

"We just track current rentals. We don't keep track of any rental histories"

Structural

Tells you what the database does and does not store

What are the three types of business rules? What do they do?

Structural rules define data and relationships and show up in an ERD.

Procedural rules describe how the business operates around that data.

Programmatic rules describe what the software must automatically enforce in the future.

What type of business rule can be found in the following sentence? What does it mean in the database?

"A patient must be admitted by a licensed physician before being assigned a room."

Procedural

This is a business policy. It does not change the shape of an ERD, but explains why certain relationships and constraints exist in the design.

Which type of business rule directly affects an ERD?

Which do not?

Do affect:

Structural (define entites, attributes and relationships)

Do not affect:

Procedural and programmatic (provide context and operational logic but do not appear as lines or boxes in a diagram)

What does "zero or more" mean in a business rule?

When would it be used?

It means the minimum is zero and the relationship is optional.

Use it when an entity does not have to be connected to the other.

Example:
In the movie scenario, "a movie can feature zero or more star actors" reflects that not every movie has a star actor. If it were "one or more," every movie would be required to have one.

What is the difference between "one or more" and "zero or more" in a business rule?

How does this relate to foreign keys?

"One or more" means the relationship is mandatory

"Zero or more" means it is optional (none is allowed)

This is the minimum cardinality. It tells you whether a foreign key can be NULL or not.

Read the ERD and determine the two business rules.

1. A customer can place one or more orders.

2. An order belongs to one and only one customer.

This is a one-to-many. The circle on the ORDER side means a customer can have zero orders (optional minimum). The crow's foot means many. The single bar on CUSTOMER means one and only one customer owns the order.

Read the ERD and determine the two business rules.

1. A student can enroll in one or more courses.

2. A course can be taken by one or more students.

This is a many-to-many. Crow's feet on both sides with a mandatory minimum. Every student must be in at least one course and every course must have at least one student.

ENROLLMENT is the bridge entity resolving the many-to-many. Its two foreign keys, student_id and course_id, together form the composite key identifying each unique enrollment.

Read the ERD and determine the business rules for each relationship.

Relationship 1: physician and prescription

1. A physician can write zero or more prescriptions.

2. A prescription is written by one and only one physician.

One-to-many. The circle means a physician might have zero prescriptions. The double bar on PHYSICIAN means exactly one must own each prescription — cannot be NULL.

Relationship 2: patient and prescription

1. A patient can have zero or more prescriptions.

2. A prescription belongs to one and only one patient.

Same one-to-many pattern. PRESCRIPTION sits in the middle as the "many" side of two separate one-to-many relationships: one to PHYSICIAN and one to PATIENT.