14-08-2012, 02:36 PM
DATABASE DESIGN and NORMALIZATION
DATABASE DESIGN.docx (Size: 207.82 KB / Downloads: 53)
Abstract
A Web site that provides a virtual community for people to share their daily activities with family and friends, or to share their interest in a particular topic, or to increase their circle of acquaintances. There are dating sites, friendship sites, sites with a business purpose and hybrids that offer a combination of these. Facebook is the leading personal site, and LinkedIn is the leading business site. Globally, hundreds of millions of people have joined one or more social sites.
Members create their own online "profile" with biographical data, pictures and any other information they choose to post. They communicate with each other by making their latest thoughts public in a blog-like format or via e-mail, instant messaging, voice or videoconferencing to selected members. The service lets members find and invite other members into their personal network (to "friend" them) as well as invite friends of friends (see friending). A photo sharing capability may also be provided. See sharing buttons.
INTRODUCTION
Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. 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.
NORMALIZATION:
Database Normalization or data normalization is a technique to organize the contents of the table for the transactional database and data warehouses. The process of reducing data redundancy in a relational database is called Normalization.
Why perform Normalization ?
A database is a set of facts about the world. Database administrators would like their database to contain only true facts about the world, and no false facts. Normalization allows the RDBMS to prevent some classes of false fact from occurring in the database, and therefore helps consistency. Obviously much of the information in the database can't be verified by the computer; the only types of error that can be prevented are errors that are inconsistent and entail some logical contradiction. Generally speaking, with each additional level of normalization one or more classes of logical error are made impossible by virtue of the structure of the tables.
What's the point of knowing Functional Dependencies ?
If you don't know your functional dependencies, then forever will you be condemned to reincarnate your update, insertion and deletion anomalies. Contemplate your FDs, and you may be able to achieve BCNF across your whole database, and if not, at least there are two levels in normalization Nirvana, and you could do 3NF, and break the hold of inefficient database design, which your puny and vain ER modelling did not account for. Despite the best efforts of that DB Kung Fu master to show you the way of ER Diagramming, it is still no match for the Fumbling Palm Style or the Fist of the Unyielding Blind Computer. You thought you chose great relationships and hung all the necessary attributes off your entities rectangles, and underlined all your primary key attributes, but the diagrammed decomposition of the originally cloud of an idea of a Video store franchise management system didn't prevent lossy joins and dropping some FDs. Skeptically, you could say that dropping FDs isn't a big deal if you don't know about FDs in the first place, but the point of FDs is that they're like used car warranties, and preserving FDs avoids update anomaly costly repairs.
Normal Form:
A relation is said to be in 5NF if and only if it is in 4NF and can not be further non-loss decomposed. In 5NF we use the concept of Join Dependency which is the generalized form of the multi-value dependency.
The purpose of 5NF is to achieve a lossless join decomposition with respect to all the candidate keys of a relation. 5NF is defined in terms of not breaking join dependencies that hold over a relation. a Join dependency is a statement that a particular decomposition will result in a lossless recomposition. A multivalued dependency, is a special case of a join dependency, expressed as a subrelation consisting of the MVDs determinant and dependent attributes, and a subrelation consisting of all the other attributes in the original relation and the MVDs determinant attributes, and hence the join occurs on the determinant attributes. A paper showed that if a 3NF relation has all candidate keys as single attributes, then all join dependencies will hold (there will be no decomposition involving candidate keys which will lead to a lossy recomposition.
Conclusion:
This is only an introduction to the subject of normalization. The topic is extensive. In easy wa y we can say normalization is a process of reducing duplicacy or redundancy of database. For those of you who are developing databases on a day to day basis, knowing how to formally normalize a table can save large amounts of time in the long run. Being able to determine what level of normalization a database needs and designing to that level of normalization can be difficult in larger applications and will often have many possible solutions. Education and experience are the tools you will need to design efficient databases with minimal (or, in a dream, no) anomalies. I hope this introduction to normalization is helpful to the readers.