Project Base Training Part 1 Create Database With Source Code

on
Hi, Dear student here you can learn how to create database for Accounting Project in sql server, so now if you want to create database you have required Microsoft Sql Server. Ilyasoft software company provide full project on our YouTube Channel ilyasoft software company. 

In this tutorial we will cover all the below mention topics:
    i)  How to create database?
    ii) How to create tables in database?
    iii) How to create tables for stock management?
    iv) How to create tables for sale's and customer's management?
    v) How to create tables for purchase and supplier's management?
    vi) How to create tables for sale and purchase payment?
    vii) How to create tables for account head, account controls and sub controls?
    viii) How to create tables for expenses management?
    ix) How to create tables for sale and purchase return to manage?
    x) How to manage employee salaries?
    xi) How to create tables for account transactions?
    xii) How to apply normalization process on database?
    xiii) How create relation between tables, using code and ui?
    xiv How to manage financial years?
So now watch the video and learn how?
    

Database Script : 
    
1) Account Head Table : 
    CREATE TABLE [dbo].[AccountHeadTable](
	[HeadID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[HeadTitle] [varchar](150) NOT NULL )
2) Category Table : 
    CREATE TABLE [dbo].[CategoryTable](
	[CategoryID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[CategoryName] [nvarchar](150) NOT NULL)
3) Customer Table : 
    CREATE TABLE [dbo].[CustomerTable](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [CustomerName] [nvarchar](150) NOT NULL,
        [ContactNo] [nvarchar](20) NULL,
        [Email] [nvarchar](150) NULL,       
        [Address] [nvarchar](300) NULL)
4) Expense Category Table : 
    CREATE TABLE [dbo].[ExpCategoryTable](
	[ExpCategoryID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ExpCategoryName] [nvarchar](100) NOT NULL)
5) Expense Details Table : 
    CREATE TABLE [dbo].[ExpensesDetailTable](
	[ExpDetailID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ExpCategoryID] [int] NOT NULL,
	[ItemName] [nvarchar](150) NOT NULL,
	[ItemCost] [float] NOT NULL,
	[ExpensesID] [int] NOT NULL)
6) Expense Header Table : 
    CREATE TABLE [dbo].[ExpensesTable](
	[ExpensesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[InvoiceNo] [nvarchar](150) NOT NULL,
	[ExpensesTitle] [int] NOT NULL,
	[ExpensesDate] [date] NOT NULL,
	[ExpensesReason] [nvarchar](300) NOT NULL,
	[TotalAmount] [float] NOT NULL)
7) Financial Year Table : 
    CREATE TABLE [dbo].[FinancialYearTable](
	[FinanicalYearID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[FinanicalYearTitle] [nvarchar](250) NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NOT NULL,
	[Status] [bit] NOT NULL)
8) Head Control Table : 
    CREATE TABLE [dbo].[HeadControlTable](
	[HeadControlID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[HeadID] [int] NOT NULL,
	[ControlTitle] [nvarchar](250) NOT NULL)
9) Product Table : 
    CREATE TABLE [dbo].[ProductTable](
	[ProductID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[CategoryID] [int] NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[MfcDate] [date] NOT NULL,
	[ExpDate] [date] NOT NULL,
	[Quantity] [int] NOT NULL CONSTRAINT [DF_ProductTable_Quantity]  DEFAULT ((0)),
	[PurchaseUnitPrice] [float] NOT NULL CONSTRAINT [DF_ProductTable_PurchaseUnitPrice]  DEFAULT ((0)),
	[SaleUnitPrice] [float] NOT NULL CONSTRAINT [DF_ProductTable_SaleUnitPrice]  DEFAULT ((0)),
	[Description] [nvarchar](300) NULL)
10) Purchase Details Table : 
    CREATE TABLE [dbo].[PurchaseDetailsTable](
	[PurchaseDetailID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[PurchaseID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[PurchaseQty] [int] NOT NULL,
	[PurchaseUnitPrice] [float] NOT NULL)
11) Purchase Return Details Table : 
    CREATE TABLE [dbo].[PurchaseReturnDetailTable](
	[PurchaseReturnDetailID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[PurchaseReturnID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[ReturnQty] [int] NOT NULL,
	[ReturnUnitPrice] [float] NOT NULL,
	[ReturnReason] [nvarchar](50) NULL)
12) Purchase Return Header Table : 
    CREATE TABLE [dbo].[PurchaseReturnTable](
	[PurchaseReturnID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[PurchaseID] [int] NOT NULL,
	[SupplierID] [int] NOT NULL,
	[PurchaseReturnDate] [date] NOT NULL,
	[ReturnTotalAmount] [float] NOT NULL,
	[InvoiceNo] [nvarchar](150) NOT NULL)
13) Purchase Header Table : 
    CREATE TABLE [dbo].[PurchaseTable](
	[PurchaseID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[SupplierID] [int] NOT NULL,
	[InvoiceNo] [nvarchar](150) NOT NULL,
	[PurchaseDate] [date] NOT NULL,
	[TotalAmount] [float] NOT NULL)
14) Sale Details Table : 
    CREATE TABLE [dbo].[SaleDetailTable](
	[SaleDetailID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[SaleID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[SaleQty] [int] NOT NULL,
	[SaleUnitPrice] [float] NOT NULL)
15) Sale Return Details Table : 
    CREATE TABLE [dbo].[SaleReturnDetailTable](
	[SaleReturnDetailID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[SaleReturnID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[ReturnQty] [int] NOT NULL,
	[ReturnUnitPrice] [float] NOT NULL,
	[ReturnReason] [nvarchar](50) NULL)
16) Sale Return Header Table : 
    CREATE TABLE [dbo].[SaleReturnTable](
	[SaleReturnID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[SaleID] [int] NOT NULL,
	[CustomerID] [int] NOT NULL,
	[SaleReturnDate] [date] NOT NULL,
	[ReturnTotalAmount] [float] NOT NULL,
	[InvoiceNo] [nvarchar](150) NULL)
17) Sale Header Table : 
    CREATE TABLE [dbo].[SaleTable](
	[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[CustomerID] [int] NOT NULL,
	[InvoiceNo] [nvarchar](150) NOT NULL,
	[SaleDate] [date] NOT NULL,
	[TotalAmount] [float] NOT NULL)
18) Supplier Table : 
    CREATE TABLE [dbo].[SupplierTable](
	[SupplierID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[SupplierName] [nvarchar](150) NOT NULL,
	[ContactNo] [nvarchar](20) NOT NULL,
	[Address] [nvarchar](300) NOT NULL,
	[Email] [nvarchar](150) NULL,
	[Description] [nvarchar](300) NULL)
19) Transaction Table : 
    CREATE TABLE [dbo].[TransactionTable](
	[TransactionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[HeadID] [int] NOT NULL,
	[HeadControlID] [int] NOT NULL,
	[FinanicalYearID] [int] NOT NULL,
	[TransactionTitle] [nvarchar](300) NOT NULL,
	[InvoiceNo] [nvarchar](150) NOT NULL,
	[Credit] [float] NOT NULL,
	[Debit] [float] NOT NULL,
	[TransactionDate] [datetime] NOT NULL)
20) Transaction Table : 
    CREATE TABLE [dbo].[TransactionTable](
	[TransactionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[HeadID] [int] NOT NULL,
	[HeadControlID] [int] NOT NULL,
	[FinanicalYearID] [int] NOT NULL,
	[TransactionTitle] [nvarchar](300) NOT NULL,
	[InvoiceNo] [nvarchar](150) NOT NULL,
	[Credit] [float] NOT NULL,
	[Debit] [float] NOT NULL,
	[TransactionDate] [datetime] NOT NULL)
For Creating relationship between these table watch full video





Comments

Post a Comment