SpEAK:Speak sql

/** Speak Create Table sql code
 * @authors andy

drop database if exists speak; create database speak; use speak;

/* must use innodb engine in order to enforce foreign key constraints */ set storage_engine = innodb;

/* drop child tables before you drop their parent tables */ drop table if exists experiment_admin; drop table if exists author; drop table if exists viewer; drop table if exists experiment_user; drop table if exists accessible_experiment; drop table if exists experiment_attachment; drop table if exists experiment_url; drop table if exists experiment; drop table if exists experiment_keyword; drop table if exists keyword;

/* create parent tables before you create their child tables */

This creates the first table Experiment_user where the Unique UserID is crated and stored for every user.

create table experiment_user ( 	UserID int unsigned not null

);

This creates the Second table Experiment_admin who will have full control over the database and experiments.

create table experiment_admin ( 	UserID int unsigned not null, 	LastName varchar(20) not null,	FirstName varchar(20) not null,	Phone varchar (20)not null,	email int not null,	Campus varchar(20) not null );

This is the third database table Author who will have the capability to view and added new data to the experiments.

create table author (	UserID int unsigned not null,	LastName varchar(20) not null,	FirstName varchar(20) not null,	Phone varchar(20) not null,	email int not null,	Campus varchar(20) not null );

This is the forth database table Viewer who will have only view capability so that any person interested in the project may see it. create table viewer (	UserID int unsigned not null );

This is the Fifth table Experiments that stores the Unique experiment number when it was created and its description.

create table experiment (	ExperimentNumber int not null,	UserID int unsigned not null,	Title varchar(20) not null,	DateCreated date not null,	DateModified date not null,	Description varchar(20) not null );

This is the Sixth Table accessible_experiment that will show the user depending upon privileges the experiment.

create table accessible_experiment (	UserID int unsigned not null,	ExperimentNumber int not null );

This is the Seventh table keyword that will store keywords for a experiment search.

create table keyword (	KeywordID int unsigned not null,	KewordName varchar(50) );

This is the eighth table Experiment_keyword that links the experiment number to an keywordID.

create table experiment_keyword (	ExperimentNumber int not null,	KeywordID int unsigned not null );

This is the ninth table experiment_attachment that stores the attachment id for each attachment name that will link to each experiment number. create table experiment_attachment (	ExperimentNumber int not null,	AttachmentID int unsigned not null,	AttachmentName varchar(50) not null );

''' This is the tenth table experiment_url that stores the url of each sound file and links it to each experiment number. ''' create table experiment_url (	ExperimentNumber int not null,	UrlID int unsigned not null,	UrlName varchar(50) not null );

''' This modifies the experiment user table to create userID as the primary key. '''

alter table experiment_user add constraint pk_experiment_user primary key (UserID);

alter table experiment_user modify UserID int unsigned auto_increment; show create table experiment_user\G

''' This modifies the Experiment_admin table to pull the primary key from the experiment_user table. '''

alter table experiment_admin add constraint pk_experiment_admin primary key (UserID); alter table experiment_admin add constraint fk_experiment_admin_experiment_user foreign key (UserID) references experiment_user (UserID);

show create table experiment_admin\G

''' This modifies the author table to pull the primary key from the experiment_user table. '''

alter table author add constraint pk_author primary key (UserID);

alter table author add constraint fk_author_experiment_user foreign key (UserID) references experiment_user (UserID);

show create table author\G

''' This modifies the viewer table to pull its primary key from the experiment_user table. '''

alter table viewer add constraint pk_viewer primary key (UserID);

alter table Viewer add constraint fk_viewer_experiment_user foreign key (UserID) references experiment_user (UserId);

show create table viewer\G

''' This modifies the experiment table to make ExperimentNumber as its Primary Key. '''

alter table experiment add constraint pk_experiment primary key (ExperimentNumber); show create table experiment\G

This modifies the accessible_experiment table to use UserID and ExperimentNumber for a Primary key and pulls them from author and accessible_experiment tables. alter table accessible_experiment add constraint pk_accesible_experiment primary key (UserID, ExperimentNumber); alter table accessible_experiment add constraint fk_accessible_experiment_author foreign key (UserID) references author (UserID); alter table accessible_experiment add constraint fk_accessible_experiment_experiment foreign key (ExperimentNumber) references Experiment (ExperimentNumber); show create table accessible_experiment\G

''' This modifies the experiment_attachment table to use experimentNumber and AttachemntID as primary key and pulls the experimentNumber from the experiment_attachment table. ''' alter table experiment_attachment add constraint pk_experiment_attachment primary kEY (ExperimentNumber, AttachmentID); alter table experiment_attachment add constraint fk_experiment_attachment_experiment foreign key (ExperimentNumber) references Experiment (ExperimentNumber);

show create table experiment_attachment\G ''' This modifies the experiment_url table to use UrlID and ExperimentNumber as primary keys and pulls experimentNumber from the Experiment table. '''

alter table experiment_url add constraint pk_experiment_url primary key (UrlID, ExperimentNumber); alter table experiment_url add constraint fk_experiment_url_experiment foreign key (ExperimentNumber) references Experiment (ExperimentNumber); show create table experiment_url\G

This modifies the keyword table to use keywordID as the Primary key alter table keyword add constraint pk_keyword primary key (KeywordID); alter table keyword modify KeywordID int unsigned auto_increment; show create table keyword\G

'''This modifies the experiment_keyword table to use KeywordID and ExerimentNumber as primary key and pulls both from the experiment and keyword tables. '''

alter table experiment_keyword add constraint pk_experiment_keyword primary key (KeywordID, ExperimentNumber); alter table experiment_keyword add constraint fk_experiment_keyword_experiment foreign key (ExperimentNumber) references Experiment (ExperimentNumber);

alter table Experiment_Keyword add constraint fk_experiment_keyword_keyword foreign key (KeywordID) references Keyword (KeywordID);

show create table experiment_keyword\G