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.