BactImAs Database

In principle, you can use any database you like as long as you can connect (have a) JDBC driver for that database.
For the time being, we're using sqlite database, since it is light, portable, multi-platform and easy to install.
Below, you'll find both sqlite create database script, and Microsoft SQL Server database script.
Starting from those, it's easy to adjust the script for any SQL database server.

Note that you should not bother with creating an empty sqlite database, there is one included in the installation package.

Database diagram

(Views are shown in gray.)

SqLite Create Database Script

CREATE TABLE Version (
  version char(10) primary key
);
insert into Version(version) values ("v.0.7.");

CREATE TABLE [Bacteria] (
  "idBacteria"    integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "idExperiment"    integer,
  "bactName"    varchar(50) NOT NULL COLLATE NOCASE,
    FOREIGN KEY ([idExperiment])
        REFERENCES [Experiment]([idExperiment])
);
CREATE UNIQUE INDEX [Bacteria_unq_exp_bactName]
ON [Bacteria] ([idExperiment] DESC, [bactName] DESC);

CREATE TABLE [BacteriaSplit] (
  "idBacteriaParent"    integer NOT NULL,
  "idBacteriaChildA"    integer NOT NULL,
  "idBacteriaChildB"    integer NOT NULL,
  "frameNo"   integer NOT NULL,
    PRIMARY KEY ([idBacteriaParent]),
    FOREIGN KEY ([idBacteriaParent])
        REFERENCES [Bacteria]([idBacteria]),
    FOREIGN KEY ([idBacteriaChildA])
        REFERENCES [Bacteria]([idBacteria]),
    FOREIGN KEY ([idBacteriaChildB])
        REFERENCES [Bacteria]([idBacteria])
);
CREATE TABLE [Measurement] (
  "idMeasurement"   integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "measurementName"   nvarchar(20) NOT NULL COLLATE NOCASE
);
CREATE UNIQUE INDEX [Measurement_unq_measurementName] ON [Measurement] ([measurementName] DESC);

CREATE TABLE [RoiType] (
  "idRoiType"   smallint NOT NULL,
  "typeName"    varchar(20) NOT NULL COLLATE NOCASE,
    PRIMARY KEY ([idRoiType])
);
CREATE TABLE [Roi] (
  "idBacteria"    integer NOT NULL,
  "frameNo"   integer NOT NULL,
  "idRoiType"   smallint NOT NULL,
  "roiBlob"   blob,
  "roiName"   nvarchar(50) COLLATE NOCASE,
    PRIMARY KEY ([idBacteria], [frameNo], [idRoiType]),
    FOREIGN KEY ([idBacteria])
        REFERENCES [Bacteria]([idBacteria]),
    FOREIGN KEY ([idRoiType])
        REFERENCES [RoiType]([idRoiType])
);

CREATE TABLE [Frame] (
  "idFrame"   integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "idExperiment"    integer,
  "frameNo"   integer NOT NULL,
  "frameRedFileName"    nvarchar(500) COLLATE NOCASE,
  "frameGreenFileName"    nvarchar(500) COLLATE NOCASE,
  "frameBlueFileName"   nvarchar(500) COLLATE NOCASE,
  "transX"    smallint DEFAULT 0,
  "transY"    smallint DEFAULT 0,  
  ignoreFrame char(1) default 'n', 
  bgRedMean double,   
  bgGreenMean double, 
  bgBlueMean double, 
  Algorithm varchar(500),
    FOREIGN KEY ([idExperiment])
        REFERENCES [Experiment]([idExperiment])
);
CREATE INDEX idx_frame_id_experiment_frame_no on frame(idExperiment, frameNo);
CREATE TABLE [ExperimentMeasurements] (
  "idExperiment"    integer NOT NULL,
  "idMeasurement"   smallint NOT NULL,
  "idChannel"   smallint, collarSize int default 0,
    PRIMARY KEY ([idExperiment], [idMeasurement])
,
    FOREIGN KEY ([idExperiment])
        REFERENCES [Experiment]([idExperiment]),
    FOREIGN KEY ([idMeasurement])
        REFERENCES [Measurement]([idMeasurement])
);
CREATE TABLE [Channel] (
  "idChannel"   smallint NOT NULL,
  "channelName"   nvarchar(5) NOT NULL COLLATE NOCASE,
    PRIMARY KEY ([idChannel])

);
CREATE TABLE [Palette] (
  "idPalette"   smallint NOT NULL,
  "paletteName"   nvarchar(30) NOT NULL COLLATE NOCASE,
    PRIMARY KEY ([idPalette])

);
CREATE TABLE [PaletteColor] (
  "idPalette"   smallint NOT NULL,
  "ordinal"   smallint NOT NULL,
  "red"   smallint NOT NULL,
  "green"   smallint NOT NULL,
  "blue"    smallint NOT NULL,
    PRIMARY KEY ([idPalette], [ordinal])
);
CREATE TABLE [Experiment] (

  [idExperiment] integer  PRIMARY KEY AUTOINCREMENT NOT NULL,
  [expName] char(100)  NULL,
  [redMovieFileName] nvarchar(500)  NULL,
  [greenMovieFileName] nvarchar(500)  NULL,
  [blueMovieFileName] nvarchar(500)  NOT NULL,
  [movieSpf] INTEGER  NULL,
  [dateCreated] integer  NULL, 
  pixelWidthMicron decimal(6,2), 
  pixelHeightMicron decimal(6,2), 
  pictureScale decimal(6,2)
);

CREATE TABLE [BacteriaMeasurement] (
                "idBacteria"                       integer NOT NULL,
                "frameNo"                         integer NOT NULL,
                "idMeasurement"                          smallint NOT NULL,
                "idRoiType"                       smallint NOT NULL,
                "idChannel" smallint not null,
                "value"                float NOT NULL,
    PRIMARY KEY ([idBacteria], [frameNo], [idMeasurement], [idRoiType], idChannel),
    FOREIGN KEY ([idBacteria])
        REFERENCES [Bacteria]([idBacteria]),
    FOREIGN KEY ([idMeasurement])
        REFERENCES [Measurement]([idMeasurement]),
    FOREIGN KEY ([idChannel])
        REFERENCES [Channel]([idChannel])
);

CREATE TABLE BacteriaState (
        idState integer PRIMARY KEY,
        stateName varchar(50) not null,
        stateTag char(1) not null
);
CREATE TABLE BacteriaStateChange (
    idBacteria        integer ,
    frameNo           integer ,
    idState           integer ,
    PRIMARY KEY (idBacteria, frameNo, idState),
    FOREIGN KEY (idBacteria) REFERENCES Bacteria(idBacteria),
    FOREIGN KEY (idState) REFERENCES BacteriaState(idState)
);
CREATE TABLE ExperimentEvent (
    idExperiment  integer NOT NULL,
    frameNo       integer NOT NULL,
    eventDesc      varchar(255) not null,
    eventAbbr      varchar(15) not null, 
    PRIMARY KEY (idExperiment, frameNo),
    FOREIGN KEY (idExperiment) REFERENCES Experiment(idExperiment)
);


INSERT INTO Measurement(measurementName) VALUES ('area');
INSERT INTO Measurement(measurementName) VALUES ('mean');
INSERT INTO Measurement(measurementName) VALUES ('min');
INSERT INTO Measurement(measurementName) VALUES ('max');
INSERT INTO Measurement(measurementName) VALUES ('x');
INSERT INTO Measurement(measurementName) VALUES ('y');
INSERT INTO Measurement(measurementName) VALUES ('perim.');
INSERT INTO Measurement(measurementName) VALUES ('major');
INSERT INTO Measurement(measurementName) VALUES ('minor');
INSERT INTO Measurement(measurementName) VALUES ('angle');
INSERT INTO Measurement(measurementName) VALUES ('feret');
INSERT INTO Measurement(measurementName) VALUES ('kurt');
INSERT INTO Measurement(measurementName) VALUES ('slice');
INSERT INTO Measurement(measurementName) VALUES ('feretx');
INSERT INTO Measurement(measurementName) VALUES ('ferety');
INSERT INTO Measurement(measurementName) VALUES ('feretangle');
INSERT INTO Measurement(measurementName) VALUES ('minferet');
INSERT INTO Measurement(measurementName) VALUES ('length');
INSERT INTO Measurement(measurementName) VALUES ('width');
INSERT INTO Measurement(measurementName) VALUES ('rawintden');
INSERT INTO Measurement(measurementName) VALUES ('intden');
INSERT INTO Measurement(measurementName) VALUES ('stddev');
INSERT INTO Measurement(measurementName) VALUES ('height');
INSERT INTO Measurement(measurementName) VALUES ('median');

INSERT INTO Channel(idChannel, channelName) VALUES (1,'Red');
INSERT INTO Channel(idChannel, channelName) VALUES (2,'Green');
INSERT INTO Channel(idChannel, channelName) VALUES (3,'Blue');

insert into Palette(idPalette, paletteName) values (1, 'Default pallete');

INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,1,0,0,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,2,0,0,255);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,3,0,255,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,4,255,127,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,5,255,0,0);

insert into BacteriaState values (1, 'Death', '*');
insert into BacteriaState values (2, 'Ignore', '#');  


CREATE VIEW vBacteriaMeanMeasurement as 
SELECT idBacteria, frameNo, channelName, AVG(value) as mean, count(*) as cnt  -- if there are duplicate measuements on diff channels
  FROM BacteriaMeasurement
            JOIN Measurement
              ON BacteriaMeasurement.idMeasurement = Measurement.idMeasurement
             AND lower(measurementName) = 'mean'
            JOIN Channel
              ON BacteriaMeasurement.idChannel = Channel.idChannel
GROUP BY idBacteria, frameNo, channelName;

CREATE VIEW vBacteriaAreaMeasurement as
SELECT MArea.idBacteria, frameNo, pixelWidthMicron * pixelHeightMicron * AVG(value)/(pictureScale * pictureScale) as areaSquareMicron, AVG(value) as area, count(*) as cnt  -- if there are duplicate measuements on diff channels
  FROM BacteriaMeasurement AS MArea
            JOIN Measurement
              ON MArea.idMeasurement = measurement.idMeasurement
             AND lower(measurementName) = 'area'
            JOIN Bacteria
              ON MArea.idBacteria = Bacteria.idBacteria
            JOIN Experiment
              ON Bacteria.idExperiment = Experiment.idExperiment 
GROUP BY MArea.idBacteria, frameNo, pixelWidthMicron, pixelHeightMicron, pictureScale;




CREATE VIEW vBacteriaMeasurement AS
SELECT expName as exp_name,
       Bacteria.idExperiment AS id_experiment,
       DistBactMeasurement.frameNo AS frame_no,       
       DistBactMeasurement.frameNo * movieSpf AS time_s,       
       Bacteria.bactName AS bact_name,
       Bacteria.idBacteria AS id_bacteria, 

       length (Bacteria.bactName) AS generation,
       Birth.parentName     AS parent,       
       CASE WHEN (Bacteria.bactName = Birth.childAName) THEN Birth.childBName
            ELSE Birth.childAName
       END AS sibling,       
       -- Division.childAName || ', ' || Division.childBName AS progeny, 
       Division.childAName  AS progeny_a,
       Division.childBName  AS progeny_b,       

       (Birth.frameNo - 1) * movieSpf                AS birth_time,
       (Division.frameNo - 1) * movieSpf             AS division_time,
       (Division.frameNo - Birth.frameNo) * movieSpf AS interdivision_time,

       1.0 * (DivisionArea.areaSquareMicron/BirthArea.areaSquareMicron)/((Division.frameNo - Birth.frameNo) * movieSpf)     AS growth_rate,       
       1.0 * (DivisionArea.areaSquareMicron - BirthArea.areaSquareMicron)/((Division.frameNo - Birth.frameNo) * movieSpf)   AS growth_velocity,

       CurrArea.area,
       CurrArea.areaSquareMicron AS area_square_microns,
       CASE WHEN (PrevFrame.frameNo IS NULL) THEN 0
            ELSE CurrArea.areaSquareMicron - PrevArea.areaSquareMicron
       END AS area_square_microns_growth,


       CurrRedMean.Mean   AS red_mean,
       currGreenMean.Mean AS green_mean,
       currBlueMean.Mean  AS blue_mean,  

       Frame.bgRedMean   AS red_bg,
       Frame.bgGreenMean AS green_bg,
       Frame.bgBlueMean  AS blue_bg,

       (CurrRedMean.Mean   - Frame.bgRedMean)   AS red_mean_bg_cor,
       (currGreenMean.Mean - Frame.bgGreenMean) AS green_mean_bg_cor,
       (currBlueMean.Mean  - Frame.bgBlueMean)  AS blue_mean_bg_cor,

       (CurrRedMean.Mean   - Frame.bgRedMean)   * CurrArea.areaSquareMicron AS red_ctcf,
       (currGreenMean.Mean - Frame.bgGreenMean) * CurrArea.areaSquareMicron AS green_ctcf,
       (currBlueMean.Mean  - Frame.bgBlueMean)  * CurrArea.areaSquareMicron AS blue_ctcf,
       
       (CurrRedMean.Mean   - Frame.bgRedMean)   - (PrevRedMean.Mean   - PrevFrame.bgRedMean)   AS red_mean_bg_cor_change,
       (CurrGreenMean.Mean - Frame.bgGreenMean) - (PrevGreenMean.Mean - PrevFrame.bgGreenMean) AS green_mean_bg_cor_change,
       (CurrBlueMean.Mean  - Frame.bgBlueMean)  - (PrevBlueMean.Mean  - PrevFrame.bgBlueMean)  AS blue_mean_bg_cor_change,

       (CurrRedMean.Mean   - Frame.bgRedMean)   * CurrArea.areaSquareMicron - (PrevRedMean.Mean   - PrevFrame.bgRedMean)   * PrevArea.areaSquareMicron AS red_ctcf_change,
       (CurrGreenMean.Mean - Frame.bgGreenMean) * CurrArea.areaSquareMicron - (PrevGreenMean.Mean - PrevFrame.bgGreenMean) * PrevArea.areaSquareMicron AS green_ctcf_change,
       (CurrBlueMean.Mean  - Frame.bgBlueMean)  * CurrArea.areaSquareMicron - (PrevBlueMean.Mean  - PrevFrame.bgBlueMean)  * PrevArea.areaSquareMicron AS blue_ctcf_change
       
              


FROM      (SELECT DISTINCT idBacteria, frameNo FROM BacteriaMeasurement) AS DistBactMeasurement 
          JOIN Bacteria 
            ON DistBactMeasurement.idBacteria = bacteria.idBacteria
          JOIN Experiment 
            ON Bacteria.idExperiment = Experiment.idExperiment
          JOIN Frame
            ON Bacteria.idExperiment       = Frame.idExperiment
           AND DistBactMeasurement.frameNo = Frame.frameNo
           AND Frame.ignoreFrame = 'n'           
     LEFT JOIN Frame PrevFrame
            ON PrevFrame.idExperiment = Bacteria.idExperiment
           AND PrevFrame.frameNo = (SELECT MAX(frameNo) FROM Frame F WHERE F.idExperiment = PrevFrame.idExperiment AND F.frameNo < DistBactMeasurement.frameNo AND ignoreFrame = 'n')
     LEFT JOIN (
        SELECT BirthParent.bactName AS parentName, idBacteriaChildA, idBacteriaChildB, BirthChildA.bactName AS childAName, BirthChildB.bactName AS childBName, frameNo
          FROM BacteriaSplit 
                    JOIN Bacteria AS BirthParent ON BacteriaSplit.idBacteriaParent = BirthParent.idBacteria
                    JOIN Bacteria AS BirthChildA ON BacteriaSplit.idBacteriaChildA = BirthChildA.idBacteria
                    JOIN Bacteria AS BirthChildB ON BacteriaSplit.idBacteriaChildB = BirthChildB.idBacteria     
     ) AS Birth
        ON (DistBactMeasurement.idBacteria = Birth.idBacteriaChildA OR DistBactMeasurement.idBacteria = Birth.idBacteriaChildB)
     
     LEFT JOIN (
        SELECT idBacteriaParent, DivChildA.bactName AS childAName, DivChildB.bactName AS childBName, frameNo
          FROM BacteriaSplit                
                    JOIN Bacteria AS DivChildA ON BacteriaSplit.idBacteriaChildA = DivChildA.idBacteria
                    JOIN Bacteria AS DivChildB ON BacteriaSplit.idBacteriaChildB = DivChildB.idBacteria   
     ) AS Division
        ON (DistBactMeasurement.idBacteria = Division.idBacteriaParent)
     
     LEFT JOIN vBacteriaAreaMeasurement AS BirthArea
            ON Birth.frameNo                  = BirthArea.frameNo
           AND DistBactMeasurement.idBacteria = BirthArea.idBacteria

     LEFT JOIN vBacteriaAreaMeasurement AS DivisionArea
            ON DivisionArea.frameNo = ((SELECT MAX(frameNo) FROM Frame F WHERE F.idExperiment = Experiment.idExperiment AND F.frameNo < Division.frameNo AND ignoreFrame = 'n'))
           AND DivisionArea.idBacteria = DistBactMeasurement.idBacteria
     
     LEFT JOIN vBacteriaAreaMeasurement AS PrevArea
            ON PrevFrame.frameNo               = PrevArea.frameNo
           AND DistBactMeasurement.idBacteria  = PrevArea.idBacteria
     
     LEFT JOIN vBacteriaAreaMeasurement AS CurrArea
            ON DistBactMeasurement.frameNo    = CurrArea.frameNo
           AND DistBactMeasurement.idBacteria = CurrArea.idBacteria           
     --// Prev mean
     LEFT JOIN vBacteriaMeanMeasurement AS prevBlueMean
            ON PrevFrame.frameNo                 = prevBlueMean.frameNo
           AND DistBactMeasurement.idBacteria    = prevBlueMean.idBacteria           
           AND prevBlueMean.channelName = 'Blue'
     LEFT JOIN vBacteriaMeanMeasurement AS prevGreenMean
            ON PrevFrame.frameNo                 = prevGreenMean.frameNo
           AND DistBactMeasurement.idBacteria    = prevGreenMean.idBacteria
           AND prevGreenMean.channelName = 'Green'
     LEFT JOIN vBacteriaMeanMeasurement AS PrevRedMean
            ON PrevFrame.frameNo                 = PrevRedMean.frameNo
           AND DistBactMeasurement.idBacteria    = PrevRedMean.idBacteria
           AND PrevRedMean.channelName = 'Red'
    --// Curr mean
     LEFT JOIN vBacteriaMeanMeasurement AS currBlueMean
            ON DistBactMeasurement.frameNo      = currBlueMean.frameNo
           AND DistBactMeasurement.idBacteria   = currBlueMean.idBacteria           
           AND currBlueMean.channelName = 'Blue'
     LEFT JOIN vBacteriaMeanMeasurement AS currGreenMean
            ON DistBactMeasurement.frameNo      = currGreenMean.frameNo
           AND DistBactMeasurement.idBacteria   = currGreenMean.idBacteria
           AND currGreenMean.channelName = 'Green'
     LEFT JOIN vBacteriaMeanMeasurement AS CurrRedMean
            ON DistBactMeasurement.frameNo      = CurrRedMean.frameNo
           AND DistBactMeasurement.idBacteria   = CurrRedMean.idBacteria
           AND CurrRedMean.channelName = 'Red';

MS Sql Server Create Database Script

CREATE TABLE Version (
    version char(10) primary key
);
insert into Version(version) values ('v.0.7.');

CREATE TABLE [Experiment] (
    [idExperiment] integer  PRIMARY KEY identity(1000, 1) NOT NULL,
    [expName] char(100)  NULL,
    [redMovieFileName] nvarchar(500)  NULL,
    [greenMovieFileName] nvarchar(500)  NULL,
    [blueMovieFileName] nvarchar(500)  NOT NULL,
    [movieSpf] INTEGER  NULL,
    [dateCreated] integer  NULL, 
    pixelWidthMicron decimal(6,2), 
    pixelHeightMicron decimal(6,2), 
    pictureScale decimal(6,2)
);

CREATE TABLE [Bacteria] (
    idBacteria    integer PRIMARY KEY identity(1, 1) NOT NULL,
    idExperiment    integer,
    bactName    varchar(50) NOT NULL ,
    FOREIGN KEY ([idExperiment])
        REFERENCES [Experiment]([idExperiment])
);
CREATE UNIQUE INDEX [Bacteria_unq_exp_bactName]
ON [Bacteria] ([idExperiment] DESC, [bactName] DESC);

CREATE TABLE [BacteriaSplit] (
    idBacteriaParent    integer NOT NULL,
    idBacteriaChildA    integer NOT NULL,
    idBacteriaChildB    integer NOT NULL,
    frameNo   integer NOT NULL,
    PRIMARY KEY ([idBacteriaParent]),
    FOREIGN KEY ([idBacteriaParent])
        REFERENCES [Bacteria]([idBacteria]),
    FOREIGN KEY ([idBacteriaChildA])
        REFERENCES [Bacteria]([idBacteria]),
    FOREIGN KEY ([idBacteriaChildB])
        REFERENCES [Bacteria]([idBacteria])
);
CREATE TABLE [Measurement] (
    idMeasurement   integer PRIMARY KEY identity NOT NULL,
    measurementName   nvarchar(20) NOT NULL 
);
CREATE UNIQUE INDEX [Measurement_unq_measurementName] ON [Measurement] ([measurementName] DESC);
INSERT INTO Measurement(measurementName) VALUES ('area');
INSERT INTO Measurement(measurementName) VALUES ('mean');
INSERT INTO Measurement(measurementName) VALUES ('min');
INSERT INTO Measurement(measurementName) VALUES ('max');
INSERT INTO Measurement(measurementName) VALUES ('x');
INSERT INTO Measurement(measurementName) VALUES ('y');
INSERT INTO Measurement(measurementName) VALUES ('perim.');
INSERT INTO Measurement(measurementName) VALUES ('major');
INSERT INTO Measurement(measurementName) VALUES ('minor');
INSERT INTO Measurement(measurementName) VALUES ('angle');
INSERT INTO Measurement(measurementName) VALUES ('feret');
INSERT INTO Measurement(measurementName) VALUES ('kurt');
INSERT INTO Measurement(measurementName) VALUES ('slice');
INSERT INTO Measurement(measurementName) VALUES ('feretx');
INSERT INTO Measurement(measurementName) VALUES ('ferety');
INSERT INTO Measurement(measurementName) VALUES ('feretangle');
INSERT INTO Measurement(measurementName) VALUES ('minferet');
INSERT INTO Measurement(measurementName) VALUES ('length');
INSERT INTO Measurement(measurementName) VALUES ('width');
INSERT INTO Measurement(measurementName) VALUES ('rawintden');
INSERT INTO Measurement(measurementName) VALUES ('intden');
INSERT INTO Measurement(measurementName) VALUES ('stddev');
INSERT INTO Measurement(measurementName) VALUES ('height');
INSERT INTO Measurement(measurementName) VALUES ('median');



CREATE TABLE [RoiType] (
    idRoiType   smallint NOT NULL,
    typeName    varchar(20) NOT NULL ,
    PRIMARY KEY ([idRoiType])
);

insert into RoiType values (1, 'Human')
insert into RoiType values (2, 'Computer')

CREATE TABLE [Roi] (
    idBacteria    integer NOT NULL,
    frameNo   integer NOT NULL,
    idRoiType   smallint NOT NULL,
    roiBlob   VARBINARY (MAX),
    roiName   nvarchar(50) ,
    PRIMARY KEY ([idBacteria], [frameNo], [idRoiType]),
    FOREIGN KEY ([idBacteria])
        REFERENCES [Bacteria]([idBacteria]),
    FOREIGN KEY ([idRoiType])
        REFERENCES [RoiType]([idRoiType])
);

CREATE TABLE [Frame] (
    idFrame             integer PRIMARY KEY identity ,
    idExperiment        integer,
    frameNo             integer NOT NULL,
    frameRedFileName    nvarchar(500) ,
    frameGreenFileName  nvarchar(500) ,
    frameBlueFileName   nvarchar(500) ,
    transX              smallint DEFAULT 0,
    transY              smallint DEFAULT 0,  
    ignoreFrame         char(1) default 'n', 
    bgRedMean           float(53),   
    bgGreenMean         float(53), 
    bgBlueMean          float(53), 
    [Algorithm]         varchar(500),
    FOREIGN KEY ([idExperiment])
        REFERENCES [Experiment]([idExperiment])
);
CREATE INDEX idx_frame_id_experiment_frame_no on frame(idExperiment, frameNo)
CREATE TABLE [ExperimentMeasurements] (
    idExperiment    integer NOT NULL,
    idMeasurement   integer NOT NULL,
    idChannel       smallint, 
    collarSize      int default 0,
    PRIMARY KEY ([idExperiment], [idMeasurement]),
    FOREIGN KEY ([idExperiment])
        REFERENCES [Experiment]([idExperiment]),
    FOREIGN KEY ([idMeasurement])
        REFERENCES [Measurement]([idMeasurement])
);
CREATE TABLE [Channel] (
    idChannel     smallint NOT NULL,
    channelName   nvarchar(5) NOT NULL ,
    PRIMARY KEY ([idChannel])

);

INSERT INTO Channel(idChannel, channelName) VALUES (1,'Red');
INSERT INTO Channel(idChannel, channelName) VALUES (2,'Green');
INSERT INTO Channel(idChannel, channelName) VALUES (3,'Blue');

CREATE TABLE [Palette] (
    idPalette     smallint NOT NULL,
    paletteName   nvarchar(30) NOT NULL,
    PRIMARY KEY ([idPalette])

);

insert into Palette(idPalette, paletteName) values (1, 'Default pallete')

CREATE TABLE [PaletteColor] (
    idPalette smallint NOT NULL,
    ordinal   smallint NOT NULL,
    red       smallint NOT NULL,
    green     smallint NOT NULL,
    blue      smallint NOT NULL,
    PRIMARY KEY ([idPalette], [ordinal])
    FOREIGN KEY ([idPalette])
        REFERENCES [Palette]([idPalette])
);

INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,1,0,0,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,2,0,0,255);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,3,0,255,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,4,255,127,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,5,255,0,0);

CREATE TABLE [BacteriaMeasurement] (
    idBacteria        integer NOT NULL,
    frameNo           integer NOT NULL,
    idMeasurement     int  NOT NULL,
    idRoiType         smallint NOT NULL,
    idChannel         smallint not null,
    value             float NOT NULL,
    PRIMARY KEY ([idBacteria], [frameNo], [idMeasurement], [idRoiType], idChannel),
    FOREIGN KEY ([idBacteria])
        REFERENCES [Bacteria]([idBacteria]),
    FOREIGN KEY ([idMeasurement])
        REFERENCES [Measurement]([idMeasurement]),
    FOREIGN KEY ([idChannel])
        REFERENCES [Channel]([idChannel])
);

CREATE TABLE BacteriaState (
        idState integer PRIMARY KEY,
        stateName varchar(50) not null,
        stateTag char(1) not null
);

insert into BacteriaState values (1, 'Death', '*');
insert into BacteriaState values (2, 'Ignore', '#');


CREATE TABLE BacteriaStateChange (
    idBacteria        integer ,
    frameNo           integer ,
    idState           integer ,
    PRIMARY KEY (idBacteria, frameNo, idState),
    FOREIGN KEY (idBacteria) REFERENCES Bacteria(idBacteria),
    FOREIGN KEY (idState) REFERENCES BacteriaState(idState)
);
CREATE TABLE ExperimentEvent (
    idExperiment    integer NOT NULL,
    frameNo         integer NOT NULL,
    eventDesc       varchar(255) not null,
    eventAbbr       varchar(15) not null, 
    PRIMARY KEY (idExperiment, frameNo),
    FOREIGN KEY (idExperiment) REFERENCES Experiment(idExperiment)
);

CREATE VIEW vBacteriaMeanMeasurement as 
SELECT idBacteria, frameNo, channelName, AVG(value) as mean, count(*) as cnt  -- if there are duplicate measuements on diff channels
  FROM BacteriaMeasurement
            JOIN Measurement
              ON BacteriaMeasurement.idMeasurement = Measurement.idMeasurement
             AND lower(measurementName) = 'mean'
            JOIN Channel
              ON BacteriaMeasurement.idChannel = Channel.idChannel
GROUP BY idBacteria, frameNo, channelName;



CREATE VIEW vBacteriaAreaMeasurement as
SELECT MArea.idBacteria, frameNo, pixelWidthMicron * pixelHeightMicron * AVG(value)/(pictureScale * pictureScale) as areaSquareMicron, AVG(value) as area, count(*) as cnt  -- if there are duplicate measuements on diff channels
  FROM BacteriaMeasurement AS MArea
            JOIN Measurement
              ON MArea.idMeasurement = measurement.idMeasurement
             AND lower(measurementName) = 'area'
            JOIN Bacteria
              ON MArea.idBacteria = Bacteria.idBacteria
            JOIN Experiment
              ON Bacteria.idExperiment = Experiment.idExperiment 
GROUP BY MArea.idBacteria, frameNo, pixelWidthMicron, pixelHeightMicron, pictureScale;




CREATE VIEW vBacteriaMeasurement AS
SELECT expName as exp_name,
       Bacteria.idExperiment AS id_experiment,
       DistBactMeasurement.frameNo AS frame_no,       
       DistBactMeasurement.frameNo * movieSpf AS time_s,       
       Bacteria.bactName AS bact_name,
       Bacteria.idBacteria AS id_bacteria, 

       len (Bacteria.bactName) AS generation,
       Birth.parentName     AS parent,       
       CASE WHEN (Bacteria.bactName = Birth.childAName) THEN Birth.childBName
            ELSE Birth.childAName
       END AS sibling,       
       -- Division.childAName || ', ' || Division.childBName AS progeny, 
       Division.childAName  AS progeny_a,
       Division.childBName  AS progeny_b,       

       (Birth.frameNo - 1) * movieSpf                AS birth_time,
       (Division.frameNo - 1) * movieSpf             AS division_time,
       (Division.frameNo - Birth.frameNo) * movieSpf AS interdivision_time,

       1.0 * (DivisionArea.areaSquareMicron/BirthArea.areaSquareMicron)/((Division.frameNo - Birth.frameNo) * movieSpf)     AS growth_rate,       
       1.0 * (DivisionArea.areaSquareMicron - BirthArea.areaSquareMicron)/((Division.frameNo - Birth.frameNo) * movieSpf)   AS growth_velocity,

       CurrArea.area,
       CurrArea.areaSquareMicron AS area_square_microns,
       CASE WHEN (PrevFrame.frameNo IS NULL) THEN 0
            ELSE CurrArea.areaSquareMicron - PrevArea.areaSquareMicron
       END AS area_square_microns_growth,


       CurrRedMean.Mean   AS red_mean,
       currGreenMean.Mean AS green_mean,
       currBlueMean.Mean  AS blue_mean,  

       Frame.bgRedMean   AS red_bg,
       Frame.bgGreenMean AS green_bg,
       Frame.bgBlueMean  AS blue_bg,

       (CurrRedMean.Mean   - Frame.bgRedMean)   AS red_mean_bg_cor,
       (currGreenMean.Mean - Frame.bgGreenMean) AS green_mean_bg_cor,
       (currBlueMean.Mean  - Frame.bgBlueMean)  AS blue_mean_bg_cor,

       (CurrRedMean.Mean   - Frame.bgRedMean)   * CurrArea.areaSquareMicron AS red_ctcf,
       (currGreenMean.Mean - Frame.bgGreenMean) * CurrArea.areaSquareMicron AS green_ctcf,
       (currBlueMean.Mean  - Frame.bgBlueMean)  * CurrArea.areaSquareMicron AS blue_ctcf,
       
       (CurrRedMean.Mean   - Frame.bgRedMean)   - (PrevRedMean.Mean   - PrevFrame.bgRedMean)   AS red_mean_bg_cor_change,
       (CurrGreenMean.Mean - Frame.bgGreenMean) - (PrevGreenMean.Mean - PrevFrame.bgGreenMean) AS green_mean_bg_cor_change,
       (CurrBlueMean.Mean  - Frame.bgBlueMean)  - (PrevBlueMean.Mean  - PrevFrame.bgBlueMean)  AS blue_mean_bg_cor_change,

       (CurrRedMean.Mean   - Frame.bgRedMean)   * CurrArea.areaSquareMicron - (PrevRedMean.Mean   - PrevFrame.bgRedMean)   * PrevArea.areaSquareMicron AS red_ctcf_change,
       (CurrGreenMean.Mean - Frame.bgGreenMean) * CurrArea.areaSquareMicron - (PrevGreenMean.Mean - PrevFrame.bgGreenMean) * PrevArea.areaSquareMicron AS green_ctcf_change,
       (CurrBlueMean.Mean  - Frame.bgBlueMean)  * CurrArea.areaSquareMicron - (PrevBlueMean.Mean  - PrevFrame.bgBlueMean)  * PrevArea.areaSquareMicron AS blue_ctcf_change
       
              


FROM      (SELECT DISTINCT idBacteria, frameNo FROM BacteriaMeasurement) AS DistBactMeasurement 
          JOIN Bacteria 
            ON DistBactMeasurement.idBacteria = bacteria.idBacteria
          JOIN Experiment 
            ON Bacteria.idExperiment = Experiment.idExperiment
          JOIN Frame
            ON Bacteria.idExperiment       = Frame.idExperiment
           AND DistBactMeasurement.frameNo = Frame.frameNo
           AND Frame.ignoreFrame = 'n'           
     LEFT JOIN Frame PrevFrame
            ON PrevFrame.idExperiment = Bacteria.idExperiment
           AND PrevFrame.frameNo = (SELECT MAX(frameNo) FROM Frame F WHERE F.idExperiment = PrevFrame.idExperiment AND F.frameNo < DistBactMeasurement.frameNo AND ignoreFrame = 'n')
     LEFT JOIN (
        SELECT BirthParent.bactName AS parentName, idBacteriaChildA, idBacteriaChildB, BirthChildA.bactName AS childAName, BirthChildB.bactName AS childBName, frameNo
          FROM BacteriaSplit 
                    JOIN Bacteria AS BirthParent ON BacteriaSplit.idBacteriaParent = BirthParent.idBacteria
                    JOIN Bacteria AS BirthChildA ON BacteriaSplit.idBacteriaChildA = BirthChildA.idBacteria
                    JOIN Bacteria AS BirthChildB ON BacteriaSplit.idBacteriaChildB = BirthChildB.idBacteria     
     ) AS Birth
        ON (DistBactMeasurement.idBacteria = Birth.idBacteriaChildA OR DistBactMeasurement.idBacteria = Birth.idBacteriaChildB)
     
     LEFT JOIN (
        SELECT idBacteriaParent, DivChildA.bactName AS childAName, DivChildB.bactName AS childBName, frameNo
          FROM BacteriaSplit                
                    JOIN Bacteria AS DivChildA ON BacteriaSplit.idBacteriaChildA = DivChildA.idBacteria
                    JOIN Bacteria AS DivChildB ON BacteriaSplit.idBacteriaChildB = DivChildB.idBacteria   
     ) AS Division
        ON (DistBactMeasurement.idBacteria = Division.idBacteriaParent)
     
     LEFT JOIN vBacteriaAreaMeasurement AS BirthArea
            ON Birth.frameNo                  = BirthArea.frameNo
           AND DistBactMeasurement.idBacteria = BirthArea.idBacteria

     LEFT JOIN vBacteriaAreaMeasurement AS DivisionArea
            ON DivisionArea.frameNo = ((SELECT MAX(frameNo) FROM Frame F WHERE F.idExperiment = Experiment.idExperiment AND F.frameNo < Division.frameNo AND ignoreFrame = 'n'))
           AND DivisionArea.idBacteria = DistBactMeasurement.idBacteria
     
     LEFT JOIN vBacteriaAreaMeasurement AS PrevArea
            ON PrevFrame.frameNo               = PrevArea.frameNo
           AND DistBactMeasurement.idBacteria  = PrevArea.idBacteria
     
     LEFT JOIN vBacteriaAreaMeasurement AS CurrArea
            ON DistBactMeasurement.frameNo    = CurrArea.frameNo
           AND DistBactMeasurement.idBacteria = CurrArea.idBacteria           
     --// Prev mean
     LEFT JOIN vBacteriaMeanMeasurement AS prevBlueMean
            ON PrevFrame.frameNo                 = prevBlueMean.frameNo
           AND DistBactMeasurement.idBacteria    = prevBlueMean.idBacteria           
           AND prevBlueMean.channelName = 'Blue'
     LEFT JOIN vBacteriaMeanMeasurement AS prevGreenMean
            ON PrevFrame.frameNo                 = prevGreenMean.frameNo
           AND DistBactMeasurement.idBacteria    = prevGreenMean.idBacteria
           AND prevGreenMean.channelName = 'Green'
     LEFT JOIN vBacteriaMeanMeasurement AS PrevRedMean
            ON PrevFrame.frameNo                 = PrevRedMean.frameNo
           AND DistBactMeasurement.idBacteria    = PrevRedMean.idBacteria
           AND PrevRedMean.channelName = 'Red'
    --// Curr mean
     LEFT JOIN vBacteriaMeanMeasurement AS currBlueMean
            ON DistBactMeasurement.frameNo      = currBlueMean.frameNo
           AND DistBactMeasurement.idBacteria   = currBlueMean.idBacteria           
           AND currBlueMean.channelName = 'Blue'
     LEFT JOIN vBacteriaMeanMeasurement AS currGreenMean
            ON DistBactMeasurement.frameNo      = currGreenMean.frameNo
           AND DistBactMeasurement.idBacteria   = currGreenMean.idBacteria
           AND currGreenMean.channelName = 'Green'
     LEFT JOIN vBacteriaMeanMeasurement AS CurrRedMean
            ON DistBactMeasurement.frameNo      = CurrRedMean.frameNo
           AND DistBactMeasurement.idBacteria   = CurrRedMean.idBacteria
           AND CurrRedMean.channelName = 'Red';
Print/export
QR Code
QR Code db:database (generated for current page)