Data Modelling is a process in Database Management System (DBMS) that organizes, structures, and defines the data in the database, which helps to maintain the integrity and efficiency of the data. Data modeling provides a blueprint for the database by using elements that define relationships and constraints. By defining the model, the user will get the idea of how to create a database with better performance and reduced redundancy. There are many modelling methods to make better interaction between the user, the database administrator, and stakeholders. In this article, you will learn about data modeling, its tools, and their features.
Table of Contents:
Different Types of Models in DBMS
The three main types of models in DBMS are the conceptual model, logical model, and physical model.
Conceptual Model in DBMS
The conceptual model in DBMS is a high-level interface. This model will not explain the technical details of the data. This will only help the user to know and understand the concept. The concepts were explained in attributes and relationships with the help of an Entity-Relationship (ER) diagram. The ER diagram will be helpful to let the stakeholders know the hierarchy and structure of the data.
Example: If the database wants to store data of banking, the conceptual model will create an Entity Relationship (ER) with the manager, transactions, account, and user ID. This entity diagram will allow the stakeholders and users to learn the structure of the database.
Example of ER:
+————-+ +————-+ +—————+
| Customer |——<| Account |——<| Transaction |
+————-+ 1:N +————-+ 1:N +—————+
|
| 1:N
v
+————-+ +————-+
| Loan | | Branch |
+————-+ +————-+
| 1:N
v
+————-+
| Employee |
+————-+
Logical Model in DBMS
The Logical model in DBMS is helpful to decide the elements that are present in the database. This model will decide the primary keys, foreign keys, attributes and the relationship between them. It will make sure of normalization and data integrity. This model will represent data in a table format, but neither of the entities will depend on a specific database.
Example: In a banking system, the user will have their own user ID and transaction ID, which will be connected to their loans in a particular bank.
Diagram representation:
+────────────+ +────────────+ +─────────────────+
| CUSTOMER |◄───┐ | ACCOUNT |◄──────| TRANSACTION |
+────────────+ │ +────────────+ +─────────────────+
▲ │ │ ▲ ▲
│ └────────┼─────┘ │
│ 1:N │ 1:N │ 1:N
▼ ▼ │
+────────────+ +────────────+ │
| LOAN | | BRANCH |───────────────────┘
+────────────+ +────────────+
│ 1:N
▼
+────────────+
| EMPLOYEE |
+────────────+
Physical Model in DBMS
Physical model in DBMS is the actual implementation of the database. It will show the user how the data is stored and managed in a database. The physical model will represent and decide the details of table structures, where they will be stored, and the constraints that can be used, the table indexes, the partitions of tables or data, and the type of data that can be used to make the database more efficient. This model will transform the logical design into a working schema with the help of the SQL language. SQL will optimize the query performance by indexing and methods of storing data efficiently.
Example: MySQL will be very efficient in creating the working schema in a database.
Diagram representation:
+───────────────+
| CUSTOMER |
+───────────────+
| PK customer_id|
| name |
| address |
| phone |
| email (UQ) |
| ssn (ENC) |
+───────────────+
△ 1
│
│ * (1:N)
+───────────────+ +─────────────────+
| ACCOUNT |─────| TRANSACTION |
+────────────+ +────────────────+
| PK account_id | * | PK transaction_id|
| FK customer_id| ◄──────┤ FK account_id |
| FK branch_id | | amount |
| type | | type |
| balance | | timestamp |
| status | +─────────────────+
+────────────+
△ 1
│
│ 1 (1:1)
+───────────────+
| BRANCH |
+───────────────+
| PK branch_id |
| location |
| FK manager_id |
+───────────────+
△ 1
│
│ * (1:N)
+───────────────+
| EMPLOYEE |
+───────────────+
| PK employee_id|
| FK branch_id |
| name |
| salary(ENC)|
+───────────────+
△ 1
│
│ * (1:N)
+───────────────+
| LOAN |
+───────────────+
| PK loan_id |
| FK customer_id|
| amount |
| interest |
| status |
+───────────────+
Explanation: This will be the diagrammatic representation of the banking system with indexes and attributes of the table. This is the working schema for a banking system that has better performance and data integrity.
1. Creating an ER Diagram
An Entity-Relationship (ER) is a visual representation of data structures in a database. An ER diagram contains entities, attributes, and relationships of the entities. An ER diagram helps the user to understand the design and relationships of the data elements by mapping them together. It is used to create a design strategy to have a clear understanding of data dependencies and data flow.
2. Using Schema
Using schema, the ER diagrams or conceptual model are converted into a physical database schema, which is a model representation. Schema will automatically generate tables, keys, indexes, and relationships with the help of SQL scripts. It follows dynamics, so the user does not manually do the queries. This will have consistency.
3. Reverse Engineering
Data modelling has reverse engineering that allows users to generate or create a model from the data that already exists in the database. This will analyze the structure of the data and then create a model based on that. This will be helpful to redesign the existing model, which is not clear enough to understand. The reverse engineering is very helpful in optimizing and improving the efficiency of the database by improving the existing database. Reverse engineering is used to improvise, modify, and redesign the database. This will help the business in upgrading the database without compromising its integrity and structure.
4. Multi-User and Version Control
The Multi-user function allows multiple users to work on the same model simultaneously without any disturbance to other users in the database. It tracks all the logs of changes made, version history, and, if needed, users can revert to the previous version. For large projects or databases, it integrates with the help of source control systems like Git to manage the changes.
5. Error Prevention and Data Consistency
The Data model will ensure that the designed models follow the rules and integrity of the constraints. They will find lost records, duplicate (redundant) relationships, and missing constraints, which may lead to errors during execution. By adhering to the normalization rules and integrity constraints, like primary key, foreign key, and unique constraints, these follow-up checks will make sure of the consistency and integrity of the model. It will ensure a properly structured and readable database.
6. Use of Other DBMS Software
Data Modelling allows creating and managing the database with the help of other database management systems (DBMS) such as Oracle, MySQL, SQL Server, PostgreSQL, and NoSQL databases. It is very flexible and lets users decide the databases that are suitable for their applications. It is so compatible that when users want to, they can transfer the data from one database to another database easily.
7. Generating Documents Dynamically
This will easily generate the log report with detailed information on modifications or changes made, database structure, schema, relationships of data elements, and indexing strategies in a report. This will make the database strategies or draft of the model easily understandable for the developers, administrators, and stakeholders without any need for technical knowledge. It is very useful when they need to conduct auditing and compliance. This will make sure all the updates are documented properly throughout the application development.
8. Performance Optimization and Indexing
This will give suggestions to the user for proper indexing using partitioning and query optimization. It will analyze the relationships of the entity and the plans that were drafted to execute the model for improving the database performance. It will reduce the repetition (duplicates) in the table, so that data can be fetched more efficiently.
9. Integrating with Development and Deployment Tools in DBMS
This allows easier integration with the help of CI/CD pipelines, Database Management Systems (DBMS), and development tools. The tools help the database models to convert into a production model directly with just simple manual work from the users, which will produce an error-free model. It will ensure that the developing database and production database are being updated simultaneously, so that it will give proper data with regular updates.
10. Security and Access Control
The access-control system will make sure all the databases follow role-based security access. This will restrict an unauthorized user from accessing the database or modifying the data model. Using methods like encryption, Multi-Factor authentication (MFA), and authorization mechanisms like usernames & passwords will help to protect the sensitive data in a data model. It complies with security policies in industries like HIPAA, GDPR, and PCI-DSS, which ensures best practices of security for data modelling.
1. Entity Relationship (ER)/Studio
It is developed by IDERA, which is a powerful data modelling tool to design, document, or manage a database. It supports conceptual, logical, and physical data modelling that can be used for creating an ER diagram and deciding the schema for the database. It has robust features that will allow the user to work simultaneously with the same model, with version control. It has features like data dictionary management, impact analysis, and metadata modelling for large database design. It supports multiple DBMS platforms like SQL Server, Oracle, MySQL, PostgreSQL, etc.
Example: In Bank, ER/Studio is used to design a secure and scalable database that can manage customer transactions efficiently. It will ensure data integrity, following the rules and compliance standards like GDPR.
2. IBM InfoSphere Data Architect
IBM designed these comprehensive data modelling tools for large-scale database architecture. It supports data modelling for platforms like relational, NoSQL, and big data platforms, which are compatible with modern technologies. It has advanced capabilities like data integration, business reference support, and metadata management. It offers schema generation dynamically, reverse engineering, and data governance for maintaining data integrity. It can integrate well with IBM’s other data management tools and analytical solutions, making it ideal for large-scale database management businesses.
Example: In a hospital, they use InfoSphere Data Architect to create a data model and have constant patient records across all the branches around the world. The IBM InfoSphere will make sure the interaction across databases is maintained properly and complies with HIPAA regulations.
3. Microsoft Visio Tool
The Microsoft Visio tool is used to design ER diagrams effectively. It has data modelling capacity that allows users to design a database structure visually and connect it to the live database server for regular automatic updates. It is not only for designing a data model but also
For conceptual and logical data modeling, Microsoft already uses Microsoft Office by connecting a single server across multiple developers, which enables updates and members to work on the project simultaneously.
It connects with Microsoft servers for integration and other relational databases for schema and reverse engineering. Microsoft has Microsoft 365, which offers cloud-based connections that enable teams to work on diagrams.
Example: A software development team in a company can use Visio to design an ER diagram with the project management system, where the entity has hierarchical relationships with the project manager and the assigned person for the project, before implementing it in SQL Server.
4. Oracle SQL Developer Data Modeler
Oracle SQL Developer is a free tool for data modeling. It is particularly designed for the Oracle database. It supports conceptual, logical, and physical modelling. It can also generate SQL Scripts based on the model for database creation. It has a user-friendly interface that defines entities, attributes, relationships, constraints, and indexes. This tool is ideal for developers who use Oracle database Administrators to integrate the database.
Example: An E-Commerce company uses a stock management system where the Oracle SQL developer will develop a data model design to handle orders, transactions, shipping details, stocks, and user details in their Oracle-based database.
5. SAP PowerDesigner
SAP PowerDesigner is a tool used in large-scale enterprises. It is very strong for database architecture, data governance, and metadata management. It can be used to model conceptual, logical, and physical things, and also has capabilities like forward and reverse engineering. It can create an impact analysis for an enterprise. SAP has advanced integration, which is very useful for large-scale businesses, as it supports robustness and version control that can help a large team work on models efficiently. It can support relational and NoSQL databases and big data analysis, which can be very helpful for modern data systems.
Example: An MNC that uses SAP PowerDesigner to design and manage the complex database tasks in all SAP ERP systems. It makes sure that data is transferred to all the financial, HR, and supply chain modules.
6. Lucidchart
Lucidchart is a cloud-based diagramming tool that can help users create ER diagrams and some basic data modelling features. It has a built-in drag-and-drop option for designing database structures. It will create the entity relationship virtually. It is not dedicated to modelling tools, but can be used for quick, easy, collaborative designs for teams. It supports platforms to integrate like Google Drive, Confluence, Jira, and Microsoft Office. It can be very efficient to work remotely and offer real-time collaboration for agile workers.
Example: A simple, small company like a start-up can use Lucidchart to collaboratively design a database Schema for their software as a Service (SAAS) application. It is useful for the team people (Group of Users) to give suggestions, feedback, and updates in real time.
7. Toad Data Modeler Tools
The Toad Data Modeler tool is developed by Quest Software and is used to design and manage the database schema. It supports most of the databases, like relational, NoSQL, Oracle, MySQL, PostgreSQL, SQL Server, and MongoDB. It also has reverse engineering, schema comparison, and generates SQL scripts based on the model created. It has a user-friendly interface with drag-and-drop options for easy modeling. There are some features like impact analysis, automation, and reporting for development and maintenance.
Example: In an export company, they use Toad Data Modeler to model a database for tracking the ships and containers in a warehouse, using multiple SQL and NoSQL database systems.
8. DbSchema Tools
The DBSchema is a multi-platform tool that supports relational, NoSQL, and cloud databases. It has a very powerful visual interface for designing, documenting, and deploying the database schema. It has features like offline modelling, which is when the user can design a data model without the use of the internet or needing a live database connection. It has built-in features that build queries, a visual explore, and schema synchronization tools. With the help of cloud-based technology, the team can share the schema and design to work on at the same time.
Example: DbSchema can be used in a gaming company where the designs can be shared throughout the team for making changes. The database can store player profiles, progress of the game, and game purchases.
During modelling a database, it is important to select the right data modelling tool. To gain an efficient model, you need to use specific tools for specific requirements. The developer has to choose the right tool by considering factors like business needs, technical requirements and industry standards.
Factors to Consider
Some major factors need to be considered before creating a data model for businesses.
Industry-Specific Requirements:
- In the healthcare industry, they have to follow HIPAA regulations before creating a data model that supports storing patient records, an EHR system, which electronically stores health records in a structured form. The tools that can be used are ER/Studio and IBM InfoSphere Data Architect, to create an efficient data model.
- In finance, which requires high security and access control mechanisms to protect the database, it must undergo audit trials and regulatory compliance like GDPR, SOX, and Basel III. The tools, like ERwin Data Modeler, can be used to provide robust security and governance risk management features.
- In the E-Commerce industry, they need a model that can handle a large set of transactional data for the inventory management system, stock management, and customer flow in the market. This needs a model with strong cloud support so that the team can share the transactional data across the cloud database for easier access and updates. For example, modeling tools like Snowflake and Amazon Redshift can be used.
- In the Manufacturing industry, a user or business needs to have supply chain integration, real-time data processing, and impact analysis. The data model tools that support IoT and ERP integration, like SAP PowerDesigner, are very efficient to consider.
- In a telecommunication network, they need to handle large datasets, real-time analysis, and performance monitoring of a network. The tools need to be stronger with big data capabilities. The data model tool, like Apache Cassandra and Vertabelo, can be used.
Database Normalization
The process of organizing the data to minimize the redundancy and improve data integrity is called database Normalization. It will structure the database based on different normal forms (NF). Each NF will remove irrelevant or irregular data from the database.
Types of Normal Form in Database Normalization
There are four types of normal form in database normalization, like 1NF, 2NF, 3NF, Boyce-Codd Normal Form (BCNF)
- In the first NF, all columns must contain atomic values in a table.
- Each column in a table must consist of only one type, either it has to be numerical or variable. It can’t be a mixture of two types.
- Each row must have a unique identifier, which is the primary key.
Error code before optimizing it to 1NF:
A table contains many values in a single column that breaks the rule of 1NF atomicity.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(255),
ProductsPurchased VARCHAR(255)
);
Explanation: Here, the row contains more than one value (products and purchases) in the table, which breaks the rule of atomicity, which will make the query processing harder.
Solution to remove the error:
- The user needs to convert the multiple values row into separate rows.
- The user must make separate rows for each of the types instead of combining them in a single column.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(255)
);
CREATE TABLE OrderDetails (
OrderID INT,
Product VARCHAR(255),
PRIMARY KEY (OrderID, Product),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
Explanation: It removed the multiple values in a single row and changed them into separate rows for each data type.
- This will ensure the atomicity.
- Query processing will be easier. The user can filter the data easily.
- It prevents irregular data and multiple values in a column.
- The second normal form will make sure that the table is already in the first normal form.
- It will remove all the partial dependencies in a table.
- That is, every attribute column must fully depend on the primary key, which means the entire attribute should depend on that one primary key in a table.
Error code before optimizing it with 2NF:
In a restaurant, when a customer orders food online through the restaurant’s website, there will be a record of the customer if he/she had ordered more than 1 food item. To remove that, the user can use 2NF.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(255),
CustomerPhone VARCHAR(20)
);
Solution to remove the error in 2NF:
- Instead of fetching all the data in a single column, the user can make different columns or tables for each type.
- Can separate the orders table and the customer details to make the query processing easier.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(255),
CustomerPhone VARCHAR(20)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderDetails (
OrderID INT,
Product VARCHAR(255),
PRIMARY KEY (OrderID, Product),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
Explanation:
Here, the tables have been made separately to keep the database clean. It reduces data redundancy and makes the table more efficient.
- The third normal form will make sure that the table is already in the second normal form.
- It makes sure that an attribute is not dependent on another attribute in a table, which is called transitive dependencies.
Error before optimizing to the second normal form:
- In the customer table, if they order, the system will get their zip codes for the delivery, along with their city details. But sometimes in a table, both attributes will combine and make the table look complex.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(255),
CustomerPhone VARCHAR(20),
CustomerZIP INT,
CustomerCity VARCHAR(255) -- Transitive dependency
);
Explanation:
Two attributes must not depend on each other. They should depend on their particular primary key instead. Here, it depends on the customer ID primary key.
Solution to remove error in 3NF:
- The user must create a separate table with ZIP codes as the primary key. This will separate the two attribute dependencies.
- Now, the 3NF removed the transitive dependencies and increased the efficiency of the table.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(255),
CustomerPhone VARCHAR(20),
CustomerZIP INT,
FOREIGN KEY (CustomerZIP) REFERENCES ZIPCodes(CustomerZIP)
);
CREATE TABLE ZIPCodes (
CustomerZIP INT PRIMARY KEY,
CustomerCity VARCHAR(255)
);
Explanation: The third normal form removed the redundant data from the table and enabled data integrity.
- In Boyce-Codd normal form, the table must already be present in third normal form.
- Every determinant (a column that can uniquely identify each row) must be a candidate key.
Error before optimizing in BCNF:
- Let’s consider a college database where a professor is teaching a course in a classroom. But the room is assigned to teach only one course, but the professor may teach many courses.
- Here, the Room Number is the Course ID, but the Room Number is not the candidate key. This will cause a dependency error.
CREATE TABLE CourseAssignments (
ProfessorID INT,
CourseID INT,
RoomNumber INT,
PRIMARY KEY (ProfessorID, CourseID)
);
Explanation:
- The RoomNumber here is considered to be the CourseID. The room needs to decide the courses that need to be taught in that particular class in the table.
- But here the RoomNumber is not a candidate key, which is against the BCNF rules.
Solution to remove the error in BCNF:
- Create two separate tables to divide the table based on the keys.
- The Courses in the table will depend on CourseID and ProfessorID.
- RoomAssignments have to depend on CourseID and RoomNumber.
CREATE TABLE Professors (
ProfessorID INT PRIMARY KEY,
ProfessorName VARCHAR(255)
);
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(255),
ProfessorID INT,
FOREIGN KEY (ProfessorID) REFERENCES Professors(ProfessorID)
);
CREATE TABLE RoomAssignments (
RoomNumber INT PRIMARY KEY,
CourseID INT,
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Explanation:
- The BCNF eliminated the anomalies (irregular data) that are dependent on functional dependencies.
- It makes sure that all the determinants are candidate keys in the table.
- It prevents data inconsistencies.
|
Normal Form |
Definition |
Eliminates |
| 1NF | It makes sure that all the tables have atomicity. | Multi-valued attributes. |
| 2NF | Columns that depend on only one of the primary keys instead of all. | It eliminates the partial dependencies. |
| 3NF | Make sure that columns do not depend on the candidate key. | Remove the data that are dependent on non-key attributes. |
| BCNF | Ensures every key has its own identity key to identify them. | Eliminates the data that are functionally dependent. |
Indexing in DBMS
Indexing in a database management system (DBMS) is a technique in data structure that is used to retrieve records from a database table quickly. The functions will scan the entire database to fetch the data, but if the table or database has an index in it, then fetching the data will be easier, as the function will fetch the value based only on the index value.
Importance of Indexing in Data Modeling
It is essential to index each database for various reasons.
- The index will speed up the process of fetching the data, which will be easier as the scanning time will be reduced because of the indexed value fetching.
- It uses a sorting and grouping method with the help of subqueries like ORDERBY and GROUP BY. This will sort and group the table, and by index, the value can be fetched more easily.
- If the user wants to join two or more tables, they can specify the index value of the foreign key, which will speed up the process of joining the tables by just mentioning their index value.
- It reduces the disk input and output operations by reducing the time while scanning large amounts of data. By that, it improves the efficiency.
Types of Indexes in DBMS
There are three types of indexes in Database Management Systems (DBMS), like primary indexes, clustered indexes, and non-clustered indexes.
1. Primary Index
A primary index is used in the primary key of a table. The index will apply based on the primary key that represents the unique identity of the row. It is stored after being sorted based on the primary key.
Features:
- The Index will be created once the user defines a primary key in the table.
- The index will make sure there are no duplicate values in the table, as every index is unique.
- The primary index will help the user to fetch or search the records easily with the help of the primary key.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10,2)
);
Explanation: The Primary key here is employee ID, which creates the primary index automatically for Employee ID.
Primary Key index searching:
- The database will store all the data in a structured format.
- So, when a user needs to search for an employee by mentioning the EmployeeID = 02, then the database will find it with the help of the index value instead of scanning the whole database.
2. Clustered Index
The Clustered index in the database will determine how the value should be stored in the database physically. When a table in a database has a clustered index, the rows arranged in disk will be the same as the index value.
Features:
- The data in the row will be sorted in the database by the index value.
- The sorting of the records will be done physically at the storage level of the disk.
- Only one clustered index should be in a table.
- It is very efficient if the user uses it with range-based queries like finding values using (BETWEEN, <, >), etc.
Example:
CREATE CLUSTERED INDEX idx_emp_name ON Employees(Name);
Explanation: The database first stored the records physically based on their index value. If the user wants to retrieve or search some records, they can mention it as, for example, WHERE name = ‘Priya.’ The database will search the particular index in a row instead of searching the whole table.
The data is stored physically in a structured or ordered format. Which is useful when the user needs to search a record in ranges, like WHERE Name BETWEEN ‘F’ AND ‘P’, which can be fetched easily.
3. Non-Clustered Index
A Non-Clustered Index is a type of index that stores the sorted index entries in a separate structure from the physical structure without changing any records in the physical structure.
Features:
- The index will store the column values separately in a sorted format.
- Unlike a clustered index, there can be many unclustered indexes in a table.
- This will not change anything in the physical storage, but it will have a reference to the actual rows.
- It is very efficient when the user needs to search without affecting the structure.
Example:
CREATE NONCLUSTERED INDEX idx_salary ON Employees(Salary);
Explanation: The index will store the salary value in the structure that is sorted according to its actual records.
If the user searches WHERE Salary = 28934, it will search quickly and will give the record from the relevant row. This index will not scan the entire table.
Usages:
A simple way of everyday use is that,
SELECT * FROM Employees WHERE Salary > 74000;
This will give the salary and list details of employees whose salary is greater than 74,000.
Comparing Clustered and Non-Clustered Indexes
|
Feature |
Clustered Index | Non-Clustered Index |
| Storing data | A clustered index will store the data physically. | The data is stored separately from the physical storage with pointers to actual data. |
| Number of indexes | A clustered index will only appear once in the table. | The non-clustered index can be present more than once in a table. |
| Performance | It gives its best when searched with a range query. | It can search easily when the values are specifically mentioned. |
| Usages | Can sort and find values with ranges efficiently. | It can be used for searching and filtering the database. |
Examples of Indexes in DBMS
Case 1: To find an employee’s details with the use of their ID number.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Salary DECIMAL(10,2) NOT NULL
);
INSERT INTO Employees (EmployeeID, Name, Salary) VALUES
(103, 'Mary', 24900),
(105, 'Kani', 45000),
(101, 'Karan', 80000),
(107, 'Pavi', 59600);
CREATE NONCLUSTERED INDEX idx_salary ON Employees(Salary);
SELECT EmployeeID, LEFT(Name, 5) AS Name, Salary FROM Employees WHERE EmployeeID = 103;
Output:
Explanation: The indexes fetched the employee details of the employee whose EmployeeID = 103.
Case 2: To fetch the details of the customer whose name starts with ‘N.’
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL,
OrderDate DATE NOT NULL,
TotalAmount DECIMAL(10,2) NOT NULL
);
INSERT INTO Orders (OrderID, CustomerName, OrderDate, TotalAmount) VALUES
(1, 'Naveen', '2024-01-10', 250.50),
(2, 'Praveen', '2024-01-15', 300.00),
(6, 'Tarun', '2024-03-10', 350.25);
CREATE NONCLUSTERED INDEX idx_customer_orderdate ON Orders(CustomerName, OrderDate);
SELECT OrderID, LEFT(CustomerName, 7) AS CustomerName, OrderDate, TotalAmount
FROM Orders WHERE CustomerName LIKE 'N%' ORDER BY OrderDate DESC;
Output:
Explanation: In the Clustered Index, the customer name and order details of the customer with the name that starts with N have been fetched.
Conclusion
Organizing and structuring data in a structured way, managing it, and being efficient so it is reliable and maintains the integrity of the data while supporting the goal of reducing redundancy in a DBMS. Data modeling is performed within the conceptual, logical, and physical models of data modeling to create databases. Data modelling tools, such as ER/Studio, Oracle SQL Developer, and SAP PowerDesigner, will help in visualizing and optimizing the data model design. Normalization techniques such as 1NF, 2NF, 3NF, and BCNF will reduce all anomalies, while indexing techniques such as primary index, clustered index, and non-clustered index will help boost the performance of the queries. When it is used properly, data modeling results in a database that is scalable, secure, and able to perform. In this article, you have gained knowledge about data modeling in database management systems (DBMS).
You can learn more about SQL in the SQL Course and also explore SQL Interview Questions prepared by industry experts.
Data Modelling in DBMS – FAQs
1. What is database modeling in DBMS?
Database modeling in DBMS is the process of designing and structuring data to ensure efficiency, integrity, and minimal redundancy.
2. What are the 3 types of data models in DBMS?
Conceptual Model, Logical Model, Physical Model.
3. What is data modeling, and what are its types?
Data modeling is the process of creating a visual representation of data structures. Its types include Conceptual, Logical, Physical, Relational, and Dimensional modeling.
4. What are the 4 types of databases?
Relational, NoSQL, Hierarchical, Network.
5. What are the five different types of database structures?
Relational, Hierarchical, Network, Object-oriented, Document-based.