a player team tables design?

i am a beginner of database design, could anyone please help me to
figure out how to make these two tables work.

1) a "players" table, with columns "name", "age"
2) a "teams" table, which can have one OR two player(s)
a team also has a column "level", which may have values "A", "B",
or "C"

how do you build the "teams" table (the critical question is "do i
need to create two fields" for the maximum two possible players?")

how do you use one query to display the information with the following
columns:
"name", "age", "levelA", "levelB", "levelC" (the later three columns
are integer type, showing how many teams with coresponding level this
player is in).

now suppose i don't have any access to sql server, i save the data
into xml, and load it into a dataset. how could you do the selection
within the dataset? or ahead of that, how do you specify the relations
between "players" and "teams".

the following is the schema file i am trying to make (I still don't
know if i need to specified the primary key... and how to build
relation between them):
<code>
<?xml version="1.0" ?>
<xs:schema id="AllTables" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="AllTables" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Players">
<xs:complexType>
<xs:sequence>
<xs:element name="PlayerID" msdata:AutoIncrement="true"
type="xs:int" minOccurs="0" />
<xs:element name="Name" type="xs:string" minOccurs="0" />
<xs:element name="Age" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Teams">
<xs:complexType>
<xs:sequence>
<xs:element name="TeamID" msdata:AutoIncrement="true"
type="xs:int" minOccurs="0" />
<xs:element name="Level" type="xs:string" minOccurs="0" />
<xs:element name="Player1" type="xs:int" minOccurs="0" />
<xs:element name="Player2" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
</code>In SQL it might be something like this to start with. Start reading about
normalization, relational theory, and table driven applications.

CREATE TABLE Team
( TeamId INTEGER NOT NULL PRIMARY KEY
, TeamName VARCHAR(40) NOT NULL
, TeamLevel CHAR(1) NOT NULL CHECK (TeamLevel IN ('A', 'B', 'C'))
)

CREATE TABLE Player
( PlayerId INTEGER NOT NULL PRIMARY KEY
, PlayerName VARCHAR(40) NOT NULL
, TeamId INTEGER NOT NULL FOREIGN KEY REFERENCES Team(TeamId)
)

"Alan Zhong" <alanchinese@.yahoo.com> wrote in message
news:b09c98a8.0408260048.79651f10@.posting.google.c om...
>i am a beginner of database design, could anyone please help me to
> figure out how to make these two tables work.
> 1) a "players" table, with columns "name", "age"
> 2) a "teams" table, which can have one OR two player(s)
> a team also has a column "level", which may have values "A", "B",
> or "C"
> how do you build the "teams" table (the critical question is "do i
> need to create two fields" for the maximum two possible players?")
> how do you use one query to display the information with the following
> columns:
> "name", "age", "levelA", "levelB", "levelC" (the later three columns
> are integer type, showing how many teams with coresponding level this
> player is in).
> now suppose i don't have any access to sql server, i save the data
> into xml, and load it into a dataset. how could you do the selection
> within the dataset? or ahead of that, how do you specify the relations
> between "players" and "teams".
>
> the following is the schema file i am trying to make (I still don't
> know if i need to specified the primary key... and how to build
> relation between them):
> <code>
> <?xml version="1.0" ?>
> <xs:schema id="AllTables" xmlns=""
> xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
> <xs:element name="AllTables" msdata:IsDataSet="true">
> <xs:complexType>
> <xs:choice maxOccurs="unbounded">
> <xs:element name="Players">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="PlayerID" msdata:AutoIncrement="true"
> type="xs:int" minOccurs="0" />
> <xs:element name="Name" type="xs:string" minOccurs="0" />
> <xs:element name="Age" type="xs:int" minOccurs="0" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Teams">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="TeamID" msdata:AutoIncrement="true"
> type="xs:int" minOccurs="0" />
> <xs:element name="Level" type="xs:string" minOccurs="0" />
> <xs:element name="Player1" type="xs:int" minOccurs="0" />
> <xs:element name="Player2" type="xs:int" minOccurs="0" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:choice>
> </xs:complexType>
> </xs:element>
> </xs:schema>
> </code|||And this would be one way to express the query that you're looking for.
Feel free to add age to schema and query.

SELECT PlayerName
, (SELECT COUNT(*) FROM Team WHERE TeamId = Player.TeamId AND TeamLevel =
'A') As LevelA
, (SELECT COUNT(*) FROM Team WHERE TeamId = Player.TeamId AND TeamLevel =
'B') As LevelB
, (SELECT COUNT(*) FROM Team WHERE TeamId = Player.TeamId AND TeamLevel =
'C') As LevelC
FROM Player

"Larry S" <nospam@.bugus.zzz> wrote in message
news:M17Yc.107934$TI1.103116@.attbi_s52...
> In SQL it might be something like this to start with. Start reading about
> normalization, relational theory, and table driven applications.
> CREATE TABLE Team
> ( TeamId INTEGER NOT NULL PRIMARY KEY
> , TeamName VARCHAR(40) NOT NULL
> , TeamLevel CHAR(1) NOT NULL CHECK (TeamLevel IN ('A', 'B', 'C'))
> )
> CREATE TABLE Player
> ( PlayerId INTEGER NOT NULL PRIMARY KEY
> , PlayerName VARCHAR(40) NOT NULL
> , TeamId INTEGER NOT NULL FOREIGN KEY REFERENCES Team(TeamId)
> )
> "Alan Zhong" <alanchinese@.yahoo.com> wrote in message
> news:b09c98a8.0408260048.79651f10@.posting.google.c om...
>>i am a beginner of database design, could anyone please help me to
>> figure out how to make these two tables work.
>>
>> 1) a "players" table, with columns "name", "age"
>> 2) a "teams" table, which can have one OR two player(s)
>> a team also has a column "level", which may have values "A", "B",
>> or "C"
>>
>> how do you build the "teams" table (the critical question is "do i
>> need to create two fields" for the maximum two possible players?")
>>
>> how do you use one query to display the information with the following
>> columns:
>> "name", "age", "levelA", "levelB", "levelC" (the later three columns
>> are integer type, showing how many teams with coresponding level this
>> player is in).
>>
>> now suppose i don't have any access to sql server, i save the data
>> into xml, and load it into a dataset. how could you do the selection
>> within the dataset? or ahead of that, how do you specify the relations
>> between "players" and "teams".
>>
>>
>> the following is the schema file i am trying to make (I still don't
>> know if i need to specified the primary key... and how to build
>> relation between them):
>> <code>
>> <?xml version="1.0" ?>
>> <xs:schema id="AllTables" xmlns=""
>> xmlns:xs="http://www.w3.org/2001/XMLSchema"
>> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
>> <xs:element name="AllTables" msdata:IsDataSet="true">
>> <xs:complexType>
>> <xs:choice maxOccurs="unbounded">
>> <xs:element name="Players">
>> <xs:complexType>
>> <xs:sequence>
>> <xs:element name="PlayerID" msdata:AutoIncrement="true"
>> type="xs:int" minOccurs="0" />
>> <xs:element name="Name" type="xs:string" minOccurs="0" />
>> <xs:element name="Age" type="xs:int" minOccurs="0" />
>> </xs:sequence>
>> </xs:complexType>
>> </xs:element>
>> <xs:element name="Teams">
>> <xs:complexType>
>> <xs:sequence>
>> <xs:element name="TeamID" msdata:AutoIncrement="true"
>> type="xs:int" minOccurs="0" />
>> <xs:element name="Level" type="xs:string" minOccurs="0" />
>> <xs:element name="Player1" type="xs:int" minOccurs="0" />
>> <xs:element name="Player2" type="xs:int" minOccurs="0" />
>> </xs:sequence>
>> </xs:complexType>
>> </xs:element>
>> </xs:choice>
>> </xs:complexType>
>> </xs:element>
>> </xs:schema>
>> </code>