June, 2017 DAT602 Todd Cochrane Contents MILESTONE ONE 4 1. Game Description 5 1.1 Base Idea 5 1.2 Logging In 5 1.3 Game Lobby 5 1.4 Gameplay 5 1.4.1 One on One Games 5 1.4.2 Two on Two Games 6 1.4.3 Objective and Scoring 6 1.5 Administrators 6 2. Storyboards 7 Storyboard 1 Login Screen 7 Storyboard 2 User Not Found 7 Storyboard 3 Register a new Account 8 Storyboard 4 Main Game Lobby 9 Storyboard 5 One vs One Game Screen 1 10 Storyboard 6 One vs One Gameplay Screen 11 Storyboard 7 Two vs Two Game Screen 1 12 Storyboard 8 Two vs Two Gameplay Screen 13 Storyboard 9 Placing Error 14 Storyboard 10 Hit Opponent 14 Storyboard 11 Opponent Missed 14 Storyboard 12 You Missed 14 Storyboard 13 You Sunk a Ship 14 Storyboard 14 Opponent Hit 14 Storyboard 15 You Lose 15 Storyboard 16 Ship was Sunk 15 Storyboard 17 You Won 15 Storyboard 18 Administrator Window 16 Storyboard 19 Administrator Create/Edit User 17 Storyboard 20 Password Incorrect 18 Storyboard 21 Account has been Locked 19 2.1 Screen Design Rationale 20 2.1.1 Login/Registration Screen 20 2.1.2 Game Lobby 20 2.1.3 Game Screen 20 2.1.4 Administrator Screens 20 3. Entity Relationship Diagram 21 3.1 Entity Relationship Diagram Rationale 22 3.1.1 Player, Player/Board and Board 22 3.1.2 Player, Session and Game 22 3.1.3 Board, Game/Board and Game 22 3.1.4 Board and Board/Tile 22 3.1.5 Board Tile, Ship/Tile and Ship Type 22 4. CRUD Table 23 4.1 CRUD Analysis 24 5. SQL 26 5.1 DDL 26 5.1.1 Player Table 26 5.1.2 Board Table 27 5.1.3 Player/Board Table 27 5.1.4 Game Table 27 5.1.5 Game/Board Table 27 5.1.6 Session Table 28 5.1.7 Board Tile Table 28 5.1.8 Ship Table 28 5.1.9 Ship/Tile Table 29 5.2 Test Data and Queries 29 MILESTONE TWO 31 1. Design Alterations from Milestone One 32 1.1 Logical and Physical Design Alterations 32 1.1.1 Logical and Physical Design Alterations Rationale 33 1.2 CRUD Table 34 1.2.1 CRUD Analysis 35 2. SQL Procedures and Transactions 36 2.1 Player Registration and Login 36 2.1.1 Find User 36 2.1.2 Check Username and Password/Login Attempts and Locking 36 2.1.3 Add New User 36 2.2 Player Selection and Game Creation 36 2.2.1 Online Players and Active Games 36 2.2.2 Creating New Games 37 2.3 Live Gameplay 37 2.3.1 Placing Ships 37 2.3.2 Player Movement 37 2.3.3 Exiting and Ending Games 38 2.4 Administrative Controls 38 2.4.1 Ending Running Games 38 2.4.2 Creating New Players and Updating Existing Players 38 2.4.3 Deleting Players 38 3. Description of Multi-Player Support and ACID 39 3.1 The Importance of ACID 39 3.1.1 Atomicity 39 3.1.2 Consistency 39 3.1.3 Isolation 39 3.1.4 Durability 40 3.2 Transactions 40 3.3 Multi-player Support 41 4. SQL Test Data 42 5. VB.NET Command Line Test Application 42 5.1 Player Registration and Login 42 5.2 Player Selection for Games 42 5.3 Live Gameplay 42 5.4 Administrative Controls 42 References 43 MILESTONE ONE 1. Game Description 1.1 Base Idea The idea for a multi-user game project is online Battleships. the game will be a turn based point and click game consisting of one on one battles or two on two battles and the player/team that sinks all their opponents battleships will be the winner, however, each game will last for a maximum of 15 minutes and if this time runs out the player/team with the most remaining ships will be the victor. 1.2 Logging In When first opened users will be presented with a log in screen. In this screen the user will enter a username at which point this will be checked against the database, if the username is already registered the user will be prompted for their password at which point they will have 5 attempts to login before their account will be locked, however, if the username was not found in the database then the user will be asked if they would like to register. When registering a new account the user will be required to enter an email address and pick a password. 1.3 Game Lobby Once registered or logged in users will be taken to a game lobby, within the lobby they will be able to see a list of the players that are currently online and a list of games that are currently being played. Within the lobby users will have the options to log out, create a new game or join an existing game and, if the user is an administrator, they will also be able to open the admin window from here. When creating a new game the user will have an option of 1 on 1 battles or 2 vs 2 battles, each with similar styles of gameplay. 1.4 Gameplay When users are in a game they will see their battle ships in one panel and see their opponents field in another, these will be the playing spaces. Gameplay will be similar in both types of game (One on One and Two on Two) but there will be some differences. 1.4.1 One on One Games In one on one games each player will have a 10×10 grid to place their ships onto, each player will have 2 four tile ships, 2 three tile ships and 2 two tile ships. Players will place their ships by selecting an orientation (horizontal or vertical) the size of the ship (two, three or four tiles) and the X and Y positions (e.g. A3). Once placed on the grid the players own ships will be depicted by dark grey tiles. The game will then start once both players have placed all 6 of their ships on the grid. During the game, each player will take turns at guessing where their opponents ships are located by clicking on the tiles within the playing space, on each click the selected tile will change colour to represent either a hit or a miss, a miss will be depicted by a blue tile and a hit will be depicted by a red tile. For each hit the player will accumulate points towards their score. The player that sinks all their opponents ships or the player with the most remaining ships when the time is up will be the victor. 1.4.2 Two on Two Games In Two on Two games players will be placed into a team and given a colour of either green or yellow. Each team will have a 10×20 grid to place their ships onto, each player in the team will have 2 four tile ships, 2 three tile ships and 2 two tile ships. Players will place their ships by selecting an orientation (horizontal or vertical) the size of the ship (two, three or four tiles) and the X and Y positions (e.g. J14). Once placed on the grid the players ships will then be depicted by either a green or a yellow tile, depending on which colour they were assigned at the beginning. The game will start once all 4 players have placed all 6 of their ships on the grid. During the game, each player will take turns at guessing where their opponents ships are located by clicking on the tiles within the playing space, on each click the selected tile will change colour to represent either a hit or a miss, a miss will be depicted by a blue tile and a hit will be depicted by a red tile. For each hit the player will accumulate points towards their score. The team that sinks all their opponents ships or the team with the most remaining ships when the time is up will be the victor. 1.4.3 Objective and Scoring The main objective of the game is quite simple, sink all your opponents ships! Or as many as you can. Scores will be calculated based on a combination of the time taken to win, the number of clicks to win and how many ships a player has left. Players will accumulate points for each successful hit, the number of points earned will depend on how many times the player has missed an opponents ships, the less misses the player has had the more points theyll earn. At the end of the game the player, or both players in the team, will receive a bonus for the number of ships they have remaining and the amount of time thats remaining, if they manage to sink all their opponents ships before the time is up that is. 1.5 Administrators Certain players will be given administrator privileges, this will give them access to the admin controls which will allow them to do certain things that normal players cannot. This includes killing games that are in progress, creating new users, editing an existing users details or even deleting a users account. When creating new users, or editing existing users, an administrator will also be able to lock or unlock accounts and even grant another user admin privileges. 2. Storyboards Storyboard 1 Login Screen1.4 1.3 1.2 1.1Textbox for username to be entered, if username is found in the database this will become disabled and the password textbox, along with a button for the user to go back, will appear Textbox for password appears if username is found in Database, if not move to storyboard 2 Login button, if username and password are valid move to storyboard 4, if not move to storyboard 20, if user makes too many attempts with the wrong password their account will be locked (Move to storyboard 21) Button for user to go back to username entry.Storyboard 2 User Not Found2.2 2.3 2.12.1 Dialog will appear if username was not found in Database 2.2 If yes move to storyboard 3 2.3 If no move to Storyboard 1Storyboard 3 Register a new Account3.1 3.2 3.3 3.4 3.53.1 Textbox for user to enter an email address 3.2 Textbox for users entered Username, this will be disabled so that they cannot change it during the registering process. 3.3 Textbox for user to enter and create a Password 3.4 Button for user to complete registration, upon completion move to storyboard 4 3.5 Button for user to go back to username entry and choose a different username. Storyboard 4 Main Game Lobby4.7 4.3 4.1 4.2 4.4 4.5 4.6List box displaying players that are currently online and their current highest score. List box displaying a list of currently active games and how many players are in the game, user can select a game to join. Button to start a new 1v1 game, this will challenge the currently selected player from the online players list box. (Move to Storyboard 5) Button to start a new 2v2 game, this will challenge the currently selected players from the online players list box. (Move to Storyboard 7) Button to open the admin console (Storyboard 18) only visible if user is an administrator Button for user to log out (go to Storyboard 1) Button for player to join the selected existing game (Storyboard 5/ Storyboard 7) Storyboard 5 One vs One Game Screen 15.3 5.4 5.5 5.2 5.15.1 Player 1s Playing Grid 5.2 Player 2s Playing Grid 5.3 All 6 of the current players ships, they will be able to drag and drop them into position, right clicking to rotate 90 degrees for horizontal placement, they will be able to sit next to each other but not on top of one another, once both players have placed all their ships the game will start 5.4 Displays opponents current score 5.5 Displays current game time Storyboard 6 One vs One Gameplay Screen6.1 6.2 6.3 6.4 6.5 6.66.1 Tiles that are grey will indicate the users ship locations Tiles will turn blue to indicate a missed attack Tiles will turn red to indicate a successful hit Display players current score Display opponents current score Displays how long the game has been running for in minutes and seconds Storyboard 7 Two vs Two Game Screen 17.6 7.5 7.4 7.3 7.1 7.27.1 Player 1 and 2s playing grid 7.2 Player 3 and 4s playing grid 7.3 All 6 of the current players ships, they will be able to drag and drop them into position, right clicking to rotate 90 degrees for horizontal placement, they will be able to sit next to each other but not on top of one another, once all four players have placed their ships the game will start 7.4 Displays the current time in minutes and seconds. 7.5 Displays which player is about to move. 7.6 Displays opponents statistics, including their score, their hit/miss totals for the current game and how many ships they have left on the board. Storyboard 8 Two vs Two Gameplay Screen8.10 8.8 8.7 8.9 8.2 8.1 8.6 8.4 8.5 8.38.1 Player 1 and 2s playing grid 8.2 Player 3 and 4s playing grid 8.3 Displays players score 8.4 Displays number of missed attacks player has made 8.5 Displays number of hit attacks the player has made 8.6 Displays how many ships a player has left 8.7 Tiles will turn yellow to indicate the locations of Player 1s Ships 8.8 Tiles will turn yellow to indicate the locations of player 2s ships 8.9 Tiles will turn blue to indicate a missed attack 8.10 Tiles will turn red to indicate a successful hit attack Storyboard 9 Placing ErrorStoryboard 10 Hit Opponent9.1 Error message that is displayed if player tries to place a ship off the grid or on top of/overlapping another ship10.1 Dialog that is displayed when you hit an opponents shipStoryboard 11 Opponent MissedStoryboard 12 You Missed11.1 Dialog that is displayed when an opponent misses your ships12. Dialog that is displayed when you miss you opponents shipsStoryboard 13 You Sunk a ShipStoryboard 14 Opponent Hit13.1 Dialog that is displayed when you sink an opponents ship14.1 Dialog that is displayed when you are hit by an opponentStoryboard 15 You LoseStoryboard 16 Ship was Sunk15.1 Dialog that is displayed when all of your battleships have been sunk16.1 Dialog that is displayed when an opponent sinks your shipStoryboard 17 You Won17.1 Dialog that is displayed when you sink all of an opponents ships Storyboard 18 Administrator Window18.6 18.4 18.2 18.5 18.3 18.118.1 List box displaying a list of currently active games and the number of players in that game 18.2 List box displaying a list of the currently registered users that are stored in the database 18.3 Button that can be used to kill the selected game 18.4 Button for an administrator to create a new user (Storyboard 19) 18.5 Button for an administrator to update the selected users details (Storyboard 19) 18.6 Button for an administrator to delete the selected user (Prompt for confirmation) Storyboard 19 Administrator Create/Edit User19.6 19.4 19.2 19.7 19.5 19.3 19.119.1 Text box for administrator to enter/alter a Username 19.2 Textbox for administrator to enter/alter a password 19.3 Textbox for administrator to enter/alter an email address 19.4 Textbox for administrator to enter/alter a High score 19.5 Checkbox to indicate if players account is locked or not 19.6 Checkbox to indicate if player has administrator privileges 19.7 Button to save changes, will display create if creating a new player or update if altering an existing player, when altering an existing player the fields will auto fill with the selected players information Storyboard 20 Password Incorrect20.1 20.2 20.3 20.420.1 Textbox for user to alter the username they would like to login with 20.2 Textbox for user to alter the password they are attempting to use 20.3 The message that will display when the password does not match the username, this will also display the remaining amount of attempts the user has before the account will be locked, after a successful login attempt is made (storyboard 4) the attempts counter will reset, otherwise, if the attempts counter reaches 0 move to storyboard 21 20.4 Button for user to go back and choose a different username. Storyboard 21 Account has been Locked21.5 21.4 21.3 21.2 21.121.1 Textbox to enter a new username 21.2 Textbox for player to enter a new password 21.3 Message displayed after a users attempt counter has reached 0, or when a player attempts to login to a locked account. This must be reset by an admin user. 21.4 Button for user to go back and try a different username. 21.5 Button to display administrators contact details. 2.1 Screen Design Rationale 2.1.1 Login/Registration Screen The Login Screen was designed with simplicity and elegance in mind, simple and direct that would also allow re-use of the same form as the registration form as well. On top of this, based on the requirements in the project brief, it made sense to start the screen with only the username box available and have the different options come available, such as password entry or registration, after the username had been checked in the database. 2.1.2 Game Lobby The game
lobby was designed to meet the requirement of showing online players with their high scores and to allow players to challenge other players. It also allows a player to join an existing game that doesnt have enough players and a place for administrators to access the administrator screen. 2.1.3 Game Screen The game screen was designed to make it as minimalistic and simple as possible while still getting the idea across with intuitive controls for the user. This led to a change in the initial idea regarding ship placement from a series of combo boxes to a drag and drop system. Battleships is traditionally played on a 10×10 grid so this made it easy to determine the grid size for a 1 on 1 game. For the 2 on 2 games it was decided to make it a 20×10 grid that is open for either player to place their ships on, this was simply a matter of adding another 10×10 grid which seemed like the logical thing to do for 2 players per board. 2.1.4 Administrator Screens The requirements for administrator controls were that an admin could kill running games, add players and update or delete existing players, this inherently means that an administrator is required to have a list of running games and players with buttons for each of these controls, which, is exactly how the main administrator window was designed. Because the creation of players and updating an existing players details require the same information it only seemed appropriate to be able to re-use the same screen for both so this screen was designed with this information in mind. 3. Entity Relationship Diagram 3.1 Entity Relationship Diagram Rationale 3.1.1 Player, Player/Board and Board The player table contains information about the player, including login details and statistics, the board table gives each board a unique identifier. Because many players can play on many boards and many boards can have many players these tables require a join table to be normalized and fit in the relational system hence why there is a player/board join table. 3.1.2 Player, Session and Game Many players can join many games and many games can have many players, therefore, a join table is required between these two tables, the session table contains information about the players statistics during the game and links players to games in a way that means a single player can have many instances of the game running simultaneously. The game table gives each game a unique identifier and determines whether they are still active or not. 3.1.3 Board, Game/Board and Game Many games can have many boards and many boards can be part of many games, so this many to many relationship required a join table that could link different boards to different games. 3.1.4 Board and Board/Tile To keep the database normalised the tiles that make up the boards each need to have their own record in their own table, this is what the board/tile table is for, and it contains a foreign key that links it back to the board table so that one board can have many tiles. 3.1.5 Board Tile, Ship/Tile and Ship Type Because many tiles can contain many ships and many ships can exist on many tiles these two tables needed a join table. The ship table contains all of the information about the types of ships that are in the game and the ship/tile table links them to specific tiles on a board. 4. CRUD Table Entity/AttributeCheck Username in DatabaseRegister New UserCheck Password against UsernameLock AccountSuccessful Login/Lobby LoadsStart New GameJoin Existing GamePlayer MovesGame EndsPlayer Logs OffOpen Admin ConsoleAdmin Kill Running GameAdmin Delete PlayerAdmin Creates New PlayerAdmin Edits Existing Player InfoPlayerRCRRRRURURDRCRUPlayer IDCRRRRDCRUsernameRCRRRDRCRUUser PasswordCRDCRUEmailCDCRULocked UserRCUDCRUAdmin UserCRDCRULogin AttemptsRCRUUDCRUUser OnlineRCUUDCRUHit TotalCUUUDCRUMiss TotalCUUUDCRUHigh ScoreCRRURURUDCRUGameRCRUUURUGame IDRCRRTypeRCRRTurnCRUUGame StatusUURUSessionRCCRURRRDPlayer IDRCCRRDGame IDRCCRRDHitCCRURRRDMissCCRURRRDScoreCCRURRRDBoardCRRUBoard IDCRBoard TypeCRPlayer BoardCCRUDPlayer IDCUUDBoard IDCRDPlayer NumberCUUDGame BoardCRGame IDCRBoard IDCRBoard NumberCRBoard TileCRRUTile IDCRRBoard IDCRRTile RowCRRTile ColumnCRRTile StatusCRUShip TileCRRURRTile IDCRRShip IDCRRRShip HitCRURRShipRRRShip IDRRRShip NameRShip Tile SizeRRR 4.1 CRUD Analysis For the CRUD analysis, the Logical ERD that was created, which contained all the entities and attributes, helped to fill in the left column, then the storyboards and the project requirements were analysed to determine the events that needed analysing, this also helped to confirm that the design and idea was meeting the requirements and led to the creation of storyboards 20 and 21. After the table was created it was time to start filling it in. Check Username in Database This is a simple event that would only need to retrieve certain fields from the player table, the query would look something like: SELECT Username, LockedUser, LoginAttempts, UserOnline FROM tblPlayer WHERE Username = John; Then if no records were found the user would be asked to register, otherwise the player would be checked to see if they were locked or how many login attempts they had remaining or whether that username is already logged in. Register New User This is again a simple event to handle, due to the project design already checking for matching records before registering this event would only require an insert statement that contained the new users information. Check Password Against Username This event has three possible outcomes, 1 the user logs in successfully, 2 the user makes a failed attempt to log in, or 3 the user account gets locked due to repeated failed attempts. Although it is an extremely insecure way of storing a username and password, this event would create a select query which would look for records with a where clause that would require it have the username and password that match what the user entered. (I.E. SELECT Username, UserPassword FROM tblPlayer WHERE Username = [Username] AND UserPassword = [Password]) If a record is found the player will login, however, if no records were found the users login attempts would be retrieved and deducted by 1, once the login attempts reach 0 the users account would be locked. Lock Account When an account is locked the Locked User field will simply be updated to true. Successful Login/Lobby Loads This is a slightly more complex event, when the lobby loads information needs to be retrieved from many locations in the database and displayed on screen, the players login attempts also need to be reset on a successful login along with their account being checked for administration rights. Information to be retrieved will be the players username and high score, this will be displayed to other players in the lobby, information about active games will need to be retrieved to display to the user. Start New Game Starting a new game will primarily create new records in the database. The game table will have a new record to create the game ID, two new records will need to be created in the board table, the board tile table will need to be populated with the board tiles, the ship/tile table will need the records of the ships locations, the players will need to be joined to a game and a board with new records in the Player Board and Session tables, finally the boards will need to be linked to the game via the Game Board table. Join Existing Game Joining an existing game is potentially the most complex event with a mixture of creating and retrieving records and joining them all together. In this event, the player will take command of the previous players ships, this means that the board information will need to be retrieved from the various tables, next the player will need new records, linking them to both the game and the board. Player Moves The event of a player moving will need to update records in multiple areas, the turn field in the game table will need to be changed to the next player, it will need to be recorded if the player hits or misses their opponent and their score will need to be adjusted accordingly. Finally, the information from the ship/tile table and the board records will need to be retrieved to determine whether the move is a hit or miss and they needs to be updated to represent this also. Game Ends When a game ends, the players hit and miss totals would need to be updated, then their high score will be retrieved along with the score from the current game and updated if the new score is higher, finally the number of hits will be calculated to see who the victor is. Player Logs Off When a player logs off their statistics will be update, like when a game ends, however, they will be un-associated with their board which will leave it open for other players to take over. Open Admin Console When an administrator opens the admin console there will be two lists that need to be populated, this will mean that information for players and running games will be retrieved from the database. Admin Kills Running Game When an admin kills a game that is running it will force the game end procedure so it will have the same effect on the database as the Game Ends Event. Admin Deletes Player When an admin deletes a player, it will delete that row from the player table, it will also trigger the ON DELETE CASCADE clause on the player/board and session tables. Admin Creates Player When a new player is created using the admin console the username will first be checked in the database to see if it already exists, if it does an error will occur, if not then the record will be created. Admin Updates Existing Player When an admin updates an existing player all that players information will be retrieved from the database to populate the fields in the window. If the admin tries to change the username the new username will be checked in the database to see if it already exists, if it does then an error will occur, however, if it does not then the record will be updated. 5. SQL Separate file attached. (DAT602_Milestone2_SQL_Corben_Ferris.sql) 5.1 DDL 5.1.1 Player Table The player table will be used to store registration information and statistics for a player, it is essentially a record of their account and their privileges. It consists of the following fields: Player ID The Player ID was implemented as the primary key as it made more sense to use an auto number when it is to be used in other tables as a foreign key, such as the Player Board table and the Session Table. The auto number has been set to start at 1000 to easily differentiate between players, boards and games. Username The usernames are VARCHAR(15) to keep them a reasonable length, mainly for GUI purposes. The Unique constraint was also included as there should not be any players that have the same username. User Password and Email Both fields have the NOT NULL constraint as they are important for registration, log in and unlocking accounts. Locked User, Admin User and User Online These all use the BOOLEAN data type as they are all treated like true/false questions so they will only ever need to hold one of these two values. Login Attempts This is defined as a TINYINT as it will never need to hold a number higher than 5, if a player makes a failed attempt to login the value will decrease by 1, once a player logs in successfully the field will be reset to 5. Hit Total, Miss Total and High Score These are statistical fields; although they could be derived values it was decided to keep them stored in the database for easier access, the high score is kept as part of the requirements and the other statistics are kept for potential extra features in the next iteration 5.1.2 Board Table The board table is used as a parent table that will link to the board tiles and a game. It consists of the following fields: Board ID This is an Auto Number used as the Primary key and to link boards to players and games within the relational database. Board Type This is used to identify if the board is a 1 on 1 board (10×10) or a 2 on 2 board (20×10), it is a TINYINT data type as it will use a value of 1 to identify a 1 on 1 board and a value of 2 for a 2 on 2 board. 5.1.3 Player/Board Table The player/board table is used to link a player record to a board record in the relational database. It will also store the information that shows if a player is the first or second for a 20×10 board, which will determine the colour of their ships. 5.1.4 Game Table The game table is used to store the base information regarding a game, including an identifier, its type and which players turn it is. It contains the following fields: Game ID This is just an auto number field used as the primary key and an identifier. Game Type This is stored as a TINYINT and will use the number 1 to represent a 1 on 1 game and the number 2 to represent a 2 on 2 game. Game Status The game status is used to determine whether a game is still active or not, it is stored as a TINYINT with 1 representing an active session, 2 representing a pending session and 0 representing a completed session. Turn This is used to show which players turn it is. It will hold a Player ID from the Player table. 5.1.5 Game/Board Table The game/board table is used to link a game record to a board record in the relational database. It will also store information as to which board number the board is in relation to the game (either board 1 or board 2) for record keeping and reference checks to ensure there are no more than 2 boards per game. 5.1.6 Session Table The session table is used to link players to games within the relational database. It is designed this way so that there can be many players in one game also so that one player can play multiple games at once. It consists of the following fields: Player ID and Game ID These two fields are both Foreign keys that relate to the Player table and the Game Table, they are also a composite Primary Key for the session table. Hit, Miss and Score These are used for statistics and score calculations during a session, when a game ends the players current score will be checked against their high score and their high score will be updated, or not, accordingly. 5.1.7 Board Tile Table To meet the requirements for the assignment brief, regarding player moves being stored in the database, every location on the grid of each new board will be stored in the database. The board tile table has the following fields: Tile ID The Tile ID is used as the primary key and will be an auto number to give it a unique identifier. Board ID This is a foreign key that links the board tile table to the board table. Tile Row This is a VARCHAR(1), it is used to hold the letters that represent the locations along the y-axis of the game board Tile Column This is a TINYINT as it will only need to hold values between 0 and 19, this depicts the co-ordinate numbers along the x-axis of the game board Tile Status The status field is used to store whether the tile has been attacked or not and the outcome of the attack. 5.1.8 Ship Table The Ship Table is a static table that is simply used to hold information about the ships that are available in the game. It stores the ships name, the ships size and it gives them a unique ID number. 5.1.9 Ship/Tile Table The ship/tile table is an important joining table that is used to store the co-ordinates of ships that are placed on the board by using the tile ID and a ship ID. It will also store whether that part of the ship has been hit or not with a Boolean field called Ship Hit. 5.2 Test Data and Queries Player Table For the player table, there was 11 rows of test data created, with a mixture of privileges, some admin users and many standard users, this is because an admin user is required to promote other users. Some locked users were included for testing purposes later. The queries were designed to replicate some of the more basic functions of the game, including promoting a user to admin, a failed login attempt, locking a user, deleting a us