The following script ran and produced tables in the database membership.

CREATE TABLE people

(

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

title VARCHAR(25) BINARY,

firstname VARCHAR(25) BINARY,

middlename VARCHAR(25) BINARY,

lastname VARCHAR(25) BINARY,

shiftcontact VARCHAR(50) BINARY,

shiftstatus ENUM('exper', 'worker', 'untrained') NOT NULL DEFAULT 'untrained',

shiftstatus2 ENUM('available','not available') NOT NULL DEFAULT 'available',

 

PRIMARY KEY(id)

)

;

 

 

CREATE TABLE institutes

(

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

acronym VARCHAR(20),

rep SMALLINT UNSIGNED REFERENCES people,

name VARCHAR(100) BINARY,

address1 VARCHAR(100) BINARY,

address2 VARCHAR(100) BINARY,

address3 VARCHAR(100) BINARY,

address4 VARCHAR(100) BINARY,

address5 VARCHAR(100) BINARY,

web VARCHAR(100) BINARY,

phone VARCHAR(25) BINARY,

fax VARCHAR(25) BINARY,

CLASmember ENUM('Y','N'),

SOSokay ENUM('Y','N'),

SOSlink VARCHAR(100) BINARY,

PRIMARY KEY(id)

)

;

 

CREATE TABLE JMU

(

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

person SMALLINT UNSIGNED REFERENCES people,

institute SMALLINT UNSIGNED REFERENCES institute,

address1 VARCHAR(100) BINARY,

address2 VARCHAR(100) BINARY,

address3 VARCHAR(100) BINARY,

address4 VARCHAR(100) BINARY,

address5 VARCHAR(100) BINARY,

web VARCHAR(100) BINARY,

phone VARCHAR(25) BINARY,

fax VARCHAR(25) BINARY,

PRIMARY KEY(id)

)

;

 

CREATE TABLE history

(

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

start DATE,

end DATE DEFAULT '9999-01-01',

person SMALLINT UNSIGNED REFERENCES people,

institute1 SMALLINT UNSIGNED REFERENCES institute,

institute2 SMALLINT UNSIGNED REFERENCES institute,

institute3 SMALLINT UNSIGNED REFERENCES institute,

CLASstatus SMALLINT UNSIGNED REFERENCES CLASstatus,

comment VARCHAR(250) BINARY,

PRIMARY KEY(id)

)

;

 

 

CREATE TABLE CLASstatus

(

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

type VARCHAR(10) BINARY DEFAULT 'observer',

PRIMARY KEY(id)

)

;