Database Design And Normalization
Database normalization, or simply normalisation, is the process of organizing the columns (attributes) and tables (relations) of a relational database to minimize data redundancy.
Normalization involves decomposing a table into less redundant (and smaller) tables without losing information, and then linking the data back together by defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys.
We will demonstrate the process of normalisation (to 3NF) by use of an example. Normalisation is a bottom-up technique for database design, normally based on an existing system (which may be paper-based). We start by analysing the documentation, eg reports, screen layouts from that system. We will begin with the Project Management Report, which describes projects being worked upon by employees. This report is to be 'normalised'. Each of the first four normalisation steps is explained.
Commonly asked interview questions on Constructors
1.What is Normalisation (or Normalization)?
Answer :Normalization is basically to design a database schema such that duplicate and redundant data is avoided. If some piece of data is duplicated several places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption.
There is a number of normalization levels from 1. normal form through 5. normal form. Each normal form describes how to get rid of some specific problem, usually related to redundancy.
2.What is De-normalization?
Answer :As the name indicates, de-normalization is the reverse process of normalization. It’s the controlled introduction of redundancy in to the database design.
It helps improve the query performance as the number of joins could be reduced.
3.What is a Stored Procedure?
Answer :Stored Procedure is a group of sql statements that has been created once and stored in server database. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance.
4.What is a Trigger?
Answer :Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
5.What is the advantage of normalization?
Answer :Normalization is the process used to reduce the unnecessary repetetion of data i.e, redundant data.It is performed on the data which is redundant and makes the data in a normalized format.It is of step-by-step processIstNotmal FormIIndNormal formIIIrdNormalformIVth Normalform or BoyceCodd Normal formBy perofmring this we will get the data in the Normalized formati.,e from DBMS to RDBMS.
1. Head First Java