Usuari:Sergi/Temari/Tema 40 Diseño de relacionales.

De Acacha

Dreceres ràpides: navegació, cerca

Contingut

Introducció al model relacional

Vegeu Tema 38. Història del model de dades relacional. Model de Codd

Objectiu del disseny

La creació d'una base de dades és un procés complex que neix de la necessitat d'emmagatzemar informació sobre el món real. Un bon disseny de la base de dades té com objectiu optimitzar el rendiment de la base de dades (rapidesa i eficiència) i l'espai que ocupa la base de dades.

Els problemes que pot causar un mal disseny de la base de dades són:

  • Redundàncies: una base de dades mal dissenyada pot repetir més del necessari les dades emmagatzemades. L'exemple clar és el de una fitxer o full de càlcul on tenim un columna on sovint es repeteix la informació
  • Incoherència: les incoherències són estats de la base de dades que no es corresponen amb la realitat. Les redundàncies poden ser una font d'incoherències. P. ex., si tenim dades duplicades i les volem modificar ho hem de fer sobre totes les còpies o tindrem incoherències (i per un altre banda si ho fem sobre totes les còpies estem empobrint el rendiment de la base de dades)
  • Pèrdua de dependències funcionals:

Fases del disseny i l'arquitectura de 3 nivells ANSI/X3/SPARC

Seguint l'arquitectura de 3 nivells ANSI/X3/SPARC podem dividir les fases del disseny de base de dades en 3 (una per nivell):

  • Disseny conceptual: Aquest disseny és independent del model de base de dades utilitzat, de l'ordinador, del sistema operatiu i en general de qualsevol altre característica física de la base de dades. El model més utilitzat en aquesta fase és el model Entitat-Relació i els diagrames de Chen.
  • Disseny lògic: En aquesta fase del disseny tenim en compte el model de base de dades utilitzat. Per aquesta tema utilitzarem el model relacional i veurem com crear les taules (relacions) de la base de dades a partir del model conceptual E/R i també comptem amb la teoria de la normalització per tal de millorar les nostres bases de dades.
  • Disseny físic: El disseny a aquest nivell depèn del sistema gestor de base de dades relacionals (SGBDR) i per tant als desenvolupadors d'aquests sistemes. En aquesta fase és baixa al nivell del bit i no en tractarem els detalls en aquests tema.

Disseny conceptual. Model Entitat/Relació. Chen

Vegeu Tema 35. Model Entitat/Relació.

Transformació del model E/R en model relacional

Per fer aquesta transformació, els primers passos són:

  • Definició de les taules (relacions): Tant les Entitats com les Relacions del model de Chen es converteixen en taules.
  • Definició de claus: Les relacions del model de Chen tindran com a clau una clau composta com a mínim per la clau primària de cada una de les Entitats que relacionen
  • Definició d'atributs: Els atributs del model de Chen es transformen en camps.

Disseny de la persistència (base de dades relacional) d'un model orientat a objectes. UML

L'ús més habitual de les bases de dades és proporcionar persistència a els objectes d'una aplicació orientada a objectes (Oriented Object Programming/Paradigm (OOP)). La persistència és la propietat que permet recuperar la informació relacionada als objectes entre diferents execucions de l'aplicació. Les aplicacions en executar-se utilitzant la memòria principal del sistema, que com es sabut és una memòria no persistent (es perd la informació quan la memòria deixa de rebre energia elèctrica) i a més, els sistemes operatius alliberen els recursos de memòria utilitzats per una aplicació un cop finalitzada la mateixa.

La persistència també és aplicable a altres paradigmes de programació que no siguin el OOP (per exemple aplicacions desenvolupades amb un llenguatge de programació estructurat). Així mateix, la persistència es pot implementar mitjançant altres sistemes com l'ús de fitxers.

El cas de la programació orientada a objectes és un cas interessant perquè és un dels paradigmes més utilitzats en l'actualitat i a més és força fàcil establir un procés per a dissenyar una base de dades relacional a partir de les especificacions de disseny i l'anàlisi d'una aplicació orientada a objectes.

L'estàndard per a l'anàlisi d'aplicacions orientades a objecte és Universal/Uniform Modelling Language.

El més habitual per tal de crear la base de dades és partir del diagrama de classes de UML. Els passos a seguir per dissenyar una base de dades a partir d'un model orientat a objectes són:

  • Eliminació de l’herència: Les bases de dades relacionals no ofereixen cap tipus de suport per l’herència, per tant inclús l’herència simple s’ha d’eliminar de l’esquema. Normalment és crea una taula per cada objecte de la relació d'herència i les taules que representen les classes filla, han de tenir tant les dades específiques com les dades "heretades" del pare.
  • Obtenció del disseny de les taules: Per fer-ho cal aplicar un conjunt de regles. Aquestes regles són una adaptació de les regles per a la transformació de models E/R en bases de dades relacionals:
    • Conversió de cada classe a una taula relacional. Els atributs de la classe passen a ser camps de la taula. Totes les taules necessiten una clau primària, que és un atribut o conjunt d’atributs que permeten identificar cada fila (“objecte”) de la taula. Per exemple, el DNI (si suposem que no hi ha errors i per tant no hi ha DNIs repetits) podria ser un bona clau primària per una taula empleat. Si no trobem cap atribut o combinació d’atributs que ens faci el pes, es pot crear una clau artificial (afegint un nou camp “codi” a la taula).
    • Associacions de tipus 1:*: es tracta afegint a la taula que representa la classe del costat * el/s atribut/s que formen la clau primària de la taula que representa la classe del costat 1. Aquests atributs queden marcats com claus foranes. Una clau forana obliga a què el valor del nou atribut sigui vàlid, és a dir, que sigui un dels valors existents dins l’altra taula. L’altra taula queda inalterada ja que el model relacional no permet atributs amb valors múltiples (atributs de tipus vector).
  • Per cada associació *:* (relacions on ambdós extrems tenen una cardinalitat >1): es crea una nova taula que té com a atributs les claus primàries de les taules que representen les classes participants en la associació. Aquests atributs es marquen com a clau forana. La suma de tots ells es la clau primària de la nova taula.

De manera similar, cada associació n-ària es transforma en una nova taula amb n claus foranes. La clau primària es la suma de totes (o algunes) de les claus foranes. A vegades només amb la suma d’alguns dels atributs que fan de clau forana en tenim prou per identificar la taula, depèn de la cardinalitat de la associació en el disseny original.

Tot i que, com s’ha dit anteriorment, no es pretén proporcionar una visió exhaustiva de la transformació i per tant no es tindran en compte, calen altres consideracions per completar la transformació, com per exemple si els atributs poden tenir valors nuls o no (depèn de la cardinalitat mínima de les relacions), el tractament de les relacions 1:1 (que es poden tractar com si fossin 1:* o *:*) ...

Sintaxi:

Taules:

  • Ruta(codi,origen,destinacio,distancia)
  • Pilot(codi, nom, horesVol, categoria)
  • PilotRutes(codiPilot,codiRuta)
  • PlaVol(codi,data, horaSortida, durada, codiRuta, codiAvio)
    • {codiRuta} és clau forana a Ruta
    • {codiAvio} és clau forana a Avio
  • PlaVolPilots(codiPlaVol, codiPilot)
  • Avio(codi, model, autonomia, dataAlta, horesVol, codiMotor)
    • {codiMotor} és clau forana a Motor
  • Motor(codi, marca, potencia, model, consumMitja, horesfuncionament, tipusMotor)

Recursos:

Dissenys lògic. Teoria de la normalització. Formes normals

Un cop hem aplicat les fases inicials de creació del model lògic a partir del model conceptual (ja sigui si venim d'un diagrama de Chen com d'un diagrama de classes de UML) la següent fase és un fase de refinament en la que aplicarem la teoria de la normalització.

La teoria de la normalització esta basada en 5 regles (o formes normals) que s'han de seguir si es vol tenir una base de dades normalitzada. La normalització és una característica que permet evitar en gran mesura els problemes que es poden tenir a l'actualitza la base de dades per problemes de redundàncies. Cada fase de normalització elimina un tipus de redundància.

L'objectiu de la normalització és:

  • Evitar la redundància de les dades.
  • Evitar problemes d'actualització de les dades de les taules.
  • Protegir l'integritat de les dades.

En general, les 3 primeres formes normals són suficients per a cobrir les necessitats de la majoria de base de dades. Aquestes formes normals van ser proposades per Edgar F. Codd.

Recursos:

Problemes que soluciona la normalització. Anomàlies d'inserció, actualització i eliminació

An update anomaly. Employee 519 is shown as having different addresses on different records.
An insertion anomaly. Until the new faculty member is assigned to teach at least one course, his details cannot be recorded.
A deletion anomaly. All information about Dr. Giddens is lost when he temporarily ceases to be assigned to any courses.

A table that is not sufficiently normalized can suffer from logical inconsistencies of various types, and from anomalies involving data operations. In such a table:

  • The same information can be expressed on multiple records; therefore updates to the table may result in logical inconsistencies. For example, each record in an "Employees' Skills" table might contain an Employee ID, Employee Address, and Skill; thus a change of address for a particular employee will potentially need to be applied to multiple records (one for each of his skills). If the update is not carried through successfully—if, that is, the employee's address is updated on some records but not others—then the table is left in an inconsistent state. Specifically, the table provides conflicting answers to the question of what this particular employee's address is. This phenomenon is known as an update anomaly.
  • There are circumstances in which certain facts cannot be recorded at all. For example, each record in a "Faculty and Their Courses" table might contain a Faculty ID, Faculty Name, Faculty Hire Date, and Course Code—thus we can record the details of any faculty member who teaches at least one course, but we cannot record the details of a newly-hired faculty member who has not yet been assigned to teach any courses. This phenomenon is known as an insertion anomaly.
  • There are circumstances in which the deletion of data representing certain facts necessitates the deletion of data representing completely different facts. The "Faculty and Their Courses" table described in the previous example suffers from this type of anomaly, for if a faculty member temporarily ceases to be assigned to any courses, we must delete the last of the records on which that faculty member appears. This phenomenon is known as a deletion anomaly.

Ideally, a relational database table should be designed in such a way as to exclude the possibility of update, insertion, and deletion anomalies. The normal forms of relational database theory provide guidelines for deciding whether a particular design will be vulnerable to such anomalies. It is possible to correct an unnormalized design so as to make it adhere to the demands of the normal forms: this is called normalization. Removal of redundancies of the tables will lead to several tables, with referential integrity restrictions between them.

Normalization typically involves decomposing an unnormalized table into two or more tables that, were they to be combined (joined), would convey exactly the same information as the original table.

1a Forma Normal (1NF)

Es diu que una taula està en primera forma normal si no té camps multivaluats (més d'una valor).

Exemple:

Cliente
ID Cliente Nombre Apellido Teléfono
123 Rachel Ingram 555-861-2025
456 James Wright 555-403-1659
555-776-4100
789 Maria Fernandez 555-808-9633
Cliente
ID Cliente Nombre Apellido Teléfono
123 Rachel Ingram 555-861-2025
456 James Wright 555-403-1659
456 James Wright 555-776-4100
789 Maria Fernandez 555-808-9633

Recursos:

2a Forma Normal (2NF)

Diem que una relació (taula) està en segona forma normal, si està en primera forma normal i a més i tots els seus atributs depenen funcionalment de la seva clau primària.

Aquesta forma normal permet identificar possibles claus de la relació a partir de les quals escollir la clau primària i també permet detectar la necessitat de dividir una relació (taula) en múltiples relacions (taules). P. ex.:

Habilidades de los empleados
Empleado Habilidad Lugar actual de trabajo
Jones Mecanografía 114 Main Street
Jones Taquigrafía 114 Main Street
Jones Tallado 114 Main Street
Roberts Limpieza ligera 73 Industrial Way
Ellis Alquimia 73 Industrial Way
Ellis Malabarismo 73 Industrial Way
Harrison Limpieza ligera 73 Industrial Way

La clau candidata es "Empleado" però l'atribut lloc de treball no depèn únicament d'aquesta clau. Les redundàncies en el camp lloc de treball fan la taula vulnerable a anomàlies d'actualització. Per exemple podríem obtenir resultats contradictoris a la pregunta: "¿Quin és el lloc de treball de Jones?".

Un alternativa 2NF és:

Empleados
Empleado Lugar actual de trabajo
Jones 114 Main Street
Roberts 73 Industrial Way
Ellis 73 Industrial Way
Harrison 73 Industrial Way
Habilidades de los empleados
Empleado Habilidad
Jones Mecanografía
Jones Taquigrafía
Jones Tallado
Roberts Limpieza ligera
Ellis Alquimia
Ellis Malabarismo
Harrison Limpieza ligera

Recursos:

Dependències funcionals

La dependència funcional és un concepte que no es pot definir matemàticament i que només es pot establir coneixent la naturalesa d'un problema i estudiant-la. La dependència funcional estableix una relació semàntica entre dos elements.

Diem que B té una dependència funcional amb A si el valor de A determina, indica o implica el valor de B. Per exemple:

  • DNI: El DNI d'una persona determina el seu
    • Nom
    • Cognoms
    • Adreça...

3a Forma Normal (3NF)

Un relació esta en tercera forma normal si i només si:

  • La taula està en la segona forma normal (2NF)
  • Cap atribut no-primari de la taula és depenent transitivament de la clau candidata.

Aquesta forma normal permet evitar l'ús repetitiu d'espai i evita anomalies d'actualització. Veiem un exemple:

COMPTES
DNI BANC NOM_BANC SUCURSAL DC COMPTE
14275002BBVABanc Bilbao Vizcaia Argentaria 332121 0123401234
14275003BBVABanc Bilbao Vizcaia Argentaria 332250 0123401454
14275004BBVABanc Bilbao Vizcaia Argentaria 333134 0123401324
14275005BBVABanc Bilbao Vizcaia Argentaria 335125 0145401234

Podem convertir aquesta taula en dos taules en 3NF:

COMPTES
DNI BANC SUCURSAL DC COMPTE
14275002BBVA332121 0123401234
14275003BBVA332250 0123401454
14275004BBVA333134 0123401324
14275005BBVA335125 0145401234
COMPTES
BANC NOM_BANC
BBVABanc Bilbao Vizcaia Argentaria

Recursos:

Dependència funcional transitiva

Diem que un element C depèn transitivament de A si B depèn de A i C depèn de B. Veiem un exemple:

  • COMPTES(DNI, CODI_BANC, SUCURSAL, DC, NUMERO_COMPTE, NOM_BANC)

Tenim que:

DNI --> CODI_BANC, SUCURSAL, DC, NUMERO_COMPTE, CODI_BANC --> BANC

Forma Normal de Boyce-Codd (FNBC)

Recursos:

4a Forma Normal (4NF)

La majoria de taules 3NF estàn lliures d'anomalies d'actualització, inserció, i esborrat.

Només alguns tipus de taules 3NF, que en la pràctica rarament es troben, són afectades per aquestes anomalies;

Recursos:

5a Forma Normal (5NF)

Molts autors consideren que no és necessari arribar a aquest nivell de normalització i que fins i tot en alguns casos pot ser contraproduent.

Recursos:

Integritat referencial

Eines de l'usuari