0

Database Model TSQL Tutorial for Online Gaming Use

Emeka OkekeMar 15, 2020 | edited Mar 16, 2020 - by @sqldibia

Alright fellas, here is a SQL evaluation exercise and solutions on database for online gamers who participate in teams.

Objective

We have a list of online gamers who participate in teams. We need to keep track of some basic information about our players as well as which players are on which teams.

There is a list of Players and a list of Teams in the form of existing tables, and there are some stored procedures to access those tables, but there are a lot of problems with the existing design.

Your job is to fix the existing data structure by modifying, adding and/or removing components as necessary in order to create a solid, well designed database that meets the requirements and deliverables defined below.

Requirements

  • Player Table:
    • Each player must have the following information:
      • ID
      • First Name
      • Last Name
      • Date Of Birth
    • Each player may belong to zero or more teams
    • A team must exist before a player can join it
  • Team Table:
    • Each team must have a name
    • Each team may have zero or more members
    • Each team with participants may have one and only one leader
  • Both Stored Procedures
    • Add an integer return code: 0 indicates success, non-zero indicates an error. If you like you can create different return codes for specific errors, or you can just return any nonzero value to indicate an error.
  • SetPlayer Stored Procedure
    • Allow caller to create a new player, edit and existing player or delete an existing player using one stored procedure (SetPlayer)
    • When creating a new player the database should generate a new ID for the caller and return it to the caller ? i.e. the caller should not have to pass in an ID for a new user, and the newly generated ID should be returned to the caller in some useful way
    • When creating a new user, an error should be returned if any of the required fields are missing
    • When editing a user, the caller should only have to provide the ID and any fields that he or she wants to change. For example, the caller should be able to pass in just the ID and the LastName value in order to change a player?s last name
    • The caller should be able to set / update the teams that a player is associated with in one call, and the stored procedure should accommodate any number of teams in case we add more teams in the future (it is OK to have the caller always pass in a complete list of teams that the player is associated with and replace the entire list)
  • GetPlayer Stored Procedure
    • All arguments should be treated as filters. If any argument is passed in, it will be used to filter the result set, if any argument is not passed in, the result set will not be filtered on that field ? all arguments should therefore be optional
    • It is OK for the @Team argument to only filter on a single team ? i.e. it is not necessary to come up with a way to filter on more than one team in a single call

Deliverables

  1. Tables
    • Provide a Player table and a Team table, as described above
    • You may modify the existing tables or create new ones
    • The initial tables must start with the same sample data provided
  2. Stored Procedures
    • Provide a SetPlayer stored procedure and a GetPlayer stored procedure, as described above
    • You may modify the existing stored procedures or create new ones
    • Both stored procedures must provide a return code as described above

Considerations

  • Obviously a complete example would include a Set/Get procedure for the Team table but for this exercise it is acceptable to create / modify the Team table by hand
  • All of the T-SQL in the provided DDL is written in mixed case, please follow that convention when putting together your solution.
  • Please use tabs and not spaces in your final table DDL and stored procedure code.
  • Please provide your final solution in a simple text file with a .txt or .sql extension.
  • Your solution should be in the form of a script that can be executed all at once (use GO statement separators as needed) to add/edit/delete the tables, stored procedures, etc. that are required to meet the requirements.
  • The table structure is intentionally flawed and incomplete, please be sure to add/change/remove the DDL as needed to create well structured tables that meet the basic requirements of primary keys, referential integrity, etc.
  • The stored procedures here are intentionally horribly written, please be sure to remove any poorly written constructs / logic and make the procedures as logical, efficient and easy to read as possible. Do not assume that any logic in either of the stored procedures needs to be kept in the final product. The only part of the stored procedure code that you must keep is the formatting ? please do your best to make your final code follow the formatting conventions used.

Final Notes

Please provide your solution in the form of a T-SQL script that can be executed on a SQL Server instance all at once (use GO statement separators as needed) to add/edit/delete the tables, stored procedures, etc. that are needed to meet the requirements.

We are using SQL Server 2016 so please be sure to only use statements that are supported on that version of SQL Server.

We are looking for a simple solution. Please do not add any unnecessary complication to the DDL or stored procedure code or use any components or constructs that are not necessary to complete the evaluation. The ideal solution will be well formatted, uncomplicated and easy to read.

Then we have the following scripts that were deliberately badly written.

Table Creation and Population DDL


create table Player
(
ID int,
LastName varchar(50),
FirstName varchar(50),
DOB varchar(10),
Team1 varchar(50),
Team2 varchar(50),
Team3 varchar(50)
)
create table Team
(
ID int,
TeamName varchar(50),
TeamLeader varchar(100)
)
insert into Player values
(100, 'Smith', 'Sally', '04/18/1995', 'Red', null, null),
(101, 'Jones', 'James', '05/22/1996', 'Blue', 'Red', null),
(102, 'Brown', 'Bobby', '11/18/1995', 'Green', 'Blue', null),
(103, 'Jones', 'Janet', '08/22/1998', 'Blue', 'Green', 'Red'),
(104, 'Magoo', 'Matthew', '12/20/1994', 'Green', null, null),
(105, 'Wayfield', 'Wanda', '05/05/1995', 'Red', null, null)
insert into Team values
(10, 'Red', 'Sally Smith'),
(20, 'Blue', 'Bobby Brown'),
(30, 'Green', 'Matthew Magoo')

SetPlayer Stored Procedure


create procedure GetPlayer
@ID int,
@LastName varchar(50),
@FirstName varchar(50),
@Team varchar(50)
as begin
set nocount on
if @ID is not null begin
select *
from Player
where ID = @ID
end else if @LastName is not null begin
select *
from Player
where LastName = @LastName
end else if @FirstName is not null begin
select *
from Player
where FirstName = @FirstName
end else if @Team is not null begin
select *
from Player
where Team1 = @Team
or Team2 = @Team
or Team3 = @Team
end
end

My Solution

Here's part of the script to create the tables including the referential integrity constraints:


create table dbo.Player
(
ID int identity(1,1) primary key,
LastName varchar(50) not null,
FirstName varchar(50) not null,
DOB varchar(10) not null
)
go
create table dbo.Team
(
ID int identity(1,1) primary key,
TeamName varchar(50) not null,
TeamLeaderID int foreign key references Player(ID) null
)
go
create table dbo.TeamMembers
(
PlayerID int foreign key references Player(ID) not null,
TeamID int foreign key references Team(ID) not null
)
go

The referential integrity constraints are not null and foreign key. We have not null declaration on columns LastName, FirstName, TeamName, PlayerID, and TeamID because those values are required during insert.

There is a many-to-many relationship between Teams and Players. This means many players can belong to multiple teams. To implement this in our data model, we need a bridge table, hence why we created the additional table dbo.TeamMembers. This table has only foreign key columns PlayerID and TeamID referencing Player and Team tables respectively on those tables' ID column.

Here's part of the script to load the tables with data:


set identity_insert dbo.Player on
insert into dbo.Player (ID,LastName,FirstName,DOB)
values
(100, 'Smith', 'Sally', '04/18/1995'),
(101, 'Jones', 'James', '05/22/1996'),
(102, 'Brown', 'Bobby', '11/18/1995'),
(103, 'Jones', 'Janet', '08/22/1998'),
(104, 'Magoo', 'Matthew', '12/20/1994'),
(105, 'Wayfield', 'Wanda', '05/05/1995')
set identity_insert dbo.Player off
go
set identity_insert dbo.Team on
insert into dbo.Team(ID,TeamName,TeamLeaderID)
values
(10, 'Red', null),
(20, 'Blue', null),
(30, 'Green', null)
set identity_insert dbo.Team off
go
insert into dbo.TeamMembers values
(100, 10),
(101, 20),
(101, 10),
(102, 30),
(102, 20),
(103, 20),
(103, 30),
(103, 10),
(104, 30),
(105, 10)
go
update dbo.Team
set
  TeamLeaderID=case
  ID when 10 
  then 100 
  when 20 
  then 102 
  when 30 
  then 104 
  end
go

Notice we set identity_insert on before inserting into the tables dbo.Player and dbo.Team. We did this to override the auto-generated identity primary keys we used during those tables definition above.

Here's the refactored/new setPlayer stored procedure:



create procedure SetPlayer
	@ID int=null,
	@LastName varchar(50)=null,
	@FirstName varchar(50)=null,
	@DOB varchar(10)=null,
	@Teams varchar(4000)=null,
	@IsDelete bit=null,
	@RetID int=null out
as begin
	set nocount on

	if @IsDelete=1
	    begin

			delete from 
			dbo.TeamMembers 
			where 
			PlayerID=@ID

			update dbo.Team
			set 
			TeamLeaderID=null
			where 
			TeamLeaderID=@ID

			delete from 
			dbo.Player 
			where 
			ID=@ID

			return 0

		end

	if @ID is null begin

	    if @LastName is null or @FirstName is null or @DOB is null
	        return -1

		insert
		into Player (LastName,FirstName,DOB)
		values (				
				@LastName,
				@FirstName,
				@DOB
				)
         set @ID=SCOPE_IDENTITY()

	end
    else begin

		update Player
		set LastName = isnull(@LastName,LastName),
		FirstName = isnull(@FirstName,FirstName),
		DOB = isnull(@DOB,DOB)
		where ID = @ID

	end
	
	if @ID is not null and @Teams is not null and @Teams!=''
		begin
			delete from dbo.TeamMembers where PlayerID=@ID

			insert 
			into dbo.TeamMembers 
			select @ID,tt.ID 
			from(select ltrim(rtrim(value)) as Team 
			from STRING_SPLIT(@Teams, ',')) as t
			inner join dbo.Team as tt 
			on tt.TeamName=t.Team
		end

    
	  set @RetID=@ID

	  return 0

end
go

Finally here is the new getPlayer stored procedure



create procedure GetPlayer
	@ID int =NULL,
	@LastName varchar(50)=NULL,
	@FirstName varchar(50)=NULL,
	@Team varchar(50)=NULL
as begin
	set nocount on

	select 
	p.ID,p.FirstName,
	p.LastName,
	p.DOB,
	t.TeamName
	from dbo.Player as p
	left join dbo.TeamMembers as tm 
	on p.ID=tm.PlayerID
	inner join dbo.Team as t
	on t.ID=tm.TeamID
	WHERE 
	(@ID is null or p.ID=@ID) and
	(@LastName is null or p.LastName like '%' + @LastName + '%') and
    (@FirstName is null or p.FirstName like '%' + @FirstName + '%') and
	(@Team is null or t.TeamName like '%' + @Team + '%')

	return 0

end
go

Note that we set these stored procedure's parameters' initial value to null to make them optional. This allows us to call the SP by passing only one or some of the parameters. This means we can call the SP like EXEC PROC dbo.GetPlayer @ID=1. Let me know what you think in the comment. Do you think this could be implemented differently?

Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia