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
Amin Omi, profile picture
Uploaded byAmin Omi
PPTX, PDF583 views

Database Normalisation

The document discusses database normalization. It defines normalization as a process that organizes data into tables to eliminate redundancies and anomalies like inconsistent data changes. The document then covers various normal forms including 1st, 2nd, 3rd normal form and Boyce-Codd normal form. It provides examples of relations and how they can be normalized to remove functional dependencies and anomalies to satisfy each normal form's rules.

Data & Analyticsā—¦
Related topics:
Database Managementā€¢
Downloaded 37 times
1 / 14
2 / 14
3 / 14
4 / 14
5 / 14
6 / 14
7 / 14
8 / 14
9 / 14
10 / 14
11 / 14
12 / 14
13 / 14
14 / 14
Normalisation Prepared by Md. Zahid Hasan
Normalisation?? ā€¢ Database Normalisation is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi- step process that puts data into tabular form by removing duplicated data from the relation tables. Normalization is used for mainly two purpose, ā€¢ Eliminating reduntant(useless) data. ā€¢ Ensuring data dependencies make sense i.e data is logically stored.
Normalisation?? If a database design is not perfect, it may contain anomalies, which are like a bad dream for any database administrator. Managing a database with anomalies is next to impossible. ā€¢ Update anomalies āˆ’ If data items are scattered and are not linked to each other properly, then it could lead to strange situations. For example, when we try to update one data item having its copies scattered over several places, a few instances get updated properly while a few others are left with old values. Such instances leave the database in an inconsistent state. ā€¢ Deletion anomalies āˆ’ We tried to delete a record, but parts of it was left undeleted because of unawareness, the data is also saved somewhere else. ā€¢ Insert anomalies āˆ’ We tried to insert data in a record that does not exist at all. ā€¢ Normalization is a method to remove all these anomalies and bring the database to a consistent state.
Types of Normalisation ā€¢ 1st Normal Form ā€¢ 2nd Normal Form ā€¢ 3rd Normal Form ā€¢ Boyce Code Normal Form ā€¢ 4th Normal Form
First Normal Form ā€¢ First Normal Form is defined in the definition of relations (tables) itself. This rule defines that all the attributes in a relation must have atomic domains. The values in an atomic domain are indivisible units.
First Normal Form ā€¢ We re-arrange the relation (table) as below, to convert it to First Normal Form. ā€¢ Each attribute must contain only a single value from its pre-defined domain.
Second Normal Form Before we learn about the second normal form, we need to understand the following āˆ’ ā€¢ Prime attribute āˆ’ An attribute, which is a part of the prime-key, is known as a prime attribute. ā€¢ Non-prime attribute āˆ’ An attribute, which is not a part of the prime-key, is said to be a non-prime attribute. ā€¢ If we follow second normal form, then every non-prime attribute should be fully functionally dependent on prime key attribute. That is, if X ā†’ A holds, then there should not be any proper subset Y of X, for which Y ā†’ A also holds true.
Second Normal Form We see here in Student_Project relation that the prime key attributes are Stu_ID and Proj_ID. According to the rule, non-key attributes, i.e. Stu_Name and Proj_Name must be dependent upon both and not on any of the prime key attribute individually. But we find that Stu_Name can be identified by Stu_ID and Proj_Name can be identified by Proj_ID independently. This is called partial dependency, which is not allowed in Second Normal Form.
Second Normal Form We broke the relation in two as depicted in the above picture. So there exists no partial dependency.
Third Normal Form ā€¢ For a relation to be in Third Normal Form, it must be in Second Normal form and the following must satisfy āˆ’ ā€¢ No non-prime attribute is transitively dependent on prime key attribute. ā€¢ For any non-trivial functional dependency, X ā†’ A, then either āˆ’ ā€“ X is a superkey or, ā€“ A is prime attribute.
Third Normal Form We find that in the above Student_detail relation, Stu_ID is the key and only prime key attribute. We find that City can be identified by Stu_ID as well as Zip itself. Neither Zip is a superkey nor is City a prime attribute. Additionally, Stu_ID ā†’ Zip ā†’ City, so there exists transitive dependency.
Third Normal Form To bring this relation into third normal form, we break the relation into two relations as follows āˆ’
Boyce-Codd Normal Form Boyce-Codd Normal Form (BCNF) is an extension of Third Normal Form on strict terms. BCNF states that āˆ’ ā€¢ For any non-trivial functional dependency, X ā†’ A, X must be a super-key. ā€¢ In the Last slide image, Stu_ID is the super-key in the relation Student_Detail and Zip is the super-key in the relation ZipCodes. So, Stu_ID ā†’ Stu_Name, Zip and Zip ā†’ City Which confirms that both the relations are in BCNF.
Thanks To All