Sentencias DDL
Script para la creación de la base de datos
-- Database: "InvCyD"
-- DROP DATABASE "InvCyD";
CREATE DATABASE "InvCyD"
WITH OWNER = postgres
ENCODING = 'WIN1252';
Script para la creación de las tablas
Tabla Persona
-- Table: "Persona"
-- DROP TABLE "Persona";
CREATE TABLE "Persona"
(
"IDPersona" character varying(15) NOT NULL,
"Nombre1" character varying(20) NOT NULL,
"Nombre2" character varying(20),
"Apellido1" character varying(20) NOT NULL,
"Apellido2" character varying(20),
"Sexo" character(1) NOT NULL,
"FechaNac" date NOT NULL,
"Direccion" character varying(50),
"Email" character varying(50),
CONSTRAINT "PKPersona" PRIMARY KEY ("IDPersona"),
CONSTRAINT "CHKPersona" CHECK ("Sexo" = 'H'::bpchar OR "Sexo" = 'M'::bpchar)
)
WITH (OIDS=FALSE);
ALTER TABLE "Persona" OWNER TO postgres;
Tabla Empleado
-- Table: "Empleado"
-- DROP TABLE "Empleado";
CREATE TABLE "Empleado"
(
-- Heredado: "IDPersona" character varying(15) NOT NULL,
-- Heredado: "Nombre1" character varying(20) NOT NULL,
-- Heredado: "Nombre2" character varying(20),
-- Heredado: "Apellido1" character varying(20) NOT NULL,
-- Heredado: "Apellido2" character varying(20),
-- Heredado: "Sexo" character(1) NOT NULL,
-- Heredado: "FechaNac" date NOT NULL,
-- Heredado: "Direccion" character varying(50),
-- Heredado: "Email" character varying(50),
"Puesto" character varying(30),
CONSTRAINT "PKEmpleado" PRIMARY KEY ("IDPersona"),
CONSTRAINT "FKEmpleado" FOREIGN KEY ("IDPersona")
REFERENCES "Persona" ("IDPersona") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT,
CONSTRAINT "CHKPersona" CHECK ("Sexo" = 'H'::bpchar OR "Sexo" = 'M'::bpchar)
)
INHERITS ("Persona")
WITH (OIDS=FALSE);
ALTER TABLE "Empleado" OWNER TO postgres;


Tabla Cliente
-- Table: "Cliente"

-- DROP TABLE "Cliente";
CREATE TABLE "Cliente"
(
-- Heredado: "IDPersona" character varying(15) NOT NULL,
-- Heredado: "Nombre1" character varying(20) NOT NULL,
-- Heredado: "Nombre2" character varying(20),
-- Heredado: "Apellido1" character varying(20) NOT NULL,
-- Heredado: "Apellido2" character varying(20),
-- Heredado: "Sexo" character(1) NOT NULL,
-- Heredado: "FechaNac" date NOT NULL,
-- Heredado: "Direccion" character varying(50),
-- Heredado: "Email" character varying(50),
"Tipo_Cliente" character varying(30),
CONSTRAINT "PKCliente" PRIMARY KEY ("IDPersona"),
CONSTRAINT "FKCliente" FOREIGN KEY ("IDPersona")
REFERENCES "Persona" ("IDPersona") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT,
CONSTRAINT "CHKPersona" CHECK ("Sexo" = 'H'::bpchar OR "Sexo" = 'M'::bpchar)
)
INHERITS ("Persona")
WITH (OIDS=FALSE);
ALTER TABLE "Cliente" OWNER TO postgres;

Tabla Usuario
-- Table: "Usuario"
-- DROP TABLE "Usuario";
CREATE TABLE "Usuario"
(
"IDUsuario" character varying(30) NOT NULL,
"Contrasena" character varying(30) NOT NULL,
"IDEmpleado" character varying(15) NOT NULL,
CONSTRAINT "PKUsuario" PRIMARY KEY ("IDUsuario"),
CONSTRAINT "FKUsuario" FOREIGN KEY ("IDEmpleado")
REFERENCES "Empleado" ("IDPersona") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT
)
WITH (OIDS=FALSE);
ALTER TABLE "Usuario" OWNER TO postgres;
Tabla Modulo
-- Table: "Modulo"
-- DROP TABLE "Modulo";
CREATE TABLE "Modulo"
(
"IDModulo" integer NOT NULL,
"Descripcion" character varying(30) NOT NULL,
"AClientes" boolean,
"AEmpleados" boolean,
"AProductos" boolean,
"ABodegas" boolean,
"AUsuarios" boolean,
"AModulos" boolean,
"APedidos" boolean,
"AFacturas" boolean,
"AIngresos" boolean,
"AInventario" boolean,
"RVentas" boolean,
"RClientes" boolean,
"RPedidos" boolean,
CONSTRAINT "PKModulo" PRIMARY KEY ("IDModulo"),
CONSTRAINT "UModulo" UNIQUE ("Descripcion")
)
WITH (OIDS=FALSE);
ALTER TABLE "Modulo" OWNER TO postgres;
Tabla Bodega
-- Table: "Bodega"
-- DROP TABLE "Bodega";
CREATE TABLE "Bodega"
(
"IDBodega" integer NOT NULL,
"Nombre" character varying(30) NOT NULL,
CONSTRAINT "PKBodega" PRIMARY KEY ("IDBodega"),
CONSTRAINT "UBodega" UNIQUE ("Nombre")
)
WITH (OIDS=FALSE);
ALTER TABLE "Bodega" OWNER TO postgres;

Tabla Producto
-- Table: "Producto"
-- DROP TABLE "Producto";

CREATE TABLE "Producto"
(
"IDProducto" integer NOT NULL,
"Nombre" character varying(30) NOT NULL,
"PrecioUnitario" money NOT NULL,
"CostoUnitario" money NOT NULL,
"UnidadMed" character varying(15),
CONSTRAINT "PKProducto" PRIMARY KEY ("IDProducto")
)
WITH (OIDS=FALSE);
ALTER TABLE "Producto" OWNER TO postgres;
Tabla Pedido
-- Table: "Pedido"
-- DROP TABLE "Pedido";
CREATE TABLE "Pedido"
(
"IDPedido" integer NOT NULL,
"FechaPed" date NOT NULL,
"IDEmpleado" character varying(15) NOT NULL,
"IDCliente" character varying(15) NOT NULL,
CONSTRAINT "PKPedido" PRIMARY KEY ("IDPedido"),
CONSTRAINT "FKPedido" FOREIGN KEY ("IDCliente")
REFERENCES "Cliente" ("IDPersona") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT,
CONSTRAINT "FKPedido2" FOREIGN KEY ("IDEmpleado")
REFERENCES "Empleado" ("IDPersona") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT
)
WITH (OIDS=FALSE);
ALTER TABLE "Pedido" OWNER TO postgres;
Tabla Factura
-- Table: "Factura"
-- DROP TABLE "Factura";
CREATE TABLE "Factura"
(
"IDFactura" integer NOT NULL,
"FechaFactura" date NOT NULL,
"ISV" money,
"Total" money,
"IDEmpleado" character varying(15) NOT NULL,
"IDPedido" integer NOT NULL,
CONSTRAINT "PKFactura" PRIMARY KEY ("IDFactura"),
CONSTRAINT "FKFactura" FOREIGN KEY ("IDEmpleado")
REFERENCES "Empleado" ("IDPersona") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT,
CONSTRAINT "FKFactura2" FOREIGN KEY ("IDPedido")
REFERENCES "Pedido" ("IDPedido") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT
)
WITH (OIDS=FALSE);
ALTER TABLE "Factura" OWNER TO postgres;
Tabla Ingreso
-- Table: "Ingreso"
-- DROP TABLE "Ingreso";
CREATE TABLE "Ingreso"
(
"IDIngreso" integer NOT NULL,
"FechaIngreso" date NOT NULL,
"Total" money,
"IDEmpleado" character varying(15) NOT NULL,
CONSTRAINT "PKIngreso" PRIMARY KEY ("IDIngreso"),
CONSTRAINT "FKIngreso" FOREIGN KEY ("IDEmpleado")
REFERENCES "Empleado" ("IDPersona") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT
)
WITH (OIDS=FALSE);
ALTER TABLE "Ingreso" OWNER TO postgres;
Tabla Inventario
-- Table: "Inventario"
-- DROP TABLE "Inventario";
CREATE TABLE "Inventario"
(
"Correlativo" integer NOT NULL,
"FechaInv" date NOT NULL,
"IDProducto" integer NOT NULL,
"Tipo_Operacion" character varying(15),
"CantIni" integer NOT NULL,
"CantIngresos" integer NOT NULL,
"CantSalidas" integer NOT NULL,
"CantFinal" integer NOT NULL,
"IDIngresos" integer,
"IDPedidos" integer,
"IDBodega" integer NOT NULL,
CONSTRAINT "PKInventario" PRIMARY KEY ("Correlativo", "FechaInv", "IDProducto"),
CONSTRAINT "FKInventario" FOREIGN KEY ("IDBodega")
REFERENCES "Bodega" ("IDBodega") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "FKInventario2" FOREIGN KEY ("IDIngresos")
REFERENCES "Ingreso" ("IDIngreso") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT,
CONSTRAINT "FKInventario3" FOREIGN KEY ("IDPedidos")
REFERENCES "Pedido" ("IDPedido") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "FKInventario4" FOREIGN KEY ("IDProducto")
REFERENCES "Producto" ("IDProducto") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE "Inventario" OWNER TO postgres;
Tabla Accesa
-- Table: "Accesa"
-- DROP TABLE "Accesa";
CREATE TABLE "Accesa"
(
"IDUsuario" character varying(30) NOT NULL,
"IDModulo" integer NOT NULL,
CONSTRAINT "PKAccesa" PRIMARY KEY ("IDUsuario", "IDModulo"),
CONSTRAINT "FKAccesa" FOREIGN KEY ("IDUsuario")
REFERENCES "Usuario" ("IDUsuario") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "FKAccesa2" FOREIGN KEY ("IDModulo")
REFERENCES "Modulo" ("IDModulo") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE "Accesa" OWNER TO postgres;
Tabla DetallePedido
-- Table: "DetallePedido"
-- DROP TABLE "DetallePedido";
CREATE TABLE "DetallePedido"
(
"IDPedido" integer NOT NULL,
"Cantidad" integer NOT NULL,
"IDProducto" integer NOT NULL,
CONSTRAINT "PKDetPed" PRIMARY KEY ("IDPedido", "IDProducto"),
CONSTRAINT "FKDetPed" FOREIGN KEY ("IDPedido")
REFERENCES "Pedido" ("IDPedido") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "FKDetPed2" FOREIGN KEY ("IDProducto")
REFERENCES "Producto" ("IDProducto") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE "DetallePedido" OWNER TO postgres;
Tabla DetalleFactura
-- Table: "DetalleFactura"
-- DROP TABLE "DetalleFactura";
CREATE TABLE "DetalleFactura"
(
"IDFactura" integer NOT NULL,
"Cantidad" integer NOT NULL,
"IDProducto" integer NOT NULL,
CONSTRAINT "PKDetFact" PRIMARY KEY ("IDFactura", "IDProducto"),
CONSTRAINT "FKDetFact" FOREIGN KEY ("IDFactura")
REFERENCES "Factura" ("IDFactura") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "FKDetFact2" FOREIGN KEY ("IDProducto")
REFERENCES "Producto" ("IDProducto") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE "DetalleFactura" OWNER TO postgres;
Tabla DetalleIngreso
-- Table: "DetalleIngreso"
-- DROP TABLE "DetalleIngreso";
CREATE TABLE "DetalleIngreso"
(
"IDIngreso" integer NOT NULL,
"IDProducto" integer NOT NULL,
"Cantidad" integer NOT NULL,
CONSTRAINT "PKDetIng" PRIMARY KEY ("IDIngreso", "IDProducto"),
CONSTRAINT "FKDetIng" FOREIGN KEY ("IDIngreso")
REFERENCES "Ingreso" ("IDIngreso") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "FKDetIng2" FOREIGN KEY ("IDProducto")
REFERENCES "Producto" ("IDProducto") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE "DetalleIngreso" OWNER TO postgres;
Tabla Telefonos
-- Table: "Telefonos"
-- DROP TABLE "Telefonos";
CREATE TABLE "Telefonos"
(
"IDPersona" character varying(15) NOT NULL,
"Telefono" character varying(10) NOT NULL,
CONSTRAINT "PKTelefonos" PRIMARY KEY ("IDPersona", "Telefono"),
CONSTRAINT "FKTelefonos" FOREIGN KEY ("IDPersona")
REFERENCES "Persona" ("IDPersona") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE "Telefonos" OWNER TO postgres;