merrymeeting.sql

一个会议日程安排管理项目,解压后双击安装,数据库脚本也副在里面,安装好后登陆既可(用户名和密码都为NIIT)

CREATE DATABASE MerryMeeting
GO
use MerryMeeting
GO
CREATE TABLE [dbo].[Attendees] (
	[AttendeeID] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[AttendeeFName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[AttendeeLName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[City] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[State] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Zip] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
	[Phone] [char] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Email] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[EventID] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Status] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Events] (
	[EventId] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ContactPersonFName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ContactPersonLName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CompanyName] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[City] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[State] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Zip] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Phone] [char] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Description] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Category] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[StartDate] [datetime] NOT NULL ,
	[EndDate] [datetime] NOT NULL ,
	[VenueID] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[AttendeeFlag] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[NoOfAttendees] [int] NOT NULL ,
	[Advance] [money] NOT NULL ,
	[TotalRent] [money] NOT NULL ,
	[FacilitiesReqd] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Status] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[SeatsAvailable] [int] NOT NULL ,
	[RegnDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Managers] (
	[ManagerName] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Password] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
	[UserName] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Password] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Venues] (
	[VenueID] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[VenueName] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[City] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[State] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Zip] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Phone] [char] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ContactPersonFName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ContactPersonLName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Email] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Rent] [money] NOT NULL ,
	[Capacity] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Events] ADD 
	CONSTRAINT [PK_Events] PRIMARY KEY  CLUSTERED 
	(
		[EventId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Managers] ADD 
	CONSTRAINT [pkManagerName] PRIMARY KEY  CLUSTERED 
	(
		[ManagerName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Venues] ADD 
	CONSTRAINT [PK_Venues] PRIMARY KEY  CLUSTERED 
	(
		[VenueID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Events] ADD 
	CONSTRAINT [FK_Events_Venues] FOREIGN KEY 
	(
		[VenueID]
	) REFERENCES [dbo].[Venues] (
		[VenueID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create Procedure selectEventsByPage @PageNum int
as
if @PageNum > 0
begin
DECLARE @sqlString AS nvarchar(4000) 
set @sqlString = 
'select top 50 * from Events where EventID 
not in 
(
select top ' + CAST(@PageNum * 50 as varchar(20))+ ' EventID from Events
where datediff(dd,StartDate,getDate()) <=5 
and Status = ''Confirmed'' order by EventID 
) 
and datediff(dd,StartDate,getDate()) <=5  
and Status = ''Confirmed'' order by EventID'
EXEC(@sqlString)
end
else
begin
select top 50 * from Events 
where datediff(dd,StartDate,getDate()) <=5 
and Status = 'Confirmed' order by EventID
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create trigger insAttendees on Attendees for insert
as 
begin transaction
if (select SeatsAvailable from Events,Inserted
where Events.EventID = Inserted.EventID) > 0 
begin
   update Events 
   set Events.SeatsAvailable = Events.SeatsAvailable - 1 
   from Events,Inserted
   where Events.EventID = Inserted.EventID
   commit transaction
end
else
begin
   rollback
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
use MerryMeeting
GO
insert into Users values('niit','niit');
insert into Managers values('niit','niit');
insert into Venues values('v001','xiamen','xiamen','xiamen','xm','361008','222222','aaa','aaa','aaa',3000,500);
insert into Events values('e001','ff','aa','ff','f','aa','aa','11','11','a','a','2006-12-10','2006-12-30','a001','1',30,5000,5000,'1','Confirmed',200,getdate());