Model Relacional

• Model relacional
• Model Entitat-Relació
  • Cardinalitat
  • Generalització
    • Implementació a SQL
  • Agregació
  • Transformació a SQL
• Claus primàries (PK)
• Claus externes (FK)
• Formes normals
  • 1NF
  • 2NF
  • 3NF
• Bones pràctiques
• Referències

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ó

Algunes entitats poden relacionar-se com a una generalització (is-a), on una entitat genèrica comparteix atributs o relacions amb variants més específiques:

• Supertipus: el tipus genèric (pare).
• Subtipus: un subgrup d’entitats que comparteixen atributs comuns o relacions diferents d’altres subgrups.

S’utilitza quan alguns atributs només apliquen a certs subtipus, o bé una relació només existeix per a un subtipus concret. Per exemple, Vehicle com a supertipus, amb Cotxe i Moto com a subtipus que comparteixen atributs comuns (matrícula, propietari) però tenen atributs específics diferents.

Implementació a SQL

Hi ha tres estratègies habituals per a transformar una generalització a taules:

• Taula per classe (class table inheritance): una taula per al supertipus amb els atributs comuns, i una taula per a cada subtipus amb els atributs específics. La PK del subtipus és també una FK cap al supertipus, de manera que les PK coincideixen. Una fila completa s’obté amb un JOIN. És la més fidel al model ER, però penalitza les consultes.
• Taula única (single table inheritance): una sola taula amb totes les columnes de tots els subtipus, més una columna discriminadora que indica el subtipus. Les columnes específiques d’un subtipus són nullables per a les files d’altres subtipus. Consultes ràpides, però moltes files amb molts NULL.
• Taula per subtipus concret (concrete table inheritance): una taula independent per a cada subtipus, que duplica els atributs comuns. No hi ha taula pare, i les PK no es comparteixen entre subtipus. Evita JOINs, però duplica l’esquema i complica les consultes que agreguen tots els subtipus.

Agregació

L’agregació és un concepte del model ER que permet tractar una relació (amb les entitats que hi participen) com si fos una entitat de nivell superior, per tal que una altra relació hi pugui connectar. S’utilitza quan una relació ha de participar, al seu torn, en una altra relació.

Per exemple: si modelem que un Empleat treballa_en un Projecte, i volem dir que aquesta participació és supervisada_per un Manager, no podem connectar el Manager directament a la relació treballa_en. Amb agregació, tractem (Empleat, treballa_en, Projecte) com una unitat agregada amb la qual el Manager es pot relacionar.

Nota terminològica: a altres contexts (com UML), agregació té un significat diferent, el de part-of o composició, que descriu que una entitat forma part d’una altra. Aquí no ens referim a aquest sentit.

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 dues restriccions UNIQUE amb les altres dues parelles.
  • 1⇔1⇔N: la PK són les dues entitats del costat “1” (ja que cada parella d’aquestes determina una sola N). Cal també una restricció UNIQUE que inclogui l’entitat N amb una de les altres, per garantir la cardinalitat.
  • 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, com que no tenen cap significat al domini, poden amagar l’absència d’una clau natural i permetre que s’insereixin files duplicades a nivell de negoci: cal afegir explícitament una restricció UNIQUE sobre els atributs que realment identifiquen l’entitat. També 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 (com BCNF, que refina 3NF per a casos amb claus candidates solapades, o 4NF i 5NF, que tracten dependències multivalor i de join), 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, habitualment descrit com “atòmic” (tot i que aquest terme és discutit a la literatura, ja que l’atomicitat depèn del domini: una data o un text ja són compostos segons com es miri). A més, no pot haver grups repetits d’atributs, que són atributs anomenats amb un sufix numèric i amb la mateixa funció.

Solució: crear una taula amb el conjunt de valors com files.

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.

• student-course table:

• course table:

• student table:

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.

• course table:

• instructor table:

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: el comportament de la integritat referencial en aquests casos pot ser sorprenent i depèn del SGBD. Millor evitar-ho.

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

• Database normalization
• Database guide
• Surrogate key vs Natural key
• SQL style guide
• The realities of the relational database model
• M2 - Bases de dades
• When (and How) to Use Surrogate Keys
• Database Modeling and Design