Underlying the structure of a database is the data model : a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. To illustrate the concept of data model, two of the data models are outlined below. One data model is entity-relationship model and the other is relational model. Both provides a way to describe the design of a database at the logical level.

The Entity-Relationship Model

• The Entity-relationship (E-R) data model is based on a perception that it consists of a collection of basic objects, called as entities , and of relationships among these objects (or entities).

• An entity is a "thing" or "object" in the real world that is distinguishable from other objects. For example, each person is an entity, and bank accounts can be considered as entities.

• In databases, entities are described by a set of attributes. For example, the attributes account-number and balance may describe one particular account in a bank and they form attributes of the account entity set. Similarly, attributes customer-name, customer-street address and customer-city may describe a customer entity.

• An extra attribute customer-id is used to uniquely identify customers. A unique customer identifier must be assigned to each customer. In the U.S., many enterprises use the social security number of a person ( a unique number the U.S. government assigns to every person in the United States) as a customer identifier.

• A relationship is an association among several entities. For example, a depositor relationship associates a customer with each account that he or she has. The set of all entities of the same type and the set of all relationships of the same type are termed as entity set and relationship set, respectively.

• The overall logical structure of a database can be expressed graphically by an E-R diagram, which is built up from the components like
  
  • Rectangles, which represent entity sets
  • Ellipses, which represent attributes
  • Diamonds, which represent relationships among entity sets
  • Lines, which links attributes to entity sets and entity sets to relationships
• Each component is labeled with the entity or relationship that it represents.

As an illustration, consider a part of database banking system consisting of customers and of the accounts that these customers have. Following figure shows the corresponding E-R diagram. The E-R diagram indicates that there aretwo entity sets, customer and account with attributes as outlined earlier. The diagram also shows a relationship depositor between customer and account.

Relational Model

• The relational model uses a collections of tables to represent both data and the relationships among those data. Each table has multiple columns, andeach column has a unique name. Following figure represents a sample relational database comprising three tables: one shows details of bank customers, the second shows accounts, and the third shows which account belong to which customers.
• The first table, the customer table, shows, for example, that the customer identified by customer-id 192-83-7465 is named Johnson and lives at 12 Alma St. in Palo Alto.
• The second table, account, shows, for example, that account A-101 hasa balance of $500, and A-201 has a balance of $900.
• The third table shows which accounts belong to which customers. For example, account number A-101 belongs to the customer whose customer-id is 192-83-7465, namely Johnson, and customers 192-83-7465 (Johnson) and 019-28-3746 (Smith) share account number A-201.
• The relational model is an example of a record-based model.Record-based models are so named because the database is structured in fixed-format records of several types. Each table contains records of a particular type. Each record type defines fixed number of fields, or attributes. The columns of the table correspond to the attributes of the record type.
• The relational model is the most widely used data model, and a vast majority of current database systems are based on the relational model.
• The relational model is at a lower level of abstraction than the E-R model. Database designs are often carried out in E-R model, and then translated to the relational model.

There are also other data models like Object-oriented data model, Object-relational data model, network data model and hierarchical data model.

• Written by Abhishek Dua.
• In category MySQL.
• 15 years ago.

Hot Hits: 3281

Like it on Facebook, Tweet it or share this article on other bookmarking websites.