It is a representation of data. There are different types of model:

• Entity-Relationship Model (ER Model) - It's a high-level or conceptual view of your data as a diagram. It is mainly used in the requirement analysis phase.
• Relational Model - It's a representational or implementation view in a table format specially for database designer/programmers.
• Low-Level or Physical Model - It's a low-level view of your data that how it will be stored in hard-disk. It is stored as per data structure, data types, and their constraints.

In software design, the way UML diagrams play the role; In database design, ER diagrams play the same role. There are various things in ER diagram:

An entity is any person, place, or thing to be recorded in a database. Any noun is represented as an entity like Person, Student etc.

There are different types of entities:

• Strong entity
  • If an entity is having key attributes to identify uniquely than such an entity is called a strong entity.
• Weak entity
  • If an entity is not having any key attributes to identify uniquely than such an entity is called a weak entity.
  • To overcome a unique identity problem, every weak entity has to be related to a strong entity through a relationship which is called Identifying Relationship. This relationship is mandatory.

It is a property of an entity. An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. They are used to describe an entity or a relationship even better. The more attributes we have, the more description of an entity or a relation.

There are different types of attributes:

• Simple vs Composite attributes
  • Simple cannot be divided further. It is also called Atomic attribute. Composite is just opposite.
• Single-valued vs Multi-valued attributes
• Stored vs Derived attributes
• Complex attributes
  • It is a Composite attribute as well as Multi-values attribute.

A relationship is a set of associations between one or more entity types. Each relationship is given a name that describes its function. Any verb is represented as relationship like A [Person] <owns> a [Car], An [Employee] <works for> a [Department] etc.

Relationship has some characteristics:

• Degree
  • How many entities are participating in each relationship is called degree like Binary Degree, Ternary Degree etc.
• Cardinality-Ratio
  • Maximum number of relationships in which each entity can participate
• Participation/Existence
  • It is also called Minimum-Cardinality-Ratio.
  • Minimum number of relationships in which each entity can participate.
  • Both Cardinality-Ratio & Minimum-Cardinality-Ratio are structural-constraints.
  • Minimum-Cardinality-Ratio is also an existence-constraint.

There are four types of relationships:

• One-to-one
  • When only a single instance of an entity is associated with the relationship, it is termed as ‘1:1’.
• One-to-many
  • When more than one instance of an entity is related and linked with a relationship, it is termed as ‘1:N’.
• Many-to-many
  • When more than one instance of an entity on the left and more than one instance of an entity on the right can be linked with the relationship, then it is termed as N:N relationship.

---

The Relational model is the primary data model, which is used widely around the world for data storage and processing. This model is simple and it has all the properties and capabilities required to process data with storage efficiency.

• Table
  • In the Relational model, relations are saved in the format of Tables. This format stores the relation among entities. The smallest unit of information in a database is called a field. Fields are grouped together to form records. Records are then grouped together to form tables.
• Key
  • key - Any attribute in the table which uniquely identifies each record in the table is called key. It can be a single attribute or a combination of attributes.
  • super key - Combination of keys which uniquely identifies each record in the table is called super key. In super key, some keys have redundancy.
  • candidate key - A super key without redundancy. The candidate key is the minimal super key which cannot be reduced.
  • primary key - Each table may have one or more candidate keys, but one candidate key is unique, and it is called the primary key. If a primary key is a system/database generated value, it is referred to as a synthetic key or a surrogate key.
  • composite key - When one value does not uniquely identify a row, but two values do, where we'd combine two column values to create a unique primary key which is called composite key. Now, it's true, it might sometimes be more useful or even just more convenient to generate a surrogate primary key column anyway. But composite keys can be a useful technique and you will run into them from time to time. One place you will see them, later on, is they're used when we're joining tables together to create many to many relationships.
  • foreign key - A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
• Relationship
  • The tables for a relational database are linked to each other through the use of keys. Each table may have one primary key and any number of foreign keys. A foreign key is simply a primary key from one table that has been placed in another table.
• Transaction
  • A transaction is a combined unit of work. On a computing system, a classic example of transactions is a banking system. Imagine that you log on to your bank's website and you want to transfer $2,000 from one of your accounts to another. Now that's going to require two updates to this data, one to subtract $2,000 from the savings account and the other to add $2,000 to the checking account. Either both of these things happen or neither of them do, and the first change will be reversed instantly by the database if any part of the transaction fails.
  • Now there's a common acronym that you'll come across when working with transactions in a database, ACID, A-C-I-D. A transaction must be atomic, consistent, isolated, and durable.

---