Le BASI DI DATI : le fondamenta dei moderni sistemi Software

1. L'immaginazione è più importante della conoscenza. La conoscenza è limitata, l'immaginazione abbraccia il mondo, stimolando il progresso, facendo nascere l'evoluzione. ( A. Einstein )

2. Di cosa parleremo Le BASI DI DATI : le fondamenta dei moderni sistemi Software

3. Mi presento Paolo Castelletti • Esperienza: 15 anni maturati in aziende IT in ambito internazionale e Italiano, sia come libero professionista che come dipendente; • Ruoli: Responsabile Data Base Administration, DBA, Project Manager. • paolo.castelletti@aspasiel.it • paolocastle@gmail.com • paolocastle.wordpress.com

4. La mia ditta Chi siamo: Aspasiel è una Società del Gruppo Acciai Speciali Terni. Fornisce la sua competenza nel campo delle soluzioni ICT (Information and Communication Technology) e servizi infrastrutturali per aziende e gruppi industriali. La forza di Aspasiel è nelle capacità e nel know-how del suo personale. La Mission: • I dati e le informazioni costituiscono il patrimonio di un’Azienda. • Le minacce informatiche, i guasti, i disastri e gli errori utente mettono a repentaglio la loro sicurezza, Disponibilità, Integrità, Riservatezza. • L’obiettivo è supportare le aziende fornendo servizi IT di alta qualità e permettendogli di concentrare tutte le energie nella crescita del loro Business.

5. Personaggi Notevoli Lawrence Joseph "Larry" Ellison Edgar Frank "Ted" Codd

6. Le architetture multitier

7. La diffusione dei RDBMS oggi

8. Chi lavora con il RDBMS DB Administrator DB Developer

9. Torniamo alla teoria : come progettiamo un DB oggi ? Modello ER Normalizzazione Decomposizione dello schema di relazioni Obiettivi: Eliminare la ridondanza dati Rappresentare correttamente tutto lo schema di relazioni

10. Mai sentitoparlaredi “FormeNormali?”

11. DB non normalizzato

12. Prima forma normale Benefits: Now we can have infinite phone numbers or company addresses for each contact. Drawback: Now we have to type in everything over and over again. This leads to inconsistency, redundancy and wasting space. Thus, the second normal form…

13. Seconda forma normale

14. Terza forma normale • Eliminaicampiche non dipendonodalle PK

15. Tipi direlazioni Uno a Uno One row of a table matches exactly to another One person, one id number, one address Uno a Molti One row of a table matches many of another One person, many phone numbers Molti a Molti One row may match many of another or many rows match one row of another

16. Quarta forma normale • In unarelazionemolti-a-molti, entitàindipendenti non possonoesserememorizzatenellastessatabella.

17. Quindi … perché normalizzare una base dati? • Aumenta l’integrità dei dati • Riduce la ridondanza • Migliora l’efficienza nell’accesso ai dati • Migliora la scalabilità dell’applicazione • Anche se “è un lavoraccio” paga sul lungo termine

18. Non serve essere “talebani relazionali” basta ricordarsi … Tenete a mente il concetto di normalizzazione Non replicare i dati in tabelle Se infrangete le regole, siate consapevoli del motivo per cui lo fate e fatelo per una buona ragione (la pigrizia non lo è).

19. Fino a che punto infrangere le regole relazionali? Un caso esemplare

20. Fino a che punto infrangere le regole relazionali? Un caso esemplare

21. Fino a che punto infrangere le regole relazionali? Un caso esemplare

22. R.D.B.M.S. • Dobbiamo sapere : • Le potenzialità del RDBMS • Come sono organizzati i dati • Come viene gestita la concorrenza

23. Sql Server

24. Oracle

25. MySQL

26. Data Manipulation Language A DML statement is executed when you: Add new rows to a table Modify existing rows in a table Remove existing rows from a table A transaction consists of a collection of DML statements that form a logical unit of work.

27. The INSERT Statement Add new rows to a table by using the INSERT statement. Only one row is inserted at a time with this syntax. INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

28. Inserting Special Values The SYSDATE function records the current date and time. SQL> INSERT INTO emp (empno, ename, job, 2 mgr, hiredate, sal, comm, 3 deptno) 4 VALUES (7196, 'GREEN', 'SALESMAN', 5 7782, SYSDATE, 2000, NULL, 6 10); 1 row created.

29. Inserting Specific Date Values Add a new employee. SQL> INSERT INTO emp 2 VALUES (2296,'AROMANO','SALESMAN',7782, 3 TO_DATE('FEB 3, 97', 'MON DD, YY'), 4 1300, NULL, 10); 1 row created. • Verify your addition. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- -------- ---- --------- ---- ---- ------ 2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10

30. Inserting Values by Using Substitution Variables Create an interactive script by using SQL*Plus substitution parameters. SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (&department_id, 3 '&department_name', '&location'); Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for location: ATLANTA 1 row created.

31. Updating Rows in a Table Specific row or rows are modified when you specify the WHERE clause. All rows in the table are modified if you omit the WHERE clause. SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782; 1 row updated. SQL> UPDATE employee 2 SET deptno = 20; 14 rows updated.

32. Specific rows are deleted when you specify the WHERE clause. All rows in the table are deleted if you omit the WHERE clause. Deleting Rows from a Table SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted. SQL> DELETE FROM department; 4 rows deleted.

33. JOIN

34. INNER JOINS • SELECT EE.NAME, DD.NAME , … • FROM EMPLOYEES AS EE INNER JOIN DEPARTMENTS AS DD • ON (EE.DEP_ID = DD.DEP_ID) • WHERE … SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE , DEPARTMENTS AS DD • USING (DEP_ID) • WHERE … SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE , DEPARTMENTS AS DD • WHERE(EE.DEP_ID = DD.ID) AND …

35. OUTER JOINS • SELECT EE.NAME, DD.NAME , … • FROM EMPLOYEES AS EE LEFT OUTER JOIN DEPARTMENTS AS DD • ON (EE.DEP_ID = DD.DEP_ID) • WHERE … SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE RIGHT OUTER JOIN DEPARTMENTS AS DD ON (EE.DEP_ID = DD.DEP_ID) WHERE … SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE FULL OUTER JOIN DEPARTMENTS AS DD ON (EE.DEP_ID = DD.DEP_ID) WHERE …

36. SORTING RESULTS , PSEUDOCOLUMNS • SELECT EE.NAME, EE.SURNAME , ROWID • FROM EMPLOYEES AS EE • WHERE … • ORDER BY EE.SURNAME DESC , EE.NAME SELECT NAME, SURNAME , ROWNUM FROM EMPLOYEES ORDER BY SURNAME SELECT NAME, SURNAME FROM ( • SELECTNAME, SURNAME , ROWNUM • FROMEMPLOYEES • ORDER BY SURNAME) QQ • WHERE QQ.ROWNUM >= 10 • AND QQ.ROWNUM <= 20;

37. Operatori insiemistici SQL • SELECT product_id FROM order_items • UNION • SELECT product_id FROM inventories; • SELECT product_id FROM order_items • UNION ALL • SELECT product_id FROM inventories; • SELECT product_id FROM inventories • INTERSECT • SELECT product_id FROM order_items; • SELECT product_id FROM inventories • MINUS • SELECT product_id FROM order_items;

38. SQL aggregate functions • Select department_id , count (*) • From employees • WHERE … • Group by department_id • Having count(*) > 6 ; • SUM • MAX • MIN • AVG • SDEV • VARIANCE • PERCENT_RANK

39. Cosa accade a una istruzione SQL in una istanza

40. Transazioni … ACID Atomicità Consistenza Isolamento Durability (persistenza)

41. Database Transactions Consist of one of the following statements: DML statements that make up one consistent change to the data One DDL statement One DCL statement

42. Database Transactions Begin when the first executable SQL statement is executed End with one of the following events: COMMIT or ROLLBACK is issued DDL or DCL statement executes (automatic commit) User exits System crashes

43. Controlling Transactions Transaction INSERT UPDATE INSERT INSERT DELETE DELETE ROLLBACK to Savepoint B ROLLBACK to Savepoint A ROLLBACK COMMIT INSERT UPDATE Savepoint A Savepoint B

44. An automatic commit occurs under the following circumstances: DDL statement is issued DCL statement is issued Normal exit from SQL*Plus, without explicitly issuing COMMIT or ROLLBACK An automatic rollback occurs under an abnormal termination of SQL*Plus or a system failure. Implicit Transaction Processing

45. State of the Data Before COMMIT or ROLLBACK The previous state of the data can be recovered. The current user can review the results of the DML operations by using the SELECT statement. Other users cannot view the results of the DML statements by the current user. The affected rows are locked; other users cannot change the data within the affected rows.

46. State of the Data After COMMIT Data changes are made permanent in the database. The previous state of the data is permanently lost. All users can view the results. Locks on the affected rows are released; those rows are available for other users to manipulate. All savepoints are erased.

47. Committing Data Make the changes. SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782; 1 row updated. • Commit the changes. SQL> COMMIT; Commit complete.

48. State of the Data After ROLLBACK Discard all pending changes by using the ROLLBACK statement. Data changes are undone. Previous state of the data is restored. Locks on the affected rows are released. SQL> DELETE FROM employee; 14 rows deleted. SQL> ROLLBACK; Rollback complete.

49. Rolling Back Changes to a Marker Create a marker in a current transaction by using the SAVEPOINT statement. Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement. SQL> UPDATE... SQL> SAVEPOINT update_done; Savepoint created. SQL> INSERT... SQL> ROLLBACK TO update_done; Rollback complete.

50. Statement-Level Rollback If a single DML statement fails during execution, only that statement is rolled back. The Oracle Server implements an implicit savepoint. All other changes are retained. The user should terminate transactions explicitly by executing a COMMIT or ROLLBACK statement.