The term "ER model" refers to the Entity-Relationship model, which serves as a data model at an elevated level. Its purpose is to establish the definition of data components and their interconnections within a designated system.

It generates a conceptual blueprint for the database, creating a straightforward and easily manageable representation of data.

In the practice of ER modeling, the arrangement of the database structure is visually represented through a diagram termed as an entity-relationship diagram.

Why Use ER Diagrams In DBMS?

ER diagrams play a pivotal role in portraying the E-R model within a database, simplifying the process of transforming them into relational structures (tables).

The significance of ER diagrams lies in their ability to model real-world objects, adding a layer of practicality to their utility.

With no demand for technical prowess or hardware assistance, ER diagrams offer a user-friendly approach.

Even for individuals unfamiliar with complex concepts, these diagrams are highly accessible and uncomplicated to generate.

ER diagrams offer a uniform approach to logically visualizing data, providing a standardized solution.

Symbols Used in ER Model

The ER Model is employed to conceptualize the system's logical aspect from a data stance, encompassing these symbols

- Rectangles: These rectangles are utilized to symbolize Entities within the ER Model.

- Ellipses: Ellipses are employed to represent Attributes in the ER Model.

- Diamonds: The diamond shape is used to portray Relationships among different Entities.

- Lines: Lines are utilized to depict associations between attributes and entities, as well as entity sets with different types of relationships.

- Double Ellipses: Double ellipses are used to signify Multi-Valued Attributes.

- Double Rectangles: Double rectangles are employed to indicate a Weak Entity.

Components of ER Diagram

Entity

An entity can encompass various elements such as objects, classes, individuals, or locations. Within an ER diagram, entities are symbolized using rectangular shapes.

Take into account an instance of an organization - a manager, product, staff member, department, and so forth could be regarded as entities in this context.

1.Weak Entity

A weak entity is an entity that relies on another entity. Unlike a strong entity, a weak entity lacks its own key attribute. It is depicted using a double rectangle in diagrams.

Example:-A corporation has the ability to retain data regarding the family members (parents, children, spouse) of an employee. However, these family members only hold significance due to their connection with the employee. Consequently, family members are categorized as a vulnerable entity type, while employees are recognized as the pivotal entity type for the family members, indicating that they are.. 

2. Strong Entity

A Strong Entity refers to an entity possessing a primary Attribute. It maintains independence from other entities within the Schema, boasting a primary key that facilitates its distinct identification. This is symbolized by a rectangle and is termed as a Strong Entity Type.

Attributes

Attributes are the characteristics that establish the nature of an entity type. For instance, attributes like Roll Number, Name, Date of Birth, Age, Address, and Mobile Number serve to characterize the entity type of Student. Within an Entity-Relationship (ER) diagram, attributes are depicted using oval shapes.

1. Key Attribute-The key attribute is the distinctive characteristic that identifies each individual entity within the set of entities. For instance, the Roll_No serves as a unique identifier for every student. In an Entity-Relationship (ER) diagram, this key attribute is depicted as an oval shape containing underlying lines.

3. Multivalued Attribute

  A characteristic that holds multiple values for a specific entity. For instance, Phone_Number (which can have multiple entries for a single student). In an Entity-Relationship (ER) diagram, a multivalued attribute is depicted using a pair of connected ovals.

4.Derived Attribute

An attribute that is obtained based on other attributes within the entity type is referred to as a derived attribute. For example, Age (calculated from Date of Birth). In an ER diagram, a derived attribute is depicted using a dashed oval.

The Student entity type along with its attributes can be illustrated as follows: 

Relationship

A relationship is utilized to define the connection between entities. In graphical representations, such as in an ER diagram, this relationship is symbolized using a diamond or rhombus shape.

Types of relationship are as follows 

a. One-to-One Relationship 

If a relationship is such that it links a singular instance of an entity, it is referred to as a one-to-one relationship.

Example-A woman can be married to a single man, and conversely, a man can be married to a single woman.

2.One-to-many relationship 

When only one  instance of the entity on the left is connected to multiple instances of the entity on the right within a relationship, it is referred to as a one-to-many relationship.

Example--Scientists have the capability to create numerous inventions, while the act of inventing a particular invention is attributed to a specific scientist.

3. Many-to-one relationship 

When more than one instances of the entity on the left are linked to a single instance of the entity on the right through a relationship, it is referred to as a many-to-one relationship. 

Example-A student is enrolled in a single course, whereas a course can accommodate multiple students.

4.Many-to-many relationship 

A many-to-many relationship arises when multiple instances of the entity on the left are connected with multiple instances of the entity on the right through the relationship. 

For example -An employee can be assigned to multiple projects, and likewise, a project can involve multiple employees.

Advantages of ER Model 

Simplicity: The conceptual ER Model construction is straightforward. If we grasp the connections between attributes and entities, developing the ER Diagram for the model becomes effortless.

Efficient Communication Tool: Database designers extensively rely on this model to effectively convey their concepts.

Smooth Transition to Various Models: This model harmonizes effectively with the relational model, seamlessly transforming the ER model into tables. Furthermore, it can be adapted into other models like the network model, hierarchical model, and more.

Disadvantages of ER Model 

Lack of Notation Consistency: An established industry standard for creating an ER model is absent. Consequently, different developers might employ notations that are unclear to their peers.

Concealed Information: The ER model's high-level perspective can lead to the omission or concealment of certain details, potentially resulting in the loss of some information.

Schema 

What is Schema?

• The framework of the database takes form through the attributes, and this framework is identified as the schema.

• A schema outlines the logical limitations, such as tables and primary keys, that define the structure of a database.

• The schema doesn't encompass the data attribute types.

Database Schema

A database schema is a logical and structured representation of the organization, arrangement, and relationships among the data stored in a database. It defines the design, format, and constraints of the data stored in the database tables, along with the interconnections between these tables. In essence, a database schema outlines the blueprint for how data is organized, stored, and accessed within a database management system. It includes information about tables, fields, data types, relationships, constraints, and other elements that define the structure and integrity of the database.

Types of Database Schema

The database schema is categorized into three types, namely:

1. Logical Schema

2. Physical Schema

3. View Schema

1.Physical Database Schema 

•  The physical schema outlines how data is physically stored within storage systems as files and indices. It involves the concrete code or syntax required to establish the database's structure. When crafting a database structure on the physical level, it is referred to as the physical schema.

• The choice of data storage locations and methods within various storage blocks is made by the Database Administrator.

2.Logical Database Schema

• The logical database schema encompasses all the rational restrictions to be enforced on the stored data, as well as outlines the tables, perspectives, entity connections, and integrity constraints. 

• The logical schema elucidates the manner in which data is stored, comprising tables and their interconnected attributes. 

• Through the utilization of ER modeling, the connections among data elements are upheld.

• Within the logical schema, diverse integrity constraints are outlined to ensure the accuracy of data insertion and updates.

3.View Schema

• This refers to a view-level design that outlines how interactions between end-users and the database are defined. 

• Users can interact with the database through an interface without requiring extensive knowledge about the underlying data storage methods employed within the database.

Creating Database Schema

To create a schema, the "CREATE SCHEMA" statement is employed in various databases. However, the interpretation of this statement can differ across different database systems. Let's explore some examples of statements used for creating a database schema in various database systems:

1. MySQL:In MySQL, the "CREATE SCHEMA" statement is utilized to create a database. This is because, in MySQL, both "CREATE SCHEMA" and "CREATE DATABASE" statements serve the same purpose.

2. SQL Server:-Within SQL Server, the "CREATE SCHEMA" statement is employed to generate a new schema.

3. Oracle Database:-In Oracle Database, the "CREATE USER" statement is used to create a new schema. In Oracle, a schema is automatically generated with every database user. The "CREATE SCHEMA" statement, however, doesn't create a new schema. Instead, it populates the existing schema with tables and views, facilitating access to these objects without necessitating multiple SQL statements for separate transactions.

Database Schema Designs

Creating a schema design constitutes the initial phase in establishing a solid groundwork for data administration. Inefficient schema designs prove challenging to oversee and result in heightened memory consumption and resource utilization. The logical course of action is contingent upon the demands of the business. Opting for the appropriate database schema design is essential to simplify the project lifecycle. Presented herewith is an assortment of well-known database schema designs.

1. Flat Model

2. Hierarchical Model

3. Network Model

4. Relational Model

5. Star Schema

6. Snowflake Schema

1.Flat Model

A flat model schema embodies a 2-dimensional array where each column holds identical data types, and items within a row possess interrelatedness. It can be likened to a solitary spreadsheet or a database table devoid of interconnections. This schema structure proves optimal for modest applications devoid of intricate data structures.

2.Hierarchical Model

The Hierarchical model design features a configuration resembling a tree. Within this tree structure, there exists a root data node and subsequent child nodes. A one-to-many relationship is established between each parent node and its child node. These types of database schemas find representation in formats like XML or JSON files, which are capable of encompassing entities along with their respective sub-entities.

Hierarchical schema models excel in housing nested data, effectively representing instances like Hominoid classification.

3.Network Model 

The network model and the hierarchical model bear striking resemblances, but a significant disparity lies in their handling of data relationships. The network model permits the existence of many-to-many relationships, in contrast to the hierarchical models, which solely accommodate one-to-many relationships.

4.Relational Model 

Relational models serve as the foundation for relational databases, where data is stored in the form of tables or relations. Within this context, relational operators are employed to manipulate and compute various values from the data.

5.Star Schema  

The star schema represents an alternative schema design approach for data organization. It excels in managing and analyzing vast volumes of data and operates based on the concepts of "Facts" and "Dimensions". In this context, a fact corresponds to a numerical data point that drives business processes, while a dimension provides context and description to the facts. The Star Schema is particularly effective for structuring data within Relational Database Management Systems (RDBMS).

6.Snowflake Schema

The snowflake schema is a modification of the star schema. In the star schema, there exists a central "Fact" table housing primary data points with references to its associated dimension tables. However, in the snowflake schema, dimension tables have the potential to branch out into their own additional dimension tables, creating a more intricate hierarchical structure.