-
Notifications
You must be signed in to change notification settings - Fork 7
/
README
167 lines (115 loc) · 5.08 KB
/
README
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
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