Full Stack Application Development and Software Workshop 2 - Assignment 3: Fantasy base building game

Assignment 3 Full Stack Application Development and Software Workshop 2 Pieter Joubert April 19, 2023 Contents 1 Introduction 1 1.1 Case Study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.2 Initial ERD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 2 Tasks 3 2.1 Task 1 - Setup the database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2.2 Task 2 - Update existing entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2.3 Task 3 - Add new Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2.4 Task 4 - Populate test-data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2.5 Task 4 - Create Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.6 Task 5 - Create Code Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 3 Tests 4 3.1 Test 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 3.1.1 Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 3.1.2 Expected Result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 3.2 Test 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.2.1 Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.2.2 Expected Result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.3 Test 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.3.1 Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.3.2 Expected Result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.4 Test 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.4.1 Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.4.2 Expected Result . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 4 Quiz Instructions 5 1 Introduction Warning! Please note that to receive any marks for this Assignment you will need to complete the Quiz on Canvas. Just completing the Tasks will result in ZERO marks being awarded. Also make sure that your database can pass all the tests given at the end of the Assignment before you start the Quiz. 1 1.1 Case Study For this Assignment you will be designing and developing a Database for a fantasy base building game. One example of such a game is Townsmen. For this Assignment you do not need to worry about implementing the game at all, we are just focusing on the database. The game is multiplayer with multiple players . Each player has a base, in which they can construct various buildings . These buildings produce resources which can be used to construct other buildings or can be sold for currency . As this is designed to be an online game the construction of a building can take several hours or days in real time. You will be given an initial ERD (Entity Relationship Diagram) as well as a .ziple containing test data stored as .csv (comma-separated format) les. From this information, and following the Tasks below, you will expand the database design, populate the database with test data, test the data, and answer questions in an online quiz for your nal mark. 1.2 Initial ERD The ERD in Figure 1 is an initial ERD, that includes some of the entities required in the database. This ERD also excludes any information regarding the elds of these entities. Figure 1: Entity-relationship diagram 2 2 Tasks Warning! Make sure that each step is completed correctly before moving to the next step. If necessary DROP or TRUNCATE entities with incorrect data before xing your code and carrying on with the Assignment. 2.1 Task 1 - Setup the database CourseNana.COM

1. Download the test-data.zip from Canvas and extract the csvles in it.
2. Create a new le named assignment3.sql . In this le you will save all the sql code that you write.
3. Create a new postgres user named fsad. (Note: If you are using the lab machines, the username and database that has already been created for you is ne to use).
4. Create a new database named assignment3
5. Make sure the fsad user has full privileges on the assignment3 database. 2.2 Task 2 - Update existing entities
6. For each of the tables listed in Figure 1, create a corresponding table in your database. Use the csv les to guide you.
7. NOTE that the csv les includes a header row, which should NOT be inserted into the database.
8. Ensure that you match the datatype exactly to the test-data. HINT: The premium column in the Currency table should be a bool datatype, and the build time column BuildingCost table should be an interval datatype.
9. Create any required Primary Key Constraints.
10. Create any required Foreign Key constraints. 2.3 Task 3 - Add new Entities
11. Update your database to add any missing entities based on the csv les.
12. NOTE that the csv les includes a header row, which should NOT be inserted into the database.
13. Ensure that you match the datatype exactly to the test-data.
14. Create any required Primary Key Constraints.
15. Create any required Foreign Key constraints. 2.4 Task 4 - Populate test-data
16. For each table import the corresponding values from the csv le.
17. NOTE that the csv les includes a header row, which should NOT be inserted into the database.
18. Ensure that all the tables contain the correct data. 3 2.5 Task 4 - Create Views
19. Create a view for each building named that displays the following columns: Name, Description, Resources Generated, Required Gold, Build Time.
20. Create a view for each Player named buildings that displays all the buildings (by name) the player has built in their base.
21. Create a view for each Player named resources that displays the following columns: Name, Food Amount, Wood Amount, Stone Amount.
22. Create a view for each Player named currencies that displays the following columns: Name, Gold Amount, Ethereal Silver Amount, Diamonds Amount. 2.6 Task 5 - Create Code Block Warning! Before you create and execute the code block make a backup of your database. Once the code block has been tested and is working RESTORE the database from your backup. Create a code (DO) block that essentially fast-forwards any outstanding constructions by doing the following:
23. Declares a variable name testdate.
24. Assigns this variable to the 10th of April 2023.
25. Checks each row in the construction queue table.
26. If the construction has NOT been completed (based on the start date plus build time being after the test date) it should be removed from the construction queue table and inserted into the base building table.
27. Once the DO block is working correctly restore your database back to its state before running the DO block. 3 Tests The following tests should give you basic idea if you have set up the database correctly. Passing all the tests DOES NOT mean that your database is setup correctly, but failing ANY of the tests means your database is setup INCORRECTLY. 3.1 Test 1 3.1.1 Query 01 | SELECT FROM player ORDER BY player_name ASC LIMIT 1; 3.1.2 Expected Result 01 | player_id | player_name | player_password 02 | -- ---------+-------------+----------------- 03 | 6 | AishaKhan | password6 04 | (1 row ) 4 3.2 Test 2 3.2.1 Query 01 | SELECT COUNT () FROM constructionqueue ; 3.2.2 Expected Result 01 | count 02 | -- ----- 03 | 4 04 | (1 row ) 3.3 Test 3 3.3.1 Query 01 | SELECT building_id , build_time FROM buildingcost ORDER BY build_time DESC LIMIT 4 ; 3.3.2 Expected Result 01 | building_id | build_time 02 | -- -----------+------------ 03 | 5 | 14 days 04 | 4 | 7 days 05 | 3 | 5 days 06 | 1 | 3 days 07 | (4 rows ) 3.4 Test 4 3.4.1 Query 01 | SELECT * from currency WHERE premium = true ; 3.4.2 Expected Result 01 | currency_id | currency_name | premium 02 | -- -----------+---------------+--------- 03 | 3 | Diamonds | t 04 | (1 row ) 4 Quiz Instructions Before attempting the quiz make sure you have completed the following:
28. Most of the required content for assignment 3 has been provided in Weeks 6, 7 and 8 on Canvas. You might need to do some of your own research using the links provided on Canvas to complete some of the Tasks. 5
29. Complete all the Tasks listed above. This should take you approximately six (6) hours.
30. VERY IMPORTANT: Do not run Task 5 (the DO block) until you get asked to do so in the quiz.
31. Run all the tests listed above, and ensure they pass.
32. Run your own tests and make sure they pass. Make sure you do not change the structure or dta of the database when doing so.
33. Ensure you have a backup of the database you can quickly run if you break your database during the quiz.
34. Ensure you understand the structure of the database.
35. Make sure you attempt the quiz on a device that has postgres installed and working properly. Also ensure that the device has the Assignment3 database already loaded.
36. You will need to write a number of queries on the database to answer the quiz. Make sure you can easily run any required queries.
37. You will have a limited amount of time for the quiz (60 minutes) so make sure you understand how to write psql, sql and plpgsql commands before you start the quiz.
38. Please note that NO late submissions will be accepted. 6