use usdasr21;

DROP TABLE IF EXISTS `FOOD_DES`;
CREATE TABLE `FOOD_DES` (
    `NDB_No` varchar(5) not null,
    `FdGrp_Cd` varchar(4) not null,
    `Long_Desc` varchar(200) not null,
    `Shrt_Desc` varchar(60) not null,
    `ComName` varchar(100),
    `ManufacName` varchar(65),
    `Survey` varchar(1),
    `Ref_desc` varchar(135),
    `Refuse` decimal(2),
    `SciName` varchar(65),
    `N_Factor` decimal(4,2),
    `Pro_Factor` decimal(4,2),
    `Fat_Factor` decimal(4,2),
    `CHO_Factor` decimal(4,2),
    KEY (`NDB_No`)
);

DROP TABLE IF EXISTS `FD_GROUP`;
CREATE TABLE `FD_GROUP` (
    `FdGrp_Cd` varchar(4) not null,
    `FdGrp_Desc` varchar(60) not null,
    KEY (`FdGrp_Cd`)
);

DROP TABLE IF EXISTS `NUT_DATA`;
CREATE TABLE `NUT_DATA` (
    `NDB_No` varchar(5) not null,
    `Nutr_No` varchar(3) not null,
    `Nutr_Val` decimal(10,3) not null,
    `Num_Data_Pts` decimal(5) not null,
    `Std_Error` decimal(8,3),
    `Src_Cd` varchar(2) not null,
    `Deriv_Cd` varchar(4),
    `Ref_NDB_No` varchar(5),
    `Add_Nutr_Mark` varchar(1),
    `Num_Studies` decimal(2),
    `Min` decimal(10,3),
    `Max` decimal(10,3),
    `DF` decimal(2),
    `Low_EB` decimal(10,3),
    `Up_EB` decimal(10,3),
    `Stat_cmt` varchar(10),
    `CC` varchar(1),
    KEY (`NDB_No`),
    KEY (`Nutr_No`)
);

DROP TABLE IF EXISTS `NUTR_DEF`;
CREATE TABLE `NUTR_DEF` (
    `Nutr_No` varchar(3) not null,
    `Units` varchar(7) not null,
    `Tagname` varchar(20),
    `NutrDesc` varchar(60) not null,
    `Num_Dec` varchar(1) not null,
    `SR_Order` decimal(6) not null,
    KEY (`Nutr_No`)
);

DROP TABLE IF EXISTS `SRC_CD`;
CREATE TABLE `SRC_CD` (
    `Src_Cd` varchar(2) not null,
    `SrcCd_Desc` varchar(60) not null,
    KEY (`Src_Cd`)
);

DROP TABLE IF EXISTS `DERIV_CD`;
CREATE TABLE `DERIV_CD` (
    `Deriv_Cd` varchar(4) not null,
    `Deriv_Desc` varchar(120) not null,
    KEY (`Deriv_Cd`)
);

DROP TABLE IF EXISTS `WEIGHT`;
CREATE TABLE `WEIGHT` (
    `NDB_No` varchar(5) not null,
    `Seq` varchar(2) not null,
    `Amount` decimal(5,3) not null,
    `Msre_Desc` varchar(80) not null,
    `Gm_Wgt` decimal(7,1) not null,
    `Num_Data_Pts` decimal(3),
    `Std_Dev` decimal(7,3),
    KEY (`NDB_No`),
    KEY (`Seq`)
);

DROP TABLE IF EXISTS `FOOTNOTE`;
CREATE TABLE `FOOTNOTE` (
    `NDB_No` varchar(5) not null,
    `Footnt_No` varchar(4) not null,
    `Footnt_Typ` varchar(1) not null,
    `Nutr_No` varchar(3),
    `Footnt_Txt` varchar(200) not null
);

DROP TABLE IF EXISTS `DATSRCLN`;
CREATE TABLE `DATSRCLN` (
    `NDB_No` varchar(5) not null,
    `Nutr_No` varchar(3) not null,
    `DataSrc_ID` varchar(6) not null,
    KEY (`NDB_No`),
    KEY (`Nutr_No`),
    KEY (`DataSrc_ID`)
);

DROP TABLE IF EXISTS `DATA_SRC`;
CREATE TABLE `DATA_SRC` (
    `DataSrc_ID` varchar(6) not null,
    `Authors` varchar(255) not null,
    `Title` varchar(255) not null,
    `Year` varchar(4) not null,
    `Journal` varchar(135) not null,
    `Vol_City` varchar(16) not null,
    `Issue_State` varchar(5) not null,
    `Start_Page` varchar(5) not null,
    `End_Page` varchar(5) not null,
    KEY (`DataSrc_ID`)
);

DROP TABLE IF EXISTS `ABBREV`;
CREATE TABLE `ABBREV` (
    `NDB_No` varchar(5) not null,
    `Shrt_Desc` varchar(60),
    `Water` decimal(10,2),
    `Energ_Kcal` decimal(10),
    `Protein` decimal(10,2),
    `Lipid_Tot` decimal(10,2),
    `Ash` decimal(10,2),
    `Carbohydrt` decimal(10,2),
    `Fiber_TD` decimal(10,1),
    `Sugar_Tot` decimal(10,2),
    `Calcium` decimal(10),
    `Iron` decimal(10,2),
    `Magnesium` decimal(10),
    `Phosphorus` decimal(10),
    `Potassium` decimal(10),
    `Sodium` decimal(10),
    `Zinc` decimal(10,2),
    `Copper` decimal(10,3),
    `Manganese` decimal(10,3),
    `Selenium` decimal(10,1),
    `Vit_C` decimal(10,1),
    `Thiamin` decimal(10,3),
    `Riboflavin` decimal(10,3),
    `Niacin` decimal(10,3),
    `Panto_acid` decimal(10,3),
    `Vit_B6` decimal(10,3),
    `Folate_Tot` decimal(10),
    `Folic_acid` decimal(10),
    `Food_Folate` decimal(10),
    `Folate_DFE` decimal(10),
    `Choline` decimal(10),
    `Vit_B12` decimal(10,2),
    `Vit_A_IU` decimal(10),
    `Vit_A_RAE` decimal(10),
    `Retinol` decimal(10),
    `Alpha_Carot` decimal(10),
    `Beta_Carot` decimal(10),
    `Beta_Crypt` decimal(10),
    `Lycopene` decimal(10),
    `LutZea` decimal(10),
    `Vit_E` decimal(10,2),
    `Vit_K` decimal(10,1),
    `FA_Sat` decimal(10,3),
    `FA_Mono` decimal(10,3),
    `FA_Poly` decimal(10,3),
    `Cholestrl` decimal(10,3),
    `GmWt_1` decimal(9,2),
    `GmWt_Desc1` varchar(120),
    `GmWt_2` decimal(9,2),
    `GmWt_Desc2` varchar(120),
    `Refuse_Pct` decimal(2),
    KEY (`NDB_No`)
);