# data[].attributes.body
Hi everyone..Assalamualaikum & greetings everyone.  I am Mdm. Jihadah Ahmad your lecturer.This is an additional online platform for Database Fundamentals class activities.  Our main platform will still be the KUPTM LMS. I shall update the content weekly according to our class progress. I do hope you enjoy the class & best wishes

# data[].attributes.attachment_link.description
The one-page guide to MySQL: usage, examples, links, snippets, and more.

# data[].attributes.headline
MYSQL NEW VERSION INSTALLATION VIDEO-WINDOWS

# data[].attributes.subject
MYSQL NEW VERSION INSTALLATION VIDEO-WINDOWS

# data[].attributes.attachment_link.description
The world’s most popular open source database

# data[].attributes.headline
Chapter 4 - Entity Relationship Modelling

# data[].attributes.subject
Chapter 4 - Entity Relationship Modelling

# data[].attributes.headline
ISO COVER for assignment/project submission

# data[].attributes.subject
ISO COVER for assignment/project submission

# data[].attributes.attachment_link.readable_text
Data models define how the logical structure of a database is modeled. Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define how data is connected to each other and how they are processed and stored inside the system.

The very first data model could be flat data-models, where all the data used are to be kept in the same plane. Earlier data models were not so scientific, hence they were prone to introduce lots of duplication and update anomalies.

Entity-Relationship Model

Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them. While formulating real-world scenario into the database model, the ER Model creates entity set, relationship set, general attributes and constraints.

ER Model is best used for the conceptual design of a database.

ER Model is based on −

These concepts are explained below.

Entity − An entity in an ER Model is a real-world entity having properties called attributes. Every attribute is defined by its set of values called domain. For example, in a school database, a student is considered as an entity. Student has various attributes like name, age, class, etc.

Relationship − The logical association among entities is called relationship. Relationships are mapped with entities in various ways. Mapping cardinalities define the number of association between two entities.

Mapping cardinalities −

Relational Model

The most popular data model in DBMS is the Relational Model. It is more scientific a model than others. This model is based on first-order predicate logic and defines a table as an n-ary relation.

The main highlights of this model are −

Data is stored in tables called relations.

Relations can be normalized.

In normalized relations, values saved are atomic values.

Each row in a relation contains a unique value.

Each column in a relation contains values from a same domain.

# data[].attributes.attachment_link.description
DBMS Data Models - Data models define how the logical structure of a database is modeled. Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define how data is connected to each other and how they are processed and stored inside the system.

# data[].attributes.attachment_link.readable_text
8 minute read

Types of database models

There are many kinds of data models. Some of the most common ones include:

Hierarchical database model

Relational model

Network model

Object-oriented database model

Entity-relationship model

Document model

Entity-attribute-value model

Star schema

The object-relational model, which combines the two that make up its name

You may choose to describe a database with any one of these depending on several factors. The biggest factor is whether the database management system you are using supports a particular model. Most database management systems are built with a particular data model in mind and require their users to adopt that model, although some do support multiple models.

In addition, different models apply to different stages of the database design process. High-level conceptual data models are best for mapping out relationships between data in ways that people perceive that data. Record-based logical models, on the other hand, more closely reflect ways that the data is stored on the server.

Selecting a data model is also a matter of aligning your priorities for the database with the strengths of a particular model, whether those priorities include speed, cost reduction, usability, or something else.

Let's take a closer look at some of the most common database models.

Relational model

The most common model, the relational model sorts data into tables, also known as relations, each of which consists of columns and rows. Each column lists an attribute of the entity in question, such as price, zip code, or birth date. Together, the attributes in a relation are called a domain. A particular attribute or combination of attributes is chosen as a primary key that can be referred to in other tables, when it's called a foreign key.

Each row, also called a tuple, includes data about a specific instance of the entity in question, such as a particular employee.

The model also accounts for the types of relationships between those tables, including one-to-one, one-to-many, and many-to-many relationships. Here's an example:

Within the database, tables can be normalized, or brought to comply with normalization rules that make the database flexible, adaptable, and scalable. When normalized, each piece of data is atomic, or broken into the smallest useful pieces.

Relational databases are typically written in Structured Query Language (SQL). The model was introduced by E.F. Codd in 1970.

Hierarchical model

The hierarchical model organizes data into a tree-like structure, where each record has a single parent or root. Sibling records are sorted in a particular order. That order is used as the physical order for storing the database. This model is good for describing many real-world relationships.

This model was primarily used by IBM's Information Management Systems in the 60s and 70s, but they are rarely seen today due to certain operational inefficiencies.

Network model

The network model builds on the hierarchical model by allowing many-to-many relationships between linked records, implying multiple parent records. Based on mathematical set theory, the model is constructed with sets of related records. Each set consists of one owner or parent record and one or more member or child records. A record can be a member or child in multiple sets, allowing this model to convey complex relationships.

It was most popular in the 70s after it was formally defined by the Conference on Data Systems Languages (CODASYL).

Object-oriented database model

This model defines a database as a collection of objects, or reusable software elements, with associated features and methods. There are several kinds of object-oriented databases:

A multimedia database incorporates media, such as images, that could not be stored in a relational database.

A hypertext database allows any object to link to any other object. It's useful for organizing lots of disparate data, but it's not ideal for numerical analysis.

The object-oriented database model is the best known post-relational database model, since it incorporates tables, but isn't limited to tables. Such models are also known as hybrid database models.

Diagramming is quick and easy with Lucidchart. Start a free trial today to start creating and collaborating.
Make a database diagram

Object-relational model

This hybrid database model combines the simplicity of the relational model with some of the advanced functionality of the object-oriented database model. In essence, it allows designers to incorporate objects into the familiar table structure.

Languages and call interfaces include SQL3, vendor languages, ODBC, JDBC, and proprietary call interfaces that are extensions of the languages and interfaces used by the relational model.

Entity-relationship model

This model captures the relationships between real-world entities much like the network model, but it isn't as directly tied to the physical structure of the database. Instead, it's often used for designing a database conceptually.

Here, the people, places, and things about which data points are stored are referred to as entities, each of which has certain attributes that together make up their domain. The cardinality, or relationships between entities, are mapped as well.

A common form of the ER diagram is the star schema, in which a central fact table connects to multiple dimensional tables.

Other database models

A variety of other database models have been or are still used today.

Inverted file model

A database built with the inverted file structure is designed to facilitate fast full text searches. In this model, data content is indexed as a series of keys in a lookup table, with the values pointing to the location of the associated files. This structure can provide nearly instantaneous reporting in big data and analytics, for instance.

This model has been used by the ADABAS database management system of Software AG since 1970, and it is still supported today.

Flat model

The flat model is the earliest, simplest data model. It simply lists all the data in a single table, consisting of columns and rows. In order to access or manipulate the data, the computer has to read the entire flat file into memory, which makes this model inefficient for all but the smallest data sets.

Multidimensional model

This is a variation of the relational model designed to facilitate improved analytical processing. While the relational model is optimized for online transaction processing (OLTP), this model is designed for online analytical processing (OLAP).

Each cell in a dimensional database contains data about the dimensions tracked by the database. Visually, it's like a collection of cubes, rather than two-dimensional tables.

Semistructured model

In this model, the structural data usually contained in the database schema is embedded with the data itself. Here the distinction between data and schema is vague at best. This model is useful for describing systems, such as certain Web-based data sources, which we treat as databases but cannot constrain with a schema. It's also useful for describing interactions between databases that don't adhere to the same schema.

Context model

This model can incorporate elements from other database models as needed. It cobbles together elements from object-oriented, semistructured, and network models.

Associative model

This model divides all the data points based on whether they describe an entity or an association. In this model, an entity is anything that exists independently, whereas an association is something that only exists in relation to something else.

The associative model structures the data into two sets:

A set of items, each with a unique identifier, a name, and a type

A set of links, each with a unique identifier and the unique identifiers of a source, verb, and target. The stored fact has to do with the source, and each of the three identifiers may refer either to a link or an item.

Other, less common database models include:

Semantic model, which includes information about how the stored data relates to the real world

XML database, which allows data to be specified and even stored in XML format

Named graph

Triplestore

NoSQL database models

In addition to the object database model, other non-SQL models have emerged in contrast to the relational model:

The graph database model, which is even more flexible than a network model, allowing any node to connect with any other.

The multivalue model, which breaks from the relational model by allowing attributes to contain a list of data rather than a single data point.

The document model, which is designed for storing and managing documents or semi-structured data, rather than atomic data.

Databases on the Web

Most websites rely on some kind of database to organize and present data to users. Whenever someone uses the search functions on these sites, their search terms are converted into queries for a database server to process. Typically, middleware connects the web server with the database.

The broad presence of databases allows them to be used in almost any field, from online shopping to micro-targeting a voter segment as part of a political campaign. Various industries have developed their own norms for database design, from air transport to vehicle manufacturing.

# data[].attributes.attachment_link.description
In-depth explanation and comparison of all the major database models, with examples, definitions, and more.

# data[].attributes.attachment_link.readable_text
What is Data Model in DBMS and what are its types?

Data Model

Data Model gives us an idea that how the final system will look like after its complete implementation. It defines the data elements and the relationships between the data elements. Data Models are used to show how data is stored, connected, accessed and updated in the database management system. Here, we use a set of symbols and text to represent the information so that members of the organisation can communicate and understand it. Though there are many data models being used nowadays but the Relational model is the most widely used model. Apart from the Relational model, there are many other types of data models about which we will study in details in this blog. Some of the Data Models in DBMS are:

1. Hierarchical Model

2. Network Model

3. Entity-Relationship Model

4. Relational Model

5. Object-Oriented Data Model

6. Object-Relational Data Model

7. Flat Data Model

8. Semi-Structured Data Model

9. Associative Data Model

10. Context Data Model

Hierarchical Model

Hierarchical Model was the first DBMS model. This model organises the data in the hierarchical tree structure. The hierarchy starts from the root which has root data and then it expands in the form of a tree adding child node to the parent node. This model easily represents some of the real-world relationships like food recipes, sitemap of a website etc. Example: We can represent the relationship between the shoes present on a shopping website in the following way:

Features of a Hierarchical Model

1. One-to-many relationship: The data here is organised in a tree-like structure where the one-to-many relationship is between the datatypes. Also, there can be only one path from parent to any node. Example: In the above example, if we want to go to the node sneakers we only have one path to reach there i.e through men's shoes node.

2. Parent-Child Relationship: Each child node has a parent node but a parent node can have more than one child node. Multiple parents are not allowed.

3. Deletion Problem: If a parent node is deleted then the child node is automatically deleted.

4. Pointers: Pointers are used to link the parent node with the child node and are used to navigate between the stored data. Example: In the above example the ' shoes ' node points to the two other nodes ' women shoes ' node and ' men's shoes ' node.

Advantages of Hierarchical Model

It is very simple and fast to traverse through a tree-like structure.

Any change in the parent node is automatically reflected in the child node so, the integrity of data is maintained.

Disadvantages of Hierarchical Model

Complex relationships are not supported.

As it does not support more than one parent of the child node so if we have some complex relationship where a child node needs to have two parent node then that can't be represented using this model.

If a parent node is deleted then the child node is automatically deleted.

Network Model

This model is an extension of the hierarchical model. It was the most popular model before the relational model. This model is the same as the hierarchical model, the only difference is that a record can have more than one parent. It replaces the hierarchical tree with a graph. Example: In the example below we can see that node student has two parents i.e. CSE Department and Library. This was earlier not possible in the hierarchical model.

Features of a Network Model

1. Ability to Merge more Relationships: In this model, as there are more relationships so data is more related. This model has the ability to manage one-to-one relationships as well as many-to-many relationships.

2. Many paths: As there are more relationships so there can be more than one path to the same record. This makes data access fast and simple.

3. Circular Linked List: The operations on the network model are done with the help of the circular linked list. The current position is maintained with the help of a program and this position navigates through the records according to the relationship.

Advantages of Network Model

The data can be accessed faster as compared to the hierarchical model. This is because the data is more related in the network model and there can be more than one path to reach a particular node. So the data can be accessed in many ways.

As there is a parent-child relationship so data integrity is present. Any change in parent record is reflected in the child record.

Disadvantages of Network Model

As more and more relationships need to be handled the system might get complex. So, a user must be having detailed knowledge of the model to work with the model.

Any change like updation, deletion, insertion is very complex.

Entity-Relationship Model

Entity-Relationship Model or simply ER Model is a high-level data model diagram. In this model, we represent the real-world problem in the pictorial form to make it easy for the stakeholders to understand. It is also very easy for the developers to understand the system by just looking at the ER diagram. We use the ER diagram as a visual tool to represent an ER Model. ER diagram has the following three components:

Entities: Entity is a real-world thing. It can be a person, place, or even a concept. Example: Teachers, Students, Course, Building, Department, etc are some of the entities of a School Management System.

Attributes: An entity contains a real-world property called attribute. This is the characteristics of that attribute. Example: The entity teacher has the property like teacher id, salary, age, etc.

Relationship: Relationship tells how two attributes are related. Example: Teacher works for a department.

Example:

In the above diagram, the entities are Teacher and Department. The attributes of Teacher entity are Teacher_Name, Teacher_id, Age, Salary, Mobile_Number. The attributes of entity Department entity are Dept_id, Dept_name. The two entities are connected using the relationship. Here, each teacher works for a department.

Features of ER Model

Graphical Representation for Better Understanding: It is very easy and simple to understand so it can be used by the developers to communicate with the stakeholders.

ER Diagram: ER diagram is used as a visual tool for representing the model.

Database Design: This model helps the database designers to build the database and is widely used in database design.

Advantages of ER Model

Simple: Conceptually ER Model is very easy to build. If we know the relationship between the attributes and the entities we can easily build the ER Diagram for the model.

Effective Communication Tool : This model is used widely by the database designers for communicating their ideas.

Easy Conversion to any Model : This model maps well to the relational model and can be easily converted relational model by converting the ER model to the table. This model can also be converted to any other model like network model, hierarchical model etc.

Disadvatages of ER Model

No industry standard for notation: There is no industry standard for developing an ER model. So one developer might use notations which are not understood by other developers.

Hidden information: Some information might be lost or hidden in the ER model. As it is a high-level view so there are chances that some details of information might be hidden.

Relational Model

Relational Model is the most widely used model. In this model, the data is maintained in the form of a two-dimensional table. All the information is stored in the form of row and columns. The basic structure of a relational model is tables. So, the tables are also called relations in the relational model. Example: In this example, we have an Employee table.

Features of Relational Model

Tuples : Each row in the table is called tuple. A row contains all the information about any instance of the object. In the above example, each row has all the information about any specific individual like the first row has information about John.

Attribute or field: Attributes are the property which defines the table or relation. The values of the attribute should be from the same domain. In the above example, we have different attributes of the employee like Salary, Mobile_no, etc.

Advnatages of Relational Model

Simple: This model is more simple as compared to the network and hierarchical model.

Scalable: This model can be easily scaled as we can add as many rows and columns we want.

Structural Independence: We can make changes in database structure without changing the way to access the data. When we can make changes to the database structure without affecting the capability to DBMS to access the data we can say that structural independence has been achieved.

Disadvantages of Relatinal Model

Hardware Overheads: For hiding the complexities and making things easier for the user this model requires more powerful hardware computers and data storage devices.

Bad Design: As the relational model is very easy to design and use. So the users don't need to know how the data is stored in order to access it. This ease of design can lead to the development of a poor database which would slow down if the database grows.

But all these disadvantages are minor as compared to the advantages of the relational model. These problems can be avoided with the help of proper implementation and organisation.

Object-Oriented Data Model

The real-world problems are more closely represented through the object-oriented data model. In this model, both the data and relationship are present in a single structure known as an object. We can store audio, video, images, etc in the database which was not possible in the relational model(although you can store audio and video in relational database, it is adviced not to store in the relational database). In this model, two are more objects are connected through links. We use this link to relate one object to other objects. This can be understood by the example given below.

In the above example, we have two objects Employee and Department. All the data and relationships of each object are contained as a single unit. The attributes like Name, Job_title of the employee and the methods which will be performed by that object are stored as a single object. The two objects are connected through a common attribute i.e the Department_id and the communication between these two will be done with the help of this common id.

Object-Relational Model

As the name suggests it is a combination of both the relational model and the object-oriented model. This model was built to fill the gap between object-oriented model and the relational model. We can have many advanced features like we can make complex data types according to our requirements using the existing data types. The problem with this model is that this can get complex and difficult to handle. So, proper understanding of this model is required.

Flat Data Model

It is a simple model in which the database is represented as a table consisting of rows and columns. To access any data, the computer has to read the entire table. This makes the modes slow and inefficient.

Semi-Structured Model

Semi-structured model is an evolved form of the relational model. We cannot differentiate between data and schema in this model. Example: Web-Based data sources which we can't differentiate between the schema and data of the website. In this model, some entities may have missing attributes while others may have an extra attribute. This model gives flexibility in storing the data. It also gives flexibility to the attributes. Example: If we are storing any value in any attribute then that value can be either atomic value or a collection of values.

Associative Data Model

Associative Data Model is a model in which the data is divided into two parts. Everything which has independent existence is called as an entity and the relationship among these entities are called association . The data divided into two parts are called items and links.

Item : Items contain the name and the identifier(some numeric value).

Links: Links contain the identifier, source, verb and subject.

Example : Let us say we have a statement "The world cup is being hosted by London from 30 May 2020". In this data two links need to be stored:

1. The world cup is being hosted by London. The source here is 'the world cup', the verb 'is being' and the target is 'London'.

2. ...from 30 May 2020. The source here is the previous link, the verb is 'from' and the target is '30 May 2020'.

This is represented using the table as follows:

Context Data Model

Context Data Model is a collection of several models. This consists of models like network model, relational models etc. Using this model we can do various types of tasks which are not possible using any model alone.

This is all about the various data model of DBMS. Hope you learned something new today.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from .

Keep Learning :)

Team AfterAcademy!

# data[].attributes.attachment_link.title
What is Data Model in DBMS and what are its types?

# data[].attributes.attachment_link.description
In this blog, we will learn about various data models present in DBMS. We will also learn about various types of data models present along with advantages and disadvantages of each model.

# data[].attributes.attachment_link.readable_text
What is Data Modelling?

Data modeling (data modelling) is the process of creating a data model for the data to be stored in a database. This data model is a conceptual representation of Data objects, the associations between different data objects, and the rules.

Data modeling helps in the visual representation of data and enforces business rules, regulatory compliances, and government policies on the data. Data Models ensure consistency in naming conventions, default values, semantics, security while ensuring quality of the data.

Data Models in DBMS

The Data Model is defined as an abstract model that organizes data description, data semantics, and consistency constraints of data. The data model emphasizes on what data is needed and how it should be organized instead of what operations will be performed on data. Data Model is like an architect's building plan, which helps to build conceptual models and set a relationship between data items.

The two types of Data Modeling Techniques are

1. Entity Relationship (E-R) Model

2. UML (Unified Modelling Language)

We will discuss them in detail later.

This Data Modeling Tutorial is best suited for freshers, beginners as well as experienced professionals. In this data model tutorial, data modeling concepts in detail-

Why use Data Model?

The primary goal of using data model are:

Ensures that all data objects required by the database are accurately represented. Omission of data will lead to creation of faulty reports and produce incorrect results.

A data model helps design the database at the conceptual, physical and logical levels.

Data Model structure helps to define the relational tables, primary and foreign keys and stored procedures.

It provides a clear picture of the base data and can be used by database developers to create a physical database.

It is also helpful to identify missing and redundant data.

Though the initial creation of data model is labor and time consuming, in the long run, it makes your IT infrastructure upgrade and maintenance cheaper and faster.

Types of Data Models in DBMS

Types of Data Models: There are mainly three different types of data models: conceptual data models, logical data models, and physical data models, and each one has a specific purpose. The data models are used to represent the data and how it is stored in the database and to set the relationship between data items.

1. Conceptual Data Model: This Data Model defines WHAT the system contains. This model is typically created by Business stakeholders and Data Architects. The purpose is to organize, scope and define business concepts and rules.

2. Logical Data Model: Defines HOW the system should be implemented regardless of the DBMS. This model is typically created by Data Architects and Business Analysts. The purpose is to developed technical map of rules and data structures.

3. Physical Data Model: This Data Model describes HOW the system will be implemented using a specific DBMS system. This model is typically created by DBA and developers. The purpose is actual implementation of the database.

Types of Data Model

Conceptual Data Model

A Conceptual Data Model is an organized view of database concepts and their relationships. The purpose of creating a conceptual data model is to establish entities, their attributes, and relationships. In this data modeling level, there is hardly any detail available on the actual database structure. Business stakeholders and data architects typically create a conceptual data model.

The 3 basic tenants of Conceptual Data Model are

Entity: A real-world thing

Attribute: Characteristics or properties of an entity

Relationship: Dependency or association between two entities

Data model example:

Customer and Product are two entities. Customer number and name are attributes of the Customer entity

Product name and price are attributes of product entity

Sale is the relationship between the customer and product

Conceptual Data Model

Characteristics of a conceptual data model

Offers Organisation-wide coverage of the business concepts.

This type of Data Models are designed and developed for a business audience.

The conceptual model is developed independently of hardware specifications like data storage capacity, location or software specifications like DBMS vendor and technology. The focus is to represent data as a user will see it in the "real world."

Conceptual data models known as Domain models create a common vocabulary for all stakeholders by establishing basic concepts and scope.

Logical Data Model

The Logical Data Model is used to define the structure of data elements and to set relationships between them. The logical data model adds further information to the conceptual data model elements. The advantage of using a Logical data model is to provide a foundation to form the base for the Physical model. However, the modeling structure remains generic.

Logical Data Model

At this Data Modeling level, no primary or secondary key is defined. At this Data modeling level, you need to verify and adjust the connector details that were set earlier for relationships.
Characteristics of a Logical data model

Describes data needs for a single project but could integrate with other logical data models based on the scope of the project.

Designed and developed independently from the DBMS.

Data attributes will have datatypes with exact precisions and length.

Normalization processes to the model is applied typically till 3NF.

Physical Data Model

A Physical Data Model describes a database-specific implementation of the data model. It offers database abstraction and helps generate the schema. This is because of the richness of meta-data offered by a Physical Data Model. The physical data model also helps in visualizing database structure by replicating database column keys, constraints, indexes, triggers, and other RDBMS features.

Physical Data Model

Characteristics of a physical data model:

The physical data model describes data need for a single project or application though it maybe integrated with other physical data models based on project scope.

Data Model contains relationships between tables that which addresses cardinality and nullability of the relationships.

Developed for a specific version of a DBMS, location, data storage or technology to be used in the project.

Columns should have exact datatypes, lengths assigned and default values.

Primary and Foreign keys, views, indexes, access profiles, and authorizations, etc. are defined.

Advantages and Disadvantages of Data Model:
Advantages of Data model:

The main goal of a designing data model is to make certain that data objects offered by the functional team are represented accurately.

The data model should be detailed enough to be used for building the physical database.

The information in the data model can be used for defining the relationship between tables, primary and foreign keys, and stored procedures.

Data Model helps business to communicate the within and across organizations.

Data model helps to documents data mappings in ETL process

Help to recognize correct sources of data to populate the model

Disadvantages of Data model:

To develop Data model one should know physical data stored characteristics.

This is a navigational system produces complex application development, management. Thus, it requires a knowledge of the biographical truth.

Even smaller change made in structure require modification in the entire application.

There is no set data manipulation language in DBMS.

Conclusion

Data modeling is the process of developing data model for the data to be stored in a Database.

Data Models ensure consistency in naming conventions, default values, semantics, security while ensuring quality of the data.

Data Model structure helps to define the relational tables, primary and foreign keys and stored procedures.

There are three types of conceptual, logical, and physical.

The main aim of conceptual model is to establish the entities, their attributes, and their relationships.

Logical data model defines the structure of the data elements and set the relationships between them.

A Physical Data Model describes the database specific implementation of the data model.

The main goal of a designing data model is to make certain that data objects offered by the functional team are represented accurately.

The biggest drawback is that even smaller change made in structure require modification in the entire application.

Reading this Data Modeling tutorial, you will learn from the basic concepts such as What is Data Model? Introduction to different types of Data Model, advantages, disadvantages, and data model example.

# data[].attributes.attachment_link.title
What is Data Modelling? Types (Conceptual, Logical, Physical)

# data[].attributes.attachment_link.description
Data modeling (data modelling) is the process of creating a data model for the data to be stored in a database.

# data[].attributes.attachment_link.title
MySQL Tutorial - MySQL By Examples for Beginners

# data[].attributes.attachment_link.description
DROP DATABASE databaseName DROP DATABASE IF EXISTS databaseName CREATE DATABASE databaseName CREATE DATABASE IF NOT EXISTS databaseName SHOW DATABASES USE databaseName SELECT DATABASE() SHOW CREATE DATABASE databaseName DROP TABLE [IF EXISTS] tableName, ... CREATE TABLE [IF NOT EXISTS] tableName ( columnName columnType columnAttribute, ...

# data[].attributes.attachment_link.readable_text
But it doesn't have to be.

In this article, I'll explain what normalisation in a DBMS is and how to do it, in simple terms.

By the end of the article, you'll know all about it and how to do it.

So let's get started.

Database normalisation, or just normalisation as it's commonly called, is a process used for data modelling or database creation, where you organise your data and tables so it can be added and updated efficiently.

It's something a person does manually, as opposed to a system or a tool doing it. It's commonly done by database developers and database administrators.

It can be done on any relational database , where data is stored in tables that are linked to each other. This means that normalization in a DBMS (Database Management System) can be done in Oracle, Microsoft SQL Server, MySQL, PostgreSQL and any other type of database.

To perform the normalization process, you start with a rough idea of the data you want to store, and apply certain rules to it in order to get it to a more efficient form.

I'll show you how to normalise a database later in this article.

So why would anyone want to normalize their database?

Why do we want to go through this manual process of rearranging the data?

There are a few reasons we would want to go through this process:

Make the database more efficient

Prevent the same data from being stored in more than one place (called an "insert anomaly")

Prevent updates being made to some data but not others (called an "update anomaly")

Prevent data not being deleted when it is supposed to be, or from data being lost when it is not supposed to be (called a "delete anomaly")

Ensure the data is accurate

Reduce the storage space that a database takes up

Ensure the queries on a database run as fast as possible

Normalization in a DBMS is done to achieve these points. Without normalization on a database, the data can be slow, incorrect, and messy.

Data Anomalies

Some of these points above relate to "anomalies".

An anomaly is where there is an issue in the data that is not meant to be there. This can happen if a database is not normalised.

Let's take a look at the different kinds of data anomalies that can occur and that can be prevented with a normalised database.

Our Example

We'll be using a student database as an example in this article, which records student, class, and teacher information.

Let's say our student database looks like this:

This table keeps track of a few pieces of information:

This is not a normalised table, and there are a few issues with this.

Insert Anomaly

An insert anomaly happens when we try to insert a record into this table without knowing all the data we need to know.

For example, if we wanted to add a new student but did not know their course name.

The new record would look like this:

We would be adding incomplete data to our table, which can cause issues when trying to analyse this data.

Update Anomaly

An update anomaly happens when we want to update data, and we update some of the data but not other data.

For example, let's say the class Biology 1 was changed to "Intro to Biology". We would have to query all of the columns that could have this Class field and rename each one that was found.

There's a risk that we miss out on a value, which would cause issues.

Ideally, we would only update the value once, in one location.

Delete Anomaly

A delete anomaly occurs when we want to delete data from the table, but we end up deleting more than what we intended.

For example, let's say Susan Johnson quits and her record needs to be deleted from the system. We could delete her row:

But, if we delete this row, we lose the record of the Biology 2 class, because it's not stored anywhere else. The same can be said for the Medicine course.

We should be able to delete one type of data or one record without having impacts on other records we don't want to delete.

The process of normalization involves applying rules to a set of data. Each of these rules transforms the data to a certain structure, called a normal form.

There are three main normal forms that you should consider (Actually, there are six normal forms in total, but the first three are the most common).

Whenever the first rule is applied, the data is in " first normal form". Then, the second rule is applied and the data is in " second normal form". The third rule is then applied and the data is in " third normal form ".

Fourth and fifth normal forms are then achieved from their specific rules.

Alright, so there are three main normal forms that we're going to look at. I've written a post on designing a database , but let's see what is involved in getting to each of the normal forms in more detail.

First normal form is the way that your data is represented after it has the first rule of normalization applied to it. Normalization in DBMS starts with the first rule being applied - you need to apply the first rule before applying any other rules.

Let's start with a sample database. In this case, we're going to use a student and teacher database at a school. We mentioned this earlier in the article when we spoke about anomalies, but here it is again.

Our Example Database

We have a set of data we want to capture in our database, and this is how it currently looks. It's a single table called "student" with a lot of columns.

Everything is in one table.

How can we normalise this?

We start with getting the data to First Normal Form.

To apply first normal form to a database, we look at each table, one by one, and ask ourselves the following questions of it:

1. Does the combination of all columns make a unique row every single time?

2. What field can be used to uniquely identify the row?

Let's look at the first question.
Does the combination of all columns make a unique row every single time?
No. There could be the same combination of data, and it would represent a different row. There could be the same values for this row and it would be a separate row (even though it is rare).

The second question says:
What field can be used to uniquely identify the row?
Is this the student name? No, as there could be two students with the same name.

Address? No, this isn't unique either.

Any other field?

We don't have a field that can uniquely identify the row.

If there is no unique field, we need to create a new field. This is called a primary key, and is a database term for a field that is unique to a single row. (Related: The Complete Guide to Database Keys)

When we create a new primary key, we can call it whatever we like, but it should be obvious and consistently named between tables. I prefer using the ID suffix, so I would call it student ID.

This is our new table:

This can also be represented in an Entity Relationship Diagram (ERD):

The way I have written this is a common way of representing tables in text format. The table name is written, and all of the columns are shown in brackets, with the primary key underlined.

This data is now in first normal form.

This example is still in one table, but it's been made a little better by adding a unique value to it.

Want to find a tool that creates these kinds of diagrams? There are many tools for creating these kinds of diagrams. I've listed 76 of them in this guide to Data Modeling Tools, along with reviews, price, and other features. So if you're looking for one to use, take a look at that list.

The rule of second normal form on a database can be described as:

1. Fulfil the requirements of first normal form

2. Each non-key attribute must be functionally dependent on the primary key

What does this even mean?

It means that the first normal form rules have been applied. It also means that each field that is not the primary key is determined by that primary key, so it is specific to that record. This is what "functional dependency" means.

Let's take a look at our table.

Are all of these columns dependent on and specific to the primary key?

The primary key is student ID, which represents the student. Let's look at each column:

student name: Yes, this is dependent on the primary key. A different student ID means a different student name.

fees paid: Yes, this is dependent on the primary key. Each fees paid value is for a single student.

date of birth: Yes, it's specific to that student.

address: Yes, it's specific to that student.

subject 1: No, this column is not dependent on the student. More than one student can be enrolled in one subject.

subject 2: As above, more than one subject is allowed.

subject 3: No, same rule as subject 2.

subject 4: No, same rule as subject 2

teacher name: No, the teacher name is not dependent on the student.

teacher address: No, the teacher address is not dependent on the student.

course name: No, the course name is not dependent on the student.

We have a mix of Yes and No here. Some fields are dependent on the student ID, and others are not.

How can we resolve those we marked as No?

Let's take a look.

First, the subject 1 column. It is not dependent on the student, as more than one student can have a subject, and the subject isn't a part of the definition of a student.

So, we can move it to a new table:

I've called it subject name because that's what the value represents. When we are writing queries on this table or looking at diagrams, it's clearer what subject name is instead of using subject.

Now, is this field unique? Not necessarily. Two subjects could have the same name and this would cause problems in our data.

So, what do we do? We add a primary key column, just like we did for student. I'll call this subject ID, to be consistent with the student ID.

This means we have a student table and a subject table. We can do this for all four of our subject columns in the student table, removing them from the student table so it looks like this:

But they are in separate tables. How do we link them together?

We'll cover that shortly. For now, let's keep going with our student table.

The next column we marked as No was the Teacher Name column. The teacher is separate to the student so should be captured separately. This means we should move it to its own table.

We should also move the teacher address to this table, as it's a property of the teacher. I'll also rename teacher address to be just address.

Teacher (teacher name, address)

Just like with the subject table, the teacher name and address is not unique. Sure, in most cases it would be, but to avoid duplication we should add a primary key. Let's call it teacher ID,

The last column we have to look at was the Course Name column. This indicates the course that the student is currently enrolled in.

While the course is related to the student (a student is enrolled in a course), the name of the course itself is not dependent on the student.

So, we should move it to a separate table. This is so any changes to courses can be made independently of students.

The course table would look like this:

Let's also add a primary key called course ID.

We now have our tables created from columns that were in the student table. Our database so far looks like this:

Using the data from the original table, our data could look like this:
Student Subject Teacher Course
How do we link these tables together? We still need to know which subjects a student is taking, which course they are in, and who their teachers are.

Foreign Keys in Tables

We have four separate tables, capturing different pieces of information. We need to capture that students are taking certain courses, have teachers, and subjects. But the data is in different tables.

How can we keep track of this?

We use a concept called a foreign key.

A foreign key is a column in one table that refers to the primary key in another table. Related: The Complete Guide to Database Keys.

It's used to link one record to another based on its unique identifier, without having to store the additional information about the linked record.

Here are our two tables so far:

To link the two tables using a foreign key, we need to put the primary key (the underlined column) from one table into the other table.

Let's start with a simple one: students taking courses. For our example scenario, a student can only be enrolled in one course at a time, and a course can have many students.

We need to either:

Add the course ID from the course table into the student table

Add the student ID from the student table into the course table

But which one is it?

In this situation, I ask myself a question to work out which way it goes:
Does a table1 have many table2s, or does a table2 have many table1s?
If it's the first, then table1 ID goes into table 2, and if it's the second then table2 ID goes into table1.

So, if we substitute table1 and table2 for course and student:

Does a course have many students, or does a student have many courses?

Based on our rules, the first statement is true: a course has many students.

This means that the course ID goes into the student table.

I've italicised it to indicate it is a foreign key - a value that links to a primary key in another table.

When we actually populate our tables, instead of having the course name in the student table, the course ID goes in the student table. The course name can then be linked using this ID.

This also means that the course name is stored in one place only, and can be added/removed/updated without impacting other tables.

I've created a YouTube video to explain how to identify and diagram one-to-many relationships like this:

We've linked the student to the course. Now let's look at the teacher.

How are teachers related? Depending on the scenario, they could be related in one of a few ways:

In our scenario, a teacher is related to a course. We need to relate these two tables using a foreign key.

Does a teacher have many courses, or does a course have many teachers?

In our scenario, the first statement is true. So the teacher ID goes into the course table:

The table data would look like this:
Course Teacher
This allows us to change the teacher's information without impacting the courses or students.

Student and Subject

So we've linked the course, teacher, and student tables together so far.

What about the subject table?

Does a subject have many students, or does a student have many subjects?

The answer is both.

How is that possible?

A student can be enrolled in many subjects at a time, and a subject can have many students in it.

How can we represent that? We could try to put one table's ID in the other table:

But if we do this, we're storing many pieces of information in one column, possibly separated by commas.

This makes it hard to maintain and is very prone to errors.

If we have this kind of relationship, one that goes both ways, it's called a many to many relationship. It means that many of one record is related to many of the other record.

Many to Many Relationships

A many to many relationship is common in databases. Some examples where it can happen are:

If we can't represent this relationship by putting a foreign key in each table, how can we represent it?

We use a joining table.

This is a table that is created purely for storing the relationships between the two tables.

It works like this. Here are our two tables:

And here is our joining table:

It has two columns. Student ID is a foreign key to the student table, and subject ID is a foreign key to the subject table.

Each record in the row would look like this:

Each row represents a relationship between a student and a subject.

Student 1 is linked to subject 1.

Student 1 is linked to subject 2.

Student 2 is linked to subject 2.

This has several advantages:

It allows us to store many subjects for each student, and many students for each subject.

It separates the data that describes the records (subject name, student name, address, etc.) from the relationship of the records (linking ID to ID).

It allows us to add and remove relationships easily.

It allows us to add more information about the relationship. We could add an enrolment date, for example, to this table, to capture when a student enrolled in a subject.

You might be wondering, how do we see the data if it's in multiple tables? How can we see the student name and the name of the subjects they are enrolled in?

Well, that's where the magic of SQL comes in. We use a SELECT query with JOINs to show the data we need. But that's outside the scope of this article - you can read the articles on my Oracle Database page to find out more about writing SQL.

One final thing I have seen added to these joining tables is a primary key of its own. An ID field thatrepresents the record. This is an optional step - a primary key on a single new column works in a similar way to defining the primary key on the two ID columns. I'll leave it out in this example.

So, our final table structure looks like this:

I've called the table Subject Enrolment. I could have left it as the concatenation of both of the related tables (student subject), but I feel it's better to rename the table to what it actually captures - the fact a student has enrolled in a subject. This is something I recommend in my SQL Best Practices post.

I've also underlined both columns in this table, as they represent the primary key. They can also represent a foreign key, which is why they are also italicised.

An ERD of these tables looks like this:

This database structure is in second normal form. We almost have a normalised database.

Now, let's take a look at third normal form.

Third normal form is the final stage of the most common normalization process. The rule for this is:

Fulfils the requirements of second normal form

Has no transitive functional dependency

What does this even mean? What is a transitive functional dependency?

It means that every attribute that is not the primary key must depend on the primary key and the primary key only.

For example:

This means that column A determines column B which determines column C. This is a transitive functional dependency, and it should be removed. Column C should be in a separate table.

We need to check if this is the case for any of our tables.
Do any of the non-primary-key fields depend on something other than the primary key?
No, none of them do. However, if we look at the address, we can see something interesting:

We can see that there is a relationship between the ZIP code and the city or suburb. This is common with addresses, and you may have noticed this if you have filled out any forms for addresses online recently.

How are they related? The ZIP code, or postal code, determines the city, state, and suburb.

In this case, 56128 is South Boston, and 56125 is North Boston. (I just made this up so this is probably inaccurate data).

This falls into the pattern we mentioned earlier: A determines B which determines C.

Student determines the address ZIP code which determines the suburb.

So, how can we improve this?

We can move the ZIP code to another table, along with everything it identifies, and link to it from the student table.

Our table could look like this:

Student ( , course ID, student name, fees paid, date of birth, street address, address code ID)

I've created a new table called Address Code, and linked it to the student table. I created a new column for the address code ID, because the ZIP code may refer to more than one suburb. This way we can capture that fact, and it's in a separate table to make sure it's only stored in one place.

Let's take a look at the other tables:

Both of these tables have no columns that aren't dependent on the primary key.

The teacher table also has the same issue as the student table when we look at the address. We can, and should use the same approach for storing address.

So our table would look like this:

It uses the same Address Code table as mentioned above. We aren't creating a new address code table.

Finally, the course table:

This table is OK. The course name is dependent on the course ID.

So, what does our database look like now?

So, that's how third normal form could look if we had this example.

An ERD of third normal form would look like this:

Stopping at Third Normal Form

For most database normalisation exercises, stopping after achieving Third Normal Form is enough.

It satisfies a good relationship rules and will greatly improve your data structure from having no normalisation at all.

There are a couple of steps after third normal form that are optional. I'll explain them here so you can learn what they are.

Fourth normal form is the next step after third normal form.

What does it mean?

It needs to satisfy two conditions:

Meet the criteria of third normal form.

There are no non-trivial multivalued dependencies other than a candidate key.

So, what does this mean?

A multivalued dependency is probably better explained with an example, which I'll show you shortly. It means that there are other attributes in the table that are not dependent on the primary key, and can be moved to another table.

Our database looks like this:

This meets the third normal form rules.

However, let's take a look at the address fields: street address and address code.

Both the student and teacher table have these

What if a student moves addresses? Do we update the address in this field? If we do, then we lose the old address.

If an address is updated, is it because they moved? Or is it because there was an error in the old address?

What if two students have the same street address. Are they actually at the same address? What if we update one and not the other?

What if a teacher and a student are at the same address?

What if we want to capture a student or a teacher having multiple addresses (for example, postal and residential)?

There are a lot of "what if" questions here. There is a way we can resolve them and improve the quality of the data.

This is a multivalued dependency.

We can solve this by moving the address to a separate table.

The address can then be linked to the teacher and student tables.

Let's start with the address table.

In this table, we have a primary key of address ID, and we have stored the street address here. The address code table stays the same.

We need to link this to the student and teacher tables. How do we do this?

Do we also want to capture the fact that a student or teacher can have multiple addresses? It may be a good idea to future proof the design. It's something you would want to confirm in your organisation.

For this example, we will design it so there can be multiple addresses for a single student.

Our tables could look like this:

An ERD would look like this:

A few changes have been made here:

This design will let you do a few things:

So, that's how you can achieve fourth normal form on this database.

There are a few enhancements you can make to this design, but it depends on your business rules:

Combine the student and teacher tables into a person table, as they are both effectively people, but teachers teach a class and students take a class. This table could then link to subjects and define that relationship as "teaches" or "is enrolled in", to cater for this.

Relate a course to a subject, so you can see which subjects are in a course

Split the address into separate fields for unit number, street number, address line 1, address line 2, and so on.

Split the student name and teacher name into first and last names to help with displaying data and sorting.

These changes could improve the design, but I haven't detailed them in any of these steps as they aren't required for fourth normal form.

I hope this explanation has helped you understand what the normal forms are and what normalization in DBMS is. Do you have any questions on this process? Share them in the section below.

Lastly, if you enjoy the information and career advice I've been providing, sign up to my newsletter below to stay up-to-date on my articles. You'll also receive a fantastic bonus. Thanks!

# data[].attributes.attachment_link.title
Database Normalization: A Step-By-Step-Guide With Examples

# data[].attributes.attachment_link.description
Database normalisation is a concept that can be hard to understand. But it doesn't have to be. In this article, I'll explain what normalisation in a DBMS is and how to do it, in simple terms.

# data[].attributes.attachment_link.title
Database Normalization - in Easy to Understand English

# data[].attributes.attachment_link.description
Database normalization is used to organize a database. Get a simple explanation to first, second, and third normal forms.

# data[].attributes.attachment_link.title
Easy explanation of Normalization   Relational Database Design for Beginners - 1NF, 2NF, 3NF