Jahhundo at 18:13, 5 November 2017

2017-11-05T18:13:26Z

← Older revision		Revision as of 21:13, 5 November 2017
Line 1:		Line 1:
	<~~nowiki~~>		<pre>
	-- Last modification date: 2017-11-05 13:08:08.292		-- Last modification date: 2017-11-05 13:08:08.292

Line 285:		Line 285:

	-- End of file.		-- End of file.
	</~~nowiki~~>		</pre>

Jahhundo at 18:10, 5 November 2017

2017-11-05T18:10:57Z

← Older revision		Revision as of 21:10, 5 November 2017
Line 1:		Line 1:
	<nowiki>-- Last modification date: 2017-11-05 13:08:08.292		<nowiki>
			-- Last modification date: 2017-11-05 13:08:08.292

	-- tables		-- tables
Line 284:		Line 285:

	-- End of file.		-- End of file.
			</nowiki>

Jahhundo at 18:09, 5 November 2017

2017-11-05T18:09:51Z

← Older revision		Revision as of 21:09, 5 November 2017
Line 1:		Line 1:
	-- Last modification date: 2017-11-05 13:08:08.292		<nowiki>-- Last modification date: 2017-11-05 13:08:08.292

	-- tables		-- tables

Jahhundo: Created page with "-- 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..."

2017-11-05T18:03:21Z

Created page with "-- 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..."

New page

-- 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.

JEMP/election create.sql - Revision history

Jahhundo at 18:13, 5 November 2017

Jahhundo at 18:10, 5 November 2017

Jahhundo at 18:09, 5 November 2017

Jahhundo: Created page with "-- 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..."