Books Authors Web Service Schemas (EDM Sample Application)

  • Article

The three Entity Data Model (EDM) entities and two EDM associations used by the Books Authors Web Service described in this Books group of topics are implemented in the following schemas and mapping specification.

Conceptual Schema

The following schema declares and defines entities and associations using conceptual schema definition language (CSDL).

<?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>

Store Metadata

The following schema defines the storage structures used by applications built on the entities and associations defined in the previous schema. Storage metadata is specified by using store schema definition language (SSDL).

<?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>

Mapping Specification

The following syntax maps entities and associations defined in the CSDL schema to storage metadata in the store schema definition language (SSDL) schema. This mapping specification is written in mapping specification language (MSL).

<?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>

The following script can be used to create the database used in this example. To create the BooksAuthors database and schema with SQL Server Management Studio:

  1. On the File menu, point to New, and then click Database Engine Query.

  2. In the Connect to Database Engine dialog box, type either localhost or the name of the SQL Server instance, and then click Connect.

  3. Paste the following Transact-SQL script in the query window and then click Execute.

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', 'https://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', 'https://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', 'https://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', 'https://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', 'https://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', 'https://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', 'https://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', 'https://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', 'https://margerykempe.com', 'Kempe', 'The Book of Margery Kempe')

See Also

Concepts

Books Authors Web Service (EDM Sample Application)
Books Authors Web Service Implementation (EDM Sample Application)
Client Application for Web Service (EDM Sample Application)

Other Resources

EDM Specifications
Schemas and Mapping Specification (Entity Framework)