Benefits of XML Master Certification Exam

• what is normalization?
• What are the different types of normalization?

So when this question on being asked the interviewer who have already prepared for it start with the history of normalization and end with the geography of normalization but when the next question for which they have not prepared i.e.  apply normalization in real case scenario.

What is Normalization?
Database designed based on ER model may have some amount of inconsistency, ambiguity and redundancy. To resolve these issues some amount of refinement is required. This refinement process is called as Normalization. I know all of you are clear with the definition, let’s go with :

• what is the need of normalization?
• What are the problems we can face if we proceed without normalization?
• What are the advantages of normalization?

Asking question to oneself is the best way to get familiar with all the concepts.

The need of Normalization
I am going to show you one simple E-R model database.

1. Insert Anomaly: We cannot insert prospective course which does not have any registered student or we cannot insert student details that is yet to register for any course.
2. Update Anomaly: if we want to update the course M4’s name we need to do this operation three times. Similarly we may have to update student 1003’s name twice if it changes.
3. Delete Anomaly: if we want to delete a course M4 , in addition to M4 occurs details , other critical details of student also will be deleted. This kind of deletion is harmful to business. Moreover, M4 appears thrice in above table and needs to be deleted thrice.
4. Duplicate Data: Course M4’s data is stored thrice and student 1002’s data stored twice .This redundancy will increase as the number of course offerings increases.

1. Determinant : Attribute X can be defined as determinant if it uniquely defines the value Y in a given relationship or entity .To qualify as determinant attribute need NOT be a key attribute .Usually dependency of attribute is represented as X->Y ,which means attribute X decides attribute Y.

2. Functional Dependency: Yes functional dependency has definition but let’s not care about that. Let’s try to understand the concept by example. Consider the following relation :

• Student#-Student Number
• Course#-Course Number
• CourseName -CourseName
• IName- Name of the instructor who delivered the course
• Room#-Room number which is assigned to respective instructor
• Marks- Scored in Course Course# by student Student #
• Grade –Obtained by student Student# in course Course #
• Student#,Course#  together (called composite attribute) defines EXACTLY ONE value of marks .This can be symbolically represented as

• Course# -> CourseName
• Course#-> IName(Assuming one course is taught by one and only one instructor )
• IName -> Room# (Assuming each instructor has his /her own and non shared room)
• Marks ->Grade

3. Full functional dependency: In above example Marks is fully functional dependent on student#Course#  and not on the sub set of Student#Course# .This means marks cannot be determined either by student # or Course# alone .It can be determined by using Student# and Course# together. Hence Marks is fully functional dependent on student#course#.

4. Partial Dependency: In the above relationship CourseName,IName,Room# are partially dependent on composite attribute Student#Course# because Course# alone can defines the coursename, IName,Room#.

5. Transitive Dependency:  In above example , Room# depends on IName and in turn depends on Course# .Here Room# transitively depends on Course#.

6. Key attributes : In a given relationship R ,if the attribute X uniquely defines all other attributes ,then the attribute X is a key attribute which is nothing but the candidate key.

1. First Normal Form(1NF)

2. Second Normal Form (2NF)

• It is in the first normal form ,and
• No partial dependency exists between non-key attributes and key attributes.

• Student# is key attribute for Student ,
• Course# is key attribute for Course
• Student#Course#  together form the composite key attributes for result relationship.
• Other attributes are non-key attributes.

• StudentName and DateOfBirth depends only on student#.
• CourseName,PreRequisite and DurationInDays depends only on Course#
• DateOfExam depends only on Course#.

• In the first table (STUDENT) ,the key attribute is Student# and all other non-key attributes, StudentName and DateOfBirth are fully functionally dependant on the key attribute.
• In the Second Table (COURSE) , Course# is the key attribute and all the non-key attributes, CourseName, DurationInDays are fully functional dependant on the key attribute.
• In third table (RESULT) Student#Course# together are key attributes and all other non key attributes, Marks  and Grade are fully functional dependant on the key attributes.
• In the fourth Table (EXAM DATE) Course# is the key attribute and the non key attribute ,DateOfExam is fully functionally dependant on the key attribute.

• All attributes are atomic in nature
• No partial dependency exists between the key attributes and non-key attributes
• RESULT table is in 2NF

• Students who score more than or equal to 80 marks are awarded with “A” grade
• Students who score more than or equal to 70 marks up till 79 are awarded with “B” grade
• Students who score more than or equal to 60 marks up till 69 are awarded with “C” grade
• Students who score more than or equal to 50 marks up till 59 are awarded with “D” grade

• We don’t have an option to introduce new grades like A+ ,B- and E
• We need to do multiple updates on the existing record to bring them to new grading definition
• We will not be able to take away “D” grade if we want to.
• 2NF does not take care of all the anomalies and inconsistencies.

3. Third Normal Form (3NF)

• It is in 2NF
• No transitive dependency exists between non-key attributes and key attributes.

• INSERT
• UPDATE
• DELETE