/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     06/01/2025 21:43:14                          */
/*==============================================================*/


drop table if exists ALUNOS;

drop table if exists CURSOS;

drop table if exists DISCIPLINAS;

drop table if exists EXAMES;

drop table if exists IMAGENS;

drop table if exists NOTAS;

drop table if exists TIPOS;

drop table if exists UTILIZADORES;

/*==============================================================*/
/* Table: ALUNOS                                                */
/*==============================================================*/
create table ALUNOS
(
   AL_ID                int not null auto_increment,
   UT_ID                int not null,
   AL_NOME              varchar(200) not null,
   AL_DNASCIMENTO       date not null,
   AL_EMAIL             varchar(50) not null,
   AL_ESTADO            bool not null,
   AL_OBS               text not null,
   primary key (AL_ID)
);

/*==============================================================*/
/* Table: CURSOS                                                */
/*==============================================================*/
create table CURSOS
(
   CU_ID                int not null auto_increment,
   AL_ID                int not null,
   CU_NOME              varchar(250) not null,
   CU_OBS               text not null,
   primary key (CU_ID)
);

/*==============================================================*/
/* Table: DISCIPLINAS                                           */
/*==============================================================*/
create table DISCIPLINAS
(
   DI_ID                int not null auto_increment,
   CU_ID                int not null,
   DI_NOME              varchar(250) not null,
   DI_OBS               text not null,
   primary key (DI_ID)
);

/*==============================================================*/
/* Table: EXAMES                                                */
/*==============================================================*/
create table EXAMES
(
   EX_ID                int not null auto_increment,
   DI_ID                int not null,
   ES_NOME              varchar(250) not null,
   EX_DATA              date not null,
   EX_OBS               text not null,
   primary key (EX_ID)
);

/*==============================================================*/
/* Table: IMAGENS                                               */
/*==============================================================*/
create table IMAGENS
(
   IM_ID                int not null auto_increment,
   EX_ID                int not null,
   IM_NOME              varchar(250) not null,
   IM_LOCAL             varchar(250) not null,
   IM_OBS               text not null,
   primary key (IM_ID)
);

/*==============================================================*/
/* Table: NOTAS                                                 */
/*==============================================================*/
create table NOTAS
(
   NO_ID                int not null auto_increment,
   EX_ID                int not null,
   AL_ID                int not null,
   NO_VALOR             decimal(10,2) not null,
   NO_OBS               text not null,
   primary key (NO_ID)
);

/*==============================================================*/
/* Table: TIPOS                                                 */
/*==============================================================*/
create table TIPOS
(
   TP_ID                int not null auto_increment,
   TP_NOME              varchar(250) not null,
   TP_OBS               text not null,
   primary key (TP_ID)
);

/*==============================================================*/
/* Table: UTILIZADORES                                          */
/*==============================================================*/
create table UTILIZADORES
(
   UT_ID                int not null auto_increment,
   TP_ID                int not null,
   UT_ADMIN             bool not null,
   UT_NOME              varchar(200) not null,
   UT_EMAIL             varchar(50) not null,
   UT_PASSWORD          varchar(100) not null,
   UT_ESTADO            bool not null,
   UT_OBS               text not null,
   primary key (UT_ID)
);

alter table ALUNOS add constraint FK_UTI_ALU foreign key (UT_ID)
      references UTILIZADORES (UT_ID) on delete restrict on update restrict;

alter table CURSOS add constraint FK_ALU_CUR foreign key (AL_ID)
      references ALUNOS (AL_ID) on delete restrict on update restrict;

alter table DISCIPLINAS add constraint FK_CUR_DIS foreign key (CU_ID)
      references CURSOS (CU_ID) on delete restrict on update restrict;

alter table EXAMES add constraint FK_DIS_EXA foreign key (DI_ID)
      references DISCIPLINAS (DI_ID) on delete restrict on update restrict;

alter table IMAGENS add constraint FK_EXA_IMA foreign key (EX_ID)
      references EXAMES (EX_ID) on delete restrict on update restrict;

alter table NOTAS add constraint FK_ALU_NOT foreign key (AL_ID)
      references ALUNOS (AL_ID) on delete restrict on update restrict;

alter table NOTAS add constraint FK_EXA_NOT foreign key (EX_ID)
      references EXAMES (EX_ID) on delete restrict on update restrict;

alter table UTILIZADORES add constraint FK_TIP_UTI foreign key (TP_ID)
      references TIPOS (TP_ID) on delete restrict on update restrict;

