/* $Id: company_data.sql,v 1.4 2007/06/14 11:20:34 almhe Exp $
  The Jonson Brothers Ltd. company database */

/*
The relation jbemployee

----------------------------------------------------------------------------------
|   ID      |        NAME        |  SALARY   |  MANAGER  | BIRTHYEAR | STARTYEAR |
----------------------------------------------------------------------------------
|    157    |     Jones, Tim     |   12000   |    199    |   1940    |   1960    |
|   1110    |    Smith, Paul     |   6000    |    33     |   1952    |   1973    |
|    35     |   Evans, Michael   |   5000    |    32     |   1952    |   1974    |
|    129    |    Thomas, Tom     |   10000   |    199    |   1941    |   1962    |
|    13     |   Edwards, Peter   |   9000    |    199    |   1928    |   1958    |
|    215    |  Collins, Joanne   |   7000    |    10     |   1950    |   1971    |
|    55     |    James, Mary     |   12000   |    199    |   1920    |   1969    |
|    26     |   Thompson, Bob    |   13000   |    199    |   1930    |   1970    |
|    98     |   Williams, Judy   |   9000    |    199    |   1935    |   1969    |
|    32     |   Smythe, Carol    |   9050    |    199    |   1929    |   1967    |
|    33     |   Hayes, Evelyn    |   10100   |    199    |   1931    |   1963    |
|    199    |   Bullock, J.D.    |   27000   |           |   1920    |   1920    |
|   4901    |  Bailey, Chas M.   |   8377    |    32     |   1956    |   1975    |
|    843    |  Schmidt, Herman   |   11204   |    26     |   1936    |   1956    |
|   2398    | Wallace, Maggie J. |   7880    |    26     |   1940    |   1959    |
|   1639    |    Choy, Wanda     |   11160   |    55     |   1947    |   1970    |
|   5119    |    Bono, Sonny     |   13621   |    55     |   1939    |   1963    |
|    37     |   Raveen, Lemont   |   11985   |    26     |   1950    |   1974    |
|   5219    |  Schwarz, Jason B. |   13374   |    33     |   1944    |   1959    |
|   1523    | Zugnoni, Arthur A. |   19868   |    129    |   1928    |   1949    |
|    430    |  Brunet, Paul C.   |   17674   |    129    |   1938    |   1959    |
|    994    |  Iwano, Masahiro   |   15641   |    129    |   1944    |   1970    |
|   1330    |  Onstad, Richard   |   8779    |    13     |   1952    |   1971    |
|    10     |   Ross, Stanley    |   15908   |    199    |   1927    |   1945    |
|    11     |    Ross, Stuart    |   12067   |           |   1931    |   1932    |
----------------------------------------------------------------------------------
(25 rows)
*/

INSERT INTO jbemployee VALUES
   ( 157, 'Jones, Tim',         12000,  199, 1940, 1960),
   (1110, 'Smith, Paul',         6000,   33, 1952, 1973),
   (  35, 'Evans, Michael',      5000,   32, 1952, 1974),
   ( 129, 'Thomas, Tom',        10000,  199, 1941, 1962),
   (  13, 'Edwards, Peter',      9000,  199, 1928, 1958),
   ( 215, 'Collins, Joanne',     7000,   10, 1950, 1971),
   (  55, 'James, Mary',        12000,  199, 1920, 1969),
   (  26, 'Thompson, Bob',      13000,  199, 1930, 1970),
   (  98, 'Williams, Judy',      9000,  199, 1935, 1969),
   (  32, 'Smythe, Carol',       9050,  199, 1929, 1967),
   (  33, 'Hayes, Evelyn',      10100,  199, 1931, 1963),
   ( 199, 'Bullock, J.D.',      27000, null, 1920, 1920),
   (4901, 'Bailey, Chas M.',     8377,   32, 1956, 1975),
   ( 843,  'Schmidt, Herman',   11204,   26, 1936, 1956),
   (2398, 'Wallace, Maggie J.',  7880,   26, 1940, 1959),
   (1639, 'Choy, Wanda',        11160,   55, 1947, 1970),
   (5119, 'Bono, Sonny',        13621,   55, 1939, 1963),
   (  37, 'Raveen, Lemont',     11985,   26, 1950, 1974),
   (5219, 'Schwarz, Jason B.',  13374,   33, 1944, 1959),
   (1523, 'Zugnoni, Arthur A.', 19868,  129, 1928, 1949),
   ( 430, 'Brunet, Paul C.',    17674,  129, 1938, 1959),
   ( 994, 'Iwano, Masahiro',    15641,  129, 1944, 1970),
   (1330, 'Onstad, Richard',     8779,   13, 1952, 1971),
   (  10, 'Ross, Stanley',      15908,  199, 1927, 1945),
   (  11, 'Ross, Stuart',       12067, null, 1931, 1932);

ALTER TABLE jbemployee 
ADD CONSTRAINT fk_emp_mgr 
FOREIGN KEY (manager) REFERENCES jbemployee(id) ON DELETE SET NULL;

/*
The relation jbcity

-------------------------------+
|  ID   |     NAME      |STATE |
-------------------------------+
|  900  |  Los Angeles  |Calif |
|  946  |    Oakland    |Calif |
|  945  |  El Cerrito   |Calif |
|  303  |    Atlanta    |  Ga  |
|  941  | San Francisco |Calif |
|  021  |    Boston     | Mass |
|  752  |    Dallas     | Tex  |
|  802  |    Denver     | Colo |
|  106  | White Plains  | Neb  |
|  010  |    Amherst    | Mass |
|  981  |    Seattle    | Wash |
|  609  |    Paxton     | Ill  |
|  100  |   New York    |  NY  |
|  921  |   San Diego   |Calif |
|  118  |   Hickville   | Okla |
|  841  |Salt Lake City | Utah |
|  537  |    Madison    | Wisc |
--------------------------------
(18 rows)
*/

INSERT INTO jbcity VALUES
   (900, 'Los Angeles',   'Calif'),
   (946, 'Oakland',       'Calif'),
   (945, 'El Cerrito',    'Calif'),
   (303, 'Atlanta',          'Ga'),
   (941, 'San Francisco', 'Calif'),
   (021, 'Boston',         'Mass'),
   (752, 'Dallas',          'Tex'),
   (802, 'Denver',         'Colo'),
   (106,  'White Plains',    'Neb'),
   (010, 'Amherst',        'Mass'),
   (981, 'Seattle',        'Wash'),
   (609, 'Paxton',          'Ill'),
   (100, 'New York',         'NY'),
   (921, 'San Diego',     'Calif'),
   (118, 'Hickville',      'Okla'),
   (841, 'Salt Lake City', 'Utah'),
   (537, 'Madison',        'Wisc');
COMMIT;

/*
The relation jbstore

+------+---------------+
| ID   | CITY          |
+------+---------------+
|     5|941            |
|     7|946            |
|     8|945            |
|     9|941            |
+------+---------------+
(4 rows)
*/
INSERT INTO jbstore VALUES
   (5, 941 ),
   (7, 946 ),
   (8, 945 ),
   (9, 941 );

/*
The relation jbdept

----------------------------------------------------------------------
|   ID      |        NAME        |   STORE   |   FLOOR   |  MANAGER  |
----------------------------------------------------------------------
|    35     |        Book        |     5     |     1     |    55     |
|    10     |       Candy        |     5     |     1     |    13     |
|    19     |     Furniture      |     7     |     4     |    26     |
|    20     |  Major Appliances  |     7     |     4     |    26     |
|    14     |      Jewelry       |     8     |     1     |    33     |
|    43     |     Children's     |     8     |     2     |    32     |
|    65     |      Junior's      |     7     |     3     |    17     |
|    58     |       Men's        |     7     |     2     |    129    |
|    60     |     Sportswear     |     5     |     1     |    10     |
|    99     |      Giftwrap      |     5     |     1     |    98     |
|     1     |      Bargain       |     5     |     0     |     7     |
|    26     |       Linens       |     7     |     3     |    157    |
|    63     |      Women's       |     7     |     3     |    38     |
|    49     |        Toys        |     8     |     2     |    35     |
|    70     |      Women's       |     5     |     1     |    10     |
|    73     |     Children's     |     5     |     1     |    10     |
|    34     |     Stationary     |     5     |     1     |    33     |
|    47     |    Junior Miss     |     7     |     2     |    129    |
|    28     |      Women's       |     8     |     2     |    32     |
----------------------------------------------------------------------
(19 rows)
*/
INSERT INTO jbdept VALUES
   (35,             'Book', 5, 1,  55),
   (10,            'Candy', 5, 1,  13),
   (19,        'Furniture', 7, 4,  26),
   (20, 'Major Appliances', 7, 4,  26),
   (14,          'Jewelry', 8, 1,  33),
   (43,      'Children''s', 8, 2,  32),
   (65,        'Junior''s', 7, 3,  37),
   (58,           'Men''s', 7, 2, 129),
   (60,       'Sportswear', 5, 1,  10),
   (99,         'Giftwrap', 5, 1,  98),
   ( 1,          'Bargain', 5, 0,  37),
   (26,           'Linens', 7, 3, 157),
   (63,         'Women''s', 7, 3,  32),
   (49,             'Toys', 8, 2,  35),
   (70,         'Women''s', 5, 1,  10),
   (73,      'Children''s', 5, 1,  10),
   (34,       'Stationary', 5, 1,  33),
   (47,      'Junior Miss', 7, 2, 129),
   (28,         'Women''s', 8, 2,  32);
COMMIT;

/*
The relation jbsupplier

+------+---------------+---------------+
| ID   |NAME           |CITY           |
+------+---------------+---------------+
|   199|Koret          |900            |
|   213|Cannon         |303            |
|    33|Levi-Strauss   |941            |
|    89|Fisher-Price   |021            |
|   125|Playskool      |752            |
|    42|Whitman's      |802            |
|    15|White Stag     |106            |
|   475|DEC            |010            |
|   122|White Paper    |981            |
|   440|Spooley        |609            |
|   241|IBM            |100            |
|    62|Data General   |303            |
|     5|Amdahl         |921            |
|    20|Wormley        |118            |
|    67|Edger          |841            |
|   999|A E Neumann    |537            |
+------+---------------+---------------+
(16 rows)
*/
INSERT INTO jbsupplier VALUES
   (199,        'Koret',    900),
   (213,       'Cannon',    303),
   ( 33, 'Levi-Strauss',    941),
   ( 89, 'Fisher-Price',    021),
   (125,    'Playskool',    752),
   ( 42,   'Whitman''s',    802),
   ( 15,   'White Stag',    106),
   (475,          'DEC',    010),
   (122,  'White Paper',    981),
   (440,      'Spooley',    609),
   (241,          'IBM',    100),
   ( 62, 'Data General',    303),
   (  5,       'Amdahl',    921),
   ( 20,      'Wormley',    118),
   ( 67,        'Edger',    841),
   (999,  'A E Neumann',    537);
COMMIT;

/*
The relation jbitem

----------------------------------------------------------------------------------
|   ID      |        NAME        |   DEPT    |   PRICE   |    QOH    | SUPPLIER  |
----------------------------------------------------------------------------------
|    26     |      Earrings      |    14     |   1000    |    20     |    199    |
|    118    |    Towels, Bath    |    26     |    250    |   1000    |    213    |
|    43     |        Maze        |    49     |    325    |    200    |    89     |
|    106    |     Clock Book     |    49     |    198    |    150    |    125    |
|    23     |      1 lb Box      |    10     |    215    |    100    |    42     |
|    52     |       Jacket       |    60     |   3295    |    300    |    15     |
|    165    |        Jean        |    65     |    825    |    500    |    33     |
|    258    |       Shirt        |    58     |    650    |   1200    |    33     |
|    120    |     Twin Sheet     |    26     |    800    |    750    |    213    |
|    301    |  Boy's Jean Suit   |    43     |   1250    |    500    |    33     |
|    121    |    Queen Sheet     |    26     |   1375    |    600    |    213    |
|    101    |       Slacks       |    63     |   1600    |    325    |    15     |
|    115    |     Gold Ring      |    14     |   4995    |    10     |    199    |
|    25     |   2 lb Box, Mix    |    10     |    450    |    75     |    42     |
|    119    |    Squeeze Ball    |    49     |    250    |    400    |    89     |
|    11     |     Wash Cloth     |     1     |    75     |    575    |    213    |
|    19     |    Bellbottoms     |    43     |    450    |    600    |    33     |
|    21     |     ABC Blocks     |     1     |    198    |    405    |    125    |
|    107    |  The 'Feel' Book   |    35     |    225    |    225    |    89     |
|    127    |    Ski Jumpsuit    |    65     |   4350    |    125    |    15     |
----------------------------------------------------------------------------------
(20 rows)
*/
INSERT INTO jbitem VALUES
   ( 26, 'Earrings',          14, 1000,   20, 199),
   (118, 'Towels, Bath',      26,  250, 1000, 213),
   ( 43, 'Maze',              49,  325,  200,  89),
   (106, 'Clock Book',        49,  198,  150, 125),
   ( 23, '1 lb Box',          10,  215,  100,  42),
   ( 52, 'Jacket',            60, 3295,  300,  15),
   (165, 'Jean',              65,  825,  500,  33),
   (258, 'Shirt',             58,  650, 1200,  33),
   (120, 'Twin Sheet',        26,  800,  750, 213),
   (301, 'Boy''s Jean Suit',  43, 1250,  500,  33),
   (121, 'Queen Sheet',       26, 1375,  600, 213),
   (101, 'Slacks',            63, 1600,  325,  15),
   (115, 'Gold Ring',         14, 4995,   10, 199),
   ( 25, '2 lb Box, Mix',     10,  450,   75,  42),
   (119, 'Squeeze Ball',      49,  250,  400,  89),
   ( 11, 'Wash Cloth',         1,   75,  575, 213),
   ( 19, 'Bellbottoms',       43,  450,  600,  33),
   ( 21, 'ABC Blocks',         1,  198,  405, 125),
   (107, 'The ''Feel'' Book', 35,  225,  225,  89),
   (127, 'Ski Jumpsuit',      65, 4350,  125,  15);
COMMIT;

/*
The relation jbparts

-------------------------------------------------------------------
|  ID       |       NAME        | COLOR  |  WEIGHT   |    QOH    |
-------------------------------------------------------------------
|     1     | central processor  |  pink  |    10     |     1     |
|     2     |       memory       |  gray  |    20     |    32     |
|     3     |     disk drive     | black  |    685    |     2     |
|     4     |     tape drive     | black  |    450    |     4     |
|     5     |       tapes        |  gray  |     1     |    250    |
|     6     |    line printer    | yellow |    578    |     3     |
|     7     |     l-p paper      | white  |    15     |    95     |
|     8     |     terminals      |  blue  |    19     |    15     |
|    13     | paper tape reader  | black  |    107    |     0     |
|    14     |  paper tape punch  | black  |    147    |     0     |
|     9     |   terminal paper   | white  |     2     |    350    |
|    10     |     byte-soap      | clear  |     0     |    143    |
|    11     |    card reader     |  gray  |    327    |     0     |
|    12     |     card punch     |  gray  |    427    |     0     |
-------------------------------------------------------------------
(14 rows)
*/

INSERT INTO jbparts VALUES
   ( 1, 'central processor',  'pink',   10,   1),
   ( 2,            'memory',  'gray',   20,  32),
   ( 3,        'disk drive',  'black', 685,   2),
   ( 4,        'tape drive',  'black', 450,   4),
   ( 5,             'tapes',   'gray',   1, 250),
   ( 6,      'line printer', 'yellow', 578,   3),
   ( 7,         'l-p paper',  'white',  15,  95),
   ( 8,         'terminals',   'blue',  19,  15),
   (13, 'paper tape reader',  'black', 107,   0),
   (14,  'paper tape punch',  'black', 147,   0),
   ( 9,    'terminal paper',  'white',   2, 350),
   (10,         'byte-soap',  'clear',   0, 143),
   (11,       'card reader',   'gray', 327,   0),
   (12,        'card punch',   'gray', 427,   0);
COMMIT;

/*
The relation jbsupply

---------------------------------------------------------
|  SUPPLIER |   PART    |     SHIPDATE      |   QUAN    |
---------------------------------------------------------
|    475    |     1     |1993-12-31 00:00:00|     1     |
|    475    |     2     |1994-05-31 00:00:00|    32     |
|    475    |     3     |1993-12-31 00:00:00|     2     |
|    475    |     4     |1994-05-31 00:00:00|     1     |
|    122    |     7     |1995-02-01 00:00:00|    144    |
|    122    |     7     |1995-02-02 00:00:00|    48     |
|    122    |     9     |1995-02-01 00:00:00|    144    |
|    440    |     6     |1994-10-10 00:00:00|     2     |
|    241    |     4     |1993-12-31 00:00:00|     1     |
|    62     |     3     |1994-06-18 00:00:00|     3     |
|    475    |     2     |1993-12-31 00:00:00|    32     |
|    475    |     1     |1994-07-01 00:00:00|     1     |
|     5     |     4     |1994-11-15 00:00:00|     3     |
|     5     |     4     |1995-01-22 00:00:00|     6     |
|    20     |     5     |1995-01-10 00:00:00|    20     |
|    20     |     5     |1995-01-11 00:00:00|    75     |
|    241    |     1     |1995-06-01 00:00:00|     1     |
|    241    |     2     |1995-06-01 00:00:00|    32     |
|    241    |     3     |1995-06-01 00:00:00|     1     |
|    67     |     4     |1995-07-01 00:00:00|     1     |
|    999    |    10     |1996-01-01 00:00:00|    144    |
|    241    |     8     |1995-07-01 00:00:00|     1     |
|    241    |     9     |1995-07-01 00:00:00|    144    |
|    89     |     3     |1995-07-04 00:00:00|   1000    | 
|    89     |     4     |1995-07-04 00:00:00|   1000    | 
---------------------------------------------------------
(25 rows)
*/
INSERT INTO jbsupply VALUES
   (475,  1, '1993-12-31',    1),
   (475,  2, '1994-05-31',   32),
   (475,  3, '1993-12-31',    2),
   (475,  4, '1994-05-31',    1),
   (122,  7, '1995-02-01',  144),
   (122,  7, '1995-02-02',   48),
   (122,  9, '1995-02-01',  144),
   (440,  6, '1994-10-10',    2),
   (241,  4, '1993-12-31',    1),
   ( 62,  3, '1994-06-18',    3),
   (475,  2, '1993-12-31',   32),
   (475,  1, '1994-07-01',    1),
   (  5,  4, '1994-11-15',    3),
   (  5,  4, '1995-01-22',    6),
   ( 20,  5, '1995-01-10',   20),
   ( 20,  5, '1995-01-11',   75),
   (241,  1, '1995-06-01',    1),
   (241,  2, '1995-06-01',   32),
   (241,  3, '1995-06-01',    1),
   ( 67,  4, '1995-06-01',    1),
   (999, 10, '1996-01-01',  144),
   (241,  8, '1995-07-01',    1),
   (241,  9, '1995-07-01',  144),
   ( 89,  3, '1995-07-04', 1000),
   ( 89,  4, '1995-07-04', 1000);
COMMIT;

/*
The relation jbdebit

------------------------------------------------------
|   ID      |       SDATE       | EMPLOYEE  |ACCOUNT |
------------------------------------------------------
|  100581   |1995-01-15 12:06:03|    157    |10000000|
|  100582   |1995-01-15 17:34:27|   1110    |14356540|
|  100586   |1995-01-16 13:53:55|    35     |14096831|
|  100592   |1995-01-17 09:35:23|    129    |10000000|
|  100593   |1995-01-18 12:34:56|    35     |11652133|
|  100594   |1995-01-18 10:10:10|    215    |12591815|
------------------------------------------------------
(8 rows)
*/
 
INSERT INTO jbdebit VALUES
   (100581, '1995-01-15 12:06:03',  157, 10000000),
   (100582, '1995-01-15 17:34:27', 1110, 14356540),
   (100586, '1995-01-16 13:53:55',   35, 14096831),
   (100592, '1995-01-17 09:35:23',  129, 10000000),
   (100593, '1995-01-18 12:34:56',   35, 11652133),
   (100594, '1995-01-18 10:10:10',  215, 12591815);
COMMIT;

/*
The relation jbsale

-------------------------------------
|  DEBIT    |   ITEM    | QUANTITY  |
-------------------------------------
|  100581   |    118    |     5     |
|  100581   |    120    |     1     |
|  100582   |    26     |     1     |
|  100586   |    127    |     3     |
|  100586   |    106    |     2     |
|  100592   |    258    |     1     |
|  100593   |    23     |     2     |
|  100594   |    52     |     1     |
-------------------------------------
(8 rows)
*/

INSERT INTO jbsale VALUES
   (100581, 118, 5),
   (100581, 120, 1),
   (100582,  26, 1),
   (100586, 127, 3),
   (100586, 106, 2),
   (100592, 258, 1),
   (100593,  23, 2),
   (100594,  52, 1);
COMMIT;
