-- Creación de Tablas con sus claves primarias
CREATE TABLE carreras(
id_carrera int NOT NULL,
nombre_carrera varchar(50) NOT NULL,
	titulo varchar(50) NULL,
 CONSTRAINT PK_carreras PRIMARY KEY CLUSTERED (id_carrera ASC)
) 
GO

CREATE TABLE sexos(
	id_sexo int NOT NULL,
	n_sexo varchar(20) NOT NULL,
CONSTRAINT PK_sexos PRIMARY KEY CLUSTERED (id_sexo ASC)
) 
GO

CREATE TABLE estados_cursados(
	id_estado int NOT NULL,
	n_estado varchar(30) NOT NULL,
 CONSTRAINT PK_estados_cursados PRIMARY KEY CLUSTERED (id_estado ASC)
) 
GO

CREATE TABLE barrios(
	id_barrio int NOT NULL,
	n_barrio varchar(50) NOT NULL,
 CONSTRAINT PK_barrios PRIMARY KEY CLUSTERED (id_barrio ASC)
) 
GO

CREATE TABLE tipo_notas(
	id_tipo_nota int NOT NULL,
	n_tipo_nota varchar(20) NOT NULL,
 CONSTRAINT PK_tipo_notas PRIMARY KEY CLUSTERED (id_tipo_nota ASC)
) 
GO

CREATE TABLE tipo_documentos(
	id_tipo_documento int NOT NULL,
	n_tipo_documento varchar(20) NOT NULL,
 CONSTRAINT PK_tipo_documentos PRIMARY KEY CLUSTERED (id_tipo_documento ASC)
) 
GO

CREATE TABLE materias(
	id_materia int NOT NULL,
	nombre VARCHAR(50) NOT NULL,
 CONSTRAINT PK_materias PRIMARY KEY CLUSTERED (id_materia ASC)
) 
GO

CREATE TABLE alumnos_cursando_materias(
	id_materia int NOT NULL,
 	 id_carrera int NOT NULL,
	id_estudiante int NOT NULL,
	fecha_inscripcion datetime NOT NULL,
	id_estado int NOT NULL,
	fecha_estado datetime NOT NULL,
 CONSTRAINT PK_alumnos_cursando_materias PRIMARY KEY CLUSTERED (id_materia ASC,	id_carrera ASC, id_estudiante ASC,fecha_inscripcion ASC)
)
GO

CREATE TABLE alumnos_examenes(
	id_materia int NOT NULL,
id_carrera int NOT NULL,    
	id_estudiante int NOT NULL,  
	fecha_del_examen datetime NOT NULL,
	fecha_inscripcion_a_exament datetime NOT NULL,
	nota tinyint NULL,
 CONSTRAINT PK_alumnos_examenes PRIMARY KEY CLUSTERED (id_materia ASC, id_carrera ASC, id_estudiante ASC, fecha_del_examen ASC)
) 
GO

CREATE TABLE alumnos_cursando_notas(
	id_materia int NOT NULL,
	id_carrera int NOT NULL,    
	id_estudiante int NOT NULL,
	fecha_inscripcion datetime NOT NULL,
	fecha_nota datetime NOT NULL,
	id_tipo_nota int NOT NULL,
	nota tinyint NULL,
 CONSTRAINT PK_alumnos_cursando_notas PRIMARY KEY CLUSTERED (id_materia ASC,id_carrera ASC,id_estudiante ASC,fecha_inscripcion ASC,fecha_nota ASC)
) 
GO

CREATE TABLE inscripciones_a_carreras(
	id_estudiante int NOT NULL,
	id_carrera int NOT NULL,
	fecha datetime NOT NULL,
 CONSTRAINT PK_inscripciones_a_carreras PRIMARY KEY CLUSTERED (id_estudiante ASC,id_carrera ASC)
) 
GO

CREATE TABLE plan_de_carrera(
	id_materia int NOT NULL,
	id_carrera int NOT NULL,
	anio_cursado int NULL,
	cuatrimestre_anio int NULL,
 CONSTRAINT PK_plan_de_carrera PRIMARY KEY CLUSTERED (id_materia ASC,id_carrera ASC)
) 
GO

CREATE TABLE estudiantes(
	id_estudiante int NOT NULL,
	apellido varchar(30) NOT NULL,
	nombres varchar(100) NOT NULL,
	id_tipo_documento int NOT NULL,
	documento bigint NOT NULL,
	id_sexo int NULL,
	calle varchar(60) NOT NULL,
	calle_numero varchar(30) NOT NULL,
	id_barrio int NULL,
 CONSTRAINT PK_estudiantes PRIMARY KEY CLUSTERED (id_estudiante ASC)
) 
GO

CREATE TABLE materias_x_turnos_examenes(
	id_turno int NOT NULL,
	id_materia int NOT NULL,
 	id_carrera int NOT NULL,  
	fecha_del_examen datetime NOT NULL,
 CONSTRAINT PK_materias_x_turnos_examenes PRIMARY KEY CLUSTERED (id_materia ASC, id_carrera ASC, fecha_del_examen ASC)
) 
GO
-- Creación de claves foráneas
ALTER TABLE alumnos_cursando_materias  ADD CONSTRAINT FK_alumnos_cursando_materias_estados FOREIGN KEY(id_estado)
REFERENCES estados_cursados (id_estado)
GO
ALTER TABLE alumnos_cursando_materias  ADD CONSTRAINT FK_alumnos_cursando_materias_estudiantes FOREIGN KEY(id_estudiante)
REFERENCES estudiantes (id_estudiante)
GO
ALTER TABLE alumnos_cursando_materias  ADD CONSTRAINT FK_alumnos_cursando_materias_plan_carrera FOREIGN KEY(id_materia, id_carrera)
REFERENCES plan_de_carrera (id_materia, id_carrera)
GO
ALTER TABLE alumnos_examenes  ADD CONSTRAINT FK_alumnos_examenes_estudiantes FOREIGN KEY(id_estudiante)
REFERENCES estudiantes (id_estudiante)
GO
ALTER TABLE alumnos_examenes ADD  CONSTRAINT FK_alumnos_examenes_materias_x_turnos_examenes FOREIGN KEY(id_materia, id_carrera, fecha_del_examen)
REFERENCES materias_x_turnos_examenes (id_materia, id_carrera, fecha_del_examen)
GO
ALTER TABLE alumnos_cursando_notas  ADD CONSTRAINT FK_alumnos_cursando_notas_tipo_notas FOREIGN KEY(id_tipo_nota)
REFERENCES tipo_notas (id_tipo_nota)
GO
ALTER TABLE alumnos_cursando_notas ADD  CONSTRAINT FK_alumnos_cursando_notas_alumnos_cursando_materias FOREIGN KEY(id_materia, id_carrera, id_estudiante, fecha_inscripcion)
REFERENCES alumnos_cursando_materias (id_materia, id_carrera, id_estudiante, fecha_inscripcion)
GO
ALTER TABLE inscripciones_a_carreras  ADD CONSTRAINT FK_inscripciones_a_carreras_carreras FOREIGN KEY(id_carrera)
REFERENCES carreras (id_carrera)
GO
ALTER TABLE inscripciones_a_carreras  ADD CONSTRAINT FK_inscripciones_a_carreras_estudiantes FOREIGN KEY(id_estudiante)
REFERENCES estudiantes (id_estudiante)
GO
ALTER TABLE plan_de_carrera  ADD CONSTRAINT FK_plan_de_carrera_carreras FOREIGN KEY(id_carrera)
REFERENCES carreras (id_carrera)
GO
ALTER TABLE plan_de_carrera  ADD CONSTRAINT FK_plan_de_carrera_materias FOREIGN KEY(id_materia)
REFERENCES materias (id_materia)
GO
ALTER TABLE estudiantes  ADD CONSTRAINT FK_estudiantes_barrios FOREIGN KEY(id_barrio)
REFERENCES barrios (id_barrio)
GO
ALTER TABLE estudiantes  ADD CONSTRAINT FK_estudiantes_sexos FOREIGN KEY(id_sexo)
REFERENCES sexos (id_sexo)
GO
ALTER TABLE estudiantes  ADD CONSTRAINT FK_estudiantes_tipo_documentos FOREIGN KEY(id_tipo_documento)
REFERENCES tipo_documentos (id_tipo_documento)
GO
ALTER TABLE materias_x_turnos_examenes  ADD CONSTRAINT FK_materias_x_turnos_examenes_plan_carrera FOREIGN KEY(id_materia, id_carrera)
REFERENCES plan_de_carrera (id_materia, id_carrera)
GO