projecttimetracker sql server script.sql

来自「Beginning VB.NET DatabasesAll_Code.rar」· SQL 代码 · 共 195 行

---------------------------------------------------------------------
-- Tables
---------------------------------------------------------------------
CREATE TABLE GroupProjects 
(
	GroupProjectID uniqueidentifier NOT NULL ,
	GroupID uniqueidentifier NOT NULL ,
	ProjectID uniqueidentifier NOT NULL 
) 
GO

CREATE TABLE Groups 
(
	GroupID uniqueidentifier NOT NULL ,
	GroupName varchar (50) NOT NULL ,
	GroupDescription text NULL ,
	LastUpdateDate datetime NOT NULL 
) 
GO

CREATE TABLE Projects 
(
	ProjectID uniqueidentifier NOT NULL ,
	ProjectName varchar (50) NOT NULL ,
	ProjectDescription text NULL ,
	SequenceNumber tinyint NOT NULL ,
	LastUpdateDate datetime NOT NULL 
) 
GO

CREATE TABLE Roles 
(
	RoleID uniqueidentifier NOT NULL ,
	RoleName varchar (50) NOT NULL ,
	RoleDescription text NULL ,
	Ranking tinyint NOT NULL ,
	LastUpdateDate datetime NOT NULL 
) 
GO

CREATE TABLE TimeSheetItems 
(
	TimeSheetItemID uniqueidentifier NOT NULL ,
	TimeSheetID uniqueidentifier NOT NULL ,
	ProjectID uniqueidentifier NOT NULL ,
	Hours tinyint NOT NULL ,
	TimeSheetDate datetime NOT NULL 
) 
GO

CREATE TABLE TimeSheets 
(
	TimeSheetID uniqueidentifier NOT NULL ,
	UserID uniqueidentifier NOT NULL ,
	WeekEndingDate datetime NOT NULL ,
	Submitted bit NOT NULL ,
	ApprovalDate datetime NULL ,
	ManagerID uniqueidentifier NULL ,
	LastUpdateDate datetime NOT NULL 
) 
GO

CREATE TABLE Users 
(
	UserID uniqueidentifier NOT NULL ,
	LoginName varchar (15) NOT NULL ,
	Password varchar (30) NOT NULL ,
	FirstName varchar (30) NOT NULL ,
	LastName varchar (30) NOT NULL ,
	Email varchar (50) NOT NULL ,
	Phone varchar (20) NOT NULL ,
	Status bit NOT NULL ,
	GroupID uniqueidentifier NOT NULL ,
	RoleID uniqueidentifier NOT NULL ,
	ManagerID uniqueidentifier NULL ,
	LastUpdateDate datetime NOT NULL 
) 
GO

---------------------------------------------------------------------
-- Primary Keys
---------------------------------------------------------------------
ALTER TABLE GroupProjects WITH NOCHECK ADD 
	CONSTRAINT PK_GroupProjects PRIMARY KEY  CLUSTERED 
	(
		GroupProjectID
	)  ON PRIMARY 
GO

ALTER TABLE Groups WITH NOCHECK ADD 
	CONSTRAINT PK_Groups PRIMARY KEY  CLUSTERED 
	(
		GroupID
	)  ON PRIMARY 
GO

ALTER TABLE Projects WITH NOCHECK ADD 
	CONSTRAINT PK_Projects PRIMARY KEY  CLUSTERED 
	(
		ProjectID
	)  ON PRIMARY 
GO

ALTER TABLE Roles WITH NOCHECK ADD 
	CONSTRAINT PK_Roles PRIMARY KEY  CLUSTERED 
	(
		RoleID
	)  ON PRIMARY 
GO

ALTER TABLE TimeSheetItems WITH NOCHECK ADD 
	CONSTRAINT PK_TimeSheetItems PRIMARY KEY  CLUSTERED 
	(
		TimeSheetItemID
	)  ON PRIMARY 
GO

ALTER TABLE TimeSheets WITH NOCHECK ADD 
	CONSTRAINT PK_TimeSheets PRIMARY KEY  CLUSTERED 
	(
		TimeSheetID
	)  ON PRIMARY 
GO

ALTER TABLE Users WITH NOCHECK ADD 
	CONSTRAINT PK_Users PRIMARY KEY  CLUSTERED 
	(
		UserID
	)  ON PRIMARY 
GO

---------------------------------------------------------------------
-- Foreign Key Relationships
---------------------------------------------------------------------
ALTER TABLE GroupProjects ADD 
	CONSTRAINT FK_GroupProjects_Groups FOREIGN KEY 
	(
		GroupID
	) REFERENCES Groups (
		GroupID
	),
	CONSTRAINT FK_GroupProjects_Projects FOREIGN KEY 
	(
		ProjectID
	) REFERENCES Projects (
		ProjectID
	)
GO

ALTER TABLE TimeSheetItems ADD 
	CONSTRAINT FK_TimeSheetItems_TimeSheets FOREIGN KEY 
	(
		TimeSheetID
	) REFERENCES TimeSheets (
		TimeSheetID
	)
GO

ALTER TABLE TimeSheets ADD 
	CONSTRAINT FK_TimeSheets_Users FOREIGN KEY 
	(
		UserID
	) REFERENCES Users (
		UserID
	),
	CONSTRAINT FK_TimeSheets_Users1 FOREIGN KEY 
	(
		ManagerID
	) REFERENCES Users (
		UserID
	)
GO

ALTER TABLE Users ADD 
	CONSTRAINT FK_Users_Groups FOREIGN KEY 
	(
		GroupID
	) REFERENCES Groups (
		GroupID
	),
	CONSTRAINT FK_Users_Roles FOREIGN KEY 
	(
		RoleID
	) REFERENCES Roles (
		RoleID
	),
	CONSTRAINT FK_Users_Users FOREIGN KEY 
	(
		ManagerID
	) REFERENCES Users (
		UserID
	)
GO