By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,277 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,277 IT Pros & Developers. It's quick & easy.

a player team tables design?

P: n/a
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>
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <al*********@yahoo.com> wrote in message
news:b0**************************@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>

Jul 20 '05 #2

P: n/a
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" <no****@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" <al*********@yahoo.com> wrote in message
news:b0**************************@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>


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.