Basi di dati (2)

Università di Modena e Reggio Emilia
Basi di dati (2)
Docente: Andrea Bulgarelli
[email protected]
Argomento: tabelle (1.0)
Andrea Bulgarelli
1
Panoramica
  Tabelle
 Creazione
 Modifica
 Cancellazione
  Esempio
Clienti/Ordini: creazione tabelle
(create e alter table)
  Tabelle temporanee e di sistema
Andrea Bulgarelli
2
Creazione di tabelle
  In
SQL server si possono creare tabelle
sia con Enterprise Manager che con
Query Analyser
database.owner.tablename
Andrea Bulgarelli
3
Descrizione del comando create
table (1)
CREATE TABLE
[
database_name.[owner].
| owner.
] table_name
(
{
<column_definition>
| column_name AS computed_column_expression
| <table_constraint>
} [,...n]
)
[ON {filegroup | DEFAULT} ]
[TEXTIMAGE_ON {filegroup | DEFAULT} ]
Andrea Bulgarelli
4
Descrizione del comando create
table (2)
<table_constraint> ::= [CONSTRAINT constraint_name]
{
[ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED]
{ ( column[,...n] ) }
[ WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]
]
| FOREIGN KEY
[ ( column[,...n] ) ]
REFERENCES ref_table
[ ( ref_column[,...n] ) ]
[NOT FOR REPLICATION]
| CHECK [NOT FOR REPLICATION]
( search_conditions )
}
Andrea Bulgarelli
5
Descrizione del comando create
table (3)
<column_definition> ::= { column_name data_type }
[ [ DEFAULT constant_expression ]
| [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ]
]
[ ROWGUIDCOL ]
[ <column_constraint> ] [ ...n]
<column_constraint> ::= [CONSTRAINT constraint_name]
{
[ NULL | NOT NULL ]
| [
{ PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED]
[WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]]
]
| [
[FOREIGN KEY]
REFERENCES ref_table [(ref_column) ]
[NOT FOR REPLICATION]
]
| CHECK [NOT FOR REPLICATION]
(logical_expression)
Andrea Bulgarelli
}
6
Denominazione
 
 
 
 
Si usano un massimo di 128 lettere, caratteri numerici, o
simboli (#, $, @, _)
definire una regola generale per la denominazione degli
oggetti (tabelle, attributi, procedure, etc) è una buona
cosa
Attenzione alle parole riservate (select, create, update,
ed in genere i termini in SQL92)
Gli identificatori possono essere delimitati
Andrea Bulgarelli
7
Tipi di dati
 
Cinque gruppi di tipi di dato
 
Numerici
 
 
Caratteri
 
 
 
 
datetime, smalldatetime
BLOB (large object)
Vari
 
 
Char, varchar, nchar, nvarchar (Unicode)
Tempi (data e ora)
 
 
bigint, int, smallint, money, smallmoney, float,
real
binary, bit, rowversion (era timestamp), table,
uniqueidentifier
Il tipo sql_variant può assumere connotazioni diverse
a tempo di esecuzione
C’è un tipico trade-off tra tipi di dimensioni costanti e tipi
di dimensione variabile
Andrea Bulgarelli
8
Synonym
binary varying
char varying
character
character(n)
character varying(n)
dec
double precision
float[(n)] for n = 1 to 7
float[(n)] for n = 8 to 15
integer
national character(n)
national char(n)
national character varying(n)
national char varying(n)
national text
numeric
Andrea Bulgarelli
Mapped to syst datatype
varbinary
varchar
char(1)
char(n)
varchar(n)
decimal
float
real
float
int
nchar(n)
nchar(n)
nvarchar(n)
nvarchar(n)
ntext
decimal
9
Andrea Bulgarelli
10
Andrea Bulgarelli
11
I problemi dei valori NULL
 
 
 
 
Il valore NULL indica assenza di informazione
Permettere la presenza di valori NULL comporta
un onere maggiore per il DBMS, in particolare il
motore di archiviazione e le applicazioni
Se possibile il DBMS non gestisce il NULL; è
tipico usare colonne NOT NULL, assegnando
valori predefiniti per classificare le voci mancanti
o sconosciute
L’importante però è che il modello logico sia
rispettato
Andrea Bulgarelli
12
Tipi di dati definiti dagli utenti
  Si
possono definire tipi di dato derivati per
garantire un uso coerente dei tipi nativi
all’interno del DB e della applicazione
Andrea Bulgarelli
13
La proprietà IDENTITY
 
 
 
Le tabelle che non hanno una chiave primaria
naturale vengono in genere dotate di un
elemento di tipo contatore
Sintassi: IDENTITY [ (val_iniziale,incremento)
Esempio:
]
CREATE TABLE customer
(
cust_id
integer IDENTITY NOT NULL,
cust_name
varchar(50) NOT NULL
)
Andrea Bulgarelli
14
La proprietà IDENTITY (2)
 
 
 
 
Tipicamente accade che i valori inseriti nel
campo IDENTITY non siano consecutivi
Esempio tipico: più transazioni avviate, con
qualcuna in seguito non completata
Altro esempio: cancellazione dei record
Si può controllare l’ultimo valore assegnato per
una tabella:
SELECT IDENT_CURRENT('customer')
Andrea Bulgarelli
15
Vincoli
 
 
 
 
 
 
I vincoli contribuiscono a mantenere nel sistema l’integrità dei dati e
delle relazioni tra loro
Ad un vincolo può essere dato un nome con CONSTRAINT
Integrità di tabella (primo vincolo di integrità): esistenza di una
PRIMARY KEY o UNIQUE, a cui corrisponde la creazione di indici.
Un indice è una struttura all’interno del database che consente un
accesso rapido ai dati. Di solito il database costruisce sempre un
indice associato alla primary key
Integrità di dominio: i dati soddisfano a condizioni specifiche (e.g. di
formato, di appartenenza ad un insieme predefinito); cfr. CHECK
Valori di default: Crea un oggetto definito valore predefinito: se non
viene specificato nulla, viene inserito nella colonna il valore
specificato dal vincolo. Vedi DEFAULT
Integrità referenziale (secondo vincolo di integrità): relazioni tra dati
in tabelle distinte; cfr. FOREIGN KEY
Andrea Bulgarelli
16
Vincolo PRIMARY KEY
 
 
 
 
Nel modello relazionale ogni riga di una tabella è unica e
distinguibile da tutte le altre
Più attributi possono essere identificatori unici degli
Un indice cluster determina
l'ordinamento fisico dei dati in
elementi in una tabella, ma la PRIMARY KEY deve
una tabella. Un indice cluster
essere unica (gli altri sono ALTERNATE o
ha un funzionamento analogo
CANDIDATE KEYS)
a quello di un elenco
telefonico che dispone i dati
È raccomandato (ma non obbligatorio) definire una
per cognome. Poiché l'indice
PRIMARY KEY in ogni tabella
cluster stabilisce l'ordine di
archiviazione fisico dei dati
Viene creato per default un indice CLUSTERED
CREATE TABLE customer (
cust_id
int
cust_name
varchar(30)
PRIMARY KEY,
NOT NULL )
CREATE TABLE customer (
cust_id
int,
cust_name
varchar(30)
NOT NULL,
nella tabella, una tabella può
includere un unico indice
cluster. Tuttavia, l'indice può
includere più colonne (un
indice composto), così come
l'elenco telefonico è
organizzato per cognome e
nome.
CONSTRAINT PK_customer PRIMARY KEY(cust_id) ) Un indice cluster è
particolarmente efficiente in
colonne in cui vengono
eseguite ricerche di intervalli
di valori.
Andrea Bulgarelli
17
Vincolo UNIQUE
  Viene
creato per default un indice
UNCLUSTERED
CREATE TABLE customer_location (
cust_id
int
NOT NULL,
cust_location_number int
NOT NULL,
CONSTRAINT UNQ_customer_location
UNIQUE (cust_id, cust_location_number)
)
Andrea Bulgarelli
18
Vincolo FOREIGN KEY
CREATE TABLE customer (
cust_id
int
NOT NULL IDENTITY
PRIMARY KEY,
cust_name
varchar(50)
NOT NULL )
CREATE TABLE orders (
order_id
int
NOT NULL IDENTITY
PRIMARY KEY,
cust_id
int
NOT NULL
REFERENCES customer
(cust_id) )
Andrea Bulgarelli
19
CREATE TABLE customer
(
cust_id
int
NOT NULL,
location_num
smallint
NULL,
cust_name
varchar(50)
NOT NULL,
CONSTRAINT UNQ_customer UNIQUE (location_num, cust_id)
)
CREATE TABLE orders
(
order_id
int
NOT NULL
IDENTITY CONSTRAINT ORDER_PK
PRIMARY KEY NONCLUSTERED,
cust_num
int
NOT NULL,
cust_loc
smallint
NULL,
CONSTRAINT FK_ORDER_CUSTOMER FOREIGN KEY (cust_loc, cust_num)
REFERENCES customer (location_num, cust_id)
)
Andrea Bulgarelli
20
Vincolo CHECK
CREATE TABLE employee (
emp_id
int
NOT NULL
PRIMARY KEY
CHECK (emp_id BETWEEN 0 AND 1000),
emp_name varchar(30) NOT NULL
CONSTRAINT no_nums
CHECK (emp_name NOT LIKE '%[0-9]%'),
mgr_id
int
NOT NULL
REFERENCES employee(emp_id),
in_date
datetime
NOT NULL
CHECK (in_date >= CURRENT_TIMESTAMP)
)
Per aggiungere aggiornamenti o cancellazioni in cascata aggiungere on update
cascade on delete cascade. Esempio:
REFERENCES employee(emp_id) on update cascade
EXEC
sp_helpconstraint employee
Andrea Bulgarelli
21
Modifica di una tabella
 
 
Le modifiche alla struttura di una tabella (ins/mod/canc di
una colonna; ins/mod/canc di un vincolo; attiv/disattiv di
un vincolo) si applicano utilizzando il comando ALTER
TABLE
Non tutte le modifiche sono lecite (lista completa in
SQLBOL):
 
 
 
 
 
Se una colonna è ROWGUIDCOL, se ne può solo fare DROP
La colonna non può essere di alcuni tipi (eg text)
Non può essere una colonna replicata o calcolata
Non può avere un vincolo di PRIMARY o FOREIGN KEY
Il tipo non può divenire timestamp
Andrea Bulgarelli
22
ALTER TABLE
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision[, scale] ) ]
[ NULL | NOT NULL ]
| {ADD | DROP} ROWGUIDCOL
}
]
| ADD { [ <column_definition> ]
| column_name AS computed_column_expression
}[,...n]
| [WITH CHECK | WITH NOCHECK] ADD
{ <table_constraint> }[,...n]
| DROP
{ [CONSTRAINT] constraint_name
| COLUMN column
}[,...n]
| {CHECK | NOCHECK} CONSTRAINT
{ALL | constraint_name[,...n]}
| {ENABLE | DISABLE} TRIGGER
{ALL | trigger_name[,...n]}
}
Andrea Bulgarelli
23
Esempio: Mxx_Ordini
Andrea Bulgarelli
24
CREATE TABLE [dbo].[CLIENTE] (
[CF] [char] (16) NOT NULL ,
[Nome] [varchar] (50) NULL ,
[Cognome] [varchar] (50) NULL ,
[Indirizzo] [varchar] (100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ORDINE] (
[Numero] [int] NOT NULL ,
[Anno] [smallint] NOT NULL ,
[Data] [smalldatetime] NOT NULL ,
[CF] [char] (16) NOT NULL ,
[id] [int] IDENTITY (1, 1) NOT NULL
ON PRIMARY
significa che la
tabella viene
salvata nel file
primario del
database.
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PRODOTTO] (
[Codice] [char] (6) NOT NULL ,
[Descrizione] [varchar] (200) NOT NULL ,
[Quantita_Magazzino] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PRODOTTO_ORDINE] (
[Codice_Prodotto] [char] (6) NOT NULL ,
[Numero_Ordine] [int] NOT NULL ,
[Anno] [smallint] NOT NULL ,
[Quantita] [int] NOT NULL
) ON [PRIMARY]
Andrea Bulgarelli
GO
25
ALTER TABLE [dbo].[CLIENTE] WITH NOCHECK ADD
CONSTRAINT [PK_CLIENTE] PRIMARY KEY
NONCLUSTERED
(
[CF]
)
ON [PRIMARY] ,
CONSTRAINT [CK_CLIENTE] CHECK (([CF] like '[A-Z][A-Z][AZ][A-Z][A-Z][A-Z][0-9][0-9][A-Z][0-9][0-9][A-Z][0-9][0-9][0-9]
[A-Z]'))
GO
 
WITH NOCHECK: non applicare il vincolo ai dati
esistenti
Andrea Bulgarelli
26
ALTER TABLE [dbo].[ORDINE] WITH NOCHECK ADD
CONSTRAINT [PK_ORDINE] PRIMARY KEY
NONCLUSTERED
(
[Numero],
[Anno]
)
ON [PRIMARY] ,
CONSTRAINT [AK_ORDINE] UNIQUE
NONCLUSTERED
(
[id]
)
ON [PRIMARY] ,
CONSTRAINT [CK_ORDINE] CHECK ([numero] > 0),
CONSTRAINT [CK_ORDINE_1] CHECK (datepart(year,[data]) =
[anno]),
CONSTRAINT [CK_ORDINE_2] CHECK ([Anno] > 1990 and [anno]
< 2050)
GO
Andrea Bulgarelli
27
ALTER TABLE [dbo].[PRODOTTO] WITH NOCHECK ADD
CONSTRAINT [DF_PRODOTTO_Quantita_Magazzino] DEFAULT (0) FOR
[Quantita_Magazzino],
CONSTRAINT [PK_PRODOTTO] PRIMARY KEY
NONCLUSTERED
(
[Codice]
)
ON [PRIMARY] ,
CONSTRAINT [CK_PRODOTTO] CHECK ([quantita_magazzino] >= 0)
GO
ALTER TABLE [dbo].[PRODOTTO_ORDINE] WITH NOCHECK ADD
CONSTRAINT [DF_PRODOTTO_ORDINE_Quantita] DEFAULT (0) FOR [Quantita],
CONSTRAINT [PK_PRODOTTO_ORDINE] PRIMARY KEY
NONCLUSTERED
(
[Codice_Prodotto],
[Numero_Ordine],
[Anno]
)
ON [PRIMARY] ,
CONSTRAINT [CK_PRODOTTO_ORDINE] CHECK ([quantita] >= 0)
Andrea Bulgarelli
GO
28
ALTER TABLE [dbo].[ORDINE] ADD
CONSTRAINT [FK_ORDINE_CLIENTE] FOREIGN KEY
(
[CF]
) REFERENCES [dbo].[CLIENTE] (
[CF]
)
GO
ALTER TABLE [dbo].[PRODOTTO_ORDINE] ADD
CONSTRAINT [FK_PRODOTTO_ORDINE_ORDINE] FOREIGN KEY
(
[Numero_Ordine],
[Anno]
) REFERENCES [dbo].[ORDINE] (
[Numero],
[Anno]
),
CONSTRAINT [FK_PRODOTTO_ORDINE_PRODOTTO] FOREIGN KEY
(
[Codice_Prodotto]
) REFERENCES [dbo].[PRODOTTO] (
[Codice]
)
Andrea Bulgarelli
GO
29
Eliminazione di una tabella o di
un constraint
 
 
L’eliminazione della tabella comprende la
rimozione di tutti i dati, le definizioni, gli indici, i
trigger, i permessi specifici per la tabella
Sintassi:
DROP TABLE
 
nome-tabella
Eliminazione di un constraint
ALTER TABLE nometabella DROP CONSTRAINT
nome-constraint
Andrea Bulgarelli
30