Organized Version of my Previous Article about Database Model

Data Models and Relational Model

Understanding Data Models

A data model represents a system logically, helping to structure and define relationships within a database. Different database systems follow different designs based on specific rules and structures. The primary data models include:

• Hierarchical Model
• Object-Oriented Model
• Network Model
• Relational Model (our focus)

Regardless of the type of database, the conceptual structure remains consistent. Data models are typically represented through diagrams, which visually express relationships and data structures.

The Relational Model

The relational model is our primary concern. It is represented by the Entity-Relationship (ER) Model, which serves as a visual representation of relational databases.

Key Components:

• Entities: Objects or concepts represented as tables.
• Attributes: Properties of an entity (e.g., ID, Name, etc.).
• Primary Key: A unique attribute identifying each record.
• Foreign Key: A reference to a primary key in another table.
• Tuples (Rows): Individual records in a table.
• Columns: Represent attributes in the table.
• Relationships: Connections between entities, categorized as:
  • One-to-One
  • One-to-Many
  • Many-to-Many
• Normalization: Organizing a database to minimize redundancy and avoid anomalies.

ER Model

The ER Model was introduced by E.F. Codd in 1972 and transformed database structures. It is essential for internal database formation and understanding data relationships in systems like hospital databases and library databases.

ER Diagram Symbols:

• Rectangle: Represents entities.
• Oval: Represents attributes.
• Diamond: Represents relationships.
• Underlined Attribute: Indicates a primary key.
• Double Circled Attribute: Indicates a multi-valued attribute.
• Solid Line: Represents a mandatory relationship.

Attributes in ER Model

• Simple vs. Composite Attributes:
  • Simple: Cannot be broken down (e.g., Name).
  • Composite: Consists of multiple sub-parts (e.g., Address with Street, City, Zip).
• Single-Valued vs. Multi-Valued Attributes:
  • Single-Valued: Holds one value per entity (e.g., Age).
  • Multi-Valued: Can have multiple values (e.g., Phone Numbers).
• Stored vs. Derived Attributes:
  • Stored: Directly stored in the database (e.g., Date of Birth).
  • Derived: Computed from other attributes (e.g., Age derived from Date of Birth).
• Key vs. Non-Key Attributes:
  • Key: Used for unique identification.
  • Non-Key: Supports additional details but not for identification.

Database Design Principles

To articulate database design, follow these six steps:

1. Define the Domain (e.g., Sports)
2. Identify Sub-Domain (e.g., Team)
3. Entity Set Identification (e.g., Player Name as an entity)
4. Existence Validation (Ensure correct entity identification)
5. Define Relationships (Inter-entity relationships)
6. Apply Constraints (Ensure data integrity and relevance)

SQL & Functions in Relational Databases

• Single Row Functions:
  • Modify individual data.
  • Example: Converting lowercase to uppercase.
• Multi-Row Functions:
  • Perform calculations on multiple rows.
  • Example: Finding the sum of all salaries in a department.

Conclusion

Understanding data models, especially the relational model, is crucial for effective database design. The ER Model helps visualize relationships and structure data efficiently, ensuring a well-normalized and optimized database system.