CONNECT TO DBS USER sgi-pool\XXX;

CREATE TABLE jobtype (
	JobID INTEGER NOT NULL GENERATED AS IDENTITY,
	Description VARCHAR (50),
	PRIMARY KEY (JobID)
);

CREATE TABLE department (
	DeptID INTEGER NOT NULL GENERATED AS IDENTITY,
	Name VARCHAR (50),
	Description VARCHAR (255),
	Parent INTEGER,
	PRIMARY KEY (DeptID)
);

CREATE TABLE employee (
	EmpID INTEGER NOT NULL GENERATED AS IDENTITY,
	DeptID INTEGER NOT NULL,
	JobID INTEGER NOT NULL,
	Name VARCHAR (50),
	FamilyName VARCHAR (50),
	Salary INTEGER,
	Age INTEGER,
	PRIMARY KEY (EmpID),
	FOREIGN KEY (DeptID) REFERENCES department,
	FOREIGN KEY (JobID) REFERENCES jobtype
);

INSERT INTO jobtype (Description) VALUES
( 'boss' ),
( 'supervisre' ),
( 'secretary' ),
( 'employee' );

INSERT INTO department (Parent, Name, Description) VALUES
( 0, 'StrangeStuffSoftware', 'Headquarter of this cool but strange software house' ),
( 1, 'Customer Relations', 'Contact to the people that should buy or have bought the software' ),
( 2, 'Support', 'Satisfies the customers needs' ),
( 2, 'Marketing and Sales', 'The team that acquires new customers' ),
( 1, 'DevTeam', 'Developers, Developers, Developers!' ),
( 5, 'Documentation', 'Tries to document the source code and the application' ),
( 5, 'Client', 'Develops the Client Software' ),
( 5, 'Server', 'Develops the Server Software' );

INSERT INTO employee (DeptID, JobID, Name, FamilyName, Salary, Age) VALUES 
( 1, 1, 'Steve the Liver', 'Bobs', 1, 54 ),
( 1, 1, 'Bill', 'Fades', 12500, 53 ),
( 1, 3, 'Sandra', 'Garret', 3400, 37 ),
( 2, 2, 'Christopher', 'Parks', 5300, 48 ),
( 2, 3, 'Jonathan', 'Doe', 2800, 44 ),
( 3, 4, 'Julia', 'Barret', 3200, 33 ),
( 3, 4, 'Roberto', 'Hoskins', 3570, 52),
( 3, 4, 'Mattie', 'Cooke', 3320, 28 ),
( 4, 2, 'Jeanette', 'Solorzano', 5310, 40 ),
( 4, 4, 'Barbara', 'Hawkins', 3890, 36),
( 4, 4, 'Sam', 'Studer', 3745, 42 ),
( 4, 4, 'Dallas', 'Benton', 4075, 33 ),
( 4, 4, 'Ashley', 'Brashier', 3880, 34 ),
( 5, 2, 'John', 'Tarnack', 5300, 26),
( 5, 3, 'Richard', 'Ballmann', 3456, 29 ),
( 6, 4, 'Carmen', 'Dailly', 2799, 29 ),
( 6, 4, 'Allen', 'Hernandez', 3100, 47 ),
( 6, 4, 'Carolyn', 'Stahl', 3000, 28 ),
( 7, 2, 'Henry', 'Baden', 4600, 36),
( 7, 4, 'Evelyn', 'Salas', 3600, 31 ),
( 7, 4, 'Felix', 'Stuart', 3700, 31 ),
( 7, 4, 'Louis', 'Sinclaire', 3480, 39 ),
( 7, 4, 'Dora', 'Gardin', 3500, 37 ),
( 8, 2, 'Elizabeth', 'Carter', 4720, 34 ),
( 8, 4, 'Heather', 'Law', 3990, 28 ),
( 8, 4, 'Elroy', 'Stanton', 3800, 58 ),
( 8, 4, 'Anthony', 'McGuire', 3600, 30 );