Many To Many Relationship Table Script

I am creating a database for tracking the Cricketers and their types. By types I mean Batsman, Bowler, Wicket Keeper, etc.
The issue is a Cricketer might belong to different type. For Example Kapil Dev is both a Batsman and a Bowler.
Inorder to solve this I should create a third table referred to as Junction Table apart from Cricketer and Type tables which is having reference to both Crickter and Type tables and a Composite Key. Here I name it Cricketer Type.

Create database Cricket

use Cricket

Create table Cricketer (CricketerId int identity(1,1) primary key,Cricketer varchar(40))

Create table Type (TypeId int identity(1,1) primary key,Type varchar(40))

insert into Cricketer values ('Sumesh')
insert into Cricketer values ('Nithin')
insert into Cricketer values ('Shalvin')

insert into Cricketer values ('Mongia')
insert into Type values ('Batsman')
insert into Type values ('Bowler')
insert into Type values ('Wicket Keeper')

create table CricketerType(primary key (CricketerId, TypeId),CricketerId int references Cricketer(CricketerId),TypeId int references Type(TypeId) )

insert into CricketerType values (1, 1)
insert into CricketerType values (1, 3)
insert into CricketerType values (2, 2)
insert into CricketerType values (3, 3)
insert into CricketerType values (4, 3)

select * from Cricketer
select * from Type
select * from CricketerType

select C.CricketerId, C.Cricketer, T.Type from Cricketer C, Type T,CricketerType CT where CT.CricketerId = C.CricketerId and Ct.TypeId = T.TypeId

Expense Tracking System Sql Script

create database ExpenseTrack

use ExpenseTrack

create table ExpenseGroup(GroupId int primary key, GroupName varchar(40) unique)

insert into ExpenseGroup values (1, 'Food')
insert into ExpenseGroup values (2, 'Clothes')
insert into ExpenseGroup values (3, 'Travelling')

select * from ExpenseGroup

create table Expenses (ExpenseId int primary key,
GroupId int references ExpenseGroup(GroupId),
Expense varchar(40))

insert into Expenses values (1, 1, 'Vegetables')

insert into Expenses values (2, 1, 'Hotel')

insert into Expenses values (3, 2, 'Clothes')

insert into Expenses values (4, 3, 'Petrol')


create table ExpenseTrans (TransId int primary key,
ExpenseId int references Expenses(ExpenseId),
TransDate datetime, Amount numeric(8,2), Description varchar(200))

select * from Expenses

create procedure spGetExpenses as
select G.GroupName, E.Expense from ExpenseGroup G, Expenses E
where G.GroupId = E.GroupId
go

spGetExpenses

insert into ExpenseTrans values (1, 3, '14-Oct-09', 400, 'Bought shirt from Navigator')

insert into ExpenseTrans values (2, 2, '15-Oct-09', 40, 'Had two Masala Dosa from Megha Hotel Kacheripady')
insert into ExpenseTrans values (3, 4, '15-Oct-09', 100, 'Petrol for Rs. 100 from Vaduthal Pump')

select * from ExpenseTrans



create procedure spGetExpenseTrans as
select E.Expense, T.TransDate, T.Amount, T.Description
from Expenses E, ExpensTrans T where E.ExpenseId = T.ExpenseId
go

spGetExpenseTrans


'DbConnect


Imports System.Data.SqlClient
Public Class DbConnect
Public cnn As SqlConnection
Dim ds As New DataSet
Dim da As SqlDataAdapter

Public Sub Open()
cnn = New SqlConnection("Integrated security=sspi;Initial Catalog=ExpenseTrack")
cnn.Open()
End Sub
Public Function GetExpenses() As DataTable
Open()
da = New SqlDataAdapter("select * from Expenses", cnn)
da.Fill(ds, "Expense")
Return ds.Tables("Expense")
End Function
End Class

'frmTransDetial

Imports System.Data.SqlClient
Public Class Form1
Dim dbc As New DbConnect
Dim b As Boolean = False
Dim cmd As SqlCommand
Dim intMax As Integer
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cboExpense.DataSource = dbc.GetExpenses
cboExpense.DisplayMember = "Expense"
cboExpense.ValueMember = "ExpenseId"
b = True
cmd = New SqlCommand("select Max(TransId) from ExpenseTrans", dbc.cnn)
b = Int32.TryParse(cmd.ExecuteScalar().ToString(), intMax)
intMax += 1
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim strSql As String
strSql = "insert into ExpenseTrans values (" + intMax.ToString + ", "
strSql += cboExpense.SelectedValue.ToString + ", '"
strSql += dtpDate.Value.ToString("d").ToString + "', "
strSql += txtAmount.Text + ", '" + txtRemarks.Text + "')"
cmd = New SqlCommand(strSql, dbc.cnn)
cmd.ExecuteNonQuery()
End Sub
End Class


Windows Generated Code for Expense Transaction Form


Private Sub InitializeComponent()
Me.Label1 = New System.Windows.Forms.Label
Me.dtpDate = New System.Windows.Forms.DateTimePicker
Me.Label2 = New System.Windows.Forms.Label
Me.cboExpense = New System.Windows.Forms.ComboBox
Me.Label3 = New System.Windows.Forms.Label
Me.txtRemarks = New System.Windows.Forms.TextBox
Me.btnSave = New System.Windows.Forms.Button
Me.Label4 = New System.Windows.Forms.Label
Me.txtAmount = New System.Windows.Forms.TextBox
Me.SuspendLayout()
'
'Label1
'
Me.Label1.AutoSize = True
Me.Label1.Location = New System.Drawing.Point(38, 43)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(30, 13)
Me.Label1.TabIndex = 0
Me.Label1.Text = "Date"
'
'dtpDate
'
Me.dtpDate.CustomFormat = "dd MMM yyyy"
Me.dtpDate.Format = System.Windows.Forms.DateTimePickerFormat.Custom
Me.dtpDate.Location = New System.Drawing.Point(97, 36)
Me.dtpDate.Name = "dtpDate"
Me.dtpDate.Size = New System.Drawing.Size(140, 20)
Me.dtpDate.TabIndex = 1
'
'Label2
'
Me.Label2.AutoSize = True
Me.Label2.Location = New System.Drawing.Point(38, 96)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(48, 13)
Me.Label2.TabIndex = 2
Me.Label2.Text = "Expense"
'
'cboExpense
'
Me.cboExpense.FormattingEnabled = True
Me.cboExpense.Location = New System.Drawing.Point(97, 88)
Me.cboExpense.Name = "cboExpense"
Me.cboExpense.Size = New System.Drawing.Size(121, 21)
Me.cboExpense.TabIndex = 3
'
'Label3
'
Me.Label3.AutoSize = True
Me.Label3.Location = New System.Drawing.Point(38, 180)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(49, 13)
Me.Label3.TabIndex = 4
Me.Label3.Text = "Remarks"
'
'txtRemarks
'
Me.txtRemarks.Location = New System.Drawing.Point(97, 173)
Me.txtRemarks.Multiline = True
Me.txtRemarks.Name = "txtRemarks"
Me.txtRemarks.Size = New System.Drawing.Size(178, 48)
Me.txtRemarks.TabIndex = 5
'
'btnSave
'
Me.btnSave.Location = New System.Drawing.Point(97, 251)
Me.btnSave.Name = "btnSave"
Me.btnSave.Size = New System.Drawing.Size(75, 23)
Me.btnSave.TabIndex = 6
Me.btnSave.Text = "Save"
Me.btnSave.UseVisualStyleBackColor = True
'
'Label4
'
Me.Label4.AutoSize = True
Me.Label4.Location = New System.Drawing.Point(38, 141)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(43, 13)
Me.Label4.TabIndex = 7
Me.Label4.Text = "Amount"
'
'txtAmount
'
Me.txtAmount.Location = New System.Drawing.Point(97, 134)
Me.txtAmount.Name = "txtAmount"
Me.txtAmount.Size = New System.Drawing.Size(100, 20)
Me.txtAmount.TabIndex = 8
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(372, 321)
Me.Controls.Add(Me.txtAmount)
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.btnSave)
Me.Controls.Add(Me.txtRemarks)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.cboExpense)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.dtpDate)
Me.Controls.Add(Me.Label1)
Me.Name = "Form1"
Me.Text = "Expense Trans"
Me.ResumeLayout(False)
Me.PerformLayout()
End Sub
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents dtpDate As System.Windows.Forms.DateTimePicker
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents cboExpense As System.Windows.Forms.ComboBox
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents txtRemarks As System.Windows.Forms.TextBox
Friend WithEvents btnSave As System.Windows.Forms.Button
Friend WithEvents Label4 As System.Windows.Forms.Label
Friend WithEvents txtAmount As System.Windows.Forms.TextBox


Related Blog

Simple Contact Management System In C# and VB .Net

Sql Server 200 Generating Script

An Sql Script is a text file containing the database objects like like table, views, stored procedures etc.

Sql Server 2000 Enterprise Manager or Sql Server 2005/2008 Management Studio can be used for generating Sql Script.

Here is the generated script

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Trans__ExpenseId__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[Trans] DROP CONSTRAINT FK__Trans__ExpenseId__7C8480AEGO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Expense__GroupId__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[Expense] DROP CONSTRAINT FK__Expense__GroupId__79A81403GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spAllExpense]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[spAllExpense]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Expense]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Expense]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExpenseGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[ExpenseGroup]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Trans]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Trans]
GO

CREATE TABLE [dbo].[Expense] ( [ExpenseId] [int] IDENTITY (1, 1) NOT NULL , [GroupId] [int] NULL , [ExpenseName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ExpenseGroup] ( [GroupId] [int] IDENTITY (1, 1) NOT NULL , [GroupName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Trans] ( [TranId] [int] IDENTITY (1, 1) NOT NULL , [ExpenseId] [int] NULL , [Date] [datetime] NULL , [Amount] [numeric](8, 2) NULL , [Description] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON
GO

create procedure spAllExpense asselect * from Expense
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON
GO