Database Concepts

  • Database Definition

    • A database is a system used for recording data, often structured and organized for efficient retrieval and management.

  • Basic Terminology

    • Fields: Individual attributes or properties of an entity, usually represented as columns in a table.
    • Records: Rows in a table that contain data for a specific entity.
    • Tables: Structures that store data about entities.

  • Types of Databases

    • Flat File: A single file for data storage, simple but limited in ability to manage relationships.
    • Relational Database: Composed of multiple tables linked together, allowing complex relationships through keys.

  • Keys in Databases

    • Primary Key: A unique identifier for an entity; must never repeat within a table.
    • Foreign Key: An attribute that creates a link between two tables; it is the primary key from one table that appears in another.
    • Secondary Key: An indexing method for primary keys, facilitating quick searches.

  • Entity Relationship Diagrams (ERD)

    • Visual representations of the relationships between entities in a database.
    • Types of relationships:
    • 1:1 (one-to-one)
    • 1:M (one-to-many)
    • M:M (many-to-many), which requires a junction table to link the entities.

  • Composite Key

    • A primary key formed by combining more than one attribute.

  • Referential Integrity

    • Ensures that a key attribute in one table must not be deleted if it is referenced in another table, maintaining consistency.

  • Important Examples of Entities

    • Employee, Film, Actor, Product, Recipe, Ingredient.

  • Limitations of Flat File vs. Relational Database

    • Flat file: simple but lacks data integrity and is less efficient for complex queries.
    • Relational: supports complex queries and relationships but requires more management.