le basi di dati : le fondamenta dei moderni sistemi software
DESCRIPTION
L'immaginazione è più importante della conoscenza. La conoscenza è limitata, l'immaginazione abbraccia il mondo, stimolando il progresso, facendo nascere l'evoluzione. ( A. Einstein ). Di cosa parleremo. Le BASI DI DATI : le fondamenta dei moderni sistemi Software. Mi presento. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/1.jpg)
1
L'immaginazione è più importante della conoscenza. La conoscenza è limitata, l'immaginazione abbraccia il mondo, stimolando il progresso, facendo nascere l'evoluzione. ( A. Einstein )
![Page 2: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/2.jpg)
2
Le BASI DI DATI : le fondamenta dei moderni sistemi Software
Di cosa parleremo
![Page 3: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/3.jpg)
3
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.
– [email protected]– [email protected]– paolocastle.wordpress.com
Mi presento
![Page 4: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/4.jpg)
4
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.
La mia ditta
![Page 5: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/5.jpg)
5
Edgar Frank "Ted" Codd
Lawrence Joseph "Larry" Ellison
Personaggi Notevoli
![Page 6: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/6.jpg)
6
Le architetture multitier
![Page 7: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/7.jpg)
7
La diffusione dei RDBMS oggi
![Page 8: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/8.jpg)
8
Chi lavora con il RDBMS
DB Developer
DB Administrat
or
![Page 9: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/9.jpg)
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
![Page 10: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/10.jpg)
10
Mai sentito parlare di “Forme Normali?”
![Page 11: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/11.jpg)
11
DB non normalizzato
ContactsName Company Address Phone1 Phone2 Phone3 ZipCode
Joe ABC 123 5532 2234 3211 12345
Jane XYZ 456 3421 14454
Chris PDQ 789 2341 6655 14423
![Page 12: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/12.jpg)
12
Prima forma normale
ContactsId Name Compan
yAddress Phone ZipCode
1 Joe ABC 123 5532 123451 Joe ABC 123 2234 123451 Joe ABC 123 3211 123452 Jane XYZ 456 3421 144543 Chris PDQ 789 2341 144233 Chris PDQ 789 6655 14423
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…
![Page 13: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/13.jpg)
13
Seconda forma normalePeople
Id Name Company Address Zip1 Joe ABC 123 123452 Jane XYZ 456 144543 Chris PDQ 789 14423
PhoneNumbersPhoneID Id Phone1 1 55322 1 22343 1 32114 2 34215 3 23416 3 6655
![Page 14: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/14.jpg)
14
Terza forma normale
Elimina i campi che non dipendono dalle PK
PeopleId Name AddressID1 Joe 12 Jane 23 Chris 3
AddressAddressID Compan
yAddress Zip
1 ABC 123 123452 XYZ 456 144543 PDQ 789 14423
PhoneNumbersPhoneID Id Phone1 1 55322 1 22343 1 32114 2 34215 3 23416 3 6655
![Page 15: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/15.jpg)
15
Tipi di relazioni
Uno a UnoOne row of a table matches exactly to another
One person, one id number, one addressUno a Molti
One row of a table matches many of anotherOne person, many phone numbers
Molti a MoltiOne row may match many of another or many rows match
one row of another
![Page 16: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/16.jpg)
16
Quarta forma normaleIn una relazione molti-a-molti,
entità indipendenti non possono essere memorizzate nella stessa tabella.
PhoneNumbers
PhoneID Phone1 55322 22343 32114 34215 23416 6655
PhoneRelationsPhoneRelID Id PhoneID1 1 12 1 2
3 1 34 2 45 3 56 3 6
PeopleId Name AddressID1 Joe 12 Jane 23 Chris 3
AddressAddressID Company Address Zip1 ABC 123 123452 XYZ 456 144543 PDQ 789 14423
![Page 17: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/17.jpg)
18
Quindi … perché normalizzare una base dati?
1. Aumenta l’integrità dei dati2. Riduce la ridondanza3. Migliora l’efficienza nell’accesso ai dati4. Migliora la scalabilità dell’applicazione5. Anche se “è un lavoraccio” paga sul
lungo termine
![Page 18: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/18.jpg)
19
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 è).
![Page 19: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/19.jpg)
20
Fino a che punto infrangere le regole relazionali? Un caso esemplare
![Page 20: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/20.jpg)
21
Fino a che punto infrangere le regole relazionali? Un caso esemplare
![Page 21: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/21.jpg)
22
Fino a che punto infrangere le regole relazionali? Un caso esemplare
OLTP System Online Transaction Processing
(Operational System)
OLAP System Online Analytical Processing
(Data Warehouse)
Source of data Operational data; OLTPs are the original source of the data.
Consolidation data; OLAP data comes from the various OLTP Databases
Purpose of data To control and run fundamental business tasks
To help with planning, problem solving, and decision support
What the data Reveals a snapshot of ongoing business processes
Multi-dimensional views of various kinds of business activities
Inserts and Updates Short and fast inserts and updates initiated by end users
Periodic long-running batch jobs refresh the data
Queries Relatively standardized and simple queries Returning relatively few records
Often complex queries involving aggregations
Processing Speed Typically very fastDepends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be
improved by creating indexes
Space Requirements Can be relatively small if historical data is archived
Larger due to the existence of aggregation structures and history data; requires more
indexes than OLTPDatabase Design Highly normalized with many tables Typically de-normalized with fewer tables;
use of star and/or snowflake schemas
Backup and Recovery
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss
and legal liability
Instead of regular backups, some environments may consider simply reloading
the OLTP data as a recovery method
![Page 22: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/22.jpg)
23
R.D.B.M.S.
Dobbiamo sapere :• Le potenzialità del RDBMS• Come sono organizzati i dati• Come viene gestita la concorrenza
![Page 23: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/23.jpg)
24
Sql Server
![Page 24: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/24.jpg)
25
Oracle
![Page 25: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/25.jpg)
26
MySQL
![Page 26: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/26.jpg)
27
Data Manipulation Language
A DML statement is executed when you:Add new rows to a tableModify existing rows in a tableRemove existing rows from a table
A transaction consists of a collection of DML statements that form a logical unit of work.
![Page 27: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/27.jpg)
28
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...]);
![Page 28: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/28.jpg)
29
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.
![Page 29: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/29.jpg)
30
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
![Page 30: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/30.jpg)
31
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: 80Enter value for department_name: EDUCATIONEnter value for location: ATLANTA
1 row created.
![Page 31: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/31.jpg)
32
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.
![Page 32: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/32.jpg)
33
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.
![Page 33: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/33.jpg)
34
JOIN
![Page 34: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/34.jpg)
35
INNER JOINS
SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE INNER JOIN DEPARTMENTS AS DDON (EE.DEP_ID = DD.DEP_ID)WHERE …
SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE , DEPARTMENTS AS DDWHERE(EE.DEP_ID = DD.ID)AND …
SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE , DEPARTMENTS AS DDUSING (DEP_ID)WHERE …
![Page 35: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/35.jpg)
36
OUTER JOINS
SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE LEFT OUTER JOIN DEPARTMENTS AS DDON (EE.DEP_ID = DD.DEP_ID)WHERE …
SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE RIGHT OUTER JOIN DEPARTMENTS AS DDON (EE.DEP_ID = DD.DEP_ID)WHERE …
SELECT EE.NAME, DD.NAME , … FROM EMPLOYEES AS EE FULL OUTER JOIN DEPARTMENTS AS DDON (EE.DEP_ID = DD.DEP_ID)WHERE …
![Page 36: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/36.jpg)
37
SORTING RESULTS , PSEUDOCOLUMNS
SELECT EE.NAME, EE.SURNAME , ROWID FROM EMPLOYEES AS EE WHERE … ORDER BY EE.SURNAME DESC , EE.NAME
SELECT NAME, SURNAME , ROWNUMFROM EMPLOYEESORDER BY SURNAME
SELECT NAME, SURNAME FROM (
SELECT NAME, SURNAME , ROWNUM FROM EMPLOYEESORDER BY SURNAME) QQ
WHERE QQ.ROWNUM >= 10 AND QQ.ROWNUM <= 20;
![Page 37: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/37.jpg)
38
Operatori insiemistici SQL SELECT product_id FROM order_itemsUNIONSELECT product_id FROM inventories;
SELECT product_id FROM order_itemsUNION ALLSELECT product_id FROM inventories;
SELECT product_id FROM inventoriesINTERSECTSELECT product_id FROM order_items;
SELECT product_id FROM inventoriesMINUSSELECT product_id FROM order_items;
![Page 38: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/38.jpg)
39
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
![Page 39: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/39.jpg)
40
Cosa accade a una istruzione SQL in una istanza
![Page 40: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/40.jpg)
41
Transazioni … ACID
AtomicitàConsistenzaIsolamento
Durability (persistenza)
![Page 41: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/41.jpg)
42
Database Transactions
Consist of one of the following statements:DML statements that make up one consistent change to
the dataOne DDL statementOne DCL statement
![Page 42: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/42.jpg)
43
Database Transactions
Begin when the first executable SQL statement is executed
End with one of the following events:COMMIT or ROLLBACK is issuedDDL or DCL statement executes (automatic commit)User exitsSystem crashes
![Page 43: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/43.jpg)
44
DELETE
Controlling Transactions
Transaction
Savepoint A
ROLLBACK to Savepoint B
DELETE
Savepoint B
COMMIT
INSERTUPDATE
ROLLBACK to Savepoint A
INSERTUPDATEINSERT
ROLLBACK
INSERT
![Page 44: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/44.jpg)
45
An automatic commit occurs under the following circumstances:
DDL statement is issuedDCL statement is issuedNormal exit from SQL*Plus, without explicitly issuing
COMMIT or ROLLBACKAn automatic rollback occurs under an abnormal
termination of SQL*Plus or a system failure.
Implicit Transaction Processing
![Page 45: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/45.jpg)
46
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.
![Page 46: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/46.jpg)
47
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.
![Page 47: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/47.jpg)
48
Committing Data
SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782;1 row updated.
Make the changes.
• Commit the changes.
SQL> COMMIT;Commit complete.
![Page 48: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/48.jpg)
49
State of the Data After ROLLBACKDiscard 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.
![Page 49: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/49.jpg)
50
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.
![Page 50: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/50.jpg)
51
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.
![Page 51: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/51.jpg)
52
Read Consistency
Read consistency guarantees a consistent view of the data at all times.
Changes made by one user do not conflict with changes made by another user.
Read consistency ensures that on the same data:Readers do not wait for writersWriters do not wait for readers
![Page 52: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/52.jpg)
53
Locking
Oracle locks:Prevent destructive interaction between concurrent
transactionsRequire no user actionAutomatically use the lowest level of restrictivenessAre held for the duration of the transactionHave two basic modes:
ExclusiveShare
![Page 53: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/53.jpg)
54
Isolamento,consistenza e concorrenza
Isolation Level Dirty Read Nonrepeatable Read Phantom Read
Read uncommitted Possible Possible PossibleRead committed Not possible Possible PossibleRepeatable read Not possible Not possible PossibleSerializable Not possible Not possible Not possible
![Page 54: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/54.jpg)
55
Read committed : statement consistency on OracleTransaction 2
SQL> update accounts set balance = 100 where account = 1;1 row updated. SQL> update accounts set balance = 500 where account = 30;1 row updated. SQL>
SQL> Commit; Commit complete.
Transaction 1SQL> select balance from accounts where account = 1; BALANCE ---------- 500 SQL> select balance from accounts where account = 2; BALANCE ---------- 200
SQL> select balance from accounts where account = 30; BALANCE ---------- 100
SQL> select balance from accounts where account = 30; BALANCE ---------- 500SQL>
![Page 55: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/55.jpg)
56
Read committed : statement consistency and locks on SQLTransaction 2
begin transaction update accounts set balance = 100 where account = 1; update accounts set balance = 500 where account = 30; (1 row(s) affected) (1 row(s) affected)
Commit transaction Commit complete.
Transaction 1begin transaction select * from accounts where account = 1;select * from accounts where account = 2; account balance ------------------ ---------------- 1 500 (1 row(s) affected)
account balance ------------------ ---------------- 2 200 (1 row(s) affected)
select * from accounts where account = 30;
account balance ------------------ ---------------- 30 500 (1 row(s) affected)
![Page 56: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/56.jpg)
57
Read committed : exclusive locks on OracleTransaction 2
SQL> update accounts set balance = 100 where account = 1;1 row updated. SQL> update accounts set balance = 700 where account = 30;
1 row updated. SQL>
Transaction 1SQL> update accounts set balance = 500 where account = 30;1 row updated. SQL> select balance from accounts where account = 2; BALANCE ---------- 200
SQL> Commit; Commit complete.SQL> select balance from accounts where account = 30; BALANCE ---------- 500SQL>
![Page 57: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/57.jpg)
58
Read committed : exclusive locks on OracleTransaction 2
SQL> update accounts set balance = 100 where account = 1;1 row updated. SQL> update accounts set balance = 700 where account = 30;
1 row updated. SQL>
Transaction 1SQL> update accounts set balance = 500 where account = 30;1 row updated. SQL> select balance from accounts where account = 2; BALANCE ---------- 200
SQL> Commit; Commit complete.SQL> select balance from accounts where account = 30; BALANCE ---------- 500SQL>
![Page 58: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/58.jpg)
59
Deadlocks
![Page 59: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/59.jpg)
60
Come fa Oracle ?
![Page 60: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/60.jpg)
61
Gestione sessioni serializable
![Page 61: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/61.jpg)
62
![Page 62: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/62.jpg)
63
Architettura Oracle
![Page 63: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/63.jpg)
64
1 - Oracle Server Architecture Overview
User Process
Server Process
Other Files...Parameter,Password,Archive Logs
PGA
Oracle Database Data files
Redo Log Files
Control Files
DatabaseBufferCache
Shared Pool
RedoLogBuffer
SGA (System Global Area)
DBWR LGWR SMON PMON CKPT RECO
ARCH LCKn Pnnn Dnnn SNPn
Oracle Instance
Java Pool(optional)
Large Pool(optional)
![Page 64: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/64.jpg)
65
Client Oracle
![Page 65: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/65.jpg)
66
Connessione applicazioni
![Page 66: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/66.jpg)
67
Tablespace
![Page 67: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/67.jpg)
68
Oggetti Oracle
• Tablespace• Tabelle (tipi di tabelle) • Constraints ( vincoli )• Sequences• Triggers• Viste• Viste Materializzate• Indici (vari tipi di indice)• Funzioni• Procedure• Packages• DB link• Directories• Tipi• Autorizzazioni : Grants, ruoli e accenni di VPD• Sinonimi• …
![Page 68: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/68.jpg)
69
Tipi di base Oracle : la regola d’oro
NUMBER(P,S) VARCHAR(S)
DATE
27 15,325,883
32E12
15/3/2013 27/07/1972
31/12/2003 19:30
TestoPaolo
Alfa34
LOB
![Page 69: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/69.jpg)
70
Il Data Dictionary
![Page 70: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/70.jpg)
71
Tabelle
• CREATE TABLE• DDL • ALTER TABLE• DML • INDICI
![Page 71: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/71.jpg)
72
DML
• SELECT …• INSERT …• UPDATE …• DELETE …• MERGE …
INSERT INTO HR.TESTTABLE (AAA,BBB) VALUES (100, 'PROVA');
UPDATE HR.TESTTABLE SET BBB = 'PROVA‘WHERE AAA = 100;
DELETE HR.TESTTABLE WHERE AAA = 100;
![Page 72: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/72.jpg)
73
Bulk operations
• Merge ( DML ) • Insert into select ( DML ) • Create table as ( DDL ) • Truncate ( DDL )
MERGE INTO dest_tab a USING src_tab b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status);
INSERT INTO dest_tab a (object_id,status ) SELECT (object_id,status ) FROM src_tab b WHERE object_id > 500;
![Page 73: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/73.jpg)
74
High Water Mark
![Page 74: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/74.jpg)
75
Create tableCREATE TABLE RICHIESTA( ID_RICHIESTA NUMBER(7), DATA_RICHIESTA DATE, RICHIEDENTE VARCHAR2(300 BYTE), NOMINATIVO VARCHAR2(300 BYTE), PERIODO_DA DATE, PERIODO_A DATE, SOC_APPARTENENZA VARCHAR2(300 BYTE), LUOGO VARCHAR2(300 BYTE), MOTIVO VARCHAR2(300 BYTE), NR_RDA VARCHAR2(50 BYTE))TABLESPACE ARCHIMEDE
![Page 75: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/75.jpg)
76
Indici
![Page 76: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/76.jpg)
77
Come è fatto un indice ?
![Page 77: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/77.jpg)
78
Creazione indici
Cause problemi lentezza query0
1020304050607080
Bad IndexingStale statsBad SQLNeed parti-tioning/PXOthers
CREATE INDEX emp_ename ON emp(ename,esurname) TABLESPACE users;
![Page 78: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/78.jpg)
79
Bitmap Indexes
![Page 79: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/79.jpg)
80
Viste
CREATE VIEW staff ASSELECT employee_id,last_name,job_id,manager_id,department_idFROM employeesWHERE status=1;
![Page 80: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/80.jpg)
81
Inline viewsSELECT * FROM ( SELECT … ) a INNER JOIN ( SELECT … ) b ON (a.object_id = b.object_id)WHERE b.fa IN ( SELECT fa FROM …)AND …
WITHsum_sales AS ( select /*+ materialize */ sum(quantity) all_sales from stores ),number_stores AS ( select /*+ materialize */ count(*) nbr_stores from stores ),sales_by_store AS ( select /*+ materialize */ store_name, sum(quantity) store_sales from store natural join sales )SELECT store_nameFROM store, sum_sales, number_stores, sales_by_storewhere store_sales > (all_sales / nbr_stores);
![Page 81: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/81.jpg)
82
Viste Materializzate
![Page 82: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/82.jpg)
83
Constraints
TIPI DI VINCOLI• Primary Key• Unique• Forign Key• Not Null• Check
![Page 83: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/83.jpg)
84
Contraints ( how to create them )
ALTER TABLE DEPARTMENTS ADD ( CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID));
ALTER TABLE EMPLOYEES ADD ( CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS (DEPARTMENT_ID));
ALTER TABLE EMPLOYEES ADD ( CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID));
ALTER TABLE EMPLOYEES ADD ( CONSTRAINT EMP_EMAIL_UK UNIQUE (EMAIL));
ALTER TABLE EMPLOYEES ADD ( HIRE_DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL);
ALTER TABLE EMPLOYEES ADD ( CONSTRAINT EMP_SALARY_MIN CHECK (salary > 0));
![Page 84: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/84.jpg)
85
Distributed DB
![Page 85: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/85.jpg)
86
Programmability
• PL/SQL , T-SQL • Triggers• Functions • Procedures
![Page 86: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/86.jpg)
87
Bakup
![Page 87: Le BASI DI DATI : le fondamenta dei moderni sistemi Software](https://reader037.vdocumenti.com/reader037/viewer/2022102912/56816903550346895de016ce/html5/thumbnails/87.jpg)
88
Posso fermare il DB ?