SQL Server Mentalist

Hi Everyone

We really got good response for this series named “Laymen to SQL Developer” as i am getting various mail / comments feedbacks for my lot of readers.

This will be my last post for this assignment named “Basics of DBMS”, after this in the upcoming assignment we are moving into practice world of SQL

So lets continuing from my last post of current assignment , you can visit if you have miss any

• BISQL # 83 – Laymen to SQL Developer # 7 – Assignment #1 – Part #6 – Storage manager, Query Processing ,Transaction Management and Internals
• BISQL # 77 – Laymen to SQL Developer # 6 – Assignment #1 – Part #5 – Database Schemes ,Database Instances and Some Basic Definitions
• BISQL # 76 – Laymen to SQL Developer # 5 – Assignment #1 – Part #4 – What is Data Independence and All information related to Data Independence in DBMS
• BISQL # 75– Laymen to SQL Developer # 4 – Assignment #1 – Part #3 – What is Data Abstraction and All information related to Data Abstraction in DBMS
• BISQL # 72 – Laymen to SQL Developer # 3 – Assignment #1 – Part #2 – Why DBMS or What are Limitations of file processing systems
• BISQL # 69 – Laymen to SQL Developer # 1 – Introduction to brand New SQL Server Zero to Basics Series
• BISQL # 70 – Laymen to SQL Developer # 2 – Assignment #1 – Part #1–Introduction to Data and database Why Database exist ?

In this article we are going to cover

• Why / What is Codd’s Rule ?
• Listing 12 Distinct artefacts of Codd’s Rule .
• Explanation for 12 Distinct artefacts of Codd’s Rule.
• Why / What is Normalization ?
• What are Normal Forms
• Explanation for some of Normal Forms

Now lets go through each every point for more understanding and more details :

Before that one point to be noted that “Do not get feared from technical names and content , read freely and ask any time online if you have any doubt on my blogs “

Why / What is Codd’s Rule ?

Codd’s Rules that are 12 in number specify what a database must support in order to be relational. The rules were defined by Edgar F Codd in a paper published in 1985.

Hence, they are known as Codd’s Rules. For example, data are represented only one way; as values within columns and rows.

Every value can be assessed by providing table name, column name and key, and all data are unique.

Codd produced these rules as part of a personal campaign to prevent his vision of the relational database being diluted, as database vendors scrambled in the early 1980s to repackage existing products with a relational veneer.

Rule 12 was particularly designed to counter such a positioning.

We call it 12 rule but there are actually 13 as Rule are start with 0 and ends with 12 🙂

Listing 13 Distinct artefacts of Codd’s Rule .

1. Rule (0):Relational and Management system
2. Rule 1: The information rule:
3. Rule 2: The guaranteed access rule:
4. Rule 3: Systematic treatment of null values:
5. Rule 4: Active online catalog based on the relational model:
6. Rule 5: The comprehensive data sublanguage rule:
7. Rule 6: The view updating rule:
8. Rule 7: High-level insert, update, and delete:
9. Rule 8: Physical data independence:
10. Rule 9: Logical data independence:
11. Rule 10: Integrity independence:
12. Rule 11: Distribution independence:
13. Rule 12: The nonsubversion rule:

Explanation for 13 Distinct artefacts of Codd’s Rule.

Rule (0):Relational and Management system

For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database

Rule 1: The information rule:

All information in a relational database including table names, column names are represented by values in tables. This simple view of data speeds design and learning. User productivity is improved since knowledge of only one language is necessary to access all data such as description of the table and attribute definitions, integrity constraints. Action can be taken when the constraints are violated. Access to data can be restricted. All these information are also stored in tables.

Rule 2: The guaranteed access rule:

Every piece of data in a relational database can be accessed by using combination of a table name, a primary key value that identifies the row and column name which identified a cell. User productivity is improved since there is no need to resort to using physical pointers addresses. Provides data independence. Possible to retrieve each individual piece of data stored in a relational database by specifying the name of the table in which it is stored, the column and primary key which identified the cell in which it is stored.

Rule 3: Systematic treatment of null values:

Every piece of data in a relational database can be accessed by using combination of a table name, a primary key value that identifies the row and column name which identified a cell. User productivity is improved since there is no need to resort to using physical pointers addresses. Provides data independence. Possible to retrieve each individual piece of data stored in a relational database by specifying the name of the table in which it is stored, the column and primary key which identified the cell in which it is stored.

3 Systematic Treatment of Nulls Rule: The RDBMS handles records that have unknown or inapplicable values in a pre-defined fashion. Also, the RDBMS distinguishes between zeros, blanks and nulls in the records hand handles such values in a consistent manner that produces correct answers, comparisons and calculations. Through the set of rules for handling nulls, users can distinguish results of the queries that involve nulls, zeros and blanks. Even though the rule doesn’t specify what should be done in the case of nulls it specifies that there should be a consistent policy in the treatment of nulls.

Rule 4: Active online catalog based on the relational model:

The description of a database and in its contents are database tables and therefore can be queried on-line via the data manipulation language. The database administrator’s productivity is improved since the changes and additions to the catalog can be done with the same commands that are used to access any other table. All queries and reports can also be done as any other table.

Rule 5: The comprehensive data sublanguage rule:

A RDBMS may support several languages. But at least one of them should allow user to do all of the following: define tables and views, query and update the data, set integrity constraints, set authorizations and define transactions. User productivity is improved since there is just one approach that can be used for all database operations. In a multi-user environment the user does not have to worry about the data integrity an such things, which will be taken care by the system. Also, only users with proper authorization will be able to access data.

Rule 6: The view updating rule:

Any view that is theoretically updateable can be updated using the RDBMS. Data consistency is ensured since the changes made in the view are transmitted to the base table and vice-versa.

Rule 7: High-level insert, update, and delete:

The RDBMS supports insertions, updation and deletion at a table level. The performance is improved since the commands act on a set of records rather than one record at a time.

Rule 8: Physical data independence:

The execution of adhoc requests and application programs is not affected by changes in the physical data access and storage methods. Database administrators can make changes to the physical access and storage method which improve performance and do not require changes in the application programs or requests. Here the user specified what he wants an need not worry about how the data is obtained.

Rule 9: Logical data independence:

Logical changes in tables and views such adding/deleting columns or changing fields lengths need not necessitate modifications in the programs or in the format of adhoc requests. The database can change and grow to reflect changes in reality without requiring the user intervention or changes in the applications. For example, adding attribute or column to the base table should not disrupt the programs or the interactive command that have no use for the new attribute.

Rule 10: Integrity independence:

Like table/view definition, integrity constraints are stored in the on-line catalog and can therefore be changed without necessitating changes in the application programs. Integrity constraints specific to a particular RDB must be definable in the relational data sub-language and storable in the catalog. At least the Entity integrity and referential integrity must be supported.

Rule 11: Distribution independence:

Application programs and adhoc requests are not affected by change in the distribution of physical data. Improved systems reliability since application programs will work even if the programs and data are moved in different sites.

Rule 12: The nonsubversion rule:

If the RDBMS has a language that accesses the information of a record at a time, this language should not be used to bypass the integrity constraints. This is necessary for data integrity.

Why / What is Normalization ?

Normalization is the process of efficiently organizing data in a database.

There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table).

Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

In Short : Normalization is The process of decomposing tables to eliminate data redundancy is called Normalization.

Normal forms in space representation where space in nothing but data

What are Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized.

These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you’ll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won’t be discussed in this article.
Before we begin our discussion of the normal forms, it’s important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it’s extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies. That said, let’s explore the normal forms.

Explanation for some of Normal Forms

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

Eliminate duplicative columns from the same table.Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

Meet all the requirements of the first normal form. Remove subsets of data that apply to multiple rows of a table and place them in separate tables. Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

Meet all the requirements of the second normal form.Remove columns that are not dependent upon the primary key.

There are additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables.

We will see more on particle side in my upcoming post in this series .

Hope you will like this post on Codd’s Rule and Normalization.

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where today’s links are

Link Resource Website