Database [3i Infotech Placement]: Sample Questions 194 - 194 of 294

Glide to success with Doorsteptutor material for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 194

Describe in Detail Essay▾

Explain normalization with examples.

Edit

Explanation

  • Normalization is a database design technique, which organizes tables to reduce redundancy and dependency of data.
  • It divides larger tables to smaller tables and links them using relationships.

Example of Normalization:

Table Showing the Normalization Table
Full NamePhysical addressMovies rentedsalutationcategory
JanatFirst street plot No 4Pirates of the Caribbean clash of the titansMs.Action, Action
Rohit3rd street 34Forgetting Sarah Marshal Daddy՚s Little GirlsMr.Romance, Romance
Rohit5th AvenueClash of the TitansMrAction

Database Normal Forms

1NF (First Normal Form) Rules

  • Each table cell should contain a single value.
  • Each record needs to be unique.

1NF Example:

Understanding the example of above Normalization table

Table Showing the Normalization Table
Full NamePhysical AddressMOVIES RENTEDSALTUTATION
JanatFirst Street Plot No 4Pirates of the CaribbeanMs.
JanatFirst Street Plot No 4Clash of the TitansMs.
Rohit3rd Street 34Forgetting Sarah MarshalMr.
Rohit3rd Street 34Daddy՚s Little GirlsMr.
Rohit5th AvenueClash of the TitansMr.

2NF (Second Normal Form) Rules

  • It is clear that we cannot move forward to make our simple database in second Normalization form unless we partition the table above.
Table Showing the Normalization Table
MEMBERSHIP IDFULL NAMESPHYSICAL ADDRESSSALTUTATION
1JanatFirst street Plot No 4Ms.
2Rohit3rd street 34Mr.
3Rohit5th AvenueMr.
Table Showing the Normalization Table
MEMBERSHIP IDMOVIES RENTED
1Pirates of the Caribbean
1Clash of the Titans
2Forgetting sarah Marshal
2Daddy՚s Little Girls
3Clash of the Titans
  • We have divided our 1NF table into two tables՚ viz. Table 1 and Table2.
  • Table 1 contains member information. Table 2 contains information on movies rented.
  • We have introduced a new column called Membership_id, which is the primary key for table 1. Records can be uniquely identified in Table 1 using membership id

    3NF (Third Normal From) Rules

  • To move our 2NF table into 3NF, we again need to again divide our table.

Example

Table Showing the Normalization Table
MEMBERSHIP IDFULL NAMEPHYSICAL ADDRESSSALUTATION ID
1JanatFirst street plot No 42
2Rohit3rd street 341
3Rohit5th Avenue1
Table Showing the Normalization Table
MEMBERSHIP IDMOVIES RENTED
1Pirates of the Caribbean
1Clash of the Titans
2Forgetting Sarah Marshal
2Daddy՚s Little Girls
3Clash of the Titans
Table Showing the Normalization Table
SALUTATION IDSALUTATION
1Mr.
2Ms.
3Mrs.
4Dr.
  • We have again divided our tables and created a new table, which stores Salutations.
  • There are no transitive functional dependencies, and hence our table is in 3NF.
  • In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3