ER-Model

ER Diagram: An ER diagram is a model of a logical view of the database which is represented using the following components:

• Entity: The entity is a real-world object, represented using a rectangular box.
  • Strong Entity: A strong entity set has a primary key and all the tuples of the set can be identified using that primary key
  • Weak entity: When an entity does not have sufficient attributes to form a primary key. Weak entities are associated with another strong entity set also known as identifying an entity. A weak entity’s existence depends upon the existence of its identifying entity. The weak entity is represented using a double-lined or bold-lined rectangle. 
• Attribute: Attribute is the properties or characteristics of the real-world object. It is represented using an oval. 
  • Key attribute: The attribute which determines each entity uniquely is known as the Key attribute. It is represented by an oval with an underlying line. 
  • Composite Attribute: An attribute that is composed of many other attributes. E.g. address is an attribute it is formed of other attributes like state, district, city, street, etc. It is represented using an oval comprises of many other ovals. 
  • Multivalued Attribute: An attribute that can have multiple values, like a mobile number. It is represented using a double-lined oval. 
  • Derived attribute: An attribute that can be derived from other attributes. E.g. Age is an attribute that can be derived from another attribute Data of Birth. It is represented using a dashed oval.
• Relationship: A relationship is an association between two or more entities. Entities are connected or related to each other and this relationship is represented using a diamond. 

Some Important Terms

Cardinality of DBMS: Cardinality of relation expresses the maximum number of possible relationship occurrences for an entity participating in a relationship. Cardinality of a relationship can be defined as the number of times an entity of an entity set participates in a relationship set. Let’s suppose a binary relationship R between two entity sets A and B.  The relationship must have one of the following mapping cardinalities:

• One-to-One: When one entity of A is related to at most one entity of B, and vice-versa.
• One-to-Many: When one entity of A is related to one or more than one entity of B. Whereas B is associated with at most one entity in A. 
• Many-to-One: When one entity of B is related to one or more than one entity of A. Whereas A is associated with at most one entity in B. 
• Many-to-Many: Any number of entities of A is related to any number of entities of B, and vice-versa. 

The most commonly asked question in ER diagram is the minimum number of tables required for a given ER diagram. Generally, the following criteria are used:    

• If the relation is one-to-many or many-to-one then two or more relational tables can be combined.
• If the relation is many-to-many two tables cannot be combined. 
• If the relation is one-to-one and there is total participation of one entity then that entity can be combined with a relational table. 
• If there is total participation of both entities then one table can be obtained by combining one table and both entities of the relation. 

Note: This is a general observation. Special cases need to be taken care of. We may need an extra table if the attribute of a relationship can’t be moved to any entity side. 

Specialization: It is a  top-down approach in which one entity is divided/specialized into two or more sub-entities based on its characteristics.

Generalization: It is a bottom-up approach in which common properties of two or more subentities are combined/generalized to form one entity. It is exactly the reverse of Specialization. In this, two or lower level entities are generalized to one higher level entity.

Aggregation: Aggregation is an abstraction process through which relationships are represented as higher-level entity sets. 

Participation Constraint: It specifies the maximum or a minimum number of relationship instances in which any entity can participate. In simple words, participation means how an entity is linked to a relationship.

• Total Participation: Each entity of an entity set participates in at least one relationship. 
• Partial Participation: Some entities of the entity set may not participate in any relationship. 

Database Management System is an organized collection of interrelated data that helps in accessing data quickly, along with efficient insertion, and deletion of data into the DBMS. DBMS organizes data in the form of tables, schemas, records, etc. 

DBMS over File System

The file system has numerous issues, which were resolved with the help of DBMS, the issues with the file system are:

• Data Redundancy: Same data can be stored at multiple places. 
• Data Inconsistency: If multiple copies of the same data have different content in each copy. Like, the phone number of students is different in academic and accounts files. 
• Data access: In a file system, accessing data was difficult and insecure as well. Accessing data concurrently was not possible.
• No Backup and Recovery: There is no backup and recovery in the file system that can lead to data loss.