File: bien09_read_me.html

Update: 2/22/2011

Biennial Ownership Subset

 

The CDBS data files in this directory are named with their table name followed by the “dat” extension.  We have also provided a set of files that begin with “bien09_” followed by a table name and the “dat” extension.  These contain a subset of ownership data as follows:

 

The Ownership Subset files are:

bien09_ownership_capitalization.dat

bien09_ownership_contract.dat

bien09_ownership_exemptions.dat

bien09_ownership_group.dat

bien09_ownership_interests.dat

bien09_ownership_other_int_xml_data.dat

bien09_ownership_other_int.dat

bien09_ownership_relationships.dat

bien09_ownership_report.dat

bien09_ownership_structure.dat

File Structure and Format

All of the data files (the full files and the ownership subset files) are documented in the file named http://www.fcc.gov/ftp/Bureaus/MB/Databases/cdbs/_readme.html which describes the order and type of the data elements on each line of the data files.  Note: The information about the ownership tables has been copied below.  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 ownership subset files have the same structure and format as the full versions.

 

This information is extracted from CDBS which is a relational database.  The field application_id is the primary key which associates data elements for many of the various tables and which uniquely identifies an ownership report (Form 323) filing. In the information, char indicates a character field with exactly the specified number of characters; varchar indicates a character field with up to the specified number of characters.  In addition, there are several user-defined field types.  These are listed at the bottom of table listing in the full readme file.

 

The data elements are defined in:  ftp://ftp.fcc.gov/pub/Bureaus/MB/Databases/cdbs/_Engineering_Data_Description.pdf .  Note that the PDF document describes the fields in each table in alphabetical order, not the order as they appear in the data files.  In addition, we have also provided a separate smaller file describing the fields for just the ownership tables.

Help

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

AM & FM Radio
Using CDBS data: Jorden Brinn, jorden.brinn@fcc.gov
Other CDBS questions: James Bradshaw, james.bradshaw@fcc.gov

TV
All CDBS questions: Hossein Hashemzadeh, hossein.hashemzadeh@fcc.gov

******************************************************************************
******************************************************************************
CDBS TABLES - OWNERSHIP
*****************************************************************************
******************************************************************************

ownership_capitalization
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    ownership_capitalization_id int, NOT NULL
 (2)    application_id            int, NOT NULL
 (3)    class_of_stock            varchar(60)
 (4)    voting_ind                char(1)
 (5)    authorized_shares         float()
 (6)    issued_shares             float()
 (7)    treasury_shares           float()
 (8)    unissued_shares           float()
 (9)    order_number              smallint
 (10)   class_of_stock_flg        char(1)
 (11)   class_of_stock_other      varchar(20)
 (12)   voting_flg                char(1)
 (13)   last_update_date          datetime
 
ownership_contract
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    ownership_contract_id     int, NOT NULL
 (2)    application_id            int, NOT NULL
 (3)    contract_description      varchar(255)
 (4)    contract_person           varchar(255)
 (5)    execution_date            datetime
 (6)    expiration_date           datetime
 (7)    order_number              smallint
 (8)    agreement_type_lma        char(1)
 (9)    agreement_type_naa        char(1)
 (10)   agreement_type_oth        char(1)
 (11)   execution_date_month      char(3)
 (12)   execution_date_year       smallint
 (13)   expiration_date_month     char(3)
 (14)   expiration_date_year      smallint
 (15)   expiration_date_na        char(1)
 (16)   last_update_date          datetime
 
ownership_exemptions
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    ownership_exemptions_id   int, NOT NULL
 (2)    application_id            int, NOT NULL
 (3)    order_number              smallint, NOT NULL
 (4)    name                      varchar(150)
 (5)    title                     varchar(60)
 (6)    last_update_date          datetime
 
ownership_group
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    ownership_group_id        int, NOT NULL
 (2)    main_application_id       int, NOT NULL
 (3)    fac_callsign              varchar(12)
 (4)    facility_id               int, NOT NULL
 (5)    fac_service               varchar(2)
 (6)    comm_city                 varchar(20)
 (7)    comm_state                varchar(2)
 (8)    fac_service_o             varchar(2)
 (9)    app_arn                   varchar(12)
 (10)   file_prefix               char(10)
 (11)   order_number              smallint
 (12)   last_update_date          datetime
 
ownership_interests
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    ownership_interests_id    int, NOT NULL
 (2)    application_id            int, NOT NULL
 (3)    order_number              smallint, NOT NULL
 (4)    frn                       varchar(10)
 (5)    name                      varchar(150)
 (6)    last_update_date          datetime
 
ownership_other_int
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    ownership_other_int_id    int, NOT NULL
 (2)    application_id            int, NOT NULL
 (3)    order_number              smallint, NOT NULL
 (4)    assets_perc               decimal(4,1)
 (5)    comm_city                 varchar(20)
 (6)    comm_state                char(2)
 (7)    entity_type               char(1)
 (8)    equity_perc               decimal(4,1)
 (9)    fac_callsign              varchar(12)
 (10)   facility_id               int
 (11)   name                      varchar(150)
 (12)   newspaper                 varchar(150)
 (13)   positional_int_off        char(1)
 (14)   positional_int_dir        char(1)
 (15)   positional_int_par        char(1)
 (16)   positional_int_lim        char(1)
 (17)   positional_int_stk        char(1)
 (18)   positional_int_own        char(1)
 (19)   positional_int_ent        char(1)
 (20)   positional_int_oth        char(1)
 (21)   positional_int_other_info varchar(20)
 (22)   votes_perc                decimal(4,1)
 (23)   last_update_date          datetime
 
ownership_other_int_xml_data
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    application_id            int, NOT NULL
 (2)    file_id                   int, NOT NULL
 (3)    order_number              smallint, NOT NULL
 (4)    assets_perc               decimal(4,1)
 (5)    comm_city                 varchar(20)
 (6)    comm_state                char(2)
 (7)    entity_type               char(1)
 (8)    equity_perc               decimal(4,1)
 (9)    fac_callsign              varchar(12)
 (10)   facility_id               int
 (11)   name                      varchar(150)
 (12)   newspaper                 varchar(150)
 (13)   positional_int_off        char(1)
 (14)   positional_int_dir        char(1)
 (15)   positional_int_par        char(1)
 (16)   positional_int_lim        char(1)
 (17)   positional_int_stk        char(1)
 (18)   positional_int_own        char(1)
 (19)   positional_int_ent        char(1)
 (20)   positional_int_oth        char(1)
 (21)   positional_int_other_info varchar(20)
 (22)   votes_perc                decimal(4,1)
 (23)   last_update_date          datetime
 
ownership_relationships
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    ownership_relationships_i int, NOT NULL
 (2)    application_id            int, NOT NULL
 (3)    order_number              smallint, NOT NULL
 (4)    names                     varchar(255)
 (5)    relationship_flg          char(2)
 (6)    last_update_date          datetime
 
ownership_report
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    application_id            int, NOT NULL
 (2)    accurate_date             datetime
 (3)    non_attributable_ind      char(1)
 (4)    attributable_ind          char(1)
 (5)    related_ind               char(1)
 (6)    exemption_ind             char(1)
 (7)    owner_type                varchar(5)
 (8)    entity_control_ind        char(1)
 (9)    separate_form_ind         char(1)
 (10)   attrib_exemption_ind      char(1)
 (11)   capitalization_na         char(1)
 (12)   contract_info_na          char(1)
 (13)   individuals_related_ind   char(1)
 (14)   lic_frn                   varchar(10)
 (15)   lic_name                  varchar(60)
 (16)   org_chart_na              char(1)
 (17)   respondent_nature_flg     char(3)
 (18)   resp_interests_na         char(1)
 (19)   last_update_date          datetime
 
 
ownership_structure
 No.    Column Name               Type
 ------ ------------------------- ------------------------------
 (1)    ownership_structure_id    int, NOT NULL
 (2)    application_id            int, NOT NULL
 (3)    name_address              varchar(255)
 (4)    gender_flg                char(1)
 (5)    ethnicity_flg             varchar(5)
 (6)    race_flg                  varchar(5)
 (7)    citizenship               char(2)
 (8)    positional_int            varchar(60)
 (9)    votes_perc                decimal(5,2)
 (10)   equity_perc               decimal(5,2)
 (11)   active_ind                char(1)
 (12)   office_held               varchar(60)
 (13)   interest_perc             decimal(5,2)
 (14)   occupation                varchar(60)
 (15)   appointed_by              varchar(60)
 (16)   existing_interests        varchar(255)
 (17)   order_number              smallint
 (18)   assets_perc               decimal(5,2)
 (19)   entity_exemption_ind      char(1)
 (20)   frn                       varchar(10)
 (21)   listing_type_flg          char(1)
 (22)   personal_info_na          char(1)
 (23)   positional_int_crd        char(1)
 (24)   positional_int_dir        char(1)
 (25)   positional_int_gen        char(1)
 (26)   positional_int_inv        char(1)
 (27)   positional_int_lim        char(1)
 (28)   positional_int_llc        char(1)
 (29)   positional_int_off        char(1)
 (30)   positional_int_oth        char(1)
 (31)   positional_int_own        char(1)
 (32)   positional_int_stk        char(1)
 (33)   positional_int_other_info varchar(20)
 (34)   relationship_flg          char(1)
 (35)   name                      varchar(150)
 (36)   city                      varchar(20)
 (37)   country                   varchar(20)
 (38)   state                     char(2)
 (39)   street_address1           varchar(60)
 (40)   street_address2           varchar(60)
 (41)   zip1                      char(5)
 (42)   zip2                      char(4)
 (43)   last_update_date          datetime