what is Normalization means..?
Answer Posted / nitin bisht
NORMALIZATION: The process of structuring data to minimise
duplication and inconsistencies. The process usually
involves breaking down a single table into two or more
tables and defining relationships between those tables.
Normalisation is usually done in stages, with each stage
applying more rigourous rules to the types of information
which can be stored in a table. While full adherence to
normalisation principles increases the efficiency of a
particular database, the process can become so esoteric that
you need a professional to create and understand the table
design. Most people, when creating a database, don't need to
go beyond the third level of normalisation, called third
normal form. And there's no need to know the terminology:
simply applying the principles is sufficient.
The first three levels in normalising a database are:
First Normal Form (1NF): There should be no repeating groups
in a table.
For example, say you have a students table with the
following structure:
student ID
name
date of birth
advisor
advisor's telephone
student
course ID 1
course description 1
course instructor 1
course ID 2
course description 2
course instructor 2
The repeating course fields are in conflict with first
normal form.To fix the problems created by such repeating
fields, you should place the course information in a
separate course table, and then provide a linking field
(most likely student ID) between the students table and the
course table.
Second Normal Form (2NF): No non-key fields may depend on a
portion of the primary key.
For example, say we create a course table with the structure:
student ID
course ID
course description
course instructor
We can create a unique primary key by combining student ID +
course ID (student ID is not unique in itself, as one
student may take multiple courses; similarly, course ID is
not unique in itself as many students may take the same
course; however, each student will only be taking a
particular course once at any one time, so the combination
of student ID + course ID gives us a unique primary key).
Now, in 2NF, no non-key fields (course description, course
instructor) may depend on a portion of the primary key.
That, however, is exactly what we have here: the course
instructor and course description are the same for any
course, regardless of the student taking the course.
To fix this and put the database in second normal form, we
create a third table, so our database structure now looks
like this (with key fields in italics):
Student table
student ID
name
date of birth
advisor
advisor's telephone
Student courses table
student ID
course ID
Courses table
course ID
course description
course instructor
Third Normal From (3FN): No fields may depend on other
non-key fields. In other words, each field in a record
should contain information about the entity that is defined
by the primary key.
In our students table, for example, each field should
provide information about the particular student referred to
by the key field, student ID. That certainly applies to the
student's name and date of birth. But the advisor's name and
telephone doesn't change depending on the student. So, to
put this database in third normal form, we need to place the
advisor's information in a separate table:
Student table
student ID
name
date of birth
advisor ID
Student courses table
student ID
course ID
Courses table
course ID
course description
course instructor
Advisor table
advisor ID
advisor name
advisor telephone
Is This Answer Correct ? | 19 Yes | 0 No |
Post New Answer View All Answers
How to do Stored procedure testing? Who does this testing (Developer or tester)?
What do you mean by database?
Does facebook use graph database?
Design an ETL process( batch job/script)from source to starting that u will not be dependent on the source in the near future?please elobarate details on what are things to consider.if you'd like to do a diagram that will be great.please answer it
How many types of locks are present in a database?
Explain phantom deadlock?
london school of science and technology has a software development unit,known as lsst solutions,which is responsible for developing and implementing software throughout the collegejames tristan keeps a record of all student details in the college including student id,name,and start date and phone number.There are many courses offered by LSST. For example MBA, MSc, BSc, BABS, DF, English etc. Each course id identified by course id, description, duration. Each course has many semesters. Each semester takes place during a particular term. Semester details and term details are required to be stored in the database. Each course has many subjects and same subject may be offered in many courses. For each subject many details such as Subject code, name, result and attendance are required to be stored. Every subject has mainly two assessments; one assignment and one exam. Assignment is worth of 30% and exam 70% respectively. You have been appointed as a database designer. Your job is to provide ERD with all possible entities and relationships. Note: Feel free to make necessary assumptions 3: Assignment Tasks. 1. Create an ERD for the above problem 2. Translate the ERD to Relational Data model (RDM) 3. Implement the RDM by creating the tables 4. Populate the database with your own data. 5. SQL Queries for the following tasks a. List all student details in each course along with course details b. Count and display number of students in each course. c. List student name, course name, assessment description, marks for all students.
How to do Data size testing? Who does this testing (Developer or tester)?
What is odm in database?
Why merge statement is used inside the database?
Explain alternate key?
How does database replication work?
Can you tell any two advantages of stored procedures?
can u combine pipeline parallelism and partition parallalism? how and whan?
What is the database url?