Database Topics - Part II: Knowing How to Develop Data Model for Business Application

• Data Modeling and Data Model
• Developing ER Diagram ← Current Focus
• Developing a Relational Database Schema from an ER Diagram

Part 1: Introduction to ERD

What is an Entity Relationship Diagram (ERD)?

• Entity Relationship Diagram is a visual representation of the data model
• Shows the relationships between different entities in a database system
• Acts as a blueprint for database design

Analogy: Think of an ERD like a house blueprint - just as you can't live in a blueprint but it shows you how the house will be structured, an ERD shows you how your database will be organized but isn't the actual database itself.

Data Model = Blueprint Concept

• Data model is the overview of the data = House blueprint is the overview of a house
• They are abstraction (you can't live in a blueprint)

Key Point: ERDs are abstractions that help us plan and visualize our database structure before actually building it.

Two Well-Known ER Notations

1. Chen Notation

• Creator: Peter Chen
• Website: http://www.csc.lsu.edu/~chen/
• Characteristics: Uses geometric shapes (rectangles, diamonds, ovals)

2. Crow's Foot Notation

• Creator: Gordon Everest
• Website: http://geverest.umn.edu/
• Characteristics: Uses line endings that look like crow's feet to show relationships

Chen's Notation Symbols

Basic Symbols (Part 1)

• Rectangle → Entity type
• Double Rectangle → Weak entity type
• Diamond → Relationship type
• Oval → Attribute

Attribute Types (Part 2)

• Simple Oval → Regular attribute
• Oval with underline → Key attribute (Identifier/Primary key)
• Double Oval → Multivalued attribute
• Oval with component ovals → Composite attribute
• Dashed Oval → Derived attribute

Relationship Constraints (Part 3)

• Double line to entity → Total participation of entity in relationship
• Numbers (1, N, M) → Cardinality ratio for entities in relationship
• (min, max) notation → Structural constraint on participation

Core Concepts

Entity

• Definition: A data object representing meaningful raw data
• Refers to: Entity set (a collection of similar entities)
• Notation: Rectangle containing entity name
• Examples: STUDENT, EMPLOYEE, COURSE

Analogy: Think of an entity like a filing cabinet category - "STUDENT" is the category, and each individual student is a file in that cabinet.

Attributes

• Definition: The properties of an entity
• Example: A student has first name, last name, initial, email, and telephone number
• Notation: Oval-shape containing attribute name

Types of Attributes

1. Simple vs. Composite

• Simple (Atomic)
  • Cannot be divided further
  • Examples: Gender, Student ID, Phone number
• Composite
  • Can be further subdivided into smaller components
  • Examples:
    • Name → First Name, Last Name, Middle Initial
    • Address → Street Address, City, State, Zip Code

Analogy: Simple attributes are like a coin - you can't break it down further while maintaining its meaning. Composite attributes are like a sandwich - you can separate the bread, meat, and vegetables and each part has meaning.

2. Single-valued vs. Multivalued

• Single-valued
  • Has only one value for each entity instance
  • Examples: Student ID, Gender, Date of Birth
• Multivalued
  • Can have multiple values for a single entity instance
  • Examples:
    • Colors of a car (Red roof, Black body, Blue trim, White interior)
    • College degrees (B.Sc., M.Sc., Ph.D.)
    • Phone numbers (Home, Work, Mobile)
    • Notation: Double oval or double circle

Problem with Multivalued Attributes: They violate database normalization rules and make querying difficult.

3. Stored vs. Derived (Processed Attributes)

• Stored
  • Values are directly stored in the database
  • Examples: Birth date, Employee hire date
• Derived
  • Values are calculated from other stored attributes
  • Notation: Dashed oval
  • Examples:
  • $\boxed{\text{Age} = \text{Current Date} - \text{Birth Date}}$
  • $\boxed{\text{Number of Employees} = \text{COUNT(active employees)}}$
  • $\boxed{\text{Total Credits} = \text{SUM(enrolled subjects' credits)}}$

Analogy: Stored attributes are like ingredients you buy and keep in your pantry. Derived attributes are like a recipe result - you don't store the cake, you make it fresh using the ingredients when needed.

Key Attributes

• Definition: Attribute(s) whose values are distinct (unique) for each entity
• Also called: Identifiers or Primary Keys
• Property: Key constraint or uniqueness constraint

Types of Keys:

• Single Key: One attribute uniquely identifies the entity
  • Example: License_Plate_No for CAR entity
• Composite Key: Combination of attributes uniquely identifies the entity
  • Example: (COURSE_NUM + SECTION) for CLASS entity
  • Course CIS-420, Section 1 vs. Course CIS-420, Section 2

Strong vs. Weak Entities

Strong Entity

• Definition: An entity type that has a key attribute
• Characteristics: Can exist independently
• Examples: STUDENT, EMPLOYEE, COURSE

Weak Entity

• Definition: An entity that cannot be uniquely identified by its attributes alone
• Requirements:
  1. Existence-dependent (cannot exist without the strong entity)
  2. Has a primary key that is partially or totally derived from the parent entity
• Examples:
  • DEPENDENT (depends on EMPLOYEE)
  • ORDER_ITEM (depends on ORDER)
  • CLASS_ENROLLMENT (depends on CLASS)

Analogy: Strong entities are like independent adults who can live on their own. Weak entities are like children who depend on their parents for their identity and existence.

Handling Multivalued Attributes

Problem with Multivalued Attributes:

• Violates First Normal Form (1NF)
• Makes querying and data manipulation difficult
• Creates data redundancy issues

Solution Methods:

Method 1: Create Multiple Attributes

• Original: COLOR (multivalued)
• Solution: TOPCOLOR, BODYCOLOR, TRIMCOLOR (separate attributes)

Method 2: Create New Entity with 1:M Relationship

• Create: New entity COLOR with attributes (SECTION, COLOR)
• Relationship: CAR has COLOR (1:M)
• Data Structure:

  CAR_ID | SECTION | COLOR
  001    | Top     | White
  001    | Body    | Blue  
  001    | Trim    | Green
  

Relationships

What is a Relationship?

• Definition: An association between entities
• Indication: Typically indicated by a verb connecting two or more entities
• Example: Students enroll for many courses
• Classification: Should be classified in terms of connectivity constraint

Relationship Types by Connectivity:

• One-to-One (1:1): HUSBAND marry WIFE
• One-to-Many (1:M): PROFESSOR teach COURSE
• Many-to-Many (M:N): STUDENT enroll COURSE

How to Determine Relationships

Forward and Backward Analysis Method:

Steps:

1. Forward (Active Voice): Analyze the relationship from Entity A to Entity B
2. Backward (Passive Voice): Analyze the relationship from Entity B to Entity A
3. Combine: Determine final relationship based on both directions

Examples:

Example 1: Student-Class

• Forward: A student can enroll in many classes (1:M)
• Backward: A class is enrolled by many students (1:M)
• Final Relationship: Many-to-Many (M:N)

Example 2: Customer-Invoice

• Forward: A customer may generate many invoices (1:M)
• Backward: Each invoice is generated by one customer (M:1)
• Final Relationship: One-to-Many (1:M)

Example 3: Employee-Division

• Forward: An employee may manage only one division (1:1)
• Backward: A division is managed by one employee (1:1)
• Final Relationship: One-to-One (1:1)

Degrees of Relationship

By Number of Participating Entities:

• Unary (1): Same entity participates with itself
  • Example: COURSE has prerequisite COURSE
• Binary (2): Two different entities participate
  • Example: PROFESSOR teaches CLASS
• Ternary (3): Three entities participate
  • Example: DOCTOR prescribes DRUG to PATIENT
• N-ary: More than three entities participate

Recursive Relationship

• Definition: Same entity type participates in the relationship more than once with different roles
• Example: EMPLOYEE supervision relationship
  • Same EMPLOYEE entity plays both SUPERVISOR and SUPERVISEE roles
• Business Case: Employee management hierarchy

Analogy: Recursive relationships are like a family tree where the same person can be both a parent and a child, depending on which relationship you're looking at.

Part 2: ERD Development in Details

Mapping Constraints

Definition:

• Constraints: Pre-defined rules which the content of a database must conform to
• Purpose: Ensure data integrity and enforce business rules

Types of Relationship Constraints:

1. Connectivity Constraint
2. Cardinality Constraint
3. Participation Constraint

Connectivity Constraint

• Definition: Used to describe the relationship classification
• Purpose: Show the relation existence and type of relation between entities
• Notation: Numbers (1, M, N) placed near entities

Types:

• One-to-One (1:1)
• One-to-Many (1:M)
• Many-to-Many (M:N)

Examples:

• 1:1 → HUSBAND marry WIFE
• 1:M → PROFESSOR teach COURSE
• M:N → STUDENT enroll COURSE

Cardinality Constraint

• Definition: A specific value assigned for the connectivity
• Purpose: Expresses the range (minimum & maximum) of allowed entity occurrences
• Format: Written as (Min, Max)
• Source: Established from business rules/constraints

Examples:

• (0, 4) → Can have 0 to 4 occurrences
• (1, 1) → Must have exactly 1 occurrence
• (1, 6) → Must have between 1 and 6 occurrences
• (0, 35) → Can have 0 to 35 occurrences

Real-World Business Rules:

• "Maximum credit for a student to register is 21 credits per semester"
• "Minimum subjects for a student to enroll is 5, maximum is 7"
• "A professor can teach 0 up to 3 classes"
• "A class requires at least 1 and only 1 professor"

Chen Notation Examples:

1:M Relationship

PROFESSOR -(0,3)- teach -(1,1)- CLASS

• A professor can teach 0 up to 3 classes
• A class requires at least 1 and only 1 professor

M:N Relationship

STUDENT -(1,6)- enroll -(0,35)- CLASS

• A student can enroll minimum of 1 class or maximum of 6 classes
• A class can have no students or 35 students at most

Participation Constraint

Definition:

Determines whether entity participation in a relationship is required or optional.

Types:

Optional Participation (Partial Participation)

• Definition: Entity occurrence does not require corresponding entity occurrence in relationship
• When: Minimum cardinality is zero (0)
• Notation: Single line in ER diagrams
• Example: (0, M) - can have zero occurrences

Mandatory Participation (Total Participation)

• Definition: Entity occurrence requires corresponding entity occurrence in relationship
• When: Minimum cardinality is one or more (1+)
• Notation: Double line in ER diagrams
• Example: (1, 1) - must have at least one occurrence

Examples:

Course-Class Relationship

Course -(0,M)- generate -(1,1)- Class

• Course: Optional participation (can exist without classes)
• Class: Mandatory participation (must belong to a course)

Customer-Product Relationship

Customer -(1,M)- buy -(0,M)- Product Set  

• Customer: Mandatory participation (must buy something to be a customer)
• Product: Optional participation (can exist without being bought)

Types of Entities (Advanced)

Strong Entities

• Definition: Exists independently of other entities
• Characteristics: Has its own primary key
• Examples: STUDENT, EMPLOYEE, COURSE

Weak Entities

• Definition:
  • Existence depends on another entity
  • Cannot be uniquely identified by its attributes alone
• Requirements:
  1. Existence-dependent on a strong entity
  2. Primary key partially or totally derived from parent entity

Examples of Weak Entities:

• DEPENDENT depends on EMPLOYEE
• ORDER_ITEM depends on ORDER
• CLASS_ENROLLMENT depends on CLASS

Types of Relationships (Advanced)

Weak Relationship (Non-Identifying Relationship)

• Definition: Link among strong entities
• Characteristics:
  • Child entity has its own independent primary key
  • Parent's key becomes foreign key in child
• Example: MANAGER manages DEPARTMENT

Strong Relationship (Identifying Relationship)

• Definition: Link between strong and weak entities
• Characteristics:
  • Parent's primary key becomes part of child's primary key
  • Child cannot exist without parent
• Example: ACCOUNT logs TRANSACTION

Key Differences:

Summary of Key Concepts

ERD Components:

1. Entities - Things or objects (rectangles)
2. Attributes - Properties of entities (ovals)
3. Relationships - Associations between entities (diamonds)

Constraint Types:

1. Connectivity - Type of relationship (1:1, 1:M, M:N)
2. Cardinality - Specific min/max values (min, max)
3. Participation - Optional vs. mandatory (single/double lines)

Entity Classifications:

1. Strong - Independent existence
2. Weak - Dependent on other entities

Attribute Types:

1. Simple/Composite - Atomic vs. subdividable
2. Single/Multivalued - One vs. multiple values
3. Stored/Derived - Direct storage vs. calculated