August 26, 2004 at 3:03 am
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>
August 26, 2004 at 12:51 pm
oh.... i have the xsd file ready, how do i use Visual Studio .NET 2003 DataSet control to read this file and put it into the application?
August 26, 2004 at 2:43 pm
I will first off tell you that I don't know the answer to your XML questions. I know enough about XML to avoid it almost entirely. It's barely an OK method of data transport and it is a horrendously bad method of data management.
As far as the situation you described here's what I would do:
CREATE TABLE TeamLevel (
Level char(1) PRIMARY KEY)
GO
CREATE TABLE Team (
TeamID int IDENTITY PRIMARY KEY
, Name varchar(50) NOT NULL
, Level char(1) REFERENCES TeamLevel(Level) NOT NULL)
GO
CREATE TABLE Player (
PlayerID int IDENTITY PRIMARY KEY
, FirstName varchar(20) NOT NULL
, LastName varchar(30) NOT NULL
, YrBorn int NOT NULL)
/*Instead of storing the players age and then having to keep it up to date, it is preferable to store the information needed to calculate their age. Here I have just used the year, which may, or may not be accurate enough, you might want to store their birthdate as a datetime column which would allow you to more accurately calculate their age, but the principle is the same.*/
GO
CREATE TABLE TeamPlayer (
TeamID int REFERENCES Team(TeamID) NOT NULL
, PlayerID int REFERENCES Player(PlayerID) NOT NULL)
GO
ALTER TABLE TeamPlayer ADD PRIMARY KEY (TeamID,PlayerID)
GO
/*Since a team may have no more than two players the following triggers are used to enforce that rule. In a real system you would probably want to create a custom message and raise it rather than just rolling the transactions back with no explanation...*/
CREATE TRIGGER TeamPlayer_INSERT ON TeamPlayer AFTER INSERT
AS
IF @@ROWCOUNT<>1 RETURN
DECLARE @TeamPlayerCount int,
@TeamID int
SET @TeamID = (select TeamID from inserted)
SET @TeamPlayerCount = (SELECT count(TeamID) FROM TeamPlayer
WHERE TeamID = @TeamID)
IF @TeamPlayerCount > 2 rollback
GO
CREATE TRIGGER TeamPlayer_UPDATE ON TeamPlayer AFTER UPDATE
AS
IF @@ROWCOUNT<>1 RETURN
DECLARE @TeamPlayerCount int,
@TeamID int
SET @TeamID = (select TeamID from inserted)
SET @TeamPlayerCount = (SELECT count(TeamID) FROM TeamPlayer
WHERE TeamID = @TeamID)
IF @TeamPlayerCount > 2 rollback
/*The following is the query to return the requested results using an inline view and a cross-tab query*/
SELECT FirstName
, LastName
, Age
, SUM(CASE level WHEN 'a' THEN TeamCount ELSE 0 END) AS "Level A"
, SUM(CASE level WHEN 'b' THEN TeamCount ELSE 0 END) AS "Level B"
, SUM(CASE level WHEN 'c' THEN TeamCount ELSE 0 END) AS "Level C"
FROM (
SELECT FirstName
, LastName
, (datepart(yy, getdate())-YrBorn) AS Age
, t.level AS Level
, count(*) as TeamCount
FROM Player p
JOIN TeamPlayer tp
ON p.PlayerID = tp.PlayerID
JOIN Team t
ON t.TeamID = tp.TeamID
GROUP BY FirstName
, LastName
, (datepart(yy, getdate())-YrBorn)
, t.level
) tmp
GROUP BY FirstName, LastName, Age
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
August 26, 2004 at 5:00 pm
i am making a small application (althought complicate) for my friends. they don't have sql server or ms access installed. and i don't want to host a server for the data source. isn't XML the best choice? what else i can do? i experienced the poor side of XML already, lack of flexible.
August 26, 2004 at 6:11 pm
Why not use MSDE, or MySQL or any number of other freely available DBMS's out there? XML is NEVER the best choice for anything, at its best XML is an incredibly inefficient mechanism for data transmission. The lack of flexibility (which is inherent in its hierarchical nature) is just the start of the problems with XML, a total lack of data integrity is the biggest problem with using it for data management. Why would you want to write your own XML DBMS when there are free SQL DBMS's availble?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
August 27, 2004 at 11:15 am
If you use MSDE, you might want to consider using a free GUI front-end such as TOAD for SQL in order to create the tables, easily view data, etc. Do a search on the Internet. . .there are several out there.
August 27, 2004 at 11:26 am
if i finish the program, every user needs to download and install MSDE in their computers?
August 27, 2004 at 11:31 am
Yes.
How many uses are you talking about here? Is this supposed to be a shared dataset?
August 27, 2004 at 11:35 am
Oops. . ..Meant "How many users" will be using this application?
August 27, 2004 at 11:36 am
That depends on the application design. If it is to be a "Fat" client that can be used on a single workstation without network connectivity, then yes, each client would need msde. The problem with this kind of application is that collaborative efforts become very difficult to achieve. If it is going to be a web-based client that needs to connect to an web (or application) server then msde could reside on the web server or another server that can be accessed by the web server. There are literally hundreds of possible configuration options, it all comes down to how you want to do it and what resources you have at your disposal.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 8, 2004 at 6:24 am
i think also that you can write into txt files (file for every table) ..
i also see TOAD for SQL Server Server .. it is very good but does it support to make relationships?
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply