Entity Relationship Model
Relationships are created by dividing object of interest into entity and its characteristics into attributes.
ER models are defined to represent relationship into pictorial form to make it easier for different stake holders to understand. This model is good to design database, which can then be turned into tables in relational model.
Components of ER-Model:
i) Entity: An entity is a real world object having state and behavior . It can be place, person or thing that exists in real life. It is represented by rectangle symbol.
Weak Entity: Entity that depends upon other entities is called weak entity. The weak entity doesn’t contain any key entity of its own. It is represented by double rectangle.
- ii) Attributes: It is used to describe the property of an entity. It is represented by eclipse.
Some types of Attributes are:-
1) Key Attribute: It is used to represent the main characteristics of an entity. It represents the primary key. It is represented by eclipse with text underlined.
2) Composite Attribute: The composite attribute is the attribute which is composed of many other attribute.
Example:
3) Multi Valued Attribute: It can have more than 1 value. It is represented by double eclipse.
4) Derived Attribute: An attribute derived from other attribute is called derived attribute. It is represented by shaded eclipse.
DB Relationship
The logical association between multiple entities is called relationship. Relationship is represented by diamond symbol.
Degree of Relationship:
The number of entity type that participate in a relationship is called degree of relationship.
Types of relationship:
i) Unary Relationship: If there exists an association with only one entity it is called unary relationship.
Example:
-
ii) Binary Relationship: If there exist an association between 2 entities it is called binary relationship.
Example:
iii) Ternary Relationship: Exists when there is relation between 3 entities.
Example:
iv) N-Degree Relationship: In this relationship there exists association between (n) number of entities.
Example:
Entity Set
An entity set is a group of entities that share the same attributes.
Keys in DBMS
Keys are used to manage data more effectively and efficiently inside the database. Keys are used to uniquely identify the tuple.
Some keys in DBMS are:-
i) Primary Key: A primary key is a unique identifier for each record in a table. It ensures that each row in a table is uniquely identifiable. Primary keys cannot contain NULL values. Each table can have only one primary key.
- ii) Foreign Key: A foreign key is a column or set of columns in one table that refers to the primary key in another table. It establishes relationships between tables. Foreign keys enforce referential integrity, ensuring that values in the foreign key column must exist in the primary key column of the referenced table.
iii) Unique Key: A unique key ensures that all values in a column or a set of columns are unique. Unlike a primary key, it can contain NULL values, but only one NULL value per column. A table can have multiple unique keys.
- iv) Candidate Key: A candidate key is a column or set of columns that can uniquely identify a record in a table. From these candidate keys, one is selected as the primary key, and the rest become alternate keys. Each candidate key must be unique and minimal; meaning no subset of its columns can uniquely identify a record.
- v) Super Key: A super key is a set of one or more columns that, taken collectively, can uniquely identify a row in a table. It may contain more columns than necessary to uniquely identify a record. Every primary key is a super key, but not every super key is a primary key.
- vi) Composite Key: A composite key is a primary key composed of multiple columns. It’s used when a single column cannot uniquely identify a record, but a combination of columns can. Composite keys are commonly used in many-to-many relationship tables.
Mapping Cardinalities:
Mapping cardinalities in an Entity-Relationship Diagram (ERD) define the relationships between entities by specifying how many instances of one entity are associated with how many instances of another entity.
There are 4 types of mapping cardinalities:
i) One-to-One (1:1): In a one-to-one relationship, each instance of one entity is associated with exactly one instance of another entity, and vice versa. This type of relationship is less common but is useful when there is a strict and singular association between entities.
ii)One-to-Many (1:N): In a one-to-many relationship, one instance of an entity is associated with zero, one, or many instances of another entity, but each instance of the related entity is associated with at most one instance of the first entity. This is the most common type of relationship in database modeling and is used in scenarios where one entity has multiple related instances in another entity.
iii) Many-to-One (N:1): In a many-to-one relationship, many instances of one entity are associated with at most one instance of another entity, but each instance of the related entity can be associated with zero, one, or many instances of the first entity. This relationship is essentially the inverse of a one-to-many relationship and is less common but still applicable in certain scenarios.
iv) Many-to-Many (M:N): In a many-to-many relationship, many instances of one entity can be associated with many instances of another entity, and vice versa. This type of relationship typically requires the use of an intermediary table, often called a junction or associative table, to resolve the many-to-many relationship into two one-to-many relationships.