IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'fastfood')
	DROP DATABASE [fastfood]
GO

CREATE DATABASE [fastfood]  ON (NAME = N'fastfood_Data', FILENAME = N'E:\fastfood\data\fastfood_Data.MDF' , SIZE = 1, FILEGROWTH = 10%), (NAME = N'fastfood_1_Data', FILENAME = N'E:\fastfood\data\fastfood_log.ldf' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'fastfood_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\fastfood_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Arabic_CI_AI_KS_WS
GO

exec sp_dboption N'fastfood', N'autoclose', N'true'
GO

exec sp_dboption N'fastfood', N'bulkcopy', N'false'
GO

exec sp_dboption N'fastfood', N'trunc. log', N'true'
GO

exec sp_dboption N'fastfood', N'torn page detection', N'true'
GO

exec sp_dboption N'fastfood', N'read only', N'false'
GO

exec sp_dboption N'fastfood', N'dbo use', N'false'
GO

exec sp_dboption N'fastfood', N'single', N'false'
GO

exec sp_dboption N'fastfood', N'autoshrink', N'true'
GO

exec sp_dboption N'fastfood', N'ANSI null default', N'false'
GO

exec sp_dboption N'fastfood', N'recursive triggers', N'false'
GO

exec sp_dboption N'fastfood', N'ANSI nulls', N'false'
GO

exec sp_dboption N'fastfood', N'concat null yields null', N'false'
GO

exec sp_dboption N'fastfood', N'cursor close on commit', N'false'
GO

exec sp_dboption N'fastfood', N'default to local cursor', N'false'
GO

exec sp_dboption N'fastfood', N'quoted identifier', N'false'
GO

exec sp_dboption N'fastfood', N'ANSI warnings', N'false'
GO

exec sp_dboption N'fastfood', N'auto create statistics', N'true'
GO

exec sp_dboption N'fastfood', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
	exec sp_dboption N'fastfood', N'db chaining', N'false'
GO

use [fastfood]
GO

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

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

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

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

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

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

if not exists (select * from dbo.sysusers where name = N'guest' and hasdbaccess = 1)
	EXEC sp_grantdbaccess N'guest'
GO

CREATE TABLE [dbo].[tbl_factor] (
	[shomare_factor] [int] IDENTITY (1, 1) NOT NULL ,
	[id_foods] [int] NOT NULL ,
	[id_tarafHesab] [int] NOT NULL ,
	[gheymt] [money] NOT NULL ,
	[tedad] [int] NOT NULL ,
	[tarikh] [varchar] (10) COLLATE Arabic_CI_AI_KS_WS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_foods] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[food] [nvarchar] (50) COLLATE Arabic_CI_AI_KS_WS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_tarafHesab] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[name] [nvarchar] (50) COLLATE Arabic_CI_AI_KS_WS NOT NULL ,
	[tel] [nvarchar] (50) COLLATE Arabic_CI_AI_KS_WS NOT NULL ,
	[address] [nvarchar] (200) COLLATE Arabic_CI_AI_KS_WS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_factor] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_factor] PRIMARY KEY  CLUSTERED 
	(
		[shomare_factor]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_foods] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_foods] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_tarafHesab] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_tarafHesab] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

 CREATE  UNIQUE  INDEX [IX_tbl_foods] ON [dbo].[tbl_foods]([food]) WITH  IGNORE_DUP_KEY  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_factor] ADD 
	CONSTRAINT [FK_tbl_factor_tbl_foods] FOREIGN KEY 
	(
		[id_foods]
	) REFERENCES [dbo].[tbl_foods] (
		[id]
	),
	CONSTRAINT [FK_tbl_factor_tbl_tarafHesab] FOREIGN KEY 
	(
		[id_tarafHesab]
	) REFERENCES [dbo].[tbl_tarafHesab] (
		[id]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.vw_food_rep
AS
SELECT     dbo.tbl_foods.food, dbo.tbl_tarafHesab.name, dbo.tbl_tarafHesab.tel, dbo.tbl_tarafHesab.address, dbo.tbl_factor.tarikh, dbo.tbl_factor.shomare_factor, 
                      dbo.tbl_factor.gheymt, dbo.tbl_factor.tedad, dbo.tbl_factor.tedad * dbo.tbl_factor.gheymt AS mablaghKol
FROM         dbo.tbl_factor INNER JOIN
                      dbo.tbl_foods ON dbo.tbl_factor.id_foods = dbo.tbl_foods.id INNER JOIN
                      dbo.tbl_tarafHesab ON dbo.tbl_factor.id_tarafHesab = dbo.tbl_tarafHesab.id

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

