SQL Server Database Test

create database planificacion
go

use planificacion
go

create table universidad
(
idUniversidad int identity(1,1) primary key not null,
nombre varchar(50) not null,
lema varchar(100) not null,
fechaFundacion date not null,
autorizacionCNU varchar(50) not null,
mision varchar(250) not null,
vision varchar(250) not null
)
go

create table deptoNic
(
idUbicacion int identity(1,1) primary key not null,
ubicacion varchar(45) not null
)
go

-- drop table recintos

create table recintos
(
idRecinto int identity(1,1) primary key not null,
recinto varchar(45) not null,
nomb_abreviado varchar(45) not null,
idUbicacion int not null,
direccion varchar(45) not null,
idUniversidad int not null,
constraint fk_ubicacion_recintos foreign key(idUbicacion)
references deptoNic(idUbicacion)
on delete no action on update no action,
constraint fk_universidad_recintos foreign key(idUniversidad)
references universidad(idUniversidad)
on delete no action on update no action
)
go

-- drop table edificios

create table edificios
(
idEdificio int identity(1,1) primary key not null,
edificio varchar(45) not null,
idRecinto int not null,
constraint fk_recinto_edificios foreign key(idRecinto)
references recintos(idRecinto)
on delete no action on update no action
)
go

create table facultades
(
idFacultad int identity(1,1) primary key not null,
facultad varchar(50) not null,
descripcion varchar(50),
idRecinto int not null,
constraint fk_recinto_facultades foreign key(idRecinto)
references recintos(idRecinto)
on delete no action on update no action
)
go

create table aulas
(
idAula int identity(1,1) primary key not null,
aula varchar(45) not null,
idEdificio int not null,
constraint fk_edificio_aulas foreign key(idEdificio)
references edificios(idEdificio)
on delete no action on update no action
)
go

create table carreras
(
idCarrera int identity(1,1) primary key not null,
carrera varchar(50) not null,
descripcion varchar(50),
perfil varchar(100) not null,
duracion varchar(50) not null,
idFacultad int not null,
constraint fk_facultad_carreras foreign key(idFacultad)
references facultades(idFacultad)
on delete no action on update no action
)
go

create table grupos
(
idGrupo int identity(1,1) primary key not null,
annoLectivo int not null,
idCarrera int not null,
annoAcademico varchar(50) not null,
semestre varchar(2) not null,
cantAlumnos int not null,
grupo varchar(50) not null,
constraint fk_carrera_grupos foreign key(idCarrera)
references carreras(idCarrera)
on delete no action on update no action
)
go

create table laboratorios
(
idLab int identity(1,1) primary key not null,
nomLab varchar(45) not null,
puestosTrab int not null,
idCarrera int not null,
constraint fk_carrera_laboratorios foreign key(idCarrera)
references carreras(idCarrera)
on delete no action on update no action
)
go

create table deptoAcad
(
idDepto int identity(1,1) primary key not null,
departamento varchar(45) not null,
descripcion varchar(45),
idCarrera int not null,
constraint fk_carrera_deptoAcad foreign key(idCarrera)
references carreras(idCarrera)
on delete no action on update no action
)
go

create table asignaturas
(
idAsignatura int identity(1,1) primary key not null,
asignatura varchar(50) not null,
horasPlan int not null,
horasPlanificacion int not null,
frecuencia int not null,
horasLab int not null
)
go

create table flujograma
(
idCarrera int not null,
anno varchar(3) not null,
semestre varchar(2) not null,
idAsignatura int not null,
constraint fk_carrera_flujograma foreign key(idCarrera)
references carreras(idCarrera)
on delete no action on update no action,
constraint fk_asignatura_flujograma foreign key(idAsignatura)
references asignaturas(idAsignatura)
on delete no action on update no action
)
go

create table categoria
(
idCategoria int identity(1,1) primary key not null,
categoria varchar(45) not null,
salarioBase int not null
)
go

create table tipoContrato
(
idTipo int identity(1,1) primary key not null,
tipoContrato varchar(45) not null,
cantidadHoras int not null
)
go

-- drop table docentes

create table docentes
(
idDocente int identity(1,1) primary key not null,
nombres varchar(45) not null,
apellidos varchar(45) not null,
fechaNac date not null,
sexo varchar(1) not null,
direccion varchar(45) not null,
telefono varchar(45) not null,
celular varchar(45) not null,
email varchar(45) not null,
idDepto int not null,
idCategoria int not null,
idTipoContrato int not null,
docentescol varchar(45) not null,
constraint fk_dpto_academico_docentes foreign key(idDepto)
references deptoAcad(idDepto)
on delete no action on update no action,
constraint fk_categoria_docentes foreign key(idCategoria)
references categoria(idCategoria)
on delete no action on update no action,
constraint fk_tipo_docentes foreign key(idTipoContrato)
references tipoContrato(idTipo)
on delete no action on update no action
)
go

create table turnos
(
idTurno int identity(1,1) primary key not null,
turno varchar(45) not null,
descripcion varchar(45)
)
go

-- drop table planificacion

create table planificacion
(
idPlan int identity(1,1) primary key not null,
anoLectivo int not null,
semestre varchar(5) not null,
idTurno int not null,
idGrupo int not null,
idAula int not null,
idAsignatura int not null,
idDia int not null,
idHorario int not null,
idDocente int not null,
idLab int not null,
observaciones varchar(45),
constraint fk_turno_planificacion foreign key(idTurno)
references turnos(idTurno)
on delete no action on update no action,
constraint fk_grupo_planificacion foreign key(idGrupo)
references grupos(idGrupo)
on delete no action on update no action,
constraint fk_aula_planificacion foreign key(idAula)
references aulas(idAula)
on delete no action on update no action,
constraint fk_asignatura_planificacion foreign key(idAsignatura)
references asignaturas(idAsignatura)
on delete no action on update no action,
constraint fk_docente_planificacion foreign key(idDocente)
references docentes(idDocente)
on delete no action on update no action,
constraint fk_laboratorio_planificacion foreign key(idLab)
references laboratorios(idLab)
on delete no action on update no action
)
go