/*  -----------------------------------------------------
/*
/*  Group 13 View Creation SQL Script
/*  Laura Dippold, Lisa Gebreamlak, Huseyin Karaarslan
/*
/*  -----------------------------------------------------


/*  View 1 = This view connects Experiments and Participants  */

CREATE VIEW EXPERIMENT_PARTICIPANT AS
(
SELECT EP.EXPERIMENT_ExperimentID, EP.PARTICIPANTS_ParticipantID, EAT.ExperimentAttributeValue, P.FirstName, P.LastName, P.EmailAddress, P.WorkPhoneNumber, P.CellPhoneNumber, P.ShareInformation, OT.OrganizationType, PS.Status
	FROM
	EXPERIMENT_has_PARTICIPANTS EP 
	INNER JOIN EXPERIMENT E ON EP.EXPERIMENT_ExperimentID = E.ExperimentID
	INNER JOIN EXPERIMENT_TYPE ET ON E.Experiment_TYPE_ExperimentTypeID = ET.ExperimentTypeID
	INNER JOIN EXPERIMENT_ATTRIBUTE EAT ON E.Experiment_ATTRIBUTE_ExperimentAttributeID = EAT.ExperimentAttributeID
	INNER JOIN PARTICIPANTS P ON EP.PARTICIPANTS_ParticipantID = P.ParticipantID
	INNER JOIN PARTICIPANT_STATUS PS ON P.PARTICIPANT_STATUS_StatusID = PS.StatusID
	INNER JOIN ORGANIZATION_TYPE OT ON P.ORGANIZATION_TYPE_OrganizationTypeId = OT.OrganizationTypeID
    ORDER BY EP.EXPERIMENT_ExperimentID, EP.PARTICIPANTS_ParticipantID
    );
    
    
/*  View 2 = This view joins Experiments and Experiment Results and shows the values rather than IDs of the related information  */

CREATE VIEW EXPERIMENT_RESULTS_VIEW AS 
(
SELECT ERS.ExperimentResultID, E.ExperimentDescription, D.DemoValue, I.ImprovementValue, S.SensorPurpose, BS.BaseStationDescription, R.RfiValue, VT.VehicleType, V.VehicleName, ERS.Adhoc, ERS.Expert, ERS.Relevantone, ERS.Rfiadd, ERS.Strength,ERS.Strengthadd, ERS.Weak, ERS.Weakadd, ERS.Refinement, ERS.Newapproach, ERS.Reapplication, ERS.Collaborate, ERS.Dataplan, ERS.Prior, ERS.Additional, ERS.Imageq, ERS.Imageone
FROM EXPERIMENT_RESULTS ERS
INNER JOIN EXPERIMENT E ON ERS.EXPERIMENT_ExperimentID = E.ExperimentID
INNER JOIN DEMO D ON ERS.DEMO_DemoID = D.DemoID
INNER JOIN IMPROVEMENT I ON ERS.IMPROVEMENT_ImprovementID = I.ImprovementID
INNER JOIN SENSORS S ON ERS.SENSORS_SensorID = S.SensorId
INNER JOIN RFI R ON ERS.RFI_RfiID = R.RfiID
INNER JOIN VEHICLE V ON ERS.VEHICLE_VehicleID = V.VehicleID
INNER JOIN BASE_STATION BS ON S.BASE_STATION_BaseStationID = BS.BaseStationID
INNER JOIN VEHICLE_TYPE VT ON V.VEHICLE_TYPE_VehicleTypeID = VT.VehicleTypeID

ORDER BY ERS.ExperimentResultID
);