1NF 2NF 3NF DBMS

Summary

This script emphasizes the importance of normalization in database design to reduce data redundancy and enhance security. It outlines the process of achieving different normal forms: First Normal Form by eliminating repeating values or groups, Second Normal Form by resolving partial dependencies in composite keys, and Third Normal Form by eliminating transitive dependencies. Each step is crucial for creating a flexible, secure, and efficient database structure.

Takeaways

• 📚 Normalization is crucial in database design to reduce data redundancy and improve data organization.
• 🔐 Database security is enhanced through normalization as it involves creating more tables which can restrict access to sensitive data.
• 📈 The process of normalization should follow a sequence: First Normal Form (1NF), Second Normal Form (2NF), and then Third Normal Form (3NF).
• 🚫 A table in 1NF must avoid repeating values within a group and repeating groups within the table.
• 🔑 To achieve 1NF, if a table has repeating values or columns, these should be separated into distinct tables and linked appropriately.
• 🔄 Composite keys, which consist of more than one column, can lead to partial dependencies and are a consideration for 2NF.
• 📝 For a table to be in 2NF, it must be in 1NF and have no partial dependencies where non-key columns depend only on part of the composite key.
• 🔄 In 3NF, tables must be in both 1NF and 2NF, and there should be no transitive dependencies where non-key columns depend on other non-key columns.
• 🔍 Identifying and removing transitive dependencies, such as guessing one non-key column from another, is essential for achieving 3NF.
• 💡 Examples provided in the script, such as employee phone numbers, student results, and match capacities, illustrate how to apply normalization rules in real-world scenarios.

Q & A

What is the primary purpose of normalization in database design?

The primary purpose of normalization in database design is to reduce data redundancy and improve data integrity by organizing data into well-structured tables.

Why is it important to follow the sequence of normalization forms when designing a database?

Following the sequence of normalization forms is important because each form builds upon the previous one. You cannot bypass earlier forms and directly go to a higher normal form, as each step ensures the database adheres to specific structural rules that prevent data anomalies.

What are the two criteria that a table must meet to be in the first normal form?

A table must meet two criteria to be in the first normal form: it should not have repeating values in a group, and it should not have repeating groups in the table.

How does the example of an employee table with multiple phone numbers violate the first normal form?

The example of an employee table with multiple phone numbers violates the first normal form by having repeating values in a column, as the phone numbers are listed in a single column, which goes against the rule of having no repeating groups.

What is a composite key and why is it necessary in a database?

A composite key is a primary key composed of more than one column, used when a single column is not sufficient to uniquely identify a record. It is necessary to ensure that each record in the database can be uniquely identified.

Can you explain partial dependency and how it relates to the second normal form?

Partial dependency occurs when a non-key column depends on only a part of a composite key rather than the entire key. To achieve the second normal form, the table must be free from partial dependencies, ensuring that non-key columns depend on the entire primary key.

What is a transitive dependency and how does it violate the third normal form?

A transitive dependency occurs when a non-key column is dependent on another non-key column or when a non-key column can be determined from other non-key columns. This violates the third normal form, which requires that non-key columns should only depend on the primary key.

How does normalization affect database security?

Normalization can enhance database security by creating more tables that can be used to restrict access to sensitive information. By segmenting data into multiple tables, it becomes easier to implement security measures that control access to specific data sets.

What is the solution to the problem of repeating columns in a table?

The solution to the problem of repeating columns is to remove the columns that are causing the issue and create separate tables for those columns, then link them back to the original table using a foreign key.

Why is it beneficial for a database to handle more tables rather than having tables with repeating values or columns?

Handling more tables is beneficial because it adheres to the principles of normalization, which reduces redundancy and improves data integrity. Databases can efficiently manage multiple tables, but they struggle with performance and consistency when dealing with repeating values or columns.

Outlines

00:00

📚 Database Normalization Basics

This paragraph introduces the fundamental concept of normalization in database design. Normalization is a set of rules aimed at reducing data redundancy and improving data integrity. It involves creating more tables than in non-normalized forms, which can be managed more efficiently by a database system. The benefits of normalization include enhanced flexibility in design and improved database security, as it allows for the creation of tables that can restrict access to certain users. The paragraph explains the process of normalization, starting with the first normal form (1NF), which requires tables to have no repeating values or groups. An example is provided where an employee table is modified to avoid repeating phone numbers by creating a separate table for phone numbers linked to the employee table. The paragraph also touches on the sequential nature of normalization forms, where each subsequent form builds upon the previous one, and that one cannot skip forms in the normalization process.

05:01

🔐 Advanced Normalization Forms: 2NF and 3NF

The second paragraph delves into the specifics of the second (2NF) and third (3NF) normal forms in database normalization. For a table to be in 2NF, it must first be in 1NF and must not have partial dependencies, which occur when non-key columns depend on only a part of a composite key. An example is given where a table with student IDs and courses has a partial dependency issue, as the teacher column depends only on the course, not the student ID. To resolve this, the table is split to eliminate partial dependencies. The third normal form (3NF) requires a table to be in both 1NF and 2NF and to have no transitive dependencies, where a non-key column is dependent on another non-key column. Examples are provided to illustrate transitive dependencies, such as a table where the maximum marks column can be inferred from the exam type, or a table where the capacity of a stadium can be deduced from the ground name. The paragraph concludes by emphasizing that transitive dependencies, including those resulting from calculations within the table, must be eliminated to achieve 3NF.

Keywords

💡Normalization

Normalization is a process in database design aimed at organizing data to minimize redundancy and dependency. In the context of the video, normalization is crucial for creating a flexible and secure database structure. The script explains that normalization involves creating more tables to handle data more efficiently, which is beneficial for database performance and security. The video emphasizes that normalization should be approached step by step, starting from the first normal form and progressing to higher forms.

💡First Normal Form (1NF)

First Normal Form is the first step in the normalization process where a table is structured to have no repeating values or groups. The video uses the example of an employee table to illustrate how separating out phone numbers into a new table can resolve repeating values and achieve 1NF. This step is fundamental because it ensures that each record in a table is unique and can be easily accessed and modified.

💡Second Normal Form (2NF)

Second Normal Form builds upon 1NF by eliminating partial dependencies, which occur when a non-key column depends on only a part of a composite key. The video explains this with a student results table, where the 'teacher' column only depends on the 'course' part of the composite key, not the entire key. To achieve 2NF, the table is split to ensure that non-key columns are fully dependent on the entire primary key.

💡Composite Key

A composite key is a primary key consisting of two or more columns, used when a single column is not sufficient to uniquely identify a record. The video script uses the example of a student results table where both 'student id' and 'course' are needed to uniquely identify a student's record. The concept is integral to understanding 2NF, as it helps in identifying and resolving partial dependencies.

💡Partial Dependency

Partial dependency refers to a situation where a non-key column is dependent on only a part of a composite key, rather than the entire key. The video script explains this concept using a table where the 'teacher' column depends only on the 'course' and not on the 'student id', which is a part of the composite key. This dependency violates the rules of 2NF, and resolving it by restructuring the table is essential for normalization.

💡Third Normal Form (3NF)

Third Normal Form is the next level of normalization that eliminates transitive dependencies, where a non-key column is determined by another non-key column. The video provides examples such as a table where 'maximum marks' can be inferred from the 'exam type', violating 3NF. To achieve 3NF, the table is split to ensure that only the primary key determines the non-key columns, thus reducing redundancy and improving data integrity.

💡Transitive Dependency

Transitive dependency occurs when a non-key column can be determined by another non-key column, which is not directly related to the primary key. The video script uses examples like a sports match table where the 'capacity' of a ground can be inferred from the 'ground' column, indicating a transitive dependency that needs to be resolved to achieve 3NF.

💡Data Redundancy

Data redundancy refers to the duplication of data in a database, which can lead to inconsistencies and inefficiencies. The video script emphasizes that normalization, particularly 1NF, 2NF, and 3NF, helps in reducing data redundancy by structuring data in a way that each piece of information is stored only once and is easily accessible.

💡Database Security

Database security involves protecting data from unauthorized access and ensuring its integrity. The video script mentions that normalization contributes to database security by creating more tables, which can be managed to restrict access to sensitive information. This is particularly relevant when certain data needs to be hidden from some users, thus enhancing the overall security of the database system.

💡Flexibility

Flexibility in database design refers to the ease with which the database can be modified or expanded. The video script highlights that normalization increases flexibility by creating a structure that can accommodate changes more easily. For instance, adding a new type of data or modifying existing data becomes simpler and less disruptive in a normalized database.

💡Data Integrity

Data integrity ensures that data is accurate, consistent, and remains unchanged in the absence of intervention. The video script explains that normalization helps maintain data integrity by eliminating redundancy and dependencies that can lead to data inconsistencies. For example, by separating phone numbers into a separate table, the database ensures that each employee's phone numbers are accurately recorded and updated without affecting other records.

Highlights

Normalization is crucial for database design as it reduces data redundancy and increases table count.

Databases handle more tables efficiently than non-normalized forms, enhancing flexibility and security.

Advantages of normalization include increased design flexibility and improved database security.

Normalization forms must be followed sequentially, starting from the first normal form.

A table in the first normal form must avoid repeating values in a group and repeating groups in the table.

Example of an employee table in perfect form with employee ID as the primary key.

Handling multiple phone numbers for employees by separating them into different columns violates the first normal form.

Solution to repeating values is to create a separate table for phone numbers and connect it to the employee table.

A table in the second normal form must be in the first normal form and avoid partial dependencies.

Composite keys are used when there is more than one primary key column.

Partial dependency occurs when a non-key column depends on only part of the composite key.

To resolve partial dependency, break the dependent column into another table.

A table in the third normal form must be in the first and second normal forms and avoid transitive dependencies.

Transitive dependency occurs when a non-key column can be determined from other non-key columns.

Example of a student exam table where the maximum marks can be inferred from the exam type, violating third normal form.

Breaking the table to remove transitive dependency, such as maximum marks, and placing it in another table.

Transitive dependency also occurs with calculated values, such as earnings based on ticket sales and price.

Transcripts

whenever designing

database the most important thing that

you need to do is to

take care of the normalization rules by

definition normalization says it reduces

the

data redundancy and creates more table

than before

well database can handle more tables

rather than

the tables that are not in normalized

forms so making more tables

is much more suitable for database

rather than wrong tables

well the advantages that normalization

brings is

flexibility designs flexibility database

security database is much secure with

normalization because

more tables are formed and there are

cases when you

don't when you need some information to

hide from some users so

so it counts in data security reduces

data redundancy in a much organized way

these are the normalization types we'll

talk about by the way when considering

normalization forms you cannot bypass

the previous form

means first you have to go through first

normal form then second normal form

and then eventually third normal form

you cannot bypass the first two and

directly go to third one

first normal form for a table to be in a

first normal form it needs

two criterias two things that should not

happen in this table

first no repeating values in a group and

no repeating

groups in the table what does that mean

we'll see in the example

in this example of the employee table it

is in a perfect form

employee id is the primary key and it

has

values of name address and phone number

so far so good

what if there's a possibility when

employees having more than one phone

number

you can write them like this by

separating with the columns that's

repeating values in a column

and it's violating the first normal form

the other possibility of writing it

writing them in different columns

and that's repeating columns repeating

columns

again violates the first normal form

remember what we gathered from the

definition of first normal form

table cannot be in a first normal form

if it contains repeating values or

repeating columns repeating columns are

the ones

that contain almost same type of

information but in different

columns the solution for the problem is

that

we'll take out the part which is which

is creating problem in this table which

are the phone numbers

and create a separate table for phone

numbers and

connect them with the relevant value

which is the name in this case and then

join both the tables

now it's in first normal form although

the tables

have increased from 1 to 2 but there is

no repeating values or no repeating

columns database can easily handle

more than one tables but it does not

handle repeating values and repeating

columns so we'll need to take care of

that and now this table is in perfectly

first normal form

condition the table does not contain any

repeating values or repeated columns

for a table to be in a second normal

form it must have three things in it it

should be in first normal form

no partial dependency partial dependency

only can occur when there is a composite

key

now what is composite key composite key

composite key is when

there is more than one primary key

column means we just cannot identify

the result from one particular column in

this example

if we only make a primary key of student

id

we cannot take a record of any student

conveniently because

what if we say give me the result of two

zero one now two zero one

we cannot be sure if we are asking for

one two zero ones for software

architecture result or

quality assurance result likewise what

if somebody asked

us to give a result of 205 205 is

enrolled in history and quality software

architecture as well so

we make another column as a primary key

now we can easily judge a record

now the question will be give us a

result of student id 201

in the course quality assurance

or give us a result of student id 205

in the course of history now student id

was already a primary key we have also

made course as a primary key

as well when there is there are more

primary key columns

they are combinedly called as composite

key now more about partial dependency

student id and course are key columns

name marks and teacher are non-key

columns here in this table

now what is partial dependency partial

dependency is when

any of the non-key column does not

depend on all primary key columns

like here a teacher only depends on

the course what course does a teacher

teach

it only depends on code it it does not

have anything to do with student id

both both are key columns here is a

classic situation of

the fact that non-key column

is only partially dependent on on the

key columns

so this is called partial dependency and

then when there is a partial dependency

we need to we need to break that that

column

into another table which is only

partially dependent marks and name both

are totally dependent on both the keys

so no need there

teaches column and taken away from the

main

table and it is it is combined with the

course column

two tables and both tables does not have

any partial dependency

so it is now considered in the second

normal form

for a table to be in a third normal form

it must be in a first normal form and

second normal form already

third normal form should not have a

transitive dependency

what is transitive dependency we'll see

in the example now one more thing to

note is that

if one non-key column can be guessed

from other non-key column

that means it is violating third normal

forms rule

and we need to break the table we can go

through the examples to

make it more understandable in this

table student id is the primary key

column and name exam type and maximum

marks are non-key columns

from the table we can guess that if a

student is opting for viva

means maximum marks will be 20 student

is opting for theory

it will be of hundred marks and

practical will be of 50 marks

means we can guess the maximum marks

column from

exam type column and it also means that

maximum marks column and transitively

depending on

exam type column so if

some student of id 209 comes

and he's opting for practical exam we'll

already know

the maximum marks of practical would be

50. this is the classic case of

violating third normal form we will

break the maximum marks column and put

it in

the another table now it's not in third

normal form

there is no transitive dependency

between the tables transitive dependency

is bit tricky so we'll see another

example of that

now mesh match number is the primary key

teams ground and capacity

these are the non-primary keys of this

table it is absolutely clear

that if the match is played in mcg the

capacity of mcg is 80 000.

if it's played in hobart it's 30 000.

likewise the other ground we already

know the capacity of the grounds

so if match number 9 is played in mcg

will automatically put 80 000 in the

capacity column

so this is again capacity table is

transitively

depending on ground table again

violating third normal form we'll break

the table

taking away the transitive part of the

table which is capacity

putting it in another table last example

for the third normal form

in this table if someone's designation

is manager

the salary he'll be getting will be of

80 000

in the database table if there is any

transitive dependency between the

non-key columns we'll only break the

part that is transitively depending

on other part transitive dependency also

occurs when there is a kind of

calculation performed in the tables like

in this table

the amount of earning is dependent on

total

ticket sale multiplied by the price of

the ticket

this is also a transitive dependency if

there are a thousand tickets sold and

each ticket is of 200 then there is a

earning of 200 000 so if any calculation

can be performed on table

it is also considered in third normal

form