Tables: File, ResearchStudy, VisitResearchStudy, PatientStudy, SystemUsed, VisitSystemUsed

CREATE TABLE VisitSystemUsed(
systemUsedID INT,
visitID INT,
FOREIGN KEY (systemUsedID) references SystemUsed(systemUsedID),
FOREIGN KEY (visitID) references Visit(visitID)
);

INSERT INTO VisitSystemUsed(systemUsedID, visitID) VALUES (0300, 8), (0400, 9), (0200, 4);


ResearchStudy(IRBNum, name, description)
SystemUsed(systemUsedID , name)
 


 
CREATE TABLE ResearchStudy (
IRBNum INT(6),
name VARCHAR(30),
description VARCHAR(50),
PRIMARY KEY (IRBNum))
 
CREATE TABLE SystemUsed (
systemUsedID INT(4),
name VARCHAR(30) NOT NULL DEFAULT 'EMG-Surface',
PRIMARY KEY (systemUsedID))

INSERT INTO `mayo project`.`researchstudy` (`IRBNum`, `name`, `description`) VALUES ('000001', 'ResStudy1', 'Description1');
 
INSERT INTO `mayo project`.`researchstudy` (`IRBNum`, `name`, `description`) VALUES ('000002', 'ResStudy2', 'Description2');
 
INSERT INTO `mayo project`.`researchstudy` (`IRBNum`, `name`, `description`) VALUES ('000003', 'ResStudy3', 'Description3');




CREATE TABLE PatientStudy (
studyID INT,
visitID INT,
PRIMARY KEY(studyID, visitID),
FOREIGN KEY(studyID) REFERENCES Study(studyID),
FOREIGN KEY(visitID) REFERENCES Visit(visitID)
);


INSERT INTO PatientStudy(studyID, visitID) VALUES('1001','1');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1001','2');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1001','3');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1002','3');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1002','5');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1004','6');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1004','2');



CREATE TABLE File(
filename CHAR(25),
clinicNum INT (9),
PRIMARY KEY(filename),
FOREIGN KEY(clinicNum) REFERENCES Patient(clinicNum)
);
INSERT INTO File(filename, clinicNum) VALUES('file1.xml', 100000001);
INSERT INTO File(filename, clinicNum) VALUES('file2.xml', 100000002);
INSERT INTO File(filename, clinicNum) VALUES('file3.xml', 100000003);




CREATE TABLE VisitResearchStudy (
visitID INT,
IRBNum INT,
Foreign Key (visitID) references Visit(visitID)),
Foreign Key (IRBNum) references ResearchStudy(IRBNum));