/*********************************************************************

This document is a collection of the SQL queries used in the
"Understanding Relational Database Design" tutorial at kirupa.com

The SQL was written for MySQL v4+ databases.

Visit kirupa.com for the full tutorial.

*********************************************************************/





/***** CREATE the database *****/
CREATE DATABASE `kirupa_portfolio` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
/***** End CREATE *****/



/***** CREATES tbl_works *****/
CREATE TABLE `tbl_works` (
  `work_id` INT NOT NULL AUTO_INCREMENT, 
  `work_title` VARCHAR(50) NOT NULL, 
  `work_dscpn` TEXT NOT NULL, 
  `work_image_url` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`work_id`)
)
TYPE = myisam;
/***** End CREATE *****/



/***** INSERTS sample data into tbl_works *****/
INSERT INTO `tbl_works` (
	`work_id`,
	`work_title`,
	`work_dscpn`,
	`work_image_url`)
VALUES (
	'',
	'Image Uploader',
	'Allows users to upload an image to the server using a Flash form.',
	'http://mysite.com/images/image_uploader.jpg'
);
INSERT INTO `tbl_works` (
	`work_id`,
	`work_title`,
	`work_dscpn`,
	`work_image_url`)
VALUES (
	'',
	'Super Duper Identity',
	'The company asked us to create an identity package including logo.',
	'http://mysite.com/images/super_duper.jpg'
);
INSERT INTO `tbl_works` (
	`work_id`,
	`work_title`,
	`work_dscpn`,
	`work_image_url`)
VALUES (
	'',
	'RSS Aggregator',
	'Collects data from a given list of RSS feeds.',
	'http://mysite.com/images/rss_aggregator.jpg'
);
INSERT INTO `tbl_works` (
	`work_id`,
	`work_title`,
	`work_dscpn`,
	`work_image_url`)
VALUES (
	'',
	'Order Tracking System',
	'Tracks orders made through a web interface',
	'http://mysite.com/images/order_track_system.jpg'
);

/***** End INSERT *****/


/***** CREATES tbl_categories *****/
CREATE TABLE `tbl_categories` (
  `category_id` INT NOT NULL AUTO_INCREMENT, 
  `category_name` VARCHAR(20) NOT NULL, 
  `category_dscpn` TEXT NOT NULL,
  PRIMARY KEY (`category_id`)
)
TYPE = myisam;
/***** End CREATE *****/

/***** INSERTS sample data into tbl_categories *****/
INSERT INTO `tbl_categories` (
	`category_id`,
	`category_name`,
	`category_dscpn`
)
VALUES (
	'',
	'Websites',
	'Websites can be static or dynamic. Some involve database functionality.'
);
INSERT INTO `tbl_categories` (
	`category_id`,
	`category_name`,
	`category_dscpn`
)
VALUES (
	'',
	'Logos',
	'Logos are done in vector based applications so that they can be reproduced at different sizes without reducing quality.'
);
/***** End INSERT *****/


/***** CREATES tbl_works_categories *****/
CREATE TABLE `tbl_works_categories` (
  `work_id` INT NOT NULL, 
  `category_id` INT NOT NULL
)
TYPE = myisam;
/***** End CREATE *****/

/***** INSERTS sample data into tbl_works_categories *****/
INSERT INTO `tbl_works_categories` (
	`work_id`,
	`category_id`
)
VALUES (
	'1',
	'1'
);
INSERT INTO `tbl_works_categories` (
	`work_id`,
	`category_id`
)
VALUES (
	'2',
	'1'
);
INSERT INTO `tbl_works_categories` (
	`work_id`,
	`category_id`
)
VALUES (
	'3',
	'2'
);
INSERT INTO `tbl_works_categories` (
	`work_id`,
	`category_id`
)
VALUES (
	'4',
	'1'
);
/***** End INSERT *****/


/***** CREATES tbl_tech *****/
CREATE TABLE `tbl_tech` (
  `tech_id` INT NOT NULL AUTO_INCREMENT, 
  `tech_name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`tech_id`)
)
TYPE = myisam;
/***** End CREATE *****/

/***** INSERTS sample data into tbl_tech *****/
INSERT INTO `tbl_tech` (
	`tech_id`,
	`tech_name`
)
VALUES (
	'',
	'(X)HTML'
);
INSERT INTO `tbl_tech` (
	`tech_id`,
	`tech_name`
)
VALUES (
	'',
	'PHP'
);
INSERT INTO `tbl_tech` (
	`tech_id`,
	`tech_name`
)
VALUES (
	'',
	'Flash'
);
INSERT INTO `tbl_tech` (
	`tech_id`,
	`tech_name`
)
VALUES (
	'',
	'Illustrator'
);
INSERT INTO `tbl_tech` (
	`tech_id`,
	`tech_name`
)
VALUES (
	'',
	'XML'
);
INSERT INTO `tbl_tech` (
	`tech_id`,
	`tech_name`
)
VALUES (
	'',
	'MySQL'
);
INSERT INTO `tbl_tech` (
	`tech_id`,
	`tech_name`
)
VALUES (
	'',
	'CSS'
);
/***** End INSERT *****/


/***** CREATEs tbl_works_tech *****/
CREATE TABLE `tbl_works_tech` (
  `work_id` INT NOT NULL, 
  `tech_id` INT NOT NULL
)
TYPE = myisam;
/***** End CREATE *****/

/***** INSERTS sample data into tbl_works_categories *****/
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'1',
	'3'
);
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'1',
	'2'
);
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'2',
	'3'
);
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'2',
	'5'
);
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'3',
	'4'
);
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'4',
	'1'
);
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'4',
	'2'
);
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'4',
	'6'
);
INSERT INTO `tbl_works_tech` (
	`work_id`,
	`tech_id`
)
VALUES (
	'4',
	'7'
);
/***** End INSERT *****/


/***** CREATE tbl_clients *****/
CREATE TABLE `tbl_clients` (
  `client_id` INT NOT NULL AUTO_INCREMENT, 
  `client_name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`client_id`)
)
TYPE = myisam;
/***** End CREATE *****/

/***** INSERT sample data into tbl_clients *****/
INSERT INTO `tbl_clients` (
	`client_id`, `client_name`
)
VALUES (
	'',
	'Johnson Photography'
);
INSERT INTO `tbl_clients` (
	`client_id`, `client_name`
)
VALUES (
	'',
	'Super Duper Company'
);
INSERT INTO `tbl_clients` (
	`client_id`, `client_name`
)
VALUES (
	'',
	'Smith News Service'
);
/***** End INSERT *****/



/***** CREATE tbl_works_clients *****/
CREATE TABLE `tbl_works_clients` (
  `work_id` INT NOT NULL, 
  `client_id` INT NOT NULL
)
TYPE = myisam;
/***** End CREATE *****/

/***** INSERT sample data into tbl_works_clients *****/
INSERT INTO `tbl_works_clients` (
	`work_id`,
	`client_id`
)
VALUES (
	'1',
	'1'
);
INSERT INTO `tbl_works_clients` (
	`work_id`,
	`client_id`
)
VALUES (
	'2',
	'3'
);
INSERT INTO `tbl_works_clients` (
	`work_id`,
	`client_id`
)
VALUES (
	'3',
	'2'
);
INSERT INTO `tbl_works_clients` (
	`work_id`,
	`client_id`
)
VALUES (
	'4',
	'2'
);
/***** End INSERT *****/