Partager via

Schéma conceptuel des ventes AdventureWorks (EDM)

  • Article

Le modèle de vente AdventureWorks Sales Model est une implémentation EDM (Modèle de données d'entité) basée sur les tables de l'exemple AdventureWorks fourni avec SQL Server. Cinq entités sont déclarées :

  • Address

  • Contact

  • Product

  • SalesOrderDetail

  • SalesOrderHeader

Les instances de ces types sont contenues logiquement par les jeux d'entités du même nom dans le modèle objet créé à partir du schéma conceptuel.

Trois associations et ensembles d'associations sont déclarés. Les noms des associations suivantes commencent tous par FK_. Cela reflète les relations de clé étrangère dans les tables des données Adventure Works.

FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID

FK_SalesOrderHeader_Address_ShipToAddressID

FK_SalesOrderHeader_Contact_ContactID

Schéma CSDL

Les entités et les associations de ce modèle sont déclarées et définies dans le langage CSDL (Conceptual Schema Definition Language). Le schéma complet est présenté dans l'exemple ci-dessous.

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="AdvWrksSalesModel" Alias="Self" xmlns="https://schemas.microsoft.com/ado/2006/04/edm">
  <EntityContainer Name="AdvWksSalesEntities">
    <EntitySet Name="Address" EntityType="AdvWrksSalesModel.Address" />
    <EntitySet Name="Contact" EntityType="AdvWrksSalesModel.Contact" />
    <EntitySet Name="Product" EntityType="AdvWrksSalesModel.Product" />
    <EntitySet Name="SalesOrderDetail" EntityType="AdvWrksSalesModel.SalesOrderDetail" />
    <EntitySet Name="SalesOrderHeader" EntityType="AdvWrksSalesModel.SalesOrderHeader" />
    <AssociationSet Name="FK_SalesOrderHeader_Address_BillToAddressID" Association="AdvWrksSalesModel.FK_SalesOrderHeader_Address_BillToAddressID">
      <End Role="Address" EntitySet="Address" />
      <End Role="SalesOrderHeader" EntitySet="SalesOrderHeader" />
    </AssociationSet>
    <AssociationSet Name="FK_SalesOrderHeader_Address_ShipToAddressID"
 Association="AdvWrksSalesModel.FK_SalesOrderHeader_Address_ShipToAddressID">
      <End Role="Address" EntitySet="Address" />
      <End Role="SalesOrderHeader" EntitySet="SalesOrderHeader" />
    </AssociationSet>
    <AssociationSet Name="FK_SalesOrderHeader_Contact_ContactID"
 Association="AdvWrksSalesModel.FK_SalesOrderHeader_Contact_ContactID">
      <End Role="Contact" EntitySet="Contact" />
      <End Role="SalesOrderHeader" EntitySet="SalesOrderHeader" />
    </AssociationSet>
    <AssociationSet Name="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
 Association="AdvWrksSalesModel.FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID">
      <End Role="SalesOrderHeader" EntitySet="SalesOrderHeader" />
      <End Role="SalesOrderDetail" EntitySet="SalesOrderDetail" />
    </AssociationSet>
  </EntityContainer>
  <EntityType Name="Address">
    <Key>
      <PropertyRef Name="AddressID" />
    </Key>
    <Property Name="AddressID" Type="Int32" Nullable="false" />
    <Property Name="AddressLine1" Type="String" Nullable="false" />
    <Property Name="AddressLine2" Type="String" />
    <Property Name="City" Type="String" Nullable="false" />
    <Property Name="StateProvinceID" Type="Int32" Nullable="false" />
    <Property Name="PostalCode" Type="String" Nullable="false" />
    <Property Name="rowguid" Type="Guid" Nullable="false" />
    <Property Name="ModifiedDate" Type="DateTime" Nullable="false" />
    <NavigationProperty Name="SalesOrderHeader" Relationship="AdvWrksSalesModel.FK_SalesOrderHeader_Address_BillToAddressID"
 FromRole="Address" ToRole="SalesOrderHeader" />
    <NavigationProperty Name="SalesOrderHeader1" Relationship="AdvWrksSalesModel.FK_SalesOrderHeader_Address_ShipToAddressID"
 FromRole="Address" ToRole="SalesOrderHeader" />
  </EntityType>
  <EntityType Name="Contact">
    <Key>
      <PropertyRef Name="ContactID" />
    </Key>
    <Property Name="ContactID" Type="Int32" Nullable="false" />
    <Property Name="NameStyle" Type="Boolean" Nullable="false" />
    <Property Name="Title" Type="String" />
    <Property Name="FirstName" Type="String" Nullable="false" />
    <Property Name="MiddleName" Type="String" />
    <Property Name="LastName" Type="String" Nullable="false" />
    <Property Name="Suffix" Type="String" />
    <Property Name="EmailAddress" Type="String" />
    <Property Name="EmailPromotion" Type="Int32" Nullable="false" />
    <Property Name="Phone" Type="String" />
    <Property Name="PasswordHash" Type="String" Nullable="false" />
    <Property Name="PasswordSalt" Type="String" Nullable="false" />
    <Property Name="AdditionalContactInfo" Type="String" />
    <Property Name="rowguid" Type="Guid" Nullable="false" />
    <Property Name="ModifiedDate" Type="DateTime" Nullable="false" />
    <NavigationProperty Name="SalesOrderHeader" Relationship="AdvWrksSalesModel.FK_SalesOrderHeader_Contact_ContactID"
             FromRole="Contact" ToRole="SalesOrderHeader" />
  </EntityType>
  <EntityType Name="Product">
    <Key>
      <PropertyRef Name="ProductID" />
    </Key>
    <Property Name="ProductID" Type="Int32" Nullable="false" />
    <Property Name="Name" Type="String" Nullable="false" />
    <Property Name="ProductNumber" Type="String" Nullable="false" />
    <Property Name="MakeFlag" Type="Boolean" Nullable="false" />
    <Property Name="FinishedGoodsFlag" Type="Boolean" Nullable="false" />
    <Property Name="Color" Type="String" />
    <Property Name="SafetyStockLevel" Type="Int16" Nullable="false" />
    <Property Name="ReorderPoint" Type="Int16" Nullable="false" />
    <Property Name="StandardCost" Type="Decimal" Nullable="false" />
    <Property Name="ListPrice" Type="Decimal" Nullable="false" />
    <Property Name="Size" Type="String" />
    <Property Name="SizeUnitMeasureCode" Type="String" />
    <Property Name="WeightUnitMeasureCode" Type="String" />
    <Property Name="Weight" Type="Decimal" />
    <Property Name="DaysToManufacture" Type="Int32" Nullable="false" />
    <Property Name="ProductLine" Type="String" />
    <Property Name="Class" Type="String" />
    <Property Name="Style" Type="String" />
    <Property Name="ProductSubcategoryID" Type="Int32" />
    <Property Name="ProductModelID" Type="Int32" />
    <Property Name="SellStartDate" Type="DateTime" Nullable="false" />
    <Property Name="SellEndDate" Type="DateTime" />
    <Property Name="DiscontinuedDate" Type="DateTime" />
    <Property Name="rowguid" Type="Guid" Nullable="false" />
    <Property Name="ModifiedDate" Type="DateTime" Nullable="false" />
  </EntityType>
  <EntityType Name="SalesOrderDetail">
    <Key>
      <PropertyRef Name="SalesOrderID" />
      <PropertyRef Name="SalesOrderDetailID" />
    </Key>
    <Property Name="SalesOrderID" Type="Int32" Nullable="false" />
    <Property Name="SalesOrderDetailID" Type="Int32" Nullable="false" />
    <Property Name="CarrierTrackingNumber" Type="String" />
    <Property Name="OrderQty" Type="Int16" Nullable="false" />
    <Property Name="ProductID" Type="Int32" Nullable="false" />
    <Property Name="SpecialOfferID" Type="Int32" Nullable="false" />
    <Property Name="UnitPrice" Type="Decimal" Nullable="false" />
    <Property Name="UnitPriceDiscount" Type="Decimal" Nullable="false" />
    <Property Name="LineTotal" Type="Decimal" Nullable="false" />
    <Property Name="rowguid" Type="Guid" Nullable="false" />
    <Property Name="ModifiedDate" Type="DateTime" Nullable="false" />
    <NavigationProperty Name="SalesOrderHeader" Relationship="AdvWrksSalesModel.FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
        FromRole="SalesOrderDetail" ToRole="SalesOrderHeader" />
  </EntityType>
  <EntityType Name="SalesOrderHeader">
    <Key>
      <PropertyRef Name="SalesOrderID" />
    </Key>
    <Property Name="SalesOrderID" Type="Int32" Nullable="false" />
    <Property Name="RevisionNumber" Type="Byte" Nullable="false" />
    <Property Name="OrderDate" Type="DateTime" Nullable="false" />
    <Property Name="DueDate" Type="DateTime" Nullable="false" />
    <Property Name="ShipDate" Type="DateTime" />
    <Property Name="Status" Type="Byte" Nullable="false" />
    <Property Name="OnlineOrderFlag" Type="Boolean" Nullable="false" />
    <Property Name="SalesOrderNumber" Type="String" Nullable="false" />
    <Property Name="PurchaseOrderNumber" Type="String" />
    <Property Name="AccountNumber" Type="String" />
    <Property Name="CustomerID" Type="Int32" Nullable="false" />
    <Property Name="SalesPersonID" Type="Int32" />
    <Property Name="TerritoryID" Type="Int32" />
    <Property Name="ShipMethodID" Type="Int32" Nullable="false" />
    <Property Name="CreditCardID" Type="Int32" />
    <Property Name="CreditCardApprovalCode" Type="String" />
    <Property Name="CurrencyRateID" Type="Int32" />
    <Property Name="SubTotal" Type="Decimal" Nullable="false" />
    <Property Name="TaxAmt" Type="Decimal" Nullable="false" />
    <Property Name="Freight" Type="Decimal" Nullable="false" />
    <Property Name="TotalDue" Type="Decimal" Nullable="false" />
    <Property Name="Comment" Type="String" />
    <Property Name="rowguid" Type="Guid" Nullable="false" />
    <Property Name="ModifiedDate" Type="DateTime" Nullable="false" />
    <NavigationProperty Name="Address"
 Relationship="AdvWrksSalesModel.FK_SalesOrderHeader_Address_BillToAddressID"  FromRole="SalesOrderHeader" ToRole="Address" />
    <NavigationProperty Name="Address1" Relationship="AdvWrksSalesModel.FK_SalesOrderHeader_Address_ShipToAddressID"
 FromRole="SalesOrderHeader" ToRole="Address" />
    <NavigationProperty Name="Contact" Relationship="AdvWrksSalesModel.FK_SalesOrderHeader_Contact_ContactID" FromRole="SalesOrderHeader" ToRole="Contact" />
    <NavigationProperty Name="SalesOrderDetail"
 Relationship="AdvWrksSalesModel.FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
 FromRole="SalesOrderHeader" ToRole="SalesOrderDetail" />
  </EntityType>
  <Association Name="FK_SalesOrderHeader_Address_BillToAddressID">
    <End Role="Address" Type="AdvWrksSalesModel.Address" Multiplicity="1" />
    <End Role="SalesOrderHeader" Type="AdvWrksSalesModel.SalesOrderHeader" Multiplicity="*" />
  </Association>
  <Association Name="FK_SalesOrderHeader_Address_ShipToAddressID">
    <End Role="Address" Type="AdvWrksSalesModel.Address" Multiplicity="1" />
    <End Role="SalesOrderHeader" Type="AdvWrksSalesModel.SalesOrderHeader" Multiplicity="*" />
  </Association>
  <Association Name="FK_SalesOrderHeader_Contact_ContactID">
    <End Role="Contact" Type="AdvWrksSalesModel.Contact" Multiplicity="1" />
    <End Role="SalesOrderHeader" Type="AdvWrksSalesModel.SalesOrderHeader" Multiplicity="*" />
  </Association>
  <Association Name="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID">
    <End Role="SalesOrderHeader" Type="AdvWrksSalesModel.SalesOrderHeader" Multiplicity="1">
      <OnDelete Action="Cascade" />
    </End>
    <End Role="SalesOrderDetail" Type="AdvWrksSalesModel.SalesOrderDetail" Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="SalesOrderHeader">
        <PropertyRef Name="SalesOrderID" />
      </Principal>
      <Dependent Role="SalesOrderDetail">
        <PropertyRef Name="SalesOrderID" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
</Schema>

Voir aussi

Concepts

Schéma de stockage de l'exemple de base de données AdventureWorks Sales (EDM)
Schéma de mappage des ventes AdventureWorks (EDM)
Connexion au modèle de vente AdventureWorks Sales Model (EDM)