TRA Race Entry System Author: Name + Number.

Verified

Added on  2023/03/31

|7
|471
|85
AI Summary
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)

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
i
TRA Race Entry System
Author: Name + Number
Teesside University
Design Document
Systems Design and Databases

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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
Document Page
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
Document Page
Figure 2: Sequence Diagram for Race Entry
3 UML CLASS DIAGRAM – IMPLEMENTATION MODEL
The following diagram shows….

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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,
Document Page
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
Document Page
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];
1 out of 7
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]