gistfile1.sql

CREATE TABLE dae.dbo.stimuls(
  id INT IDENTITY,
  stimul NVARCHAR(100) NOT NULL,
  CONSTRAINT PK_stimuls PRIMARY KEY (id)
)
GO

gistfile2.sql

CREATE TABLE dae.dbo.speciality(
  id INT IDENTITY,
  name VARCHAR(50) NOT NULL,
  CONSTRAINT PK_speciality PRIMARY KEY (id)
)
GO

gistfile3.sql

CREATE TABLE dae.dbo.experiment_stimuls(
  id INT IDENTITY,
  experiment_id INT NOT NULL,
  stimul_id INT NOT NULL,
  CONSTRAINT PK_experiment_stimuls PRIMARY KEY (id),
  CONSTRAINT FK_experiment_stimuls_experiments_id FOREIGN KEY (experiment_id) REFERENCES dbo.experiments (id),
  CONSTRAINT FK_experiment_stimuls_stimuls_id FOREIGN KEY (stimul_id) REFERENCES dbo.stimuls (id)
)
GO

gistfile4.sql

CREATE TABLE dae.dbo.reactions(
  id INT IDENTITY,
  experiment INT NOT NULL,
  stimul INT NOT NULL,
  reaction NVARCHAR(250) NOT NULL,
  speciality INT NULL,
  gender NCHAR(1) NULL,
  age TINYINT NULL,
  CONSTRAINT PK_reactions PRIMARY KEY (id),
  CONSTRAINT FK_reactions_experiments_id FOREIGN KEY (experiment) REFERENCES dbo.experiments (id),
  CONSTRAINT FK_reactions_speciality_id FOREIGN KEY (speciality) REFERENCES dbo.speciality (id),
  CONSTRAINT FK_reactions_stimuls_id FOREIGN KEY (stimul) REFERENCES dbo.stimuls (id)
)
GO

CREATE INDEX IX_reactions_experiment ON dae.dbo.reactions (experiment)
GO

gistfile5.sql

CREATE TABLE dae.dbo.experiments(
  id INT IDENTITY,
  is_enabled BIT NOT NULL DEFAULT (1),
  name NVARCHAR(50) NOT NULL,
  description NVARCHAR(200) NOT NULL DEFAULT (''),
  date DATETIME NOT NULL,
  stimuls_per_people INT NOT NULL DEFAULT (20),
  CONSTRAINT PK_experiments PRIMARY KEY (id)
)
GO