Scribd
Upload
15 views4 pages

First Normalization in DBMS Explained

The document describes an experiment on first normalization in database management systems. The objectives are to understand first normalization, and how to create and perform operations on tables using MySQL Workbench. The theory section defines normalization, first normal form, and SQL. Several tasks are performed to demonstrate first normalization including splitting a table, handling multi-valued attributes, and merging normalized tables. The conclusion discusses how first normalization was achieved to minimize redundancy and ensure data integrity and efficiency.

Uploaded by

Mohammad Jakir Hossain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views4 pages

First Normalization in DBMS Explained

The document describes an experiment on first normalization in database management systems. The objectives are to understand first normalization, and how to create and perform operations on tables using MySQL Workbench. The theory section defines normalization, first normal form, and SQL. Several tasks are performed to demonstrate first normalization including splitting a table, handling multi-valued attributes, and merging normalized tables. The conclusion discusses how first normalization was achieved to minimize redundancy and ensure data integrity and efficiency.

Uploaded by

Mohammad Jakir Hossain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Experiment Name: Study of First Normalization in DBMS.

3.1 Objectives:
• To know about the First Normalization in Database Management System.
• To learn about how to create database and how to perform some simple
mathematical calculations by using ‘MySQL Workbench.’
• To learn about how to create table and how to perform some simple
operations on the table by using ‘MySQL Workbench.’
3.2 Theory:
Normalization: Normalization is the process of minimizing redundancy from a relation
or set of relations. Redundancy in relation may cause insertion, deletion and update
anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used
to eliminate or reduce redundancy in database tables.
First Normal Form (1NF): If a relation contains a composite or multi-valued attribute,
it violates the first normal form, or the relation is in first normal form if it does not
contain any composite or multi-valued attribute. A relation is in first normal form if
every attribute in that relation is singled valued attribute.
A table is in 1 NF if: There are only Single Valued Attributes. Attribute Domain does
not change. There is a unique name for every Attribute/Column. The order in which
data is stored does not matter.
SQL: SQL (structured query language) is a language for specifying the organization of
databases (collections of records). Databases organized with SQL are called relational,
because SQL provides the ability to query a database for information that falls in a given
relation.
3.3 SQL Code and Outputs:
Task 01: Create a table and split this table
Code:
create table orginial_table(
id INT PRIMARY KEY,
name varchar(50),
age INT);
INSERT INTO orginial_table(id,name,age)values

(10,'Fuad',30),
(20,'Promy',40),
(30,'Jakir',20),
(40,'Ritu',35),
(50,'Antu',18);

select * from orginial_table;


create table t1 as select*from orginial_table where age <=30;
select * from t1;
create table t2 as select*from orginial_table where age > 30;
select * from t2;

Output:

10
Figure 1: Original table Figure 2: Table t1 Figure 3: Table t2

Lab task 2: First Normalization


Code:
Table Creation part:
create table updates_orginial_table(id INT PRIMARY KEY,name
varchar(50),age INT,mobile varchar(50));
INSERT INTO updates_orginial_table(id,name,age,mobile)values
(10,'Fuad',30,'01521,01721'),
(20,'Promy',40,'01912'),
(30,'Jakir',20,'0167'),
(40,'Ritu',35,'01322'),
(50,'Antu',18,'01818,01321'),
(60,'mamun','25','01818,01835');
select * from updates_orginial_table;
set @var=0;
call ETE_20.new_procedure(@var);
select @var;
select ETE_20.new_function(@var);
Output:

New Procedure Part:

New function part:

11
Task 03: Splitting the main table for first phone number
Code:
create table n1f as SELECT id,name, age, SUBSTRING_INDEX(mobile, ',', 1)
AS mobile1 from updates_orginial_table;
select * from n1f order by id;

Output:

Task 04: Splitting the main table for second phone number
Code:
create table n1f2 as SELECT id,name, age, SUBSTRING_INDEX(mobile, ',', -
1)
AS mobile2 from updates_orginial_table where mobile LIKE '%,%';
select * from n1f2 order by id;

Output:

Task 05: Merge the splitted tables for every single phone number
Code:
SELECT * FROM n1f
UNION ALL
SELECT * FROM n1f2 order by id;
Output:

12
3.4 Discussion & Conclusion:
In this lab experiment, we delved into the concept of normalization in database
management systems (DBMS). Normalization is a crucial process that aims to minimize
redundancy and dependency in a database by organizing data into separate tables. It
involves a series of rules, known as normal forms, which guide the structuring of the
database to ensure data integrity and efficiency. Our focus was primarily on achieving the
first normal form (1NF), which necessitates that each column in a table contains atomic
values, and there are no repeating groups or arrays. To accomplish this, we systematically
divided the initial dataset into two distinct tables, adhering to the principles of 1NF. This
entailed identifying and isolating distinct pieces of information to maintain a clean and
well-organized database structure. By doing so, we aimed to eliminate data redundancy
and minimize the chances of update anomalies, ultimately contributing to a more robust
and efficient database system

13

Common questions

Powered by AI

When splitting a database table to achieve the first normal form, challenges such as data misalignment, increase in join operations, and potential loss of relational context may arise. These can lead to complexity in retrieving data and may affect performance. To mitigate these challenges, it is crucial to carefully design primary and foreign keys that maintain relationships between the split tables. Additionally, employing indexes can optimize performance, and regularly verifying relational consistency through SQL queries can ensure the integrity of the newly organized tables .

The primary goal of normalization in a database management system is to minimize redundancy and dependency by organizing data into separate tables, which ensures data integrity and efficiency. The first normal form (1NF) contributes to achieving this goal by requiring that each column contains atomic values, meaning there are no repeating groups or arrays. This helps eliminate data redundancy and minimizes the chances of update anomalies, thereby contributing to a more robust and efficient database system .

The document implies that the order of data storage does not affect compliance with the first normal form. This is because 1NF deals primarily with the atomicity and integrity of the data rather than the sequence it is stored in. Therefore, as long as each attribute contains a single, atomic value, and there are no multi-valued or composite attributes, the table satisfies 1NF regardless of the order of data .

Splitting tables according to the first normal form enhances database integrity and efficiency by ensuring that each attribute contains atomic values and removing any composite or multi-valued attributes. By doing so, the database reduces redundancy, minimizing the risk of insertion, deletion, and update anomalies. This structuring leads to more efficient data retrieval and manipulation because the data is organized into logical, non-redundant tables .

Ensuring atomic values in database attributes is critical because it guarantees that every piece of data is indivisible within its column, preventing redundancy and maintaining integrity. Atomic values ensure each attribute is singularly defined without ambiguity or multiple parts, which simplifies data validation, querying, and management while avoiding anomalies. This streamlined and precise structure is essential in sustaining efficient, reliable, and consistent databases .

Employing stored procedures and functions in conjunction with SQL tables enhances database operations related to normalization by enabling automated, consistent, and efficient data processing tasks. Stored procedures can encapsulate complex SQL queries and logic needed to enforce normalization rules such as splitting multi-valued fields into atomic ones. Functions can automate computation or transformations obtained through these normalized tables, ensuring data is always processed correctly with minimal manual intervention. This minimizes errors, speeds up operations, and maintains the integrity and efficiency of data transactions .

The SQL 'UNION ALL' operation plays a crucial role in the normalization process by allowing the combination of sets of rows that have been separated into different tables due to multi-valued attributes. After splitting such attributes into separate tables to comply with the first normal form, UNION ALL can combine these tables in query results while preserving duplicates necessary for retaining complete data records. This operation aids in reconstructing original contexts from normalized parts without aggregating or eliminating essential repeated information .

Avoiding composite and multi-valued attributes in a database table is important because they introduce redundancy and complexity, leading to potential anomalies during data operations such as insertion, deletion, or updates. By ensuring attributes are single valued, as required by the first normal form, the database maintains a clear, organized structure that simplifies data handling and enhances consistency, reliability, and efficiency in the database .

To transform a database table into the first normal form using SQL, one must: 1) Ensure that all attributes have unique, atomic values (no composite or multi-valued attributes); 2) Split multi-valued attributes into separate tables or columns, using SQL commands such as SUBSTRING_INDEX to extract and separate different values; 3) Use SQL statements like SELECT and INSERT to populate the new tables with organized data; and 4) Combine relevant tables if needed using UNION or other join operations to maintain relational integrity .

Achieving the first normal form significantly minimizes update anomalies by ensuring that each attribute contains single atomic values. By avoiding composite or multi-valued attributes, databases are less likely to encounter scenarios where updates to a dataset require simultaneous changes to multiple fields or tables. This structure reduces the risks of databases falling out of sync and maintains data integrity, which diminishes update anomalies arising from inconsistency in multi-part fields .

You might also like