‘Attributes & Functional Dependency Rohan Singh R ATTRIBUTES IN SOL: ATTRIBUTES : Are the properties which defines the entity 1 ea ane record uniquely from a table is known as key attribute Ex: Phone_No , mail id, aadhar, pan , sation , paseport , di , bank a/c Ree Ex: Name , age, gender , dob the key attributes an attribute is chosen to Seth main ata eat arta mil fm he we ae as prime key attribute Ex: Phone No.x Prime key attribute : Among the key attributes an attribute is chosen to be the main attribute to identify a record uniquely from the table is known 1s prime key attribute. Ex: Phone_No . Non-scima ory atte Al the ey stwibuies other than Prime key strbutes Ex : mail_id , asdhar , pan , ration , passport , dl , bank a/c ‘Coppel mnctans: > i contitaten tse we mete aon Mey aera which to identify record uniquely from. > Campeche oy seuss choaner ca wentey tcl Ex: (name , age , dob , address ) Super key attribute - itis a set of all key attributes . Ex: (Phone No , mail_id, aadhar, pan , ration , passport , dl , bank a/c } Forcign key attribute ; It is an attributes which behaves as an attribute of another entity to represent the relationship . Ex: DaoEUINCTIONAL DEPENDENCY : “THERE EXISTS A DEPENECY SUCH THAT AN ATTRIBUTE IN A RELATION DETERMINES ANOTHER ATTRIBUTE ". HD EMP-(EID,ENAME) 1 A EID -> ENAME - me> EID >(ENAME,, SAL ,DOB) 2. PARTIAL FUNCTIONAL DEPENDENCY %ae eae ee ee CUSTOMER -( CNAME , ADDRESS , MAIL_ID , PHONE_NO) ( PHONE_NO , MAIL_ID ) — Composite key attribute PHONE_NO - > CNAME , ADDRESS MAIL ID -> CNAME , ADDRESS Customer CNAME ADDRESS MAILID PHONE NO Smith Mysore smith@[Link] Miller Bangalore Scott 1001 t Mangalore scott@[Link]which is intern determined by a key attribute X-(A,B,C,D) A->B A* KEYATTRIBUTE A->D A=B D->C B=C A=c CUSTOMER -( CID , CNAME , PINCODE , STATE) CID CNAME CID->PINCODE PINCODE > STATE Redundancy : The repetition of unwanted data ismown as redundancy . _———E— lll EE ll OeNORMALIZATION ‘What is Normalization? 5 Ler aca table into smaller tables in order to remove wdetueartetieaae Sy taeitying tots fonction doponencioe known as Normalization ." Or "The of i table into smaller table is known ‘process ae as Or “Reducing a table to its Normal Form is known as Normalization."Levels of Normal From. 1. First Normal Form ( INF) 2. Second Normal Form (2NF) 3. Third Normal Form (3NF ) Many Table / entity is reduced » then the table is said to be normalized.L ~ No duplicates records - Multivalued data should not be present .2 + Table should be in INF ~ Table should not have Partial Functional Dependency . EMPLOYEE -( EID, ENAME , SAL , DEPTNO , DNAME , LOC ) Eid cname sal Tas B Eid - ename ,sal Deptno - dname , loc (Bid , deptno ) > (Ename , Sal , Dname , Loc )= ( Bid, deptno ) > ( Ename , Sal , Dname , Loc ) composite key attribute results in PFD RI -( EID ,ENAME, SAL) R2-(DEPTNO , DNAME , LOC) RL R 1 ork io (mina- Table should be in 2NF. ~ Table should not have Transitive Functional Dependency . Employee - (EID , Ename , Sal eS cite on mee PINCODE > STATE RI- (id ,ename , cOOUNTRYCNAME PINCODE CITY STATE 1 Smith 510001 Bangalore Kamataka 2 Miller 510002. Mumbai Maha 3 Scott 510001 Bangalore Karnataka 4 ‘Adams 510001 Bangalore Karnataka 3 Scott $1002 | Mumbai Maha Customer : ( cid, cname , pincode , city , state ) (PK)Cid- Cname Pincode - City Sie~Qeu se: RI - (Cid, Cname , Pincode ) R2 - ( Pincode , City , State ) RI