December 27, 2010 at 7:52 am
Also post any thread/link which will clear Normalization concepts.
Following are my Doubts
1. Does database desingning means normalization? How much weightage goes for it in DB Design?
2. Upto which normal form it is advisible?
3. I read so much documents of normalization, but issue is that different document states differently for 1st, 2nd & 3rd normal form.
What i understood is
employee details (Unnormalized data)
id name Add deptNo deptName shift1 shift2
1 prav xyz 1 MPT Y N
2 prav rbt 1 MPT N Y
--------------
employee details (ON 1NF)
id name Add deptNo deptName shift
1 prav xyz 1 MPT 1
2 prav rbt 1 MPT 2
----------------
(ON 2NF)
emp
id name Add deptNo shift
dept
deptNo deptName
Please help for 3NF
December 27, 2010 at 8:08 am
just the refs of some interesting articles on data analysis (DA) and normalization:
http://qa.sqlservercentral.com/blogs/abhijit_desai/archive/2010/09/07/noramlization.aspx
http://qa.sqlservercentral.com/articles/Advanced/coddsrules/1208/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 27, 2010 at 8:33 am
Last time I looked, Wikipedia had good articles and examples for Normalization and the various Normal Forms. It's really more than can be explained in a simple forum post.
The basic idea behind normalization is that you should never have to run an update in more than one place, and you should never have to repeat data in a row.
So, in the example of a database of employee data, yes, you would move the department name into a separate table, so that if the name changes, you only have to change it in one place.
I'd usually also move the address to a separate table from the employee name. Same for phone numbers and e-mail addresses. People can have more than one of each, and some people might not even have one. (Yes, there are still people in the world who don't have e-mail, for example.) Otherwise, what do you do if an employee has one address they live at, and another one they want their paycheck sent to?
Normalization is part of database design. There's more to design than just normalization, but it is part of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 27, 2010 at 1:39 pm
sawantpl@gmail.com (12/27/2010)
1. Does database desingning means normalization? How much weightage goes for it in DB Design?2. Upto which normal form it is advisible?
3. I read so much documents of normalization, but issue is that different document states differently for 1st, 2nd & 3rd normal form.
1- No, it doesn't. Normalization/Denormalization is one of the steps of database design.
2- As it always happens... it depends. In this case mostly depends of the type of database you are designing, for an OLTP system I'll go (at least) 3NF; for a DSS system I do prefer to go with dimensional design.
3- Different definitions of 1NF, 2NF and 3NF in different documents? really? I know there are good and bad documents in the net but failing to properly define 1NF, 2NF and 3NF is something I still have to see.
_____________________________________
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.December 28, 2010 at 7:52 am
1. Normalization is a part of design, as Paul says. The amount of weight that goes behind it? A lot. You really should normalize the database design properly or you will pay for it as the design of the system goes on, and over the life of the system.
2. Absolutely 3rd normal form. You may back off from that in some places as the project is developed, but you must get to 3rd normal form to start with. An old phrase, not exactly accurate, but applicable is: "Normalize til it hurts, denormalize til it works."
3.The rules of normalization were established by Dr. Codd. They should be same anywhere you look. If they're different, that source is suspect.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 28, 2010 at 8:40 am
Thanks Grant Fritchey.
Can you please complete my example & give solution for 3NF.
December 28, 2010 at 9:09 am
sawantpl@gmail.com (12/28/2010)
Thanks Grant Fritchey.Can you please complete my example & give solution for 3NF.
Honestly? I don't think so. I don't have enough understanding what that data represents to give you a structure.
On a guess, it looks like you should probably have another table for Shift, but I'm not sure about that. I'm also not sure how that relates to the other table, is it one to many or many to many?
Too much up in the air to make direct advice on the examples provided.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 28, 2010 at 10:01 am
sawantpl@gmail.com (12/28/2010)
Can you please complete my example & give solution for 3NF.
Post a proper ER model and we will gladly help out 🙂
_____________________________________
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.December 28, 2010 at 10:28 am
Actually I would suggest that you, the original poster, try to normalize this according to documentation and ask if you are correct, or ask specific questions about whether your design conforms to normalization.
We don't know the rules of your data, and whether a piece of data represents a repeating value, as Grant mentioned.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 12, 2011 at 11:35 am
PaulB-TheOneAndOnly (12/27/2010)
sawantpl@gmail.com (12/27/2010)
3- Different definitions of 1NF, 2NF and 3NF in different documents? really? I know there are good and bad documents in the net but failing to properly define 1NF, 2NF and 3NF is something I still have to see.
Paul, I don't think you can have looked very hard :). ALZDBA's link leads to an article by Abhijit Desai which is one totally awful example among many.
sawantpl, So much of the information on database design that you will find online is just rubbish. Please don't try to learn from online sources. Get a decent book or take a course.
January 12, 2011 at 3:10 pm
David Portas (1/12/2011)
PaulB-TheOneAndOnly (12/27/2010)
sawantpl@gmail.com (12/27/2010)
3- Different definitions of 1NF, 2NF and 3NF in different documents? really? I know there are good and bad documents in the net but failing to properly define 1NF, 2NF and 3NF is something I still have to see.Paul, I don't think you can have looked very hard :). ALZDBA's link leads to an article by Abhijit Desai which is one totally awful example among many.
You are most certainly correct David - I do not wake up at 3 in the morning with an urge to check those things 😀 Bad definitions of 1NF/2NF/3NF equates to bad definitios of the OSI model - whoever does something like that should be impaled, burned and lapidated... in no particular order 😉
_____________________________________
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply