Advertisement
Guest User

Untitled

a guest
Apr 24th, 2014
111
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 8.89 KB | None | 0 0
  1. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  2. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  3. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  4.  
  5. DROP SCHEMA IF EXISTS `cat10e` ;
  6. CREATE SCHEMA IF NOT EXISTS `cat10e` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
  7. SHOW WARNINGS;
  8. USE `cat10e` ;
  9.  
  10. -- -----------------------------------------------------
  11. -- Table `cat10e`.`artist`
  12. -- -----------------------------------------------------
  13. DROP TABLE IF EXISTS `cat10e`.`artist` ;
  14.  
  15. SHOW WARNINGS;
  16. CREATE TABLE IF NOT EXISTS `cat10e`.`artist` (
  17.   `art_id` SMALLINT NOT NULL,
  18.   `art_name` VARCHAR(45) NOT NULL,
  19.   `art_url` VARCHAR(100) NOT NULL,
  20.   `art_note` VARCHAR(255) NOT NULL,
  21.   PRIMARY KEY (`art_id`))
  22. ENGINE = InnoDB;
  23.  
  24. SHOW WARNINGS;
  25.  
  26. -- -----------------------------------------------------
  27. -- Table `cat10e`.`label`
  28. -- -----------------------------------------------------
  29. DROP TABLE IF EXISTS `cat10e`.`label` ;
  30.  
  31. SHOW WARNINGS;
  32. CREATE TABLE IF NOT EXISTS `cat10e`.`label` (
  33.   `label_id` TINYINT NOT NULL,
  34.   `label_type` ENUM('m', 'n', 'i') NOT NULL,
  35.   `label_name` VARCHAR(45) NOT NULL,
  36.   `label_street` VARCHAR(30) NOT NULL,
  37.   `label_city` VARCHAR(20) NOT NULL,
  38.   `label_state` VARCHAR(2) NOT NULL,
  39.   `label_zip` CHAR(9) NOT NULL,
  40.   `label_url` VARCHAR(100) NOT NULL,
  41.   `label_note` VARCHAR(255) NULL,
  42.   PRIMARY KEY (`label_id`))
  43. ENGINE = InnoDB;
  44.  
  45. SHOW WARNINGS;
  46.  
  47. -- -----------------------------------------------------
  48. -- Table `cat10e`.`album`
  49. -- -----------------------------------------------------
  50. DROP TABLE IF EXISTS `cat10e`.`album` ;
  51.  
  52. SHOW WARNINGS;
  53. CREATE TABLE IF NOT EXISTS `cat10e`.`album` (
  54.   `album_id` SMALLINT NOT NULL,
  55.   `art_id` SMALLINT NOT NULL,
  56.   `label_id` TINYINT NOT NULL,
  57.   `album_name` VARCHAR(45) NOT NULL,
  58.   `album_cost` DECIMAL(4,2) NOT NULL,
  59.   `album_tracks` TINYINT NOT NULL,
  60.   `album_rating` DECIMAL(2,1) NOT NULL,
  61.   `album_producer` VARCHAR(45) NOT NULL,
  62.   `album_genre` VARCHAR(45) NOT NULL,
  63.   `album_note` VARCHAR(255) NULL,
  64.   PRIMARY KEY (`album_id`, `art_id`, `label_id`),
  65.   INDEX `fk_album_artist_idx` (`art_id` ASC),
  66.   INDEX `fk_album_label1_idx` (`label_id` ASC),
  67.   CONSTRAINT `fk_album_artist`
  68.     FOREIGN KEY (`art_id`)
  69.     REFERENCES `cat10e`.`artist` (`art_id`)
  70.     ON DELETE NO ACTION
  71.     ON UPDATE NO ACTION,
  72.   CONSTRAINT `fk_album_label1`
  73.     FOREIGN KEY (`label_id`)
  74.     REFERENCES `cat10e`.`label` (`label_id`)
  75.     ON DELETE NO ACTION
  76.     ON UPDATE NO ACTION)
  77. ENGINE = InnoDB;
  78.  
  79. SHOW WARNINGS;
  80.  
  81. -- -----------------------------------------------------
  82. -- Table `cat10e`.`recording`
  83. -- -----------------------------------------------------
  84. DROP TABLE IF EXISTS `cat10e`.`recording` ;
  85.  
  86. SHOW WARNINGS;
  87. CREATE TABLE IF NOT EXISTS `cat10e`.`recording` (
  88.   `rec_id` SMALLINT NOT NULL,
  89.   `album_id` SMALLINT NOT NULL,
  90.   `rec_duration` TIME NOT NULL,
  91.   `rec_genre` VARCHAR(45) NOT NULL,
  92.   `rec_cost` DECIMAL(4,2) NOT NULL,
  93.   `rec_title` VARCHAR(45) NOT NULL,
  94.   `rec_date` DATE NOT NULL,
  95.   `rec_note` VARCHAR(255) NULL,
  96.   PRIMARY KEY (`rec_id`, `album_id`),
  97.   INDEX `fk_recording_album1_idx` (`album_id` ASC),
  98.   CONSTRAINT `fk_recording_album1`
  99.     FOREIGN KEY (`album_id`)
  100.     REFERENCES `cat10e`.`album` (`album_id`)
  101.     ON DELETE NO ACTION
  102.     ON UPDATE NO ACTION)
  103. ENGINE = InnoDB;
  104.  
  105. SHOW WARNINGS;
  106.  
  107. -- -----------------------------------------------------
  108. -- Table `cat10e`.`format`
  109. -- -----------------------------------------------------
  110. DROP TABLE IF EXISTS `cat10e`.`format` ;
  111.  
  112. SHOW WARNINGS;
  113. CREATE TABLE IF NOT EXISTS `cat10e`.`format` (
  114.   `format_id` SMALLINT NOT NULL,
  115.   `rec_id` SMALLINT NOT NULL,
  116.   `format_type` VARCHAR(15) NOT NULL,
  117.   `format_note` VARCHAR(255) NULL,
  118.   PRIMARY KEY (`format_id`, `rec_id`),
  119.   INDEX `fk_format_recording1_idx` (`rec_id` ASC),
  120.   CONSTRAINT `fk_format_recording1`
  121.     FOREIGN KEY (`rec_id`)
  122.     REFERENCES `cat10e`.`recording` (`rec_id`)
  123.     ON DELETE NO ACTION
  124.     ON UPDATE NO ACTION)
  125. ENGINE = InnoDB;
  126.  
  127. SHOW WARNINGS;
  128.  
  129. -- -----------------------------------------------------
  130. -- Table `cat10e`.`member`
  131. -- -----------------------------------------------------
  132. DROP TABLE IF EXISTS `cat10e`.`member` ;
  133.  
  134. SHOW WARNINGS;
  135. CREATE TABLE IF NOT EXISTS `cat10e`.`member` (
  136.   `mem_id` SMALLINT NOT NULL,
  137.   `mem_fname` VARCHAR(20) NOT NULL,
  138.   `mem_lname` VARCHAR(30) NOT NULL,
  139.   `mem_note` VARCHAR(255) NOT NULL,
  140.   PRIMARY KEY (`mem_id`))
  141. ENGINE = InnoDB;
  142.  
  143. SHOW WARNINGS;
  144.  
  145. -- -----------------------------------------------------
  146. -- Table `cat10e`.`participant`
  147. -- -----------------------------------------------------
  148. DROP TABLE IF EXISTS `cat10e`.`participant` ;
  149.  
  150. SHOW WARNINGS;
  151. CREATE TABLE IF NOT EXISTS `cat10e`.`participant` (
  152.   `art_id` SMALLINT NOT NULL,
  153.   `mem_id` SMALLINT NOT NULL,
  154.   `part_startdate` DATE NOT NULL,
  155.   `part_enddate` DATE NOT NULL,
  156.   PRIMARY KEY (`art_id`, `mem_id`),
  157.   INDEX `fk_participant_artist1_idx` (`art_id` ASC),
  158.   INDEX `fk_participant_member1_idx` (`mem_id` ASC),
  159.   CONSTRAINT `fk_participant_artist1`
  160.     FOREIGN KEY (`art_id`)
  161.     REFERENCES `cat10e`.`artist` (`art_id`)
  162.     ON DELETE NO ACTION
  163.     ON UPDATE NO ACTION,
  164.   CONSTRAINT `fk_participant_member1`
  165.     FOREIGN KEY (`mem_id`)
  166.     REFERENCES `cat10e`.`member` (`mem_id`)
  167.     ON DELETE NO ACTION
  168.     ON UPDATE NO ACTION)
  169. ENGINE = InnoDB;
  170.  
  171. SHOW WARNINGS;
  172.  
  173. SET SQL_MODE=@OLD_SQL_MODE;
  174. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  175. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  176.  
  177. -- -----------------------------------------------------
  178. -- Data for table `cat10e`.`album`
  179. -- -----------------------------------------------------
  180. START TRANSACTION;
  181. USE `cat10e`;
  182. INSERT INTO `cat10e`.`album` (`album_id`, `art_id`, `label_id`, `album_name`, `album_cost`, `album_tracks`, `album_rating`, `album_producer`, `album_genre`, `album_note`) VALUES (1, 1, 1, 'legends of country', 55.55, 4, 4, 'poor jim', 'country', NULL);
  183. INSERT INTO `cat10e`.`album` (`album_id`, `art_id`, `label_id`, `album_name`, `album_cost`, `album_tracks`, `album_rating`, `album_producer`, `album_genre`, `album_note`) VALUES (2, 2, 2, 'legends of rock', 22.22, 5, 8, 'rich joe', 'rock', NULL);
  184. INSERT INTO `cat10e`.`album` (`album_id`, `art_id`, `label_id`, `album_name`, `album_cost`, `album_tracks`, `album_rating`, `album_producer`, `album_genre`, `album_note`) VALUES (3, 3, 3, 'legends of rock vol2', 22.22, 5, 9, 'rocker inc', 'rock', NULL);
  185. INSERT INTO `cat10e`.`album` (`album_id`, `art_id`, `label_id`, `album_name`, `album_cost`, `album_tracks`, `album_rating`, `album_producer`, `album_genre`, `album_note`) VALUES (4, 4, 4, 'legends of rap', 66.66, 8, 8, 'hip hop lords', 'hip hop', NULL);
  186. INSERT INTO `cat10e`.`album` (`album_id`, `art_id`, `label_id`, `album_name`, `album_cost`, `album_tracks`, `album_rating`, `album_producer`, `album_genre`, `album_note`) VALUES (5, 5, 5, 'random noises', 11.11, 1, 1, 'worst guys', 'dubstep', NULL);
  187.  
  188. COMMIT;
  189.  
  190.  
  191. -- -----------------------------------------------------
  192. -- Data for table `cat10e`.`recording`
  193. -- -----------------------------------------------------
  194. START TRANSACTION;
  195. USE `cat10e`;
  196. INSERT INTO `cat10e`.`recording` (`rec_id`, `album_id`, `rec_duration`, `rec_genre`, `rec_cost`, `rec_title`, `rec_date`, `rec_note`) VALUES (1, 1, '00:05:43', 'country', 23.45, 'country roads', '1992-02-04', NULL);
  197. INSERT INTO `cat10e`.`recording` (`rec_id`, `album_id`, `rec_duration`, `rec_genre`, `rec_cost`, `rec_title`, `rec_date`, `rec_note`) VALUES (2, 2, '00:04:22', 'rock', 45.65, 'one', '2001-04-04', NULL);
  198. INSERT INTO `cat10e`.`recording` (`rec_id`, `album_id`, `rec_duration`, `rec_genre`, `rec_cost`, `rec_title`, `rec_date`, `rec_note`) VALUES (3, 3, '01:00:00', 'rock', 99.23, 'stairway to heaven', '1999-05-09', NULL);
  199. INSERT INTO `cat10e`.`recording` (`rec_id`, `album_id`, `rec_duration`, `rec_genre`, `rec_cost`, `rec_title`, `rec_date`, `rec_note`) VALUES (4, 4, '00:02:01', 'hiphop', 55.32, 'juicy', '1995-01-02', NULL);
  200. INSERT INTO `cat10e`.`recording` (`rec_id`, `album_id`, `rec_duration`, `rec_genre`, `rec_cost`, `rec_title`, `rec_date`, `rec_note`) VALUES (5, 5, '00:00:01', 'dubstep', 67.87, 'shortest song ever', '2014-04-01', NULL);
  201.  
  202. COMMIT;
  203.  
  204.  
  205. -- -----------------------------------------------------
  206. -- Data for table `cat10e`.`format`
  207. -- -----------------------------------------------------
  208. START TRANSACTION;
  209. USE `cat10e`;
  210. INSERT INTO `cat10e`.`format` (`format_id`, `rec_id`, `format_type`, `format_note`) VALUES (1, 1, 'mp3', NULL);
  211. INSERT INTO `cat10e`.`format` (`format_id`, `rec_id`, `format_type`, `format_note`) VALUES (2, 2, 'wav', NULL);
  212. INSERT INTO `cat10e`.`format` (`format_id`, `rec_id`, `format_type`, `format_note`) VALUES (3, 3, 'mp4', NULL);
  213. INSERT INTO `cat10e`.`format` (`format_id`, `rec_id`, `format_type`, `format_note`) VALUES (4, 4, 'oggvorbis', NULL);
  214. INSERT INTO `cat10e`.`format` (`format_id`, `rec_id`, `format_type`, `format_note`) VALUES (5, 5, 'divx', NULL);
  215.  
  216. COMMIT;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement