Unit – II :ER Model and Relational Algebra

 1. Basic Concepts of E–R (Entity-Relationship) Model

The Entity–Relationship (ER) Model is a high-level data model proposed by Peter Chen in 1976.
It provides a conceptual framework to describe the structure of a database using real-world objects (entities), their characteristics (attributes), and how they interact (relationships).
ER Models are mainly represented by ER Diagrams which are graphical notations.

1. Entities

An Entity is an object in the real world that is distinguishable from other objects. Entities can be physical (Student, Book, Car) or conceptual (Course, Department, Salary).

Types of Entities:

  1. Strong Entity

    • Can exist independently.

    • Has a primary key that uniquely identifies it.

    • Example: Student (identified by Roll_No).

    🔹 ER Diagram Notation: Rectangle (e.g., STUDENT)

  2. Weak Entity

    • Cannot exist without being related to a strong entity.

    • Identified using a partial key (discriminator) and linked with a strong entity through an identifying relationship.

    • Example: Dependent (child of Employee).

    🔹 ER Diagram Notation: Double Rectangle (e.g., DEPENDENT)

2. Attributes

Attributes describe the properties of an entity or relationship.

Types of Attributes:

  • Simple Attribute – Cannot be divided further.
    Example: Roll_No, Gender.

  • Composite Attribute – Can be divided into smaller attributes.
    Example: Name → {First_Name, Last_Name}.

  • Derived Attribute – Calculated from other attributes.
    Example: Age (from Date_of_Birth).

  • Multivalued Attribute – Can have multiple values.
    Example: Phone_Numbers.

  • Key Attribute – Unique identifier for entity (Primary key).

🔹 ER Diagram Notation:

  • Oval for attribute.

  • Double oval for multivalued attributes.

  • Dashed oval for derived attributes.

  • Underline attribute name for primary key.

3. Relationships

A Relationship is an association among two or more entities.

Types of Relationships:

  • Unary Relationship (Recursive) – Relationship among instances of the same entity.
    Example: Employee manages Employee.

  • Binary Relationship – Relationship between two entities (most common).
    Example: Student enrolls in Course.

  • Ternary Relationship – Relationship among three entities.
    Example: Supplier supplies Product to Store.

🔹 ER Diagram Notation: Diamond shape (e.g., ENROLLS).

4. Mapping Cardinality (Relationship Constraints)

Specifies the number of entities that can participate in a relationship.

  • One-to-One (1:1): A student has exactly one ID card.

  • One-to-Many (1:N): One department has many students.

  • Many-to-One (N:1): Many employees work in one company.

  • Many-to-Many (M:N): Students enroll in many courses, and courses have many students.

🔹 ER Diagram Notation: Numbers/lines near relationship diamond.

5. Keys in ER Model

  • Super Key – Any attribute(s) that uniquely identifies a record.

  • Candidate Key – Minimal super key.

  • Primary Key – Chosen candidate key.

  • Composite Key – Combination of attributes as a key.

  • Foreign Key – Attribute referencing another entity’s primary key.

6. Generalization and Specialization

  • Generalization: Combining lower-level entities into a higher-level entity.
    Example: Car and Bike → generalized into Vehicle.

  • Specialization: Breaking down higher-level entity into lower-level sub-entities.
    Example: Employee → subdivided into Faculty and Staff.

🔹 ER Diagram Notation: Triangle symbol.

7. ER Diagram Example

Imagine a University Database:

  • Entities: STUDENT, COURSE, DEPARTMENT, INSTRUCTOR

  • Attributes:

    • STUDENT → Roll_No (PK), Name, DOB, Phone (multivalued).

    • COURSE → Course_ID (PK), Title, Credits.

    • DEPARTMENT → Dept_ID (PK), Dept_Name.

  • Relationships:

    • STUDENT enrolls in COURSE (M:N).

    • COURSE offered by DEPARTMENT (M:1).

    • INSTRUCTOR teaches COURSE (1:N).

In ER diagram form:

  • Rectangles → entities.

  • Ovals → attributes.

  • Diamonds → relationships.

  • Lines with symbols (1, N) → cardinality.

8. Advantages of ER Model

  • Easy to understand and design (uses diagrams).

  • Represents real-world data intuitively.

  • Helps in converting conceptual model → relational model.

  • Useful in database normalization and schema design.


2. Different Types of Data Models in DBMS

Different Types of Data Models in DBMS (Detailed)

A Data Model is a set of concepts that describes:

  • How data is represented (structure),

  • How data is stored in the database,

  • How data is manipulated (operations), and

  • How data is related (constraints).

👉 In simple words: A Data Model is a blueprint for organizing and designing a database.

1. High-Level (Conceptual) Data Models

  • Provide a user-level view of data (close to real world).

  • Easy to understand for end-users and database designers.

  • Abstract away technical details of storage.

  • Example: Entity–Relationship (E–R) Model.

🔹 Features:

  • Describes entities, attributes, and relationships.

  • Used in initial database design phase.

  • Represented using ER Diagrams.

✅ Example:

  • STUDENT (Roll_No, Name, DOB)

  • COURSE (Course_ID, Title)

  • Relationship: STUDENT enrolls in COURSE.

2. Representational (Implementation) Data Models

  • Provide a bridge between high-level (conceptual) and low-level (physical) models.

  • They are not too abstract (like ER) and not too detailed (like physical storage).

  • Used by database designers to implement databases.

Examples:

  1. Relational Model

    • Data is represented in tables (relations) with rows (tuples) and columns (attributes).

    • Proposed by E. F. Codd (1970).

    • Example:

    Student_IDNameDept
    101AmitCS
    102MeeraIT

    • SQL is based on Relational Model.

  2. Network Model

    • Data represented as records connected by links (pointers).

    • Uses set-relationship to represent connections.

    • Example: A Department has multiple Students → Department record linked to Student records.

  3. Hierarchical Model

    • Data is organized in a tree structure (parent-child relationship).

    • Each child has only one parent, but parent may have multiple children.

    • Example: Company → Departments → Employees.

3. Low-Level (Physical) Data Models

  • Concerned with how data is stored in memory/storage devices.

  • Provides details of storage structures, indexing, and access paths.

  • Used by DBMS developers (not end users).

🔹 Features:

  • Shows how records are stored (arrays, linked lists).

  • Defines indexes, pointers, and hashing techniques.

  • Example: Storing Employee records as sequential files or hash tables.

4. Object-Based Data Models

  • Based on object-oriented programming (OOP) concepts.

  • Represents both data (attributes) and behavior (methods).

  • Example: In Object-Oriented Model, an object "Student" has:

    • Attributes: Roll_No, Name, DOB.

    • Methods: getAge(), registerCourse().

🔹 Advantages:

  • Supports multimedia data (images, videos).

  • Encapsulation of data + operations.

  • More realistic modeling compared to traditional models.

5. Record-Based Data Models (Sub-class of Representational Models)

These focus on how records (rows) are stored in a table-like structure.

  • Relational Model (Tables).

  • Network Model (Graphs).

  • Hierarchical Model (Tree).

👉 Called record-based because data is stored in fixed-format records (rows).

🔹 Classification Diagram (Text Form)

Data Models
│
├── High-Level (Conceptual)
│     └── ER Model
│
├── Representational (Implementation)
│     ├── Relational Model
│     ├── Hierarchical Model
│     └── Network Model
│
├── Low-Level (Physical)
│     └── Storage, Indexing, Hashing
│
└── Object-Based
      └── Object-Oriented Model

Comments

Post a Comment

Popular posts from this blog

Unit – I: Introduction to Database Management System

Image
1. Introduction to DBMS and Its Advantages Definition: A Database Management System (DBMS) is software that helps in storing, managing, and retrieving data from databases efficiently. Example: A library system where books, members, issue/return records are stored in a database and managed using software like MySQL or Oracle DB. Advantages of DBMS: Advantage Description Data Redundancy Control Avoids duplication of data. Data Consistency Ensures all users see the same data. Data Integrity Maintains accuracy and consistency. Data Security Provides user-level access control. Concurrent Access Multiple users can access data simultaneously without conflict. Backup and Recovery Automatically handles data backup and recovery after failure. Better Data Sharing Centralized a...
Read more

UNIT – IV: Structured Query Language (SQL)

  1. Concept of SQL SQL is a standard language for managing relational databases. Categories: DDL, DML, DCL, TCL . 2. Data Types in SQL Integer, Float, Double → Numeric values Char(n), Varchar(n) → Strings Date, Time, Timestamp → Date & time values Number(p,s) → Numeric with precision 3. DDL Commands CREATE : Creates table. CREATE TABLE Student(RollNo INT , Name VARCHAR ( 50 )); ALTER : Modifies table structure. DROP : Deletes table. RENAME : Changes table name. TRUNCATE : Deletes all rows, keeps structure. COMMENT : Adds comments. 4. DML Commands SELECT : Retrieve data. INSERT : Add rows. UPDATE : Modify rows. DELETE : Remove rows. MERGE : Insert/update simultaneously. CALL : Call stored procedure. EXPLAIN PLAN : Shows execution plan. LOCK TABLE : Prevents changes by others. 5. DCL Commands GRANT : Give access. REVOKE : Remove access. 6. TCL Commands COMMIT : Save changes. ROL...
Read more