Posted By

georgemathewkunnathoor on 08/19/11


Tagged

database for the schema


Versions (?)

Schema for the database DVD


 / Published in: SQL
 

URL: http://www.gladsys.in/blog/stored-procedures-in-mysql/

  1. CREATE DATABASE DVD;
  2.  
  3. USE DVD;
  4.  
  5. CREATE TABLE movie(
  6. id int AUTO_INCREMENT PRIMARY KEY,
  7. movie_name varchar(100) UNIQUE NOT NULL,
  8. release_year int
  9. )engine=innodb;
  10.  
  11. INSERT INTO movie(movie_name,release_year) VALUES ('The King\'s Speech',2010),
  12. ('The Hurt Locker',2009),
  13. ('Slumdog Millionaire',2008);
  14.  
  15.  
  16. CREATE TABLE participant_role(
  17. id int AUTO_INCREMENT PRIMARY KEY,
  18. role varchar(20) UNIQUE NOT NULL
  19. )engine=innodb;
  20.  
  21.  
  22. INSERT INTO participant_role(role) VALUES ('Director'),
  23. ('Actor'),
  24. ('Actress');
  25.  
  26.  
  27. CREATE TABLE movie_participant(
  28. id int AUTO_INCREMENT PRIMARY KEY,
  29. role_id int,
  30. participant_name varchar(100),
  31. movie_id int,
  32. constraint movie_participant_fk1 FOREIGN KEY (role_id) REFERENCES participant_role(id),
  33. constraint movie_participant_fk2 FOREIGN KEY (movie_id) REFERENCES movie(id)
  34. )engine=innodb;
  35.  
  36.  
  37. INSERT INTO movie_participant(role_id,movie_id,participant_name) VALUES (1,1,'Tom Hooper'),
  38. (1,2,'Colin Firth'),
  39. (1,3,'Helena Bonham Carter'),
  40. (2,1,'Kathryn Bigelow'),
  41. (2,2,'Jeremy Renner'),
  42. (2,3,'Sandra Bullock'),
  43. (3,1,'Danny Boyle'),
  44. (3,2,'Dev Patel'),
  45. (3,3,'Freida Pinto');
  46.  
  47.  
  48.  
  49. CREATE TABLE dvd (
  50. id int AUTO_INCREMENT PRIMARY KEY,
  51. dvd_no int UNIQUE NOT NULL,
  52. movie_id int,
  53. availability_status int,
  54. constraint dvd_fk1 FOREIGN KEY(movie_id) REFERENCES movie(id)
  55. )engine=innodb;
  56.  
  57.  
  58. INSERT INTO dvd(dvd_no,movie_id,availability_status) VALUES (101,1,1),
  59. (102,1,1),
  60. (103,1,1),
  61. (104,1,1),
  62. (105,1,1),
  63. (201,2,1),
  64. (202,2,1),
  65. (203,2,1),
  66. (204,2,1),
  67. (205,2,1),
  68. (301,3,1),
  69. (302,3,1),
  70. (303,3,1),
  71. (304,3,1),
  72. (305,3,1);
  73.  
  74.  
  75. CREATE TABLE employee (
  76. id int AUTO_INCREMENT PRIMARY KEY,
  77. employee_no int UNIQUE NOT NULL,
  78. employee_name varchar(100),
  79. address text
  80. )engine=innodb;
  81.  
  82.  
  83. INSERT INTO employee(employee_no, employee_name, address ) VALUES (1,'Albert','Cochin'),
  84. (2,'Choudhari','Calcutta'),
  85. (3,'Chidambaram','Chennai');
  86.  
  87.  
  88. CREATE TABLE customer(
  89. id int AUTO_INCREMENT PRIMARY KEY,
  90. customer_no int UNIQUE NOT NULL,
  91. customer_name varchar(100),
  92. address text
  93. )engine=innodb;
  94.  
  95.  
  96. INSERT INTO customer(customer_no, customer_name, address) VALUES (1,'George','Calicut'),
  97. (2,'Priya','Trichur'),
  98. (3,'Aniya','Kannur');
  99.  
  100.  
  101.  
  102. CREATE TABLE rent(
  103. id int AUTO_INCREMENT PRIMARY KEY,
  104. customer_id int,
  105. employee_id int,
  106. rent_date date,
  107. due_date date,
  108. dvd_id int ,
  109. constraint rent_fk1 FOREIGN KEY(customer_id) REFERENCES customer(id),
  110. constraint rent_fk2 FOREIGN KEY(employee_id) REFERENCES employee(id),
  111. constraint rent_fk3 FOREIGN KEY(dvd_id) REFERENCES dvd(id)
  112. )engine=innodb;
  113.  
  114.  
  115. CREATE TABLE return_dvd(
  116. id int AUTO_INCREMENT PRIMARY KEY,
  117. rent_id int,
  118. employee_id int ,
  119. return_date date,
  120. constraint return_dvd_fk1 FOREIGN KEY(employee_id) REFERENCES employee(id),
  121. constraint return_dvd_fk2 FOREIGN KEY(rent_id) REFERENCES rent(id)
  122. )engine=innodb;

Report this snippet  

You need to login to post a comment.