Learning Y PROJECT

Preparacion

primero instalar el manejador de paquetes COMPOSER y NODE

Acceder a la carpeta raiz del proyecto desde la consola de comandos y ejecutar los siguientes comandos

composer install

npm install

DB

• primero creamos la base de datos

    create database eyProyect;

• credentialstypes

create table credentialstypes(
    `id` int(11) NOT NULL,
    `description` varchar(50) NOT NULL,

    PRIMARY KEY(id)
);

INSERT INTO `credentialstypes` (`id`, `description`) VALUES
(1, 'Administrador'),
(2, 'Soporte'),
(3, 'Profesor'),
(4, 'Alumno especial'),
(5, 'alumno');

• usuarios

CREATE TABLE `usuarios` (
    `id` int(11) NOT NULL,
    `name` varchar(60) NOT NULL,
    `user` varchar(40) NOT NULL,
    `mail` varchar(50) NOT NULL,
    `password` varchar(100) NOT NULL,
    `birthday` date NOT NULL,
    `tyc` varchar(10) NOT NULL,

    PRIMARY KEY (id)
);

• cursos

CREATE TABLE `cursos` (
    `id` int(11) NOT NULL,
    `profesor` int(11) NOT NULL,
    `nom_curso` varchar(40) NOT NULL,
    `descripcion` text DEFAULT NULL,
    `contenidos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '{["0": "sin contenidos"]}' CHECK (json_valid(`contenidos`)) ,

    PRIMARY KEY(id),

    CONSTRAINT user_curso
    FOREIGN KEY (profesor)
    REFERENCES usuarios(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• creador_curso

create table creador_curso( 
    id_user int(11) not null, 
    id_curso int(11) not null, 
    
    PRIMARY KEY(id_user, id_curso), 

    CONSTRAINT user_creador 
    FOREIGN KEY (id_user) 
    REFERENCES usuarios(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
    
    CONSTRAINT curso_creador 
    FOREIGN KEY (id_curso) 
    REFERENCES cursos(id) 
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• credentials

create table credentials(
    id int AUTO_INCREMENT,
    id_user int(11) not null,
    credential_type int(11) not null DEFAULT 5,
    
    PRIMARY KEY (`id`, `id_user`),
    
    CONSTRAINT user_credential
    FOREIGN KEY (`id_user`) 
    REFERENCES usuarios (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

    CONSTRAINT type_credential
    FOREIGN KEY (`credential_type`) 
    REFERENCES credentialstypes (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• peticiones

create table peticiones( 
    id int AUTO_INCREMENT,
    id_user int(11) not null, 
    type int(11) not null, 

    PRIMARY KEY(id), 

    CONSTRAINT user_petition 
    FOREIGN KEY (id_user) 
    REFERENCES usuarios(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
    
    CONSTRAINT user_credenType 
    FOREIGN KEY (type) 
    REFERENCES credentialstypes(id) 
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• usu_cur

CREATE TABLE `usu_cur` (
    `id` int(11) NOT NULL,
    `id_user` int(11) NOT NULL,
    `id_curso` int(11) NOT NULL,
    `estado` int(2) NOT NULL DEFAULT 0,

    PRIMARY KEY (id),

    CONSTRAINT user_uscur
    FOREIGN KEY (id_user)
    REFERENCES usuarios(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

    CONSTRAINT curso_uscur
    FOREIGN KEY (id_curso)
    REFERENCES cursos(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

);