hrmanager.sql

精通SQL Server2005项目开发

CREATE DATABASE [HRManager1]
GO

USE [HRManager1]
GO

/****** 对象:  Table [dbo].[City]    脚本日期: 10/21/2006 16:02:45 ******/
CREATE TABLE [dbo].[City](
	[CityID] [int] NOT NULL,
	[CityName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/****** 对象:  Table [dbo].[Job]    脚本日期: 10/21/2006 16:03:45 ******/
CREATE TABLE [dbo].[Job](
	[JobId] [int] IDENTITY(1,1) NOT NULL,
	[JobName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[JobCityId] [int] NOT NULL,
	[JobTypeId] [int] NOT NULL,
	[Responsibility] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[Requirement] [text] COLLATE Chinese_PRC_CI_AS NULL,
	[IsUrgent] [int] NULL,
 CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED 
(
	[JobName] ASC,
	[JobCityId] ASC,
	[JobTypeId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** 对象:  Table [dbo].[JobType]    脚本日期: 10/21/2006 16:04:00 ******/
CREATE TABLE [dbo].[JobType](
	[TypeId] [int] NOT NULL,
	[TypeName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_JobType] PRIMARY KEY CLUSTERED 
(
	[TypeId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** 对象:  Table [dbo].[Resume]    脚本日期: 10/21/2006 16:04:11 ******/
CREATE TABLE [dbo].[Resume](
	[ResumeId] [int] IDENTITY(1,1) NOT NULL,
	[JobName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[SecondJobName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Password] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Gender] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Birthday] [datetime] NULL,
	[City] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Degree] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[School] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Major] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[IsFreshGraduate] [int] NULL,
	[GraduateTime] [datetime] NULL,
	[WorkYears] [int] NULL,
	[CurCompany] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[CurJob] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[ForeignLanguageType] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[ForeignLanguageLevel] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Phone] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Mobile] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Email] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[AffixFile] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Resume] PRIMARY KEY CLUSTERED 
(
	[Name] ASC,
	[Password] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [FK_Job_City] FOREIGN KEY([JobCityId])
REFERENCES [dbo].[City] ([CityID])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_City]
GO
ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [FK_Job_JobType] FOREIGN KEY([JobTypeId])
REFERENCES [dbo].[JobType] ([TypeId])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_JobType]

/****** 初始化City表数据******/
INSERT INTO [City] VALUES (1,'北京');
INSERT INTO [City] VALUES (2,'上海');
INSERT INTO [City] VALUES (3,'广州');	
INSERT INTO [City] VALUES (4,'深圳');
GO

/****** 初始化JobType表数据******/
INSERT INTO [JobType] VALUES (1,'技术');
INSERT INTO [JobType] VALUES (2,'研发');
INSERT INTO [JobType] VALUES (3,'市场与销售');	
INSERT INTO [JobType] VALUES (4,'客户服务')
INSERT INTO [JobType] VALUES (5,'管理');
GO