Normalization in System Analysis and Design

Comments (0)

Transcript of Normalization in System Analysis and Design

System Analysis and Design Normalization Normalization is the transformation of complex user views and data stores to a set of smaller, stabledata structures. Normalization Three Steps of Normalization The first step in normalizing a relation is to remove the repeating groups. First Step or the 1NF In the second normal form, all the attributes will be functionally dependent on the primary key. Therefore, the next step is to remove all the partially dependent attributes and place them in another relation. Second Step or the 2NF A normalized relation is in the third normal form if all the nonkey attributes are fully functionally dependent on the primary key and there are no transitive (nonkey) dependencies. In a manner similar to the previous steps, it is possible to break apart the relation CUSTOMER-WAREHOUSE into two relations, Third Step or the 3NF The E-R diagram may be used to determine the keys required for a record or a database relation. The first step is to construct the E-R diagram and label a unique (primary) key for each data entity. Using the Entity-Relationship Diagram to Determine Record Keys A one-to-many relationship is the most common type of relationship, since all many-to-many relationships must be broken down into two one-to-many relationships. When a one-to-many relationship occurs, place the primary key on the table at the one end of the relationship as a foreign key on the table on the many end of the relationship. For example, since one customer may have many orders, place the customer number on the order record. One-to-many Relationship The first stage of the process includes removing all repeating groups and identifying the primary key.

The second step ensures that all nonkey attributes are fully dependent on the primary key.

The third step removes any transitive dependencies. The complete database consists of four 1NF relations called SALESPERSON, SALES,CUSTOMER, and WAREHOUSE. An entity-relationship diagram for the Al S. Well Hydraulic Company database. Many-to-Many Relationship When the relationship is many-to-many, three tables are necessary: one for each data entity and onefor the relationship. The ORDER and ITEM entities in our example have a many-to-many relationship.The primary key of each data entity is stored as a foreign key of the relational table. The relationaltable may simply contain the primary keys for each data entity or may contain additional data,such as the grade received for a course or the quantity of an item ordered.