Remerciez-le!

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

http://www.apsql.com 1 Les instructions du DDL Mots clés : CREATE TABLE, ALTER

http://www.apsql.com 1 Les instructions du DDL Mots clés : CREATE TABLE, ALTER TABLE, Contrainte d'intégrités, CONSTRAINT, PRIMARY KEY, FOREIGN KEY, REFERENCES, UNIQUE, CHECK, NULL Etablir le schéma entités – relations – attributs (ERA) A partir de l'interview d'Alfred le gérant d'un magasin de location de films proposer le schéma ERA : c'est-à-dire la définition des tables, des colonnes, des types de données pour ces colonnes et des différentes contraintes d'intégrités Interview d'Alfred le responsable de la boutique. Nos clients sont de plus en plus exigeant, cependant ils oublient également fréquemment de nous rendre les DVD qu'ils ont loués, à la date indiquée lors de leur location. Bien qu'il s'agisse d'un club dans lequel nos clients sont de confiance, nous souhaitons mieux suivre nos locations et l'ensemble des éléments mis à disposition de nos membres. Lorsque les clients empruntent un ou plusieurs films, ils s'engagent sur une durée de location. Même si certains dépassent cette date limite, la très grande majorité d'entre eux la respecte. Ainsi lorsqu'un film n'est pas disponible à la location, il nous est possible d'indiquer la date de retour prévue, ceci afin d'éviter que les gens reviennent inutilement au magasin. Pour chaque client, il nous est nécessaire de connaître son nom, prénom, son adresse et un numéro de téléphone. Nous utilisons ces informations en cas de non retour d'un ou plusieurs DVD 2 jours après la date prévue pour contacter le client et l'inviter à rende son exemplaire au plus vite. Ce délai est réduit à 24 h dans le cas d'une nouveauté. Un client ne peut pas emprunter plus de 5 films à la fois avec au maximum 2 nouveautés. Toutefois, moyennant une majoration, le client peut exceptionnellement emprunter 3 nouveautés. Cette autorisation n'est accordée qu'une fois par mois au maximum. Pour nous, une nouveauté est un film qui est disponible à la location depuis moins de 3 mois. Passé ce délai, le film perd son statut de nouveauté. Un même film peut être disponible en plusieurs exemplaires. C'est généralement le cas pour les nouveautés, puis au fur et à mesure que le nombre d'emprunts pour ce titre diminue, nous réduisons le nombre d'exemplaire disponibles à la location. Chaque exemplaire, est parfaitement identifié par son numéro d'inventaire. Il dispose également d'une place spécifique en magasin. Cette place est repérée par son numéro de travée, et son numéro d'étagère. Il est possible de disposer plusieurs films différents sur une même étagère. En général nous essayons de regrouper ensemble tous les exemplaires d'un même film. Cet emplacement physique peut varier au cours du temps car les nouveautés sont mises en avant et ne sont pas classé selon la catégorie du film. Les catégories permettent de classer de façon grossière les films, c'est par exemple: horreur, aventure, comédie, western, enfant, documentaire, …. http://www.apsql.com 2 Lorsqu'un client vient pour louer des films, nous commençons toujours par nous assurer qu'il a bien rendu tous les films emprunté et qu'il est à jour dans ses règlements. Nous créons alors une fiche sur laquelle nous notons les références du client, la référence de chaque exemplaire emprunté, son titre et la date de retour prévue. Comme le montant de la location est lié à la durée d'emprunt, le client paiera lorsqu'il aura rendu tous les exemplaires empruntés. Le paiement s'effectue toujours en une seule fois. Pour calculer le prix de location nous tenons compte des éléments suivant: - la durée de location (en jours), - le type du film : nouveauté ou non, - le prix de location à la journée fixé au niveau de chaque film. Nos clients et nous même souhaitons pour chaque film connaître le réalisateur et les principaux acteurs afin de pouvoir réaliser des recherches par rapport à ces critères. Pour chaque film présent dans la boutique sous la forme de un ou plusieurs exemplaires nous dressons la fiche du film sur laquelle sont présent le détail du film, comme son nom, sa nationalité, son réalisateur, la liste des principaux acteurs, mais également un résumé. Un e fois par mois, nous envoyons par mail, à nos clients qui possède une adresse électronique, la liste de nos nouveautés. http://www.apsql.com 3 Identifier les entités A la lecture de l’interview il est possible d’identifier les entités suivantes :  Client : regroupe toutes les informations relatives au client  Film : regroupe toutes les informations relatives à un film  Artiste : regroupe les acteurs et les réalisateurs  Exemplaire : regroupe toutes les informations d’un exemplaire de film mis en location  FicheLocation : regroupe toutes les informations communes à un ou plusieurs emprunts réalisés simultanément par un client  DétailLocation : regroupe les informations spécifiques à chaque exemplaire emprunté  Catégorie : permet de regrouper les films de façon logique http://www.apsql.com 4 Le schéma ERA Le schéma suivant donne un aperçu graphique du schéma ERA Clients PK numero nom prenom adresse codePostal ville telephone mobile email Films PK numero titre disponibleLocation prixLocationJour FK1 categorie FK2 realisateur FK3 producteur anneeProduction nationalite duree FK4 spectateurs resume Artistes PK numero nom prenom Exemplaires PK numero travee etagere FK1 film Fiches PK numero FK1 client creeLe montantPaye Details PK numero PK,FK1 fiche dureeLocation FK2 exemplaire Categories PK code libelle Acteurs PK,FK1 artiste PK,FK2 film Producteurs PK numero nom Spectateurs PK numero libelle http://www.apsql.com 5 Le détail des entités Clients Fiches Détails Exemplaires Attribut Type de données Contraintes Commentaire numero int clé primaire identity nom nvarchar(50) obligatoire prenom nvarchar(50) obligatoire adresse nvarchar(100) obligatoire codePostal char(5) obligatoire ville nvarchar(50) obligatoire telephone char(14) mobile char(14) email nvarchar(100) Attribut Type de données Contraintes Commentaire numero int clé primaire identity client int obligatoire clé étrangère (Clients) creele datetime Date du jour par défaut montantPaye money Attribut Type de données Contraintes Commentaire numero int clé primaire fiche int clé primaire clé étrangère (Fiches) dureeLocation int obligatoire exemplaire int obligatoire clé étrangère (Exemplaires) Attribut Type de données Contraintes Commentaire numero int clé primaire identity travee int obligatoire etagere int obligatoire film int obligatoire clé étrangère (Films) http://www.apsql.com 6 Films Acteurs Artistes Categories Producteurs Spectateurs Attribut Type de données Contraintes Commentaire numero Int clé primaire identity titre nvarchar(80) obligatoires disponibleLocation Tinyint obligatoire prixLocationJour Money obligatoire categorie char(5) obligatoire realisateur int obligatoire clé étrangère (Artistes) producteur int obligatoire clé étrangère (Producteurs) anneeProduction smallint obligatoire nationalite char(5) obligatoire Valeur par défaut : Fra duree int obligatoire spectateurs int obligatoire clé étrangère (Spectateurs) resume nvarchar(max) obligatoire Attribut Type de données Contraintes Commentaire artiste int clé primaire clé étrangère (Artistes) film int clé primaire clé étrangère (Films) Attribut Type de données Contraintes Commentaire numero int clé primaire identity nom nvarchar(50) obligatoire prenom nvarchar(50) obligatoire Attribut Type de données Contraintes Commentaire code char(5) clé primaire libelle nvarchar(50) obligatoire Attribut Type de données Contraintes Commentaire numero int clé primaire identity nom nvarchar(50) obligatoire Attribut Type de données Contraintes Commentaire numero int clé primaire identity libelle nvarchar(50) obligatoire http://www.apsql.com 7 Créer un nouveau projet De nombreux scripts Transact SQL vont être définis, aussi est il préférable de les regrouper dans un projet au sein de SQL Server Management Studio. Le projet portera le nom de Video et sera placé dans une nouvelle solution Créer la base de données Les entités identifiées, il va être possible de créer les tables dans SQL Server. Cependant, il n’est pas question de définir ces tables dans une base quelconque. Il est donc nécessaire, dans un premier temps, de définir la base de données Video. Pour définir cette base, il est préférable d’utiliser les caractéristiques suivantes :  Fichier de données Nom Video_Data Nom physique C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Video_Data.mdf Taille initiale 10 Mo Croissance 10% Taille maximale 50 Mo  Fichier journal Nom Video_Log Nom physique C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Video_Log.ldf Taille initiale 5 Mo Croissance 10% Taille maximale Illimité Le script suivant permet de créer une telle base : use master; go CREATE DATABASE Video ON PRIMARY ( NAME = N'Video_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Video_Data.mdf' , SIZE = 10MB , MAXSIZE = 50MB , FILEGROWTH = 10%) LOG ON ( NAME = N'Video_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Video_log.ldf' , SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%); go http://www.apsql.com 8 Définir un schéma Afin de gagner en clarté certaines tables devront être définies dans le schéma Magasin tandis que les autres seront définies dans le schéma dbo. Le schéma va être défini avec le script suivant : use Video; go CREATE SCHEMA Magasin; Créer les tables Lors de cette étape de création de table, aucune contrainte d’intégrité n’est spécifiée à l’exception de la gestion des valeurs NULL (autorisées ou interdites). En procédant de cette façon, il n’y a pas d’ordre privilégié pour définir les tables. Les contraintes d’intégrités seront définies dans une seconde étape. Les tables Clients, Fiches, Details et Exemplaires vont être définies dans le schéma Magasin. Les autres tables seront définies sur le schéma dbo. use Video; go CREATE TABLE Spectateurs( numero int identity not null, libelle nvarchar(50) not null); CREATE TABLE Producteurs( numero int identity not null, nom nvarchar(50) not null); CREATE TABLE Categories( code char(5) not uploads/Geographie/ 1-les-instructions-du-ddl.pdf