Normalization in Oracle Database
Normalization in Oracle Database CSC 271
Comsats Institute of Information Technology
Popular in Database Management
verified elite notetaker
Popular in ComputerScienence
This 23 page Class Notes was uploaded by Ismail Yousuf on Saturday January 9, 2016. The Class Notes belongs to CSC 271 at Comsats Institute of Information Technology taught by Amjad Usman in Winter 2016. Since its upload, it has received 35 views. For similar materials see Database Management in ComputerScienence at Comsats Institute of Information Technology.
Reviews for Normalization in Oracle Database
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 01/09/16
CSC271 Database Systems Lecture 27 Normalization Instructor : Amjad Usman firstname.lastname@example.org Tuesday, December 15, 2015 Outline In this lesson,you will learn about: Well Structured Relations Functional Dependencies Normalization Steps Example 3 COMSATS Institute of Information Technology, Wah Campus Student Relation regno sname program cgpa CourseTitle Datecompleted 100 Umar BSE 3.4 DLD 24-01-2009 100 Umar BSE 3.4 ITCP 06-05-2009 140 Ali BEE 3.6 Engineering Design 06-05-2009 110 Usman BCS 3.1 Database systems 15-02-2010 110 Usman BCS 3.1 DLD 24-01-2009 190 Akbar MSCS 0.0 150 Fatima MSSE 2.9 Net Security 06-01-2009 150 Fatima MSSE 2.9 Advance database 06-01-2009 Is this a relation? Yes, Unique rows and no multi-valued attributes What’s the primary key? Composite Key: Reg_Num, Course_Title Is this relationWell Structured? 4 Well-Structured Relations A relation is said to be well structured if and only if it contains minimal data redundancy and allows users to insert,delete,and update rows without causing data inconsistencies (i.e.anomalies) Goal is to avoid inconsistency / anomaly in a relation 5 Types of anomalies InsertionAnomaly adding new rows forces user to create duplicate data DeletionAnomaly deleting rows may cause a loss of data that would be needed for other future rows ModificationAnomaly changing data in a row forces changes to other rows because of duplication General rule of thumb: A table should not pertain to more than one entity type 6 Insertion Anomalies regno sname program cgpa courseTitle dateCompleted 300 Ahmad MSCS 3.0 ????????????????? ??????????????? Composite primary key (regno,courseTitle) Insert a new student (300,‘Ahmad’,’MSCS’,3.00) User will must supply at-least the values for primary key fields because primary keys cannot be null Problem:User can’t enter a new student if he has not taken any course InsertionAnomaly User is unable to enter a new student data without supplying the course data 7 Deletion Anomaly regno sname program cgpa courseTitle dateCompleted 140 Ali BEE 3.6 Engineering Design 06-05-2009 150 Fatima MCSE 2.9 Advance database 06-01-2009 If we delete student having regno 140,what happens? We lose information not only about the student (regno=140) But also about the course (Eng.Design) that he has completed DeletionAnomaly By deleting one record when it results in the loss of other pieces of information 8 Modification Anomaly regno sname program cgpa courseTitle dateCompleted 150 Fatima MCSE 2.9 Network Security 06-01-2009 150 Fatima MCSE 2.9 Advance database 06-01-2009 If the student having regno150 changes the program,this must be recorded in both the rows of the table otherwise the data will be inconsistent – modification anomaly 9 Anomalies in the Tables Why do these anomalies exist? Because there are two themes (entity types) in this one relation.This results in data duplication and an unnecessary dependency between the entities These anomalies indicate that STUDENT is not a well- structured relation We should use normalization theory to divide STUDENT into multiple relations For example one for STUDENT core information and the other one for student’s COURSE information to keep track of the course details. 10 Data Normalization The process of decomposing relations with anomalies to produce smaller,well-structured relations The formal process for deciding which attributes should be assigned to which entities Advantages Reduces data redundancies Helps eliminate data anomalies Produces controlled redundancies to link tables 11 Steps in Normalization 12 First Normal Form (1NF) Each regular entity type in an ER diagram is transformed into a relation The name given to the relation is generally the same as the entity type Each simple attribute of the type becomes an attribute of the relation The identifier of the entity type becomes the primary key of the corresponding relation No multivalued attributes,every attribute value is atomic 13 Example Tables with no multi-valued attributes and unique rows are in 1st normal form This is valid relation in 1st normal form,but not a well-structured one 14 Anomalies in this Table Insertion – if new product is ordered for order 1007 of existing customer,customer data must be re-entered, causing duplication Deletion – if we delete the DiningT able from Order 1006,we lose information concerning this item's finish and price Update – changing the price of product ID 4 requires update in several records Why do these anomalies exist? Because there are multiple themes (entity types) in one relation.This results in duplication and an unnecessary dependency between the entities 15 Functional Dependencies and Keys Functional Dependency:The value of one attribute (the determinant) determines the value of another attribute. For any relation R,attribute B is functionally dependent on attribute A if,for every valid instance of A,that value of A uniquely determines the value of B. Candidate Key: An attribute,or combination of attributes,that uniquely identifies a row in a relation. One of the candidate keys will become the primary key E.g.perhaps there is both credit card number and NIC# in a table…in this case both are candidate keys Each non-key field should be functionally dependent on every candidate key 16 Second Normal Form (2NF) A relation is in 2NF if it is in 1NF plus every non-key attribute is fully functionally dependent on the ENTIRE primary key. Every non-key attribute must be defined by the entire key, NOT by only part of the key. Every relation whose primary key consists of just one attribute is automatically in Second Normal Form. Every non-key attribute is functionally depended on the full set of PK attributes. No partial functional dependencies 17 Functional Dependency Diagram - Invoice Customer_ID Customer_Name,Customer_Address Order_ID Order_Date,Customer_ID,Customer_Name,Customer_Address Product_ID Product_Description,Product_Finish,Unit_Price Order_ID,Product_ID Order_Quantity Therefore,NOT in 2 nd Normal Form 18 Getting it into Second Normal Form Remove Partial Dependencies by splitting the relation into multiple relations where full dependency is achieved 19 Third Normal Form 2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes) Note:This is called transitive,because the primary key is a determinant for another attribute,which in turn is a determinant for a third Solution:Non-key determinant with transitive dependencies go into a new table;non-key determinant becomes primary key in the new table and stays as foreign key in the old table 20 Getting it into Third Normal Form Transitive dependencies are removed 21 Conclusion Normal Forms (NFs) Description First Normal Form (1NF) No repeating groups (multi-valued attributes) Second Normal Form In First Normal Form (1NF) (2NF) No Partial Dependencies Third Normal Form In Second Normal Form (2NF) (3NF) NoTransitive Dependencies 22 THANK YOU SO MUCH 23 COMSATS Institute of Information Technology, Wah Campus
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'