CondTools/IntegrationTest/onlinedb/DTDescriptionDB_create_tables7.sql - master - githubmirror/cmssw

CondTools/IntegrationTest/onlinedb/DTDescriptionDB_create_tables7.sql
421 строка13 KB
Giulio Eulisse
Snapshot of CMSSW_6_2_0_pre8.
26 июн 2013, 18:12
26 июн 2013, 18:12214ab73
CREATE TABLE DT_NUMBERING_MASTER
	(	NUMBERING_ID			NUMBER(8) NOT NULL,
		NUMBERING_VERSION		VARCHAR2(400),
		PRIMARY KEY (NUMBERING_ID)
	);

CREATE TABLE DT_WHEEL
	(	WHEEL_ID			NUMBER(1) NOT NULL,
		PRIMARY KEY (WHEEL_ID)
	);

CREATE TABLE DT_WHEEL_NUMBERING
	(	NUMBERING_ID			NUMBER(8) NOT NULL,
		WHEEL_ID			NUMBER(1) NOT NULL,
		OBJECT_NUMBER			NUMBER(8) NOT NULL,
		FOREIGN KEY (NUMBERING_ID) REFERENCES DT_NUMBERING_MASTER(NUMBERING_ID),
		FOREIGN KEY (WHEEL_ID) REFERENCES DT_WHEEL(WHEEL_ID)
	);

CREATE TABLE DT_SECTOR
	(	SECTOR_ID			NUMBER(3) NOT NULL,
		SECTOR_NUMBER			NUMBER(2) NOT NULL,
		WHEEL_ID			NUMBER(1) NOT NULL,
		CHAMBER_NUMBER			NUMBER(1) NOT NULL,
		PRIMARY KEY (SECTOR_ID),
		FOREIGN KEY (WHEEL_ID) REFERENCES DT_WHEEL(WHEEL_ID)
	);

CREATE TABLE DT_CHAMBER
	(	CHAMBER_ID			NUMBER(4) NOT NULL,
		CHAMBER_NUMBER			NUMBER(1) NOT NULL,
		SECTOR_ID			NUMBER(3) NOT NULL,
		SL_NUMBER			NUMBER(1) NOT NULL,
		ROB_NUMBER			NUMBER(1) NOT NULL,
		PRIMARY KEY (CHAMBER_ID),
		FOREIGN KEY (SECTOR_ID) REFERENCES DT_SECTOR(SECTOR_ID)
	);

CREATE TABLE DT_CHAMBER_NUMBERING
	(	NUMBERING_ID			NUMBER(8) NOT NULL,
		CHAMBER_ID			NUMBER(4) NOT NULL,
		STATION_NUMBER			NUMBER(8) NOT NULL,
		SECTOR_NUMBER			NUMBER(8) NOT NULL,
		FOREIGN KEY (NUMBERING_ID) REFERENCES DT_NUMBERING_MASTER(NUMBERING_ID),
		FOREIGN KEY (CHAMBER_ID) REFERENCES DT_CHAMBER(CHAMBER_ID)
	);

CREATE TABLE DT_SUPERLAYER
	(	SL_ID				NUMBER(6) NOT NULL,
		SL_NUMBER			NUMBER(1) NOT NULL,
		CHAMBER_ID			NUMBER(4) NOT NULL,
		FEB_NUMBER			NUMBER(2) NOT NULL,
		PRIMARY KEY (SL_ID),
		FOREIGN KEY (CHAMBER_ID) REFERENCES DT_CHAMBER(CHAMBER_ID)
	);

CREATE TABLE DT_SUPERLAYER_NUMBERING
	(	NUMBERING_ID			NUMBER(8) NOT NULL,
		SL_ID				NUMBER(6) NOT NULL,
		OBJECT_NUMBER			NUMBER(8) NOT NULL,
		FOREIGN KEY (NUMBERING_ID) REFERENCES DT_NUMBERING_MASTER(NUMBERING_ID),
		FOREIGN KEY (SL_ID) REFERENCES DT_SUPERLAYER(SL_ID)
	);

CREATE TABLE DT_LAYER
	(	LAYER_ID			NUMBER(6) NOT NULL,
		LAYER_NUMBER			NUMBER(1) NOT NULL,
		SL_ID				NUMBER(6) NOT NULL,
		CELL_NUMBER			NUMBER(2) NOT NULL,
		PRIMARY KEY (LAYER_ID),
		FOREIGN KEY (SL_ID) REFERENCES DT_SUPERLAYER(SL_ID)
	);

CREATE TABLE DT_LAYER_NUMBERING
	(	NUMBERING_ID			NUMBER(8) NOT NULL,
		LAYER_ID			NUMBER(6) NOT NULL,
		OBJECT_NUMBER			NUMBER(8) NOT NULL,
		FOREIGN KEY (NUMBERING_ID) REFERENCES DT_NUMBERING_MASTER(NUMBERING_ID),
		FOREIGN KEY (LAYER_ID) REFERENCES DT_LAYER(LAYER_ID)
	);

CREATE TABLE DT_CELL
	(	CELL_ID				NUMBER(8) NOT NULL,
		CELL_NUMBER			NUMBER(2) NOT NULL,
		LAYER_ID			NUMBER(6) NOT NULL,
		FE_POSITION			NUMBER(2) NOT NULL,
		HV_POSITION			NUMBER(2) NOT NULL,
		PRIMARY KEY (CELL_ID),
		FOREIGN KEY (LAYER_ID) REFERENCES DT_LAYER(LAYER_ID)
	);

CREATE TABLE DT_CELL_NUMBERING
	(	NUMBERING_ID			NUMBER(8) NOT NULL,
		CELL_ID				NUMBER(8) NOT NULL,
		OBJECT_NUMBER			NUMBER(8) NOT NULL,
		FOREIGN KEY (NUMBERING_ID) REFERENCES DT_NUMBERING_MASTER(NUMBERING_ID),
		FOREIGN KEY (CELL_ID) REFERENCES DT_CELL(CELL_ID)
	);

CREATE TABLE DT_FEB
	(	FEB_ID				NUMBER(7) NOT NULL,
		SL_ID				NUMBER(6) NOT NULL,
		CHANNEL_NUMBER			NUMBER(2) NOT NULL,
		PRIMARY KEY (FEB_ID),
		FOREIGN KEY (SL_ID) REFERENCES DT_SUPERLAYER(SL_ID)
	);

CREATE TABLE DT_FE_CHANNEL
	(	FE_CHANNEL_ID			NUMBER(8) NOT NULL,
		FE_CHANNEL_NUMBER		NUMBER(2) NOT NULL,
		FEB_ID				NUMBER(7) NOT NULL,
		CELL_ID				NUMBER(8) NOT NULL,
		PRIMARY KEY (FE_CHANNEL_ID),
		FOREIGN KEY (FEB_ID) REFERENCES DT_FEB(FEB_ID),
		FOREIGN KEY (CELL_ID) REFERENCES DT_CELL(CELL_ID)
	);

CREATE TABLE DT_TSM
	(	TSM_ID				NUMBER(4) NOT NULL,
		CHAMBER_ID			NUMBER(4) NOT NULL,
		PRIMARY KEY (TSM_ID),
		FOREIGN KEY (CHAMBER_ID) REFERENCES DT_CHAMBER(CHAMBER_ID)
	);

CREATE TABLE DT_TRB
	(	TRB_ID				NUMBER(6) NOT NULL,
		TRB_NUMBER			NUMBER(1) NOT NULL,
		TSM_ID				NUMBER(4) NOT NULL,
		TRACO_NUMBER			NUMBER(1) NOT NULL,
		PRIMARY KEY (TRB_ID),
		FOREIGN KEY (TSM_ID) REFERENCES DT_TSM(TSM_ID)
	);

CREATE TABLE DT_BTI
	(	BTI_ID				NUMBER(7) NOT NULL,
		BTI_NUMBER			NUMBER(1) NOT NULL,
		TRB_ID				NUMBER(6) NOT NULL,
		PRIMARY KEY (BTI_ID),
		FOREIGN KEY (TRB_ID) REFERENCES DT_TRB(TRB_ID)
	);

CREATE TABLE DT_TRACO
	(	TRACO_ID			NUMBER(4) NOT NULL,
		TRACO_NUMBER			NUMBER(1) NOT NULL,
		TRB_ID				NUMBER(4) NOT NULL,
		BTI_INNER			NUMBER(7) NOT NULL,
		BTI_OUTER_L			NUMBER(7),
		BTI_OUTER_C			NUMBER(7) NOT NULL,
		BTI_OUTER_R			NUMBER(7),
		PRIMARY KEY (TRACO_ID),
		FOREIGN KEY (TRB_ID) REFERENCES DT_TRB(TRB_ID),
		FOREIGN KEY (BTI_INNER) REFERENCES DT_BTI(BTI_ID),
		FOREIGN KEY (BTI_OUTER_L) REFERENCES DT_BTI(BTI_ID),
		FOREIGN KEY (BTI_OUTER_C) REFERENCES DT_BTI(BTI_ID),
		FOREIGN KEY (BTI_OUTER_R) REFERENCES DT_BTI(BTI_ID)
	);

CREATE TABLE DT_BTI_CHANNEL
	(	BTI_CHANNEL_ID			NUMBER(8) NOT NULL,
		BTI_CHANNEL_NUMBER		NUMBER(1) NOT NULL,
		CELL_ID				NUMBER(8) NOT NULL,
		BTI_ID				NUMBER(7) NOT NULL,
		PRIMARY KEY (BTI_CHANNEL_ID),
		FOREIGN KEY (BTI_ID) REFERENCES DT_BTI (BTI_ID),
		FOREIGN KEY (CELL_ID) REFERENCES DT_CELL (CELL_ID)
	);

CREATE TABLE DT_DDU
	(	DDU_ID				NUMBER(2) NOT NULL,
		DDU_NUMBER			NUMBER(1) NOT NULL,
		WHEEL_ID			NUMBER(1) NOT NULL,
		READ_OUT_NUMBER                 NUMBER(8) NOT NULL,
		PRIMARY KEY (DDU_ID),
		FOREIGN KEY (WHEEL_ID) REFERENCES DT_WHEEL(WHEEL_ID)
	);

CREATE TABLE DT_ROS
	(	ROS_ID				NUMBER(4) NOT NULL,
		ROS_NUMBER			NUMBER(2) NOT NULL,
		DDU_ID				NUMBER(2) NOT NULL,
		READ_OUT_NUMBER			NUMBER(8) NOT NULL,
		PRIMARY KEY (ROS_ID),
		FOREIGN KEY (DDU_ID) REFERENCES DT_DDU(DDU_ID)
	);

CREATE TABLE DT_ROS_CHANNEL
	(	ROS_CHANNEL_ID			NUMBER(6) NOT NULL,
		ROS_CHANNEL_NUMBER		NUMBER(2) NOT NULL,
		ROS_ID				NUMBER(4) NOT NULL,
		READ_OUT_NUMBER			NUMBER(8) NOT NULL,
		PRIMARY KEY (ROS_CHANNEL_ID),
		FOREIGN KEY (ROS_ID) REFERENCES DT_ROS(ROS_ID)
	);

CREATE TABLE DT_ROB
	(	ROB_ID				NUMBER(6) NOT NULL,
		ROB_NUMBER			NUMBER(1) NOT NULL,
		CHAMBER_ID			NUMBER(4) NOT NULL,
		TDC_NUMBER			NUMBER(1) NOT NULL,
		PRIMARY KEY (ROB_ID),
		FOREIGN KEY (CHAMBER_ID) REFERENCES DT_CHAMBER(CHAMBER_ID)
	);

CREATE TABLE DT_TDC
	(	TDC_ID				NUMBER(6) NOT NULL,
		TDC_NUMBER			NUMBER(1) NOT NULL,
		ROB_ID				NUMBER(6) NOT NULL,
		READ_OUT_NUMBER			NUMBER(8) NOT NULL,
		PRIMARY KEY (TDC_ID),
		FOREIGN KEY (ROB_ID) REFERENCES DT_ROB(ROB_ID)
	);

CREATE TABLE DT_TDC_CHANNEL
	(	TDC_CHANNEL_ID			NUMBER(8) NOT NULL,
		TDC_CHANNEL_NUMBER		NUMBER(2) NOT NULL,
		TDC_ID				NUMBER(6) NOT NULL,
		READ_OUT_NUMBER			NUMBER(8) NOT NULL,
		PRIMARY KEY (TDC_CHANNEL_ID),
		FOREIGN KEY (TDC_ID) REFERENCES DT_TDC(TDC_ID)
	);

CREATE TABLE DT_SY1527
	(	SY1527_ID			NUMBER(2) NOT NULL,
		PRIMARY KEY (SY1527_ID)
	);

CREATE TABLE DT_A876
	(	A876_ID				NUMBER(3) NOT NULL,
		A876_NUMBER			NUMBER(1) NOT NULL,
		SY1527_ID			NUMBER(2) NOT NULL,
		SECTOR_ID			NUMBER(3) NOT NULL,
		PRIMARY KEY (A876_ID),
		FOREIGN KEY (SY1527_ID) REFERENCES DT_SY1527(SY1527_ID),
		FOREIGN KEY (SECTOR_ID) REFERENCES DT_SECTOR(SECTOR_ID)
	);

CREATE TABLE DT_A877
	(	A877_ID				NUMBER(4) NOT NULL,
                A877_NUMBER			NUMBER(1) NOT NULL,
		A876_ID				NUMBER(3) NOT NULL,
		CHAMBER_ID			NUMBER(4) NOT NULL,
		PRIMARY KEY (A877_ID),
		FOREIGN KEY (A876_ID) REFERENCES DT_A876(A876_ID),
		FOREIGN KEY (CHAMBER_ID) REFERENCES DT_CHAMBER(CHAMBER_ID)
	);

CREATE TABLE DT_HV_CONNECTOR
	(	HV_CONNECTOR_ID			NUMBER(6) NOT NULL,
		HV_CONNECTOR_NUMBER		NUMBER(1) NOT NULL,
		A877_ID				NUMBER(4) NOT NULL,
		SL_ID				NUMBER(6) NOT NULL,
		PRIMARY KEY (HV_CONNECTOR_ID),
		FOREIGN KEY (A877_ID) REFERENCES DT_A877(A877_ID),
		FOREIGN KEY (SL_ID) REFERENCES DT_SUPERLAYER(SL_ID)
	);

CREATE TABLE DT_HV_CHANNEL
	(	HV_CHANNEL_ID			NUMBER(7) NOT NULL,
		HV_CHANNEL_NUMBER		NUMBER(2) NOT NULL,
		HV_CHANNEL_CODE			VARCHAR2(4) NOT NULL,
		HV_CONNECTOR_ID			NUMBER(6) NOT NULL,
		PRIMARY KEY (HV_CHANNEL_ID),
		FOREIGN KEY (HV_CONNECTOR_ID) REFERENCES DT_HV_CONNECTOR(HV_CONNECTOR_ID)
	);

CREATE TABLE DT_HV_CARD
	(	HV_CARD_ID			NUMBER(6) NOT NULL,
		HV_CARD_NUMBER			NUMBER(1) NOT NULL,
		SL_ID				NUMBER(6) NOT NULL,
		CELL_NUMBER			NUMBER(2) NOT NULL,
		PRIMARY KEY (HV_CARD_ID),
		FOREIGN KEY (SL_ID) REFERENCES DT_SUPERLAYER(SL_ID)
	);

CREATE TABLE DT_HV_CARD_SIDE
	(	HV_CARD_SIDE_ID			NUMBER(7) NOT NULL,
		HV_CARD_SIDE_NUMBER		NUMBER(1) NOT NULL,
		HV_CARD_ID			NUMBER(6) NOT NULL,
		HV_WIRE_CHANNEL_ID		NUMBER(7) NOT NULL,
		HV_STRIP_CHANNEL_ID		NUMBER(7) NOT NULL,
		HV_CATHODE_CHANNEL_ID		NUMBER(7) NOT NULL,
		LAYER_ID			NUMBER(6) NOT NULL,
		PRIMARY KEY (HV_CARD_SIDE_ID),
		FOREIGN KEY (HV_CARD_ID) REFERENCES DT_HV_CARD(HV_CARD_ID),
		FOREIGN KEY (LAYER_ID) REFERENCES DT_LAYER(LAYER_ID),
		FOREIGN KEY (HV_WIRE_CHANNEL_ID) REFERENCES DT_HV_CHANNEL(HV_CHANNEL_ID),
		FOREIGN KEY (HV_STRIP_CHANNEL_ID) REFERENCES DT_HV_CHANNEL(HV_CHANNEL_ID),
		FOREIGN KEY (HV_CATHODE_CHANNEL_ID) REFERENCES DT_HV_CHANNEL(HV_CHANNEL_ID)
	);

CREATE TABLE DT_HV_CARD_WIRE
	(	HV_CARD_WIRE_ID			NUMBER(8) NOT NULL,
		HV_CARD_SIDE_ID			NUMBER(8) NOT NULL,
		CELL_ID				NUMBER(8) NOT NULL,
		PRIMARY KEY (HV_CARD_WIRE_ID),
		FOREIGN KEY (HV_CARD_SIDE_ID) REFERENCES DT_HV_CARD_SIDE(HV_CARD_SIDE_ID),
		FOREIGN KEY (CELL_ID) REFERENCES DT_CELL(CELL_ID)
	);

CREATE TABLE DT_HV_CARD_STRIP
	(	HV_CARD_STRIP_ID		NUMBER(8) NOT NULL,
		HV_CARD_SIDE_ID			NUMBER(8) NOT NULL,
		CELL_ID				NUMBER(8) NOT NULL,
		PRIMARY KEY (HV_CARD_STRIP_ID),
		FOREIGN KEY (HV_CARD_SIDE_ID) REFERENCES DT_HV_CARD_SIDE(HV_CARD_SIDE_ID),
		FOREIGN KEY (CELL_ID) REFERENCES DT_CELL(CELL_ID)
	);

CREATE TABLE DT_HV_CARD_CATHODE
	(	HV_CARD_CATHODE_ID		NUMBER(8) NOT NULL,
		HV_CARD_SIDE_ID			NUMBER(8) NOT NULL,
		CELL_ID				NUMBER(8) NOT NULL,
		PRIMARY KEY (HV_CARD_CATHODE_ID),
		FOREIGN KEY (HV_CARD_SIDE_ID) REFERENCES DT_HV_CARD_SIDE(HV_CARD_SIDE_ID),
		FOREIGN KEY (CELL_ID) REFERENCES DT_CELL(CELL_ID)
	);

CREATE TABLE DT_IOV_MASTER
	(	IOV_ID				NUMBER(8) NOT NULL,
		VALID_FROM			DATE DEFAULT '01-JAN-1970' NOT NULL,
		VALID_UNTIL			DATE DEFAULT '31-DEC-2099' NOT NULL,
		IOV_VERSION			VARCHAR2(50),
		PRIMARY KEY (IOV_ID)
	);

CREATE TABLE DT_FEB_TEMPERATURE
	(	FEB_ID				NUMBER(7) NOT NULL,
		IOV_ID				NUMBER NOT NULL,
		TEMPERATURE			NUMBER(4) NOT NULL,
                PRIMARY KEY (FEB_ID, IOV_ID),
		FOREIGN KEY (FEB_ID) REFERENCES DT_FEB(FEB_ID),
		FOREIGN KEY (IOV_ID) REFERENCES DT_IOV_MASTER(IOV_ID)
	);

CREATE TABLE DT_MEAN_FEB_TEMPERATURE
	(	SL_ID				NUMBER(6) NOT NULL,
		IOV_ID				NUMBER(8) NOT NULL,
		MEAN_TEMPERATURE		NUMBER(4) NOT NULL,
                PRIMARY KEY (SL_ID, IOV_ID),
		FOREIGN KEY (SL_ID) REFERENCES DT_SUPERLAYER(SL_ID),
		FOREIGN KEY (IOV_ID) REFERENCES DT_IOV_MASTER(IOV_ID)
	);

CREATE TABLE DT_TRB_TEMPERATURE
	(	TRB_ID				NUMBER(6) NOT NULL,
		IOV_ID				NUMBER(8) NOT NULL,
		TEMPERATURE			NUMBER(4) NOT NULL,
                PRIMARY KEY (TRB_ID, IOV_ID),
		FOREIGN KEY (TRB_ID) REFERENCES DT_TRB(TRB_ID),
		FOREIGN KEY (IOV_ID) REFERENCES DT_IOV_MASTER(IOV_ID)
	);

CREATE TABLE DT_CHAMBER_PRESSURE
	(	CHAMBER_ID			NUMBER(4) NOT NULL,
		IOV_ID				NUMBER(8) NOT NULL,
		PRESSURE_1			NUMBER(4) NOT NULL,
		PRESSURE_2			NUMBER(4) NOT NULL,
                PRIMARY KEY (CHAMBER_ID, IOV_ID),
		FOREIGN KEY (CHAMBER_ID) REFERENCES DT_CHAMBER(CHAMBER_ID),
		FOREIGN KEY (IOV_ID) REFERENCES DT_IOV_MASTER(IOV_ID)
	);

CREATE TABLE DT_HV_VALUES
	(	HV_CHANNEL_ID			NUMBER(7) NOT NULL,
		IOV_ID				NUMBER(8) NOT NULL,
		VOLTAGE_VALUE			NUMBER(6) NOT NULL,
		CURRENT_VALUE			NUMBER(6) NOT NULL,
                PRIMARY KEY (HV_CHANNEL_ID, IOV_ID),
		FOREIGN KEY (HV_CHANNEL_ID) REFERENCES DT_HV_CHANNEL(HV_CHANNEL_ID)
	);

CREATE TABLE DT_TEST_PULSE
	(	CELL_ID				NUMBER(8) NOT NULL,
		IOV_ID				NUMBER(8) NOT NULL,
		MEAN_T0				NUMBER(8) NOT NULL,
		RMS_T0				NUMBER(8) NOT NULL,
                PRIMARY KEY (CELL_ID, IOV_ID),
		FOREIGN KEY (CELL_ID) REFERENCES DT_CELL(CELL_ID)
	);

CREATE TABLE DT_MEAN_TIMER
	(	SL_ID				NUMBER(6) NOT NULL,
		IOV_ID				NUMBER(8) NOT NULL,
		MEAN_MTIMER			NUMBER(8) NOT NULL,
		RMS_MTIMER			NUMBER(8) NOT NULL,
                PRIMARY KEY (SL_ID, IOV_ID),
		FOREIGN KEY (SL_ID) REFERENCES DT_SUPERLAYER(SL_ID)
	);

CREATE TABLE DT_FE_MASK
	(	FE_CHANNEL_ID			NUMBER(8) NOT NULL,
		IOV_ID				NUMBER(8) NOT NULL,
		ENABLE_MASK			NUMBER(1) NOT NULL,
                PRIMARY KEY (FE_CHANNEL_ID, IOV_ID),
		FOREIGN KEY (FE_CHANNEL_ID) REFERENCES DT_FE_CHANNEL(FE_CHANNEL_ID)
	);

CREATE TABLE DT_CONNECTION_MASTER
	(	CONNECTION_ID			NUMBER(8) NOT NULL,
		CONNECTION_TYPE			VARCHAR2(40),
		MAPPING_VERSION			VARCHAR2(40),
		PRIMARY KEY (CONNECTION_ID)
	);

CREATE TABLE DT_CELL_READOUT_MAP
	(	CONNECTION_ID			NUMBER(8) NOT NULL,
		CELL_ID				NUMBER(8) NOT NULL,
		TDC_CHANNEL_ID			NUMBER(8) NOT NULL,
		FOREIGN KEY (CONNECTION_ID) REFERENCES DT_CONNECTION_MASTER(CONNECTION_ID),
		FOREIGN KEY (CELL_ID) REFERENCES DT_CELL(CELL_ID),
		FOREIGN KEY (TDC_CHANNEL_ID) REFERENCES DT_TDC_CHANNEL(TDC_CHANNEL_ID)
	);

CREATE TABLE DT_ROB_ROS_CONNECTION
	(	CONNECTION_ID			NUMBER(8) NOT NULL,
		ROS_CHANNEL_ID			NUMBER(6) NOT NULL,
		ROB_ID				NUMBER(6) NOT NULL,
		FOREIGN KEY (CONNECTION_ID) REFERENCES DT_CONNECTION_MASTER(CONNECTION_ID),
		FOREIGN KEY (ROS_CHANNEL_ID) REFERENCES DT_ROS_CHANNEL(ROS_CHANNEL_ID),
		FOREIGN KEY (ROB_ID) REFERENCES DT_ROB(ROB_ID)
	);