Description

This classical data base example uses a small department store to
demonstrate the power of the relational data model.

Small Model of Type : GAMS

Category : GAMS Model library

Main file : zloof.gms

$title Relational Data Base Example (ZLOOF,SEQ=29)

$onText
This classical data base example uses a small department store to
demonstrate the power of the relational data model.


Zloof, M M, Query-by-Example: A Data Base Language. IBM Systems
Journal 16, 4 (1977), 324-343.

Keywords: relational data model
$offText

Set
   name  'names of employees' / anderson, henry , hoffman, jones
                                lee     , lewis , long   , morgan
                                murphy  , nelson, smith           /
   dep   'departments'        / cosmetics, hardware, houshold, stationary, toy /
   sup   'supplier'           / bic,  dupont, parker, revlon                   /
   item  'sales items'        / dish, ink, lipstick, pen, pencil, perfume      /
   color 'all colors'         / white, red, green, blue /
   size                       / small, medium, large    /
   sales(dep,item)  'departments and items sold'
                    / cosmetics.  (lipstick,perfume)
                      hardware.    ink
                      houshold.   (dish,pen)
                      stationary. (dish,ink,pen,pencil)
                      toy.        (ink,pen,pencil)      /
   supply(item,sup) 'items and suppliers'
                    / dish.(bic,dupont)  , ink.(bic,parker)   , lipstick.revlon
                      pen.(parker,revlon), pencil.(bic,parker), perfume.revlon  /
   type(item,color,size) 'items color and size'
                         / dish.     white.    medium
                           ink.     (green.    large
                                     blue.     small)
                           lipstick. red.      large
                           pen.      green.    small
                           pencil. ((red,blue).large
                                     green.    small)
                           perfume.  white.    large  /;

Alias (name,namep);

Parameter emp(name,namep,dep) 'employees their managers and salaries'
                              / anderson.murphy .toy             6000
                                henry   .smith  .toy             9000
                                hoffman .morgan .cosmetics      16000
                                jones   .smith  .hardware        8000
                                lewis   .long   .stationary     12000
                                long    .morgan .cosmetics       7000
                                morgan  .lee    .cosmetics      10000
                                murphy  .smith  .houshold        8000
                                nelson  .murphy .toy             6000
                                smith   .hoffman.stationary      1200 /;

Set
   g01(item)  'red items'
   g02(color) 'colors of ink'
   g03(dep)   'departments selling items supplied by parker'
   g04(sup)   'suppliers selling items to the toy department';

g01(item)  = sum(size, type(item ,"red",size));
g02(color) = sum(size, type("ink",color,size));
g03(dep)   = sum(supply(item,"parker"), sales(dep,item));
g04(sup)   = sum(sales("toy",item),     supply(item,sup));

Parameter
   g05(name,namep) 'salaries of toy dep employees and managers'
   sal(name,dep)   'salaries by department'
   money(name)     'employees salary'

Set list(name,dep) 'employment list';

g05(name,namep) = emp(name,namep,"toy");
list(name,dep)  = yes$sum(namep, emp(name,namep,dep) or emp(namep,name,dep));
sal(name,dep)   = sum(namep, emp(name,namep,dep));
money(name)     = sum(dep, sal(name,dep));

option  sal:0, money:0;

display list, sal, money;

Set
   g06(dep,item,sup) 'departments sales items and suppliers'
   g07(name)         'employees who earn more than their supervisors'
   g08(dep)          'departments selling pens or pencils'
   g09(dep)          'departments selling pens and pencils'
   g10(dep)          'departments selling all items supplied by parker'
   g11(dep)          'departments only selling items supplied by parker'
   g12(dep)          'deps selling only all items supplied by parker';

g06(dep,item,sup) = sales(dep,item)*supply(item,sup);
g07(name) = sum((namep,dep)$(emp(name,namep,dep) and money(name) > money(namep)), yes);
g08(dep)  = sales(dep,"pen") + sales(dep,"pencil");
g09(dep)  = sales(dep,"pen") * sales(dep,"pencil");
g10(dep)  = prod(supply(item,"parker"), sales(dep,item));
g11(dep)  = prod(sales(dep,item), supply(item,"parker"));
g12(dep)  = g10(dep)*g11(dep);

Scalar
   g13 'total salary of employees in toy department'
   g14 'different colors of pencils';

g13 = sum((name,namep), emp(name,namep,"toy"));
g14 = sum(color$sum(size,type("pencil",color,size)), 1);

Set
   g15(dep)  'departments with total salaries gt 22k selling pens'
   g16(item) 'items in colors other than green'
   g17(item) 'all items except green ones';

g15(dep)  = sales(dep,"pen")$(sum((name,namep), emp(name,namep,dep)) > 22);
g16(item) = sum((size,color), type(item,color,size) - type(item,"green",size));
g17(item) = yes - sum(size, type(item,"green",size));

option  g05:0:0:1, g13:0, g14:0;

display g01, g02, g03, g04, g05, g06, g07, g08, g09, g10, g11, g12, g13, g14, g15, g16, g17;