Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
aklima

New User

Joined: 25 Nov 2005
Posts: 18
Location: uae

 Posted: Tue Dec 13, 2005 7:50 am    Post subject: what are the different normalisations 1.Is it possible to improve performance with the use of Clustering Index? Explain. 2. what are dif normalisations? 3.Explain Denormalization with example.

iknow

Active User

Joined: 22 Aug 2005
Posts: 413

Posted: Tue Dec 13, 2005 8:34 am    Post subject: Re: some queries

Hi aklima,

 Quote: 1.Is it possible to improve performance with the use of Clustering Index? Explain.

For a clustered index DB2 maintains rows in the same sequence as the columns in the index for as long as there is free space. DB2 can then process that table in that order efficiently.

Yes, you can improve the perfromance that too it depends on the situations.

Nested loop Join is efficient when clustering index on join columns of inner table.

NOTE

Please check my attachment on clustering index.

 Quote: 2. what are dif normalisations?

Normalization is a design procedure for representing data in tabular format. The five normal forms are progressive rules to represent the data with minimal redundancy.

Normalization

Record design process that identifies and avoids data problems and redundancy
Specifies the fields and the primary key
Normalization analyzes record structure through four stages
Unnormalized records
First normal form (1NF) records
Second normal form (2NF) records
Third normal form (3NF) records
Normalization

First normal form
Unnormalized records contain a repeating group
A repeating group refers to a single record that has multiple values in a particular field
Example: multiple product numbers in a single order record
A 1NF record cannot have a repeating group
Normalization

First normal form
To convert an unnormalized record to 1NF, the repeating group must be removed
Expand the primary key to include the primary key of the repeating group
The new primary key is a combination of the original primary key and the key of the repeating group
Instead of a single record with a repeating group, the result is many records, one for each instance of the repeating group
Normalization

Second normal form (2NF)
To be in second normal form, a record must be in 1 NF, and all nonkey fields must be functionally dependent on the entire primary key - not just part of it
Functional dependency means that a value in one field determines a value in another field
If the primary key is a single field, then any record in 1 NF is automatically in 2 NF
In 2NF, all nonkey fields are functionally dependent on the entire primary key
Normalization

Second normal form (2NF)
To convert a 1NF record to 2NF
Create a new record design for each field (or combination of fields) in the primary key
Place remaining fields with the appropriate record
The result will be several records, each with a primary key field (or combination of fields) that determines the values of the other fields in that record
Normalization

Third normal form (3NF)
To be in 3NF, a record must be in 2NF and no nonkey field is functionally dependent on another nonkey field
In 3NF, all nonkey fields are functionally dependent on the primary key, the entire key, and nothing but the key
Normalization

Third normal form (3NF)
To convert a 2NF record to 3NF
Remove all nonkey fields that depend on another nonkey field and place them in a new record that has the determining field as a primary key
Normalization

A normalization example
Identify the entities
STUDENT
COURSE
Identify the relationships
Students take one or more courses, and courses have one or more students (M:N)
Normalization

A normalization example
Identify the entities
STUDENT
COURSE
Identify the relationships
Students take one or more courses, and courses have one or more students (M:N)
Document the unnormalized record
Note the repeating group of courses
Normalization

A normalization example
Convert the unnormalized record to 1 NF
Remove the repeating group
Create a primary key composed of the original primary key (student number) and the primary key of the repeating group (course number)
The result is one record for each instance of the combination primary key
Normalization

A normalization example
Convert the 1 NF record to 2NF
Create a separate record design for each field and combination of fields in the primary key
Place functionally dependent fields with an appropriate primary key
The result is three records instead of one, each with a unique primary key
Now all nonkey fields are dependent on the entire primary key, not just a portion of it
Normalization

A normalization example
Convert the 2NF record to 3NF
The STUDENT record contains a nonkey field (advisor name) that is dependent on another nonkey field (advisor number)
Create a new record with advisor number as the primary key
Remove the dependent nonkey field (advisor name) and include it in the new record
Normalization

A normalization example
Convert the 2NF record to 3NF
The STUDENT record contains a nonkey field (advisor name) that is dependent on another nonkey field (advisor number)
Create a new record with advisor number as the primary key
Remove the dependent nonkey field (advisor name) and include it in the new record
Now all nonkey fields are dependent on the entire primary key, and nothing but the key

It reduces redundancy in the data.

 Quote: 3.Explain Denormalization with example.

Denormalization

In short, to guarantee the data integrity, i.e., "All or None".

Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.

Hope this helps.
parikshit123

Active User

Joined: 01 Jul 2005
Posts: 269
Location: India

 Posted: Tue Dec 13, 2005 5:53 pm    Post subject: Hi aklima, Was it really necessary to cram all the above three topics in one? I think it would have been much better if they would have seperate topics.
 All times are GMT + 6 Hours
 Page 1 of 1
 Search our Forum:

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us