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
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:
Full Name | Physical address | Movies rented | salutation | category |
Janat | First street plot No 4 | Pirates of the Caribbean clash of the titans | Ms. | Action, Action |
Rohit | 3rd street 34 | Forgetting Sarah Marshal Daddy՚s Little Girls | Mr. | Romance, Romance |
Rohit | 5th Avenue | Clash of the Titans | Mr | Action |
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
Full Name | Physical Address | MOVIES RENTED | SALTUTATION |
Janat | First Street Plot No 4 | Pirates of the Caribbean | Ms. |
Janat | First Street Plot No 4 | Clash of the Titans | Ms. |
Rohit | 3rd Street 34 | Forgetting Sarah Marshal | Mr. |
Rohit | 3rd Street 34 | Daddy՚s Little Girls | Mr. |
Rohit | 5th Avenue | Clash of the Titans | Mr. |
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.
MEMBERSHIP ID | FULL NAMES | PHYSICAL ADDRESS | SALTUTATION |
1 | Janat | First street Plot No 4 | Ms. |
2 | Rohit | 3rd street 34 | Mr. |
3 | Rohit | 5th Avenue | Mr. |
MEMBERSHIP ID | MOVIES RENTED |
1 | Pirates of the Caribbean |
1 | Clash of the Titans |
2 | Forgetting sarah Marshal |
2 | Daddy՚s Little Girls |
3 | Clash 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
MEMBERSHIP ID | FULL NAME | PHYSICAL ADDRESS | SALUTATION ID |
1 | Janat | First street plot No 4 | 2 |
2 | Rohit | 3rd street 34 | 1 |
3 | Rohit | 5th Avenue | 1 |
MEMBERSHIP ID | MOVIES RENTED |
1 | Pirates of the Caribbean |
1 | Clash of the Titans |
2 | Forgetting Sarah Marshal |
2 | Daddy՚s Little Girls |
3 | Clash of the Titans |
SALUTATION ID | SALUTATION |
1 | Mr. |
2 | Ms. |
3 | Mrs. |
4 | Dr. |
- 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