Aprendiendo-Db--Sql-/script_bd_prueba.sql at main · Carchaloneo22/Aprendiendo-Db--Sql- · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
DROP TABLE IF EXISTS tab_pagos;
DROP TABLE IF EXISTS tab_det_compra;
DROP TABLE IF EXISTS tab_enc_compra;
DROP TABLE IF EXISTS tab_prod_prov;
DROP TABLE IF EXISTS tab_prov;
DROP TABLE IF EXISTS tab_prod;
DROP TABLE IF EXISTS tab_ciud;
DROP TABLE IF EXISTS tab_dptos;
DROP TYPE IF EXISTS datos;
DROP TABLE IF EXISTS tab_pmtros;

CREATE TABLE IF NOT EXISTS tab_pmtros
(
	id_empresa	DECIMAL(10,0)	NOT NULL,
	dia_pagos1	DECIMAL(1,0)	NOT NULL,
	dia_pagos2	DECIMAL(1,0)	NOT NULL,
	dia_pagos3	DECIMAL(1,0)	NOT NULL,
	num_orden	DECIMAL(6,0)	NOT NULL,
	PRIMARY KEY(id_empresa)
);

CREATE TYPE datos AS
(
	direccion	VARCHAR,
	telefono	DECIMAL(10,0),
	correo		VARCHAR
);

CREATE TABLE IF NOT EXISTS tab_dptos
(
	id_dpto		VARCHAR(2)	NOT NULL,
	nom_dpto	VARCHAR		NOT NULL,
	PRIMARY KEY(id_dpto)
);

CREATE TABLE IF NOT EXISTS tab_ciud
(
	id_ciud		VARCHAR(5) 		NOT NULL,
	nom_ciud	VARCHAR			NOT NULL,
	id_dpto		VARCHAR(2)		NOT NULL,
	PRIMARY KEY(id_ciud),
	FOREIGN KEY(id_dpto)	REFERENCES tab_dptos(id_dpto)
);

CREATE TABLE IF NOT EXISTS tab_prov
(
	id_prov		DECIMAL(10,0)	NOT NULL,
	nom_prov	VARCHAR			NOT NULL,
	ape_prov	VARCHAR			NOT NULL,
	nom_comp	VARCHAR			NOT NULL,
	datos_prov	datos,
	id_ciud		VARCHAR(5)		NOT NULL,
	val_cupocre	DECIMAL(10,0)	NOT NULL DEFAULT 0,
	val_puntos	DECIMAL(3,0)	NOT NULL,
	ind_estado	BOOLEAN			NOT NULL DEFAULT TRUE,
	PRIMARY KEY(id_prov),
	FOREIGN KEY(id_ciud)	REFERENCES tab_ciud(id_ciud)
);

CREATE TABLE IF NOT EXISTS tab_prod
(
	id_prod		SMALLINT		NOT NULL,
	id_ref		VARCHAR			NOT NULL,
	nom_prod	VARCHAR			NOT NULL,
	ind_estado	BOOLEAN			NOT NULL,
	PRIMARY KEY(id_prod)
);
CREATE UNIQUE INDEX idx_id_ref ON tab_prod(id_ref);

CREATE TABLE IF NOT EXISTS tab_prod_prov
(
	id_prov		DECIMAL(10,0)	NOT NULL,
	id_prod		SMALLINT		NOT NULL,
	val_prod	DECIMAL(10,0)	NOT NULL,
	ind_disp	BOOLEAN			NOT NULL,
	PRIMARY KEY(id_prov,id_prod),
	FOREIGN KEY(id_prov)	REFERENCES tab_prov(id_prov),
	FOREIGN KEY(id_prod)	REFERENCES tab_prod(id_prod)
);

CREATE TABLE tab_areas
(
	id_area		DECIMAL(2,0)	NOT NULL,
	nom_area	VARCHAR			NOT NULL,
	nom_ecarg	VARCHAR			NOT NULL,
	PRIMARY KEY(id_area)
);

CREATE TABLE IF NOT EXISTS tab_orden_compra
(
	id_orden	DECIMAL(6,0)	NOT NULL,
	fec_orden	TIMESTAMP WITHOUT TIME ZONE	NOT NULL,
	id_area		DECIMAL(2,0)	NOT NULL,
	id_prod		SMALLINT		NOT NULL,
	val_cant	SMALLINT		NOT NULL,
	PRIMARY KEY(id_orden),
	FOREIGN KEY(id_prod)	REFERENCES tab_prod(id_prod),
	FOREIGN KEY(id_area)	REFERENCES tab_areas(id_area)
);

CREATE TABLE IF NOT EXISTS tab_enc_compra
(
	id_factura	DECIMAL(6,0)	NOT NULL,
	fec_factura	DATE 			NOT NULL,
	id_ciud		VARCHAR(5)		NOT NULL,
	id_prov		DECIMAL(10,0)	NOT NULL,
	val_observa	TEXT,
	PRIMARY KEY(id_factura),
	FOREIGN KEY(id_ciud)	REFERENCES tab_ciud(id_ciud),
	FOREIGN KEY(id_prov)	REFERENCES tab_prov(id_prov)
);

CREATE TABLE IF NOT EXISTS tab_det_compra
(
	id_factura	DECIMAL(6,0)	NOT NULL,
	id_prod		SMALLINT		NOT NULL,
	val_cant	SMALLINT		NOT NULL,
	val_bruto	DECIMAL(10,0)	NOT NULL,
	val_descu	DECIMAL(10,0)	NOT NULL,
	val_iva		DECIMAL(10,0)	NOT NULL,
	val_neto	DECIMAL(10,0)	NOT NULL,
	PRIMARY KEY(id_factura,id_prod),
	FOREIGN KEY(id_factura)	REFERENCES tab_enc_compra(id_factura),
	FOREIGN KEY(id_prod)	REFERENCES tab_prod(id_prod)
);

CREATE TABLE IF NOT EXISTS tab_pagos
(
	id_factura	DECIMAL(6,0)	NOT NULL,
	id_pago		DECIMAL(2,0)	NOT NULL,
	fec_pago	DATE			NOT NULL,
	val_pago	DECIMAL(10,0)	NOT NULL,
	val_saldo	DECIMAL(10,0)	NOT NULL,
	PRIMARY KEY(id_factura,id_pago),
	FOREIGN KEY(id_factura)	REFERENCES tab_enc_compra(id_factura)
);