CREATE DATABASE MySourceCode
GO

use MySourceCode
GO

CREATE TABLE [dbo].[ArticleDetails] (
	[ArticleID] [int] IDENTITY (1, 1) NOT NULL ,
	[ArticleTitle] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Author] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ArticleDescription] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ArticleURL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Cat1] [bit] NULL ,
	[Cat2] [bit] NULL ,
	[Cat3] [bit] NULL ,
	[Cat4] [bit] NULL ,
	[Cat5] [bit] NULL ,
	[DiffLevel] [int] NOT NULL ,
	[Attachments] [bit] NOT NULL ,
	[Picture] [bit] NOT NULL ,
	[LastUpdate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ArticleRating] (
	[ArticleID] [int] NOT NULL ,
	[Rating] [float] NOT NULL ,
	[NoOfUsers] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserComments] (
	[PostID] [int] IDENTITY (1, 1) NOT NULL ,
	[ArticleID] [int] NOT NULL ,
	[Sender] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Posted] [datetime] NOT NULL ,
	[Subject] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Message] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE PROCEDURE UpdateArticleRating
@ArtID int, @UserRating int
AS
Declare @ExRating float
Declare @ExUserCount int
set @ExUserCount=0
select @ExRating=Rating, @ExUserCount=NoOfUsers 
from ArticleRating where ArticleID=@ArtID
if @ExUserCount=0
insert into ArticleRating values (@ArtID, @UserRating, 1)
else
BEGIN
set @ExRating=@ExRating*@ExUserCount
set @ExRating=@ExRating + @UserRating
set @ExUserCount=@ExUserCount+1
set @ExRating=@ExRating/@ExUserCount
update ArticleRating set Rating=@ExRating, NoOfUsers=@ExUserCount where ArticleID=@ArtID
END

GO
