Intro

This database is primarily a LIMS to keep track of user and sample imformation for the wet half of the project.

Dictionary

• data_dictionary.xls

Schema

#SQL Scripts for creating lims schema
CREATE TABLE `person` (
`person_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`last_name` VARCHAR(32),
`first_name` VARCHAR(32),
`title` VARCHAR(32),
`email` VARCHAR(40),
`phone_land` VARCHAR(20),
`phone_cell` VARCHAR(20),
`lab_id` SMALLINT(5),
PRIMARY KEY (`person_id`)
)TYPE = InnoDB COMMENT = 'contact person';

CREATE TABLE `laboratory` (
`lab_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`institution` VARCHAR(80),
`address1` VARCHAR(80),
`address2` VARCHAR(80),
`address3` VARCHAR(80),
`contact_id` MEDIUMINT(8),
PRIMARY KEY (`lab_id`)
) TYPE = InnoDB COMMENT = 'laboratory';

CREATE TABLE `document` (
`doc_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255),
`authors` VARCHAR(255),
`citation` VARCHAR(255),
`doc_description` VARCHAR(255),
`doc_file` VARCHAR(80),
PRIMARY KEY (`doc_id`)
) TYPE = InnoDB COMMENT = 'document';

CREATE TABLE `method` (
`method_id` INT(10)  UNSIGNED NOT NULL AUTO_INCREMENT,
`method_type` VARCHAR(40),
`description` VARCHAR(80),
`doc_id` INT(10),
PRIMARY KEY (`method_id`)
) TYPE = InnoDB COMMENT = 'method';

CREATE TABLE `study` (
`study_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(80),
`description` VARCHAR(255),
PRIMARY KEY (`study_id`),
UNIQUE(`name`)
) TYPE = InnoDB COMMENT = 'study';

CREATE TABLE `subject` (
`subject_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20),
`external_id` VARCHAR(80),
`description` VARCHAR(255),
`study_id` INT(10),
`doc_id` INT(10),
PRIMARY KEY(`subject_id`),
UNIQUE (`name`)
) TYPE = InnoDB COMMENT = 'study subjects';

CREATE TABLE `sample_type` (
`name` VARCHAR(32),
`description` VARCHAR(255),
PRIMARY KEY(`name`)
) TYPE = InnoDB COMMENT = 'sample type';

CREATE TABLE `sample` (
`sample_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(24),
`subject_id` INT(10),
`sample_type` VARCHAR(32),
`status` VARCHAR(16),
`container_id` INT(10),
`container_column` SMALLINT(5),
`container_row` SMALLINT(5),
`concentration` VARCHAR(10),
`amount` VARCHAR(10),
`doc_id` INT(10),
PRIMARY KEY(`sample_id`)
) TYPE = InnoDB COMMENT = 'sample';

CREATE TABLE `sample_composition` (
`sample_id` INT(10) UNSIGNED NOT NULL,
`component_id` INT(10) UNSIGNED NOT NULL,
`transfer_vol` VARCHAR(10),
`dilution_vol` VARCHAR(10)
) TYPE =InnoDB COMMENT ='link between child and parent sample';

CREATE TABLE `container_type` (
`name` VARCHAR(16),
`description` VARCHAR(255),
`number_of_columns` SMALLINT(5),
`number_of_rows` SMALLINT(5),
`sample_type` VARCHAR(32),
PRIMARY KEY (`name`)
) TYPE =InnoDB COMMENT = 'Type of container';

CREATE TABLE `container`(
`container_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(40),
`description` VARCHAR(255),
`status` VARCHAR(16),
`container_type` VARCHAR(16),
`location_id` INT(5),
PRIMARY KEY (`container_id`),
UNIQUE(`name`)
)TYPE = InnoDB COMMENT='container';

CREATE TABLE `location_hierarchy` (
`location_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50),
`lab_id` SMALLINT(5),
`bldg_room` VARCHAR(50),
`unit_type` VARCHAR(16),
`unit_name` VARCHAR(32),
`subunit_type` VARCHAR(16),
`subunit_name` VARCHAR(32),
PRIMARY KEY (`location_id`),
UNIQUE(`name`)
) TYPE = InnoDB COMMENT ='location';

#Transactions
CREATE TABLE `container_transaction` (
`con_trans_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`con_trans_type` CHAR( 1 ) NOT NULL ,
`con_trans_date` TIMESTAMP,
`lims_user_id` SMALLINT( 5 ) UNSIGNED ZEROFILL,
PRIMARY KEY ( `con_trans_id` )
) TYPE = InnoDB COMMENT = 'container transaction';

CREATE TABLE `container_transaction_type` (
`con_trans_type` CHAR(1),
`description` VARCHAR(80),
PRIMARY KEY (`con_trans_type`)
) TYPE = InnoDB COMMENT = 'container transaction type';

CREATE TABLE `sample_transaction`(
`sample_trans_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sample_trans_type` CHAR(1),
`sample_trans_date` TIMESTAMP,
`lims_user_id` SMALLINT(5),
PRIMARY KEY(`sample_trans_id`)
)TYPE = InnoDB COMMENT='sample transaction';

CREATE TABLE `sample_transaction_type` (
`sample_trans_type` CHAR(1),
`description` VARCHAR(80),
PRIMARY KEY (`sample_trans_type`)
) TYPE = InnoDB COMMENT = 'sample transaction type';

#Data Repository
CREATE TABLE `collection`(
`collection_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`collection_date` DATE,
`collected_by` INT(10),
PRIMARY KEY (`collection_id`)
) TYPE = InnoDB COMMENT = 'phenotype data collection';

# more fields can be added here
CREATE TABLE `phenotype` (
`subject_id` INT(10) UNSIGNED NOT NULL,
`collection_id` INT(10) UNSIGNED NOT NULL
) TYPE = InnoDB COMMENT='phenotype';

CREATE TABLE `pedigree` (
subject_id INT(10) UNSIGNED NOT NULL,
m_parent_id INT(10) UNSIGNED NOT NULL,
f_parent_id INT(10) UNSIGNED NOT NULL,
gender char(1),
family_name VARCHAR(20),
PRIMARY KEY (`subject_id`)
) TYPE = InnoDB COMMENT = 'pedigree information for subjects';

CREATE TABLE `genotype` (
subject_id INT(10) UNSIGNED NOT NULL,
marker_name VARCHAR(80),
allele1 VARCHAR(16),
allele2 VARCHAR(16),
allele3 VARCHAR(16),
PRIMARY KEY (`subject_id`)
) TYPE = InnoDB COMMENT = 'genotype';

#System Control
CREATE TABLE `lims_user` (
`user_id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_name` VARCHAR( 16 ) NOT NULL ,
`password` VARCHAR( 16 ) NOT NULL ,
`role_id` SMALLINT( 5 ) UNSIGNED,
`person_id` SMALLINT( 5 ) UNSIGNED,
`active` CHAR(1) DEFAULT 'T' NOT NULL,
`start_up` VARCHAR(80),
PRIMARY KEY ( `user_id` ),
UNIQUE (`user_name`)
) TYPE = InnoDB COMMENT = 'lims user';

CREATE TABLE `privilege` (
`privilege_id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 16 ) NOT NULL ,
`description` VARCHAR( 80 ) NOT NULL ,
PRIMARY KEY ( `privilege_id` ) ,
UNIQUE ( `name` )
) TYPE = InnoDB COMMENT = 'lims user privilege';

CREATE TABLE `lims_role` (
`role_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`description` VARCHAR(80),
PRIMARY KEY (`role_id`),
UNIQUE (`name`)
)TYPE = InnoDB COMMENT = 'lims role';

CREATE TABLE `role_privilege` (
`role_id` SMALLINT(5) UNSIGNED NOT NULL,
`privilege_id` SMALLINT(5) UNSIGNED NOT NULL
)TYPE = InnoDB COMMENT = 'lims role privilege';

CREATE TABLE `sys_config` (
`config_id` SMALLINT(5) UNSIGNED NOT NULL,
`name` VARCHAR(16),
`description` VARCHAR(80),
`value` VARCHAR(80)
) TYPE = InnoDB COMMENT = 'system configuration';