Ejemplo de creación y diseño de base de datos en Microsoft SQL Server

Microsoft SQL Server SQL

Supongamos que queremos crear una base de datos para una tienda en línea que vende productos. Primero, debemos identificar las entidades relevantes para nuestra base de datos.

Aquí te presento un ejemplo de creación y diseño de una base de datos utilizando las tablas tb_cliente, tb_producto, tb_factura y tb_factura_detalle.

En Microsoft SQL Server se pueden crear bases de datos y cada base de datos puede contener uno o varios esquemas, vamos a crear la base de datos "BDCRUD" y por defecto se crear el esquema "dbo".

En Microsoft SQL Server, dbo es un esquema predeterminado que se crea automáticamente cuando se crea una nueva base de datos. dbo es un acrónimo de "Database Owner" (Propietario de la base de datos).

El esquema dbo se utiliza como el contenedor predeterminado para los objetos de base de datos, como tablas, vistas, procedimientos almacenados, etc. que no se crean en otro esquema específico. Los objetos de base de datos que se crean en el esquema dbo se identifican con el prefijo "dbo." seguido del nombre del objeto.

CREATE DATABASE [BDCRUD]
GO
USE [BDCRUD]
GO

Tabla "tb_cliente"

La tabla "tb_cliente" contiene información sobre los clientes de la tienda, incluyendo su nombre, dirección y fecha de nacimiento.

CREATE TABLE [dbo].[tb_cliente](
	[IDCLIENTE] [int] IDENTITY(1,1) NOT NULL,
	[NOMBRES] [varchar](100) NOT NULL,
	[DIRECCION] [varchar](100) NULL,
	[FECHA_NACIMIENTO] [date] NOT NULL,
	[FECHA_REGISTRO] [datetime] NOT NULL,
 CONSTRAINT [tb_cliente_pk] PRIMARY KEY CLUSTERED 
(
	[IDCLIENTE] 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].[tb_cliente] ADD  CONSTRAINT [tb_cliente_const]  DEFAULT (NULL) FOR [DIRECCION]
GO

Script para insertar datos a la tabla "tb_cliente"

SET IDENTITY_INSERT dbo.tb_cliente ON;
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (1,'Juan Perez','Av. Siempreviva 123','1990-06-15',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (2,'Maria Rodriguez','Calle Falsa 123','1985-08-20',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (3,'Pedro Gomez','Av. Principal 456','1995-02-10',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (4,'Ana Fernandez','Calle Real 789','1982-12-01',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (5,'Luisa Martinez','Av. America 321','1993-07-18',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (6,'Jorge Ramirez','Calle Luna 456','1978-11-30',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (7,'Sofia Sanchez','Av. Sol 987','1998-04-05',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (8,'Carlos Torres','Calle Estrella 654','1987-03-22',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (9,'Fernando Garcia','Av. Universitaria 321','1991-09-28',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (10,'Gloria Castro','Calle Alegria 987','1975-05-12',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (11,'Diego Lopez','Av. Libertad 456','1996-08-02',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (12,'Valeria Jimenez','Calle Flores 789','1989-01-07',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (13,'Ricardo Hernandez','Av. Rio 123','1979-10-25',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (14,'Silvia Morales','Calle Montaña 456','1992-03-12',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (15,'Gabriel Diaz','Av. Peru 987','1984-06-22',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (16,'Lucia Ortiz','Calle Paraiso 654','1976-09-08',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (17,'Oscar Nieto','Av. España 321','1990-12-15',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (18,'Alicia Rivera','Calle Paz 789','1983-05-28',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (19,'Pablo Castro','Av. Amazonas 456','1994-02-03',GETDATE());
INSERT INTO dbo.tb_cliente (idcliente,nombres,direccion,fecha_nacimiento,fecha_registro) values (20,'Natalia Vega','Calle Luna 123','1977-11-17',GETDATE());
SET IDENTITY_INSERT dbo.tb_cliente OFF;

Tabla "tb_producto"

La tabla "tb_producto" contiene información sobre los productos que vende la tienda, incluyendo su nombre, precio y stock.

CREATE TABLE [dbo].[tb_producto](
	[IDPRODUCTO] [int] IDENTITY(1,1) NOT NULL,
	[NOMBRE] [varchar](100) NOT NULL,
	[PRECIO] [decimal](10, 2) NOT NULL,
	[STOCK] [int] NOT NULL,
	[FECHA_REGISTRO] [datetime] NOT NULL,
 CONSTRAINT [tb_producto_pk] PRIMARY KEY CLUSTERED 
(
	[IDPRODUCTO] 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

Script para insertar datos a la tabla "tb_producto"

SET IDENTITY_INSERT dbo.tb_producto ON;
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (1, 'Smartphone Samsung Galaxy S20', 899.99, 10.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (2, 'Laptop Dell Inspiron 15', 1099.99, 5.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (3, 'Smart TV LG 55"', 1299.99, 3.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (4, 'Aspiradora Dyson V11', 599.99, 2.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (5, 'Camisa Polo Ralph Lauren', 99.99, 20.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (6, 'Tenis Nike Air Max', 149.99, 8.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (7, 'Muñeca Barbie Dreamhouse', 199.99, 4.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (8, 'Camisa', 39.99, 50.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (9, 'Pantalón', 59.99, 30.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (10, 'Zapatos', 89.99, 20.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (11, 'Gorra', 19.99, 100.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (12, 'Bufanda', 29.99, 40.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (13, 'Guantes', 14.99, 50.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (14, 'Chaqueta', 99.99, 10.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (15, 'Calcetines', 9.99, 80.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (16, 'Sombrero', 24.99, 30.00, GETDATE());
insert into dbo.tb_producto (idproducto, nombre, precio, stock, fecha_registro) values (17, 'Cinturón', 19.99, 60.00, GETDATE());
SET IDENTITY_INSERT dbo.tb_producto OFF;

Tabla "tb_factura"

La tabla "tb_factura" relaciona los clientes con las facturas que han realizado y contiene información sobre la fecha y el total(monto) de la factura

CREATE TABLE [dbo].[tb_factura](
	[IDFACTURA] [int] IDENTITY(1,1) NOT NULL,
	[ID_CLIENTE] [int] NOT NULL,
	[NRO_FACTURA] [varchar](10) NOT NULL,
	[MONTO] [decimal](10, 2) NOT NULL,
	[FECHA_REGISTRO] [datetime] NOT NULL,
 CONSTRAINT [tb_factura_pk] PRIMARY KEY CLUSTERED 
(
	[IDFACTURA] 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].[tb_factura]  WITH CHECK ADD  CONSTRAINT [tb_factura_fk] FOREIGN KEY([ID_CLIENTE])
REFERENCES [dbo].[tb_cliente] ([IDCLIENTE])
GO

ALTER TABLE [dbo].[tb_factura] CHECK CONSTRAINT [tb_factura_fk]
GO

Script para insertar datos a la tabla "tb_factura"

SET IDENTITY_INSERT dbo.tb_factura ON;
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(1,1,'FAC001',100.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(2,2,'FAC002',200.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(3,3,'FAC003',300.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(4,4,'FAC004',400.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(5,5,'FAC005',500.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(6,1,'FAC006',150.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(7,2,'FAC007',250.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(8,3,'FAC008',350.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(9,4,'FAC009',450.00,GETDATE());
INSERT INTO dbo.tb_factura (idfactura,id_cliente,nro_factura,monto,fecha_registro) values(10,5,'FAC010',550.00,GETDATE());
SET IDENTITY_INSERT dbo.tb_factura OFF;

Tabla "tb_factura_detalle"

La tabla "tb_factura_detalle" contiene información sobre los productos que se incluyen en cada factura y la cantidad y precio de cada producto.

CREATE TABLE [dbo].[tb_factura_detalle](
	[IDDETALLEFACTURA] [int] IDENTITY(1,1) NOT NULL,
	[ID_FACTURA] [int] NOT NULL,
	[ID_PRODUCTO] [int] NOT NULL,
	[CANTIDAD] [int] NOT NULL,
	[PRECIO] [decimal](10, 2) NOT NULL,
	[SUBTOTAL] [decimal](10, 2) NOT NULL,
	[FECHA_REGISTRO] [datetime] NOT NULL,
 CONSTRAINT [tb_factura_detalle_pk] PRIMARY KEY CLUSTERED 
(
	[IDDETALLEFACTURA] 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].[tb_factura_detalle]  WITH CHECK ADD  CONSTRAINT [tb_factura_detalle_fk01] FOREIGN KEY([ID_FACTURA])
REFERENCES [dbo].[tb_factura] ([IDFACTURA])
GO

ALTER TABLE [dbo].[tb_factura_detalle] CHECK CONSTRAINT [tb_factura_detalle_fk01]
GO

ALTER TABLE [dbo].[tb_factura_detalle]  WITH CHECK ADD  CONSTRAINT [tb_factura_detalle_fk02] FOREIGN KEY([ID_PRODUCTO])
REFERENCES [dbo].[tb_producto] ([IDPRODUCTO])
GO

ALTER TABLE [dbo].[tb_factura_detalle] CHECK CONSTRAINT [tb_factura_detalle_fk02]
GO

Script para insertar datos a la tabla "tb_factura_detalle"

SET IDENTITY_INSERT dbo.tb_factura_detalle ON;
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (1, 1, 1, 2, 25.00, 50.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (2, 1, 2, 1, 50.00, 50.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (3, 2, 3, 3, 20.00, 60.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (4, 2, 4, 2, 35.00, 70.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (5, 3, 5, 4, 15.00, 60.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (6, 3, 6, 1, 100.00, 100.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (7, 4, 7, 3, 30.00, 90.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (8, 4, 8, 2, 45.00, 90.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (9, 5, 9, 5, 10.00, 50.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (10, 5, 10, 2, 75.00, 150.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (11, 6, 1, 1, 25.00, 25.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (12, 6, 4, 3, 35.00, 105.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (13, 7, 6, 2, 100.00, 200.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (14, 7, 9, 1, 10.00, 10.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (15, 8, 2, 2, 50.00, 100.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (16, 8, 3, 1, 20.00, 20.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (17, 9, 5, 3, 15.00, 45.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (18, 9, 8, 4, 45.00, 180.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (19, 10, 10, 1, 75.00, 75.00, GETDATE());
insert into dbo.tb_factura_detalle (iddetallefactura, id_factura, id_producto, cantidad, precio, subtotal, fecha_registro) values (20, 10, 7, 2, 30.00, 60.00, GETDATE());
SET IDENTITY_INSERT dbo.tb_factura_detalle OFF;

Diseño de la base de datos "bdcrud"

Con este diseño de base de datos, podemos almacenar y recuperar información de manera eficiente sobre los clientes que realizan compras, los productos que vendemos, las facturas que generamos y los detalles de cada factura. Además, podemos realizar consultas y generar informes útiles para la gestión de la tienda.

Para visualizar el diseño completo de las tablas creadas utilizando el modelo lógico, lo ideal es utilizar una herramienta de modelado de datos que te permita crear y visualizar diagramas ER (Entidad-Relación) que representen la estructura de la base de datos y las relaciones entre las tablas.