UploadLanguage (EN)Support
BusinessMobileSocial MediaMarketingTechnologyArt & PhotosCareerDesignEducationPresentations & Public SpeakingGovernment & NonprofitHealthcareInternetLawLeadership & ManagementAutomotiveEngineeringSoftwareRecruiting & HRRetailSalesServicesScienceSmall Business & EntrepreneurshipFoodEnvironmentEconomy & FinanceData & AnalyticsInvestor RelationsSportsSpiritualNews & PoliticsTravelSelf ImprovementReal EstateEntertainment & HumorHealth & MedicineDevices & HardwareLifestyle
Upload
Sign in
SA
Uploaded byShriya agrawal
PPTX, PDF5,071 views

Normalization 1 nf,2nf,3nf,bcnf

The document discusses database normalization and different normal forms. It defines normalization as removing redundant data to improve storage efficiency and integrity. It outlines Edgar Codd's introduction of normalization and the first three normal forms he proposed: 1NF, 2NF, 3NF. It also discusses Boyce-Codd Normal Form and defines the differences between 3NF and BCNF. Examples are provided to illustrate the different normal forms.

Education◦
Downloaded 126 times
1 / 22
2 / 22
Most read
3 / 22
4 / 22
5 / 22
6 / 22
7 / 22
8 / 22
9 / 22
10 / 22
11 / 22
12 / 22
13 / 22
14 / 22
15 / 22
16 / 22
17 / 22
18 / 22
Most read
19 / 22
Most read
20 / 22
Index  Normalization and history of norm…  Why we do normalization..  How many normal form..  (1NF) and (2NF) with example…  (3NF) and (BCNF) with example..  Difference between 3NF and BCNF.. --------------------------******----------------------
Normalization Normalization is the process of removing redundant data from tabels into improve storage efficiency and data integrity……… This process takes a relation schema through a series of tests to certify wether it satisfies a certain normal forms………..
History of normalization  Edgar.F codd, the inventor of the relational model,introduced the concept of normalization..  The first normal form(1NF)was proposed by codd in 1970…  Codd went on to define second normal form(2NF) and third normal form (3NF) in 1971. To eliminate some anomalies in (3NF) the stronger version of 3NF is called“BCNF” was proposed by “Boyce and Codd”…
Why we do normalization?  It is the process of analyzing the given relation schema based on their functional dependencies and primary keys to achieve the disiarable properties of.  It is used to minimizing data redundancy.  It is used to minimizing deletion,insertion and updation anomalies.
Sid. s.name c.id c.name f.id f.Name S1 A C1 C F1 X S2 A C1 C F1 X S3 A C1 C F1 X S4 B C2 C++ F2 Y S.id s.name c.id S1 A C1 S2 A C1 S3 A C1 s4 B C2 c.id c.name f.id f.name C1 C F1 X C2 C++ F2 Y
How Many normal form?  Initially codd proposed three normal form, which he called first,second and third normal forms.  A stronger defination of third normal form is called Boyce-codd normal form(BCNF),was proposed later by Boyce-Codd.  Later fourth and fifth normal form were proposed.
First normal form.  A Relation schema R is in 1NF if the domain of all attributes of R is atomic….  A Domain is atomic if element of the domain are considered to be indivitual units…. All value for a given attribute must be of the same type….  compound attribute is not allowed in first normal form…. Each attribute name must be unique …..  What is atomic value?
Person Id Name Phone num. 1 Rajat 7489 9054 7645 2 Megha 9032 Person Id Phone num. Name 1 7489 Rajat 1 9054 Rajat 1 7645 Rajat 2 9032 Megha Multivalued attribute is not allowed in first normal form…….
Person id Name 1 Rajat 2 Megha ………… ……… ………. ………. Person id Phone num. 1 7489 1 9054 1 7645 2 9032
Second normal form  Second normal form (2NF) is a normal form used in database normalization.  2NF was originally organised by E.F.CODD.  A table that is in first normal form(1NF) must meet additional criteria if it is two qualify for second normal form.  Second normal forms state that it should meet all the rules for 1NF and their must be no partial dependences of any of the coloumns on the primary key.
What is partial dependency St.ID Batch Id Name Phn num. email Batch time Start date Batch size faculty 1 b1 amit 7489-- amit4 @----- 4:30pm 6.05.16 20 Rajat 2 b1 amit ----- ----- ----- ----- ----- ----- 3 b2 rajat ----- ----- ----- ----- ----- ----- 4 b2 raman ----- ----- ----- ----- ----- -----
S.Id name email Phn. 1 amit amit4 @ 7489 2 amit ------- ------- 3 ------- ------ ------ 4 ------ ------ ------- B .Id B.tym s.d b.size b1 4:30p 6.05.16 20 b1 -------- -------- ------- b2 ------- --------- ------- b2 ------- -------- -------- S.Id B.id s.d 1 b1 6.05.2016 2 b1 7.05.2016 3 b2 6.05.2016 4 b2 -------------
Third normal forms  Third normal forms(3NF) is a normal form used in database normalization.  (3NF) was originally defined by E.F.CODD…
Third normal form… • Codd’s defination states that a table is in 3NF if and only if both of the following condition should be hold. (i) the relation R (table) is in (2NF)…. (ii) every non prime key attribute of R(Table) is non –transitively dependent on non -prime key attribute of R….. (i.e.)no transitive dependencies….
Rollno. Name Dept. Year Hostel nm. 10 Rajat Physics 1 H1 20 Raman Math 1 H1 30 Rahul Cs. 2 H2 40 Akash IT Dept. 2 H2 50 Megha Commerce 3 H3
Roll no. Name Dept. Year 10 Rajat Physics 1 20 Raman Math 1 30 Rahul Cs. 2 40 Akash IT Dept. 2 50 Megha Commerce 3 Year Hostel nm. 1 H1 1 H1 2 H2 2 H2 3 H3
Boyce codd normal form The official qualification for BCNF are:- • A table is already in 3NF. • Atmost one determinant must be superkey in relation.. Atmost one determinant that are not superkeys are removed to place in another table.
The difference between 3NF and BCNF  The difference between 3NF and BCNF is that for a functional dependency A->B ,3NF allows this dependency in a relation if A is prime key attribute and B is not a cnadidate key.. Whereas BCNF insists that for this dependency to remain in a relation B,must be a candidate.
Table Faculty Faculty id Name Dept Dept head 101 Rajat agrawal Computer sc…. Mrs. pavani.. 102 Akash agrawal Mathematics Kiran avasthi 103 R.Gupta physics D.K.Ranjak ----- ------ ------- ------ Faculty id->Dept, Dept->Depthead Faculty id->Depthead
Faculty Id Name Dept 101 Rajat agrawal Computer sc. 102 Akash agrawal Mathematics 103 R.gupta Physics 104 -------- --------- Dept Depthead Computer sc. Mrs.pavani.. Mathematics Kiran Avasthi Physics R.K.Ranjak ---------- -------------
Normalization 1 nf,2nf,3nf,bcnf