betoesquivel

AMSS sql ddl

Nov 4th, 2014
407
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.00 KB | None | 0 0
  1. CREATE TABLE escuela (
  2.   id INT NOT NULL,
  3.   nombre VARCHAR(30),
  4.  
  5.   PRIMARY KEY (id)
  6.  
  7. ) ENGINE=INNODB;
  8.  
  9. CREATE TABLE departamento (
  10.   id INT NOT NULL,
  11.   nombre VARCHAR(30),
  12.   idEscuela INT NOT NULL,
  13.   idJefe INT NOT NULL,
  14.  
  15.   PRIMARY KEY (id),
  16.  
  17.   FOREIGN KEY (idEscuela)
  18.     REFERENCES escuela(id)
  19.     ON UPDATE CASCADE
  20.     ON DELETE CASCADE
  21.  
  22. ) ENGINE=INNODB;
  23.  
  24. CREATE TABLE profesor (
  25.   id INT NOT NULL,
  26.   nombre VARCHAR(30),
  27.   idDepartamento INT NOT NULL,
  28.  
  29.   PRIMARY KEY (id),
  30.  
  31.   FOREIGN KEY (idDepartamento)
  32.     REFERENCES departamento(id)
  33.     ON UPDATE CASCADE
  34.     ON DELETE CASCADE
  35.  
  36. ) ENGINE=INNODB;
  37.  
  38. CREATE TABLE curso (
  39.   id INT NOT NULL,
  40.   nombre VARCHAR(30),
  41.   idDepartamento INT NOT NULL,
  42.  
  43.   PRIMARY KEY (id),
  44.  
  45.   FOREIGN KEY (idDepartamento)
  46.     REFERENCES departamento(id)
  47.     ON UPDATE CASCADE
  48.     ON DELETE CASCADE
  49.  
  50. ) ENGINE=INNODB;
  51.  
  52. CREATE TABLE estudiante (
  53.   id INT NOT NULL,
  54.   nombre VARCHAR(30),
  55.   idEscuela INT NOT NULL,
  56.  
  57.   PRIMARY KEY (id),
  58.  
  59.   FOREIGN KEY (idEscuela)
  60.     REFERENCES escuela(id)
  61.     ON UPDATE CASCADE
  62.     ON DELETE CASCADE
  63.  
  64. ) ENGINE=INNODB;
  65.  
  66. CREATE TABLE curso_estudiante (
  67.   idCurso INT NOT NULL,
  68.   idEstudiante INT NOT NULL,
  69.   calificacion INT,
  70.  
  71.   PRIMARY KEY (idCurso, idEstudiante),
  72.  
  73.   FOREIGN KEY (idCurso)
  74.     REFERENCES curso(id)
  75.     ON UPDATE CASCADE
  76.     ON DELETE CASCADE,
  77.  
  78.   FOREIGN KEY (idEstudiante)
  79.     REFERENCES estudiante(id)
  80.     ON UPDATE CASCADE
  81.     ON DELETE CASCADE
  82.  
  83. ) ENGINE=INNODB;
  84.  
  85. CREATE TABLE curso_profesor (
  86.   idCurso INT NOT NULL,
  87.   idProfesor INT NOT NULL,
  88.  
  89.   PRIMARY KEY (idCurso, idProfesor),
  90.  
  91.   FOREIGN KEY (idCurso)
  92.     REFERENCES curso(id)
  93.     ON UPDATE CASCADE
  94.     ON DELETE CASCADE,
  95.  
  96.   FOREIGN KEY (idProfesor)
  97.     REFERENCES profesor(id)
  98.     ON UPDATE CASCADE
  99.     ON DELETE CASCADE
  100.  
  101. ) ENGINE=INNODB;
  102.  
  103. ALTER TABLE departamento
  104.  
  105. ADD CONSTRAINT
  106. FOREIGN KEY (idJefe) REFERENCES profesor(id)
  107. ON UPDATE CASCADE
  108. ON DELETE CASCADE;
Advertisement
Add Comment
Please, Sign In to add comment