Schema with Relationships

for RuBoard

If we add relationships to the schema we just created, we can use the schema to create a typed data class to work with our database.

We could do that programmatically by adding constraints and relationships to the dataset, as discussed earlier in the chapter, and then writing out the schema. The DataSchemaXml example does just that. You could also create a schema document by hand, or edit the one we generated in the previous example.

The XSD Tool directory has a schema which has been revised to add the relationships between the tables in the AirlineBroker database. The first part of the file, AirlineBroker.xsd , looks like the previous version except that the minOccurs=0 attribute has been removed from all the fields because we do not allow nulls in any of them.

 ...   <xsd:element name="Airlines">      <xsd:complexType>      <xsd:sequence>         <xsd:element name="Name" type="xsd:string" />          <xsd:element name="Abbreviation"                          type="xsd:string" />          <xsd:element name="ReservationNumber"                          type="xsd:string" />          <xsd:element name="WebSite" type="xsd:string" />      </xsd:sequence>      </xsd:complexType>   </xsd:element>  ... 

The last section defines the relationships. Here is the definition for the Airlines table primary key. Note the use of attributes in the msdata namespace. These attributes are defined by Microsoft using the W3C Schema standard to express additional semantic information about the DataSet . These extensions themselves are not a W3C standard. The Schema standard can express constraints with the unique , key , or keyref constructs. Nonetheless, they do not specify which unique key is the primary key.

XPath, which is used to specify relationships to other tables and fields is a W3C standard for locating elements within an XML file. It is used when an XML constraint has to specify to which other element it refers to.

The primary key definition states that the Airlines_PrimaryKey is a primary key defined for the Airlines element, consisting of the sub element, Name . Note how the msdata:PrimaryKey attribute is used in conjunction with the standard unique construct.

 <xsd:unique name="Airlines_PrimaryKey"                                   msdata:PrimaryKey="true">    <xsd:selector xpath=".//Airlines" />    <xsd:field xpath="Name" />  </xsd:unique> 

The next section constrains the Abbreviation column in an Airlines row to be unique.

 <xsd:unique name="Unique_Airline_Abbreviation">    <xsd:selector xpath=".//Airlines" />    <xsd:field xpath="Abbreviation" />  </xsd:unique>  ... 

Reservations_x0020_CustomerId is defined to be a foreign key. The CustomerId field in the Reservations table must be found in the CustomerId field of some row in the Customer table.

 <xsd:keyref name="Reservations_x0020_CustomerId"                                refer="Customers_PrimaryKey"    <xsd:selector xpath=".//Reservations" />    <xsd:field xpath="CustomerId" />  </xsd:keyref> 

The foreign key Flights_x0020_Abbrev has some rules defined for it.

 <xsd:keyref name="Flights_x0020_Abbrev"                     refer="Unique_Airline_Abbreviation"                     msdata:AcceptRejectRule="Cascade"                     msdata:DeleteRule="SetNull">    <xsd:selector xpath=".//Flights" />    <xsd:field xpath="Airline" />  </xsd:keyref>  ... 

for RuBoard