Description
A mini relational data base of the us economy is used to demonstrate some basic concepts of the relational data model. Data verification and the use of math programming is shown as well.
Small Model of Type : MIP
Category : GAMS Model library
Main file : rdata.gms
$title Sample Data Base of the US Economy (RDATA,SEQ=38)
$onText
A mini relational data base of the us economy is used to demonstrate
some basic concepts of the relational data model. Data verification
and the use of math programming is shown as well.
Kendrick, D, Chapter 3: A Relational Database of the US Economy.
In Kindleberger, C P, and Ditella, G, Eds, Economics in the Long View,
Essays in the Honor of W W Rostow. Macmillan, London, 1982.
Keywords: mixed integer linear programming, US economy, relational data model
$offText
$sTitle Set Definitions
Set
plant / sparrows, inland, comfort, rockdale, lansing /
city / sparrows-p, rockdale, p-comfort, gary, lansing /
state / indiana, maryland, michigan, texas /
region / e-coast, g-coast, mid-west /
governor / bowen, clements, hughes, milliken /
party / democrat, republican /
company / us-steel, alcoa, inld-steel, gm /
union / iam, ibew, ibt, uaw, usa /
unit / blast-furn, steel-shop, roll-mill, alumina, aluminum, stamping, assembly /
commodity / iron-ore, pig-iron, scrap-iron, steel, flat-steel, bauxite, alumina, aluminum, auto-body, automobile /
process / pig-iron, steel-pig, stl-scrap, rolling, alumina, aluminum, auto-body, auto-assm /
industry / steel, aluminum, automobile /
sector / p-metals, transp-equ /
geography(plant,city,state,region) / (sparrows.sparrows-p.maryland.e-coast
inland .gary .indiana .mid-west
comfort .p-comfort .texas .g-coast
rockdale.rockdale .texas .g-coast
lansing .lansing .michigan.mid-west) /
govaff(state,governor,party) / (indiana .bowen .republican
maryland.hughes .democrat
michigan.milliken.republican
texas .clements.republican) /
ownership(company,plant) / (alcoa .(comfort,rockdale)
gm .lansing
inld-steel.inland
us-steel .sparrows ) /
sic(sector,industry,commodity) / p-metals.(steel.(iron-ore,pig-iron,steel,flat-steel,scrap-iron)
aluminum.(bauxite,alumina,aluminum))
transp-equ.automobile.(auto-body,automobile) /
indpl(industry,plant) 'classification of plants by industry';
$sTitle Data
Table a(commodity,process) 'input-output matrix'
pig-iron steel-pig stl-scrap rolling alumina aluminum auto-body auto-assm
iron-ore -1.
pig-iron 1. -.9 -.7
scrap-iron -.2 -.4 .2
steel 1. 1. -1.2
flat-steel 1. -1.2
bauxite -1.4
alumina 1. -1.2
aluminum 1 -.2
auto-body 1. -1.
automobile 1.;
Table b(unit,process) 'capacity utilization matrix'
pig-iron steel-pig stl-scrap rolling alumina aluminum auto-body auto-assm
blast-furn 1
steel-shop 1 1
roll-mill 1
alumina 1
aluminum 1
stamping 1
assembly 1;
Table k80(unit,plant) 'capacity in 1980 (millions of units)'
sparrows inland comfort rockdale lansing
blast-furn 2 2.5
steel-shop 2.35 2.8
roll-mill 1.9 2.4
alumina .8
aluminum .6 .5
stamping .6
assembly .6;
Table emp(plant,union) 'employment (thousands)'
uaw usa ibew ibt iam
sparrows 1.2 .3 .05
inland .4
comfort .7 .2
rockdale .5 .05
lansing 1.2 ;
$sTitle Data Manipulations
indpl(industry,plant) = yes$sum((sector,commodity,process,unit)$(sic(sector,industry,commodity)
$(a(commodity,process) > 0)$b(unit,process)
$k80(unit,plant)), 1);
display indpl;
Parameter
q1(union,company) 'employment by union and company (thousands)'
q2(unit,region) 'capacity by region (millions of units)'
q3(governor) 'employment in steel and automobiles (thousands)'
q4 'smallest number of union participation to build cars';
q1(union,company) = sum(plant$ownership(company,plant), emp(plant,union));
q2(unit,region) = sum((plant,city,state)$geography(plant,city,state,region), k80(unit,plant));
Set ind3(industry) 'industry grouping for q3' / steel, automobile /;
q3(governor) = sum((state,party)$govaff(state,governor,party),
sum((ind3,plant,city,region)$(geography(plant,city,state,region)*indpl(ind3,plant)),
sum(union, emp(plant,union))));
display q1, q2, q3;
* Query number 4 requires the solution of a mixed integer problem. Some other parameters are
* are needed for the mip formulation.
Parameter
demand(commodity) 'in millions of units' / automobile .5 /
ur(process,plant,union) 'union relationship to plant processes'
mu(union) 'maximum';
Set rawmat(commodity) 'raw materials';
ur(process,plant,union) = sum(unit$k80(unit,plant), emp(plant,union)*b(unit,process));
mu(union) = sum((process,plant), ur(process,plant,union));
rawmat(commodity) = yes$(not sum(process, a(commodity,process) > 0));
rawmat("scrap-iron") = yes;
display demand, ur, mu, rawmat;
$sTitle Model Definiton
Variable
nunion 'number of unions (number)'
z(process,plant) 'process level (million units)'
up(union) 'union participation'
u(commodity) 'purchase of raw materials (million units)';
Positive Variable z;
Binary Variable up;
Equation
mb(commodity) 'material balance (million units)'
cc(unit,plant) 'capacity constraint (million units)'
ub(union) 'union balance'
ud 'union definition';
mb(commodity).. sum((process,plant), a(commodity,process)*z(process,plant))
+ u(commodity)$rawmat(commodity)
=e= demand(commodity);
cc(unit,plant).. sum(process, b(unit,process)*z(process,plant)) =l= k80(unit,plant);
ub(union).. sum((process,plant), ur(process,plant,union)*z(process,plant)) =l= mu(union)*up(union);
ud.. nunion =e= sum(union, up(union));
Model david / all /;
solve david minimizing nunion using mip;
q4 = nunion.l;
display q4;