There's "instituciones", each with 0...* 'alumno', each alumno has 0 or 1 'antecedente', and each 'antecedente has 0...* 'antecedente_delito'
instituciones (centroeducacional) has 'id_institucion' as primary key.
alumno has id_instalumno as foreign key related to id_institucion
antecedente has rut_antecedente as a reference to alumno's primary key
antecedente_delito has id_doc2 as foreign key to antecedente and 2 other foreigns you can ignore for now.
code
Code:
create table centroeducacional(
id_institucion int not null auto_increment,
nombre_institucion varchar(30) not null,
primary key (id_institucion))ENGINE=InnoDB;
create table alumno(
rut_alumno varchar(10) not null,
nombre_alumno varchar(30) not null,
edad int not null,
id_instalumno int not null,
primary key (rut_alumno),
foreign key (id_instalumno) references centroeducacional(id_institucion)
on delete cascade
on update cascade
)ENGINE=InnoDB;
create table antecedente(
rut_antecedente varchar(10) not null,
id_documento int not null auto_increment,
sentencia varchar(30),
primary key (id_documento, rut_antecedente),
foreign key (rut_antecedente) references alumno (rut_alumno)
on delete cascade
on update cascade)ENGINE=InnoDB;
create table antecedente_delito(
id_delito2 int not null,
id_doc2 int not null,
fecha date not null,
rut_poli2 varchar(10) not null,
primary key(id_doc2,id_delito2,fecha),
foreign key (id_delito2) references delito(id_delito)
on delete cascade
on update cascade,
foreign key (id_doc2) references antecedente(id_documento)
on delete cascade
on update cascade,
foreign key (rut_poli2) references polichile(rut_polichile)
on delete cascade
on update cascade)ENGINE=InnoDB;
I need to create a view with the top 5 'nombre_institucion' from the first table with most 'delitos' (entries in antecedente_delito).
Basically the result I need in the view is:
