Gestionnaire de base de données SQL Server 2008

Microsoft SQL Server est un système de gestion de base de données (abrégé en SGBD ou SGBDR pour « Système de gestion de base de données relationnelles ») développé et commercialisé par la société Microsoft.

Pour les requêtes, SQL Server utilise T-SQL (Transact-SQL), il s'agit d'une implémentation de SQL qui prend en charge les procédures stockées et les déclencheurs (trigger).
SQL Server est un SGBD relationnel. Il est possible de définir des relations entre les tables de façon à garantir fortement l'intégrité des données qui y sont stockées. Ces relations peuvent être utilisées pour modifier ou supprimer en chaîne des enregistrements liés.
SQL Server est un SGBD transactionnel. Il est capable de préparer des modifications sur les données d'une base et de les valider ou de les annuler d'un bloc. Cela garantit l'intégrité des informations stockées dans la base.

Les bases de données sont contenues physiquement dans des fichiers. Les fichiers portent généralement les extensions :

• MDF (Main Database File) pour le premier fichier de données
• NDF (Next Database File) pour les autres fichiers de données
• LDF (Log Database File) pour les fichiers du journal de transaction

Les fichiers de données et journaux sont stockés dans :

C:\ProgramFiles\Microsoft\SQLServer\MSSQL10.MSSQL\MSSQL\DATA

• SQL : Structured Query Language
• SGBDR : Systèmes de Gestion de Base de Données Relationnelles

Le Transact SQL est un language de requêtes amélioré par rapport au SQL dont il reprend les bases. Le SQL est le language standard, créé par un ingénieur d'IBM dans les années 70 pour la gestion des SGBDR.

Il existe 3 catégories d'instructions :

Il permet la création, modification et suppression des objets SQL (Base, Tables, Index, Vues, Procédures..)

Il fournit les instructions de création, mise à jour, suppression et extraction des données stockées. Le LMD vise à modifier une ligne caractérisé par des attributs. (Ensemble de n-upplets)

Il permet la gestion des accès aux aux données, des transactions et de la configuration des sessions et des bases.

SQL Server Management Studio est l'interface d'administration, accessible avec l'authentification Windows (compte administrateur) ou avec l'authentification interne (compte sa):

Points forts de l'interface :

• Le shell avec coloration syntaxique et complétion pour intéragir sur la serveur
• La possibilité de créer un schéma relationnel
• L'aide local et en ligne à la programmation

Lors de la définition d'une colonne, on précisera le format d'utilisation de la donnée ainsi que le mode de stockage par le type de colonne.

• La mise en œuvre de l'intégrité des données peut se faire de manière procédurale par les valeurs par défaut (DEFAULT) et les déclencheurs (TRIGGER) ou de manière interactive par les contraintes (CONSTRAINT) et la propriété IDENTITY.
  

Permet de faire générer par le système un ID partant d'une valeur initiale (spécifiée ou 1 par défaut) en augmentant/diminuant ligne après ligne. IDENTITY se définie sur une colonne dans une commande de création (CREATE TABLE) ou de mise à jour (ALTER TABLE)

• Exemple :

CREATE TABLE TEST (
         identifiant int IDENTITY (1000,1),
         label varchar (30)
         )

• La variable @@IDENTITY stocke la dernière valeur affectée par une identié au cours de la session courante.

• Pour pouvoir insérer des données sans utiliser la numérotation automatique, il faut faire appel à l'instruction :

SET IDENTITY_INSERT nom_2_table ON

Spécifie que la colonne doit être valorisé à la création/modification.

• Exemple :

CREATE TABLE TEST (
       nom        varchar (30) not null,
       prenom     varchar (30) 
);

• RAPPEL : la valeur par défaut d'une colonne est NULL (accepte les champs non valorisés)

Cette contrainte permet de définir un identifiant clé primaire, une ou plusieurs colonnes n'acceptant que des valeurs uniques dans la table.

• Exemple :

CREATE TABLE TEST (
       numclient    numeric (6) 
                    CONSTRAINT primary key,
       nom        varchar (30) not null,
       prenom     varchar (30) 
);

• Une PK peut-être définie sur plusieurs colonnes à condition de poser la contrainte sur la table !

CREATE TABLE GRILLETARIFS (
	codeCate	char (5),
	codeFami	char (5),
	codeTarif	char (5),
	-- contraintes de table --
	constraint FK_codeTarif foreign key (codeTarif) references TARIFS(codeTarif),
	constraint PK_grilletarifs primary key (codeCate,codeFami)
);

Cette contrainte permet de traduire la règle d'unicité pour les autres clés uniques d'une table (clé secondaire). Contrairement à une PK, il est possible d'avoir plusieurs UNIQUE par table et les colonnes utilisées peuvent être à NULL (non recommandé).

• Exemple :

CREATE TABLE FAMILLES (
	codeFami	char (5)
				constraint PK_codeFami primary key,
	libelle		varchar (80)
				constraint UN_familles_libelle UNIQUE
);

Cette contrainte traduit l'intégrité référentielle entre une clé étrangère d'une table et une clé primaire (ou secondaire) d'une autre table.

• Exemple :

create table GRILLETARIFS (
	codeCate	char (5),
	codeFami	char (5),
	codeTarif	char (5),
	-- contraintes de table --
	constraint FK_codeDate foreign key (codeCate) references CATEGORIES(codeCate),
	constraint FK_codeFami foreign key (codeFami) references FAMILLES(codeFami),
	constraint FK_codeTarif foreign key (codeTarif) references TARIFS(codeTarif),
	constraint PK_grilletarifs primary key (codeCate,codeFami)
);

L'option de cascade permet de préciser le comportement que doit adopter SQL Server lorsque l'utilisateur met à jour ou tente de supprimer une colonne référencée.
Les clauses ON DELETE et ON UPDATE sont suivies d'une de ces options :

• NO ACTION
• ON DELETE CASCADE
• ON UPDATE CASCADE
• SET NULL
• SET DEFAULT

Cette contrainte permet de préciser la valeur qui va être positionnée dans la colonne si aucune information n'est précisée lors de l'insertion de la ligne.

• Exemple :

CREATE TABLE LIGNESFIC (
	nofic		numeric (6) not null,
	nolig		numeric (3) not null,
	refart		char (8),
	depart		datetime not null default getdate(),
	retour		datetime null,
	-- contraintes de table --
	constraint PK_LIGNESFIC primary key (nofic,nolig),
	constraint FK_nofic foreign key (nofic) references FICHES(nofic),
	constraint FK_refart foreign key (refart) references ARTICLES(refart),
	constraint CK_depart check (retour is NULL or retour >= depart)

La contrainte de validation permet de définir des règles mettant en rapport des valeurs issues de différentes colonnes de la même ligne. Ce type de contrainte permet aussi de s'assurer que les données respectent un format d'entrée précis lors de l'insertion et mise à jour.

• Exemple:

CREATE TABLE FICHES (
	nofic		numeric (6) constraint PK_nofic primary key,
	nocli		numeric (6) not null,
	datcreation	datetime not null default getdate(),
	datretour	datetime null, 	
	etat		char (2) not null
				constraint CHOIX_etat check (etat in ('EC', 'RE', 'SO'))
				constraint DF_etat default 'EC',
	-- contraintes de table --
	constraint FK_nocli foreign key (nocli) references CLIENTS(nocli),
	constraint CK_date check (datretour is NULL or datretour >= datcreation),
	constraint CK_fiches_date_etat CHECK(datretour is NULL and etat = 'EC'
									OR datretour is not NULL and etat <> 'EC')
);

• Schéma relationnel

• Script de création de base et tables

TP.sql

/****************************************/
/*         SQL Server 2008              */
/*       Création bdd et tables         */
/****************************************/
-- définition de la database --
CREATE DATABASE TP_sport ;
 
GO
 
-- séléction de la table --
USE TP_sport;
 
GO
 
-- définition des tables --
 
-- création table CLIENTS --
CREATE TABLE CLIENTS (
	nocli		numeric (6) constraint PK_nocli PRIMARY KEY,
	nom			char (30) NOT NULL,
	prenom		varchar (30) NOT NULL,
	adresse		varchar (120) NOT NULL,
	cpo			char (5) NOT NULL,
	ville       char (80) NOT NULL constraint DF_ville DEFAULT 'NANTES',
	-- contraintes de table --
	constraint CK_cpo CHECK (cpo BETWEEN 1000 AND 95999)
);
 
GO
 
-- création table FICHES --
CREATE TABLE FICHES (
	nofic		numeric (6) constraint PK_nofic PRIMARY KEY,
	nocli		numeric (6) NOT NULL,
	datcreation	datetime NOT NULL DEFAULT getdate(),
	datretour	datetime NULL, 	
	etat		char (2) NOT NULL
				constraint CHOIX_etat CHECK (etat IN ('EC', 'RE', 'SO'))
				constraint DF_etat DEFAULT 'EC',
	-- contraintes de table --
	constraint FK_nocli FOREIGN KEY (nocli) REFERENCES CLIENTS(nocli),
	constraint CK_date CHECK (datretour IS NULL OR datretour >= datcreation),
	constraint CK_fiches_date_etat CHECK(datretour IS NULL AND etat = 'EC'
									OR datretour IS NOT NULL AND etat <> 'EC')
);
 
GO
 
-- création table CATEGORIES --
CREATE TABLE CATEGORIES (
	codeCate	char (5)
				constraint PK_codeDate PRIMARY KEY,
	libelle		varchar (80)
				constraint UN_categories_libelle UNIQUE
);
 
GO
 
-- création table FAMILLE --
CREATE TABLE FAMILLES (
	codeFami	char (5)
				constraint PK_codeFami PRIMARY KEY,
	libelle		varchar (80)
				constraint UN_familles_libelle UNIQUE
);
 
GO
 
-- création table TARIFS --
CREATE TABLE TARIFS (
	codeTarif	char (5) NOT NULL
				constraint PK_codeTarif PRIMARY KEY,
	libelle		varchar (80) NOT NULL,
	codeDevise	char (2) NOT NULL,
	prixJour	numeric (10,2) NOT NULL
				constraint CK_prixJour CHECK (prixJour >= 0)
);
 
GO
 
-- création table GRILLETARIFS --
CREATE TABLE GRILLETARIFS (
	codeCate	char (5),
	codeFami	char (5),
	codeTarif	char (5),
	-- contraintes de table --
	constraint FK_codeDate FOREIGN KEY (codeCate) REFERENCES CATEGORIES(codeCate),
	constraint FK_codeFami FOREIGN KEY (codeFami) REFERENCES FAMILLES(codeFami),
	constraint FK_codeTarif FOREIGN KEY (codeTarif) REFERENCES TARIFS(codeTarif),
	constraint PK_grilletarifs PRIMARY KEY (codeCate,codeFami)
);
 
GO
 
-- création table ARTICLES --
CREATE TABLE ARTICLES (
	refart		char (8)
				constraint PK_refart PRIMARY KEY,
	designation	varchar (80),
	codeCate	char (5),
	codeFami	char (5),
	-- contraintes de table --
	constraint FK_grilletarifs FOREIGN KEY (codeCate,codeFami) REFERENCES GRILLETARIFS(codeCate,codeFami)
);
 
Go
 
-- création table LIGNESFIC --
CREATE TABLE LIGNESFIC (
	nofic		numeric (6) NOT NULL,
	nolig		numeric (3) NOT NULL,
	refart		char (8),
	depart		datetime NOT NULL DEFAULT getdate(),
	retour		datetime NULL,
	-- contraintes de table --
	constraint PK_LIGNESFIC PRIMARY KEY (nofic,nolig),
	constraint FK_nofic FOREIGN KEY (nofic) REFERENCES FICHES(nofic),
	constraint FK_refart FOREIGN KEY (refart) REFERENCES ARTICLES(refart),
	constraint CK_depart CHECK (retour IS NULL OR retour >= depart)
);