El blog de los Ingenieros en TI

Scrip para darle mantenimiento a las tablas de una base de datos

*********************************************************

--Creación de Base de Datos Préstamos
drop database prestamos;

CREATE DATABASE Prestamos
ON
PRIMARY
(NAME = Prestamos_Primary,
FILENAME = 'C:\Prestamos\Prestamos_Primary.mdf',
SIZE = 50MB,
MAXSIZE = 200,
FILEGROWTH = 20),
FILEGROUP VentasFG
( NAME = VentasData1,
FILENAME = 'C:\Prestamos\PrestamosData1.nfd',
SIZE = 200MB,
MAXSIZE = 800,
FILEGROWTH = 100)
LOG ON
( NAME = Archlog1,
FILENAME = 'C:\Prestamos\Prestamos_Log.lfd',
SIZE = 300MB,
MAXSIZE = 800,
FILEGROWTH = 100);

---Crear Tablas de Base de Datos
use prestamos;
go

--1: Tabla de clientes
Create TABLE dbo.Clientes
(ID_Cliente varchar(12) PRIMARY KEY,
CONSTRAINT chkid_cliente check --chkid_cliente es el nombre del constraint
(ID_Cliente like '[0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),--corquetes= valores prmitidos
Nombre varchar(50) NOT NULL,
Apellido1 varchar(50) NOT NULL,
Apellido2 varchar(50) NOT NULL,
Fecha_Nacimiento datetime NOT NULL,
Sexo varchar(1) NOT NULL,
CHECK (sexo ='M' or sexo='F'),
codigo_pais int NOT NULL,
codigo_provincia int NOT NULL,
Estado_Civil varchar(1) NOT NULL);

--Agregar campos a la tabla de clientes
Alter Table dbo.Clientes
Add Lugar_Trabajo varchar(50) default 'Universidad Americana',
Usuario_Incluye varchar(50);

--Ver detalle de la tabla dbo.clientes
EXEC sp_help Clientes;

--Eliminar campos de la tabla clientes
Alter Table dbo.Clientes
Drop column Usuario_Incluye;

--Modificar el tamaño en un campo de una tabla
Alter Table dbo.Clientes
Alter column Lugar_Trabajo varchar(100);

--Modificar el tipo de datos en un campo de una tabla
Alter Table dbo.Clientes
Alter column Estado_Civil int;

--Insertar datos a la tabla dbo.clientes
insert into dbo.clientes (id_cliente,nombre,apellido1,apellido2,fecha_nacimiento,sexo,estado_civil,
codigo_pais, codigo_provincia)
values('01-0000-0322','Carlos Alberto','Molina','Gutiérrez',convert(datetime,'10/15/1970'),'F',1,
1, 1);


--Seleccionar todos los campos de la tabla y observar su contenido
select * from dbo.clientes;


--2: Tabla de préstamos
Create TABLE dbo.prestamos
(ID_prestamo varchar(12) unique, --valor unico
tipo_prestamo int NOT NULL,
monto_prestamo money NOT NULL,
fecha_formalizacion datetime NOT NULL,
fecha_desembolso datetime NOT NULL,
fecha_vencimiento datetime NOT NULL);

--3: Tabla Relación de Préstamos y Clientes (Muchos clientes pueden tener muchos prestamos y viceversa)
Create Table dbo.Cliente_x_Prestamo
(ID_prestamo varchar(12),
ID_Cliente varchar(12),
tipo_relacion varchar(1)
---LLave primaria de dos campos de la tabla
CONSTRAINT PK_Cliente_x_Prestamo PRIMARY KEY CLUSTERED --clustered se ordena de acuerdo a estos campos
(id_prestamo ASC, id_cliente ASC),
--LLaves foraneas de las tablas clientes y prestamos respectivamente
FOREIGN KEY (ID_cliente) REFERENCES dbo.Clientes(ID_cliente),
FOREIGN KEY (ID_prestamo) REFERENCES dbo.prestamos(ID_prestamo));

--Inserción de datos FALTA CLIENTE Y PRESTAMO
insert into dbo.cliente_x_prestamo (ID_prestamo,ID_Cliente,tipo_relacion)
values(1,'01-0888-0999',1);

--Tiene que insertar el cliente
insert into dbo.clientes (id_cliente,nombre,apellido1,apellido2,fecha_nacimiento,sexo,estado_civil,
codigo_pais, codigo_provincia)
values('01-0888-0999','Carlos Alberto','Molina','Gutiérrez',convert(datetime,'10/15/1970'),'F',1,
1, 1);

--Volver a insertar la relación PRESTAMO NO EXISTE
insert into dbo.cliente_x_prestamo (ID_prestamo,ID_Cliente,tipo_relacion)
values(1,'01-0888-0999',1);

--Tiene que insertar el préstamo
insert into dbo.prestamos (id_prestamo,monto_prestamo,fecha_formalizacion,fecha_desembolso,fecha_vencimiento, tipo_prestamo)
values (1,1000000.00,'02/01/2010','02/01/2010','02/01/2020', 1);

--Volver a insertar la relación
insert into dbo.cliente_x_prestamo (ID_prestamo,ID_Cliente,tipo_relacion)
values(1,'01-0888-0999',1);

select * from dbo.cliente_x_prestamo;

--Creación de reglas para campos de tablas
CREATE RULE Formato_prestamo --es lo mismo que un constraint
AS --se cuando ya se ha creado la tabla
@value like '[0-9][0-9][0-9]';--no debe de haber valores en la tabla diferentes al formato

--Enlace de regla
EXECUTE sp_bindrule 'Formato_prestamo','dbo.prestamos.id_prestamo';--le asigno la regla al campo de la tabla

--Volver a insertar el prestamo
insert into dbo.prestamos (id_prestamo,monto_prestamo,fecha_formalizacion,fecha_desembolso,fecha_vencimiento, tipo_prestamo)
values ('111',1000000.00,'02/01/2010','02/01/2010','02/01/2020',1);

--Agregar nuevas llaves foraneas a la tabla clientes
CREATE TABLE PAIS (
codigo_pais INT primary key,
descripcion varchar(50));

--borrado en cascada
delete from cliente_x_prestamo
delete from prestamos
delete from clientes

--agrega a la tabla ya creada una llave foranea
ALTER TABLE DBO.CLIENTES add
FOREIGN KEY (codigo_pais) REFERENCES dbo.pais(codigo_PAIS);

--agrega dos campos y una llave foranea
CREATE TABLE Provincia (
codigo_provincia INT primary key,
codigo_pais int,
FOREIGN KEY (codigo_pais) REFERENCES dbo.pais(codigo_PAIS),
descripcion varchar(50));

ALTER TABLE DBO.CLIENTES add
FOREIGN KEY (codigo_provincia) REFERENCES dbo.provincia(codigo_provincia);


--Borrado de tablas
drop table dbo.cliente_x_prestamo;
drop table dbo.prestamos;
drop table dbo.clientes;