database.sql

-- Criação do banco de dados. --
drop database AulasTech2;
create database AulasTech2;
use AulasTech2;

-- Criação das tabelas. --
create table tb_schools (
	id_school tinyint unsigned not null auto_increment primary key,
	name varchar(256) not null unique
);

create table tb_user (
	user varchar(256) not null primary key,
	name varchar(256) not null
);

create table tb_user_school (
	id smallint unsigned not null auto_increment primary key,
	user varchar(256) not null,
	id_school tinyint unsigned not null,
	foreign key (user) references tb_user(user),
	foreign key (id_school) references tb_schools(id_school)
);

create table tb_materials (
	id_material smallint unsigned not null auto_increment primary key,
	name varchar(256) not null,
	form_link varchar(256) default null,
	assignment_link varchar(256) default null,
	added_in date default current_date
);

create table tb_school_material (
	id smallint unsigned not null auto_increment primary key,
	id_school tinyint unsigned not null,
	id_material smallint unsigned not null,
	foreign key (id_material) references tb_materials(id_material),
	foreign key (id_school) references tb_schools(id_school)
);

create table tb_link_material (
	id smallint unsigned not null auto_increment primary key,
	id_material smallint unsigned not null,
	title varchar(256),
	url varchar(512) unique not null,
	foreign key (id_material) references tb_materials(id_material)
);


insert into tb_user values ('ANDRE_SANTOS', 'André Francisco Dos Santos');