Normalization:-
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
Below are the different normal forms:-
1NF: Eliminate Repeating Groups
A database is said to be in First Normal Form when all entities have a unique identifier or key(primary key), and when every column in every table contains only a single value and doesn't contain a repeating group or composite field. "All columns (fields) must be atomic".
A database is said to be in First Normal Form when all entities have a unique identifier or key(primary key), and when every column in every table contains only a single value and doesn't contain a repeating group or composite field. "All columns (fields) must be atomic".
2NF: Eliminate Redundant Data
A database table is in 2NF when it is in 1NF and if all its non-prime attributes are functionally dependent on the whole of every candidate key. (A non-prime attribute is one that does not belong to any candidate key. that means "table should be in1NF and every non-key column is fully dependent on the (entire) primary key".
As the First Normal Form deals with redundancy of data across a horizontal row, Second Normal Form (or 2NF) deals with redundancy of data in vertical columns.
3NF: Eliminate Columns Not Dependent On Key
A database is in Third Normal Form when it is in Second Normal Form and each column that isn't part of the primary key doesn't depend on another column that isn't part of the primary key.All attributes must be directly dependent on the primary key."In 2NF and every non-key column is mutually independent."
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related. "Remove the multivalued dependency."
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
A table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.
De-normalization:-
It is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
When not to normalize:-
1)Want to keep tables simple so user can make their own queries
2)Avoid processing multiple tables
3)whenever performance issues.Testing shows Normalization has poorer performance
A database table is in 2NF when it is in 1NF and if all its non-prime attributes are functionally dependent on the whole of every candidate key. (A non-prime attribute is one that does not belong to any candidate key. that means "table should be in1NF and every non-key column is fully dependent on the (entire) primary key".
As the First Normal Form deals with redundancy of data across a horizontal row, Second Normal Form (or 2NF) deals with redundancy of data in vertical columns.
3NF: Eliminate Columns Not Dependent On Key
A database is in Third Normal Form when it is in Second Normal Form and each column that isn't part of the primary key doesn't depend on another column that isn't part of the primary key.All attributes must be directly dependent on the primary key."In 2NF and every non-key column is mutually independent."
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related. "Remove the multivalued dependency."
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
A table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.
De-normalization:-
It is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
When not to normalize:-
1)Want to keep tables simple so user can make their own queries
2)Avoid processing multiple tables
3)whenever performance issues.Testing shows Normalization has poorer performance
Hi There,
ReplyDeleteWhat a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.
Showing error converting data type varchar to numeric.
This code:
declare @code as varchar(250)
set @code = '8164,8165,8166,8167,8168'
select * from mac_accounts where acc_code in (@code)
A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.
Thanks a lot. This was a perfect step-by-step guide. Don’t think it could have been done better.
Merci,
Lenin
Greetings Mate,
ReplyDeleteI love all the posts, I really enjoyed.
I would like more information about this, because it is very nice., Thanks for sharing.
Do you mean that you meet
a failure in the "global rules" check? This means that you require restart your machine before installing SQL Server for some reasons like LiveUpdate on the machine or a previous uninstalling of SQL Server, you can restart your machine.
If you are repeatedly prompted to restart the computer when installing SQL Server, please use the following code to install SQL Server.
Setup.exe /SkipRules=RebootRequiredCheck
Appreciate your effort for making such useful blogs and helping the community.
Kind Regards,
Ajeeth Kapoor
Hi Mate,
ReplyDeleteFully agree on SQL and PL/SQL Interview Questions and Answers. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.
I want to execute the different files at the run time in the SQL PLUS
Ex: I have two different files like ABC.SQL, XYZ.SQL,
I had declared one bind variable to hold file_name .
var file_name VARCHAR2(200);
DECLARE
sr VARCHAR2(20) :='ABC';
BEGIN
IF sr ='ABC' THEN
:file_name :='ABC.SQL';
ELSE
:file_name :='XYZ.SQL';
END IF;
END;
/
PRINT file_name
Now i want to execute the file which is there in :file_name bind variable
SQL>@:file_name
It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
Keep up the good work!
Thanks,
Morgan
Aloha,
ReplyDeleteGrazie! Grazie! Grazie! Your blog is indeed quite interesting around PL/SQL Interview Questions with Answers I agree with you on lot of points!
I have one hierarchy query for tables exchange_grp and node.
Second hierarchy query for tables node and node_side.
SELECT E.P_K, E.I4030_EXCH_GRP_CD, N.P_K, N.I4020_NODE_ID, N.I4020_NODE_CAT, LEVEL
FROM WORK_REP.EXCHANGE_GRP E, WORK_REP.NODE N
WHERE E.P_K = N.FK_S_EXGRP_NODE
AND E.I4030_EXCH_GRP_TYPE = 'SVX'
START WITH N.P_K IN E.NK_S_EXGRP_NODE
CONNECT BY NOCYCLE N.P_K = PRIOR N.NK_S_EXGRP_NODE
and
SELECT N.P_K, NS.P_K, NS.FK_S_NODE_NSIDE, NS.I4040_SIDE_TYPE, LEVEL
FROM WORK_REP.NODE N, WORK_REP.NODE_SIDE NS
WHERE N.P_K = NS.FK_S_NODE_NSIDE
START WITH NS.P_K IN N.NK_S_NODE_NSIDE
CONNECT BY NOCYCLE NS.P_K = PRIOR NS.NK_S_NODE_NSIDE
Can we combine these two queries into one.
It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
Keep up the good work!
Thanks a heaps,
Radhey
kastamonu
ReplyDeletebilecik
edirne
nevşehir
van
0PXL