tkl

USE [UNIVDB]
GO
/****** Object:  Table [dbo].[Aulas]    Script Date: 2/29/2020 10:00:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Aulas](
	[AulaId] [int] IDENTITY(1,1) NOT NULL,
	[Edificio] [varchar](10) NOT NULL,
	[Piso] [varchar](5) NOT NULL,
	[Numero] [varchar](3) NOT NULL,
 CONSTRAINT [PK_Aulas] PRIMARY KEY CLUSTERED
(
	[AulaId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Carreras]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Carreras](
	[CarreraId] [int] IDENTITY(1,1) NOT NULL,
	[DecanatoId] [int] NOT NULL,
	[Nombre] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Carreras] PRIMARY KEY CLUSTERED
(
	[CarreraId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Decanatos]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Decanatos](
	[DecanadoId] [int] IDENTITY(1,1) NOT NULL,
	[Nombre] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Decanatos] PRIMARY KEY CLUSTERED
(
	[DecanadoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DisponibilidadProfesores]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DisponibilidadProfesores](
	[DisponibilidadProfesorId] [int] IDENTITY(1,1) NOT NULL,
	[ProfesorId] [int] NOT NULL,
	[MateriaId] [int] NOT NULL,
	[HorarioClaseId] [int] NOT NULL,
	[Activo] [bit] NOT NULL,
	[PeriodoAcademicoId] [int] NOT NULL,
 CONSTRAINT [PK_Profesores_Materias] PRIMARY KEY CLUSTERED
(
	[DisponibilidadProfesorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Estudiantes]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Estudiantes](
	[EstudianteId] [int] IDENTITY(1,1) NOT NULL,
	[Nombres] [varchar](50) NOT NULL,
	[Apellidos] [varchar](50) NOT NULL,
	[Matricula] [varchar](10) NOT NULL,
	[Cedula] [char](11) NOT NULL,
	[Activo] [bit] NOT NULL,
 CONSTRAINT [PK_Estudiantes] PRIMARY KEY CLUSTERED
(
	[EstudianteId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Estudiantes_Carreras]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Estudiantes_Carreras](
	[EstudianteCarreraId] [int] IDENTITY(1,1) NOT NULL,
	[EstudianteId] [int] NOT NULL,
	[CarreraId] [int] NOT NULL,
	[FechaInscripcion] [datetime] NOT NULL,
 CONSTRAINT [PK_Estudiantes_Carreras] PRIMARY KEY CLUSTERED
(
	[EstudianteCarreraId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GruposEstudiantes]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GruposEstudiantes](
	[GrupoEstudianteId] [int] IDENTITY(1,1) NOT NULL,
	[EstudianteCarreraId] [int] NOT NULL,
	[SeccionId] [int] NOT NULL,
	[FechaInscripcion] [datetime] NOT NULL,
 CONSTRAINT [PK_GruposEstudiantes] PRIMARY KEY CLUSTERED
(
	[GrupoEstudianteId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[HorariosClases]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HorariosClases](
	[HorarioClaseId] [int] IDENTITY(1,1) NOT NULL,
	[Dia] [char](10) NOT NULL,
	[HoraInicio] [time](7) NOT NULL,
	[HoraFinal] [time](7) NOT NULL,
 CONSTRAINT [PK_HorariosClases] PRIMARY KEY CLUSTERED
(
	[HorarioClaseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Materias]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Materias](
	[MateriaId] [int] IDENTITY(1,1) NOT NULL,
	[CarreraId] [int] NOT NULL,
	[Nombre] [varchar](50) NOT NULL,
	[Activo] [bit] NOT NULL,
 CONSTRAINT [PK_Materias] PRIMARY KEY CLUSTERED
(
	[MateriaId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PeriodosAcademicos]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PeriodosAcademicos](
	[PeriodoAcademicoId] [int] IDENTITY(1,1) NOT NULL,
	[Nombre] [varchar](50) NOT NULL,
	[FechaInicio] [date] NOT NULL,
	[FechaFinal] [date] NOT NULL,
	[Activo] [bit] NOT NULL,
 CONSTRAINT [PK_PeriodosAcademicos] PRIMARY KEY CLUSTERED
(
	[PeriodoAcademicoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Profesores]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Profesores](
	[ProfesorId] [int] IDENTITY(1,1) NOT NULL,
	[Nombre] [varchar](40) NOT NULL,
	[Apellido] [varchar](40) NOT NULL,
	[Telefono] [char](10) NOT NULL,
	[TelExtension] [varchar](6) NULL,
	[CorreoElectronico] [varchar](35) NOT NULL,
 CONSTRAINT [PK_Profesores] PRIMARY KEY CLUSTERED
(
	[ProfesorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Secciones]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Secciones](
	[SeccionId] [int] IDENTITY(1,1) NOT NULL,
	[DisponibilidadProfesorId] [int] NOT NULL,
	[AulaId] [int] NOT NULL,
	[Activo] [bit] NOT NULL,
 CONSTRAINT [PK_Secciones] PRIMARY KEY CLUSTERED
(
	[SeccionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Carreras]  WITH CHECK ADD  CONSTRAINT [FK_Carreras_Decanatos] FOREIGN KEY([DecanatoId])
REFERENCES [dbo].[Decanatos] ([DecanadoId])
GO
ALTER TABLE [dbo].[Carreras] CHECK CONSTRAINT [FK_Carreras_Decanatos]
GO
ALTER TABLE [dbo].[DisponibilidadProfesores]  WITH CHECK ADD  CONSTRAINT [FK_DisponibilidadProfesores_PeriodosAcademicos] FOREIGN KEY([PeriodoAcademicoId])
REFERENCES [dbo].[PeriodosAcademicos] ([PeriodoAcademicoId])
GO
ALTER TABLE [dbo].[DisponibilidadProfesores] CHECK CONSTRAINT [FK_DisponibilidadProfesores_PeriodosAcademicos]
GO
ALTER TABLE [dbo].[DisponibilidadProfesores]  WITH CHECK ADD  CONSTRAINT [FK_Profesores_Materias_Horario_HorariosClases] FOREIGN KEY([HorarioClaseId])
REFERENCES [dbo].[HorariosClases] ([HorarioClaseId])
GO
ALTER TABLE [dbo].[DisponibilidadProfesores] CHECK CONSTRAINT [FK_Profesores_Materias_Horario_HorariosClases]
GO
ALTER TABLE [dbo].[DisponibilidadProfesores]  WITH CHECK ADD  CONSTRAINT [FK_Profesores_Materias_Materias] FOREIGN KEY([MateriaId])
REFERENCES [dbo].[Materias] ([MateriaId])
GO
ALTER TABLE [dbo].[DisponibilidadProfesores] CHECK CONSTRAINT [FK_Profesores_Materias_Materias]
GO
ALTER TABLE [dbo].[DisponibilidadProfesores]  WITH CHECK ADD  CONSTRAINT [FK_Profesores_Materias_Profesores] FOREIGN KEY([ProfesorId])
REFERENCES [dbo].[Profesores] ([ProfesorId])
GO
ALTER TABLE [dbo].[DisponibilidadProfesores] CHECK CONSTRAINT [FK_Profesores_Materias_Profesores]
GO
ALTER TABLE [dbo].[Estudiantes_Carreras]  WITH CHECK ADD  CONSTRAINT [FK_Estudiantes_Carreras_Estudiantes] FOREIGN KEY([EstudianteId])
REFERENCES [dbo].[Estudiantes] ([EstudianteId])
GO
ALTER TABLE [dbo].[Estudiantes_Carreras] CHECK CONSTRAINT [FK_Estudiantes_Carreras_Estudiantes]
GO
ALTER TABLE [dbo].[GruposEstudiantes]  WITH CHECK ADD  CONSTRAINT [FK_GruposEstudiantes_Estudiantes_Carreras] FOREIGN KEY([EstudianteCarreraId])
REFERENCES [dbo].[Estudiantes_Carreras] ([EstudianteCarreraId])
GO
ALTER TABLE [dbo].[GruposEstudiantes] CHECK CONSTRAINT [FK_GruposEstudiantes_Estudiantes_Carreras]
GO
ALTER TABLE [dbo].[GruposEstudiantes]  WITH CHECK ADD  CONSTRAINT [FK_GruposEstudiantes_Secciones] FOREIGN KEY([SeccionId])
REFERENCES [dbo].[Secciones] ([SeccionId])
GO
ALTER TABLE [dbo].[GruposEstudiantes] CHECK CONSTRAINT [FK_GruposEstudiantes_Secciones]
GO
ALTER TABLE [dbo].[Materias]  WITH CHECK ADD  CONSTRAINT [FK_Materias_Carreras] FOREIGN KEY([CarreraId])
REFERENCES [dbo].[Carreras] ([CarreraId])
GO
ALTER TABLE [dbo].[Materias] CHECK CONSTRAINT [FK_Materias_Carreras]
GO
ALTER TABLE [dbo].[Secciones]  WITH CHECK ADD  CONSTRAINT [FK_Secciones_Aulas] FOREIGN KEY([AulaId])
REFERENCES [dbo].[Aulas] ([AulaId])
GO
ALTER TABLE [dbo].[Secciones] CHECK CONSTRAINT [FK_Secciones_Aulas]
GO
ALTER TABLE [dbo].[Secciones]  WITH CHECK ADD  CONSTRAINT [FK_Secciones_DisponibilidadProfesores] FOREIGN KEY([DisponibilidadProfesorId])
REFERENCES [dbo].[DisponibilidadProfesores] ([DisponibilidadProfesorId])
GO
ALTER TABLE [dbo].[Secciones] CHECK CONSTRAINT [FK_Secciones_DisponibilidadProfesores]
GO
/****** Object:  StoredProcedure [dbo].[USP_INSERT_CARRERA]    Script Date: 2/29/2020 10:00:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USP_INSERT_CARRERA]
@DECANATOID INT,
@CARRERA_NOMBRE VARCHAR(50)
AS
INSERT INTO CARRERAS(DECANATOID,NOMBRE)
VALUES(@DECANATOID,@CARRERA_NOMBRE)
GO