SpEAK:Speak sql

From Openitware
Jump to: navigation, search

/**

  • @authors andy
  • /

Speak Create Table sql code

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