CREATE TABLE Patient 
(
	PatientName varchar (64) DEFAULT '' ,
	PatientID varchar (64) NOT NULL,
	PatientBirthDate varchar (64)  DEFAULT '',
	PatientBirthTime varchar (64)DEFAULT ''  ,
	PatientSex varchar (64) DEFAULT '' ,	
	OtherPatientID varchar (64) DEFAULT '' ,
	OtherPatientNames varchar (64) DEFAULT '' ,
	EthnicGroup varchar (16) DEFAULT '' ,
	PatientComments varchar (100) DEFAULT '',
	NoOfPatientRelatedStudies varchar (12) DEFAULT '',
	NoOfPatientRelatedSeries varchar (12) DEFAULT '',
	NoOfPatientRelatedInstances varchar (12) DEFAULT ''
);

CREATE TABLE PatientSQInfo
(
	PatientID varchar (64) NOT NULL,
	SequenceID varchar (64) NOT NULL
);


CREATE TABLE Study 
(
	PatientID varchar (64) NOT NULL,
	StudyInstanceUID varchar (64) NOT NULL ,
	StudyDate varchar (64) DEFAULT '' ,
	StudyTime varchar (64) DEFAULT '' ,
	AccessionNumber varchar (64) DEFAULT '' ,
	StudyID varchar (64) DEFAULT '' ,
	Modality varchar (64) DEFAULT '',
	SOPClasses varchar(100) DEFAULT '',
	ReferringPhysiciansName varchar (64) DEFAULT '' ,	
	StudyDescription varchar (64)  DEFAULT '',	
	NameOfPhysiciansReadingStudy varchar(100) DEFAULT '',
	AdmittingDiagnosesDescription varchar(100) DEFAULT '',	
	IssuerOfPatientId varchar (64) DEFAULT '',
	PatientAge varchar (64) DEFAULT '' ,
	PatientSize varchar (64) DEFAULT '',
	PatientWeight varchar (64)  DEFAULT '',
	Occupation varchar (16)  DEFAULT '',
	AdditionalPatientHistory varchar(100) DEFAULT '',
	OtherStudyNumbers varchar(100) DEFAULT '',
	NoOfStudyRelatedSeries  varchar(12) DEFAULT '',
	NoOfStudyRelatedInstances varchar(12) DEFAULT '',
	InterpretationAuthor varchar(64) DEFAULT ''	
);

CREATE TABLE StudySQInfo
(
	StudyInstanceUID varchar (64) NOT NULL,
	SequenceID varchar (64) NOT NULL
);

CREATE TABLE SQInfo
(
	SequenceName varchar (64) NOT NULL,
	SequenceID varchar (64) NOT NULL
);

CREATE TABLE Series
(
	Modality varchar (64)  DEFAULT '',
	SeriesInstanceUID varchar (64) NOT NULL ,
	SeriesNumber varchar (64)  DEFAULT '',
	SeriesDate varchar (64)  DEFAULT '',
	SeriesTime varchar (64)  DEFAULT '',
	SeriesDescription varchar (64)  DEFAULT '',
	PatientID varchar (64) NOT NULL,
	StudyInstanceUID varchar (64) NOT NULL ,
	NoOfSeriesRelatedInstances  varchar (12)  DEFAULT ''
);

CREATE TABLE Image
(
	PatientName varchar(64) DEFAULT '',
	PatientID varchar(64) NOT NULL,
	PatientBirthDate varchar(64) DEFAULT '',
	PatientSex varchar(64) DEFAULT '',
	StudyInstanceUID varchar (64) NOT NULL,
	StudyDate varchar(64) DEFAULT '',
	StudyTime varchar(64) DEFAULT '',
	StudyID varchar (64) DEFAULT '',
	AccessionNumber varchar(64) DEFAULT '',
	PatientAge varchar (64) DEFAULT '',
	Modality varchar(64) DEFAULT '',
	SeriesInstanceUID varchar(64) NOT NULL,
	SeriesNumber varchar (64) DEFAULT '',
	ImageNumber varchar (64) DEFAULT '',
	ImageComments varchar(100) DEFAULT '',
	SOPClassUID varchar (64) DEFAULT '',
	SOPInstanceUID varchar(64) NOT NULL,
	ImageType varchar(64) DEFAULT '' ,
	FilePath varchar(256) DEFAULT '',
	OverlayNumber varchar(12) DEFAULT '',
	CurveNumber varchar(12) DEFAULT '',
	LUTNumber varchar(12) DEFAULT '',
	RelatedGeneralSOPClassUID varchar(64) DEFAULT ''
);

CREATE TABLE SQDEINFO
(
	DERowID varchar (64) NOT NULL,
	SequenceID varchar (64) NOT NULL,
	VRType integer NOT NULL,
	GroupNumber integer NOT NULL,
	ElementNumber integer NOT NULL,
	IsSQ varchar (64) NOT NULL,
	ParentSQUID varchar (64) DEFAULT '',
	Value varchar (64) DEFAULT '',
	SQUUID varchar (64) DEFAULT '',
	ItemUUID varchar (64) DEFAULT ''
);

ALTER TABLE SQDEINFO ADD 
	CONSTRAINT SQDEINFO_PK PRIMARY KEY 
	(
		DERowID
	);


ALTER TABLE Patient ADD 
	CONSTRAINT Patient_PK PRIMARY KEY 
	(
		PatientID
	);

ALTER TABLE Study ADD 
	CONSTRAINT Study_PK PRIMARY KEY  
	(
		StudyInstanceUID,PatientID
	);

ALTER TABLE Series ADD 
	CONSTRAINT Series_PK PRIMARY KEY  
	(
		SeriesInstanceUID,StudyInstanceUID,PatientID
	);
	
ALTER TABLE Image ADD 
	CONSTRAINT Image_PK PRIMARY KEY 
	(
		SOPInstanceUID,SeriesInstanceUID,StudyInstanceUID,PatientID
	);
	
ALTER TABLE Study ADD 
	CONSTRAINT Patient_Study_FK00 FOREIGN KEY 
	(
		PatientID
	) REFERENCES Patient (
		PatientID
	)on delete cascade;
	
ALTER TABLE Series ADD 
	CONSTRAINT Study_Series_FK00 FOREIGN KEY 
	(
		StudyInstanceUID,PatientID
	) REFERENCES Study (
		StudyInstanceUID,PatientID
	)on delete cascade;	

ALTER TABLE Image ADD 
	CONSTRAINT Series_Image_FK00 FOREIGN KEY 
	(
		SeriesInstanceUID,StudyInstanceUID,PatientID
	) REFERENCES Series (
		SeriesInstanceUID,StudyInstanceUID,PatientID
	)on delete cascade;