Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

BASES DE DONNEES AVANCEESSGBDR 18/10/2022 Pr. OUBEDDA LATIFA Année Universitair

BASES DE DONNEES AVANCEESSGBDR 18/10/2022 Pr. OUBEDDA LATIFA Année Universitaire 2017/2018ÉCOLE SUPÉRIEURE DE TECHNOLOGIE –AGADIRFilières : Génie Informatique CHAPITRE:I langage Description de Données 1. SQL server SQL-Server est un SGBDR Client-Serveur qui utilise TRANSACT- SQL dans ses transactions. Les bases de données contiennent un certain nombre d’objets logiques. Il est possible de regrouper ces objets en trois grandes catégories: • Gestion et stockage des données : tables, type de données, contraintes d’intégrité, valeur par défaut, règles et index. • Accès aux données : vues et procédures stockées. • Gestion de l’intégrité complexe: déclencheur (Trigger) 18/10/2022 2 Création d’une base de données La création d’une base de données est une étape ponctuelle, réalisée par un administrateur SQL Server. Avant de créer une base de données, il est important de définir un certain nombre d’éléments de façon précise : • Le nom de la base de données qui doit être unique sur le serveur SQL, • La taille de la base de données, • Les fichiers utilisés pour le stockage des données. Une base peut être créée de deux façons différentes : • Par l’intermédiaire de l’instruction Transact SQL CREATE DATABASE; • Par l’intermédiaire de SQL Server Management Studio 18/10/2022 Syntaxe : CREATE DATABASE nom_bd ON ( NAME = nom logique de fichier, FILENAME = chemin de fichier .mdf, SIZE = taille en MB , MAXSIZE = taille en MB , FILEGROWTH = pas d’ incrément en MB ou % ) LOG ON ( NAME = nom logique de fichier log, FILENAME = chemin de fichier log .ldf, SIZE = taille en MB , MAXSIZE = taille en MB , FILEGROWTH = pas d’ incrément en MB ou % ) 18/10/2022 Exemple: Créer une base de données nommée « BD-inscription". Cette base aura les caractéristiques suivantes: Un fichier de données: • taille initiale: 3 Mo • croissance du fichier: 20 % • taille maximale: 10 Mo Un fichier "Journal": • taille initiale: 1 Mo • croissance du fichier: 10% • taille maximale: illimité 18/10/2022 Solution • CREATE DATABASE BD-inscription • ON ( NAME = 'inscription_Data', • FILENAME = 'C :\inscription_Data.mdf' , • SIZE = 3MB , • MAXSIZE = 10MB , • FILEGROWTH = 20%) • LOG ON ( NAME = 'inscription_log', • FILENAME = 'C:\inscription_log.ldf' , • SIZE = 1MB , • MAXSIZE = UNLIMITED, • FILEGROWTH = 10%); 18/10/2022 3. les tables , View 3.1 création des tables: Syntaxe : CREATE TABLE nom_table ( champ type , ....) Exemple : Créer les tables suivantes : Etudiant( code , nom , prenom , date_naiss , note, numCla) Classe (numCla, libelle ) Solution : CREATE TABLE Etudiant( code int, nom varchar(10) , prenom varchar(20) , date_naiss date , note decimal(4,2) , numCla int) CREATE TABLE Classe (numCla int, libelle varchar(10)) 18/10/2022 3.2 les contraintes d’intégrités • Contraintes de clé primaire : Syntaxe : ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte PRIMARY KEY (nom_champ); Exemple : Le champ code de la table Etudiant Solution: ALTER TABLE Etudiant ADD CONSTRAINT pk_code PRIMARY KEY (code); 18/10/2022 Contraintes de clés étrangères : Syntaxe : ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte FOREIGN KEY (nom_champ) REFERENCES nom_table(champ); Exemple : Le champ numCla de la table etudiant Solution: ALTER TABLE Etudiant ADD CONSTRAINT fk_numClasse FOREIGN KEY (numCla) REFERENCES Classe ( numCla); 18/10/2022 Contraintes de validation Les contraintes de validations (CHECK) vont permettre de définir un certain nombre de règles simples de gestion des données. Syntaxe : ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte CHECK ( règles); Exemple : Définir la règle suivante : La note de l’étudiant doit être compris entre 0 et 20 Solution: ALTER TABLE etudiant ADD CONSTRAINT contrainte_note CHECK ( note between 0 and 20 ); 18/10/2022 Les index Les index vont permettre d’accéder plus rapidement aux différentes informations. Syntaxe : CREATE INDEX nom_index ON nom_table(nom_champ); Exemple : Définir un index pour le champ numCla de la table Etudiant Solution: CREATE INDEX ix_numCla ON Etudiant ( numCla); 18/10/2022 Exercice : Dans MS SQL server : • Créer la base de données bd1 • Créer les 3 tables suivantes avec toutes les contraintes possibles etudiant ( NumEtdINT , nom varchar (10), prenom varchar (10),adresse varchar (12),ville varchar (10)) Matiere ( NumMat INT , libelle varchar (10)) Notation ( Num Etd INT, NumMat INT, note decimal (4,2), dateEvaluer DateTime) 18/10/2022 18/10/2022 Pr. OUBEDDA LATIFA Année Universitaire 2017/2018 Solution Create Database bd_scolarite3 Create table etudiant ( NumEtd INT primary Key , nom varchar (10), prenom Varchar (10),adresse varchar (9),ville Varchar (10)) Create table matiere ( NumMat INT primary Key, libelle varchar(10)) Create table notation (NumEtd INT, NumMat INT, note decimal(2,2),dateEvaluer Datetime, Constraint pk_NumEtd_NumMat primary Key( NumEtd, NumMat), Constraint fk_NumEtd foreign key (NumEtd) references etudiant ( NumEtd), Constraint fk_NumMat foreign key(NumMat) references matiere ( NumMat)) Manipulation de données Modification de la table Types de données définis par l’utilisateur Il est possible de définir ses propres types de données, soit par l’intermédiaire de Management Studio, soit par la commande CREATE TYPE. Syntaxe : CREATE TYPE nom_type FROM type_base { null / not null} Avec ALTER on peut modifier les colonnes d’une table ou ajouter de nouvelles colonnes par syntaxe: ALTER TABLE NomTable ADD NomColonne TypeDonnees Exemple : Définir un type de données utilisateur nommé texte de type nvarchar (20) non null et attribuer ce type aux champs nom de la table etudiant? Solution: CREATE TYPE texte FROM nvarchar (20) not null ALTER TABLE Etudiant ALTER COLUMN nom texte 18/10/2022 Les schémas Un schéma est ensemble logique d’objets à l’intérieur d’une base de données à la manière de bibliothèque package ou des espaces de noms. Par exemple : dbo.Etudiant signifier que l’objet table etudiant est liée au schéma dbo. Syntaxe : CREATE SCHEMA mon_Schema Exemple : Créer le schéma ecole. Puis, Ajouter à ce schéma la table professeur ( code , nom ) Solution: CREATE SCHEMA ecole CREATE TABLE ecole.professeur ( code int, nom varchar(8)) 18/10/2022 Séquence Une séquence est un objet lié au schéma défini par l'utilisateur qui génère une séquence de valeurs numériques (exemple : 12,14,16,18,20,...) Syntaxe : Création d’une séquence CREATE SEQUENCE schema_name. sequence_name AS integer_type START WITH <constant> INCREMENT BY <constant> MINVALUE <constant> | NO MINVALUE MAXVALUE < constant> | NO MAXVALUE CYCLE | NO CYCLE Utilisation d’une séquence l’instruction: NEXT VALUE FOR schema_name. Sequence_name permet d’obtenir le numéro séquentiel suivant. On peut attribuer une séquence à un champ en utilisant la contrainte default : ALTER TABLE nom_table ADD CONSTRAINT nom_defaut DEFAULT valeur FOR nom_champ 18/10/2022 Exemple: Créer la séquence seq qui permet de générer une séquence de valeurs numériques (10,12,14,16,...). Puis, attribuer la séquence seq au champ code de la table professeur Solution : 1. CREATE SEQUENCE ecole.seq AS int START WITH 10 INCREMENT BY 2 2. ALTER TABLE ecole.professeur ADD CONSTRAINT def DEFAULT NEXT VALUE FOR ecole.seq FOR code 18/10/2022 18/10/2022 3.3 View Une vue est une table virtuelle, dans laquelle il est possible de rassembler des informations provenant de plusieurs tables. les données ne sont pas stockées dans une table de la base de données. Syntaxe : CREATE VIEW Nom_de_la_Vue (colonnes) AS SELECT ... Exemple: 1. Créer la view v1 qui permet de visualiser les noms et prénoms des étudiants ainsi que ses notes? 2. Afficher les informations de la view v1? Solution : 1. CREATE VIEW v1 AS SELECT nom,prenom,note FROM etudiant e,notation n WHERE e.numetd = n.numetd 2. SELECT * FROM v1 18/10/2022 Pr. OUBEDDA LATIFA Année Universitaire 2017/2018 l'Optimiseur de requête SQL Objectifs Indication des étapes de traitement d'une requête. Distinction entre les arguments de recherche et les arguments hors recherche. Description de la procédure de calcul du coût d'une requête par l'Optimiseur de requête. Description des méthodes de traitement des jointures. ATTENTION : vérifiez bien que la transformation opère une réduction du temps de traitement 18/10/2022 Pr. OUBEDDA LATIFA ÉVITEZ PRÉFÉREZ évitez d'employer l'étoile dans la clause SELECT... Sélectionnez SELECT * FROM T_CLIENT ...préférez nommer les colonnes une à une Sélectionnez SELECT CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM, CLI_ENSEIGNE FROM T_CLIENT n'employez pas de colonne dans la clause SELECT... de la sous requête EXISTS... Sélectionnez SELECT CHB_ID FROM T_CHAMBRE T1 WHERE NOT EXISTS (SELECT CHB_ID FROM TJ_CHB_PLN_CLI T2 WHERE PLN_JOUR = '2000-11-11' AND T2.CHB_ID = T1.CHB_ID) ...utilisez l'étoile ou une constante Sélectionnez SELECT CHB_ID FROM T_CHAMBRE T1 WHERE NOT EXISTS (SELECT * FROM TJ_CHB_PLN_CLI T2 WHERE PLN_JOUR = '2000-11-11' AND T2.CHB_ID = T1.CHB_ID) 18/10/2022 Pr. OUBEDDA LATIFA Année Universitaire 2017/2018 évitez de compter une colonne... Sélectionnez SELECT COUNT (CHB_ID) FROM T_CHAMBRE ...quand-il suffit de compter les lignes Sélectionnez SELECT COUNT (*) FROM T_CHAMBRE évitez les fourchettes < et > pour des valeurs discrètes... Sélectionnez SELECT * FROM T_FACTURE WHERE FAC_DATE > '2000-06-18' AND FAC_DATE < '2000-07-15' ...préférez le BETWEEN Sélectionnez SELECT * FROM T_FACTURE WHERE FAC_DATE BETWEEN '2000-06-18' AND '2000-07-14' évitez d'employer le DISTINCT... Sélectionnez SELECT DISTINCT CLI_NOM, CLI_PRENOM FROM T_CLIENT C JOIN TJ_CHB_PLN_CLI J ON C.CLI_ID = J.CLI_ID WHERE PLN_JOUR = '2000-11-11' ...si une sous requête EXISTS vous offre le dé doublonnage Sélectionnez SELECT CLI_NOM, CLI_PRENOM FROM T_CLIENT C WHERE EXISTS (SELECT * FROM TJ_CHB_PLN_CLI J WHERE C.CLI_ID = J.CLI_ID AND PLN_JOUR = '2000-11-11') uploads/Management/lundi.pdf