Bases de dades relacionals i SQL 15 h .

1. Bases de dades relacionals i SQL15 h. Màster en Teledetecció i Sistemes d’Informació Geogràfica Curs 2013-2014. 15ª edició Professor: Jordi Vayreda Duran E-mail: j.vayreda@creaf.uab.cat

2. Programa de l’assignatura Dimecres 8 de gener (1r dia, 17-19h, 2 hores) • Introducció a les bases de dades relacionals • Disseny conceptual d’una base de dades relacional: model entitat-relació • Fonaments de bases de dades relacionals • Entitats, atributs, instàncies • Claus primàries i claus foranes • Tipus de relacions i classificació • Lògica trivaluada • Disseny lògic d’una base de dades • Disseny físic d’una base de dades (normalització) • Avantatges d’una base de dades relacional:integritat d'entitats i integritat referencial • Definició d’una BD relacional: les 12 regles de Codd.

3. Programa de l’assignatura Dimecres 8 de gener (1r dia, 19-21) • La base de dades d’exemple: IEFC_Garrotxa.mdb • l’Inventari Ecològic i Forestal de Catalunya (IEFC de la Garrotxa) • Model conceptual, lògic i físic de la base de dades de l'IEFC • Característiques d’un Sistema Gestor de Bases de Dades (SGDB) • Com podem gestionar una base de dades: SQL • Què és SQL • Avantatges d’SQL • DML (Data Management Language) • Recuperació de dades amb SQL: sentència SELECT • Consultes simples (SELECT ... FROM) • Pràctiques de gestió de bases de dades via SQL (1) • Consultes unió (UNION)

4. Programa de l’assignatura Dimecres 15 de gener (2n dia, 17-19) • Consultes multi-taula: composicions • consultes multi-taula en SQL1 • composicions internes (INNER JOIN) • composicions externes (LEFT, RIGHT i OUTER JOIN) • autocomposicions • Pràctiques de gestió de bases de dades via SQL (2) • Consultes resum • funcions columna (GROUP BY) • condicions en consultes resum (HAVING) • Pràctiques de gestió de bases e dades via SQL (3)

5. Programa de l’assignatura Dimecres 15 de gener (2n dia, 19-21h, 2 hores) • Subconsultes • test de comparació amb subconsulta • test de pertinença a un conjunt d’una subconsulta • test d’existència • test de comparació quantificada • Pràctiques de gestió de bases de dades via SQL (4) • Consultes aniuades • Pràctiques de gestió de bases de dades via SQL (5)

6. Programa de l’assignatura Dilluns 20 de gener (3r dia, 17-19h) • Enllaç ODBC d’una capa SIG amb una consulta SQL (fitxer DSN) • Creació d’un fitxer DSN per a la base de dades • Creació d’una capa de punts a partir de la base de dades • Creació d’un enllaç via ODBC d’una consulta SQL amb la capa de punts de MiraMon • Creació d’un enllaç via ODBC d’una consulta SQL amb una capa de polígons de MiraMon • Transaccions (COMMIT ROLLBACK)

7. Programa de l’assignatura Dilluns 20 de gener (3r dia, 19-21h) • Actualització de registres: • Inserció (INSERT) • Eliminació i eliminació amb subconsulta (DELETE) • Modificació i modificació amb subconsulta (UPDATE) • Pràctiques de gestió de bases de dades via SQL (6)

8. Programa de l’assignatura Dimecres 22 de gener (4t dia, 17-20h, 3 hores) • DDL (Data Definition Language) • Definició i creació de bases de dades • Definició de taules i vistes • Definició de camps • Definició de restriccions • Definició d'índexs • Canvis en l’estructura de la base de dades • Pràctiques de gestió de bases de dades via SQL (7)

9. Que és una base de dades Relacional • Definició informal • Una base de dades relacional és una estructura on tota la informació (dades i metadades) s’organitza estrictament en taules i, per tant, totes les operacions dins la base de dades tenen lloc sobre aquestes taules i dins de cada taula en cada columna i cada fila. • En una base de dades cada taula ha de tenir un nom diferent • Cada columna (o camp) dins d’una taula ha de tenir un nom diferent • Les columnes de taules diferents poden dir-se igual. • Cada columna s’ha de definir amb un tipus de dada (text, enter, real, data, ...) • Cada fila (o registre) s’ha de definir per tal que es pugui distingir de qualsevol altra (unívoca) • Però quan es dissenya una base de dades, cal definir moltes coses més.

10. Dissenyant una base de dades relacional Si cada treballador sap més d’una llengua? • No es pot preveure quantes columnes faran falta? • Cada columna ha de portar un nom diferent • Molt espai desaprofitat i molts espais en blanc • Dificultat a l’hora de fer les consultes • L’ordre en què apareixen marca la importància o és arbitrari? • Repetició en una columna d’una llengua que ja està inclosa.

11. Solució: • crear dues taules • relacionar-les Empleat-Llengua Treballador La relació es fa a través de la columna comuna entre les dues taules.

12. O millor encara... • crear 3 taules relacionades Empleat-Llengua Empleat Llengua La relació es fa a través de la columna comuna entre les 3 taules.

13. Empleat-Llengua Empleat Llengua clau forana clau primària

14. Model Entitat-Relació (E/R) Tècnica d’anàlisi basada en la identificació de les entitats i de les relacions que es donen entre elles en la part de realitat que volem representar. • En el disseny d’una base de dades es distingeixen 3 fases: • disseny conceptual • disseny lògic • disseny físic • Disseny conceptual • Es defineixen o se separen diferents conceptes en tantes entitats com facin falta • A cada entitat se li assignen els atributs. • Es defineix l’identificador primari (cas particular d’atribut) • Es defineixen les relacions entre entitats i el tipus de relacions.

15. Objectes d’una Base de Dades Relacional • Una base de dades relacional es composa de: • Entitats: cadascun dels objectes en els quals s’emmagatzema la informació amb un conjunt d’Instàncies o tuplas (són els casos individuals de l’entitat). • Atributs: són els descriptors associats a cada entitat • Identificadors: identifiquen de forma única cada instància d’una entitat • principal: és el que s’escull per diferents motius • Exemples: Entitat ‘Persona’ (DNI) • Entitat ‘Llibre’ (ISBN) • alternatius: qualsevol altre que també identifica de manera única cada instància de l’entitat (Nº seg. social) • Descriptors: descriuen característiques no úniques de cada instància: Persona  Nom, Cognom, Adreça, etc

16. Base de dades Relacional. Tipus d’entitat Entitats regulars (o fortes) i entitats dèbils Un tipus d’entitat és forta si l’existència de les seves instàncies no depèn de cap altre entitat. En cas contrari, seria de tipus dèbil. Entitat “Parcela” és una entitat forta mentre que l’entitat “Arbre” és dèbil perquè depèn de l’existència de l’entitat “Parcela”. Si s’elimina una instància del tipus entitat forta, caldrà eliminar les instàncies del tipus entitat dèbil que depenen d’ella. L’entitat dèbil no té suficients atributs propis per a formar la seva pròpia identitat: L’identificador de l’entitat dèbil inclou l’identificador de l’entitat forta de la que depèn per a la seva existència: Identificador entitat dèbil = Identificador entitat forta (+ Discriminant)

17. Base de dades Relacional: Tipus d’entitat • Especialització i generalització • Exemples: • Inventari forestal  Estrat de vegetació – arbori, arbustiu, herbaci... • Empresa  Persones – treballadors i clients • Supertipus: Tipus d’entitat que inclou un o més subgrups diferents d’instàncies. • Subtipus: Cadascun dels subgrups d’instàncies d’un tipus d’entitat que està per sobre. • - Els subtipus hereten els atributs del supertipus: • - Els subtipus tenen tots els atributs del supertipus més alguns propis. • - L’identificador dels subtipus és l’identificador del supertipus. • Especializació: Procés de trobar les diferències entre les instàncies d’un tipus d’entitat per distingir els subtipus que el formen. • Generalizació: Procés de trobar la part comú de les instàncies de diferents tipus d’entitat per extreure el supertipus que les engloba.

18. Base de dades Relacional. Atributs Tipus d’atributs Atributs compostos vs. Atributs simples (atòmics) Els atributs compostos es poden dividir en components més petits amb significat propi Exemple: direcció postal= carrer + nom municipi + CP + província Atributs monovaluats vs. Atributs multivaluats Un atribut multivaluat té diferents valors per a una entitat particular. Exemple: Diàmetre de capçada d’un arbre és multivaluat perquè es prenen dues mesures Atributs emmagatzemats vs. Atributs derivats Un atribut derivat és tot aquell que és deduïble a partir d’atributs emmagatzemats. Si es pot deduir no s’ha d’emmagatzemar! Exemple atributs derivats: l’edat d’una persona, tipus funcional d’una espècie

19. Base de dades Relacional. Domini • Domini • És el conjunt de valors possiblesd’un atribut. • Exemple: El domini de l’atribut DNI és un número enter de 8 xifres. • Diferents atributs poden compartir el mateix domini. • Quan es defineixen les propietats d’un atribut és obligatori especificar el tipus (text, numèric enter, byte, decimal...) que és una primera manera de definir el domini.

20. Base de dades Relacional. Relacions • Relacions: representen una associació entre dues o més entitats • Connexió semàntica entre una, dues (o més) entitats • Exemples: • Relació: comarca — municipi • Relació: llibre — escriptor • Relació: treballador — treball • Aquesta associació es fa a través d’1 o més atributs de cada entitat.

21. Classificació de les relacions • Les relacions es poden classificar en funció: • cardinalitat • existència • dependència • herència • grau • direcció

22. Classificació de les relacions. Cardinalitat • Cardinalitat • És el nombre d’instàncies relacionats per cadascuna de les 2 entitats implicades • relació u a u (1:1): quan cada instància d’una entitat A està associada a com a molt 1 instància de l’entitat B • exemple: home  dona • relació u a molts (1:n): quan per cada instància d’una entitat A hi ha zero, una o moltes instàncies de l’entitat Bexemple: comarques  municipis; Parella  Fills • relació molts a molts (n:m): quan per cada instància d’una entitat A hi ha zero, una o moltes instància de l’entitat B i viceversa • ex: Empleat  Llengua; Alumnes Professors Cardinalitat 1:1 Cardinalitat 1:molts Cardinalitat molts:molts Notació en el disseny conceptual

23. Classificació de les relacions. Existència • Existència • Indica si pot existir la relació entre una instància d’una entitat amb una de l’entitat amb la que es relaciona: • obligada (un municipi pertany a una comarca) o, • opcional (un municipi pot tenir algun EIN o no), opcional obligada Notació en el disseny conceptual

24. Classificació de les relacions. Herència • Dependència en existència (Herència) • Sempre hi ha una entitat regular o forta i una dèbil • Herència: Les dues entitats filles heretentots els atributs de l’entitat pare ‘Persona’. Empleat Persona Id_Persona Client Notació en el disseny conceptual • Per les entitats filles l ‘identificador és directament l’identificador de l’entitat ‘Persona’. • Sempre és una relació 1 a 1.

25. Classificació de les relacions. Herència Existència obligada Entitat pare Entitat filla Un Id principal Un Relació d’herència Existència opcional Notació en el disseny conceptual • L’identificador de la taula pare migra a la taula filla, i per tant és una relació sempre obligada amb la taula pare (cardinalitat 1 a 1).

26. Classificació de les relacions. Dependència • Dependència en identificació (relacions pare-fill) • Sempre hi ha una entitat forta i una dèbil • Les relacions de dependència existeixen quan els identificadors d’una entitat es basen en la relació que tenen amb una altra entitat, és a dir, una instància de l’entitat ‘filla’ només pot existir si existeix la instància a l’entitat e la qual depèn (entitat ‘pare’). • La dependència existeix perquè per a identificar de forma unívoca cada instància de l’entitat filla cal saber de quina instància prové. • Les relacions de dependència són obligades per definició (de l’entitat filla a la pare). A la inversa la relació amb l’entitat pot ser opcional o obligada. pare filla pare filla Relació de dependència obligada Relació de dependència opcional Notació en el disseny conceptual

27. Classificació de les relacions. Dependència Existència obligada Taula pare Taula filla Molts Id discriminant Id_principal Relació de dependència Un Existència opcional Notació en el disseny conceptual La clau primària de la taula filla és la combinació de la clau primària de la taula pare de la que depèn en combinació amb almenys un altre camp. El conjunt identifica de forma única i inequívoca a cadascuna de les instàncies. Exemple: taula ‘arbre’ l’identificador principal està format per ‘IdParcela’ i ‘IdArbre’.

28. Classificació de les relacions. Grau • Grau. És el nombre d’entitats involucrades en una mateixa relació • reflexiva: 1 entitat implicada en la relació Exemple: el director d’una oficina també és treballador de l’oficina • binària: 2 entitats implicades en una relació • ternària: 3 entitats implicades en una mateixa relació • ... Empleat Persona DNI Client Notació en el disseny conceptual Companyia Les relacions de més de dues entitats s’han de resoldre quan es passa al model lògic  generalitzacions o especialitzacions.

29. Classificació de les relacions. Lògica trivaluada • Lògica trivaluada (sí, no, NULL) • Tractament sistemàtic de valors nuls • Els valors nuls (diferent de cadena buida, blanc, 0, ...) se suporten en els SGBD totalment relacionals per representar informació desconeguda o no aplicable de manera sistemàtica • És independentment del tipus de dada. • En les operacions lògiques convencionals els valors nuls no se suporten. • Als SGBD la lògica trivaluada és la solució. • Existeixen tres valors (no dos) : Veritat, Fals i Desconegut (null). • S’aplica a nivell de: • Registre: s’aplica quan l’existència del registre a la taula filla és opcional. • Camp: quan un determinat camp admet valors nul.

30. Classificació de les relacions. Lògica trivaluada • Lògica trivaluada (sí, no, NULL) • El nul (null value) s’utilitza quan es desconeix el valor d’un atribut per a certa entitat • El valor existeix però no el tenim • Exemple: edat [d’un treballador] • No se sap si el valor existeix • Exemple: num_mobil [d’un treballador] • L’entitat no té cap valor aplicable per a l’atribut: • Exemple: pis [com a atribut de direcció de l’entitat EMPLEAT], no n’hi ha perquè viu en una casa.

31. Classificació de les relacions. Direcció • Direcció • Indica la taula d’origen d’una relació entre dues taules. • En cas de dependència o d’herència la direccióés de la taula pare a la taula filla. • Clau migrada: la taula filla hereta el camp clau de la taula pare de la qual depèn,exemple: pare  fill; provincia  municipi • En les relacions en què no hi ha dependència (dues entitats independents) NO hi ha direcció • Exemple: Comarca  Parc natural • Clau aliena o forana: quan la relació és 1 a molts la direcció és d’1 a molts perquè la taula molts conté una clau forana que prové de la taula ‘1’.

32. Disseny conceptual. Passos • En el procés de disseny d’una base de dades cal seguir els següents passos: • Identificar entitats i accions d’ús habitual • Eliminar entitats prescindibles fusionant entitats • Identificar relacions entre entitats • Eliminar relacions innecessàries • Assignar els atributs de cada entitat • Determinar quin o quins atributs són identificadors primaris • Identificar entitats • Una entitat és una entitat per ella mateixa si es poden identificar diferents atributs que li són propis i exclusius. • Un atribut ha de ser atòmic, és a dir, no pot ser subdividit en altres atributs.

33. Disseny conceptual (2/6) Identificar relacions 1- determinar la cardinalitat d’una relació 2- determinar l’opcionalitat de la relació 3- Verificar si s’ha fet bé, llegint la relació en els dos sentits. exemple: EstacioMostreig Municipi Id_Estació Id_Inventari Id_Municipi Cardinalitat (1:molt) Cada municipi pot contenir varies estacions de mostreig i alguns potser cap estació (existència opcional, cardinalitat 1 a molts). Cada estació de mostreig pertany a un i un sol municipi (existència obligada, cardinalitat 1 a 1).

34. Disseny conceptual (3/6) • Les relacions 1 a 1 són problemàtiques perquè es pot confondre la relació entre dues entitats amb una sola entitat amb els atributs de totes dues entitats. • Dues entitats amb una relació de cardinalitat 1 a 1 obligada en els dos sentits és una sola entitat cal fusionar-les. Això es tradueix en incorporar els atributs d’una de les entitats a l’altra. • Les relacions 1 a 1 són poc comunes a menys que hi hagi relacions de més de dues entitat (ternàries o més). • En una relació 1 a 1, opcional d’una banda i obligada de l’altra, és recomanable mantenir les dues entitats.

35. Disseny conceptual (4/6) • Eliminar les relacions innecessàries • Un cop fetes totes les relacions entre totes les entitats n’hi haurà que seran redundants i per tant és bo eliminar-les. • Exemple: Pare Avi Pare Nom Id_Avi Nom Fill Id_Fill Nom La relació avi-net no és necessària perquè està implícita en les relacions fill-pare pare-avi

36. Disseny conceptual (5/6) • Identificar atributs • Un mateix atribut no pot aparèixer en més d’una taula. Els atributs són exclusius de cada taula. • Els atributs que es repeteixen en diferents taules són les claus primàries o foranes que són les que formen les relacions. • Un atribut és un atribut si no pot ser dividit en altres atributs • Les dades derivades, és a dir, que poden ser deduïdes d’atributs ja presents a la taula, han de ser evitats.Exemple: L’Idcomarca es pot deduir de l’IdMunicipi a través de la taula que relaciona municipis amb comarques. • Altres dades derivades poden ser les que es poden deduir fent consultesExemple: el nombre d’arbres per estació de mostreig (es pot deduir fent una consulta).

37. Disseny conceptual (6/6) • Decidir quin atribut(s) de cada entitat serà l’identificador primari • Són qualsevol combinació d’un o més atributs o relacions que identifiquen unívocament cada instància d’una entitat. • Sovint hi ha més d’un atribut candidat a identificador primari. Un cop decidit quin és el millor candidat els altres es queden com identificadors alternatius (que cal definir com a únics!). • Quin és el millor? és preferible usar el més curt o el més habitual. • exemple: EstacioInv • CoordenadaX + CoordenadaY  IdUTM • De vegades no hi ha cap atribut candidat. En aquest cas cal un identificadorarbitrari (generat a l’atzar o autonumèric).

38. Matrius Entitat-Entitat i Entitat-Atribut • Matriu E-E: • Matriu de dues dimensions on a cada dimensió s’hi posen les entitats i a cada casella la cardinalitat de la relació • (0,1); (1,n); (0,n) o (n,m) • Matriu E-A: • Matriu de dues dimensions, en columnes les entitats i en fileres els atributs. Per cada atribut es marca a la casella corresponent si es tracta de: • clau primària (pk) • clau forana (o aliena) (fk) • migrada (herència o dependència) (m) • atribut propi (o –owner)

39. Exemple: matriu Entitat-Entitat

40. Exemple: matriu Entitat-Entitat

41. TesaureMunicipi DivisioAdministrativa EstacioInv TesaureComarca IdMunicipi IdUTM IdComarca EstacioMostreigInv ArbreTipus EstacioMostreigVar Arbre IdArbre TesaureEspecie IdEspecie ResultatEspecie ResultatGlobal IdEspecie Disseny conceptual de la BD de l’IEFC.

42. TesaureMunicipi DivisioAdministrativa EstacioInv TesaureComarca IdMunicipi IdUTM IdComarca EstacioMostreigInv ArbreTipus EstacioMostreigVar Arbre IdArbre TesaureEspecie IdEspecie ResultatEspecie ResultatGlobal IdEspecie Disseny conceptual de la BD de l’IEFC.

43. TesaureMunicipi DivisioAdministrativa EstacioInv TesaureComarca IdMunicipi IdUTM IdComarca EstacioMostreigInv ArbreTipus EstacioMostreigVar Arbre IdArbre TesaureEspecie IdEspecie ResultatEspecie ResultatGlobal IdEspecie Disseny conceptual de la BD de l’IEFC.

44. TesaureMunicipi DivisioAdministrativa EstacioInv TesaureComarca IdMunicipi IdUTM IdComarca EstacioMostreigInv ArbreTipus EstacioMostreigVar Arbre IdArbre TesaureEspecie IdEspecie ResultatEspecie ResultatGlobal IdEspecie Disseny conceptual de la BD de l’IEFC.

45. TesaureMunicipi DivisioAdministrativa EstacioInv TesaureComarca IdMunicipi IdUTM IdComarca EstacioMostreigInv ArbreTipus EstacioMostreigVar Arbre IdArbre TesaureEspecie IdEspecie ResultatEspecie ResultatGlobal IdEspecie Disseny conceptual de la BD de l’IEFC.

46. TesaureMunicipi DivisioAdministrativa EstacioInv TesaureComarca IdMunicipi IdUTM IdComarca EstacioMostreigInv ArbreTipus EstacioMostreigVar Arbre IdArbre TesaureEspecie IdEspecie ResultatEspecie ResultatGlobal IdEspecie Disseny conceptual de la BD de l’IEFC.

47. Disseny lògic de la base de dades (1/4) • Entitats  Taules • Atributs  Columnes o camps • Identificadors primaris  Claus primàries • Per cada relació 1 a molts la clau primària de la taula 1 esdevé la clau forana de la taula molts. ARBRE Clau migrada (dependència) IdUTM IdArbre Id Especie IdTipus DBH Altura Clau primària (PK) Clau discriminant (dependència) Clau forana (M)de la taula ‘TesaureEspecie’ Clau forana (M) de la taula ‘TesaureTipusForma’ Atributs

48. Disseny lògic de la base de dades (2/4) • Per cada relació molts a molts es crea una taula intermèdia. S’estableix una relació 1 a molts entre cada taula original amb la nova taula. Comarca Pein IdPein Nom IdComarca Nom Comarca PeinComarca Pein IdPein IdComarca IdComarca Nom IdPein Nom

49. Disseny lògic de la base de dades (3/4) • Les entitats que tenen relacions d’herència cal convertir-les en taules separades o ajuntar-les en una supertaula. • Cada herència es transforma en una generalització o en una especialització: • Generalització: és recollir en una entitat pare tots els atributs de les entitats filles. Aquesta s’anomena entitat supertipus. • Especialització: és separar en diferents taules a partir de les entitats filles. Les entitats filles s’anomenen entitats subtipus. • total: tots els atributs de l’entitat pare migren a les entitats filles. La taula pare desapareix. • parcial: es mou una part dels atributs a les entitats filles i l’identificador primari de la taula pare que s’hereta de la taula pare. La taula pare es manté.

50. Disseny lògic de la base de dades (4/4) • L’entitat subtipus hereta (migració de la clau) la clau de l’entitat pare (supertipus). • Una altra forma de classificar-les és: • jerarquia amb solapament: quan comparteixen atributs comuns que físicament estan a l’entitat supertipus.Una instància pot estar en més d’una de les entitats subtipus. • jerarquia disjunta: quan no comparteixen res més que la clau primària de l’entitat pare.Una instància només pot estar en una de les entitats subtipus. • Les generalitzacions es poden aniuar tantes vegades com calgui (una entitat subtipus pot ser a la vegada supertipus d’un altre conjunt d’entitats). • Com més especialitzacions hi hagi (més taules) més complexa és la base de dades i més difícil de manipular. • És molt recomanable fer generalitzacions i reduir així el nombre de taules.