colegio/doc/Informe/e2/crear_tablas.sql

CREATE TABLE region(
  id_region SMALLSERIAL UNIQUE,
  nombre_region VARCHAR(20) NOT NULL,
  PRIMARY KEY (id_region)
);

CREATE TABLE ciudad(
  id_ciudad SMALLSERIAL UNIQUE,
  nombre_ciudad VARCHAR(20) NOT NULL,
  id_region SMALLINT,
  PRIMARY KEY (id_ciudad),
  FOREIGN KEY (id_region) REFERENCES region (id_region) ON DELETE RESTRICT
);

CREATE TABLE apoderado(
  rut_apoderado VARCHAR(9) NOT NULL UNIQUE CHECK (rut_apoderado ~ '^[0-9k]+$'),
  nombres VARCHAR(50) NOT NULL,
  apellidos VARCHAR(50) NOT NULL,
  direccion VARCHAR(30) NOT NULL,
  id_ciudad SMALLINT,
  PRIMARY KEY (rut_apoderado),
  FOREIGN KEY (id_ciudad) REFERENCES ciudad (id_ciudad) ON DELETE RESTRICT
);

CREATE TABLE alumno(
  rut_alumno VARCHAR(9) NOT NULL UNIQUE CHECK (rut_alumno ~ '^[0-9k]+$'),
  nombres VARCHAR(50) NOT NULL,
  apellidos VARCHAR(50) NOT NULL,
  direccion VARCHAR(30) NOT NULL,
  id_ciudad SMALLINT,
  anyo_ingreso SMALLINT CHECK (anyo_ingreso > 1980),
  fecha_nacimiento DATE NOT NULL CHECK (fecha_nacimiento > '1980-01-01'),
  rut_apoderado VARCHAR(9) NOT NULL CHECK (rut_apoderado ~ '^[0-9k]+$'),
  PRIMARY KEY (rut_alumno),
  FOREIGN KEY (id_ciudad) REFERENCES ciudad (id_ciudad) ON DELETE RESTRICT,
  FOREIGN KEY (rut_apoderado) REFERENCES apoderado (rut_apoderado) ON DELETE RESTRICT
);

CREATE TABLE profesor(
  rut_profesor VARCHAR(9) NOT NULL UNIQUE CHECK (rut_profesor ~ '^[0-9k]+$'),
  nombres VARCHAR(50) NOT NULL,
  apellidos VARCHAR(50) NOT NULL,
  direccion VARCHAR(30) NOT NULL,
  id_ciudad SMALLINT,
  PRIMARY KEY (rut_profesor),
  FOREIGN KEY (id_ciudad) REFERENCES ciudad (id_ciudad) ON DELETE RESTRICT
);

CREATE TABLE actividad(
  id_actividad SERIAL UNIQUE,
  nombre VARCHAR(50) NOT NULL,
  cupos SMALLINT CHECK (cupos > 0),
  lugar VARCHAR(20) NOT NULL,
  rut_profesor VARCHAR(9) NOT NULL CHECK (rut_profesor ~ '^[0-9k]+$'),
  PRIMARY KEY (id_actividad),
  FOREIGN KEY (rut_profesor) REFERENCES profesor (rut_profesor) ON DELETE RESTRICT
);

CREATE TABLE nivel(
  id_nivel SERIAL UNIQUE,
  nombre VARCHAR(20) NOT NULL,
  PRIMARY KEY (id_nivel)
);

CREATE TABLE bloque(
  id_bloque SERIAL UNIQUE,
  descripcion VARCHAR(20) NOT NULL,
  PRIMARY KEY (id_bloque)
);

CREATE TABLE curso(
  id_curso VARCHAR(10) NOT NULL UNIQUE,
  nombre VARCHAR(6) NOT NULL,
  anyo SMALLINT CHECK (anyo > 1980),
  rut_profesor VARCHAR(9) NOT NULL CHECK (rut_profesor ~ '^[0-9k]+$'),
  PRIMARY KEY (id_curso),
  FOREIGN KEY (rut_profesor) REFERENCES profesor (rut_profesor) ON DELETE RESTRICT
);

CREATE TABLE asignatura(
  id_asignatura SERIAL UNIQUE,
  nombre VARCHAR(50) NOT NULL,
  rut_profesor VARCHAR(9) NOT NULL CHECK (rut_profesor ~ '^[0-9k]+$'),
  PRIMARY KEY (id_asignatura),
  FOREIGN KEY (rut_profesor) REFERENCES profesor (rut_profesor) ON DELETE RESTRICT
);

CREATE TABLE participar(
  rut_alumno VARCHAR(9) NOT NULL CHECK (rut_alumno ~ '^[0-9k]+$'),
  id_actividad INT,
  PRIMARY KEY (rut_alumno, id_actividad),
  FOREIGN KEY (rut_alumno) REFERENCES alumno (rut_alumno) ON DELETE RESTRICT,
  FOREIGN KEY (id_actividad) REFERENCES actividad (id_actividad) ON DELETE RESTRICT
);

CREATE TABLE actividad_nivel(
  id_actividad INT,
  id_nivel INT,
  PRIMARY KEY (id_actividad, id_nivel),
  FOREIGN KEY (id_actividad) REFERENCES actividad (id_actividad) ON DELETE RESTRICT,
  FOREIGN KEY (id_nivel) REFERENCES nivel (id_nivel) ON DELETE RESTRICT
);

CREATE TABLE actividad_bloque(
  id_actividad INT,
  id_bloque INT,
  PRIMARY KEY (id_actividad, id_bloque),
  FOREIGN KEY (id_actividad) REFERENCES actividad (id_actividad) ON DELETE RESTRICT,
  FOREIGN KEY (id_bloque) REFERENCES bloque (id_bloque) ON DELETE RESTRICT
);

CREATE TABLE horario(
  id_asignatura INT,
  id_bloque INT,
  sala VARCHAR(20) NOT NULL,
  PRIMARY KEY (id_asignatura, id_bloque),
  FOREIGN KEY (id_asignatura) REFERENCES asignatura (id_asignatura) ON DELETE RESTRICT,
  FOREIGN KEY (id_bloque) REFERENCES bloque (id_bloque) ON DELETE RESTRICT
);

CREATE TABLE registro(
  rut_alumno VARCHAR(9) NOT NULL CHECK (rut_alumno ~ '^[0-9k]+$'),
  id_asignatura INT,
  nota NUMERIC(2,1) CHECK (nota >= 1.0 AND nota <= 7.0),
  PRIMARY KEY (rut_alumno, id_asignatura),
  FOREIGN KEY (rut_alumno) REFERENCES alumno (rut_alumno) ON DELETE RESTRICT,
  FOREIGN KEY (id_asignatura) REFERENCES asignatura (id_asignatura) ON DELETE RESTRICT
);

CREATE TABLE cursar(
  rut_alumno VARCHAR(9) NOT NULL CHECK (rut_alumno ~ '^[0-9k]+$'),
  id_curso VARCHAR(10) NOT NULL,
  PRIMARY KEY (rut_alumno, id_curso),
  FOREIGN KEY (rut_alumno) REFERENCES alumno (rut_alumno) ON DELETE RESTRICT,
  FOREIGN KEY (id_curso) REFERENCES curso (id_curso) ON DELETE RESTRICT
);

CREATE TABLE asociado(
  id_curso VARCHAR(10) NOT NULL,
  id_asignatura INT,
  PRIMARY KEY (id_curso, id_asignatura),
  FOREIGN KEY (id_curso) REFERENCES curso (id_curso) ON DELETE RESTRICT,
  FOREIGN KEY (id_asignatura) REFERENCES asignatura (id_asignatura) ON DELETE RESTRICT
);

CREATE TABLE asistente(
  rut_profesor VARCHAR(9) NOT NULL CHECK (rut_profesor ~ '^[0-9k]+$'),
  id_curso VARCHAR(10) NOT NULL,
  PRIMARY KEY (rut_profesor, id_curso),
  FOREIGN KEY (rut_profesor) REFERENCES profesor (rut_profesor) ON DELETE RESTRICT,
  FOREIGN KEY (id_curso) REFERENCES curso (id_curso) ON DELETE RESTRICT
);