Design Document: TRA Race Entry System - Teesside University Project

This document presents the design of a TRA Race Entry System, encompassing a conceptual data model represented by a UML class diagram, a data dictionary defining race entrant points calculation, and a sequence diagram illustrating the race registration process. It further includes an implementation model UML class diagram, data definition language (DDL) scripts for creating the database schema and tables (Member, Race, Fees, Race_Outcome), and SQL queries for various functionalities such as listing races not entered by a member, inserting a new race, calculating total points for each member, and generating a list of race entries. This resource is helpful for students, and Desklib provides many such solved assignments and past papers.

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];