Logic Data Modeling (Normalization)

Logic Data Modeling

Able to take a list of data elements and some how applaying various methodology to come up the best data base design.
Where Data Come From?
Order Fine

Order Number:- Order Date:- Customer Number:- Customer Name:- Customer Address:-

Item Number Item Name Item Quentity Item Price Item Total

Total

Keeping in mind, it is a physical order form, we are going to make a relational database.
These are the process how we are going to convert our form into a relational databse.
Steps:-

1. Identifay Candidate Key
2. Select The Primery Key
3. Apply Normalization

Entity Relational Diagram (ERD)

ERD shows us how our database going to look like.

1. Identifay Candidate Key

Each fields of the order form is known as data element. The question become when we look for a key which one of them is any or how many of them unikly all identify.
Order number would be the cendidate key, because no other order number has the same value.So order number is the primery key.
So definetly you would have confused, if we choose order number is the cendidate key and the same could be the primery key how .
Example:-in politice there are more then one cendidate for election but only one win the game, so it soes not metter , we have more then one cendidate key, but we have to choose out of cendidate one is the primery key.

2. Select The Primery Key

So

3. Apply Normalization

• 1 NF -- No Repeating Elements, No Repeting Groups
• 2 NF -- No Partial Dependency on part of the primery Key
• 3 NF -- No Non Key Attribute dependent on another non key attribute.

A File in logical model is besically called an Entity,Attributes of a particular entity.
Look at out figure order form and find the key and non key attributes.
Remember non key attributes dependent on key attriutes.So what is these keys lets figure out.

Key Attributes	Non Key Attributes
Order Number	Order Date
	Item Num
	Item Name
	Item Qty
	Item Amount
	Customer Number
	Customer Name
	Customer Address
	Total Amount

In Normalization we go through a Serious of Questions.

1. No Repeting elements?

Ans. yes, we have repeting elements.
Going back to the form, find out do we have repeting elements, yes we have , lets how.

Now we have failed First Noramal Form , This is called integrity problem.

 

Remember When ever you fail a Normal Form, You Must Create A new Entity.
This is the methmetical term of Normalization
Remember when ever you create a new entity we must have a primery key.Concentinate primery key means more then one element come together to form a key, New primery key Concentination form the entity That fail, which is order num.
primery key is order_num_item_num
Now item_num is not a non key attribute now it is a primery key. So the dependent elements must come with it.

Now you have two entity.
2 Nd Normal Form ther will be no partial dependency
partial dependency means, which we have a concentinated key which is two pices (order number) + (item number) is it possible the non key attributes is only dependen on one partion of the entity elements.
Lets proof this,
Suppose your order number is 100 and three item number A,B,C you would have one record in the first file_order called order number 100 and you would have three in.

Order Number + Item Number
100	A
100	B
100	C

to be continued...