Does Normalization has any fixed answers
@pradeepchalmani (553)
India
April 21, 2011 3:16am CST
Any relation taken that may be any thing like take an example of library branch and all the entities that are related to it
ex:
Branch( branch id, branch addr, isbn, title, author, publisher id, publisher name, number of copies)
can any one do the normalization for the above mentioned example
does norrmalization has the exact answer...
2 responses
@owlwings (43910)
• Cambridge, England
21 Apr 11
Normalisation doesn't really have any exact answers. It is a process (or rather a series of processes because it is reiterated) until it reaches a stage where (in the final stage) each table has a N:M relationship with another and there is no redundancy. In some cases, carrying normalisation to such a stage is neither necessary nor desirable (you may be asked to say WHY and WHEN it is not always desirable!): it depends on the size and scope of the database and on the practicality of opening many tables for a particular query. Normalisation is only taken as far as it needs to go, in practice.
In your example, the only things which are pertinent to 'Branch' itself are 'branch_id' and 'branch_address'. Another table would be needed for 'BranchStock' which would then contain 'branch_id', 'isbn' and 'no_of_copies' (assuming that your library only holds publications with an ISBN). A further table, 'ISBN', would contain the fields 'isbn', 'title', 'author', 'publisher id' or 'publisher name' (but logically not both, since that would be redundant information).
I have to admit that I am rather rusty on normalisation and what I have given above is just a rough example off the top of my head! You will probably be required to know exactly what defines 1st, 2nd, 3rd and 4th Stage Normalisation (I think that I have only done up to 2nd Stage above, but, as I say, I am rusty: it is some 20 years since I took my exams!)
1 person likes this
@pradeepchalmani (553)
• India
21 Apr 11
superb i must say
i did taking branch, books and publisher as table names and
Branch(br id, br add)
books(isbn,title,author)
publisher(publisher id,publisher name, no. of books)
these are the tables which i have form from the above table ...
@owlwings (43910)
• Cambridge, England
21 Apr 11
"... each table has a N:M relationship with another"
I think that I meant to say that "no field has a M:M relationship with another"! I really should dust off my textbooks and notes! I find it relatively easy to do normalisation but I forget what I learned about the stages.
OK, Having dusted off a textbook, I see that I have probably forgotten more than I learned.
First Normal Form (1NF) is when the attribute values are indivisible. If a table contains the fields ISBN, Title, Authors, Publisher, it is not in first normal form because a book may have more than one author. If you want to find all the books written by a particular author, whether on their own or in conjunction with other authors, you would need a table of authors with a record for each author-ISBN dependency. This would probably be simply Author(Author_ID, ISBN) and the actual name (and other details) of the author would be stored in a different table simply because there may be more than one author with the same name!
There are then Second and Third Normal forms and finally Boyce-Codd Normal Form. I guess that you already have descriptions of these or can find them online, so there's no need for me to go into more detail.
@owlwings (43910)
• Cambridge, England
21 Apr 11
The tables you give above would be incorrect for the following reasons:
Branch (br_ID, br_addr) is OK as far as it goes (what happens if you want to query the database for branches in a particular zip code or city, though?)
Books (ISBN, Title, Author) is reasonable (but many books have more than one author. What if a customer wants to know what books you have by a particular author? Even if you can find all the books by John Smith, are they, in fact, the same John Smith?)
Publisher (Publisher_ID, Publisher_Name, No_of_books) is incorrect, however. The number of books is not an attribute of anything in that table because it refers to the number of copies a particular branch holds of a particular book (identified by ISBN). You therefore need a table which holds this information, maybe:
Branch_Stock (br_ID, ISBN, No_of_books) ... notice that I haven't underlined a key field. How is the central library going to find which branches hold copies of a particular ISBN (and how many in total?)
@sswallace21 (1824)
• United States
21 Apr 11
Normalization can never have a exact answer. Because everyone perceive normal in a different way. What's normal to me, may not be normal to you. Even though there may be a set definition for a word, does not mean we all understand it in the same context. Everyone's level of understand is also different. Best Wishes!
