-- The Jonson Brothers Ltd. company database
-- MIMER 8.2
-- Populate the company database relations

-- The relation employee

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

-- internal error in MIMER
-- ALTER TABLE employee
--	ADD CONSTRAINT fk_emp_mgr FOREIGN KEY (manager) REFERENCES employee(number)
--	ON DELETE SET NULL;



-- The relation city

INSERT INTO city (name, state)
        VALUES
        ('Los Angeles', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('Oakland', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('El Cerrito', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('Atlanta', 'Ga');
INSERT INTO city (name, state)
        VALUES
        ('San Francisco', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('Boston', 'Mass');
INSERT INTO city (name, state)
        VALUES
        ('Dallas', 'Tex');
INSERT INTO city (name, state)
        VALUES
        ('Denver', 'Colo');
INSERT INTO city (name, state)
        VALUES
        ('White Plains', 'Neb');
INSERT INTO city (name, state)
        VALUES
        ('Amherst', 'Mass');
INSERT INTO city (name, state)
        VALUES
        ('Seattle', 'Wash');
INSERT INTO city (name, state)
        VALUES
        ('Paxton', 'Ill');
INSERT INTO city (name, state)
        VALUES
        ('New York', 'NY');
INSERT INTO city (name, state)
        VALUES
        ('San Diego', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('Hickville', 'Okla');
INSERT INTO city (name, state)
        VALUES
        ('Salt Lake City', 'Utah');
INSERT INTO city (name, state)
        VALUES
        ('Madison', 'Wisc');

-- COMMIT;


-- The relation store

INSERT INTO store (number, city)
	VALUES
	(5, 'San Francisco');
INSERT INTO store (number, city)
	VALUES
	(7, 'Oakland');
INSERT INTO store (number, city)
	VALUES
	(8, 'El Cerrito');


-- The relation dept


INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(35, 'Book', 5, 1, 55);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(10, 'Candy', 5, 1, 13);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(19, 'Furniture', 7, 4, 26);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(20, 'Major Appliances', 7, 4, 26);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(14, 'Jewelry', 8, 1, 33);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(43, 'Children''s', 8, 2, 32);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(65, 'Junior''s', 7, 3, 37);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(58, 'Men''s', 7, 2, 129);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(60, 'Sportswear', 5, 1, 10);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(99, 'Giftwrap', 5, 1, 98);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(1, 'Bargain', 5, 0, 37);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(26, 'Linens', 7, 3, 157);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(63, 'Women''s', 7, 3, 32);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(49, 'Toys', 8, 2, 35);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(70, 'Women''s', 5, 1, 10);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(73, 'Children''s', 5, 1, 10);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(34, 'Stationary', 5, 1, 33);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(47, 'Junior Miss', 7, 2, 129);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(28, 'Women''s', 8, 2, 32);
-- COMMIT;



--The relation supplier

INSERT INTO supplier (number, name, city)
	VALUES
	(199, 'Koret', 'Los Angeles');
INSERT INTO supplier (number, name, city)
	VALUES
	(213,'Cannon', 'Atlanta');
INSERT INTO supplier (number, name, city)
	VALUES
	(33, 'Levi-Strauss', 'San Francisco');
INSERT INTO supplier (number, name, city)
	VALUES
	(89, 'Fisher-Price', 'Boston');
INSERT INTO supplier (number, name, city)
	VALUES
	(125, 'Playskool', 'Dallas');
INSERT INTO supplier (number, name, city)
	VALUES
	(42, 'Whitman''s', 'Denver');
INSERT INTO supplier (number, name, city)
	VALUES
	(15, 'White Stag', 'White Plains');
INSERT INTO supplier (number, name, city)
	VALUES
	(475, 'DEC', 'Amherst');
INSERT INTO supplier (number, name, city)
	VALUES
	(122, 'White Paper', 'Seattle');
INSERT INTO supplier (number, name, city)
	VALUES
	(440, 'Spooley', 'Paxton');
INSERT INTO supplier (number, name, city)
	VALUES
	(241, 'IBM', 'New York');
INSERT INTO supplier (number, name, city)
	VALUES
	(62, 'Data General', 'Atlanta');
INSERT INTO supplier (number, name, city)
	VALUES
	(5, 'Amdahl', 'San Diego');
INSERT INTO supplier (number, name, city)
	VALUES
	(20, 'Wormley', 'Hickville');
INSERT INTO supplier (number, name, city)
	VALUES
	(67, 'Edger', 'Salt Lake City');
INSERT INTO supplier (number, name, city)
	VALUES
	(999, 'A E Neumann', 'Madison');
-- COMMIT;



-- The relation item

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


-- The relation parts

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


-- The relation supply

create table months
	(name char(3) constraint pk_months primary key,
	 num char(2) unique);

insert into months(name, num) values ('JAN', '01');
insert into months(name, num) values ('FEB', '02');
insert into months(name, num) values ('MAR', '03');
insert into months(name, num) values ('APR', '04');
insert into months(name, num) values ('MAY', '05');
insert into months(name, num) values ('JUN', '06');
insert into months(name, num) values ('JUL', '07');
insert into months(name, num) values ('AUG', '08');
insert into months(name, num) values ('SEP', '09');
insert into months(name, num) values ('OCT', '10');
insert into months(name, num) values ('NOV', '11');
insert into months(name, num) values ('DEC', '12');

@
CREATE FUNCTION to_date(char_date CHAR(11))
RETURNS DATE
READS SQL DATA
BEGIN
  declare month_num char(2);
  declare month_name char(3);
  declare year_name char(4);
  declare day_name char(2);

  set year_name = substring(char_date from 8 for 4);  
  set month_name = substring(char_date from 4 for 3);
  select num into month_num from months where name = month_name;
  set day_name = substring(char_date from 1 for 2);  

  RETURN CAST(year_name || '-' || month_num || '-' || day_name AS DATE);
END
@

INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 1, to_date('31-DEC-1993'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 2, to_date('31-MAY-1994'),  32);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 3, to_date('31-DEC-1993'), 2);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 4, to_date('31-MAY-1994'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(122, 7, to_date('01-FEB-1995'), 144);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(122, 7, to_date('02-FEB-1995'), 48);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(122, 9, to_date('01-FEB-1995'), 144);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(440, 6, to_date('10-OCT-1994'), 2);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 4, to_date('31-DEC-1993'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(62, 3, to_date('18-JUN-1994'), 3);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 2, to_date('31-DEC-1993'), 32);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 1, to_date('01-JUL-1994'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(5, 4, to_date('15-NOV-1994'), 3);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(5, 4, to_date('22-JAN-1995'), 6);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(20, 5, to_date('10-JAN-1995'), 20);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(20, 5, to_date('11-JAN-1995'), 75);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 1, to_date('01-JUN-1995'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 2, to_date('01-JUN-1995'), 32);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 3, to_date('01-JUN-1995'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(67, 4, to_date('01-JUL-1995'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(999, 10, to_date('01-JAN-1996'), 144);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 8, to_date('01-JUL-1995'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 9, to_date('01-JUL-1995'), 144);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(89, 3, to_date('04-JUL-1995'), 1000);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(89, 4, to_date('04-JUL-1995'), 1000);
-- COMMIT;



-- The relation debit


INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100581, to_date('15-JAN-1995'), 157, 10000000);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100582, to_date('15-JAN-1995'), 1110, 14356540);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100586, to_date('16-JAN-1995'), 35, 14096831);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100592, to_date('17-JAN-1995'), 129, 10000000);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100593, to_date('18-JAN-1995'), 13, 11652133);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100594, to_date('18-JAN-1995'), 215, 12591815);
-- COMMIT;



-- The relation sale


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

-- To see the contents of the database uncomment and execute the following lines

--SET OUTPUT ON;
--SET ECHO ON;
--SET PAGELENGTH 0;
--SET INPUT, OUTPUT LOG ON;
--LOG INPUT, OUTPUT ON 'company_contents.txt';

--SELECT * FROM employee;
--SELECT * FROM dept;
--SELECT * FROM item;
--SELECT * FROM parts;
--SELECT * FROM sale;
--SELECT * FROM debit;
--SELECT * FROM city;
--SELECT * FROM store;
--SELECT * FROM supply;
--SELECT * FROM supplier;


