Normal Forms in Relational Database Management System

 

Normal Forms in Relational Database Management System:



TO watch YouTube Video ,

    




raw-data-:

name  , phone  city,  state

Ram,    919876543210 , 918987654327, Banaglore,                       KA

Dave,    919876543212 , 918987654328, Banaglore,                       KA

Ali,    919876543213 Jaipur,                            RJ

Milkha,    919876543219,  918987654329, Bathinda,                           PA




1st Normal Form -


1. each table cell contain a single value (atomic)

2. each record needs to be unique


eliminate the repetition



Solution:



name    ,  Phone ,    City,    State

Ram ,  919876543210,      Banaglore, KA

Ram ,  918987654327,      Banaglore, KA

Dave                ,  919876543212,        Banaglore, KA

Dave                ,  918987654328,        Banaglore, KA

Ali                     , 919876543213,     Jaipur, RJ

Milkha,                919876543219         Bathinda,           PA

Milkha,                918987654329         Bathinda,           PA




2nd  Normal Form ->


1,  Must Be in 1st Normal Form

2,  No non-prime attribute is dependent on the  proper subset of any candidate key of the table


TeacherId ,  Subject ,           SubjectId

111, Maths  ,          11

111,                Physics,          12

222,                Biology,  13

333,                Physics,          12

333,                Chemistry, 15


Candidate-Key = {TeacherId, SubjectId}


Non-Prime attribute = Subject


as the non-prime attribute Subject is dependent on SubjectId which is part (subset of the candidate key)

, hence its not in 2nd Normal Form.


   OR 

   

   There should not be partial dependency


Solution:


subject-detail table ,    

SubjectId  , Subject

11,   Maths

12,   Physics

13,   Biology

15,   Chemistry




subject-teacher table

SiubjectId , TeacherId

11, 111

12, 111

12, 333

13, 222

15, 333




3Rd Normal Form ->


1, Must be in 2nd Normal Form

2, Transitive functional dependency of any non prime attribute on any super key must be removed

                OR

there should not be any transitive dependency



EmpId , EmpName,  EmpZip, EmpState, EmpCity, EmpDistrict

--------------------------------------------------------

1001, John,   28005,  UP, Agra, D1

1002, Ajith,   22208,  TN,       Chennai, D2

1006,   Laura,    282007, TN,       Chennai, D3

1101,   Lilly,    292008, UK,       Massorie, D4

1201,   Ram,      22999,  HR,       Karnal,    D5

1203,   Sudha,   22999,  HR,       Karnal,     D5



Super Key = {EmpId}, {EmpId,EmpName}, {EmpId,EmpName,EmpZip}


Candidate-Key = EmpId


Non-Prime Attribute -  All attribute except EmpId


here EmpState, EmpCity, EmpDistrict transitively dependent on super key EmpId


Non-Prime Attribute (EmpState, EmpCity, EmpDistrict) are transitively dependent 

on super key which is EmpId, this violates the 3rd Normal Form.


EmpId->EmpZip->EmpState


Solution:


Emp Table

EmpId, EmpName

1001, John

1002, Ajith

...


Address Table

Empzip , EmpState, EmpId

28005, UP,         

...

22999, HR,        1201

22999, HR,   1203



For more Database concepts, Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel 

Click DataPundit

Comments

Post a Comment

Popular posts from this blog

Abinitio Interview Question # 1 - Write Multiple Files in Abinitio

Image
 Question :  I get records of various region in huge volume ,  we want to write the records in region specific files.   How to achieve that? View the explanation in the below video: Answer: -WMF WMF: filename::get_filename(in)= begin let string("") l_filename=string_substring(in.data.region); filename::string_concat($AI_SERIAL,"/",l_filename.dat); end; out::reformat(in)= begin out::in.data; end; OUTPUT DML-: DML record string(int) filename; string(integer(4)) file_contents; end; RMF: filename::get_filename(in)= begin filename::string_concat($AI_SERIAL,"/",in.filename); end; out::reformat(read,filename,in)= begin out.file_contents::read; out.filename::filename; end; Please view my youtube channel  DataPundit
Read more
Image
  Metaprogramming in Abinitio 1. How to create output DML dynamically based in INPUT DML 2. How to create intermediate transform( XFR) dynamically based on INPUT DML      Please See my you tube video for explanation : PART 1 PART 2 For Example, let us say, we have an Input DML as below-: cat input.dml record string(",") name; decimal(",") age; decimal(",") salary; string(",") aadharid; date("yyyy-mm-dd")(",") joiningdate; string(",") mobileno=NULL; string(",") dlno=NULL; string(",") passportnumber=NULL; end; (1.1) prepare a new DML based on input DML i.e. add fields, remove fields, modify type Graph based parameters: ===================================================================== input_dml=$DIR/input.dml  [interpretation = constant] input_dml_info =$[read_type(input_dml)]   [interpretation = pdl] /* Define functions in AB_DML_DEFS */ AB_DML_DEFS= $[ out:: dynamic_dml_gen(input_dml_info)= beg
Read more

Next In Sequence in ABinitio | How next_in_sequence() works in MFS

Image
https://youtu.be/JdDDpjZcv-4 next_in_sequence() working in Abinitio: Definition -      within a single process(component) , this gives successive number.   next_in_sequence()>1 i.e. all records whose occurance is 2 and more in the file.      for example dataset below:    employeeName ram shyam ravi hanuman shekhar seeta geeta shayama vidushi vishakha If we process by condition next_in_sequence()>1 then the output will be [first record will be removed] shyam ravi hanuman shekhar seeta geeta shayama vidushi vishakha 2. next_in_sequence() with reformat  - rely -  FieldA 1a 2a 3a 4a 5a 6a 7a Example 1 (serial processing)--> Reformat   count=2      select:   next_in_sequence()<900      Transofom1:   out:reformat(in)=   begin   out.*::in.*;   out.Seq-No::next_in_sequence();   end;      Transform2:   out:reformat(in)=   begin   out.*::in.*;   out.Seq-No::next_in_sequence();   end;      Output_indexes()=   begin   out::if(next_in_sequence()%2==0) [vector 0,1] else [vector 0,1];   e
Read more