JEMP/election create.sql

-- Last modification date: 2017-11-05 13:08:08.292 -- tables -- Table: ASSOCIATION CREATE TABLE ASSOCIATION ( ID int NOT NULL IDENTITY(1, 1), Name varchar(100) NOT NULL, CONSTRAINT ASSOCIATION_pk PRIMARY KEY (ID) ); -- Table: EVENT CREATE TABLE EVENT ( ID int NOT NULL IDENTITY(1, 1), Type_ID int NOT NULL, start_date datetime NOT NULL, end_date datetime NULL, Comment varchar(200) NULL, CONSTRAINT EVENT_pk PRIMARY KEY (ID) ); -- Table: EVENT_VOTE CREATE TABLE EVENT_VOTE ( ID int NOT NULL, Event_ID int NULL, User_ID int NULL, Votes int NOT NULL, CONSTRAINT EVENT_VOTE_pk PRIMARY KEY (ID) ); -- Table: LOG CREATE TABLE LOG ( ID int NOT NULL, System_ID int NOT NULL, User_ID int NULL, Date datetime NULL, Comment varchar(200) NOT NULL, CONSTRAINT LOG_pk PRIMARY KEY (ID) ); -- Table: PASSWORD CREATE TABLE PASSWORD ( ID int NOT NULL IDENTITY(1, 1), User_ID int NULL, Password varchar(65) NOT NULL, start_date datetime NOT NULL, end_date datetime NULL, CONSTRAINT PASSWORD_pk PRIMARY KEY (ID) ); -- Table: PERSON CREATE TABLE PERSON ( ID int NOT NULL IDENTITY(1, 1), Firstname varchar(30) NOT NULL, Lastname varchar(50) NOT NULL, Code varchar(15) NOT NULL, Born date NOT NULL, Sex char(2) NOT NULL, Address varchar(40) NULL, Phone varchar(20) NULL, Email varchar(40) NULL, CONSTRAINT PERSON_pk PRIMARY KEY (ID) ); -- Table: REGION CREATE TABLE REGION ( ID int NOT NULL IDENTITY(1, 1), Region_ID int NULL, Name varchar(60) NOT NULL, CONSTRAINT REGION_pk PRIMARY KEY (ID) ); -- Table: RIGHTS CREATE TABLE RIGHTS ( ID int NOT NULL IDENTITY(1, 1), Name varchar(10) NOT NULL, CONSTRAINT RIGHTS_pk PRIMARY KEY (ID) ); -- Table: ROLE CREATE TABLE ROLE ( ID int NOT NULL IDENTITY(1, 1), Name varchar(30) NOT NULL, CONSTRAINT ROLE_pk PRIMARY KEY (ID) ); -- Table: ROLE_RIGHTS CREATE TABLE ROLE_RIGHTS ( ID int NOT NULL, Role_ID int NOT NULL IDENTITY(1, 1), Rights_ID int NOT NULL, start_date datetime NOT NULL, end_date datetime NULL, CONSTRAINT ROLE_RIGHTS_pk PRIMARY KEY (ID) ); -- Table: SYSTEM CREATE TABLE SYSTEM ( ID int NOT NULL, System_ID int NULL, Type varchar(50) NOT NULL, CONSTRAINT SYSTEM_pk PRIMARY KEY (ID) ); -- Table: TYPE CREATE TABLE TYPE ( ID int NOT NULL IDENTITY(1, 1), Type_ID int NULL, Name varchar(50) NOT NULL, Code varchar(50) NULL, Adress varchar(200) NULL, Comment varchar(200) NULL, CONSTRAINT TYPE_pk PRIMARY KEY (ID) ); -- Table: USER CREATE TABLE "USER" ( ID int NOT NULL IDENTITY(1, 1), Person_ID int NOT NULL, Name varchar(20) NOT NULL, Created datetime NOT NULL, CONSTRAINT USER_pk PRIMARY KEY (ID) ); -- Table: USER_ASSOCIATION CREATE TABLE USER_ASSOCIATION ( ID int NOT NULL IDENTITY(1, 1), Association_ID int NOT NULL, User_ID int NULL, start_date datetime NOT NULL, end_date datetime NULL, CONSTRAINT USER_ASSOCIATION_pk PRIMARY KEY (ID) ); -- Table: USER_EVENT CREATE TABLE USER_EVENT ( ID int NOT NULL IDENTITY(1, 1), User_ID int NULL, Event_ID int NULL, Votes int NULL, start_date datetime NOT NULL, end_date datetime NULL, CONSTRAINT USER_EVENT_pk PRIMARY KEY (ID) ); -- Table: USER_REGION CREATE TABLE USER_REGION ( ID int NOT NULL IDENTITY(1, 1), User_ID int NULL, Region_ID int NOT NULL, start_date datetime NOT NULL, end_date datetime NULL, CONSTRAINT USER_REGION_pk PRIMARY KEY (ID) ); -- Table: USER_ROLE CREATE TABLE USER_ROLE ( ID int NOT NULL IDENTITY(1, 1), User_ID int NOT NULL, Role_ID int NOT NULL, start_date datetime NOT NULL, end_date datetime NULL, CONSTRAINT USER_ROLE_pk PRIMARY KEY (ID) ); -- Table: USER_VOTE CREATE TABLE USER_VOTE ( ID int NOT NULL IDENTITY(1, 1), User_ID int NULL, Uevent_ID int NULL, Voted bit NOT NULL, CONSTRAINT USER_VOTE_pk PRIMARY KEY (ID) ); -- foreign keys -- Reference: ALTERATION_ALTERATION (table: SYSTEM) ALTER TABLE SYSTEM ADD CONSTRAINT ALTERATION_ALTERATION FOREIGN KEY (System_ID) REFERENCES SYSTEM (ID); -- Reference: EVENT_TYPE (table: EVENT) ALTER TABLE EVENT ADD CONSTRAINT EVENT_TYPE FOREIGN KEY (Type_ID) REFERENCES TYPE (ID); -- Reference: EVENT_VOTE_EVENT (table: EVENT_VOTE) ALTER TABLE EVENT_VOTE ADD CONSTRAINT EVENT_VOTE_EVENT FOREIGN KEY (Event_ID) REFERENCES EVENT (ID); -- Reference: EVENT_VOTE_USER (table: EVENT_VOTE) ALTER TABLE EVENT_VOTE ADD CONSTRAINT EVENT_VOTE_USER FOREIGN KEY (User_ID) REFERENCES "USER" (ID); -- Reference: LOGIN_USER (table: LOG) ALTER TABLE LOG ADD CONSTRAINT LOGIN_USER FOREIGN KEY (User_ID) REFERENCES "USER" (ID); -- Reference: LOG_ALTERATION (table: LOG) ALTER TABLE LOG ADD CONSTRAINT LOG_ALTERATION FOREIGN KEY (System_ID) REFERENCES SYSTEM (ID); -- Reference: PASSWORD_USER (table: PASSWORD) ALTER TABLE PASSWORD ADD CONSTRAINT PASSWORD_USER FOREIGN KEY (User_ID) REFERENCES "USER" (ID); -- Reference: REGION_REGION (table: REGION) ALTER TABLE REGION ADD CONSTRAINT REGION_REGION FOREIGN KEY (Region_ID) REFERENCES REGION (ID); -- Reference: ROLE_RIGHTS_RIGHTS (table: ROLE_RIGHTS) ALTER TABLE ROLE_RIGHTS ADD CONSTRAINT ROLE_RIGHTS_RIGHTS FOREIGN KEY (Rights_ID) REFERENCES RIGHTS (ID); -- Reference: ROLE_RIGHTS_ROLE (table: ROLE_RIGHTS) ALTER TABLE ROLE_RIGHTS ADD CONSTRAINT ROLE_RIGHTS_ROLE FOREIGN KEY (Role_ID) REFERENCES ROLE (ID); -- Reference: TYPE_TYPE (table: TYPE) ALTER TABLE TYPE ADD CONSTRAINT TYPE_TYPE FOREIGN KEY (Type_ID) REFERENCES TYPE (ID); -- Reference: Table_6_EVENT (table: USER_EVENT) ALTER TABLE USER_EVENT ADD CONSTRAINT Table_6_EVENT FOREIGN KEY (Event_ID) REFERENCES EVENT (ID); -- Reference: Table_6_USER (table: USER_EVENT) ALTER TABLE USER_EVENT ADD CONSTRAINT Table_6_USER FOREIGN KEY (User_ID) REFERENCES "USER" (ID); -- Reference: USER_IN_ASSOCIATION_ASSOCIATION (table: USER_ASSOCIATION) ALTER TABLE USER_ASSOCIATION ADD CONSTRAINT USER_IN_ASSOCIATION_ASSOCIATION FOREIGN KEY (Association_ID) REFERENCES ASSOCIATION (ID); -- Reference: USER_IN_ASSOCIATION_USER (table: USER_ASSOCIATION) ALTER TABLE USER_ASSOCIATION ADD CONSTRAINT USER_IN_ASSOCIATION_USER FOREIGN KEY (User_ID) REFERENCES "USER" (ID); -- Reference: USER_IN_ROLE_ROLE (table: USER_ROLE) ALTER TABLE USER_ROLE ADD CONSTRAINT USER_IN_ROLE_ROLE FOREIGN KEY (Role_ID) REFERENCES ROLE (ID); -- Reference: USER_IN_ROLE_USER (table: USER_ROLE) ALTER TABLE USER_ROLE ADD CONSTRAINT USER_IN_ROLE_USER FOREIGN KEY (User_ID) REFERENCES "USER" (ID); -- Reference: USER_PERSON (table: USER) ALTER TABLE "USER" ADD CONSTRAINT USER_PERSON FOREIGN KEY (Person_ID) REFERENCES PERSON (ID); -- Reference: USER_REGION_REGION (table: USER_REGION) ALTER TABLE USER_REGION ADD CONSTRAINT USER_REGION_REGION FOREIGN KEY (Region_ID) REFERENCES REGION (ID); -- Reference: USER_REGION_USER (table: USER_REGION) ALTER TABLE USER_REGION ADD CONSTRAINT USER_REGION_USER FOREIGN KEY (User_ID) REFERENCES "USER" (ID); -- Reference: USER_VOTE_PARTICIPATIONS (table: USER_VOTE) ALTER TABLE USER_VOTE ADD CONSTRAINT USER_VOTE_PARTICIPATIONS FOREIGN KEY (Uevent_ID) REFERENCES USER_EVENT (ID); -- Reference: USER_VOTE_USER (table: USER_VOTE) ALTER TABLE USER_VOTE ADD CONSTRAINT USER_VOTE_USER FOREIGN KEY (User_ID) REFERENCES "USER" (ID); -- End of file.