Database Structure with Designing

ER Model (Entity Relationship Model)

• The Entity-Relationship (E/R) Model is a fundamental concept used in database design to visually represent data and their relationships. 
• It serves as a blueprint for designing databases by modeling real-world entities, their attributes, and the relationships between them. 
• The E/R model simplifies the process of understanding and designing the structure of data before implementation in a Database Management System (DBMS).

• Key components of the E/R (Entity-Relationship) Model
• Entities

• Definition: 
•  An entity represents a real-world object or concept that has a distinct existence in a system. It is typically a noun and forms the primary data point in an E/R model.
• Sub-components:
• Entity Type: 
• The category or class of the entity (e.g., Customer, Product, Employee).
• Entity Instance: 
• A specific example of an entity (e.g., John Doe is an instance of the Customer entity).
• Weak Entity: 
• An entity that cannot be uniquely identified by its own attributes and relies on another entity (called the strong entity).
• Strong Entity: 
• An entity that can be uniquely identified by its own attributes, independent of other entities.
• Attributes
• 
  Definition: 
•  Attributes describe properties or characteristics of entities or relationships. They hold the actual data.
• Sub-components:
• Simple Attribute: 
• Cannot be divided further (e.g., Age, ID).
• Composite Attribute: 
• Can be divided into sub-parts (e.g., Full Name can be divided into First Name and Last Name).
• 
  
  
  
  
• Derived Attribute: 
• An attribute whose value can be derived from other attributes (e.g., Age can be derived from Date of Birth).
• 
  
  
  
  
• Multivalued Attribute: 
• Can hold multiple values (e.g., Phone Numbers for a Customer).
• 
  
  
  
  
• Key Attribute: 
• Uniquely identifies an entity in the entity set (e.g., Employee_ID).
• 
  
  
  
  
• Primary Key: The main unique attribute used to identify instances of an entity (e.g., Student_ID).
• Candidate Key: Any attribute that could serve as a primary key.
• Foreign Key: An attribute that creates a relationship between two entities, linking the primary key of another entity.

• Relationships
• Definition: 
•  Relationships define how two or more entities are associated with each other. They are typically verbs.
• 
  
  
  
  
• Sub-components:
• Relationship Type: 
• The nature of the relationship between entities (e.g., enrolls, orders, teaches).
• Cardinality: 
•  Specifies the number of instances of one entity that can be associated with another entity.
• One-to-One (1:1): One instance of an entity is associated with one instance of another entity.
• 
  
  
  
  
• One-to-Many (1:M): One instance of an entity is associated with multiple instances of another entity.
• 
  
  
  
  
• Many-to-Many (M:M): Multiple instances of one entity are associated with multiple instances of another entity.
• 
  
  
  
  
• Degree of Relationship: The number of entity types involved in a relationship (binary, ternary, etc.).
• Primary Key
• Definition: 
•  A primary key is a unique attribute (or a set of attributes) that identifies each entity instance in an entity set.
• Sub-components:
• Single Attribute Key: A primary key made up of one attribute (e.g., Student_ID).
• Composite Key: A primary key made up of multiple attributes (e.g., Order_ID and Product_ID together form the primary key for an Order entity).
• Foreign Key
• Definition: 
•  A foreign key is an attribute in one entity that refers to the primary key of another entity to create a relationship between the two entities.
• Sub-components:
• Referential Integrity: Ensures that a foreign key value always points to a valid primary key in the referenced table.
• Cascading Actions: Define what happens to the foreign key when the related record in the primary key table is updated or deleted (e.g., Cascade Delete, Cascade Update).
• Cardinality (Participation)
• Definition: 
•  Specifies the number of instances of one entity that can be associated with instances of another entity.
• Sub-components:
• One-to-One (1:1): One entity is associated with one other entity (e.g., Person to Passport).
• One-to-Many (1:M): One entity is associated with multiple instances of another (e.g., Teacher to Students).
• Many-to-Many (M:M): Multiple instances of one entity are associated with multiple instances of another (e.g., Students to Courses).
• Participation Constraints
• Definition: 
• Constraints that define whether all or only some entity instances participate in a relationship.
• Sub-components:
• Total Participation: Every instance of the entity must participate in the relationship (e.g., every Employee must be assigned a Department).
• Partial Participation: Some instances of the entity participate in the relationship (e.g., some Students may be enrolled in Clubs).
• Weak Entity and Identifying Relationship
  

• Definition: 
• A weak entity is an entity that cannot be uniquely identified by its attributes alone. It depends on another entity (strong entity) for its existence.
• Sub-components:
• Identifying Relationship: The relationship between a weak entity and its strong entity that links them. This relationship helps define the weak entity’s existence (e.g., Dependent is a weak entity, and its identifying relationship with Employee is that the Dependent is associated with the Employee).

Symbols of Key Components in the E/R Model

What is E/R Diagram?

• An Entity-Relationship (ER) Diagram is a visual representation of the data model of a system, illustrating the relationships between different entities within that system. 
• It is widely used in database design to provide a blueprint for how data will be structured, stored, and accessed in a database.

Purpose of E/R Diagram:

• ·        To serve as a conceptual framework for designing databases.
• ·        It helps in visualizing how data will be stored, organized, and interconnected.
• ·        ER Diagrams help ensure that the relationships between data are logical and meet the business requirements before creating the physical database.

Example of Library Management System

Entity and Attribute:

• Books
• Book_id (Primary Key)
• Author
• Title
• Price
• Available
• Publisher
• Pub_id (Primary Key)
• Address
• Name
• Borrow By
• Due Date
• Rtrn Date
• Issue
• Member
• Mem_id (Primary Key)
• Mem_name
• Mem_type
• Address
• Name
• Expiry Date