Architecture d'une base de données SQL Server PDF

Architecture d’une base de données SQL Server Prof. C. El Amrani Introduction Une base de données peut être définie selon plusieurs points de vu : Administrateur, c'est un ensemble de fichiers contenant des données organisées, qui doivent être sauvegardées, nettoyées, réorga...

Architecture d’une base de données SQL Server Prof. C. El Amrani Introduction Une base de données peut être définie selon plusieurs points de vu : Administrateur, c'est un ensemble de fichiers contenant des données organisées, qui doivent être sauvegardées, nettoyées, réorganisées, sécurisées, etc. Utilisateur, c'est un espace, lui permettant d'enregistrer des informa- tions et de les retrouver quand il en a besoin. Développeur, c'est un ensemble de tables contenant des données, d'index permettant d'accélérer leur récupération et de procédures permettant d'y travailler de la manière la plus rapide et la plus sûre. Une base de données SQL Server est constituée d'au moins deux fichiers : un fichier de données, avec une extension.MDF un fichier de journal avec une extension.LDF Administration SQL Server -- 2024 2 Introduction Une base de donnée SQL Server est composée de plusieurs familles d'objets: Les tables, contenant des données Les index, servant à retrouver, trier, regrouper rapidement les données Les déclencheurs (triggers), permettant d'exécuter des opérations particulières lors de l'insertion, la modification ou la suppression de données Les types de données définis par l'utilisateur (UDDT), servant de référentiel à plusieurs tables Les valeurs par défaut (Defaults), autorisant le système à insérer des valeurs dans les colonnes non renseignées par l'utilisateur Les vues, ou pseudo-tables (Views), offrant une vue particulière des données aux utilisateurs Les fonctions définies par l'utilisateur (UDF), permettant de renvoyer soit une valeur, soit une table Les procédures stockées, exécutées par l'utilisateur pour produire un résultat donné Les diagrammes (Diagrams), qui visualisent les relations entre les tables. Schémas (à partir de SQL Server 2005/2008), contient un objet Base de Données, comme une table une vue, etc. Administration SQL Server -- 2024 3 Création d’une Base de donnée create database on(name=db1data, filename='C:\data\db1data.mdf',size=1, maxsize=5, filegrowth=1) Allouer 1 Mo au fichier de données log on(name=db1log, filename='C:\backup\db1log.ldf',Size=1, maxsize=5, filegrowth=1) Allouer 1 Mo au fichier de journal des transactions alter database db1 modify file(name=db1data, size=2 MB) Augmente la taille du fichier db1data à 2 Mo dbcc dbcc shrinkdatabase (db1,10) Réduit la taille de la base db1 pour atteindre 10% d’espace libre après réduction drop database db1 Supprime la base de données db1 et tous ses fichiers alter database db1 modify name=db2 Renomme la base de données db1 en db2 sp_helpdb ‘db2’ Donne des informations sur la base de données db2 alter database db2 set read_only Active l’option read only de la base de données db2 Administration SQL Server -- 2024 4 Types de donnée Type de donnée Type de données système Binaire binary[(n)] varbinary[(n)] Caractère char[(n)] varchar[(n)] Text Unicode nchar nvarchar ntext Date et heure datetime smalldatetime Numérique exact decimal [(p[, s])] decimal [(p[, s])] Numérique en virgule flottante float[(n)] real Entier bigint int smallint tinyint Monétaire money smallmoney Image image bit timestamp unique identifier cursor Spécial sql_variant table Administration SQL Server -- 2024 5 Création des tables Une table peut contenir 1024 colonnes au maximum, le nombre de lignes étant limité par la capacité de stockage du serveur. On peut créer jusqu’à 2 milliards de tables par base. Mais pour une bonne maintenance, une base de donnée ne doit pas dépasser 100 à 200 tables. Exemple : create table Clients ( nom char(30) Not Null, prenom char(30) Null, ville char(25) Not Null ) Administration SQL Server -- 2024 6 Création des types de données définis par l’utilisateur (UDDT) Les types de données définis par l’utilisateur sont liés à une base de données Les TDDU permettent de constituer un référentiel de travail pour la création de tables Les TDDU peuvent avoir une valeur par défaut et une règle de Validation Exemple : sp_addtype CodePostal, ‘char(5)’, ‘NOT NULL’ créé un type appelé CodePostal, de type char(5), n’acceptant pas des valeurs NULL sp_rename ancien_nom, nouveau_non, ‘USERDATATYPE’ renomme le TDDU sp_droptype CodePostal supprime le TDDU Administration SQL Server -- 2024 7 Les valeurs par défaut Contrainte default La contrainte default permet de définir la valeur par défaut attachée à une colonne de la table. Exemple : create table commande ( DateComm DateTime default (GETDATE()), Montant float(10) default 0, NumCli int ) La colonne DateComm est initialisée avec la date système (fonction getdate()), et la colonne montant avec la valeur 0. alter table commande add constraint DefNumCli default 0 for NumCli ajoute une contrainte default à la colonne NumCli alter table commande drop constraint DefNumCli supprime la contrainte DefNumCli Administration SQL Server -- 2024 8 Les valeurs par défaut create default On peut aussi créer une valeur par défaut sans la rattacher immédiatement à une colonne. Exemple : create default DefCodePostal as ‘90000’ on peut associer la valeur par défaut DefCodePostal au TDDU CodePostalType : sp_bindefault DefCodePostal, CodePostalType sp_bindefault DefCodePostal, ‘Clients.Cp’ associe la valeur par défaut DefCodePostal à la colonne Cp de la table Clients. Les deux points indiquent que la table Clients appartient au propriétaire de la base de données. pour supprimer la valeur par défaut : drop default DefCodePostal pour délier la valeur par défaut du TDDU CodePostalType : sp_unbindefault CodePostalType pour délier la valeur par défaut de la colonne Cp de la table Clients : sp_unbindefault ‘Clients.Cp’ Il faut d’abord délier la valeur par défaut, avant de pouvoir la supprimer. Administration SQL Server -- 2024 9 Les règles de validation Les règles de validation assurent l’intégrité de domaine. Elles peuvent jouer deux rôles : limiter les valeurs saisies par rapport à une plage déterminée imposer un format de saisie Contrainte check Exemple 1 : Create table test ( Nom char(25) , Age int check (age > 18) Ville char) Exemple 2 : alter table test add constraint ccville check (ville in (‘tanger’, ‘rabat’, ‘fes’)) alter table test drop constraint ccville Supprime la contrainte ccville de la table test Administration SQL Server -- 2024 10 Les règles de validation create rule: on peut créer des règles de validation indépendantes des colonnes de la base de données. Puis on peut les attacher aux colonnes ou aux types de données. Exemple : create rule AgeRule as @age>=18 and @age