Normalization

-
Normalization is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. Normalization starts by identifying the dependencies of a given relation and progressively breaking up the relation (table) into a set of new relations (tables) based on the identified dependencies.

Normal Forms

  • 0NF 
          Multi-valued attributes exists
  • 1NF
          Any multi-valued attributes (repeating groups) have been removed.
  • 2NF
          Any partial functional dependencies have been removed
  • 3NF
          Any transitive dependencies have been removed
  • BCNF
          Any anomalies resulting from functional dependencies have been removed
  • 4NF
          Any multi valued dependencies have been removed
  • 5NF
          Any remaining anomalies have been removed


The table in 0NF :

  • eliminate the nulls by making sure that each repeating group attribute contains an appropriate data value.
  • identify the primary key
            PO-No ← This is the primary key for PO relation
           (PO-No , PART-No)  ← This is the primary key for PO-PART relation



The tables in 1NF :



  • identify partial functional dependencies. Check whether every non-key attribute is dependent on the whole key(not the part of the key)
          PART-DESC(Part Description) is depended only on PART-No, which is part of the key of 
         PO-PART relation.


The tables in 2NF :

  • identify transitive dependencies. Check whether each non-key attribute is only dependent on the whole key, and not dependent on any non-key attribute.
          SUPP-NAME (Supplier name) is a non-key field depended on another non-key field 
          (SUPP-No)  in  addition to be depended on the key PO-No (purchase order no)

The tables in 3NF :


From the data modeler’s point of view, the objective of normalization is to ensure that all tables are at least in third normal form (3NF). Some very specialized applications, such as statistical research, might require normalization beyond the 4NF.




Comments

Post a Comment

Popular posts from this blog

Database Types

-
Types of Databases Databases can be classified according to the number of users, o    Single user database - A single-user database that runs on a personal computer is called a desktop database o    Multi user database ·          Workgroup database - the multiuser database supports a relatively small number of users (usually fewer than 50) or a specific department within an organization ·          Enterprise database Databases can be classified according to the database location(s), o    Centralized database - a database that supports data located at a single Site. o    Distributed database – a database that supports data distributed across several different sites The most popular way of classifying databases today, however, is based on how they will be used and on the time sensitivity of the information gathered from them. o    operational database (sometimes referred to as a transactional or production database) - A database that is designed primarily to suppor
Read more

Entity Relationship Diagram (Pat II)

-
Image
Mapping Binary Relationships One to one Relationship  Partial participation (Partial participation is represented by single lines.)           Not every professor teaches every student. Every student is not learnt by every professor.             Relational Mapping                                                                   Professor ( Name , Phone , Address )                                                                                       ↑FK            Course   ( CourseId  , CourseName , Credits , Name) Total Participation (Total participation is represented by double lines.)             Every professor teaches every student. Every student is learnt by every professor           Relational Mapping                      Professor ( Name , Phone , Address , CourseId , CourseName , Credits) One to many Relationship (Partial participation)           Relational Mapping                                          
Read more