--
-- A simple employee database illustrating how a primary
-- key in one table can be used in another table (foreign key).
-- Each employee has an id number. 
--
-- The jobs table records the number of hours an employee
-- has worked. It also contains an employee ID
-- to indicate which employee is being referenced.
--
-- This table could be expanded to include a job id to
-- refer to the type of each job.

CREATE DATABASE IF NOT EXISTS employee_db;
USE employee_db;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS jobs;

CREATE TABLE employees
(
   employeeID SMALLINT NOT NULL,
   name VARCHAR(20) NOT NULL,
   position VARCHAR(20) NOT NULL,
   address VARCHAR(40) NOT NULL,
   PRIMARY KEY (employeeID)
) TYPE=MyISAM;


CREATE TABLE jobs
(
   employeeID SMALLINT NOT NULL,
   hours DECIMAL(5,2) NOT NULL
) TYPE=MyISAM;

--
-- Create some employee entries
--

INSERT INTO employees VALUES (1001, 'Fred', 'programmer', '13 Windle St');
INSERT INTO employees VALUES (1002, 'Joan', 'programmer', '23 Rock St');
INSERT INTO employees VALUES (1003, 'Bill', 'manager', '37 Front St');

--
-- Create some job entries
--

INSERT INTO jobs VALUES (1001, 13.5);
INSERT INTO jobs VALUES (1002, 2);
INSERT INTO jobs VALUES (1002, 6.25);
INSERT INTO jobs VALUES (1003, 4);
INSERT INTO jobs VALUES (1001, 1);
INSERT INTO jobs VALUES (1003, 7);
INSERT INTO jobs VALUES (1003, 9.5);

