-- ANGEL GUIBERTEAU FRANCO

/*BBDD*/

CREATE TABLE alumno(
    idAlumno tinyint unsigned AUTO_INCREMENT,
    nombre varchar (100) NOT NULL,
    web varchar (200) NOT NULL,
    correo varchar UNIQUE (100) NOT NULL,
    CONSTRAINT pk_alumno PRIMARY KEY (idAlumno)
);

CREATE TABLE valoracion(
    numValoracion smallint unsigned AUTO_INCREMENT,
    categoria char(12) NOT NULL,
    momento varchar(50) NOT NULL,
    descripcion varchar(200) NOT NULL,
    idEmisor tinyint unsigned NOT NULL,
    idReceptor tinyint unsigned NOT NULL,
    CONSTRAINT pk_valoracion PRIMARY KEY (idEmisor, idReceptor),
    CONSTRAINT fk_receptor FOREIGN KEY (idReceptor) REFERENCES alumno(idAlumno),
    CONSTRAINT fk_emisor FOREIGN KEY (idEmisor) REFERENCES alumno(idAlumno)
);

CREATE UNIQUE INDEX idx_nombre ON alumno(nombre);
CREATE UNIQUE INDEX idx_web ON alumno(web);
CREATE UNIQUE INDEX idx_correo ON alumno(correo);