Justificacion del Diseno de la Base de Datos - Pillam Ltd. Co.#

Diagrama del modelo de datos

1. Tabla `escola`#

Por que existe?#

El enunciado (seccion 2) define las Escoles como entidad independiente que agrupa sectores y vias. El requisito “No poden existir enlloc dues Escoles amb el mateix nom” exige una tabla dedicada con unicidad en el nombre.

Campos#

Campo	Tipo	Justificacion
id (PK)	SERIAL	ID numerico auto-incremental, mas eficiente que usar el nombre como PK para FK y joins.
nom (NN, UNIQUE)	TEXT	NOT NULL y UNIQUE porque no pueden existir dos escuelas con el mismo nombre.
lloc	TEXT	”Poblacio” donde se encuentra la escuela. El enunciado lo incluye como atributo propio.
aproximacio	TEXT	”Breu descripcio de com arribar”. Texto libre.
nombre_vies	INTEGER	”Numero total de vies”. Entero = cantidad.
popularitat	TEXT	Tres valores: baixa, mitjana, alta. CHECK constraint en la BD.

Campos excluidos#

Coordenades: No aparece en el enunciado para escoles. Solo los sectores tienen ubicacion geografica; la escuela es un concepto de agrupacion.
Restriccions: No aparece en el enunciado para escoles. Las restricciones se aplican a nivel de sector y de via.

2. Tabla `sector`#

Por que existe?#

El enunciado (seccion 3) define los Sectors como entidad separada: “Cada Escola tindra 1 o varis Sectors”. Son subdivisiones fisicas de una escuela (ej: Montserrat -> Vinya Nova, Collbato, Can Jorba). Tabla necesaria para:

Almacenar coordenadas geograficas (solo aplican a sectores).
Garantizar nombres unicos dentro de cada escuela.
Tener restricciones y popularidad propias por sector.

Campos#

Campo	Tipo	Justificacion
id (PK)	SERIAL	El nombre no es unico globalmente (dos escuelas podrian tener un sector “Nord”), necesitamos ID propio.
nom (NN)	TEXT	Obligatorio. La combinacion (nom + escola_id) debe ser unica (restriccion del enunciado).
latitud	DECIMAL	”Coordenades: (Latitud, Longitud)” solo para sectores. DECIMAL por precision.
longitud	DECIMAL	Complementa latitud para la ubicacion geografica.
aproximacio	TEXT	”Breu descripcio de com arribar”. Cada sector puede tener acceso diferente.
nombre_vies	INTEGER	”Numero total de vies” dentro del sector.
popularitat	TEXT	Mismos valores que escola (baixa/mitjana/alta) con CHECK. Puede diferir de su escuela.
restriccions	TEXT	Restricciones propias (ej: sector cerrado en invierno por nidificacion).
escola_id (FK)	INTEGER	N:1 con escola. Cada sector pertenece a exactamente una escuela.

Restriccion de tipos de via por sector#

El enunciado especifica: “Els sectors de Gel tenen NOMES vies de Gel. No trobarem sectors que combinin vies de Gel amb Classica o Esportiva, pero si que podem trobar sectors que combinin Classica i Esportiva.” Esta regla se controla a nivel de aplicacion al insertar/modificar vias.

Por que N:1?#

Un sector pertenece a una sola escuela. No tiene sentido que “Vinya Nova” pertenezca a dos escuelas.

3. Tabla `via`#

Por que existe?#

Las Vies son la entidad central (secciones 1.1, 1.2, 1.3). Los tres tipos (esportiva, classica, gel) comparten casi todos los atributos, por eso usamos una sola tabla con campo tipus en vez de tres tablas:

Campos casi identicos -> evitamos duplicacion.
Consultas como “cercar vies per dificultat” no requieren UNION.
Las diferencias (anclajes, rango de longitud) se gestionan con CHECK o a nivel de aplicacion.

Campos#

Campo	Tipo	Justificacion
id (PK)	SERIAL	Identificador unico.
nom	TEXT	Cada via tiene “nom propi”. Los largos individuales no tienen nombre, la via si.
llargada	INTEGER	5-30m (deportivas), 15-300m (clasicas/gel). Para clasicas/gel es la longitud total; el detalle por largo va en tabla `llarg`.
grau_dificultat	TEXT	Escala: 4, 4+, 5, 5+, 6a…9c+. TEXT por combinar numeros y letras. El enunciado recomienda validar con expresion regular.
orientacio	TEXT	N, NE, NO, SE, SO, E, O, S. El enunciado recomienda validar con expresion regular. Tambien se aplica CHECK constraint por ser valores cerrados.
estat	TEXT	Apte, Construccio, Tancada con CHECK. Al pasar a Construccio/Tancada se registra el periodo de inicio y fin.
data_inici_estat	TIMESTAMP	Registra desde cuando esta en el estado actual. Al pasar a Construccio o Tancada se rellena con la fecha de inicio.
data_fi_estat	TIMESTAMP	Registra hasta cuando dura el estado. Permite saber cuando se espera que vuelva a Apte. NULL si el estado es indefinido o ya esta en Apte.
tipus	TEXT	esportiva, classica, gel con CHECK. Permite filtrar y aplicar reglas por tipo.
ancoratge	TEXT	”Cada via te un sol tipus d’ancoratge”. Deportivas: punts, parabolics, quimics. Clasicas/gel: + friends, tascons, bagues, pitons, Tricams, BigBros.
tipus_roca	TEXT	”Conglomerat, granit, calcaria, arenisca, altres”. Cada via tiene un solo tipo de roca. Presente en los tres tipos de via.
restriccions	TEXT	”Prohibicions durant certes epoques de l’any, per exemple per nidificacio d’aus”. Cada via puede tener sus propias restricciones.
sector_id (FK)	INTEGER	N:1 con sector. “Cada Sector tindra 1 o varies vies”.
creada_per_id (FK)	INTEGER	N:1 con escalador. “L’escalador ha d’apareixer a la BDD o sino donar d’alta”.

Campos excluidos#

Coordenades: No aparece en el enunciado para vias. Las vias heredan ubicacion del sector.

Restriccion de nombre unico por escola#

El enunciado dice: “Dins d’una Escola, no poden haver-hi noms de vies repetits”. Dos vias de la misma escuela (aunque esten en sectores diferentes) no pueden tener el mismo nombre.

Esta restriccion no se puede implementar con un UNIQUE constraint simple en la BD porque via solo tiene sector_id y no escola_id directamente. Para aplicarla en la BD habria que denormalizar anadiendo escola_id a via, lo que seria redundante con la cadena via->sector->escola. Por tanto, se controla a nivel de aplicacion: antes de insertar o renombrar una via, se comprueba que no exista otra con el mismo nombre en la misma escuela.

Por que apunta a sector y no a escola?#

Jerarquia: Escola -> Sector -> Via. El sector ya indica la escuela; poner escola_id en via seria redundante.

4. Tabla `llarg`#

Por que existe?#

Las vias clasicas se componen de “diversos llargs (L1, L2, L3…)” que pueden superar 50m. Las de gel tambien tienen tramos. Tabla separada porque:

Numero variable de largos por via (1, 2, 5, 10…).
Cada largo tiene su propia longitud.
Relacion 1 que no cabe en un solo campo.

Las vias deportivas NO tienen largos (un solo tramo de 5-30m).

Campos#

Campo	Tipo	Justificacion
id (PK)	SERIAL	Identificador unico.
numero	INTEGER	Numero del largo (1, 2, 3… = L1, L2, L3). Permite ordenar secuencialmente.
llargada	INTEGER	”La Llargada s’haura de definir per a cada Llarg (Tram)”. Cada largo tiene su longitud.
via_id (FK)	INTEGER	N:1 con via. Cada largo pertenece a una sola via.

5. Tabla `escalador`#

Por que existe?#

El enunciado (seccion 4) define los Escaladors como entidad propia, referenciados desde via.creada_per_id y necesarios para el CRUD que pide el enunciado.

Campos#

Campo	Tipo	Justificacion
id (PK)	SERIAL	Identificador unico.
nom	TEXT	”Nom de l’escalador”.
alies	TEXT	”Alies de l’escalador”. Apodo/nickname.
edat	INTEGER	”Edat de l’escalador”. Entero = anos.
nivell	TEXT	”Nivell maxim que ha assolit, de 4 a 9c+”. TEXT por formato numeros + letras.
estil_preferit	TEXT	Tres opciones: esportiva, classica, gel. Se puede aplicar CHECK como en `via.tipus`.
nivell	TEXT	Nivel maximo alcanzado (4 a 9c+). Es un valor calculado a partir de la tabla `ascensio`: la via con mayor grau_dificultat que ha realizado el escalador. Si hay empate de grado, se coge la primera en fecha. Se puede mantener desnormalizado para eficiencia o calcularse en cada consulta.

Campos excluidos#

via_maxim_id: eliminado. Con la tabla ascensio se puede derivar la via del nivel maximo mediante consulta (mayor grau_dificultat; en empate, la de menor data_ascensio).
Historial: marcado como PENDENT en el enunciado. No se implementa en esta version.

6. Tabla `ascensio`#

Por que existe?#

Sustituye al campo via_maxim_id de escalador. En lugar de guardar solo la via del nivel maximo, registramos todas las vias que ha realizado un escalador con su fecha. Con esto podemos calcular:

Nivel maximo: via con mayor grau_dificultat; en caso de empate, la de menor data_ascensio (la primera que hizo).
Primera via escalada: la ascension con menor data_ascensio.
Historial completo: todas las ascensiones ordenadas por fecha.

Es una relacion N entre escalador y via: un escalador puede escalar muchas vias, y una via puede ser escalada por muchos escaladores.

Campos#

Campo	Tipo	Justificacion
id (PK)	SERIAL	Identificador unico de la ascension.
escalador_id (FK)	INTEGER	N:1 con escalador. A que escalador pertenece esta ascension.
via_id (FK)	INTEGER	N:1 con via. Que via se ha escalado.
data_ascensio	DATE	Fecha en que se realizo la ascension. Clave para determinar cual fue la primera via en caso de empate de grado. DATE (sin hora) es suficiente.

Resumen de Relaciones#

Relacion	Tipo	Justificacion
sector.escola_id -> escola.id	N:1	”Cada Escola tindra 1 o varis Sectors”. Un sector pertenece a una escuela.
via.sector_id -> sector.id	N:1	”Cada Sector tindra 1 o varies vies”. Una via pertenece a un sector.
via.creada_per_id -> escalador.id	N:1	El creador debe existir en la BD. Un escalador puede crear multiples vias.
llarg.via_id -> via.id	N:1	Vias clasicas/gel tienen multiples largos. Cada largo pertenece a una via.
ascensio.escalador_id -> escalador.id	N:1	Un escalador tiene multiples ascensiones registradas.
ascensio.via_id -> via.id	N:1	Una via puede haber sido escalada por multiples escaladores.

Jerarquia del Modelo#

1
Escola (1) ---> (N) Sector (1) ---> (N) Via (1) ---> (N) Llarg
2
                                     |
3
                                     | creada_per
4
                                     v
5
                                  Escalador
6
                                     |
7
                                     | via_maxim
8
                                     v
9
                                    Via

Decisiones de Diseno Clave#

Una sola tabla via para los 3 tipos: Atributos casi identicos + consultas sin UNION. Las diferencias se gestionan con tipus y logica de aplicacion.
Tabla llarg separada: Numero variable de tramos con longitud propia. Relacion 1 que no cabe en un campo.
Coordenadas solo en sector: No aparecen en escola ni via. La escuela agrupa; la via hereda ubicacion del sector.
creada_per_id como FK: El enunciado exige que el escalador creador exista en la BD -> integridad referencial.
Validacion con regex recomendada: El enunciado recomienda expresion regular para grau_dificultat y orientacio. Para orientacio ademas aplicamos CHECK por ser valores cerrados. Para grau_dificultat la validacion se hace a nivel de aplicacion por su rango extenso (4…9c+).
Restriccion de tipos por sector: Los sectores de Gel solo contienen vias de Gel. Classica y Esportiva pueden coexistir en un sector, pero no mezclarse con Gel. Se controla a nivel de aplicacion.

SQL de Creacion de Tablas#

1
-- =========================
2
-- ESCOLA
3
-- =========================
4
CREATE TABLE escola (
5
    id SERIAL PRIMARY KEY,
6
    nom TEXT UNIQUE NOT NULL,
7
    lloc TEXT,
8
    aproximacio TEXT,
9
    nombre_vies INTEGER,
10
    popularitat TEXT CHECK (popularitat IN ('baixa', 'mitjana', 'alta'))
11
);
12

13
-- =========================
14
-- SECTOR
15
-- =========================
16
CREATE TABLE sector (
17
    id SERIAL PRIMARY KEY,
18
    nom TEXT NOT NULL,
19
    latitud DECIMAL,
20
    longitud DECIMAL,
21
    aproximacio TEXT,
22
    nombre_vies INTEGER,
23
    popularitat TEXT CHECK (popularitat IN ('baixa', 'mitjana', 'alta')),
24
    restriccions TEXT,
25
    escola_id INTEGER,
26
    CONSTRAINT fk_sector_escola
27
        FOREIGN KEY (escola_id)
28
        REFERENCES escola(id)
29
        ON DELETE CASCADE
30
);
31

32
-- =========================
33
-- ESCALADOR
34
-- =========================
35
CREATE TABLE escalador (
36
    id SERIAL PRIMARY KEY,
37
    nom TEXT NOT NULL,
38
    alies TEXT,
39
    edat INTEGER,
40
    nivell TEXT,
41
    estil_preferit TEXT
42
);
43

44
-- =========================
45
-- VIA
46
-- =========================
47
CREATE TABLE via (
48
    id SERIAL PRIMARY KEY,
49
    nom TEXT NOT NULL,
50
    llargada INTEGER,
51
    grau_dificultat TEXT,
52
    orientacio TEXT CHECK (orientacio IN ('N','NE','NO','SE','SO','E','O','S')),
53
    estat TEXT CHECK (estat IN ('Apte','Construccio','Tancada')),
54
    data_inici_estat TIMESTAMP,
55
    data_fi_estat TIMESTAMP,
56
    tipus TEXT CHECK (tipus IN ('esportiva','classica','gel')),
57
    ancoratge TEXT,
58
    tipus_roca TEXT CHECK (tipus_roca IN ('conglomerat','granit','calcaria','arenisca','altres')),
59
    restriccions TEXT,
60
    sector_id INTEGER,
61
    creada_per_id INTEGER,
62
    CONSTRAINT fk_via_sector
63
        FOREIGN KEY (sector_id)
64
        REFERENCES sector(id)
65
        ON DELETE CASCADE,
66
    CONSTRAINT fk_via_escalador
67
        FOREIGN KEY (creada_per_id)
68
        REFERENCES escalador(id)
69
        ON DELETE SET NULL
70
);
71

72
-- =========================
73
-- LLARG
74
-- =========================
75
CREATE TABLE llarg (
76
    id SERIAL PRIMARY KEY,
77
    numero INTEGER,
78
    llargada INTEGER,
79
    via_id INTEGER,
80
    CONSTRAINT fk_llarg_via
81
        FOREIGN KEY (via_id)
82
        REFERENCES via(id)
83
        ON DELETE CASCADE
84
);
85

86
-- =========================
87
-- ASCENSIO
88
-- =========================
89
CREATE TABLE ascensio (
90
    id SERIAL PRIMARY KEY,
91
    escalador_id INTEGER NOT NULL,
92
    via_id INTEGER NOT NULL,
93
    data_ascensio DATE NOT NULL,
94
    CONSTRAINT fk_ascensio_escalador
95
        FOREIGN KEY (escalador_id)
96
        REFERENCES escalador(id)
97
        ON DELETE CASCADE,
98
    CONSTRAINT fk_ascensio_via
99
        FOREIGN KEY (via_id)
100
        REFERENCES via(id)
101
        ON DELETE CASCADE
102
);

Notas sobre el SQL#

Sin dependencia circular: al eliminar via_maxim_id de escalador, la dependencia circular desaparece. El orden de creacion es: escola -> sector -> escalador -> via -> llarg -> ascensio.
CHECK constraints: En popularitat, orientacio, estat, tipus y tipus_roca para restringir valores en BD.
ON DELETE CASCADE: Refleja la jerarquia Escola -> Sector -> Via -> Llarg. Borrar un padre borra sus hijos.
ON DELETE SET NULL: En via.creada_per_id. No queremos borrar una via porque se elimine su creador.
ON DELETE CASCADE en ascensio: Si se borra un escalador o una via, sus ascensiones asociadas se borran tambien.
Restriccion sector-gel: Los sectores de Gel no mezclan con Classica/Esportiva. Se controla a nivel de aplicacion al insertar/modificar vias.

Justificacion del Diseno de la Base de Datos - Pillam Ltd. Co.#

1. Tabla escola#

Por que existe?#

Campos#

Campos excluidos#

2. Tabla sector#

Por que existe?#

Campos#

Restriccion de tipos de via por sector#

Por que N:1?#

3. Tabla via#

Por que existe?#

Campos#

Campos excluidos#

Restriccion de nombre unico por escola#

Por que apunta a sector y no a escola?#

4. Tabla llarg#

Por que existe?#

Campos#

5. Tabla escalador#

Por que existe?#

Campos#

Campos excluidos#

6. Tabla ascensio#

Por que existe?#

Campos#

Resumen de Relaciones#

Jerarquia del Modelo#

Decisiones de Diseno Clave#

SQL de Creacion de Tablas#

Notas sobre el SQL#

1. Tabla `escola`#

2. Tabla `sector`#

3. Tabla `via`#

4. Tabla `llarg`#

5. Tabla `escalador`#

6. Tabla `ascensio`#