bd.sql

START TRANSACTION;


CREATE TABLE session(
   sessionSer BIGINT,
   SessionNum INT,
   Comment VARCHAR(254),
   HstryUserName VARCHAR(32),
   HstryTimeStamp TIMESTAMP,
   HstryDateTime TIMESTAMP,
   HstryTaskName VARCHAR(32),
   CourseSer BIGINT,
   CONSTRAINT pk_session primary key(sessionSer)
);

CREATE TABLE SessionProcedurePart(
   RadiationSer BIGINT,
   ImageType VARCHAR(32),
   SequenceNumber INT,
   SessionProcedurePartSer BIGINT,
   AcqAdjustment FLOAT,
   AutoSave INT,
   DoseAccumulation INT,
   Continuous INT,
   BeamOff INT,
   DeviationImage INT,
   DevEnergy INT,
   DevDoseRate INT,
   DevGeometry INT,
   JawState FLOAT,
   DevCollX1 FLOAT,
   DevCollX2 FLOAT,
   DevCollY1 FLOAT,
   DevCollY2 FLOAT,
   MUSubtraction INT,
   AcquisitionMode VARCHAR(64),
   HstryUserName VARCHAR(32),
   HstryTimeStamp TIMESTAMP,
   HstryDateTime TIMESTAMP,
   HstryTaskName VARCHAR(32),
   SessionProcedureSer BIGINT,
   ImageModality VARCHAR(16),
   RTPlanSer BIGINT,
	CONSTRAINT pk_SessionProcedurePart primary key(SessionProcedurePartSer)
);

CREATE TABLE Course(
   CourseSer BIGINT,
   PatientSer BIGINT,
   CourseId VARCHAR(16),
   StartDateTime TIMESTAMP,
   ClinicalStatus VARCHAR(16),
   CompletedByUserName VARCHAR(32),
   CompletedDateTime TIMESTAMP,
   Comment VARCHAR(254),
   ClinicalProtocolDir VARCHAR(254),
   HstryUserName VARCHAR(32) not null,
   HstryTimeStamp TIMESTAMP,
   HstryDateTime TIMESTAMP,
   HstryTaskName VARCHAR(32),
   TransactionId VARCHAR(255),
   CONSTRAINT pk_Course primary key(CourseSer)
);

ALTER TABLE session ADD CONSTRAINT fk_session_1 FOREIGN KEY(CourseSer) REFERENCES Course(CourseSer);
ALTER TABLE SessionProcedurePart ADD CONSTRAINT fk_SessionProcedurePart_2 FOREIGN KEY(SessionProcedurePartSer) REFERENCES session(sessionSer);
ALTER TABLE SessionProcedurePart ADD CONSTRAINT unique_SessionProcedurePart UNIQUE(SessionProcedurePartSer);

COMMIT;