Data Modeling Conceptual vs Logical vs Physical Data Model

Data Modeling: Conceptual vs Logical vs Physical Data Model

7-9 minutes

Data modeling is a technique to document a software system using entity relationship diagrams (ER Diagram) which is a representation of the data structures in a table for a company’s database. It is a very powerful expression of the company’s business requirements. Data models are used for many purposes, from high-level conceptual models, logical to physical data models and typically represented by the entity-relationship diagram. It serves as a guide used by database analysts and software developers in the design and implementation of a system and the underlining database.

What is Entity Relationship Diagram?

An Entity Relationship Diagram (ERD) is a pictorial representation of the information that can be captured by a database. Such a “picture” serves two purposes. It allows database professionals to describe an overall design concisely yet accurately. An ER Diagram can be easily transformed into the relational schema. There are three components in ERD: Entities, Attributes, and Relationships.

Entities

The number of tables you need for your database – Entities is the basic objects of ERDs. These are the tables of your database, i.e. students, courses, books, campus, employees, payment, projects. A specific example of an entity is called an instance. Each instance becomes a record or a row in a table.

Attributes

Information such as property, facts you need to describe each table – Attributes are facts or descriptions of entities. They are also often nouns and become the columns of the table. For example, for entity students, the attributes can be first name, last name, email, address, and phone numbers.

• Primary Key is an attribute or a set of attributes that uniquely identifies an instance of the entity. For example, for a student entity, student number is the primary key since no two students have the same student number. We can have only one primary key in a table. It identifies uniquely every row and it cannot be null.
• Foreign key is a key used to link two tables together. Typically you take the primary key field from one table and insert it into the other table where it becomes a foreign key (it remains a primary key in the original table). We can have more than one foreign key in a table.

Relationships: How tables are linked together – Relationships are the associations between the entities. Verbs often describe relationships between entities. We will use Crow’s Foot Symbols to represent the relationships. Three types of relationships are discussed in this lab. If you read or hear cardinality ratios, it also refers to types of relationships.

Cardinality: it defines the possible number of occurrences in one entity which is associated with the number of occurrences in another. For example, ONE team has MANY players. When present in an ERD, the entity Team and Player are inter-connected with a one-to-many relationship.