May 11, 2010 at 9:15 am
Hi,Can anyone tell me how to normalize and denormalize the database please?
I know there are five normalize forms and i know first two,three of them but i am not have very clear concept.I am asked in my interview how would denormalize your database?
Thanks
Irfan
May 11, 2010 at 9:34 am
a basic example might be Addresses; a normailzed database would pull out CITY and STATE to seperate tables, so your ADDRESS table would have CITYID and STATEID instead of the actual descriptions in a normalized database.
denormalizing would be to pull the descriptions back in the record, with the assumtion that the records would be faster to retrieve due to fewer joins.
Lowell
May 11, 2010 at 9:46 am
I'll do my best to briefly explain.
Normalized will leave you with 3 tables and no duplicates in any one table.
Create Table ##tName
(FirstName varchar(50),AddressID int)
Create Table ##tAddress
(Street varchar(50),City varchar(50), StateID int, AddressID int)
Create Table ##tState
(StateName varchar(50), StateID int)
go
insert into ##tState
values ('VA',1),('MD',2)
insert into ##tAddress
values ('12 first st.','Arlington',1,1),
('3 second ave.','Arlington',1,2),
('84 pink elephant rd','Baltimore',2,3)
insert into ##tName
values ('john',1),('bill',2),('jack',1),('kevin',3)
select n.FirstName, a.street, a.City, s.StateName
from ##tName n
inner join ##tAddress a
on n.AddressID = a.AddressID
inner join ##tState s
on a.StateID = s.StateID
To denormalize, you would have 1 table (or fewer than a beginning set of normalized tables) with all the information in it including duplicates.
Create Table ##person
(FirstName varchar(50),
Street varchar(50),
City varchar(50),
StateName varchar(2))
Insert Into ##person
select n.FirstName, a.street, a.City, s.StateName
from ##tName n
inner join ##tAddress a
on n.AddressID = a.AddressID
inner join ##tState s
on a.StateID = s.StateID
select * from ##person
drop table ##tName
drop table ##tAddress
drop table ##tState
drop table ##person
Yes, I realize this example is not fully normalized but it gets my point across (i hope).
May 11, 2010 at 11:57 am
Calvo,Thank very much for nice clarification with practical example.
Calvo or Anybody please,
If you have time can you please also explain to me clustered and no-clusterd index and their difference,If you want me to post a new post for question let me know.
I know data sorted and un-sorted,only one clustere index we can have in a table and 249 non-cluster.I always confused where it says data in clustered index is physically sorted and is on leafe level consists data where as non-cluster index keep pointer for data on leafe level instead of data.
But i know i need to know clear picture and concept.
Thanks in advance
Irfan
May 11, 2010 at 12:56 pm
Irfan-358189 (5/11/2010)
... please also explain to me clustered and no-clusterd index and their difference
A non-clustered index is a object totally separated from the table. Index points to the table.
A clustered index has it lower level -leaf level- sitting inside the table. If you are indexing CITY table by CITY_CODE this means CITY_CODE column on the base table is also the leaf level of the index, since index has to be ordered by indexing key this means CITY table is phisically ordered by CITY_CODE.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply