CREATE database cadee;

use cadee;

CREATE TABLE admin (
    status boolean,
    id int primary key auto_increment,
    name varchar(100),
    username varchar(100),
    pass varchar(100)
);

CREATE TABLE campus(
    status boolean,
    id int primary key auto_increment,
    name varchar(100),
    id_admin int,
    foreign key (id_admin) references admin(id)
);

CREATE TABLE person (
    status boolean,
    name varchar (100),
    id int primary key auto_increment,
    username varchar(100),
    pass varchar(100),
    id_campus int,
    foreign key (id_campus) references campus(id)
);

CREATE TABLE teacher (
    status boolean,
    id_person int,
    siape varchar(100) primary key,
    carge varchar(100),
    foreign key (id_person) references person(id)
);

CREATE TABLE student (
    status boolean,
    id_person int,
    enrollment varchar(200) primary key,
    class_name varchar(30),
    foreign key (id_person) references person(id)
);

CREATE TABLE tec (
    status boolean,
    id_person int,
    id int primary key auto_increment,
    foreign key (id_person) references person(id)
);

CREATE TABLE place (
    status boolean,
    siape_co varchar(100),
    name varchar(300),
    id_campus int,
    id int primary key auto_increment,
    foreign key (siape_co) references teacher(siape),
    foreign key (id_campus) references campus(id)
);

CREATE TABLE material (
    status boolean,
    id int primary key auto_increment,
    description varchar(300),
    quantity int,
    id_place int,
    foreign key (id_place) references place(id)
);

CREATE TABLE permanent_material (
    status boolean,
    model varchar(100),
    state varchar(1000),
    mark varchar(100),
    patrimony varchar(100) primary key,
    id_material int,
    foreign key (id_material) references material(id)
);

CREATE TABLE consumable_material (
    status boolean,
    id_material int,
    id int primary key auto_increment,
    stock_min int,
    foreign key (id_material) references material(id)
);