Advertisements

Archive

Archive for December, 2009

SQL Basics – Working with Foreign Key (FK) constraints

December 27, 2009 3 comments

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables, as per MSDN BoL.
 

–> Foreign Key (FK) Constraint

1. We can create a FK by defining a FOREIGN KEY constraint while Creating or modifying (Altering) a table.

2. We can define a FK in a table that points to a PK or UK in another table.
   – Like Employee & Department, shown in video-demo below.

3. A FK constraint can reference columns within the same table, these are called self-referencing tables. For example: Employee & Manager Relationship.
 

–> Referential Integrity:

1. The FK constraint enforces Referential Integrity by not allowing values in the Child table outside form the domain of the Parent table.

2. This also disallows any changes to the Parent table, like deleting any row or modifying PK value in the Parent table.
 

–> Check the video on Foreign Key:

FK Constraint
 

–> Department and Employee table FK relationship:

FK Constraint
 

–> SQL Code used in above video:


-- 1. Parent table:

CREATE TABLE [dbo].[Department](
	 [DeptID]	int IDENTITY (101, 1) PRIMARY KEY
	,[DeptName]	nvarchar(100)
	,[isActive]	bit DEFAULT(1)
)
GO

INSERT INTO [dbo].[Department] ([DeptName])
SELECT 'HR'
UNION ALL
SELECT 'Finance'
UNION ALL
SELECT 'Admin'
UNION ALL
SELECT 'IT'

select * from [dbo].[Department]
GO


-- 2. Child Table:

-- Method #1.a : with Column inline
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL FOREIGN KEY REFERENCES Department(DeptID)
	,[isActive]		bit DEFAULT(1)
)
GO

-- Method #1.b : with Column inline & Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY REFERENCES Department(DeptID)
	,[isActive]		bit DEFAULT(1)
)
GO

-- Method #2.a : with Explicit
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
		FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
)
GO

-- Method #2.b : with Explicit & Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
		CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
)
GO

-- Method #3 : with ALTER Table Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
)
GO

-- SSMS or ALTER Table stmt:

-- Generated by SSMS - Table Designer:
ALTER TABLE dbo.Employee ADD CONSTRAINT
	FK_Employee_Department FOREIGN KEY
	(
	DeptID
	) REFERENCES dbo.Department
	(
	DeptID
	)

-- OR --

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
GO


INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Manoj Pandey', 'M', 101)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Deepak B', 'M', 102)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Jhon B', 'M', 103)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Mariya Y', 'F', 104)

select * from [dbo].[Employee]
GO


-- REFERENTIAL INTEGRITY:

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Mary J', 'F', 105)
--Msg 547, Level 16, State 0, Line 115
--The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EmployeeID_DeptID". The conflict occurred in database "TestManDB", table "dbo.Department", column 'DeptID'.
--The statement has been terminated.

DELETE FROM [dbo].[Department]
WHERE DeptID = 102
--Msg 547, Level 16, State 0, Line 118
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeID_DeptID". The conflict occurred in database "TestManDB", table "dbo.Employee", column 'DeptID'.
--The statement has been terminated.

DROP TABLE [dbo].[Department]
--Msg 3726, Level 16, State 1, Line 121
--Could not drop object 'dbo.Department' because it is referenced by a FOREIGN KEY constraint.

GO


-- Allowed updates:

select * from [dbo].[Department]

UPDATE [dbo].[Department]
SET DeptName = 'Human Resource'
WHERE DeptID = 101

select * from [dbo].[Department]

GO


-- Final Cleanup:

DROP TABLE [dbo].[Employee]
GO
DROP TABLE [dbo].[Department]
GO

Check Primary Key & Unique Key constraints

To know about more on Constraints and their types check this blog post.


Advertisements

C++ Graphics – 21 – Analog Clock code/program showing current time

December 21, 2009 Leave a comment

Analog Clock program by using Trigonometry (SIN & COS) functions:

Cpp 21 Clock

#include <stdio.h>
#include <conio.h>
#include <graphics.h>
#include <math.h>
#include <dos.h>

#define PI 3.14

void getTime(int *h, int *m, int *s ){
	struct time t;
	gettime(&t);

	gotoxy(36,18);
	printf("%2d:%02d:%02d.%02d\n",
		 t.ti_hour,t.ti_min,t.ti_sec,t.ti_hund);

	*h = t.ti_hour;
	*m = t.ti_min;
	*s = t.ti_sec;
	}

void main(){
	int gd=DETECT, gm;
	initgraph(&gd, &gm, "\\tc");

	int xs, ys, xm, ym, xh, yh, h, m, s;

	while(!kbhit()){
		cleardevice();

		getTime(&h, &m, &s);

		settextstyle(1,0,0);
		setcolor(WHITE);
		outtextxy(300,15,"12");
		outtextxy(315,425,"6");
		outtextxy(105,220,"9");
		outtextxy(520,220,"3");
		settextstyle(5,0,0);

		setcolor(GREEN);
		outtextxy(275,300,"CLOCK");
		settextstyle(2 ,0,0);

		setcolor(LIGHTRED);
		outtextxy(310,295,"Manoj");

		xh = cos((h*30 + m / 2) * PI / 180 - PI / 2) * 150 + 320;
		yh = sin((h*30 + m / 2) * PI / 180 - PI / 2) * 150 + 240;
		xm = cos(m * PI / 30 - PI / 2) * 180 + 320;
		ym = sin(m * PI / 30 - PI / 2) * 180 + 240;
		xs = cos(s * PI / 30 - PI / 2) * 210 + 320;
		ys = sin(s * PI / 30 - PI / 2) * 210 + 240;

		setcolor(LIGHTBLUE);
		circle(320,240,220);

		setcolor(LIGHTRED);
		line(320,240,xh,yh);

		setcolor(LIGHTGREEN);
		line(320,240,xm,ym);

		setcolor(YELLOW);
		line(320,240,xs,ys);

		sleep(1);
		}
	}

C++ Graphics – 20 – SCALING of LINE, TRIANGLE and RECTANGLE w.r.t a Point

December 20, 2009 Leave a comment

SCALING of LINE, TRIANGLE and RECTANGLE w.r.t a Point.

#include <math.h>
#include <stdio.h>
#include <conio.h>
#include <stdlib.h>
#include <graphics.h>

const int MAX = 5;
const double PI = 3.14;

void GetCords(float mat1[][MAX], float mat2[][MAX], int n){
	int i, j;
	for(i=0; i<n; i++){
		printf("\n Enter Coordinate no %d:- \n", i+1);
		for(j=0; j<2; j++){
			scanf("%f", &mat1[j][i]);
			}
		}

	for(i=0; i<n; i++){
		mat2[0][i] = mat1[0][i];
		mat1[0][i] = 320 + mat1[0][i];
		mat2[1][i] = mat1[1][i];
		mat1[1][i] = 240 - mat1[1][i];
		}

	setcolor(GREEN);
	line(0, 240, 640, 240);
	outtextxy(630, 245, "x");
	line(320, 0, 320, 480);
	outtextxy(310, 0, "y");
	outtextxy(280, 245, "(0,0)");

	setcolor(YELLOW);
	moveto(mat1[0][0], mat1[1][0]);
	for(i=0; i<n; i++){
			lineto(mat1[0][i], mat1[1][i]);
			moveto(mat1[0][i], mat1[1][i]);
			}
	lineto(mat1[0][0], mat1[1][0]);

	}

void pointScale(float mat2[][MAX], int n){
	int i, j, k;
	float tmat[MAX][MAX], tot[MAX][MAX];
	int sx, sy;
	int px, py;

	// Get a Point.
	px = mat2[0][0];
	py = mat2[1][0];

	printf("\n Enter the Scale value of x: ");
	scanf("%d", &sx);
	printf("\n Enter the Scale value of y: ");
	scanf("%d", &sy);

	// Inv Translation to Origin.
	for(i=0; i<n; i++){
		mat2[0][i] -= px;
		mat2[1][i] -= py;
		}

	// Scaling
	tmat[0][0] = sx;
	tmat[0][1] = 0;
	tmat[1][0] = 0;
	tmat[1][1] = sy;

	for(i=0; i<2; i++){
		for(j=0; j<n; j++){
			tot[i][j] = 0;
			for(k=0; k<2; k++){
				tot[i][j] = tot[i][j] + (tmat[i][k] * mat2[k][j]);
				}
			}
		}

	// Trnaslate from the Origin.
	for(i=0; i<n; i++){
		tot[0][i] += px;
		tot[1][i] += py;
		}

	for(i=0; i<n; i++){
		tot[0][i] = 320 + tot[0][i];
		tot[1][i] = 240 - tot[1][i];
		}

	setcolor(LIGHTRED);
	moveto(tot[0][0], tot[1][0]);
	for(i=0; i<n; i++){
			lineto(tot[0][i], tot[1][i]);
			moveto(tot[0][i], tot[1][i]);
			}
	lineto(tot[0][0], tot[1][0]);
	}

void main(){
	int gd=DETECT, gm;
	initgraph(&gd, &gm,"\\tc");
	float mat1[MAX][MAX], mat2[MAX][MAX];
	int ch, n;
	while(1){
		clrscr();
		cleardevice();
		printf("\n SCALING w.r.t a point");
		printf("\n ~~~~~~~~~~~~~~~~~~~~~~\n");
		printf("\n 1 -> LINE.");
		printf("\n 2 -> TRIANGLE.");
		printf("\n 3 -> RECTANGLE");
		printf("\n 4 -> Exit.");
		printf("\n Enter your choice: ");
		scanf("%d", &ch);
		clrscr();
		cleardevice();
		switch(ch){
			case 1:		// LINE.
				n=2;
				break;
			case 2:		// TRIANGLE.
				n=3;
				break;
			case 3:		// RECTANGLE.
				n=4;
				break;
			default:
				gotoxy(10, 10);
				printf(" Dev. By: Manoj Pandey.");
				gotoxy(15, 15);
				printf("MCA 3d Sem.");
				getch();
				closegraph();
				exit(1);
			} // end of switch.
		GetCords(mat1, mat2, n);	// Input of Coordinates.
		pointScale(mat2, n);	// Scaling and Displaying.
		getch();
		} // end of while.
	} // end of main.

C++ Graphics – 19 – ROTATION of LINE, TRIANGLE and RECTANGLE w.r.t a Point

December 19, 2009 Leave a comment

ROTATION of LINE, TRIANGLE and RECTANGLE w.r.t a Point.

#include <math.h>
#include <stdio.h>
#include <conio.h>
#include <stdlib.h>
#include <graphics.h>

const int MAX = 5;
const double PI = 3.14;

void GetCords(float mat1[][MAX], float mat2[][MAX], int n){
	int i, j;
	for(i=0; i<n; i++){
		printf("\n Enter Coordinate no %d:- \n", i+1);
		for(j=0; j<2; j++){
			scanf("%f", &mat1[j][i]);
			}
		}

	for(i=0; i<n; i++){
		mat2[0][i] = mat1[0][i];
		mat1[0][i] = 320 + mat1[0][i];
		mat2[1][i] = mat1[1][i];
		mat1[1][i] = 240 - mat1[1][i];
		}

	setcolor(GREEN);
	line(0, 240, 640, 240);
	outtextxy(630, 245, "x");
	line(320, 0, 320, 480);
	outtextxy(310, 0, "y");
	outtextxy(280, 245, "(0,0)");

	setcolor(YELLOW);
	moveto(mat1[0][0], mat1[1][0]);
	for(i=0; i<n; i++){
			lineto(mat1[0][i], mat1[1][i]);
			moveto(mat1[0][i], mat1[1][i]);
			}
	lineto(mat1[0][0], mat1[1][0]);

	}

void pointRotate(float mat2[][MAX], int n){
	int ang, i, j, k;
	float tmat[MAX][MAX], tot[MAX][MAX];
	float rad;
	int px, py;

	// Get a Point.
	px = mat2[0][0];
	py = mat2[1][0];

	printf("\n Enter the Angle (in Degrees):- \n");
	scanf("%d", &ang);

	// Inv Translation to Origin.
	for(i=0; i<n; i++){
		mat2[0][i] -= px;
		mat2[1][i] -= py;
		}

	// Rotation.
	rad = (ang * PI) / 180;

	tmat[0][0] = cos(rad);
	tmat[0][1] = -sin(rad);
	tmat[1][0] = sin(rad);
	tmat[1][1] = cos(rad);

	for(i=0; i<2; i++){
		for(j=0; j<n; j++){
			tot[i][j] = 0;
			for(k=0; k<2; k++){
				tot[i][j] = tot[i][j] + (tmat[i][k] * mat2[k][j]);
				}
			}
		}

	// Trnaslate from the Origin.
	for(i=0; i<n; i++){
		tot[0][i] += px;
		tot[1][i] += py;
		}

	for(i=0; i<n; i++){
		tot[0][i] = 320 + tot[0][i];
		tot[1][i] = 240 - tot[1][i];
		}

	setcolor(LIGHTRED);
	moveto(tot[0][0], tot[1][0]);
	for(i=0; i<n; i++){
			lineto(tot[0][i], tot[1][i]);
			moveto(tot[0][i], tot[1][i]);
			}
	lineto(tot[0][0], tot[1][0]);
	}

void main(){
	int gd=DETECT, gm;
	initgraph(&gd, &gm,"\\tc");
	float mat1[MAX][MAX], mat2[MAX][MAX];
	int ch, n;
	while(1){

		clrscr();
		cleardevice();
		printf("\n ROTATION w.r.t a point");
		printf("\n ~~~~~~~~~~~~~~~~~~~~~~\n");
		printf("\n 1 -> LINE.");
		printf("\n 2 -> TRIANGLE.");
		printf("\n 3 -> RECTANGLE");
		printf("\n 4 -> Exit.");
		printf("\n Enter your choice: ");
		scanf("%d", &ch);
		clrscr();
		cleardevice();
		switch(ch){
			case 1:		// LINE.
				n=2;
				break;
			case 2:		// TRIANGLE.
				n=3;
				break;
			case 3:		// RECTANGLE.
				n=4;
				break;
			default:
				gotoxy(10, 10);
				printf(" Dev. By: Manoj Pandey.");
				gotoxy(15, 15);
				printf("MCA 3d Sem.");
				getch();
				closegraph();
				exit(1);
			} // end of switch.
		GetCords(mat1, mat2, n);	// Input of Coordinates.
		pointRotate(mat2, n);	// Rotating and Displaying.
		getch();
		} // end of while.
	} // end of main.

C++ Graphics – 18 – SHEARING of LINE, TRIANGLE and RECTANGLE w.r.t xy-Axis

December 18, 2009 Leave a comment

SHEARING of LINE, TRIANGLE and RECTANGLE w.r.t xy-Axis.

#include <math.h>
#include <stdio.h>
#include <conio.h>
#include <stdlib.h>
#include <graphics.h>

const int MAX = 5;
const double PI = 3.14;

void GetCords(float mat1[][MAX], float mat2[][MAX], int n){
	int i, j;
	for(i=0; i<n; i++){
		printf("\n Enter Coordinate no %d:- \n", i+1);
		for(j=0; j<2; j++){
			scanf("%f", &mat1[j][i]);
			}
		}

	for(i=0; i<n; i++){
		mat2[0][i] = mat1[0][i];
		mat1[0][i] = 320 + mat1[0][i];
		mat2[1][i] = mat1[1][i];
		mat1[1][i] = 240 - mat1[1][i];
		}

	setcolor(GREEN);
	line(0, 240, 640, 240);
	outtextxy(630, 245, "x");
	line(320, 0, 320, 480);
	outtextxy(310, 0, "y");
	outtextxy(280, 245, "(0,0)");

	setcolor(YELLOW);
	moveto(mat1[0][0], mat1[1][0]);
	for(i=0; i<n; i++){
			lineto(mat1[0][i], mat1[1][i]);
			moveto(mat1[0][i], mat1[1][i]);
			}
	lineto(mat1[0][0], mat1[1][0]);

	}

void xyShearing(float mat2[][MAX], int n){
	int i, j, k;
	float tmat[MAX][MAX], tot[MAX][MAX];
	int sh_x, sh_y;

	printf("\n Enter the Searing value w.r.t x: ");
	scanf("%d", &sh_x);
	printf("\n Enter the Searing value w.r.t y: ");
	scanf("%d", &sh_y);

	tmat[0][0] = 1;
	tmat[0][1] = sh_x;
	tmat[1][0] = sh_y;
	tmat[1][1] = 1;

	for(i=0; i<2; i++){
		for(j=0; j<n; j++){
			tot[i][j] = 0;
			for(k=0; k<2; k++){
				tot[i][j] = tot[i][j] + (tmat[i][k] * mat2[k][j]);
				}
			}
		}

	for(i=0; i<n; i++){
		tot[0][i] = 320 + tot[0][i];
		tot[1][i] = 240 - tot[1][i];
		}

	setcolor(LIGHTRED);
	moveto(tot[0][0], tot[1][0]);
	for(i=0; i<n; i++){
			lineto(tot[0][i], tot[1][i]);
			moveto(tot[0][i], tot[1][i]);
			}
	lineto(tot[0][0], tot[1][0]);
	}

void main(){
	int gd=DETECT, gm;
	initgraph(&gd, &gm,"\\tc");
	float mat1[MAX][MAX], mat2[MAX][MAX];
	int ch, n;
	while(1){

		clrscr();
		cleardevice();
		printf("\n SHEARING w.r.t xy-Axis");
		printf("\n ~~~~~~~~~~~~~~~~~~~~~~\n");
		printf("\n 1 -> LINE.");
		printf("\n 2 -> TRIANGLE.");
		printf("\n 3 -> RECTANGLE");
		printf("\n 4 -> Exit.");
		printf("\n Enter your choice: ");
		scanf("%d", &ch);
		clrscr();
		cleardevice();
		switch(ch){
			case 1:		// LINE.
				n=2;
				break;
			case 2:		// TRIANGLE.
				n=3;
				break;
			case 3:		// RECTANGLE.
				n=4;
				break;
			default:
				gotoxy(10, 10);
				printf(" Dev. By: Manoj Pandey.");
				gotoxy(15, 15);
				printf("MCA 3d Sem.");
				getch();
				closegraph();
				exit(1);
			} // end of switch.
		GetCords(mat1, mat2, n);	// Input of Coordinates.
		xyShearing(mat2, n);	// Shearing and Displaying.
		getch();
		} // end of while.
	} // end of main.