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.