Database [3i Infotech Placement]: Sample Questions 254 - 254 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 254

Database
Edit

Describe in Detail

Essay▾

What is normalization?

Explanation

Define Normalization
  • It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties
    • Minimizing redundancy
    • Minimizing insertion, deletion and update anomalies.
  • Minimizing redundancy:
    • The data in a DBMS is more concise because, as a general rule, the information in it appears just once.
    • This reduces data redundancy, or in other words, the need to repeat the same data over and over again.
    • Minimizing redundancy can therefore significantly reduce the cost of storing information on hard drives and other storage devices.
    • In contrast, data fields are commonly repeated in multiple files when a file management system is used.
  • Minimizing insertion, deletion and update anomalies.
Table Shows the Staff Data
StaffIDNameSexPositionSalBranchISBr_Address
S1AmitMAssociate Director15000 ₹B16 Lanark Square
S2MayurMProject manager12000 ₹B299 Shelly Avenue
S3JimyMIt Manager11000 ₹B3225 Euston Road
S4ParthMManager18000 ₹B16 lanark square
S5HeryFAnalyst5000 ₹B450 Malvem Road

Tbl_Staff_Branch

Table Shows the Staff Data
StaffIDNameSexPositionSalBranchID
S1AmitMAssociate Director15000 ₹B1
S2MayurMProject manager12000 ₹B2
S3JimyMIt Manager11000 ₹B3
S4ParthMManager18000 ₹B1
S5HeryFAnalyst5000 ₹B4

Tbl_Staff

Table Shows the Staff Data
BranchIDBr_Address
B16 Lanark Square
B299 Shelly Avenue
B3225 Euston Road
B450 Malvern Road

Tbl_Branch

  • Insertion anomalies:
    • To insert the details of a new member of staff located at branch B1 into the Tbl_Staff_Branch Table shown above, we must enter the correct details of branch numner B1 so that the branch details are consistent with the values for branch B1 in other rows.
    • To insert the details of a new branch that currently has no members of staff into the Tbl_Staff_Branch table, it is necessory to enter nulls for the staff details which is not allowed as staffID is the primary key.
    • But if you normalize Tbl_Staff_Branch, which is in Second Normal Form (2NF) to Third Normal Dorm (3NF) , you end up with Tbl_Staff and Tbl_Branch and you shouldn՚t have the problems mentioned above.
  • Deletion anomalies:
    • If we delete a row from the Tbl_Staff_Branch table that represents the last member of staff located at that branch, (for e. g. row with Branch numbers B ″ , B3 or B4) the detals about that branch are also lost from the Database.
  • Update anomalies:
    • Should we need to change the address of a particular branch in the Tbl_Staff_Branch table, we must update the rows of all staff located at that branch.
    • If this modification is not carried out on all the relevant rows, the database will become inconsistent.

Developed by: