DekkerBass

populate.sql entr-3

Dec 26th, 2020 (edited)
1,045
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE IF NOT EXISTS Entregable3;
  2. USE Entregable3;
  3.  
  4.  
  5. -- Grados
  6.  
  7. CREATE OR REPLACE TABLE degrees (
  8. degreeId        INT                     NOT NULL AUTO_INCREMENT,
  9. `name`          VARCHAR (60)        NOT NULL UNIQUE,
  10. years           INT DEFAULT (4) NOT NULL,
  11. PRIMARY KEY (degreeId),
  12. CONSTRAINT invalidDegreeYear CHECK (years BETWEEN 3 AND 5)
  13. );
  14.  
  15.  
  16. --DEPARTAMENTOS
  17.  
  18.  
  19. CREATE OR REPLACE TABLE Departments (
  20. departmentId        INT                 NOT NULL AUTO_INCREMENT,
  21. `name`              VARCHAR(100)    NOT NULL,
  22. PRIMARY KEY (departmentId),
  23. UNIQUE (`name`)
  24. );
  25.  
  26.  
  27. --ESPACIOS
  28.  
  29. CREATE OR REPLACE TABLE  Spaces (
  30. spaceId     INT                 NOT NULL AUTO_INCREMENT,
  31. `name`      VARCHAR (100)   NOT NULL UNIQUE,
  32. `floor`     INT                 NOT NULL,
  33. capacity        INT                 NOT NULL,
  34. PRIMARY KEY(spaceId),
  35. CONSTRAINT invalidcapacity CHECK (capacity>0)
  36. );
  37.  
  38. #DESPACHOS
  39.  
  40. CREATE OR REPLACE TABLE Offices(
  41. officeId        INT                 NOT NULL AUTO_INCREMENT,
  42. `shared`        BOOLEAN             NOT NULL ,
  43. `freespace` BOOLEAN             NOT NULL,
  44. spaceId     INT,
  45. PRIMARY KEY (officeId),
  46. FOREIGN KEY (spaceId) REFERENCES spaces (spaceId) ON DELETE CASCADE ON UPDATE CASCADE
  47. );
  48.  
  49. --AULAS
  50.  
  51. CREATE OR REPLACE TABLE classroom (
  52. classroomId         INT                 NOT NULL AUTO_INCREMENT,
  53. loudspeakers        BOOLEAN         NOT NULL,
  54. projector           BOOLEAN         NOT NULL,
  55. spaceId             INT,
  56. PRIMARY KEY(classroomId),
  57. FOREIGN KEY(spaceId) REFERENCES spaces (spaceId) ON DELETE CASCADE ON UPDATE CASCADE
  58. );
  59.  
  60.  
  61.  
  62. --PROFESORES
  63.  
  64.  
  65. CREATE OR REPLACE TABLE Teachers (
  66. teacherId       INT                 NOT NULL AUTO_INCREMENT,
  67. dni             CHAR(9)             NOT NULL UNIQUE ,
  68. firstName       VARCHAR(100)    NOT NULL,
  69. surname         VARCHAR(100)    NOT NULL,
  70. birthDate       DATE                NOT NULL,
  71. email               VARCHAR(258)    NOT NULL UNIQUE ,
  72. category            ENUM('Profesor', 'Titular de Universidad', 'Profesor Contratado Doctor', 'Profesor Ayudante Doctor'),
  73. officeId            INT,
  74. departmentId    INT,
  75. PRIMARY KEY(teacherId),
  76. FOREIGN KEY (officeId) REFERENCES offices(officeId) ON DELETE SET NULL,
  77. FOREIGN KEY (departmentId) REFERENCES offices(officeId) ON DELETE SET NULL
  78. );
  79.  
  80. --Tabla Grupos-Profesores
  81.  
  82. CREATE OR REPLACE TABLE TeachersGroups(
  83. teachergroupId      INT NOT NULL AUTO_INCREMENT,
  84. teachingLoad        INT NOT NULL,
  85. teacherId           INT NOT NULL,
  86. groupId             INT NOT NULL,
  87. PRIMARY KEY         (teacherGroupId),
  88. FOREIGN KEY (teacherId) REFERENCES teachers (teacherId),
  89. FOREIGN KEY (groupId) REFERENCES Groups (groupId)
  90. );
  91. --Alumnos
  92.  
  93.  
  94. CREATE OR REPLACE TABLE Students(
  95. studentId       INT             NOT NULL AUTO_INCREMENT,
  96. accesMethod     ENUM            ('Selectividad', 'Ciclo', 'Mayor', 'Titulado Extranjero'),
  97. dni             CHAR(9)      NOT NULL UNIQUE,
  98. firstName       VARCHAR(100) NOT NULL,
  99. surname         VARCHAR(100) NOT NULL,
  100. birthDate       DATE             NOT NULL,
  101. email               VARCHAR(250) NOT NULL UNIQUE ,
  102. PRIMARY KEY(studentId)
  103. );
  104.  
  105. --ASIGNATURAS
  106.  
  107.  
  108.  
  109. CREATE OR REPLACE TABLE Subjects (
  110. subjectId       INT                 NOT NULL AUTO_INCREMENT,
  111. `name`          VARCHAR(100)    NOT NULL UNIQUE,
  112. acronym             VARCHAR(8)      NOT NULL UNIQUE,
  113. credits             INT             NOT NULL,
  114. `year`          INT                 NOT NULL,
  115. `type`          ENUM                ('Formacion Basica','Optativa','Obligatoria'),
  116. degreeId        INT                 NOT NULL,
  117. PRIMARY KEY     (subjectId),
  118. UNIQUE  (degreeId,`name`),
  119. UNIQUE  (degreeId,`acronym`),
  120. FOREIGN KEY (degreeId)  REFERENCES Degrees (degreeId) ON DELETE CASCADE ,
  121. CONSTRAINT negativeSubjectCredits CHECK (credits > 0),
  122. CONSTRAINT invalidSubjectCourse CHECK (year >= 1 AND year <= 5)
  123. );
  124.  
  125.  
  126.  
  127. --GRUPOS
  128.  
  129. CREATE OR REPLACE TABLE Groups(
  130. groupId         INT             NOT NULL AUTO_INCREMENT,
  131. `name`          VARCHAR(30) NOT NULL,
  132. activity        ENUM            ('Teoria','Laboratorio'),
  133. `year`          INT             NOT NULL,
  134. subjectId       INT             NOT NULL,
  135. PRIMARY KEY (groupId),
  136. FOREIGN KEY (subjectId) REFERENCES Subjects (subjectId),
  137. UNIQUE (`name`, `year`, subjectId),
  138. CONSTRAINT negativeGroupYear CHECK (year > 0)
  139. );
  140.  
  141. --Grupos de Estudiantes
  142.  
  143.  
  144. CREATE OR REPLACE TABLE GroupsStudents(
  145. groupStudentId      INT         NOT NULL AUTO_INCREMENT,
  146. groupId                     INT         NOT NULL,
  147. studentId               INT         NOT NULL,
  148. PRIMARY KEY (groupStudentId),
  149. FOREIGN KEY (groupId) REFERENCES Groups  (groupId),
  150. FOREIGN KEY (studentId) REFERENCES Students (studentId),
  151. UNIQUE (groupId, studentId)
  152. );
  153.  
  154.  
  155. -- CALIFICACIONES
  156. CREATE OR REPLACE TABLE Qualification(
  157. qualificationId         INT                 NOT NULL AUTO_INCREMENT,
  158. `value`                     DECIMAL(4,2)    NOT NULL,
  159. gradeCall               INT                 NOT NULL,
  160. honours                     BOOLEAN             NOT NULL,
  161. studentId               INT                 NOT NULL,
  162. groupId                     INT                 NOT NULL,
  163. PRIMARY KEY (qualificationId),
  164. FOREIGN KEY (studentId) REFERENCES Students (studentId),
  165. FOREIGN KEY (groupId) REFERENCES Groups (groupId),
  166. CONSTRAINT invalidGradeValue CHECK (value >= 0 AND value <= 10),
  167. CONSTRAINT invalidGradeCall CHECK (gradeCall >= 1 AND gradeCall <= 3),
  168. CONSTRAINT duplicatedCallGrade UNIQUE (gradeCall, studentId, groupId)
  169. );
  170.  
  171.  
  172. -- TUTORIAS
  173.  
  174. CREATE  OR REPLACE TABLE Tutorials (
  175. tutorialId      INT                 NOT NULL AUTO_INCREMENT,
  176. `day`               ENUM ('Lunes', 'Martes', 'Miercoles', 'Jueves', 'Viernes'),
  177. start_hour      TIME                    NOT NULL,
  178. end_hour            TIME                    NOT NULL,
  179. teacherId       INT,
  180. PRIMARY KEY (tutorialId),
  181. FOREIGN KEY(teacherId) REFERENCES Teachers  (teacherId) ON DELETE SET NULL
  182. );
  183.  
  184.  
  185.  
  186. --CITAS DE TUTORIAS
  187. CREATE OR REPLACE TABLE Appointment (
  188. appointmentId       INT                 NOT NULL AUTO_INCREMENT,
  189. dateAppointment DATE                NOT NULL,
  190. hourAppointment     TIME                NOT NULL,
  191. tutorialId          INT                 NOT NULL,
  192. studentId           INT                 NOT NULL,
  193. PRIMARY KEY(appointmentId),
  194. FOREIGN KEY(tutorialId) REFERENCES Tutorials     (tutorialId) ON DELETE CASCADE ,
  195. FOREIGN KEY(studentId) REFERENCES students (studentId) ON DELETE CASCADE,
  196. UNIQUE (dateAppointment, hourAppointment)
  197. );
RAW Paste Data