README


1. AUTHORSHIP
=============
Author: Greg Elin
Email: [email protected]

Version: 0.02 - Early experiments with MySQL


2. ABOUT THIS REPOSITORY
========================

This is the start of a collection of useful scripts for working with 
Federal Communications Commission's (FCC) publicly downloadable files associated with
Consolidated Database System (CDBS). CDBS contains the broadcast media related licenses
and applications on file with the FCC. 

This repository is maintained at: https://github.com/gregelin/FCC-CDBS

See the file ROADMAP for a description of project roadmap.


3. WHO SHOULD USE THIS
======================

These scripts are intended for those experienced with relational databases.
The goal is to make it very easy for individuals with database knowledge to
create their own copy of the CDBS. 

If you are not familiar with SQL to create database tables and load data
you will likely find this repository confusing.


4. ABOUT CDBS FROM FCC's CDBS README[1]
=======================================

CDBS is the Consolidated Database System for the FCC's Media Bureau.
CDBS is a relational database; the field application_id is the primary key
which associates data elements for many of the various tables.

The public files under CDBS are available at:
http://www.fcc.gov/mb/databases/cdbs/
Normally, these files will be updated daily.

A more descriptive listing of the data elements is in:
ftp://ftp.fcc.gov/pub/Bureaus/MB/Databases/cdbs/_Engineering_Data_Description.pdf
This PDF document describes the fields in each table in 
alphabetical order, not the order as they appear in the data files.
The fields in each file are pipe-delimited ("|").

The files have been compressed on a Unix system with zip (-l option).
They can be unzipped on a PC using PKUNZIP, WINZip, or similar utilities.

The main table for FM engineering data is fm_eng_data. 
The field eng_record_type can either be 'C' (current records) or
'A' (archived or superceded records). There is a similar marker for AM
data in table am_ant_sys. AM data are also located in am_eng_data. 

Ownership information can be located through the party table. The app_party
and fac_party tables hold the associated party_id key.

Questions and corrections concerning the CDBS data should be directed to:

AM & FM Radio
Using CDBS data: Jorden Brinn, [email protected]
Other CDBS questions: James Bradshaw, [email protected]

TV
All CDBS questions: Hossein Hashemzadeh, [email protected]


5. UNDERSTANDING CDBS FIELDS
============================
Explanatory notes on each field (columns) is also loaded into the database
in the table doc_attributes. This makes it possible to present the name of the field
and an explanation of the field as part of any application you build with the data.

Field name is organized both by individual tables and logical grouping (grouping_name).
The logical groups are:

	am_eng  
	fmtv_eng
	application
	tracking
	common
	facility
	dtv_agreement_group
	facility
	Ownership
	various

The following query example returns explanatory information on network_affil field in the facility table.

select column_name, entity_name, entity_attribute_name, entity_attribute_definition from doc_attributes where table_name = "facility" and column_name = "network_affil"

select column_name, entity_name, entity_attribute_name, entity_attribute_definition from doc_attributes where table_name = "facility" and entity_attribute_name like "%license%"





6. MAPPING OF CUSTOM USER-DEFINED DATA TYPES
============================================

The publish CDBS data types include 17 custom defined fields. 
These custom defined data types have been converted to an appropriate field
definition for each specific data type. For reference the mappings are:

The publish CDBS field types include 17 custom defined fields. 

	address to varchar(40)
	arn to char(12)
	callsign to char(12)
	city to varchar(20)
	country to char(2)
	county to varchar(30)
	email to varchar(60)
	file_number to char(21)
	file_prefix to char(10)
	form_number to varchar(10)
	ind to char(1)
	name to varchar(60)
	percentage to float(9,6)
	phone to char(10)
	state to char(2)
	title to varchar(60)
	url to varchar(255)


7. NOTES FOR DIFFERENT DATABASE SOFTWARE
========================================

MYSQL
-----
Floats have been defined as float(13,6) for most values and float(9,6) for percentage.

Run script to download and uncompress file
> ./cdbs_download_unix.sh

Run commands to load data into mysql (this can be put into a script eventually)
> mysql -v -u root -p cdbs < fcc_cdbs_ddl_mysql.sql
> password: 
> mysql -v -u root -p cdbs < fcc_cdbs_load_mysql.sql 
> password:


8. ACRONYMS
===========

FCC: Federal Communication Commission
CDBS: Consolidated DataBase System


9. SEE ALSO
===========

1. CDBS Documentation page: http://transition.fcc.gov/mb/databases/cdbs/
2. FCC CDBS README: http://transition.fcc.gov/ftp/Bureaus/MB/Databases/cdbs/_readme.html
3. Explanation of fields: http://transition.fcc.gov/ftp/Bureaus/MB/Databases/cdbs/_Engineering_Data_Description.pdf


9. FOOTNOTES
============
[1] http://transition.fcc.gov/ftp/Bureaus/MB/Databases/cdbs/_readme.html