Database Modeling

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

Database Modeling

Rating:

Database creation emphasizes on three phases or models: Conceptual, Logical, and Physical. To create a well-defined database, one must be able to create these three phases successfully. If there is a flaw in any of these phases, then implementation of the database will not be successful.

The conceptual model describes entities, attributes, and the relationship between entities.

The logical model refines the conceptual model. It defines primary key, foreign key, and normalizes the tables.

The physical model refines the conceptual model. It tunes the model to implement the specific functionality of the database management systems.

The section below discusses each model in detail:

Conceptual Model:

The conceptual model defines various components of a database. These components are discussed below:

• Entity: It is the basic unit of database modeling and is the most important unit of a database. The database modeling is done around it. An entity can be a living entity such as student, employee, etc., or a non-living entity such as car, engine, school, etc.
• Attribute: An attribute describes the specific characteristics of an entity, such as the Student entity has name, age, enrolment, etc. as its attributes.
• Data value: A data value is the data stored in a given attribute for a given instance. For example, the Student entity has the name attribute and it contains "Roger". Here, Roger is the data value.
• Key: A key is defined as a set of attributes that separates one instance of an entity from another. It uniquely identifies a given entity. For example, the enrolment attribute of Student uniquely identifies each student. Therefore, enrolment is a key.
• Relationship: A relationship defines a link between two entities. It defines how entities are linked together. For example, a student reads books. Therefore, the relationship between student and book is "reads".

ER Diagram (ERD): An ER-diagram (Entity Relationship-diagram) is used to depict the relationship between two entities. It represents a graphical representation of entity relationship.

An ERD defines the following:

• It defines the data scope.
• It helps in describing the relationship in a simpler form.
• It provides a pictorial representation of an entity relationship.

After completing the conceptual modeling phase, the following should be checked:

• There should not be more than one relationship between two entities.
• There should be no circular or mutual dependencies between entities.
• Balanced one-to-one relationships must be eliminated.
• An entity cannot have an attribute that is a candidate key of another entity.

Logical Model:

In the conceptual model, we define entities, attributes, and relationship among entities. After conceptual model, logical model is created. Following steps are taken in logical model:

• The table is made in 1NF (First normal form).
• Identification of primary key among candidate keys.
• Use of foreign key to represent the relationship between two tables.

It defines primary key, foreign key, and normalizes the tables.

The logical model deals with database normalization. Database normalization is a vital aspect of data modeling. There are various advantages of normalization. These are listed below:

• It reduces data redundancy, i.e. there are no duplicate values in the database. Duplicate values slow down the searching process, require more space in the memory, and above all, results in anomalies such as update. If the data is changed in one place, then the duplicate copies of it must be changed; otherwise, data inconsistency occurs.
• It is a reversible process. It splits the table into one or more tables but this decomposition is loss less, i.e. if we perform a join on these tables, we get the original table and no unauthentic data is introduced.
• It reduces deletion and insertion anomalies.

There are mainly three types of normalization forms: 1NF (first normal form), 2NF(second normal form), and 3NF (third normal form).

Before defining the primary key, the tables must be normalized.

A primary key is an attribute, which uniquely defines an entity. An attribute is defined as a key if it has the following characteristics:

• It uniquely identifies the entity.
• It cannot be null.
• Its value should remain the same throughout the life of the instance.
• It cannot contain group of letters or digits that hold additional information.
• It should be accessible to all the users of the database.

All the attributes, which can serve as a primary key, are known as candidate keys.
The relational model has no data structure to represent a relationship. To represent a relationship between entities, a foreign key is used. A foreign key is the primary key of one entity. When it is migrated from one entity to another entity, it is known as a foreign key. Migration of a key is always done from an independent entity to a dependent entity. For example, there are two tables given below:

Author AuthorID Name Contact No. Address

Book BookID Title Category AuthorID

Here, AuthorID is the primary key of the Author table and it is migrated from the Author table to the Book table. In the Book table, it is referred to as the foreign key.

Physical model:

This phase represents the transition of the logical model into the physical model. The following steps are required to be taken in the physical model:

• Identifying the target database management systems.
• Identifying indexes.
• Modeling of specific functionality of database management systems.
• Identifying performance issues.

This phase includes the specific requirements of how the data will be used and the business functions and processes that will use it. In this phase, the designer may modify the model to support fast retrieval of frequently used queries, to provide support for indexes, and to take benefits of auto increment. The designer may denormalize one or more tables for performance reasons.

Denormalization: It is a process of converting a normalized logical design back to its original state for performance reasons. The process of denormalization has the following advantages:

• It minimizes the joins and foreign keys.
• It speeds up data retrieval.
• It reduces the number of indexes and tables to process them.
• It reduces the time required to modify the data.

With all the above-mentioned advantages, it has some disadvantages also:

• It is an application specific process. If the application is changed, then the process of denormalization also needs to reevaluated.
• It can increase the size of the tables by adding redundant or derived columns.
• It requires more code to be written in order to maintain data integrity.

The process of denormalization is considered in the following conditions:

• If all frequently used queries require access to the full set of joined data.
• If a number of applications perform table scans when joining tables.
• If the computational complexity of derived columns require temporary tables or complex queries.

After the successful completion of all these phases, the implementation phase is performed. This phase converts the physical model into specific target platform using data definition language. This phase considers the features and limitations of specific programming languages, software parameters, and hardware configurations.

Rating: