New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

Normalization in Oracle Database

by: Ismail Yousuf

Normalization in Oracle Database CSC 271

Ismail Yousuf
Comsats Institute of Information Technology
GPA 3.7

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Normalization in Database very essential slides
Database Management
Amjad Usman
Class Notes
Database, Oracle, SQL, Computer Science
25 ?




Popular in Database Management

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.

Similar to CSC 271 at Comsats Institute of Information Technology

Popular in ComputerScienence


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 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


Buy Material

Are you sure you want to buy this material for

25 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


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'

Why people love StudySoup

Steve Martinelli UC Los Angeles

"There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."

Anthony Lee UC Santa Barbara

"I bought an awesome study guide, which helped me get an A in my Math 34B class this quarter!"

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

Parker Thompson 500 Startups

"It's a great way for students to improve their educational experience and it seemed like a product that everybody wants, so all the people participating are winning."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.