projecttimetracker oracle script.sql

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

---------------------------------------------------------------------
-- Tables
---------------------------------------------------------------------
CREATE TABLE Projects 
(
	ProjectID char(36) NOT NULL ,
	ProjectName varchar2(50) NOT NULL ,
	ProjectDescription clob NULL ,
	SequenceNumber number(3) NOT NULL ,
	LastUpdateDate date NOT NULL 
);

CREATE TABLE Groups
(
	GroupID char(36) NOT NULL ,
	GroupName varchar2(50) NOT NULL ,
	GroupDescription clob NULL ,
	LastUpdateDate date NOT NULL 
);

CREATE TABLE GroupProjects 
(
	GroupProjectID char(36) NOT NULL ,
	GroupID char(36) NOT NULL ,
	ProjectID char(36) NOT NULL 
);

CREATE TABLE Users 
(
	UserID char(36) NOT NULL ,
	LoginName varchar2(15) NOT NULL ,
	Password varchar2(30) NOT NULL ,
	FirstName varchar2(30) NOT NULL ,
	LastName varchar2(30) NOT NULL ,
	Email varchar2(50) NOT NULL ,
	Phone varchar2(20) NOT NULL ,
	Status number(1) NOT NULL ,
	GroupID char(36) NOT NULL ,
	RoleID char(36) NOT NULL ,
	ManagerID char(36) NULL ,
	LastUpdateDate date NOT NULL 
);

CREATE TABLE Roles 
(
	RoleID char(36) NOT NULL ,
	RoleName varchar2(50) NOT NULL ,
	RoleDescription clob NULL ,
	Ranking number(3) NOT NULL ,
	LastUpdateDate date NOT NULL 
);

CREATE TABLE TimeSheets 
(
	TimeSheetID char(36) NOT NULL ,
	UserID char(36) NOT NULL ,
	WeekEndingDate date NOT NULL ,
	Submitted number(1) NOT NULL ,
	ApprovalDate date NULL ,
	ManagerID char(36) NULL ,
	LastUpdateDate date NOT NULL 
);

CREATE TABLE TimeSheetItems 
(
	TimeSheetItemID char(36) NOT NULL ,
	TimeSheetID char(36) NOT NULL ,
	ProjectID char(36) NOT NULL ,
	Hours number(3) NOT NULL ,
	TimeSheetDate date NOT NULL 
);
/

---------------------------------------------------------------------
-- Primary Keys
---------------------------------------------------------------------
ALTER TABLE Projects ADD 
(
	CONSTRAINT PK_Projects PRIMARY KEY (ProjectID)
);	

ALTER TABLE Groups ADD 
(
	CONSTRAINT PK_Groups PRIMARY KEY (GroupID)
);

ALTER TABLE GroupProjects ADD 
(
	CONSTRAINT PK_GroupProjects PRIMARY KEY	(GroupProjectID)
);

ALTER TABLE Users ADD 
(
	CONSTRAINT PK_Users PRIMARY KEY (UserID)
);

ALTER TABLE Roles ADD 
(
	CONSTRAINT PK_Roles PRIMARY KEY (RoleID)
);

ALTER TABLE TimeSheets ADD 
(
	CONSTRAINT PK_TimeSheets PRIMARY KEY (TimeSheetID)
);

ALTER TABLE TimeSheetItems ADD 
(
	CONSTRAINT PK_TimeSheetItems PRIMARY KEY (TimeSheetItemID)
);
/

---------------------------------------------------------------------
-- Foreign Key Relationships
---------------------------------------------------------------------
ALTER TABLE GroupProjects ADD  
(
	FOREIGN KEY (GroupID) REFERENCES Groups (GroupID) ,
	FOREIGN KEY (ProjectID) REFERENCES Projects (ProjectID)
);

ALTER TABLE Users ADD 
(
	FOREIGN KEY (ManagerID) REFERENCES Users (UserID) ,
	FOREIGN KEY (GroupID) REFERENCES Groups (GroupID) ,
	FOREIGN KEY (RoleID) REFERENCES Roles (RoleID)
);

ALTER TABLE TimeSheets ADD 
(
	FOREIGN KEY (UserID) REFERENCES Users (UserID) ,
	FOREIGN KEY (ManagerID) REFERENCES Users (UserID)
);	

ALTER TABLE TimeSheetItems ADD 
(
	FOREIGN KEY (ProjectID) REFERENCES Projects (ProjectID) ,
	FOREIGN KEY (TimeSheetID) REFERENCES TimeSheets (TimeSheetID)
);
/