Schémas du service Web Books Authors (exemple d'application EDM)

Article
08/23/2008

Les trois entités EDM (Modèle de données d'entité) et les deux associations EDM utilisées par le service Web Books Authors décrit dans ce groupe de rubriques Books sont implémentées dans les schémas et la spécification de mappage ci-dessous.

Schéma conceptuel

Le schéma suivant déclare et définit des entités et des associations à l'aide du langage CSDL (Conceptual Schema Definition Language).

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="BooksAuthorsModel" Alias="Self" xmlns="https://schemas.microsoft.com/ado/2006/04/edm">

  <EntityContainer Name="BooksAuthorsEntities">
    <EntitySet Name="Authors" EntityType="BooksAuthorsModel.Authors" />
    <EntitySet Name="Books" EntityType="BooksAuthorsModel.Books" />
    <EntitySet Name="BooksInfo"
          EntityType="BooksAuthorsModel.BooksInfo" />
    <AssociationSet Name="FK_AuthorId"
          Association="BooksAuthorsModel.FK_AuthorId">
      <End Role="Authors" EntitySet="Authors" />
      <End Role="BooksInfo" EntitySet="BooksInfo" />
    </AssociationSet>
    <AssociationSet Name="FK_BookId"
          Association="BooksAuthorsModel.FK_BookId">
      <End Role="Books" EntitySet="Books" />
      <End Role="BooksInfo" EntitySet="BooksInfo" />
    </AssociationSet>
  </EntityContainer>
  <EntityType Name="Authors">
    <Key>
      <PropertyRef Name="AuthorId" />
    </Key>
    <Property Name="AuthorId" Type="Guid" Nullable="false" />
    <Property Name="LastName" Type="String" Nullable="false" />
    <Property Name="FirstName" Type="String" />
    <NavigationProperty Name="BooksInfo"
         Relationship="BooksAuthorsModel.FK_AuthorId"
         FromRole="Authors" ToRole="BooksInfo" />
  </EntityType>
  <EntityType Name="Books">
    <Key>
      <PropertyRef Name="BookId" />
    </Key>
    <Property Name="BookId" Type="String" Nullable="false" />
    <Property Name="Title" Type="String" Nullable="false" />
    <NavigationProperty Name="BooksInfo"
        Relationship="BooksAuthorsModel.FK_BookId"
        FromRole="Books" ToRole="BooksInfo" />
  </EntityType>
  <EntityType Name="BooksInfo">
    <Key>
      <PropertyRef Name="BookInfoId" />
    </Key>
    <Property Name="BookInfoId" Type="Guid" Nullable="false" />
    <Property Name="InfoLocator" Type="String" />
    <Property Name="AuthorLastName" Type="String" />
    <Property Name="BookTitle" Type="String" />
    <NavigationProperty Name="Authors" Relationship="BooksAuthorsModel.FK_AuthorId"
             FromRole="BooksInfo" ToRole="Authors" />
    <NavigationProperty Name="Books" Relationship="BooksAuthorsModel.FK_BookId"
                FromRole="BooksInfo" ToRole="Books" />
  </EntityType>
  <Association Name="FK_AuthorId">
    <End Role="Authors" Type="BooksAuthorsModel.Authors" Multiplicity="1" />
    <End Role="BooksInfo" Type="BooksAuthorsModel.BooksInfo"
           Multiplicity="*" />
  </Association>
  <Association Name="FK_BookId">
    <End Role="Books" Type="BooksAuthorsModel.Books" Multiplicity="1" />
    <End Role="BooksInfo" Type="BooksAuthorsModel.BooksInfo"
              Multiplicity="*" />
  </Association>
</Schema>

Métadonnées de stockage

Le schéma ci-dessous définit les structures de stockage utilisées par les applications basées sur les entités et les associations définies dans le schéma précédent. Les métadonnées de stockage sont spécifiées à l'aide du langage SSDL (Store Schema Definition Language).

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="BooksAuthorsModel.Store" Alias="Self"
    Provider="System.Data.SqlClient"
    ProviderManifestToken="2005"
    xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">

  <EntityContainer Name="dbo">
    <EntitySet Name="Authors"
         EntityType="BooksAuthorsModel.Store.Authors" />
    <EntitySet Name="Books" EntityType="BooksAuthorsModel.Store.Books" />
    <EntitySet Name="BooksInfo"
              EntityType="BooksAuthorsModel.Store.BooksInfo" />
    <AssociationSet Name="FK_AuthorId"
             Association="BooksAuthorsModel.Store.FK_AuthorId">
      <End Role="Authors" EntitySet="Authors" />
      <End Role="BooksInfo" EntitySet="BooksInfo" />
    </AssociationSet>
    <AssociationSet Name="FK_BookId"
               Association="BooksAuthorsModel.Store.FK_BookId">
      <End Role="Books" EntitySet="Books" />
      <End Role="BooksInfo" EntitySet="BooksInfo" />
    </AssociationSet>
  </EntityContainer>
  <EntityType Name="Authors">
    <Key>
      <PropertyRef Name="AuthorId" />
    </Key>
    <Property Name="AuthorId" Type="uniqueidentifier" Nullable="false" />
    <Property Name="LastName" Type="nvarchar" 
                        Nullable="false" MaxLength="50" />
    <Property Name="FirstName" Type="nvarchar" MaxLength="50" />
  </EntityType>
  <EntityType Name="Books">
    <Key>
      <PropertyRef Name="BookId" />
    </Key>
    <Property Name="BookId" Type="nvarchar" 
                         Nullable="false" MaxLength="50" />
    <Property Name="Title" Type="nvarchar" 
                         Nullable="false" MaxLength="50" />
  </EntityType>
  <EntityType Name="BooksInfo">
    <Key>
      <PropertyRef Name="BookInfoId" />
    </Key>
    <Property Name="BookInfoId" Type="uniqueidentifier"
                   Nullable="false" />
    <Property Name="AuthorId" Type="uniqueidentifier"
                   Nullable="false" />
    <Property Name="BookId" Type="nvarchar" 
                   Nullable="false" MaxLength="50" />
    <Property Name="InfoLocator" Type="nvarchar(max)" />
    <Property Name="AuthorLastName" Type="nvarchar" 
                                       MaxLength="50" />
    <Property Name="BookTitle" Type="nvarchar" MaxLength="50" />
  </EntityType>
  <Association Name="FK_AuthorId">
    <End Role="Authors" Type="BooksAuthorsModel.Store.Authors"
                   Multiplicity="1" />
    <End Role="BooksInfo" Type="BooksAuthorsModel.Store.BooksInfo"
                   Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="Authors">
        <PropertyRef Name="AuthorId" />
      </Principal>
      <Dependent Role="BooksInfo">
        <PropertyRef Name="AuthorId" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
  <Association Name="FK_BookId">
    <End Role="Books" Type="BooksAuthorsModel.Store.Books"
                   Multiplicity="1" />
    <End Role="BooksInfo" Type="BooksAuthorsModel.Store.BooksInfo"
                   Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="Books">
        <PropertyRef Name="BookId" />
      </Principal>
      <Dependent Role="BooksInfo">
        <PropertyRef Name="BookId" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
</Schema>

Spécification de mappage

La syntaxe suivante mappe les entités et les associations définies dans le schéma CSDL aux métadonnées de stockage dans le schéma SSDL (Store Schema Definition Language). Cette spécification de mappage est écrite en langage MSL (Mapping Specification Language).

<?xml version="1.0" encoding="utf-8"?>
<Mapping Space="C-S" 
     xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
  <EntityContainerMapping StorageEntityContainer="dbo"
     CdmEntityContainer="BooksAuthorsEntities">
    <EntitySetMapping Name="Authors" StoreEntitySet="Authors"
            TypeName="BooksAuthorsModel.Authors">
      <ScalarProperty Name="AuthorId" ColumnName="AuthorId" />
      <ScalarProperty Name="LastName" ColumnName="LastName" />
      <ScalarProperty Name="FirstName" ColumnName="FirstName" />
    </EntitySetMapping>
    <EntitySetMapping Name="Books" StoreEntitySet="Books"
               TypeName="BooksAuthorsModel.Books">
      <ScalarProperty Name="BookId" ColumnName="BookId" />
      <ScalarProperty Name="Title" ColumnName="Title" />
    </EntitySetMapping>
    <EntitySetMapping Name="BooksInfo" StoreEntitySet="BooksInfo"
               TypeName="BooksAuthorsModel.BooksInfo">
      <ScalarProperty Name="BookInfoId" ColumnName="BookInfoId" />
      <ScalarProperty Name="InfoLocator" ColumnName="InfoLocator" />
      <ScalarProperty Name="AuthorLastName"
                           ColumnName="AuthorLastName"/>
      <ScalarProperty Name="BookTitle" ColumnName="BookTitle" />
    </EntitySetMapping>
    <AssociationSetMapping Name="FK_AuthorId"
          TypeName="BooksAuthorsModel.FK_AuthorId"
          StoreEntitySet="BooksInfo">
      <EndProperty Name="Authors">
        <ScalarProperty Name="AuthorId" ColumnName="AuthorId" />
      </EndProperty>
      <EndProperty Name="BooksInfo">
        <ScalarProperty Name="BookInfoId" ColumnName="BookInfoId" />
      </EndProperty>
      <Condition ColumnName="AuthorId" IsNull="false" />
    </AssociationSetMapping>
    <AssociationSetMapping Name="FK_BookId" TypeName="BooksAuthorsModel.FK_BookId" StoreEntitySet="BooksInfo">
      <EndProperty Name="Books">
        <ScalarProperty Name="BookId" ColumnName="BookId" />
      </EndProperty>
      <EndProperty Name="BooksInfo">
        <ScalarProperty Name="BookInfoId" ColumnName="BookInfoId" />
      </EndProperty>
      <Condition ColumnName="BookId" IsNull="false" />
    </AssociationSetMapping>
  </EntityContainerMapping>
</Mapping>

Le script ci-dessous peut être utilisé pour créer la base de données utilisée dans cet exemple. Pour créer la base de données et le schéma BooksAuthors à l'aide de SQL Server Management Studio :

Dans le menu Fichier, pointez sur Nouveau, puis cliquez sur Requête de moteur de base de données.
Dans la boîte de dialogue Se connecter au moteur de base de données, tapez localhost ou le nom de l'instance de SQL Server, puis cliquez sur Se connecter.
Collez le script Transact-SQL fourni ci-dessous dans la fenêtre de requête, puis cliquez sur Exécuter.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [master]
GO

IF EXISTS (SELECT * FROM sys.databases 
WHERE name = 'BooksAuthors')
DROP DATABASE BooksAuthors;
GO

-- Create the database.
CREATE DATABASE BooksAuthors;
GO

USE BooksAuthors;
GO

IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Books]') 
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Books](
    [BookId] [nvarchar](50) NOT NULL,
     [Title] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
     [BookId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Authors]') 
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Authors](
     [AuthorId] [uniqueidentifier] NOT NULL,
     [LastName] [nvarchar](50) NOT NULL,
     [FirstName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED 
(
     [AuthorId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[BooksInfo]') 
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BooksInfo](
     [BookInfoId] [uniqueidentifier] NOT NULL,
     [AuthorId] [uniqueidentifier] NOT NULL,
     [BookId] [nvarchar](50) NOT NULL,
     [InfoLocator] [nvarchar](max) NULL,
     [AuthorLastName] [nvarchar](50) NULL,
     [BookTitle] [nvarchar](50) NULL,
 CONSTRAINT [PK_BookInfo] PRIMARY KEY CLUSTERED 
(
     [BookInfoId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[FK_AuthorId]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[BooksInfo]'))
ALTER TABLE [dbo].[BooksInfo]  
WITH CHECK ADD  CONSTRAINT [FK_AuthorId] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Authors] ([AuthorId])
GO
ALTER TABLE [dbo].[BooksInfo] CHECK CONSTRAINT [FK_AuthorId]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
WHERE object_id = OBJECT_ID(N'[dbo].[FK_BookId]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[BooksInfo]'))
ALTER TABLE [dbo].[BooksInfo]  
WITH CHECK ADD  CONSTRAINT [FK_BookId] FOREIGN KEY([BookId])
REFERENCES [dbo].[Books] ([BookId])
GO
ALTER TABLE [dbo].[BooksInfo] CHECK CONSTRAINT [FK_BookId]
GO

Use BooksAuthors
GO

INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-012-9', 'The Assembly of Gods')
INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-234-1', 'Know Yourself')
INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-345-2', 'Recall of the Histories of Troy')
INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-456-3', 'Meliador')
INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-567-4', 'History of the Kings of Britain')
INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-678-5', 'On the Division of Nature')
INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-765-1', 'The Voyage and Travels of Sir John Mandeville')
INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-876-0', 'Morte d’Arthur')
INSERT INTO dbo.Books(BookId, Title)
VALUES ('0-12345-890-7', 'The Book of Margery Kempe')


INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('2f19e43f-0107-4ba6-9210-41edbde7a0c9', 'Margery', 'Kempe')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('b0688878-9d4e-439a-9a67-46a2cada47b5', 'Jean', 'Froissart')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('61b5b894-56d0-4598-8181-4c36085b7a3a', 'John', 'Lydgate')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('b97dfb02-89be-43e4-8a7a-7195753f426e', 'John Scott', 'Erigena')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('642b2071-81a6-4292-964b-cbdfaf77999c', 'William', 'Caxton')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('194f6bb0-7b4a-4d83-836f-db60c45d5fd5', 'Thomas', 'Malory')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('413b9202-d9cb-4353-8d53-e889d367eb1a', 'William', 'Langland')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('8d9c5792-c651-423b-a412-f120dd89585c', 'Peter ', 'Abelard')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('0a921250-25cf-4d8e-8302-fe2d077d709b', 'Geoffrey of  ', 'Monmouth')
INSERT INTO dbo.Authors(AuthorId, FirstName, LastName)
VALUES ('cc0940ab-0990-4ee0-8567-fe71d62661b7', 'John', 'Mandeville')


INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('535a39fc-a339-4277-b682-65637d69548b', 'b0688878-9d4e-439a-9a67-46a2cada47b5', '0-12345-456-3', 'http://jeanfroissart.com', 'Froissart', 'Meliador')
INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('317cba6a-5736-497b-84be-6ba725a920b6', 'b97dfb02-89be-43e4-8a7a-7195753f426e', '0-12345-678-5', 'http://jserigena.com', 'Erigena', 'On the Division of Nature')
INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('d895f8f8-04bd-40f9-9478-72fbdb7d0b52', '61b5b894-56d0-4598-8181-4c36085b7a3a', '0-12345-012-9', 'http://assemblyofgods.com', 'Lydgate', 'The Assembly of Gods')
INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('2e734ee4-4e99-4be3-adf9-9366dc954840', '8d9c5792-c651-423b-a412-f120dd89585c', '0-12345-234-1', 'http://peterabelard.com', 'Abelard', 'Know Yourself')
INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('f8358150-7aef-49b5-97e2-9ea1cae703f4', '194f6bb0-7b4a-4d83-836f-db60c45d5fd5', '0-12345-876-0', 'http://mortedarthur.com', 'Malory', 'Morte d’Arthur')
INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('9cf29606-8bdb-43eb-918f-ae08307dd497', '0a921250-25cf-4d8e-8302-fe2d077d709b', '0-12345-567-4', 'http://GeoffreyofMonmouth.com', 'Monmouth', 'History of the Kings of Britain')
INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('64b4130f-867a-45cb-84a5-b4c21307afb4', 'cc0940ab-0990-4ee0-8567-fe71d62661b7', '0-12345-765-1', 'http://VoyageandTravels.com', 'Mandeville', 'The Voyage and Travels of Sir John Mandeville')
INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('4a3e00ea-e0d3-4bf6-8ee1-ba1e2b127799', '642b2071-81a6-4292-964b-cbdfaf77999c', '0-12345-345-2', 'http://wmcaxton.com', 'Caxton', 'Recall of the Histories of Troy')
INSERT INTO dbo.BooksInfo(BookInfoId, AuthorId, BookId, InfoLocator, AuthorLastName, BookTitle)
VALUES ('0dcf2550-1970-4d24-abe0-f61dbea089a1', '2f19e43f-0107-4ba6-9210-41edbde7a0c9', '0-12345-890-7', 'http://margerykempe.com', 'Kempe', 'The Book of Margery Kempe')

Voir aussi

Concepts

Books Authors Web Service (Exemple d'application EDM)
Implémentation du service Web Books Authors (exemple d'application EDM)
Client Application for Web Service (Exemple d'application EDM)

Autres ressources

Spécifications EDM
Schémas et spécification de mappage (Entity Framework)

Partager via