Author: sqlschool

3rd Normal Form (3NF)

Third normal form:

In second normal form, all columns in the table rely on the primary key and the table has a singular purpose

There could be relationships between the columns… dependencies could lurk within these columns called transitive dependence.

Transitive dependence means that a value of a column/field within a table relies on a another column in that same table, but this is facilitated through another column between them.

A table is in third normal form when the following conditions are met:

  • It is in second normal form.
  • All nonprimary fields are dependent on the primary key.

Again, transitive dependence means dependence between columns of the same table. Think of ArtistNationality, Artist, and Artwork. The values for ArtistNationality and Artist depend on the Artwork; once you figure out the Artwork, you know the Artist/ArtistNationality. But ArtistNationality depends on the value from Artist: This is a transitive dependence.

In order to get to third normal form, we need to make sure all columns are only dependent upon the primary key. That means we have to get the country code out of the artist table. In the following example, we’ve added country name to the table to further highlight the issue with normalizing

It’s fine that the country code exists in the artist table, but having the country name breaks the 3rd normal form rule, since we can’t get the country name without the code; we can’t get the code without the artist ID. This is by nature the definition of transitive dependence.

Let’s look at some examples that will use the fictional music database as a subject.

we’ll create another table called countries, move the country code and country name to this table. Country code becomes the primary key in the countries table, but is retained in the artist table as a foreign key.

Second Normal Form(2nd) (Eliminating Redundant Data)

A relation is in second normal form if it is in 1NF and every non key attribute is fully functionally dependent on the primary key.

It is in first normal form
All non-key attributes are fully functional dependent on the primary key

The First Normal form deals with the atomicity whereas the Second Normal Form deals with the relationship between the composite key columns and non-key columns.

To achieve the next progressive level your table should satisfy the requirement of First Normal Form then move towards the Second Normal Form.

 Let see the example from above 1st Normal From

ITEM COLORS PRICES TAX
Pen Red 3.0 0.40
Pen Blue 3.0 0.40
Scale Red 3.0 0.40
Scale Yellow 3.0 0.40
Bag Blue 120.0 12.52
Bag Black 120.0 12.52

Table is not in Second Normal Form because the price and tax depends on the item, but not color.

ITEM COLORS
Pen Red
Pen Blue
Scale Red
Scale Yellow
Bag Blue
Bag Black

 

ITEM PRICE TAX
Pen 3.0 0.40
Scale 3.0 0.40
Bag 120.0 12.52

 

First Normal Form (1NF)

First Normal Form (1NF)
A database table is said to be in 1NF if it contains no repeating fields/columns.

• Make a separate table for each set of related attributes and give each table a primary key. In simple words we can say it as
• There are no repeating or duplicate fields.
• Each cell contains only a single value.
• Each record is unique and identified by primary key

Example:

ITEM COLORS PRICES TAX
Pen Red,Blue 3.0 0.40
Scale Red,Yellow 3.0 0.40
Pen Red,Blue 3.0 0.40
Bag Blue,Black 120.0 12.52

This table is not in first normal form because:

• A. There are multiple fields in color lab.

• B. Records are repeating (Duplicate records) or no primary key. First Normal Form (1NF)

ITEM COLORS PRICES TAX
Pen Red 3.0 0.40
Pen Blue 3.0 0.40
Scale Red 3.0 0.40
Scale Yellow 3.0 0.40
Bag Blue 120.0 12.52
Bag Black 120.0 12.52

This table is now in first normal form.

What is Normalization? 

What is Normalization? 

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.

It divides larger tables to smaller tables and link them using relationships.

In this tutorial, you will learn-

The inventor of the relational model Edgar Codd proposed the theory of normalization with the introduction of First Normal Form and he continued to extend theory with Second and Third Normal Form. Later he joined with Raymond F. Boyce  to develop the theory of Boyce-Codd Normal Form.

Theory of Data Normalization in Sql is still being developed further. For example there are discussions even on 6th Normal Form. But in most practical applications normalization achieves its best in 3rd Normal Form. The evolution of Normalization theories is illustrated below

1st Normal Form >> 2nd Normal Form >> 3rd Normal Form >>4th Normal Form >>  5th Normal Form >>6th Normal Form

Database Normalization Examples –

Assume a video library maintains a database of movies rented out. Without any normalization all information is stored in one table as shown below.

Further We shall discuss on different normal forms in detailed:

 

 

 

 

 

 

How to use INNER JOIN in SQL Server?

The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax: The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2…
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Image Representation:

The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.

Sample Two Table Data:

FOODS:

 ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID
1 Bread PCS 25
2 Sliced Bread PCS 16
3 Roasted Bread PCS 32
4 Garlic Bread PCS 48
5 Cheese Bread PCS 18
6 Fruit Bread PCS 22
7 Milk Bread PCS 28

Company:

COMPANY_ID COMPANY_NAME COMPANY_CITY
25 SUBWAY IRLAND
16 BURGER KING UNITED STATES
32 KFC UK
18 ROLLZONE AUSTRALIA
28 DOMINOS INDIA

Example

Here is an example of inner join in SQL between two tables.

To join item name, item unit columns from foods table and company name, company city columns from company table, with the following condition –

1. company_id of foods and company_id company table must be same, the following SQL statement can be used :

SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM foods
INNER JOIN company
ON foods.company_id =company.company_id;

Example of SQL INNER JOIN using JOIN keyword

To get item name, item unit columns from foods table and company name, company city columns from company table, after joining these mentioned tables, with the following condition –

1. company id of foods and company id of company table must be same,

the following SQL statement can be used :
SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM foods
JOIN company
ON foods.company_id =company.company_id;

How to delete a record in a table using SQL?

The SQL DELETE Query is used to delete the existing records from a table.

You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.

Syntax:
The basic syntax of DELETE query with WHERE clause is as follows:

DELETE FROM table_name
WHERE [condition];

Example:
Consider the CUSTOMERS table having the following records:
+—-+———-+—–+———–+———-+
| ID | NAME | AGE | ADDRESS | SALARY |
+—-+———-+—–+———–+———-+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |

Following is an example, which would DELETE a customer, whose ID is 6:

DELETE FROM CUSTOMERS
WHERE ID = 6;

If you want to DELETE all the records from CUSTOMERS table, you do not need to use WHERE clause and DELETE query would be as follows:

DELETE FROM CUSTOMERS;

SQL Server next version CTP 1.4

Microsoft is excited to announce a new preview for the next version of SQL Server (SQL Server v.Next). Community Technology Preview (CTP) 1.4 is available on both Windows and Linux. In this preview, they added the ability to schedule jobs using SQL Server Agent on Linux. We can try the preview in your choice of development and test environments now: www.sqlserveronlinux.com.

Key CTP 1.4 enhancements
The primary enhancement to SQL Server v.Next on Linux in this release is the ability to schedule jobs using SQL Server Agent. This functionality helps administrators automate maintenance jobs and other tasks, or run them in response to an event. Some SQL Server Agent functionality is not yet enabled for SQL Server on Linux. To learn more and see sample SQL Server Agent jobs, you can read detailed blog titled “SQL Server on Linux: Running scheduled jobs with SQL Server Agent” or attend an Engineering Town Hall about “SQL Server Agent and Full Text Search in SQL Server on Linux.”

The mssql-server-linux container image on Docker Hub now includes the sqlcmd and bcp command line utilities to make it easier to create and attach databases and automate other actions when working with containers. For additional detail on CTP 1.4, please visit What’s New in SQL Server v.Next, Release Notes and Linux
documentation.

In addition, SQL Server Analysis Services and SQL Server Reporting Services developer tools now support Visual Studio 2017. They are available for installation from the Visual Studio Marketplace providing the option for automatic updates going forward.

 

Get SQL Server v.Next CTP 1.4 today!
Try the preview of the next release of SQL Server today! Get started with the preview of SQL Server with our developer tutorials that show you how to install and use SQL Server v.Next on macOS, Docker, Windows, and Linux and quickly build an app in a programming language of your choice.

Difference Between SQL & SQL Server

SQL Server is an RDBMS just like oracle,DB2 from Microsoft

Structured Query Language (SQL), pronounced “sequel”, is a language that provides an interface to relational database systems. It is an open, vendor-independentIt was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS

SQL is the basic ANSI standard for accessing data in a relational database. When you see “MSSQL” it is referring to Microsoft SQL Server, which is the entire database architecture and not a language. T-SQL is the proprietary form of SQL used by Microsoft SQL Server. It includes special functions like cast, convert, date(), etc. that are not part of the ANSI standard.

You will also see things like plSQL, which is Oracle’s version of SQL, and there are others as well (mySQL has its own version, for example, and Microsoft Access uses Jet SQL.)

It is important to note the the ANSI standard for SQL has different releases (for example, 92 or 99, representing the year it was released.). Different database engines will advertise themselves as “mostly ANSI-92” compliant or “fully ANSI-99” compliant, etc, and any exceptions will usually be documented.

So although “SQL is SQL”, every engine uses its own “flavor” of it, and you do have to do a little reading on the particular platform before you just dive in.

A further note – the SQL extensions, like T-SQL, are generally considered full-fledged programming languages, complete with looping, if/then, case statements, etc. SQL itself is limited to simply querying and updating data and is not considered a true programming language.

For More Information Please Click On This Link : SQL & SQL Server, T-SQL

SQL is the BEST language for Job Market: 2016

SQL is the TOP technology stream for Job market in 2016.

SQL School Training Institute is an ISO Certified Organization and Microsoft Learning Partner offering real-time, practical trainings exclusively on SQL Server Technologies including SQL Server Development (SQL Dev), SQL Database Administration (SQL DBA), Microsoft Business Intelligence and Datawarehousing (MSBI / DWH) Training. Versions: SQL Server 2016, SQL Server 2014. Register Today for free demo

 

Ref:

http://www.codingdojo.com/blog/9-most-in-demand-programming-languages-of-2016/

 

Reach us today for a free demo!

+91-9666440801 (India)
+91-9542916779 (India)
+91-(0)40 64577244 (India)
+1-510-400-4845 (USA/Canada)