MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL: Entity-Relationship Model
2021-06-07 15:53:06

MySQL: Entity-Relationship Model

Entity-Relationship Model

Entity-Relationship model or E R model is used to create a relationship between different attributes or entities. It describes the structure of the database with the help of the ER Diagram or Entity Relationship Diagram. ER model creates a simple design view of the data. It helps in creating the conceptual diagram of the database that makes the data easier to understand.

For example:Here, we have a database COMPANY, and in this database, EMPLOYEE is the entity (table). The employee entity contains several attributes like EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_DATE_OF_BIRTH, EMP_AGE, and EMP_CONTACT. In these attributes, EMP_NAME can be work as another entity having attributes like FIRST_NAME, MIDDLE_NAME, and LAST_NAME. Again, another attribute EMP_ADDRESS in the table EMPLOYEE can work as another entity with attributes like EMP_CITY, EMP_HOUSE_NO, EMP_PIN, etc.

MySQL: Entity-Relationship Model

ER model contains the following essential component:

  • Entity
  • Entity Set
  • Attributes
  • Relation

Entity:

The entity in DBMS can be a real-world object having conceptual reality and existence.

For example: Above, we have a database COMPANY, and the entity type is EMPLOYEE. Here employees are the natural world person that has some existence.

An entity can be further divided into several types:

  • Strong entity
  • Weak entity
  • Tangible entity
  • Intangible entity

Strong entity:

Strong entities are those entities which do not depend on other entity. The strong entity mainly contains the primary key.A single rectangular box represents a strong entity.

MySQL: Entity-Relationship Model

For example:In the previous example, we have entity EMPLOYEE having EMP_ID as the primary key. Therefore, it is termed as a strong entity.

Weak entity:

Weak entities do not contain the primary key in the table. They mainly depend on the parent entity for the data. A double rectangular box represents it.

MySQL: Entity-Relationship Model

For example:Suppose we have an entity EMPLOYEE_ADDRESS having attributes EMP_CITY, EMP_HOUSE_NO, and EMP_PIN. Here we see that the primary key is unavailable, and EMPLOYEE_ADDRESS (entity) is dependent on the EMPLOYEE (entity) for the data.

Tangible-entity:

Tangible entities are those entities that have some physical existence. These entities exist in the real world.

For example, Person, Table, Chair, etc.

Intangible-entity:

Intangible entities are those entities that do not have physical existence. There is no existence of an intangible entity in the real world.

For example, Bank Account.

Entity Set

An entity set is a collection of more than one entity of a similar entity type. The entity type can be the data or values given to the data.

For example, suppose we have a table STUDENT(entity type) with attributes ID, NAME, and AGE.Then, the data given to this table can be the entity set. Let’s have a table name as STUDENT.

MySQL: Entity-Relationship Model

Here, STUDENT is entity type, and (01, Sumit, 23), (02, Ajay 26), and (03, Amit, 32) are the entity set.

Attributes

Attributes indicate the property and characteristics of an entity. There can be several attributes of an entity, depend on its possibility. One of the attributes is considered to be the primary key, candidate key, etc.

Attributes are represented by elliptical shape.

MySQL: Entity-Relationship Model

Example:

MySQL: Entity-Relationship Model

These attributes can be divided into several types, such as:

  • Simple attribute
  • Composite attribute
  • Single valued attribute
  • Multi-valued attribute
  • Derived attribute

Simple attribute:

The simple attribute cannot be further divided into its subtype. In other words, it cannot be further divided into one or more attributes.It also contains null values.

For example, Attributes like ID and ROLL_NO in table STUDENT (entity type) are examples of simple attributes as these attributes cannot be further divided into subtypes.

MySQL: Entity-Relationship Model

Composite Attribute

Composite attributes are those attributes that can be further divided into meaningful subparts. In other words, these attributes can be further divided into more than one sub-attributes.

For example, in the table STUDENT, attributes like NAME and ADDRESS can be further divided into sub-attributes. Like name can be divided into FIRST_NAME, MIDDLE_NAME, and LAST_NAME. ADDRESS can also be divided into CITY, HOUSE_NO, and PIN.

MySQL: Entity-Relationship Model

Single valued attribute

A single value attribute can have only a single attribute. Single value attributes are not necessarily simple attributes.

For example:AGE in entity STUDENT can be a single-valued attribute because age for each people will have a single value. It is not possible to have two ages for a single person. Each person will have only one age.

MySQL: Entity-Relationship Model

Multi-valued attribute

Multi-valued attributes are those attributes that contain more than one value at a time. It can be a composite attribute.

For example, ADDRESS attribute in the table STUDENT. An address can hold attributes like city, pin code, etc. NAME attributes in table STUDENT can also be multi-valued attributes.

MySQL: Entity-Relationship Model

Derived attribute

The derived attribute is those attributes that do not exist in the physical database. Its value gets derived from other attributes, or we can say that it depends on other database attributes for the data.

For example:Suppose we have a table SCHOOL. In this table, we can say that the AGE attribute can be derived attribute as it can be derived from the DATE OF BIRTH attribute of the table.

MySQL: Entity-Relationship Model

Relation

MySQL: Entity-Relationship Model

The relation shows the relationship among different entities. Its shows how two or more entities are related to each other.It is represented by a diamond shape in the ER diagram.

There are four types of relationship:

  • One to one
  • One to many
  • Many to one
  • Many to many

One to one

Here, one entity is related to another single entity known as one relationship.

For example, a person can have only one voter ID card, and so the relation among them will be one to one only.

One to many

When an entity is related to more than one entity, then this relation is known as one to many relationships.

For example, a relation between customer and order. Here a customer can place many orders, but many customers cannot place an order.

Many to one

When more than one entity is related to a single entity, this is called a many to one relationship.

For example, a relation between student and school, many students can study in a single school, but vice versa is not possible.

Many to many

When more than one entity is related to more than one entity, this is called many to many relationships.

For example, a relation among students and subjects will be the best example for this as many students read many subjects and vice versa.