-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathOOD.ddl
44 lines (44 loc) · 5.56 KB
/
OOD.ddl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE TABLE [User] (ID int IDENTITY NOT NULL, UserID2 int NOT NULL, UserID int NOT NULL, Username varchar(255) NULL, Password varchar(255) NULL, Email varchar(255) NULL, Approved bit NOT NULL, CreatorUser int NULL, Discriminator varchar(255) NOT NULL, PRIMARY KEY (ID));
CREATE TABLE Job (ID int IDENTITY NOT NULL, UserID int NOT NULL, Discriminator varchar(255) NOT NULL, PRIMARY KEY (ID));
CREATE TABLE Specialty (ID int IDENTITY NOT NULL, JobID2 int NOT NULL, JobID int NULL, Title varchar(255) NULL, ProficiencyLevel int NOT NULL, PRIMARY KEY (ID));
CREATE TABLE Resource (ID2 int IDENTITY NOT NULL, DateID int NULL, ResourceStateID int NOT NULL, JobID4 int NULL, JobID3 int NULL, ResourceID23 int NULL, ResourceID22 int NULL, ResourceID2 int NULL, JobID2 int NOT NULL, JobID int NULL, Id int NOT NULL, Name2 varchar(255) NULL, Attribute int NULL, State2 int NULL, Name varchar(255) NULL, State int NULL, Description varchar(255) NULL, StartDate datetime NULL, EndDate datetime NULL, LinesOfCode int NULL, Technologies int NULL, Budget int NULL, Programming int NULL, FinancialValue int NULL, Specs int NULL, Discriminator varchar(255) NOT NULL, PRIMARY KEY (ID2));
CREATE TABLE Requirement (ID int IDENTITY NOT NULL, RequirementPriorityID int NOT NULL, JobID int NOT NULL, ResourceID2 int NOT NULL, [Allocation ID] int NULL, ResourceName varchar(255) NULL, ResourceType varchar(255) NULL, Quantity int NULL, StartDate datetime NULL, EndDate datetime NULL, Priority int NULL, ProjectManager int NULL, PRIMARY KEY (ID));
CREATE TABLE ModuleChange (ID int IDENTITY NOT NULL, DateID int NOT NULL, JobID int NOT NULL, ResourceID2 int NOT NULL, Description varchar(255) NULL, StartDate datetime NULL, EndDate datetime NULL, PRIMARY KEY (ID));
CREATE TABLE SystemConfiguration (ID int IDENTITY NOT NULL, UserID int NOT NULL, [Date] datetime NULL, BackupPreiodDays int NOT NULL, BackupFormat int NULL, PRIMARY KEY (ID));
CREATE TABLE Allocation (ID int IDENTITY NOT NULL, JobID int NOT NULL, ResourceID2 int NOT NULL, Requirement int NULL, Allocated bit NOT NULL, PRIMARY KEY (ID));
CREATE TABLE Notification (ID int IDENTITY NOT NULL, JobID int NOT NULL, Content varchar(255) NULL, PRIMARY KEY (ID));
CREATE TABLE ResourceState (ID int IDENTITY NOT NULL, PRIMARY KEY (ID));
CREATE TABLE Email (ID int IDENTITY NOT NULL, UserID int NOT NULL, Content varchar(255) NULL, Address varchar(255) NULL, PRIMARY KEY (ID));
CREATE TABLE UserJob (ID int IDENTITY NOT NULL, JobID int NOT NULL, UserID int NOT NULL, [User] int NULL, Job int NULL, PRIMARY KEY (ID));
CREATE TABLE ResourceAllocation (ID int IDENTITY NOT NULL, ResourceID2 int NOT NULL, [Allocation ID] int NOT NULL, Resource int NULL, Allocation int NULL, PRIMARY KEY (ID));
CREATE TABLE Resource_Specialty (ResourceID2 int NOT NULL, SpecialtyID int NOT NULL, PRIMARY KEY (ResourceID2, SpecialtyID));
ALTER TABLE Resource ADD CONSTRAINT has FOREIGN KEY (JobID) REFERENCES Job (ID);
ALTER TABLE Resource ADD CONSTRAINT adds FOREIGN KEY (JobID2) REFERENCES Job (ID);
ALTER TABLE Resource ADD CONSTRAINT has2 FOREIGN KEY (ResourceID2) REFERENCES Resource (ID2);
ALTER TABLE Resource ADD CONSTRAINT has3 FOREIGN KEY (ResourceID22) REFERENCES Resource (ID2);
ALTER TABLE Resource ADD CONSTRAINT has4 FOREIGN KEY (ResourceID23) REFERENCES Resource (ID2);
ALTER TABLE Resource_Specialty ADD CONSTRAINT needs FOREIGN KEY (ResourceID2) REFERENCES Resource (ID2);
ALTER TABLE Resource_Specialty ADD CONSTRAINT needs2 FOREIGN KEY (SpecialtyID) REFERENCES Specialty (ID);
ALTER TABLE Allocation ADD CONSTRAINT allocated FOREIGN KEY (ResourceID2) REFERENCES Resource (ID2);
ALTER TABLE Allocation ADD CONSTRAINT creates FOREIGN KEY (JobID) REFERENCES Job (ID);
ALTER TABLE Requirement ADD CONSTRAINT relatedTo FOREIGN KEY ([Allocation ID]) REFERENCES Allocation (ID);
ALTER TABLE ModuleChange ADD CONSTRAINT has5 FOREIGN KEY (ResourceID2) REFERENCES Resource (ID2);
ALTER TABLE SystemConfiguration ADD CONSTRAINT creates2 FOREIGN KEY (UserID) REFERENCES [User] (ID);
ALTER TABLE Job ADD CONSTRAINT creates3 FOREIGN KEY (UserID) REFERENCES [User] (ID);
ALTER TABLE Specialty ADD CONSTRAINT has6 FOREIGN KEY (JobID) REFERENCES Job (ID);
ALTER TABLE Resource ADD CONSTRAINT manages FOREIGN KEY (JobID3) REFERENCES Job (ID);
ALTER TABLE Specialty ADD CONSTRAINT creates4 FOREIGN KEY (JobID2) REFERENCES Job (ID);
ALTER TABLE Resource ADD CONSTRAINT changeState FOREIGN KEY (JobID4) REFERENCES Job (ID);
ALTER TABLE ModuleChange ADD CONSTRAINT creates5 FOREIGN KEY (JobID) REFERENCES Job (ID);
ALTER TABLE Requirement ADD CONSTRAINT needs3 FOREIGN KEY (ResourceID2) REFERENCES Resource (ID2);
ALTER TABLE Requirement ADD CONSTRAINT creates6 FOREIGN KEY (JobID) REFERENCES Job (ID);
ALTER TABLE [User] ADD CONSTRAINT approves FOREIGN KEY (UserID) REFERENCES [User] (ID);
ALTER TABLE Resource ADD CONSTRAINT has7 FOREIGN KEY (ResourceStateID) REFERENCES ResourceState (ID);
ALTER TABLE Email ADD CONSTRAINT sends FOREIGN KEY (UserID) REFERENCES [User] (ID);
ALTER TABLE [User] ADD CONSTRAINT creates7 FOREIGN KEY (UserID2) REFERENCES [User] (ID);
ALTER TABLE UserJob ADD CONSTRAINT FKUserJob15306 FOREIGN KEY (UserID) REFERENCES [User] (ID);
ALTER TABLE UserJob ADD CONSTRAINT FKUserJob322807 FOREIGN KEY (JobID) REFERENCES Job (ID);
ALTER TABLE ResourceAllocation ADD CONSTRAINT FKResourceAl209355 FOREIGN KEY ([Allocation ID]) REFERENCES Allocation (ID);
ALTER TABLE ResourceAllocation ADD CONSTRAINT FKResourceAl703895 FOREIGN KEY (ResourceID2) REFERENCES Resource (ID2);
ALTER TABLE Notification ADD CONSTRAINT FKNotificati374970 FOREIGN KEY (JobID) REFERENCES Job (ID);
ALTER TABLE Spec ADD CONSTRAINT AmGhezi FOREIGN KEY (ResourceID) REFERENCES Resource (ID2);