Project Part AThe Task for Part A You are to design a solution for a database management system that addresses the needs of an online augmented reality game. In this part you will design a database by: 1. Creating an ER model for the database based on a use case; 2. Creating a relational schema for the database; and, 3. Validating the relational schema for the database using normalisation. Background You have been hired by the Brisbane based company Aggity Games. Aggity are passionate about bringingaugmented reality games to the masses. After some success with their previous game, Bush Rangers, theyare keen to get you on board to design the database for their new game, which is called Treasure Hunters! In Treasure Hunters players will travel to different locations in the real world in the hunt for treasure. Theirsmartphones will act as a gateway into the treasure hunter world, with real objects taking on different roleswithin the game, such as beacons that unlock quests or the elusive treasure. It is nowyour task to design the database. The Scenario – Treasure Hunters, an augmented reality online game. In order to play the game, each player creates their account. They must choose a unique username, provide their name (first and last names), gender (female, male, other or prefer not to disclose), date of birth, email and postal address (street number, street name, suburb, city and postcode) and up to three phone numbers. The date and time of account creation and the player’s total points will also be associated with their account. The game revolves around players embarking on quests and hunting for treasure. Multiple players can go on the same quest and find the same treasure. There are hundreds of quests, new quests are added every week and some of the more advanced quests can contain up to five mini quests. Each quest needs to be identifiable using a unique id, and they also have a name. Players initiate quests by finding objects in the real world, which act as beacons, and each quest is assigned a beacon. Once the player finds a beacon they will be presented with a story, which provides some background on the quest, and a list of the treasurethey must hunt down in order to finish the quest. The database needs to keep track of each player’sprogress (active, complete, inactive) on each quest. There are different types of treasure (common, uncommon, rare, ultra-rare and elite). The players are directed to a webpage where they can obtain various clues to find the treasure. Each treasure has one webpage. Whena player finds the treasure, they are awarded points. Each piece of treasure rewards a different amount ofpoints. When a player unlocks the list of treasures for a quest, they can see a description for each piece. Players can use their points to purchase badges from virtual stores. All stores have a name, and they are only open at certain times of the day, so their opening and closing times need to be recorded. In order to purchase badges from the stores, players can use their points. There are a lot of different badges that players can purchase. To help players decide what to purchase they will be able to read the badge names and descriptions. The price of the badges varies depending on demand, and is re-calculated when a player reaches a store. This means that the price itself is not stored in the database. However, when a player purchases a badge from a store, the store name, date and time of purchase and the cost of the purchase need to be recorded, so that they can see a detailed list of their purchases at any time. Part A Tasks Part A requires you to complete a number of sequential tasks to fulfill the requirements of the scenario. In this part you will design a database by: 1. Creating an ER model for the database; 2. Deriving the relational schema for the new database; 3. Demonstrating the constraints of the relational schema through examples; and 4. Verifying a previous (different) relational schema for the database using normalization; Task 1 Considering the use case provided, create an Entity Relationship Diagram that correctly models the data requirements of Treasure Hunters. You must ensure that everything that happens in the game (or in relation to the game) is recorded. Your ER diagram (using UML notation) needs to show: the entity types, the binary relationship types, the complex relationship type, the multiplicity of relationship types, the attributes (and if applicable their domains) of entity types, the attributes (and if applicable their domains) of relationship types, and the primary key of each entity type. State any assumptions you make. Task 2 Derive a Relational Model from the conceptual schema you provided in Task 1 (it should match exactly). For the purpose of this assignment, only derive the part of the database that stores the information about players, quests, beacons and treasures (considering a sub-ERD), and leave aside the information relating to stores and badges.
- List all the relations derived from the entity types of the ERD, and their initial attributes. For each relationship type in the sub-ERD justify whether it leads to a new relation, a new attribute in a relation derived from an entity type, or to a merger of two relations derived from two entity types. 3. List all the relations in the final relational model, and all their attributes 4. List all the constraints included in the conceptual schema in addition to the relevant primary keys and foreign keys.
|Amy||Q33 Q12 Q25||674||2|
|Diamond||The tree’s diamonds||Q25||Urban Elves||Toohey Forest Sign|
|Heart||The Big Gum’s heart||Q25||Urban Elves||Toohey Forest Sign|
|Diamond||The river’s diamonds||Q33||Pirates of Brisbane River||City Cat (Broncos)|
|Gold||The Captain’s chest||Q33||Pirates of Brisbane River||City Cat (Broncos)|
|Id||Street number||Street name||city||postcode|
- Creating the database
- Successfully creating new tables
- Including all attributes
- Including constraints
- Correctly creating Primary Keys
- Correctly creating Foreign Keys
- Book(publisherCode) is dependent on Publisher (publisherCode) • Wrote (ISBN) is dependent on Book (ISBN) • Wrote (authorID) is dependent on Author (authorID) • Inventory (ISBN) is dependent on Book (ISBN) • Inventory (branchNumber) is dependent on Branch (branchNumber)
- The domain of Publisher(state) is [QLD, VIC, NSW, WA, TAS, NT, SA]. • The domain of Book(genre) is [Non-Fiction, Science Fiction, Fantasy, Crime, Mystery, Young Adult, Romance, General Fiction] • ISBN must be a 13-digit number and may begin with a zero. • The publisher name and book title are both mandatory. • Paperback must be either True or False. • The default quantity in stock is 0.
- PhoneNumber (username) is dependent on Player(username) • Quest (advancedQuestID) is dependent on Quest(questID) • Treasure (questID) is dependent on Quest (questID) • PlayerProgress (questID) is dependent on Quest (questID) • PlayerProgress (username) is dependent on Player (username) • PlayerTreasure (username) is dependent on Player (username) • PlayerTreasure (treasureID) is dependent on Treasure (treasureID) • Purchase (storeID) is dependent on Store (storeID) • Purchase (username) is dependent on Player (username) • Purchase (badgeID) is dependent on Badge (badgeID)