if exists (select * from sysobjects where id = object_id(N'[dbo].[DataDictionary]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[DataDictionary]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[IAIDS_DATA_DICT]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[IAIDS_DATA_DICT]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[v_web_aviation_query]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_web_aviation_query]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[additional_persons]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[additional_persons]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[aircraft]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aircraft]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Aviation_Query]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Aviation_Query]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[cabin_crew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cabin_crew]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ct_iaids]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ct_iaids]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[ct_seqevt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ct_seqevt]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[dt_aircraft]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dt_aircraft]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[dt_events]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dt_events]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[dt_Flight_Crew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dt_Flight_Crew]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[engines]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[engines]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[events]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[events]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Flight_Crew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Flight_Crew]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[flight_time]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[flight_time]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[injury]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[injury]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[narratives]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[narratives]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Occurrences]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Occurrences]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[seq_of_events]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[seq_of_events]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[states]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[states]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[SwitchDB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SwitchDB]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[sysi_dict]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sysi_dict]
GO

if exists (select * from systypes where name = N'Last_Chg_Date')
exec sp_droptype N'Last_Chg_Date'
GO

if exists (select * from systypes where name = N'Last_Chg_Userid')
exec sp_droptype N'Last_Chg_Userid'
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[r_time]') and OBJECTPROPERTY(id, N'IsRule') = 1)
drop rule [dbo].[r_time]
GO


/****** Object:  Rule dbo.r_time    Script Date: 12/13/00 2:42:13 PM ******/

/****** Object:  Rule dbo.r_time    Script Date: 5/26/99 2:35:23 PM ******/
create rule r_time as @timevalue BETWEEN 0 AND 2400 AND @timevalue % 100 BETWEEN 0 AND 59



GO
setuser N'dbo'
GO

EXEC sp_addtype N'Last_Chg_Date', N'datetime', N'null'
GO

setuser
GO

setuser N'dbo'
GO

EXEC sp_addtype N'Last_Chg_Userid', N'char (18)', N'null'
GO

setuser
GO

CREATE TABLE [dbo].[additional_persons] (
	[ev_id] [char] (14) NOT NULL ,
	[person_idkey] [int] NOT NULL ,
	[person_fname] [varchar] (20) NULL ,
	[person_init] [char] (1) NULL ,
	[person_lname] [varchar] (25) NULL ,
	[person_title] [varchar] (50) NULL ,
	[person_company] [varchar] (50) NULL ,
	[person_address] [varchar] (50) NULL ,
	[person_city] [varchar] (20) NULL ,
	[person_state] [char] (2) NULL ,
	[person_zip] [char] (9) NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[aircraft] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[regis_no] [varchar] (11) NULL ,
	[ntsb_no] [varchar] (11) NULL ,
	[acft_missing] [char] (1) NULL ,
	[ntsb_19a_date] [datetime] NULL ,
	[ntsb_4_date] [datetime] NULL ,
	[faa_form_date] [datetime] NULL ,
	[far_part] [char] (4) NULL ,
	[flt_plan_filed] [char] (4) NULL ,
	[flight_plan_activated] [char] (1) NULL ,
	[damage] [char] (4) NULL ,
	[acft_fire] [char] (4) NULL ,
	[acft_expl] [char] (4) NULL ,
	[tc_sheet] [char] (8) NULL ,
	[cert_region] [char] (2) NULL ,
	[dt_cur_cert] [datetime] NULL ,
	[faa_cert_region] [char] (4) NULL ,
	[oper_cert_fsdo] [char] (4) NULL ,
	[aic_acft_id] [varchar] (30) NULL ,
	[acft_make] [varchar] (30) NULL ,
	[acft_model] [varchar] (20) NULL ,
	[acft_series] [varchar] (10) NULL ,
	[acft_serial_no] [varchar] (20) NULL ,
	[year_mfg] [smallint] NULL ,
	[cert_max_gr_wt] [int] NULL ,
	[acft_category] [char] (4) NULL ,
	[acft_class] [char] (4) NULL ,
	[acft_reg_cls] [char] (4) NULL ,
	[homebuilt] [char] (3) NULL ,
	[total_seats] [smallint] NULL ,
	[num_eng] [tinyint] NULL ,
	[fixed_retractable] [char] (4) NULL ,
	[type_last_insp] [char] (4) NULL ,
	[date_last_insp] [datetime] NULL ,
	[afm_hrs_last_insp] [real] NULL ,
	[afm_hrs] [real] NULL ,
	[afm_cycles] [int] NULL ,
	[elt_install] [char] (1) NULL ,
	[elt_oper] [char] (1) NULL ,
	[elt_aided_loc_ev] [char] (1) NULL ,
	[flight_number] [char] (4) NULL ,
	[owner_acft] [varchar] (50) NULL ,
	[owner_street] [varchar] (50) NULL ,
	[owner_city] [varchar] (50) NULL ,
	[owner_state] [char] (2) NULL ,
	[owner_country] [char] (4) NULL ,
	[owner_zip] [varchar] (9) NULL ,
	[operated_by] [varchar] (30) NULL ,
	[oper_individual_name] [char] (1) NULL ,
	[oper_name] [varchar] (50) NULL ,
	[oper_same] [char] (1) NULL ,
	[oper_dba] [varchar] (50) NULL ,
	[oper_addr_same] [char] (1) NULL ,
	[oper_street] [varchar] (50) NULL ,
	[oper_city] [varchar] (50) NULL ,
	[oper_state] [char] (2) NULL ,
	[oper_country] [char] (4) NULL ,
	[oper_zip] [varchar] (9) NULL ,
	[oper_code] [char] (4) NULL ,
	[certs_held] [char] (1) NULL ,
	[oprtng_cert] [char] (3) NULL ,
	[oper_cert] [char] (4) NULL ,
	[oper_cert_num] [varchar] (11) NULL ,
	[oper_sched] [char] (4) NULL ,
	[oper_dom_int] [char] (3) NULL ,
	[oper_pax_cargo] [char] (4) NULL ,
	[type_fly] [char] (4) NULL ,
	[second_pilot] [char] (1) NULL ,
	[dprt_pt_same_ev] [char] (1) NULL ,
	[dprt_apt_id] [char] (4) NULL ,
	[dprt_city] [varchar] (15) NULL ,
	[dprt_state] [char] (2) NULL ,
	[dprt_country] [char] (3) NULL ,
	[dprt_time] [smallint] NULL ,
	[dprt_timezn] [char] (3) NULL ,
	[dest_same_local] [char] (4) NULL ,
	[dest_apt_id] [char] (4) NULL ,
	[dest_city] [varchar] (15) NULL ,
	[dest_state] [char] (2) NULL ,
	[dest_country] [char] (3) NULL ,
	[inj_idx] [char] (4) NULL ,
	[injfcrew_fatal] [smallint] NULL ,
	[injfcrew_inj] [smallint] NULL ,
	[injfcrew_onbd] [smallint] NULL ,
	[injccrew_fatal] [smallint] NULL ,
	[injccrew_inj] [smallint] NULL ,
	[injccrew_onbd] [smallint] NULL ,
	[injpax_fatal] [int] NULL ,
	[injpax_inj] [int] NULL ,
	[injpax_onbd] [int] NULL ,
	[injgcrew_fatal] [smallint] NULL ,
	[injgcrew_inj] [smallint] NULL ,
	[injpub_fatal] [smallint] NULL ,
	[injpub_inj] [smallint] NULL ,
	[phase_flt_spec] [int] NULL ,
	[report_to_icao] [char] (1) NULL ,
	[pov_desc] [text] NULL ,
	[evacuation] [char] (1) NULL ,
	[evac_injuries] [char] (1) NULL ,
	[faa_comments] [char] (255) NULL ,
	[c5] [varchar] (13) NULL ,
	[delete_flag] [char] (1) NULL ,
	[ntsb_dtchg] [datetime] NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL ,
	[afm_hrs_since] [char] (4) NULL ,
	[rwy_num] [varchar] (4) NULL ,
	[rwy_len] [int] NULL ,
	[rwy_width] [int] NULL ,
	[site_seeing] [char] (1) NULL ,
	[air_medical] [char] (1) NULL ,
	[med_type_flight] [varchar] (15) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Aviation_Query] (
	[ev_id] [varchar] (14) NOT NULL ,
	[aircraft_key] [int] NOT NULL ,
	[ev_type] [varchar] (3) NULL ,
	[NTSB_no] [varchar] (11) NULL ,
	[status] [varchar] (5) NULL ,
	[ev_Date] [Last_Chg_Date] NULL ,
	[ev_state] [varchar] (2) NULL ,
	[ev_Loc] [varchar] (20) NULL ,
	[make] [varchar] (51) NULL ,
	[regis_no] [varchar] (11) NULL ,
	[inj_tot_f] [smallint] NULL ,
	[inj_tot_m] [smallint] NULL ,
	[inj_tot_n] [smallint] NULL ,
	[inj_tot_s] [smallint] NULL ,
	[severity] [varchar] (37) NULL ,
	[forecolor] [varchar] (7) NULL ,
	[operation] [varchar] (9) NULL ,
	[Oper_sched] [varchar] (4) NULL ,
	[fiche_number] [varchar] (5) NULL ,
	[far_part] [varchar] (4) NULL ,
	[far_desc] [varchar] (50) NULL ,
	[category_desc] [varchar] (50) NULL ,
	[operator] [varchar] (166) NULL ,
	[oper_name] [varchar] (50) NULL ,
	[homebuilt] [varchar] (3) NULL ,
	[acft_category] [varchar] (4) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[cabin_crew] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[cc_crew_no] [int] NOT NULL ,
	[cc_lname] [varchar] (25) NULL ,
	[cc_init] [char] (1) NULL ,
	[cc_fname] [varchar] (20) NULL ,
	[cc_seatbelts_used] [char] (1) NULL ,
	[cc_seat_occ] [char] (4) NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL ,
	[cc_seated] [char] (1) NULL ,
	[cc_inj_level] [char] (4) NULL 
)
GO

CREATE TABLE [dbo].[ct_iaids] (
	[ct_name] [varchar] (20) NULL ,
	[code_iaids] [varchar] (4) NULL ,
	[meaning] [varchar] (50) NULL ,
	[seq] [smallint] NULL ,
	[ntsb_type] [varchar] (1) NULL ,
	[ntsb_code] [varchar] (2) NULL ,
	[avn_code] [varchar] (5) NULL ,
	[ntsb_codes_more] [varchar] (11) NULL ,
	[not_for_ntsb_use] [bit] NOT NULL 
)
GO

CREATE TABLE [dbo].[ct_seqevt] (
	[field_type] [char] (1) NOT NULL ,
	[field_id] [char] (1) NOT NULL ,
	[code] [int] NOT NULL ,
	[meaning] [char] (50) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[dt_aircraft] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[col_name] [char] (20) NOT NULL ,
	[code] [char] (4) NOT NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[dt_events] (
	[ev_id] [char] (14) NOT NULL ,
	[col_name] [char] (20) NOT NULL ,
	[code] [char] (4) NOT NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[dt_Flight_Crew] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[crew_no] [tinyint] NOT NULL ,
	[col_name] [char] (20) NOT NULL ,
	[code] [char] (4) NOT NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[engines] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[eng_no] [smallint] NOT NULL ,
	[eng_type] [char] (4) NULL ,
	[eng_group] [char] (6) NULL ,
	[eng_mfgr] [varchar] (30) NULL ,
	[eng_make] [varchar] (6) NULL ,
	[eng_model] [varchar] (13) NULL ,
	[eng_tc_sheet] [char] (8) NULL ,
	[eng_cert_faa_reg] [char] (2) NULL ,
	[power_units] [int] NULL ,
	[hp_or_lbs] [char] (4) NULL ,
	[lchg_userid] [varchar] (18) NULL ,
	[lchg_date] [datetime] NULL ,
	[carb_fuel_injection] [char] (4) NULL ,
	[propeller_type] [char] (4) NULL 
)
GO

CREATE TABLE [dbo].[events] (
	[ev_id] [char] (14) NOT NULL ,
	[ev_type] [varchar] (3) NULL ,
	[ev_date] [datetime] NULL ,
	[ev_dow] [varchar] (2) NULL ,
	[ev_time] [smallint] NULL ,
	[ev_tmzn] [varchar] (3) NULL ,
	[ev_city] [varchar] (15) NULL ,
	[ev_state] [varchar] (2) NULL ,
	[ev_country] [varchar] (4) NULL ,
	[ev_site_zipcode] [varchar] (10) NULL ,
	[ev_year] [smallint] NULL ,
	[ev_month] [tinyint] NULL ,
	[mid_air] [varchar] (1) NULL ,
	[on_ground_collision] [varchar] (1) NULL ,
	[latitude] [varchar] (7) NULL ,
	[longitude] [varchar] (8) NULL ,
	[biohazard_area] [varchar] (1) NULL ,
	[apt_name] [varchar] (30) NULL ,
	[airport_type] [varchar] (4) NULL ,
	[airport_involvement] [varchar] (4) NULL ,
	[ev_nr_apt_id] [varchar] (4) NULL ,
	[ev_nr_apt_loc] [varchar] (4) NULL ,
	[apt_dist] [real] NULL ,
	[apt_dir] [smallint] NULL ,
	[apt_elev] [smallint] NULL ,
	[rwy_brake_cond] [varchar] (4) NULL ,
	[wx_brief_comp] [varchar] (4) NULL ,
	[wx_src_iic] [varchar] (4) NULL ,
	[wx_obs_time] [smallint] NULL ,
	[wx_obs_dir] [smallint] NULL ,
	[wx_obs_fac_id] [varchar] (4) NULL ,
	[wx_obs_elev] [int] NULL ,
	[wx_obs_dist] [smallint] NULL ,
	[wx_obs_tmzn] [varchar] (3) NULL ,
	[light_cond] [varchar] (4) NULL ,
	[sky_cond_nonceil] [varchar] (4) NULL ,
	[sky_nonceil_ht] [int] NULL ,
	[sky_ceil_ht] [int] NULL ,
	[sky_cond_ceil] [varchar] (4) NULL ,
	[vis_rvr] [real] NULL ,
	[vis_rvv] [smallint] NULL ,
	[vis_sm] [real] NULL ,
	[wx_temp] [smallint] NULL ,
	[wx_dew_pt] [smallint] NULL ,
	[wind_dir_deg] [smallint] NULL ,
	[wind_dir_ind] [varchar] (1) NULL ,
	[wind_vel_kts] [tinyint] NULL ,
	[wind_vel_ind] [varchar] (4) NULL ,
	[gust_ind] [varchar] (1) NULL ,
	[gust_kts] [smallint] NULL ,
	[altimeter] [real] NULL ,
	[wx_dens_alt] [int] NULL ,
	[wx_int_precip] [varchar] (3) NULL ,
	[metar] [text] NULL ,
	[ev_highest_injury] [varchar] (4) NULL ,
	[inj_f_grnd] [smallint] NULL ,
	[inj_m_grnd] [smallint] NULL ,
	[inj_n_grnd] [smallint] NULL ,
	[inj_s_grnd] [smallint] NULL ,
	[inj_tot_f] [smallint] NULL ,
	[inj_tot_m] [smallint] NULL ,
	[inj_tot_n] [smallint] NULL ,
	[inj_tot_s] [smallint] NULL ,
	[inj_tot_t] [smallint] NULL ,
	[invest_agy] [varchar] (1) NULL ,
	[faa_region] [varchar] (2) NULL ,
	[fsdo] [varchar] (4) NULL ,
	[iicf_fname] [varchar] (20) NULL ,
	[iicf_init] [varchar] (1) NULL ,
	[iicf_lname] [varchar] (25) NULL ,
	[notf] [varchar] (4) NULL ,
	[ntsb_docket] [smallint] NULL ,
	[ntsb_notf_from] [varchar] (30) NULL ,
	[ntsb_notf_date] [datetime] NULL ,
	[ntsb_notf_tm] [smallint] NULL ,
	[iicn_fname] [varchar] (20) NULL ,
	[iicn_init] [varchar] (1) NULL ,
	[iicn_lname] [varchar] (25) NULL ,
	[report_status] [varchar] (1) NULL ,
	[fiche_number] [varchar] (5) NULL ,
	[approval_date] [datetime] NULL ,
	[status_flag] [varchar] (1) NULL ,
	[delete_flag] [varchar] (1) NULL ,
	[latest_src] [varchar] (4) NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL ,
	[wx_cond_basic] [varchar] (3) NULL ,
	[faa_dist_office] [varchar] (50) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Flight_Crew] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[crew_no] [tinyint] NOT NULL ,
	[crew_category] [char] (5) NULL ,
	[med_certf] [char] (4) NULL ,
	[med_crtf_vldty] [char] (4) NULL ,
	[date_lst_med] [datetime] NULL ,
	[crew_rat_endorse] [char] (1) NULL ,
	[crew_inj_level] [char] (4) NULL ,
	[seatbelts_used] [char] (1) NULL ,
	[shldr_harn_used] [char] (1) NULL ,
	[crew_tox_perf] [char] (1) NULL ,
	[seat_occ_pic] [char] (4) NULL ,
	[pc_profession] [char] (4) NULL ,
	[safety_clinic_l3y] [char] (1) NULL ,
	[wings_program_l3y] [char] (1) NULL ,
	[recurrent_train_l1y] [char] (1) NULL ,
	[bfr] [char] (1) NULL ,
	[bfr_date] [datetime] NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[flight_time] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[crew_no] [tinyint] NOT NULL ,
	[flight_type] [char] (4) NULL ,
	[flight_craft] [char] (4) NULL ,
	[flight_hours] [real] NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[injury] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[inj_person_category] [char] (4) NOT NULL ,
	[injury_level] [char] (4) NOT NULL ,
	[inj_person_count] [smallint] NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[narratives] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[narr_accp] [text] NULL ,
	[narr_accf] [text] NULL ,
	[narr_cause] [text] NULL ,
	[narr_inc] [text] NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[Occurrences] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[Occurrence_No] [int] NOT NULL ,
	[Occurrence_Code] [int] NULL ,
	[Phase_of_Flight] [int] NULL ,
	[Altitude] [int] NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[seq_of_events] (
	[ev_id] [char] (14) NOT NULL ,
	[Aircraft_Key] [int] NOT NULL ,
	[Occurrence_No] [int] NOT NULL ,
	[seq_event_no] [int] NOT NULL ,
	[group_code] [smallint] NOT NULL ,
	[Subj_Code] [int] NULL ,
	[Cause_Factor] [char] (1) NULL ,
	[Modifier_Code] [int] NULL ,
	[Person_Code] [int] NULL ,
	[lchg_date] [Last_Chg_Date] NULL ,
	[lchg_userid] [Last_Chg_Userid] NULL 
)
GO

CREATE TABLE [dbo].[states] (
	[state] [char] (2) NOT NULL ,
	[name] [char] (30) NOT NULL ,
	[faa_region] [char] (2) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SwitchDB] (
	[SwitchOld] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[sysi_dict] (
	[tblname] [char] (30) NOT NULL ,
	[colname] [char] (30) NOT NULL ,
	[data_element] [char] (30) NULL ,
	[short_desc] [char] (50) NOT NULL ,
	[data_type] [char] (20) NOT NULL ,
	[dsize] [smallint] NULL ,
	[rule_type] [char] (1) NOT NULL ,
	[codetbldw] [char] (40) NULL ,
	[ct_name] [varchar] (20) NULL ,
	[r_low] [char] (15) NULL ,
	[r_high] [char] (15) NULL ,
	[ntsb_alias] [char] (10) NULL ,
	[ntsb_name] [varchar] (40) NULL ,
	[ntsb_multi] [char] (1) NULL ,
	[ntsb_mulcnt] [smallint] NULL ,
	[faa_c_number] [smallint] NULL ,
	[col_order] [smallint] NULL ,
	[storage] [int] NULL ,
	[lastupdt] [datetime] NULL ,
	[long_desc] [text] NULL ,
	[dgroup] [char] (1) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [XPKADDITIONAL_PERSONS] ON [dbo].[additional_persons]([ev_id], [person_idkey]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [XPKcabin_crew] ON [dbo].[cabin_crew]([ev_id], [Aircraft_Key], [cc_crew_no]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [ix_ct_iaids] ON [dbo].[ct_iaids]([ct_name], [code_iaids]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [ix_dt_aircraft] ON [dbo].[dt_aircraft]([ev_id], [Aircraft_Key], [col_name], [code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [ix_dt_events] ON [dbo].[dt_events]([ev_id], [col_name], [code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [ix_dt_pilots] ON [dbo].[dt_Flight_Crew]([ev_id], [Aircraft_Key], [crew_no], [col_name], [code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [XPKengines] ON [dbo].[engines]([ev_id], [Aircraft_Key], [eng_no]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [ix_pilot] ON [dbo].[Flight_Crew]([ev_id], [Aircraft_Key], [crew_no]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [XPKflight_time] ON [dbo].[flight_time]([ev_id], [Aircraft_Key], [crew_no], [flight_type], [flight_craft]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [XPKinjury] ON [dbo].[injury]([ev_id], [Aircraft_Key], [inj_person_category], [injury_level]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [XPKnarratives] ON [dbo].[narratives]([ev_id], [Aircraft_Key]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [XPKOccurances] ON [dbo].[Occurrences]([ev_id], [Aircraft_Key], [Occurrence_No]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [XPKseq_of_events] ON [dbo].[seq_of_events]([ev_id], [Aircraft_Key], [Occurrence_No], [seq_event_no], [group_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[additional_persons] WITH NOCHECK ADD 
	CONSTRAINT [DF__additiona__lchg___6205949C] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__additiona__lchg___62F9B8D5] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[aircraft] WITH NOCHECK ADD 
	CONSTRAINT [DF__aircraft__lchg_d__139CF030] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__aircraft__lchg_u__14911469] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[Aviation_Query] WITH NOCHECK ADD 
	CONSTRAINT [PK_Aviation_Query] PRIMARY KEY  NONCLUSTERED 
	(
		[ev_id],
		[aircraft_key]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[cabin_crew] WITH NOCHECK ADD 
	CONSTRAINT [DF_cabin_crew_lchg_date_1__14] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF_cabin_crew_lchg_userid2__14] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[dt_aircraft] WITH NOCHECK ADD 
	CONSTRAINT [DF__dt_aircra__lchg___69A6B664] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__dt_aircra__lchg___6A9ADA9D] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[dt_events] WITH NOCHECK ADD 
	CONSTRAINT [DF__dt_events__lchg___1F0EA2DC] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__dt_events__lchg___2002C715] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[dt_Flight_Crew] WITH NOCHECK ADD 
	CONSTRAINT [DF__dt_Flight__lchg___78E8F9F4] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__dt_Flight__lchg___79DD1E2D] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[events] WITH NOCHECK ADD 
	CONSTRAINT [DF__events__lchg_dat__22DF33C0] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__events__lchg_use__23D357F9] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[Flight_Crew] WITH NOCHECK ADD 
	CONSTRAINT [DF__Flight_Cr__lchg___7CB98AD8] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__Flight_Cr__lchg___7DADAF11] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[flight_time] WITH NOCHECK ADD 
	CONSTRAINT [DF__flight_ti__lchg___75186910] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__flight_ti__lchg___760C8D49] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[injury] WITH NOCHECK ADD 
	CONSTRAINT [DF__injury__lchg_dat__0FCC5F4C] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__injury__lchg_use__10C08385] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[narratives] WITH NOCHECK ADD 
	CONSTRAINT [DF__narrative__narr___5A6472D4] DEFAULT (null) FOR [narr_accp],
	CONSTRAINT [DF__narrative__narr___5B58970D] DEFAULT (null) FOR [narr_accf],
	CONSTRAINT [DF__narrative__narr___5C4CBB46] DEFAULT (null) FOR [narr_cause],
	CONSTRAINT [DF__narrative__narr___5D40DF7F] DEFAULT (null) FOR [narr_inc],
	CONSTRAINT [DF__narrative__lchg___5E3503B8] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__narrative__lchg___5F2927F1] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[Occurrences] WITH NOCHECK ADD 
	CONSTRAINT [DF__Occurrenc__lchg___045AACA0] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__Occurrenc__lchg___054ED0D9] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

ALTER TABLE [dbo].[seq_of_events] WITH NOCHECK ADD 
	CONSTRAINT [DF__seq_of_ev__lchg___008A1BBC] DEFAULT (getdate()) FOR [lchg_date],
	CONSTRAINT [DF__seq_of_ev__lchg___017E3FF5] DEFAULT (user_name(null)) FOR [lchg_userid]
GO

 CREATE  UNIQUE  INDEX [ix_aircraft] ON [dbo].[aircraft]([ev_id], [Aircraft_Key]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_acft_model] ON [dbo].[aircraft]([acft_model]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_acft_reg_cls] ON [dbo].[aircraft]([acft_reg_cls]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_aic_acft_mms] ON [dbo].[aircraft]([aic_acft_id]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_damage] ON [dbo].[aircraft]([damage]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_dtchg] ON [dbo].[aircraft]([lchg_date]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_far_part] ON [dbo].[aircraft]([far_part]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_oper_name] ON [dbo].[aircraft]([oper_name]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_regis_no] ON [dbo].[aircraft]([regis_no]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_aircraft_ntsb_no] ON [dbo].[aircraft]([ntsb_no]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_ev_type] ON [dbo].[Aviation_Query]([ev_type]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_NTSB_no] ON [dbo].[Aviation_Query]([NTSB_no]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_Status] ON [dbo].[Aviation_Query]([status]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_ev_date] ON [dbo].[Aviation_Query]([ev_Date]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_ev_state] ON [dbo].[Aviation_Query]([ev_state]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_ev_loc] ON [dbo].[Aviation_Query]([ev_Loc]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_severity] ON [dbo].[Aviation_Query]([severity]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_acft_category] ON [dbo].[Aviation_Query]([acft_category]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_homebuilt] ON [dbo].[Aviation_Query]([homebuilt]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_make] ON [dbo].[Aviation_Query]([make]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_regis_no] ON [dbo].[Aviation_Query]([regis_no]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_operation] ON [dbo].[Aviation_Query]([operation]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_oper_name] ON [dbo].[Aviation_Query]([oper_name]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [ix_event] ON [dbo].[events]([ev_id]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_events_ev_month] ON [dbo].[events]([ev_month]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_events_ev_state] ON [dbo].[events]([ev_state]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_events_ev_year] ON [dbo].[events]([ev_year]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_events_faa_region] ON [dbo].[events]([faa_region]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [ix_events_fsdo] ON [dbo].[events]([fsdo]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [ix_events_key] ON [dbo].[events]([ev_date], [ev_time], [ev_city], [ev_state]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE  view DataDictionary
AS 
select 
	db_name()			as DatabaseName,
	obj.name			as Table_Name,
	col.name				as Column_Name,
	typ.name 			as Data_Type,
	col.length + spt_dtp.charbin		as Data_Size,
	com.text				as COLUMN_DEFAULT,
	case col.isnullable 
		when 1 then 'YES'
		else        'No '
	end				as IS_NULLABLE
							
FROM
	sysobjects obj,
	master.dbo.spt_datatype_info spt_dtp,
	systypes typ,
	syscolumns col
	LEFT OUTER JOIN syscomments com on col.cdefault = com.id
		AND com.colid = 1
	
WHERE
	permissions(obj.id, col.name) != 0
	AND obj.id = col.id
	AND typ.xtype = spt_dtp.ss_dtype
	AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
	AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT=0)
	AND obj.xtype ='U'
	AND col.xusertype = typ.xusertype

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE VIEW dbo.IAIDS_DATA_DICT
AS
SELECT DataDictionary.DatabaseName, 
   DataDictionary.Table_Name, DataDictionary.Column_Name, 
   DataDictionary.Data_Type, DataDictionary.Data_Size, 
   sysi_dict.data_element, sysi_dict.short_desc, 
   sysi_dict.ntsb_alias, sysi_dict.ntsb_name
FROM DataDictionary LEFT OUTER JOIN
   sysi_dict ON 
   DataDictionary.Table_Name = sysi_dict.tblname AND 
   DataDictionary.Column_Name = sysi_dict.colname



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE VIEW dbo.v_web_aviation_query
AS
Select
events.ev_id as ev_id,
aircraft_key,
ev_type,
NTSB_no,

case 
     when status_flag = 'p' then 'Prel' 
     when status_flag = 'f' and report_status = 'p' then 'Final'
     when status_flag = 'f' then 'Fact'
end as status,
ev_Date, 
ev_state, 
Case
  when ev_state = 'OF' then rtrim(ev_city + ' ' + ev_country)
  else rtrim(ev_city) + ', ' + ev_state

end as ev_Loc,
acft_make + ' ' + acft_model as make,
regis_no,
inj_tot_f,
inj_tot_m,
inj_tot_n,
inj_tot_s,
case 
  when ev_type = 'inc' then 'Incident'
  when inj_tot_f > 0 then 'Fatal(' + convert(varchar,inj_tot_f) + ')'
  else 'Nonfatal'
end as severity,
case when inj_tot_f > 0 then '#ff0000'
end as forecolor,
Oper_sched + ' ' + far_part as operation,
Oper_sched,
fiche_number,
far_part,

f.meaning as far_desc,
c.meaning as category_desc,
case when (far_part = '121' or far_part = '135' or far_part = '129') then
    case when oper_dba <> '' then
    	oper_sched + ' ' + f.meaning + ' ' + oper_name + ' (D.B.A. ' + Oper_dba + ')'
    when oper_same = 'y' then
	Oper_sched + ' ' + f.meaning + ' ' + owner_acft
    else
	Oper_sched + ' ' + f.meaning + ' ' + oper_name 
    end 
else '' 
end as operator,
oper_name,

homebuilt,
acft_category
from ((events inner join aircraft on events.ev_id = aircraft.ev_id) 
	left join Ct_iaids as c on (aircraft.acft_category = c.code_iaids
	and c.ct_name = 'ct_acft_cat')) 
	left join ct_iaids as f on (aircraft.far_part = f.code_iaids
	and f.ct_name = 'ct_fars')








GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

