Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts

Sunday, 14 July 2013

Join and Union

Difference between Join and Union


Join
By using joins, you can retrieve data from two or more tables based on logical relationships between the tables.
Union
The UNION set operator is used for combining data from two tables which have columns with the same data type. When a UNION is performed the data from both tables will be collected in a single column having the same data type.

Database Constraint

Database Constraint is user-defined structures that express the behaviors of column. It is used to protect wrong entry. There are five types of database constraints. Such

  1. Primary Key Constraint
  2. Foreign Key Constraint
  3. Unique Key Constraint
  4. Not null Constraint
  5. Check Constraint

Primary Key Constraint
A column value will be unique among all the rows in a table. It does not allow any null value. Therefore, a primary key constraint has the behaviors of both Not Null and Unique Key Constraints.

Foreign Key Constraint
We can’t insert a foreign key value which is not present in the primary key column of that foreign key.

Saturday, 15 June 2013

Database Normalization With Example

Description of Normalization



Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. 

Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database. 

What is an "inconsistent dependency"? While it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee's salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access because the path to find the data may be missing or broken. 

There are a few rules for database normalization. Each rule is called a "normal form." If the first rule is observed, the database is said to be in "first normal form." If the first three rules are observed, the database is considered to be in "third normal form." Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications. 
As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tables and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization, make sure that your application anticipates any problems that could occur, such as redundant data and inconsistent dependencies. 

The following descriptions include examples. 

First Normal Form

  • Eliminate repeating groups in individual tables.
  • Create a separate table for each set of related data.
  • Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2. 

What happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.

Second Normal Form

  • Create separate tables for sets of values that apply to multiple records.
  • Relate these tables with a foreign key.
Records should not depend on anything other than a table's primary key (a compound key, if necessary). For example, consider a customer's address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer's address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

Third Normal Form

  • Eliminate fields that do not depend on the key.
Values in a record that are not part of that record's key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table. 

For example, in an Employee Recruitment table, a candidate's university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key. 

EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. In theory, normalization is worth pursing. However, many small tables may degrade performance or exceed open file and memory capacities. 

It may be more feasible to apply third normal form only to data that changes frequently. If some dependent fields remain, design your application to require the user to verify all related fields when any one is changed.

Other Normalization Forms

Fourth normal form, also called Boyce Codd Normal Form (BCNF), and fifth normal form do exist, but are rarely considered in practical design. Disregarding these rules may result in less than perfect database design, but should not affect functionality.

Normalizing an Example Table

These steps demonstrate the process of normalizing a fictitious student table.
  1. Unnormalized table:

    Student#AdvisorAdv-RoomClass1Class2Class3
    1022Jones412101-07143-01159-02
    4123Smith216201-01211-02214-01
  2. First Normal Form: No Repeating Groups

    Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble.

    Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:

    Student#AdvisorAdv-RoomClass#
    1022Jones412101-07
    1022Jones412143-01
    1022Jones412159-02
    4123Smith216201-01
    4123Smith216211-02
    4123Smith216214-01
  3. Second Normal Form: Eliminate Redundant Data

    Note the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.

    The following two tables demonstrate second normal form:

    Students:

    Student#AdvisorAdv-Room
    1022Jones412
    4123Smith216


    Registration:

    Student#Class#
    1022101-07
    1022143-01
    1022159-02
    4123201-01
    4123211-02
    4123214-01
  4. Third Normal Form: Eliminate Data Not Dependent On Key

    In the last example, Adv-Room (the advisor's office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:

    Students:

    Student#Advisor
    1022Jones
    4123Smith


    Faculty:

    NameRoomDept
    Jones41242
    Smith21642

Friday, 14 June 2013

Difference between inner join and equi join and natural join

Inner Join 
This is the most used join in the SQL. this join returns only those records/rows that match/exists in both the database tables.
Inner Join Example

SELECT * FROM tableEmp JOIN tableDept ON tableEmp.DeptID = tableDept.DeptID;

Inner Join Result
tableEmp.Name
tableEmp.DeptID
tableDept.Name
tableDept.DeptID
Abc
1
Dept1
1
Xyz
2
Dept1
2
Pqr
2
Dept1
2
Jkl
3
Dept1
3

Equi Join
Equi join is a special type of join in which we use only equalDept1y operator. Hence, when you make a query for join using equalDept1y operator then that join query comes under Equi join.
Equi Join Example

SELECT * FROM tableEmp JOIN tableDept ON tableEmp.DeptID = tableDept.DeptID;
--Using Caluse is supported in SQL Server
--Oracle and MySQL Query
SELECT * FROM tableEmp INNER JOIN tableDept USING(DeptID)


Equi Join Result
tableEmp.Name
tableEmp.DeptID
tableDept.Name
tableDept.DeptID
Abc
1
Dept1
1
Xyz
2
Dept1
2
Pqr
2
Dept1
2
Jkl
3
Dept1
3

Natural Join
Natural join is a type of equi join which occurs implicDept1ly by comparing all the Jkle names columns in both tables. The join result have only one column for each pair of equally named columns.
Natural Join Example

--Run in Oracle and MySQL
 SELECT * FROM tableEmp NATURAL JOIN tableDept
Natural Join Result
DeptID
tableEmp.Name
tableDept.Name
1
Abc
Dept1
2
Xyz
Dept1
2
Pqr
Dept1
3
Jkl
Dept1

Stored Procedure and Function in SQL

Basic Difference

  1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .
  3. Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..
  4. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

  1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
  2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
  3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
  4. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
  5. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
  6. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
  7. We can go for Transaction Management in Procedure whereas we can't go in Function.

Sunday, 9 June 2013

Basic SQL Syntax

Data Definition Language

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
1. CREATE DATABASE
The CREATE DATABASE Statement is used to create database.
Syntax:
CREATE DATABASE database_name
If We create database called testdb. Then use following statement
CREATE DATABASE testdb
2. CREATE TABLE
The CREATE TABLE Statement is used to create table.
Syntax:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
If We create table called EMPLOYEE. Then use following statement
CREATE TABLE EMPLOYEE
(
EMP_CODE int,
EMP_NAME varchar(100),
EMP_ADDRESS varchar(500)
)
3. ALTER TABLE
The ALTER TABLE Statement is used to add,modify or delete column in existing table.
-Add Column
Syntax:
ALTER TABLE table_name
ADD column_name datatype
If We add column in EMPLOYEE table called EMP_SAL. Then use following statement
ALTER TABLE EMPLOYEE ADD EMP_SAL int
-Delete Column
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name
If We delete EMP_SAL column in EMPLOYEE table. Then use following statement
ALTER TABLE EMPLOYEE DROP COLUMN EMP_SAL
-Modify Column
Syntax:
ALTER TABLE table_name
MODIFY column_name datatype
If We modify EMP_NAME column size 100 to 200 in EMPLOYEE table. Then use following statement
ALTER TABLE EMPLOYEE MODIFY EMP_NAME varchar(200)
4. DROP DATABASE
The DROP DATABASE Statement is used to delete database.
Syntax:
DROP DATABASE database_name
If We delete database called testdb. Then use following statement
DROP DATABASE testdb
5. DROP TABLE
The DROP TABLE Statement is used to delete table.
Syntax:
DROP TABLE table_name
If We delete table called EMPLOYEE. Then use following statement
DROP TABLE EMPLOYEE
 

Data Manipulation Language

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
1. SELECT STATEMENT
The SELECT statement is used to select data from a database.
Syntax:
SELECT column_name1,column_name2,.. FROM table_name;
and
SELECT * FROM table_name;
Below the record is EMPLOYEE TABLE
EMP_CODEEMP_NAMEEMP_ADDRESS
1AJAYDELHI
2VIJAYNOIDA
3SANJAYLUCKNOW
4RAMESHKANPUR
5PANKAJPUNE
If We select EMP_CODE and EMP_NAME in EMPLOYEE table. Then use following statement
SELECT EMP_CODE,EMP_NAME FROM EMPLOYEE
If We select all column in EMPLOYEE table. Then use following statement
SELECT * FROM EMPLOYEE
2. INSERT STATEMENT
The INSERT statement is used to insert new data from a database.
Syntax:
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
If We inserT a new record in EMPLOYEE table. Then use following statement
INSERT INTO EMPLOYEE(EMP_CODE,EMP_NAME,EMP_ADDRESS) VALUES('EMP001','AJAY KUMAR','NEW DELHI')
3. UPDATE STATEMENT
The UPDATE statement is used to update existing data from a database.
Syntax:
UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;
If We update employee name AJAY KUMAR to AJAY SINGH where employee code EMP001 in EMPLOYEE table. Then use following statement
UPDATE EMPLOYEE SET EMP_NAME='AJAY SINGH' WHERE EMP_CODE='EMP001'
4. DELETE STATEMENT
The DELETE statement is used to delete data from a database.
Syntax:
DELETE FROM table_name WHERE some_column=some_value;
If We delete employee where employee code is EMP001 in EMPLOYEE table. Then use following statement
DELETE FROM EMPLOYEE WHERE EMP_CODE='EMP001'

Friday, 24 May 2013

Difference between Primary Key & Foreign Key


Difference between Primary Key & Foreign Key
Primary Key
Foreign Key
Primary key uniquely identify a record in the table.
Foreign key is a field in the table that is primary key in another table.
Primary Key can't accept null values.
Foreign key can accept multiple null value.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
We can have only one Primary key in a table.
We can have more than one foreign key in a table.