Data Models in DBMS

Data model is a collection of conceptual tools for describing data, relationships of data, data semantics, and consistency constraints. There are two models to describe the design of the database at the logical level. 

The entity – relationship model

The entity – relationship (ER) data model consists of a collection of basic objects, called entities, and of relationships among these objects.

Entities are described by set of attributes in the database. For example, the attributes employee_name and employee_salary may describe particular information in a company, of the employee entity set. An extra attribute employee_id is used to uniquely identify the employees. 

A relationship is an association among several entities. The overall logical schema of the database can be represented graphically by an E – R diagram. It consists of following components:

·       Rectangles, to represent entity sets
·       Ellipses, to represent attributes
·       Diamonds, to represent relationship among entity sets
·       Lines, to link attribute to entity sets and entity sets to relationship.

The E – R model represents constraints that the database contents must conform. Mapping cardinalities, one of the important constraint, expresses the number of entities to which another entity can be associated via relationship set. 

For example, consider a database banking system consisting of customers and their account. Figure – 1 represents an E – R diagram with two entity sets, customer and account, and relationship depositor, between customer and account.

Figure – 1: An illustration of E – R diagram.

Relational model

A collection of tables are used by relational model to represent data and relationship among these data. Each table consists of multiple columns, and each column has a unique name.

Consider the below given example, where three tables are demonstrated. First table shows details of Customer, second table shows details of Account and third table shows which accounts belong to which customers.

(a)  Customer table

(b)  Account table

(c)  Depositor table

Figure – 2: An illustration of relational model

The first, customer table, shows, for example, the customer identified by customer_id 104 is named Akshay, has customer_number as 8901234567, lives in White fields, Banglore, and pincode is 560066.

The second, account table, shows, for example, the account_number A – 3008, has balance of ₹80000 and account_number A – 5009 has balance of ₹30000. 

The third, depositor table shows which account belongs to which customer. For example, account_number A – 2004 belongs to customer with customer_id 230. Similarly, account_number A – 4006 belongs to customer with customer_id 459. 

The relational model is an example of record based model because the database is structured in a fixed – format records. Each table contains particular type of records. Each record type defines fixed number of attributes. The columns of the table correspond to the attributes of the record type.