Guest User

Movies

a guest
Feb 1st, 2018
305
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE `Movies`;
  2.  
  3. USE `Movies`;
  4.  
  5. CREATE TABLE IF NOT EXISTS `directors`(
  6.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  7.     `director_name` VARCHAR(50) NOT NULL,
  8.     `notes` TEXT
  9. );
  10.  
  11. INSERT INTO `directors` (`director_name`)
  12.     VALUES ('Kiro'),
  13.         ('Miro'),
  14.         ('Tosho'),
  15.         ('Gosho'),
  16.         ('Ani');
  17.  
  18. CREATE TABLE IF NOT EXISTS `genres`(
  19.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  20.     `genre_name` VARCHAR(50) NOT NULL,
  21.     `notes` TEXT
  22. );
  23.  
  24. INSERT INTO `genres` (`genre_name`)
  25.     VALUES ('KiroG'),
  26.         ('MiroG'),
  27.         ('ToshoG'),
  28.         ('GoshoG'),
  29.         ('AniG');
  30.  
  31. CREATE TABLE IF NOT EXISTS `categories`(
  32.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  33.     `category_name` VARCHAR(50) NOT NULL,
  34.     `notes` TEXT
  35. );
  36.  
  37. INSERT INTO `categories` (`category_name`)
  38.     VALUES ('KiroC'),
  39.         ('MiroC'),
  40.         ('ToshoC'),
  41.         ('GoshoC'),
  42.         ('AniC');
  43.  
  44. CREATE TABLE IF NOT EXISTS `movies`(
  45.     `id` INT AUTO_INCREMENT PRIMARY KEY,
  46.     `title` VARCHAR(50) NOT NULL UNIQUE,
  47.     `director_id` INT,
  48.     `copyright_year` INT(4),
  49.     `length` INT(3),
  50.     `genre_id` INT,
  51.     `category_id` INT,
  52.     `rating` DECIMAL,
  53.     `notes` TEXT
  54. );
  55.  
  56. ALTER TABLE `movies`
  57.     ADD CONSTRAINT `FK_movies_directors`
  58.     FOREIGN KEY (`director_id`) REFERENCES `directors` (`id`);
  59.    
  60. ALTER TABLE `movies`
  61.     ADD CONSTRAINT `FK_movies_genres`
  62.     FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`);   
  63.    
  64. ALTER TABLE `movies`
  65.     ADD CONSTRAINT `FK_movies_categories`
  66.     FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);    
  67.    
  68. INSERT INTO `movies` (`title`,`director_id`,`genre_id`,`category_id`)
  69.     VALUES ('KiroM',1,1,1),
  70.         ('MiroM',2,2,2),
  71.         ('ToshoM',3,3,3),
  72.         ('GoshoM',4,4,4),
  73.         ('AniM',5,5,5);
RAW Paste Data