ER(Entity Relational) Diagram and Normalization in DBMS - Technology369kk

ER:- An entity-relationship model (ER model) Diagram describes the structure of a database with the help of a diagram, known as an Entity Relationship Diagram (ER Diagram). 

An ER model is a design or blueprint of a database that can later be implemented as a database.

Normalization

Normalization in a DBMS is a process used to organize data in a database efficiently and effectively. It eliminates data and redundancy (duplicate data) and ensures data dependency(logic storage). The goal is to divide a database into smaller tables and define relationships between them, making it more flexible and easy to maintain. So Important Points: 

• Decompose larger, complex tables into simple and smaller ones, 
• Moves from lower normal forms to higher normal forms.

Normalization and Normal Forms: 

Need for Normalization

•  To produce a good database design
•  To ensure all operations are efficiently performed
• Avoid any expensive DBMS operations
• Avoid unnecessary replication of information

DATABASE of Students but wrong learn next I will explain why wrong.

Students_Details	Course_details	Pre-requisite	Results_details
101 Shailu 05/01/2002	J1 Advance Java                 301	Basic Java	03/08/2022    85        A
102 Sanju 14/02/2002	P4 Advance Python           304	Basic Python	03/08/2022     78      B
103 Annu 05/01/2002	S Advance SQL                   308	Basic SQL	03/08/2022     98      A
104 Priya 17/02/2002	C5 Advance C++                 302	Basic C++	03/08/2022     58      C

    

       Note: 

•     Before learning  the types of normal forms in normalization 1NF, and 2NF………we will be most important this concept in detail:

       Functional Dependency:

·        Consider the relation

•             Results ( Students#, Course#, CourseName#, Marks#, Grade#)

o   Students# and Course# together exactly one value of marks. Students#, Course#, Marks

o   Students# and Course# determines Marks or Marks is functionally dependent on student# and Course#

• ·        Other Functional dependencies in the relation:  

o   Course# - CourseName

o   Marks#  - Grade

·        Let’s see an example of a demo

·        In a given relation R, P and Q are attributes. Attributes Q is functionally dependent on attributes P if each of P determines exactly one value of Q. 

Type of Function Dependency:-  

1.      1. Partial Functional Dependency:

1. Attributes Q is partially dependent on attributes on P, if and only if is dependent on the subset of attributes P.

2. REPORT (Student#, Course#, StudentName#, CourseName#, Marks, Grade)

       

2. Transitive Dependency:

X, Y, and Z are three attribute

Types of Normalization

• We are already discussing about normalization but here now discuss on types only major three types of normalizations: - 
• 
  

1.      First Normal Form – (1NF)

·        A relational schema is in 1NF, if and only if:

o   All attributes in the relation are atomic (indivisible value)

o   And there are no repeating elements or groups of elements

Students_Details	Course_details	Pre-requisite	Results_details
101 Shailu 05/01/2002	J1 Advance Java                301	Basic Java   18	03/08/2022    85        A
102 Sanju 14/02/2002	P4 Advance Python           304	Basic Python 25	03/08/2022     78      B
103 Annu 05/01/2002	S Advance SQL                   308	Basic SQL     33	03/08/2022     98      A
104 Priya 17/02/2002	C5 Advance C++                 302	Basic C++     17	03/08/2022     58      C

Note:- Remember, I told you this is the wrong table of student's information so now explain how to convert it right.

Student Information Table in 1NF

Student #	Student_ Name	DOB	Course#	Course Name	Course Id	Pre-Requisite	Duration in Days	Date of Exam	Marks	Grade
101	Shailu	05/01/ 2002	J1	Advance Java	301	Basic Java	18	03/08 /2022	85	A
102	Annu	07/09/ 2002	P4	Advance Python	302	Basic Python	25	03/08/ 2022	78	B
103	Priya	17/02/ 20004	S2	Advance SQL	303	Basic SQL	33	03/08/ 2022	98	A

    2. Second Normal Form – (2NF):

·        A relation is said to be in 2NF, if and only if:

·        It is in 1^st Normal form.

·        No partial dependency exists between non-key attributes and key attributes.

·        Here Now 1NF Divided by 2NF so let’s see now:

·        Students#, Course# à Marks

·        Students#, Course# à Grade

·        Marksà Grade

Student Information Table in 2NF
Student Table

Student#	Student_Name	DOB
101	Shailu	05/01/2002
102	Annu	07/09/2002
103	Priya	17/02/2002

Result Table

Student#	Course#	Marks	Grade
101	J1	85	A
102	P4	78	A
103	S2	98	B

Course Table

Course#	CourseName	Prerequisite	DurationDay	Date of Exam
J1	Advance Java	Basic Java	18	03/08 /2022
P4	Advance Python	Basic Python	25	03/08/ 2022
S2	Advance SQL	Basic SQL	33	03/08/ 2022

1.      Third Normalization –(3NF):

    Result Table

Student#	Course#	Marks	Grade
101	J1	85	A
102	P4	78	A
103	S2	98	B

·        Student#, Course# à Marks

·        Student#, Course#  à Grade

·        Marks à Grade

·        Student#, Course# à Marks à Grade: TD -----à Remove

Result Table

Student#	Course#	Marks
101	J1	85
102	P4	78
103	S2	98

Marks Grade Table

Marks	Course#
85	A
73	B
98	A

I hope this post is understandable for you, if you have any doubts pls comments.