TRA Race Entry System Author: Name + Number.

Use SQL Server Management Studio finished According to 'AssessmentGuide's instructions and requirements Answer 'DesignDocument Race Entry's questions(some questions can prt sc the picture)

i
TRA Race Entry System
Author: Name + Number
Teesside University
Design Document
Systems Design and Databases

CONTENTS
1 DATA MODEL...................................................................................................................................................1
1.1 UML Class Diagram – Conceptual Model...............................................................................................1
1.2 Data Dictionary.......................................................................................................................................1
2 Sequence Diagram for Race Registration........................................................................................................1
3 UML Class Diagram – Implementation Model................................................................................................2
4 Data Definition Language................................................................................................................................3
4.1 Schema...................................................................................................................................................3
4.2 Tables......................................................................................................................................................3
5 SQL QuerieS.....................................................................................................................................................4
5.1 Query 1...................................................................................................................................................4
5.2 Query 2...................................................................................................................................................4
5.3 Query 3...................................................................................................................................................4
5.4 Query 4...................................................................................................................................................5

1 DATA MODEL
1.1 UML CLASS DIAGRAM – CONCEPTUAL MODEL
The following model shows…
Figure 1: UML Conceptual Class Diagram
1.2 DATA DICTIONARY
The following data dictionary definition is for the operation which calculates the race entrant’s points.
…
For each member
For each race
Points =+ Race_outcome.points
Print member.name + points
End for loop
2 SEQUENCE DIAGRAM FOR RACE REGISTRATION
1

Figure 2: Sequence Diagram for Race Entry
3 UML CLASS DIAGRAM – IMPLEMENTATION MODEL
The following diagram shows….

Figure 3: UML Implementation model
4 DATA DEFINITION LANGUAGE
4.1 SCHEMA
CREATE SCHEMA RaceSchema
4.2 TABLES
Create table Member (
member_id INT Primary Key,
member_name Varchar(200),
member_age_actagory Varchar(200),
member_TRA_acategory INT,
member_entry_year DATE,
member_club_name Varchar(200)
);
Create table Race (
race_id INT Primary Key,
race_name Varchar(200),
registration_time Varchar(200),
race_start_time TIME,
parking_ref INT,
race_distance Varchar(200),
race_terrain Varchar(200),
race_date DATE
);
Create table Fees (
race_id INT,

age_type Varchar(200),
memership CHAR(4),
fees_amount DECIMAL(12,2),
PRIMARY KEY (race_id, age_type, memership),
CONSTRAINT FK_RaceFees FOREIGN KEY (race_id) REFERENCES Race(race_id)
);
Create table Race_Outcome (
member_id INT,
race_id INT,
position INT,
category CHAR(4),
points INT,
PRIMARY KEY (race_id, member_id),
CONSTRAINT FK_Race FOREIGN KEY (race_id) REFERENCES Race(race_id),
CONSTRAINT FK_Member FOREIGN KEY (member_id) REFERENCES Member(member_id)
);
5 SQL QUERIES
5.1 QUERY 1
Build a list of races that the specified member has not already entered.
select [dbo].[Race].* from [dbo].[Race]
LEFT JOIN [dbo].[Race_Outcome] On [dbo].[Race].[race_id] = [dbo].[Race_Outcome].[race_id]
WHERE [dbo].[Race_Outcome].[race_id] IS NULL;
5.2 QUERY 2
Insert a new race entry into the database.
Insert Into [dbo].[Race] values (1, 'SDD Race 3 at High Hill', '10.00 – 10.30', '10:45:00', 267578, '6km',
'Exposed open hillside, mainly footpaths but some open fields on lower slopes', '2012-12-16');
Insert Into [dbo].[Fees] Values (1, 'senior', 'Yes', 5.00);
Insert Into [dbo].[Fees] Values (1, 'junior and veterans', 'Yes', 3.00);
Insert Into [dbo].[Fees] Values (1, 'senior', 'No', 7.00);
Insert Into [dbo].[Fees] Values (1, 'junior and veterans', 'No', 5.00);
5.3 QUERY 3

The purpose of this query is to count the total number of points for every member
Select [member_name], SUM([points]) AS TotalPoint
From [dbo].[Member] Inner Join [dbo].[Race_Outcome]
ON [dbo].[Member].[member_id] = [dbo].[Race_Outcome].[member_id]
Group By [member_name];
5.4 QUERY 4
The purpose of this query generate a list of entries for all races (raceid, venue, date, bib num,
member name, age cat at time of entry, club at time of entry, position, points)
Select * from [dbo].[Member]
Inner Join [dbo].[Race_Outcome]
ON [dbo].[Member].[member_id] = [dbo].[Race_Outcome].[member_id]
Inner Join [dbo].[Race]
ON [dbo].[Race_Outcome].[race_id] = [dbo].[Race].[race_id]
Inner Join [dbo].[Fees]
On [dbo].[Race].[race_id] = [dbo].[Fees].[race_id];