Usuari:Sergi/Temari/Tema 36 La manipulación de datos. Operaciones. Lenguajes. Optimización de consultas.

De Acacha

Dreceres ràpides: navegació, cerca

Contingut

Manipulació de dades

Data Manipulation Language (DML) o llenguatges de manipulació de dades són un subconjunt o familiar de llenguatges de computador utilitzats pels ordinadors (i les aplicacions i els usuaris que utilitzen aquestes aplicacions) per tal d'obtenir i manipular les dades d'una base de dades. És un dels 2 subconjunts en que es divideixen els llenguatges de bases de dades:

Les operacions més habituals dels DML són:

  • Consulta: Obtenir informació de la base de dades (en SQL la clàusula SELECT)
  • Inserció: Afegir informació de la base de dades (en SQL la clàusula INSERT)
  • Modificació: Modificar informació de la base de dades (en SQL la clàusula UPDATE)
  • Eliminació: Eliminar informació de la base de dades (en SQL la clàusula DELETE)

Hi ha 2 tipus de llenguatges de manipulació de dades:

  • Procedimentals: Al principi de l'ús de bases de dades, les dades normalment eren manipulades per programes i els seus llenguatges de programació procedimentals. Els sistemes de bases de dades antics (del model jeràrquic i de xarxa) més coneguts són IMS/DL1 (IBM Information Managment System) i les bases de dades CODASYL (Conference on Data Systems Languages) (IDMS (Integrated Database Management System)).
  • No procedimentals o declaratius: El llenguatge més utilitzat actualment és SQL. Es tracta d'un llenguatge sense procediments (tot i que en versions modernes dona suport a PROCEDURES) on l'usuari no especificar pas a pas quines accions a de dur a terme la màquina. Amb SQL, s'utilitza un llenguatge similar al llenguatge natural (anglès) per a declarar el que volem obtenir/manipular. El com es dura aquesta acció es responsabilitat de l'interpret de SQL.

Els llenguatges no procedimentals com SQL es van dissenyar pensant en fer més fàcil la manipulació de dades. El gran inconvenient és però que al no especificar-se el com, el seu rendiment és inferior (es quelcom similar al que passa entre els llenguatges d'alt nivell i els llenguatges màquina) i per aquesta raó veurem a l'apartat Optimització de consultes com optimitzar aquests sistemes.

Recursos:

Arquitectures (2 nivell i ANSI/X3/SPARC) i DML

Consulteu els apartats:

Arquitectura de 3 nivells ANSI/X3/SPARC.Nivells de descripció

El comitè ANSI/X3/SPARC (Comitè de Planificació i Requeriments de l'ANSI) - ANSI-SPARC American National Standards Institute, Standards Planning And Requirements Committee- va suggerir a l'any 1977 la següent arquitectura de 3 nivells (X3):

Esquema intern

Una de les característiques desitjades de les bases de dades és la independència física. Aquesta característica ens permet que una base de dades sigui independent de les característiques físiques (dispositius d'emmagatzemament, estructures de dades físiques d'emmagatzemament, etc). En realitat, la majoria de sistemes gestors de bases de dades ens permeten controlar aspectes físics de la base de dades per tal de poder optimitzar el seu funcionament.

En aquest nivell, el DDL s'encarrega de definir qüestions com els tipus de estructures de dades a utilitzar per emmagatzemar les dades, els índexs, el suport físic per a transaccions, etc.

Nivell de treball només per al DBA

Aquest nivell és defineixen qüestions com:

  • Organització de les estructures de dades: ISAM, INNODB
  • Mida de blocs
  • Mètodes de direccionament
  • Índexs
  • Tècniques de compressió

Esquema conceptual

Des d'aquest punt de vista, el DBA utilitza DDL per assignar noms a camps, agregacions de dades, registres etc. i estableix les seves propietats (tipus de dades i longituds). També en aquest nivell és defineixen les relacions entre dades i les restriccions semàntiques. En aquest nivell no s'especifiquen els detalls de la implementació física.

Nivell de treball només per al DBA

Aquest nivell és defineixen qüestions com:

  • Noms, tipus i mides de les dades
  • Relacions entre dades
  • Restriccions d'integritat

Esquema extern

En aquest nivell el DDL s'encarrega de proporcionar operacions per a la definició de les vistes externes. Normalment el llenguatge utilitzat és molt similar a l'utilitzat a nivell lògic (taules i vistes tenen una estructura lògica similar, tot i que tenen funcionalitats ben diferenciades)

Nivell de treball per als usuaris

ES defineixen les vistes o subesquemes. No tots els usuaris treballen amb totes les dades, i les vistes o subesquemes ens permeten definir subconjunts de la base de dades.

Modelat de dades

El modelat d'una base de dades és un problema que s'acostuma a dividir en 3 nivells:

Nivell conceptual

És el nivell més abstracte i esta més relacionat a l'estudi dels Sistemes d'Informació (SI) en general, entès com quelcom més ampli que simplement una base de dades. En aquest nivell es modela un sistema d'informació conceptualment (funcionalitats del sistema, regles, actors, etc.) sense especificar detalls de la seva implementació

Nivell lògic

Aquest nivell representa el primer intent de implementar un solució per al problema conceptual.

Nivell físic

Imatge:SGBD2.png

Llenguatges de manipulació de dades (DML)

Ja s'ha comentat que històricament els primers llenguatges DML van ser procedimentals i dels models:

  • Xarxa: IMS/DL1 (IBM Information Managment System)
  • Jeràrquic: les bases de dades CODASYL (Conference on Data Systems Languages) (IDMS (Integrated Database Management System)).

Aquest models s'expliquen en més detall al tema de les oposicions:

Per aquesta raó i pel fet que aquests sistemes es pot considerar que actualment s'utilitzen poc, no els tractarem en detall i en canvi si que parlarem de forma somera sobre sistemes més actual que utilitzen un model jeràrquic ([Llenguatges DML en altres models. Ldap i XML com exemple de base de dades jeràrquica bases de dades XML i LDAP]).

Si que parlarem en detall del DML en SGBDR i per tant parlarem de l'estàndard més utilitzat: SQL.


DML en SGBDR

Operacions

Operacions i model matemàtic (model relacional)

Un dels punts forts del model de base de dades relacionals (respecte a altres models com el jeràrquic o el de xarxa) és que tenen al seu darrere tota una estructura matemàtica de suport creada per Codd al 1970 quan va definir el model relacional (precisament com una alternativa als models existents, jeràrquic i en xarxa).

Tota aquesta estructura matemàtica no és que s'utilitzi directament ni que faci més fàcil les operacions amb bases de dades des de el punt de vista dels usuaris de la base de dades. Tanmateix, si que és la base de la optimització dels SGBDR comercials, a partir de la qual han pogut optimitzar el rendiment d'aquestes sistemes.

Un dels avantatges del model relacional és que la manipulació de les dades es pot realitzar a partir de les seves relacions lògiques fet que va donar llum a llenguatges no procedimentals (també anomenats sovint declaratius) com SQL. Aquests llenguatges també se'ls anomena llenguatges relacionals. Cal comentar però, que el mateix Codd va lluitar per denunciar les "desviacions" del model relacional més purista que van esdevenir amb el llenguatge SQL i els SGBDR relacionats. Aquestes desviacions han estat provocades i justificades pels diferents fabricants de programari per raons comercials i d'extensibilitat i adaptació de les capacitat dels seus sistemes a les necessitats de cada moment.

Els llenguatges proposats per Codd són:

Llenguatge relacionalment complet

És diu que un llenguatge és relacionalment complet, si mitjançant aquest llenguatges es poden expressar totes les relacions de l'àlgebra relacions. Qualsevol expressió de l'àlgebra relaciona s'ha de poder expressar també amb el llenguatge relacionalment complet. SQL és relacionalment complet i a més proporciona altres operacions extres al model relacional.

Àlgebra relacional i càlcul relacional

Consulteu:

SQL

SQL és classifica en 2 tipus:

  • SQL interactiu: llenguatge de consultes basat en l'àlgebra relacional que també té suport per a inserir, modificar i eliminar dades
  • SQL immers: En angles anomenat SQL embedded (la traducció podria ser SQL encastat "empotrat")

Aquesta classificació no ens ha de fer pensar que hi ha dos llenguatges SQL, més aviat són dos maneres d'utilitzar SQL (de forma interactiva, normalment utilitzant la consola o una aplicació d'un SGBDR, i de forma programàtica amb llenguatges de programació)

SQL Interactiu

SQL i l'algebra relacional. Clàusules SELECT(projecció), FROM(producte cartesià) i WHERE(predicats de selecció)

SQL permet totes les operacions de l'àlgebra relacional. Complitud?

  • SELECT: Equival a l'operació de projecció de l'àlgebra relacional. S'utilitza per llistar els atributs que es desitgen del resultat d'una consulta.
  • FROM: Equival a l' operació de producte cartesià de l'àlgebra relacional. S'utilitza per llistar els atributs que es desitgen del resultat d'una consulta.
  • WHERE: Equival als predicats de selecció de l'àlgebra relacional. S'utilitza per llistar els atributs que es desitgen del resultat d'una consulta.

També permet altres operacions.

SELECT

SELECT * 
FROM books
WHERE price > 100.00
ORDER BY title;

Recursos:

Tuples duplicades

SQL pot retornar tuples duplicades (cosa que estrictament no hauria de passar segons el model relacional però que passa per qüestions d'eficiència a l'hora d'eliminar duplicats). Per aquesta raó tenim les clàusules:

ALL | DISTINCT | DISTINCTROW
SELECT DISTINCT FROM nomtaula

Operacions de conjunts. UNION, INTERSECT i MINUS

(SELECT * FROM CLIENTS)
UNION
(SELECT * FROM CLIENTS2)


(SELECT * FROM CLIENTS)
INTERSECT
(SELECT * FROM CLIENTS2)


(SELECT * FROM CLIENTS)
MINUS
(SELECT * FROM CLIENTS2)

Recursos:

Ordenació de tuples. ORDER BY

ORDER BY DESC/ASC

Exemple:

SELECT * FROM Employees 
ORDER BY LastName, FirstName

Ordenar pot ser una operació costosa de la qual no s'ha d'abusar. Val la pena crear un index sobre el camp d'ordenació si es preveu ordenar per aquest camp sovint.

Recursos:

JOINS

Exemple

SELECT books.title, count(*) AS Authors
FROM books
JOIN book_authors 
ON books.isbn = book_authors.isbn
GROUP BY books.title;

Tipus de join:

  • LEFT JOIN:
  • RIGHT JOIN:

Recursos:

OPERACIONS SOBRE AGRUPACIONS. GROUP BY i HAVING

Permet agrupar camps i executar operacions i aplicar condicions sobre aquestes agrupacions

GROUP BY nom camp
HAVING where_condition

Exemple:

SELECT DeptID, SUM(SaleAmount) FROM Sales
WHERE SaleDate = '01-Jan-2000'
GROUP BY DeptID
HAVING SUM(SaleAmount) > 1000

Operacions:

  • Count(expression)
  • Sum(expression)
  • Min(expression)
  • Max(expression)
  • Avg(expression)

Segons els SGBDR podem disposar d'altres operacions (operacions estadístiques, operacions matemàtiques complexes, etc.)

Recursos:

SUBCONSULTES

Exemple:

SELECT DISCTINCT * FROM clients WHERE nifclient IN
(SELECT nifclient FROM factures WHERE importfactura > 2000) 

No tots els SGBDR i versions dels SGBDR ho suporten (per exemple MySQL no ho va suportar fins a la versió 4.1)

INSERT

Exemple:

INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');

Recursos:

UPDATE

Exemple:

UPDATE T SET C1 = C1 + 1 WHERE C2 = 'a'

Recursos:

DELETE

Exemple:

DELETE FROM pies WHERE flavour='Lemon Meringue';

Recursos:

MERGE

Exemple:

Recursos: http://en.wikipedia.org/wiki/Merge_%28SQL%29


TRUNCATE

Exemple:

Recursos:

Borra una taula de la base de dades

TRUNCATE TABLE table_name

Recursos:


TRANSACCIONS. COMMIT i ROLLBACK

START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET [email protected] WHERE type=1;
COMMIT;

Recursos:

SQL immers

En angles anomenat SQL embedded (la traducció podria ser SQL encastat "empotrat") és una forma d'utilitzar SQL basada en "encastar" porcions de codi SQL en llenguatges de programació

Cursors

SQL dinàmic

Llenguatges DML en altres models. Ldap i XML com exemple de base de dades jeràrquica

LDAP (Lightweight Directory Acces Protocol) no es en si una base de dades sinó que és un protocol de xarxa lleuger per accedir a les dades d'un repositori remot. És realment aquest repositori el que podem considerar la base de dades. Hi han diferents tipus de bases de dades per a Ldap a les quals pròpiament de les anomena backends. Hi ha diferents tipus de backends:

Sovint però, es parla directament de "base de dades Ldap". Aquestes bases de dades tenen la propietat de seguir un model jeràrquic, que a diferencia del model relacional utilitza una estructura en arbre (en comptes de taules) per definir les relacions entre objectes de la base de dades. És un model més orientat a objectes, ja que de fet els nodes de l'arbre es consideren objectes (o instàncies d'una classe). Els nodes poden instanciar un o més objectes.

Ldap també segueix un model de 2 nivells (estàtic i dinàmic). El nivell estàtic defineix l'estructura de la base de dades i el nivell dinàmic ofereix una sèrie d'operacions per manipular la base de dades

  • Nivell estàtic: També s'utilitzen esquemes per definir les metadades i les restriccions dels objectes de l'arbre jeràrquic. Els esquemes de Ldap defineixen les restriccions que han de seguir els objectes del directori. El llenguatge utilitzat és un d'específic per LDAP definit i estandarditzat per un RFC. Un dels avantatges de Ldap, és que a diferència del que passa amb SQL, alguns dels objectes més habituals (persones, organitzacions, recursos com màquines, etc.) ja es troben predefinits amb esquemes estàndards.
  • Nivell dinàmic: El protocol Ldap proporciona una sèrie de comandes que ens permeten manipular el repositori de dades Ldap mitjançant un llenguatge anomenat LDIF

Recursos:

LDIF (LDAP Data Interchange Format)

El format LDAP Data Interchange Format (LDIF) és un format estàndard de intercanvi de dades en format text que permet representar els continguts d'un directori LDAP. LDIF es pot utilitzar per manipular (afegir, modificar, eliminar) els continguts d'un directori LDAP.

Cada implementació del protocol Ldap proporciona les seves pròpies comandes de manipulació del directori. La implementació més coneguda és OpenLdap que ens proporciona les comandes:

  • ldapsearch: permet fer consultes al directori (seria un equivalent a SELECT de SQL)
  • ldapadd: permet afegir objectes al directori (seria un equivalent a l'INSERT de SQL)
  • ldapmodify: permet modificar objectes del directori (seria un equivalent al UPDATE de SQL)
  • ldapdelete: permet borrar objectes del directori (seria un equivalent al DROP de SQL)

El format de LDIF és molt simple i equival a especificar els parells de valors atribut-valor dels objectes del repositori. Un exemple:

dn: cn=The Postmaster,dc=example,dc=com
objectClass: organizationalRole
cn: The Postmaster
dn: CN=John Smith,OU=Legal,DC=example,DC=com
changetype: modify
replace:employeeID
employeeID: 1234
-
replace:employeeNumber
employeeNumber: 98722
-
replace: extensionAttribute6
extensionAttribute6: JSmith98
-  

dn: CN=Jane Smith,OU=Accounting,DC=example,DC=com
changetype: modify
replace:employeeID
employeeID: 5678
-
replace:employeeNumber
employeeNumber: 76543
-
replace: extensionAttribute6
extensionAttribute6: JSmith14
- 

Recursos:

Bases de dades XML

Segurament un dels temes més de mode en l'actualitat es tot lo referent a XML (eXtensible Markup Language) i un dels camps que s'està començant a explotar és el de les bases de dades XML. Hi han dos tipus de bases de dades XML:

  • XML-enabled: s'utilitza aquest terme per distingir tots aquells gestors de bases de dades moderns (que són la majoria) que suporten XML com un format d'entrada de dades (entre d'altres formats com text o CSV (Comma Sepparated Values)) i per a la sortida/representació de les dades
  • XML natives : són aquelles bases de dades 100% XML. És a dir el sistema d'emmagatzemament físic de les dades són fitxers XML. Aquest tipus de sistemes no s'estan utilitzant massivament.

El gran avantatge de XML és la interoperabilitat (forat de text estendard que facilita l'intercanvi de dades entre sistemes heterogenis.). El principal inconvenient és inherent al format de fitxer (fitxer de text) que no pot donar els mateixos resultats de rendiment que els formats binari.

Podem establir la següent comparació entre XML i SQL i els DDL i DML:

  • XML Schema/DTD: Són els llenguatges de definició de dades de XML. Permetes definir l'estructura i les restriccions semàntiques dels documents XML
  • XSLT, XPATH i XQUERY: Són els llenguatges que permeten manipular documents XML (realitza consultes, modificar documents). De fet en alguns aspectes són superiors a XML ja que se'ls pot considerar llenguatges de programació

XML també disposa de llibreries per l'accés a base de dades mitjançant connectors com ODBC.

Recursos:

Optimització de consultes

Hi ha dos tipus d'optimització:

  • Usuari: Mitjançant SQL i tècniques com la creació de index i coneixent el funcionament de l'execució de consultes, es poden optimitzar les operacions de consultar.
  • SGBDR: Els sistemes gestors tenen els seus propis sistemes de millora del rendiment i alguns permeten utilitzar plans de consulta per optimitzar l'execució de les consultes. Altres sistemes, com MySQL, tenen un cache de consultes.

Recursos:

Optimització usuaris

Correcte dissenys de taules i tipus de camps

Correcte dissenys d'índexs

Ús d'índexs en consultes

Densitat d'indexació

Ús de camps calculats i derivats

Repartiment de càrrega entre client i servidor

Desnormalització

Eines de l'usuari