CreateDatabase.sql

USE [master]
GO

/****** Object:  Database [OBLIGATORIOBD2]    Script Date: 07/08/2022 20:04:29 ******/
CREATE DATABASE [OBLIGATORIOBD2]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Obligatorio_BD2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\OBLIGATORIOBD2.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Obligatorio_BD2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\OBLIGATORIOBD2_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [OBLIGATORIOBD2].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [OBLIGATORIOBD2] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET ARITHABORT OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET  DISABLE_BROKER 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET  MULTI_USER 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [OBLIGATORIOBD2] SET DB_CHAINING OFF 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET DELAYED_DURABILITY = DISABLED 
GO

ALTER DATABASE [OBLIGATORIOBD2] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO

ALTER DATABASE [OBLIGATORIOBD2] SET QUERY_STORE = OFF
GO

ALTER DATABASE [OBLIGATORIOBD2] SET  READ_WRITE 
GO


--TABLA AUDITORIA
USE [OBLIGATORIOBD2]
GO

/****** Object:  Table [dbo].[Auditoria]    Script Date: 07/08/2022 20:05:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Auditoria](
	[idAudit] [int] IDENTITY(1,1) NOT NULL,
	[tabla] [varchar](30) NULL,
	[host] [varchar](30) NULL,
	[fecha] [datetime] NULL,
	[operacion] [varchar](20) NULL,
	[anterior] [varchar](50) NULL,
	[actual] [varchar](50) NULL,
	[obs] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[idAudit] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

--TABLA ESTACIONES
USE [OBLIGATORIOBD2]
GO

/****** Object:  Table [dbo].[Estaciones]    Script Date: 07/08/2022 20:08:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Estaciones](
	[Codigo] [int] NOT NULL,
	[Descripcion] [nvarchar](50) NOT NULL,
	[Barrio] [nvarchar](50) NOT NULL,
	[ACUMULADO] [int] NULL,
 CONSTRAINT [PK_Estaciones] PRIMARY KEY CLUSTERED 
(
	[Codigo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Estaciones] ADD  DEFAULT ((0)) FOR [ACUMULADO]
GO

--TABLA LINEAS
USE [OBLIGATORIOBD2]
GO

/****** Object:  Table [dbo].[Lineas]    Script Date: 07/08/2022 20:08:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Lineas](
	[Numero] [int] NOT NULL,
	[Descripcion] [nvarchar](50) NULL,
	[Codigo_Estacion_Inicio] [int] NOT NULL,
	[Codigo_Estacion_Final] [int] NOT NULL,
	[Longitud] [int] NOT NULL,
	[Color] [nvarchar](8) NOT NULL,
 CONSTRAINT [PK_Lineas] PRIMARY KEY CLUSTERED 
(
	[Numero] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Lineas]  WITH CHECK ADD  CONSTRAINT [FK_Codigo_Estacion_Final] FOREIGN KEY([Codigo_Estacion_Final])
REFERENCES [dbo].[Estaciones] ([Codigo])
GO

ALTER TABLE [dbo].[Lineas] CHECK CONSTRAINT [FK_Codigo_Estacion_Final]
GO

ALTER TABLE [dbo].[Lineas]  WITH CHECK ADD  CONSTRAINT [FK_Codigo_Estacion_Inicio] FOREIGN KEY([Codigo_Estacion_Inicio])
REFERENCES [dbo].[Estaciones] ([Codigo])
GO

ALTER TABLE [dbo].[Lineas] CHECK CONSTRAINT [FK_Codigo_Estacion_Inicio]
GO

ALTER TABLE [dbo].[Lineas]  WITH CHECK ADD  CONSTRAINT [CK_Color] CHECK  (([Color]='Negro' OR [Color]='Verde' OR [Color]='Naranja' OR [Color]='Amarillo' OR [Color]='Azul' OR [Color]='Rojo'))
GO

ALTER TABLE [dbo].[Lineas] CHECK CONSTRAINT [CK_Color]
GO

ALTER TABLE [dbo].[Lineas]  WITH CHECK ADD  CONSTRAINT [CK_Longitud] CHECK  (([Longitud]>=(1)))
GO

ALTER TABLE [dbo].[Lineas] CHECK CONSTRAINT [CK_Longitud]
GO


--TABLA PASA
USE [OBLIGATORIOBD2]
GO

/****** Object:  Table [dbo].[Pasa]    Script Date: 07/08/2022 20:09:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Pasa](
	[Numero_Linea] [int] NOT NULL,
	[Codigo_Estacion] [int] NOT NULL,
	[Id_Tren] [int] NOT NULL,
	[Fecha_Hora] [datetime] NOT NULL,
 CONSTRAINT [UK_Pasa] UNIQUE NONCLUSTERED 
(
	[Numero_Linea] ASC,
	[Codigo_Estacion] ASC,
	[Id_Tren] ASC,
	[Fecha_Hora] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Pasa]  WITH CHECK ADD  CONSTRAINT [FK_Estacion_P] FOREIGN KEY([Codigo_Estacion])
REFERENCES [dbo].[Estaciones] ([Codigo])
GO

ALTER TABLE [dbo].[Pasa] CHECK CONSTRAINT [FK_Estacion_P]
GO

ALTER TABLE [dbo].[Pasa]  WITH CHECK ADD  CONSTRAINT [FK_Linea_P] FOREIGN KEY([Numero_Linea])
REFERENCES [dbo].[Lineas] ([Numero])
GO

ALTER TABLE [dbo].[Pasa] CHECK CONSTRAINT [FK_Linea_P]
GO

ALTER TABLE [dbo].[Pasa]  WITH CHECK ADD  CONSTRAINT [FK_Tren] FOREIGN KEY([Id_Tren])
REFERENCES [dbo].[Trenes] ([Numero])
GO

ALTER TABLE [dbo].[Pasa] CHECK CONSTRAINT [FK_Tren]
GO


--TABLA TIENE
USE [OBLIGATORIOBD2]
GO

/****** Object:  Table [dbo].[Tiene]    Script Date: 07/08/2022 20:10:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Tiene](
	[Numero_Linea] [int] NOT NULL,
	[Codigo_Estacion] [int] NOT NULL,
 CONSTRAINT [UK_Tiene] UNIQUE NONCLUSTERED 
(
	[Numero_Linea] ASC,
	[Codigo_Estacion] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tiene]  WITH CHECK ADD  CONSTRAINT [FK_Estacion] FOREIGN KEY([Codigo_Estacion])
REFERENCES [dbo].[Estaciones] ([Codigo])
GO

ALTER TABLE [dbo].[Tiene] CHECK CONSTRAINT [FK_Estacion]
GO

ALTER TABLE [dbo].[Tiene]  WITH CHECK ADD  CONSTRAINT [FK_Linea] FOREIGN KEY([Numero_Linea])
REFERENCES [dbo].[Lineas] ([Numero])
GO

ALTER TABLE [dbo].[Tiene] CHECK CONSTRAINT [FK_Linea]
GO

--TABLA TRENES
USE [OBLIGATORIOBD2]
GO

/****** Object:  Table [dbo].[Trenes]    Script Date: 07/08/2022 20:10:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Trenes](
	[Numero] [int] NOT NULL,
	[Descripcion] [nvarchar](50) NULL,
	[Letra] [nchar](1) NOT NULL,
	[Cantidad_Vagon] [int] NOT NULL,
	[Capacidad_Vagon] [int] NOT NULL,
 CONSTRAINT [PK_Trenes] PRIMARY KEY CLUSTERED 
(
	[Numero] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Trenes]  WITH CHECK ADD  CONSTRAINT [Capacidad_Vagon] CHECK  (([Capacidad_Vagon]<(41)))
GO

ALTER TABLE [dbo].[Trenes] CHECK CONSTRAINT [Capacidad_Vagon]
GO