Link Cerca Menu Expand Document
jgregor5.gitlab.io

Model relacional

Model relacional

El model relacional permet a un dissenyador de bases de dades crear una representació lògica i consistent de la informació:

  • La informació s’estructura mitjançant taules.
  • Cada taula es modela amb diversos atributs.
  • Les taules contenen files, que tenen valors per cadascun dels atributs.
  • Un possible valor d’un atribut és NULL, el no-valor.
  • Les files no poden repetir-se.
  • Els atributs que identifiquen una fila conformen la clau primària.
  • Les taules es relacionen utilitzant claus externes, que referencien atributs d’altres taules. Es diu que la taula amb la clau externa depèn de l’altra, i se solen anomenar taules filla i pare.

La consistència del model s’aconsegueix utilitzant restriccions (constraints), una forma de restringir el domini d’un atribut o implementar regles de negoci.

Hi ha dos tipus d’integritat al model relacional:

  • La integritat de l’entitat: cada fila d’una taula té una clau primària única i no nul·la que l’identifica, o sigui, cada fila representa una única instància d’un tipus d’entitat modelada per la taula.
  • La integritat referencial: si el valor d’un atribut referencia el valor d’una altra taula, llavors el valor referenciat ha d’existir.

Les principals restriccions del model relacional són:

  • La clau primària (PK): un conjunt d’atributs que identifiquen de forma única una fila. No permeten repeticions.
  • La clau externa (FK): un conjunt d’atributs que referencien la clau primària d’una altra taula. No permeten referències a files no existents.
  • Els índexs únics: indiquen que un índex no permet que hi hagi elements repetits.
  • Les comprovacions (checks): permeten afegir regles per als atributs d’una taula que imposen regles sobre les files.

Model Entitat-Relació

El model entitat relació ens permet modelar el món real utilitzant dos conceptes: les entitats i les relacions:

  • Entitats, una cosa que existeix al món real i es pot identificar i distingir de la resta. Són instàncies d’un tipus d’entitat o categoria, és a dir, un valor concret. Tenen atributs que les identifiquen i les descriuen. Són substantius.
  • Relacions, que expliquen com es relacionen les entitats. Poden ser verbs (o participis), i representen accions o processos entre entitats. Poden tenir atributs per a afegir informació addicional.

Segons la forma d’identificar una entitat, tenim dos tipus:

  • Fortes: no depenen de cap altra, i tenen el seu identificador únic.
  • Febles: depenen d’una entitat forta per poder ser identificades. Per tant, el seu identificador inclou el de l’entitat forta i un o més atributs addicionals.

Cardinalitat

Els tres tipus de relacions binàries, segons la cardinalitat, són:

  • one-to-one: 1⇔1
  • one-to-many: 1⇔N
  • many-to-many: M⇔N

I les ternàries:

  • one-to-one-to-one: 1⇔1⇔1
  • one-to-one-to-many: 1⇔1⇔N
  • one-to-many-to-many: 1⇔M⇔N
  • many-to-many-to-many: M⇔N⇔P

Generalització i agregació

Algunes entitats poden relacionar-se com a una generalització (is-a):

  • Supertipus: un tipus genèric (pare) que té una relació amb un o més subtipus (fills).
  • Subtipus: un subgrup d’entitats que comparteixen atributs comuns o relacions diferents d’altres subgrups.

Les PK dels supertipus i els subtipus són les mateixes.

Aquesta relació s’utilitza quan alguns atributs només apliquen a certs subtipus, o bé només existeix la relació per a cert subtipus o supertipus.

En canvi, l’agregació és una relació entre un supertipus i un o més subtipus del tipus part-of. A diferència de la generalització, no hi ha atributs heretats.

Transformació a SQL

Abans d’explicar el procés de transformació a partir del model, Cal explicar el concepte de taula associativa. Les taules associatives són una construcció que permet associar dues o més entitats. Per exemple, resol relacions many-to-many creant dos o més relacions one-to-many.

Per a convertir un diagrama ER en taules podem seguir la següent estratègia:

  • Per a transformar les entitats:
    • Identificar la clau primària de cada entitat.
    • Crear una taula per a cada entitat.
    • Si un atribut és una FK, crear la restricció corresponent.
  • Per a transformar les relacions binàries:
    • Identificar les entitats que participen i la seva cardinalitat.
    • Si la relació és many-to-many o té atributs, cal crear una taula associativa.
    • En cas contrari no cal crear cap taula, només afegir un FK per cada relació.
  • Per a transformar les relacions ternàries, crear una taula associativa i:
    • 1⇔1⇔1: una PK amb una parella i dos restriccions unique amb les altres dues parelles.
    • 1⇔1⇔N: una PK que inclou l’entitat N i una restricció unique incloent l’altra parella amb N.
    • 1⇔M⇔N: una PK amb les entitats M i N.
    • M⇔N⇔P: una PK amb les tres entitats.

Claus primàries (PK)

Les files d’una taula tenen atributs.

Una superclau és un conjunt d’atributs que identifica de forma única la fila d’una columna. O sigui, no hi ha més d’una fila amb aquest conjunt d’atributs.

Una superclau no és necessàriament un conjunt mínim. Per exemple, la superclau trivial és la de tots els atributs d’una fila. Si anem traient atributs a la superclau fins que no sigui possible treure’n més, llavors tenim un conjunt mínim, o clau candidata, o simplement clau. Si la clau candidata té més d’un atribut es diu que és composta.

Els atributs d’una clau candidata són els atributs principals. Un atribut que no es troba a cap clau candidata és un atribut no principal.

A una taula pot haver-hi més d’una clau candidata. La clau primària és la clau candidata que s’escull formalment al model relacional per a una certa taula. La resta de claus candidates es diuen claus alternatives.

Una clau pot utilitzar atributs existents al món real, i llavors es diu clau natural. Quan només s’utilitza un atribut com a clau, però no té correspondència fora del model relacional, li diem clau substituta. Habitualment són generades automàticament pel SGBD com seqüències numèriques.

Les claus substitutes tenen pros i contres respecte de les naturals:

  • Les naturals poden utilitzar-se per cerques, i no requereixen espai addicional de disc. Però si canvien les especificacions, afecten el disseny. També són més complicades i lentes si tenen més d’un atribut.
  • Les substitutes resolen els problemes de les naturals, però tenen els problemes que resolen les naturals. A més, trenquen la 3NF, ja que no tenen cap relació amb la fila, i s’implementen de forma diferent segons el SGBD.

Claus externes (FK)

Una clau externa és un conjunt d’atributs d’una taula que fan referència a la clau primària d’una altra taula. La primera es diu taula filla, i la segona, taula pare.

A un SGBD relacional s’espera que hi hagi integritat referencial: si un atribut o atributs es declaren com a clau externa, només poden contenir NULL o bé referir-se a valors existents de la clau primària de la taula pare.

Quan una fila s’actualitza o s’esborra, el SGDB ha de continuar garantint la integritat referencial. Les accions referencials que es poden definir a un fill són:

  • CASCADE: el canvi es transmet des del pare al fill.
  • RESTRICT o NO ACTION: no permet el canvi en el pare. Opció per defecte si s’omet, habitualment.
  • SET NULL: els valors dels atributs que fan la referència es canvien a NULL.
  • SET DEFAULT: els valors dels atributs que fan la referència es canvien al valor per defecte.

Formes normals

La normalització s’aplica al disseny relacional per a poder evitar anomalies quan s’insereix, s’esborra o actualitza una fila.

Les formes normals es comproven de forma incremental: 2NF requereix 1NF, 3NF requereix 2NF. Hi ha més formes normals, però les tres primeres ja permeten evitar els problemes habituals associats a un mal disseny.

1NF

Per complir 1NF, cada atribut d’una taula ha de tenir un sol valor (atòmic). A més, no pot haver grups repetits d’atributs, que són atributs anomenats amb un sufix numèric i amb la mateix funció.

Solució: crear una taula amb el conjunt de valors com files, i que cada fila tingui una FK cap a la taula original.

2NF

Introduïm el concepte de dependència funcional. Un atribut B és dependent (funcionalment) d’un altre A si a partir d’A obtenim un sol B. A és el determinant i B el depenent, i s’escriu: A ⇨ B.

Això és el que passa habitualment entre una clau i un atribut no principal: l’atribut no principal està determinat per la clau.

La 2NF es dirigeix a claus que tenen més d’un atribut. Per complir-la, cal complir 1NF i, a més, que cada atribut no principal (que no estigui a la clau candidata) depengui funcionalment de tota la clau, no només d’una part.

Solució: si un atribut no principal depèn d’una part, cal moure’l a una taula nova on aparegui només aquesta part.

3NF

Introduïm el concepte de dependència transitiva. Si C depèn de B i B de A, llavors C depèn (transitivament) de A. O sigui: si B ⇨ C i A ⇨ B, llavors A ⇨ C.

Per complir 3NF, cal complir 2NF i, a més, que no hi hagi cap atribut no principal que depengui transitivament de la clau primària.

Solució: crear dues taules sense dependències transitives. A cada una hi ha la dependència B de A i a l’altra C de B, respectivament.

Bones pràctiques

Sobre com anomenar:

  • Utilitzar minúscules i subratllats per a separar paraules.
  • Hi ha dues pràctiques per a anomenar taules: utilitzar singular o plural. Preferiblement, noms col·lectius o plurals.
  • Els atributs sempre són singulars.
  • No passa res si dues taules tenen atributs amb el mateix nom.
  • Identificar els atributs que contenen les PK i FK i utilitzar un sufix. Per exemple, nom de la taula més _id.

Sobre integritat:

  • Utilitzar sempre restriccions en lloc de fer comprovacions al codi.
  • Preferir entitats fortes a febles. Simplifiquen el disseny i generen consultes més òptimes.
  • En general, no definir atributs que siguin derivats d’altres.
  • Evitar sempre que sigui possible els atributs nullables. Estratègies:
    • Utilitzar una relació one-to-one opcional.
    • Utilitzar el valor per defecte a la definició de l’atribut.
  • Definir com a no nullable aquells atributs que no puguin ser NULL.
  • Cal pensar que pot haver-hi múltiples connexions concurrents incidint sobre les mateixes files. Per tant, cal utilitzar transaccions sempre que calgui que un conjunt de comandes es facin totes o cap.

Sobre claus primàries i externes:

  • Si la PK no és substituta, millor que sigui immutable o molt estable.
  • És millor no implicar molts atributs a la PK. Fa perdre estabilitat. Potser hi ha una clau amb menys camps, o és millor utilitzar una clau substituta.
  • Si la PK pot canviar, cal utilitzar ON UPDATE CASCADE al FK per rebre els canvis. Això no cal amb claus substitutes, ja que no canvien.
  • Compte amb ON DELETE CASCADE. És preferible esborrar explícitament les files, i que si hi ha un problema d’integritat la restricció no permeti l’operació. Podria tenir sentit utilitzar-ho amb entitats febles.
  • ON UPDATE SET NULL/DEFAULT tenen poc sentit, només en tenen pel DELETE, i només si la taula filla és una entitat forta.
  • És un problema tenir una FK amb diversos atributs on alguns poden ser NULL. En general, una FK és NULL si qualsevol part ho és.

Sobre optimització, modelar pensant en les consultes que realitzarà l’aplicació sobre la base de dades. Això pot tenir una incidència sobre l’esquema i sobre els índexs, per exemple:

  • Cercar sempre sobre camps que estan indexats.
  • Afegir índexs sobre els atributs dels joins. No cal per a les PK, s’indexen per defecte.

Referències