test.sql

物业管理和办公自动化系统

/****** Object:  User common    Script Date: 2002-06-20 10:05:54 ******/
exec sp_addrolemember N'db_owner', N'common'
GO

/****** Object:  User cxy    Script Date: 2002-06-20 10:05:54 ******/
exec sp_addrolemember N'db_owner', N'cxy'
GO

/****** Object:  User infortower    Script Date: 2002-06-20 10:05:54 ******/
exec sp_addrolemember N'db_owner', N'infortower'
GO

/****** Object:  Table [dbo].[T_Education]    Script Date: 2002-06-20 10:06:01 ******/
CREATE TABLE [dbo].[T_Education] (
	[Education_id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Education_desc] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Folk]    Script Date: 2002-06-20 10:06:07 ******/
CREATE TABLE [dbo].[T_Folk] (
	[Folk_id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Folk_desc] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Health]    Script Date: 2002-06-20 10:06:08 ******/
CREATE TABLE [dbo].[T_Health] (
	[Health_id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Health_desc] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Marriage]    Script Date: 2002-06-20 10:06:09 ******/
CREATE TABLE [dbo].[T_Marriage] (
	[Marriage_id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Marriage_desc] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Node]    Script Date: 2002-06-20 10:06:11 ******/
CREATE TABLE [dbo].[T_Node] (
	[Node_level] [tinyint] NULL ,
	[Node_id] [int] IDENTITY (10, 1) NOT NULL ,
	[Node_name] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
	[Node_func] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
	[Parent_Node_id] [int] NULL ,
	[isGroup] [tinyint] NULL ,
	[Pos] [smallint] NULL ,
	[co_id] [int] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Political]    Script Date: 2002-06-20 10:06:12 ******/
CREATE TABLE [dbo].[T_Political] (
	[Political_id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Political_desc] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Role]    Script Date: 2002-06-20 10:06:13 ******/
CREATE TABLE [dbo].[T_Role] (
	[Role_id] [int] IDENTITY (10, 1) NOT NULL ,
	[Role_Desp] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Role_Type] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Title]    Script Date: 2002-06-20 10:06:14 ******/
CREATE TABLE [dbo].[T_Title] (
	[title_id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[title_desc] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Position]    Script Date: 2002-06-20 10:06:15 ******/
CREATE TABLE [dbo].[T_Position] (
	[Position_id] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Position_desc] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[admin_level] [int] NULL ,
	[Default_role] [int] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Employee]    Script Date: 2002-06-20 10:06:17 ******/
CREATE TABLE [dbo].[T_Employee] (
	[Serial] [int] IDENTITY (1, 1) NOT NULL ,
	[Emp_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Alias] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Gender] [smallint] NULL ,
	[Birthday] [datetime] NULL ,
	[ID] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
	[Folk] [smallint] NULL ,
	[Native] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Home_Phone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Home_Address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Home_Zip] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Specialty] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
	[Education] [smallint] NULL ,
	[Graduate_Date] [datetime] NULL ,
	[Graduate_School] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Health] [smallint] NULL ,
	[Marriage] [smallint] NULL ,
	[Political] [smallint] NULL ,
	[Poli_Date] [datetime] NULL ,
	[Interest] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Node_Id] [int] NULL ,
	[Pos] [smallint] NULL ,
	[Work_Date] [datetime] NULL ,
	[Telecom_Date] [datetime] NULL ,
	[Emp_Date] [datetime] NULL ,
	[Title] [smallint] NULL ,
	[Dossier_No] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Contract_No] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Achievements] [varchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,
	[Dismissed] [tinyint] NULL ,
	[writeoff] [tinyint] NULL ,
	[Co_email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Photo] [image] NULL ,
	[Remark] [varchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,
	[co_id] [int] NULL ,
	[isdummy] [tinyint] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[T_Plan]    Script Date: 2002-06-20 10:06:20 ******/
CREATE TABLE [dbo].[T_Plan] (
	[serial] [int] IDENTITY (1, 1) NOT NULL ,
	[type] [int] NOT NULL ,
	[of_year] [int] NOT NULL ,
	[of_month] [int] NULL ,
	[of_season] [int] NULL ,
	[person] [int] NOT NULL ,
	[scope] [int] NOT NULL ,
	[node] [int] NULL ,
	[release] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[T_Education] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Education] PRIMARY KEY  CLUSTERED 
	(
		[Education_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Folk] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Folk] PRIMARY KEY  CLUSTERED 
	(
		[Folk_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Health] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Health] PRIMARY KEY  CLUSTERED 
	(
		[Health_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Marriage] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Marriage] PRIMARY KEY  CLUSTERED 
	(
		[Marriage_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Node] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Node] PRIMARY KEY  CLUSTERED 
	(
		[Node_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Political] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Political] PRIMARY KEY  CLUSTERED 
	(
		[Political_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Role] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Role] PRIMARY KEY  CLUSTERED 
	(
		[Role_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Title] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Title] PRIMARY KEY  CLUSTERED 
	(
		[title_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Position] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Position] PRIMARY KEY  CLUSTERED 
	(
		[Position_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Employee] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Employee] PRIMARY KEY  CLUSTERED 
	(
		[Serial]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Plan] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_Plan] PRIMARY KEY  CLUSTERED 
	(
		[serial]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Node] WITH NOCHECK ADD 
	CONSTRAINT [DF_T_Node_isGroup] DEFAULT (1) FOR [isGroup],
	CONSTRAINT [DF_T_Node_Pos] DEFAULT (0) FOR [Pos]
GO

ALTER TABLE [dbo].[T_Role] WITH NOCHECK ADD 
	CONSTRAINT [IX_T_Role] UNIQUE  NONCLUSTERED 
	(
		[Role_Desp]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[T_Position] WITH NOCHECK ADD 
	CONSTRAINT [DF_T_Position_admin_level] DEFAULT (0) FOR [admin_level],
	CONSTRAINT [DF_T_Position_Default_role] DEFAULT (1) FOR [Default_role]
GO

ALTER TABLE [dbo].[T_Employee] WITH NOCHECK ADD 
	CONSTRAINT [DF_T_Employee_Dismissed] DEFAULT (0) FOR [Dismissed],
	CONSTRAINT [DF_T_Employee_writeoff] DEFAULT (0) FOR [writeoff],
	CONSTRAINT [DF_T_Employee_isdummy] DEFAULT (0) FOR [isdummy]
GO

ALTER TABLE [dbo].[T_Plan] WITH NOCHECK ADD 
	CONSTRAINT [DF_T_Plan_release] DEFAULT (0) FOR [release]
GO

 CREATE  INDEX [IX_T_Plan] ON [dbo].[T_Plan]([serial]) ON [PRIMARY]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[T_Education]  TO [common]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[T_Folk]  TO [common]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[T_Health]  TO [common]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[T_Marriage]  TO [common]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[T_Node]  TO [common]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[T_Political]  TO [common]
GO

GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[T_Role]  TO [common]
GO