Lecture 50 : Database Normalization(Part-1)

IIT Kanpur July 201
20 followers
2 Video Views·Oct 5, 2024
Hello everyone, welcome back to the course on Carbon Accounting and Sustainable Designs
in Product Life Cycle Management.
I am Dr. Pradul Prasad Singh and we are co-teaching this course with Professor Deepu Philip and
Dr. Amandeep Singh.
Till now we have talked about various kinds of carbon accounting databases and then we
dive deeper into the database management systems.
So, till now we have covered carbon accounting databases, we have talked about database management
systems, we saw what is the actual database design process, so this process will be useful
to design your own carbon accounting database.
Then we talked about entity relationship diagram which is a high level design of the complete
database and in the last video we talked about how to translate these ER diagrams, translation
of ER diagram to schema of the database.
We have also looked upon these structured query language which is useful for communicating
with the different kinds of databases, since we are using RDBMS, so we have looked upon
what are the different kinds of queries that we can run on a database server.
So, for today the outline is mainly that we will start by first installing and first knowing
about what is a MariaDB database which is a kind of RDBMS, relational database management
system.
Then we will install MariaDB on our machines, since most of us are using windows so we will
be installing windows on windows only, but the process of installing on macOS and Linux
is a bit different but usually same for both Linux and macOS.
After that we will talk about what are database normalizations, why it is useful and how they
make our database tables efficient and then at the end we will try to write queries in
a freshly installed server, database server and we will try to see how we can normalize,
how to normalize unstructured or un-normalized database table.
So, this is the agenda for today, so let us start with MariaDB, so MariaDB is nothing
but an open source RDBMS, further it is a fork of MySQL, so this was the original database
server that was heavily used by the community but then it was taken up by the Oracle, so
this open source MariaDB RDBMS is a fork of, is just a copy of MySQL before getting it
acquired by Oracle, so this MariaDB was developed in 2009 and why it was developed, after the
acquisition of MySQL by Oracle, so Oracle is an organization, well known organization
and this MariaDB adds several performance improvements and security enhancements, it
is licensed open source licenses, so it is free to use, also it supports parallel queries
that is multiple users and multiple queries can be run together and also supports thread
pooling, so these features are useful to have a performant database server and it also
provides user management and encryption, so data at rest encryption, it allows handling
of semi-structured data, it is perfect for dynamic websites, it also supports data warehousing,
so web applications like WordPress, they use it heavily, also it can handle large user
groups, so if you remember our previous discussion of carbon accounting databases that are prevalent
and what are the needs while creating a carbon accounting database, you may remember that
few of the features that are available or most of the features that are available in MariaDB
are useful for creating a carbon accounting database, so let me highlight those features
like the handling of real-time data, handling of large user loads or the availability of
management of users or this parallel queries like multiple organizations are using the same
carbon accounting database or so these kinds of features, they are available and that's why we
can use MariaDB as our database server for creating our own relational database management
system, so let us now switch to install our MariaDB server on our machines, so let us see how
we can do that, so first of all let's open your browser and do a web search by writing
MariaDB, so this website with the web address mariadb.org, this is the actual website of MariaDB,
you can directly click on download MariaDB server and something like this will open on your systems
as well and here it is asking that what is the version of the server that you want to install,
so currently it is highlighting MariaDB server 11.5.2 rolling, so rolling are the cutting-edge
solutions that are available by this organization, if we are a beginner then we should not use
these kinds of versions, so we can use MariaDB server 11.4.3, all other versions are in the build
stages right, this is the stable version, now you can further choose on which operating system you
want to install, so if you want to install on Linux you can download it or if you want to install it
from source then also you have the possibility of downloading it, further if you are on a Mac OS
then you can use a package manager and can directly install this server, so since we are using a
Windows 10 we can install this, keep the architecture as same, this is a 64-bit version and
use the MSI package if you are okay and just click on download, it will start downloading,
so our file has successfully downloaded, it's about 74 MB right now and we can just click on
it to open it, something like this will open up, this is the installation window of the MariaDB
server, so clicking on next you can accept this term, press the next button and then you can see
that all these things will get installed, just click on next again and here it is asking for a
root password, so this is the main password of the MariaDB database server that you will need
to provide every time when you want to open it, so let us just write it a very simple password
R O O T 1 2 3, root 1 2 3 right, again provide the same password R O O T 1 2 3 and if you want to
access this server from a different machine than this by connecting it to this machine, then you
can utilize this check option, so that you can use the root user from that remote machine, however
it is usually not prescribed that you should use root user from different machines, so let us skip
this checkbox and you can click this use UTF-8 as default servers character set and just click on
next, now it is asking you that should we install this server as a service on this machine, so you
can tick this and also just this is the default TCP port that is how this MariaDB server will
connect to other applications if you want to, so we will in the upcoming lectures we will see how
we can connect our database and retrieve and modify the information inside the database using
other programming languages like Python, JavaScript or something like that, so while connecting with
other programming languages you need to use this port, so and the default port is 3306, so just
keep it as a default right now and just click on next, now install it, so your server is installed
and you can now access it, but before doing that we need a console to access it, so windows provide
different kinds of console like command prompt, PowerShell, so we will be using the windows terminal
that is available in windows 10 and 11 by default, if it is not available then you can install it,
you should edit your environment variables, so this is the windows for system properties, you can
click on environment variables and here is the user variables or system variables, so you can add
at any of these, let us just write in the user variable, so before that where is the actual path
of the MariaDB server that was installed, so you can press windows plus E to open the explorer,
go to this PC, C drive where your actual windows installation is there and search for MariaDB,
this is the location and in this find the bin directory, now you need to use this path, C
program files MariaDB 11.4 bin, copy this path and open this window, now find the path variable
and use edit option, now click on new and just paste the location of this installed MariaDB server,
click on ok, again ok and now you can use this MariaDB server from your terminal, so you can
open a terminal window by searching for the terminal window and just click on open, now this
is a terminal window and you can write here the command mysql u root p, now it is asking for the
same password that you have provided while installing the MariaDB server, so it was R O T
1 2 3, so now we have entered into the MariaDB server using a windows terminal and we have
installed it on a windows machine, the same steps are little bit different but same steps can be
followed on your other machines like Linux or Mac OS, so let us go back again to the topics and
our installation is complete, so let us switch to the next topic which we want to cover and
which we want to learn with the actual MariaDB server, so this is database normalization,
so what happens is we learn how to create a database entity diagram for a database,
then we learn how to translate it to a database schema and from that we also learn how to
develop the database tables or database instances, now the main thing is when you are trying to
capture the real world during the requirement analysis phase and you want to create a table
in your database, you can create a single table for all the data but it is not an efficient way
to perform all the database table operations, so that is why we want to create efficient tables
that is we need to understand why and how much database fields are required in a particular
table, can we reduce those fields, can we divide a single table into n number of tables, so that
the operations of a database management system gets efficient, so all of these decisions can
be made through the database normalization task, right, so let us understand how to do all of these
database normalization, so database normalization is a process of organizing data,
why we are organizing the data, to reduce redundancy or to improve integrity, so we can
divide a single table into, divide into multiple tables and further define relations between them,
so the most basic table that anybody can draw by getting the requirement analysis is
the un-normalized form, right, so let us consider a very simple database of a student, so we can
we can create a table like this, so these are the fields or attributes of our table
and let's say the first entry or the first tuple has a student id with 141, the name is Abhishek,
course under which this student is enrolled is model design
and let's say the phone number is 123, right, similarly let us just fill this table
again it is Abhishek, another course he is doing is machining
and the mobile numbers remain the same
the next student is 1410305 that is Manish, he is doing a course on food
his mobile number is 987654321
again it is about Manish and he is also doing a machine design course,
now while providing this detail he provided two numbers, so the first remains the same 987654321
and there is one more number let's say 999222111, so this is the most basic form of table where
all the data is present in a single table, so you can see there are different kinds of repetitions
like there are repetitions in student id, the names and the column of the phone number is
containing more than two phones, so let us start using our MariaDB server that we installed and
try to see how to create this kind of table into the server, so let us switch to our server,
right now it is showing that there is nothing, so none is being highlighted, so you can use
simple command show databases with a semicolon and it will show that these four are the default
databases that were installed while installing your MariaDB server, now you can create a database,
so let's say create database, now you have to provide the name of the database, so let it be
db normal, so it is showing that query okay one row affected that means your query has
been successfully executed by the MariaDB server, now again you can write show databases
or instead of writing it again if you know that you have written a command and you have executed
it before, so you can just use the arrow keys on your keyboard and use the upper arrow to get into
the history of the commands you have wrote, so you can see that you have just written a create
database db normal, you can go one more up and you can see that this is the same command that
you want to run it again, so just press enter, now you can see instead of four now it is showing
five rows that means db normal is also a database which has been created by you,
but this kind of arrow key commands will only be available in the same while until and unless you
do an exit command all the history will remain intact otherwise the history will get removed
okay, so now you know that your database has been created you have to switch to that database
because currently MariaDB is showing that there are no active databases because it is showing none
so write use db underscore normal semicolon, so it is showing that database has been changed
you should also see that i am using the lowercase letters while discussing in the previous lectures
i was using uppercase letters as well, so right now the configuration of database server that
you have installed has case insensitivity that means you can use any type of case but you can
change that behavior by changing the configuration of the database right, so now you have a database
in a database you can have multiple tables so let's see if you want to check how many tables
are currently there you should see that there are no tables because you have just created this
database but you can confirm this by writing show tables so it is an empty set that means
there are no tables now you need to create a table so you have seen this is the table that
you want to create right this one so we first need to create the database relations that means the
attributes of the relation and after that we need to fill this data so first let us create the scheme
of the database inside the MariaDB server so you should write create create table name of the table
is student unf that means unnormalized form unf and start providing the scheme of the table
so you can press enter this arrow is showing that the command you are writing is not yet complete
because you have not provided a semicolon so i am continuing my command otherwise it will take a
long space horizontally okay so the first field of the table was student id so you can write
student id and the data type of the variable is int next student name data type of the variable
is where character that means string the length of the string should be 100 characters maximum of
next is course name it is also a string and let us just or let us put this as 200
course name could be long so the last attribute is phone number
this let us just put it as a string as well right now
so that we can have multiple phone numbers and currently it is violating the atomicity
of the database if we are using it as a multiple so this was my database schema and the query is
that means i should have a table in my database with the name db normal and the table name should
be student unf unnormalized form and it should have a student id student name course name and
phone number so let us see what is the we can check the schema of a table by using describe
function describe statement so we made a mistake of creating the name of the field as a phone
number so we can alter this column name by using this command alter the alter what table which
table student unf and then what we want to do we want to rename a column so rename a column and
we can rename the wrong value to the correct value that we want to fill so now if you describe
this table you can see that student underscore this is the scheme of this table now you have
the fields of the table into your database now you want to fill the data in the table
so the data once again this is the data these four rows that we want to fill so let us start
writing the command for filling this data so if you remember we have discussed that insert into
command is the command for filling the data so let us write the first insert into student
underscore qnf and the student id
student
so these are all the four fields right and now the next part of this command is
what are the values that you want to fill in these fields so you can write values the first is the
student id that is 1410301 since it is a number we are not using the single quotation marks
then the next is a string so you should use single quotation mark start with it write the
name abhishek then the course name that is model design and finally the phone number of the
student and since you can see in the schema of the table that phone number is also a string
so we need to put this as a string so you can write the phone number now just check it again
that you are using an insert into command the name of the table is student underscore unf
and fields of the student table is student id student name course name and phone number
and these are the four values corresponding to these fields so you can use a semicolon and just
press enter so this is how you have filled your table you can see what are the contents of your
table so to see the schema you can write describe statement above and to see the content of this
whole table you can use the select statement that we have discussed select star from where
from the student table so student underscore unf semicolon so now you can see that you have
first defined the scheme of the table then you are start filling the tuples in the table so this is
the first tuple now use the same statement to again fill the next row so the next table also
is about the student abhishek so the row number remains the same name is same course name will
be different it is machining and phone number is also same so you can just press enter so
now you should have two rows for the same student again use the statement insert into
now fill the details for manish he is taking the course on tool mechanics
and his phone number is one more thing you can provide multiple values as well
in the same command of insert into so just use a comma instead of semicolon
and press enter and again write the values that you want to fill okay so the next and the last
row of this table is again about manish 1410305 name is manish another course he is taking is about
machine design and now he provided two phone number there so now you should press the semicolon and
so there are two rows and the your database management system is also showing that there
are two records zero duplicates and zero warnings right now see the status of your table and the
details in it so these are the four rows that we have seen here and all the phone numbers course
so this is the un-normalized form of the table and this is not the correct form of creating a
table because it is not efficient in a single table there are multiple student ids multiple
student names in a student name column and phone number is also storing composite values
so to make your tables more efficient there is a complete process of creating from going from
an un-normalized form to a normalized form so there are different stages of normalized form
one first normalized form second normalized form third normalized form and there are more advanced
normalized form as well like bcnf and all so we will study only up to the third normalized form
thank you
