Database Modelling & Design Your task for this assignment is to design a database. This assignment requires you to apply your knowledge of conceptual database design, logical database design, and...

Database Modelling & Design

Your task for this assignment is to design a database.

This assignment requires you to apply your knowledge of conceptual database design, logical database design, and table implementation in the SQL Server.

Objective: apply your database development skills to a real world application to get a database for the application.

Requirements Definition of the Application

Welcome Adventurer, this extremely well thought out document contains all the pieces of the puzzle you will need to create a relational database for a new online adventure game. The game is massive in size, so your task will be scaled down in content. You are tasked with creating a functional database for a few main features of the game. These are the core components that will objects in the game to interact with each other. I hope you aren’t feeling overwhelmed yet because the fun hasn’t even started.

Creatures: In our game there are two main types of creatures: player characters and non-playable characters (NPCs). Each of these has a race, gender, and a combat level. What makes each unique are a few attributes, players have usernames, quest points, prayer points and an energy level. Because the game will be free to play, there are different account types that a player character can have, some being a paid subscription while others are free loaders (playing our game for free, costing us money – disgusting). NPCs however only have attack and defence statistics as well as a way of finding out whether they are attackable characters or not. This will allow our game engine to determine whether a player can kill them or not. NPCs can drop items when they die, see items below for more details on them, these drops have a pre-set percentage and quantity of that item dropped.

Items: No adventure game would be complete without some form of accessories that a player can use. These will be known in our game as items. All items have similar attributes however, some can be worn as an item of clothing, which have a certain armour slot they can be placed into. Items do share similarities though, regardless of what slot they can go into, they have a selling price, weight and whether they can be stacked in a single item slot or not. What an item slot you may ask? Please refer to the Inventory and Banking section below. When it comes to putting items into slots however, they are only able to equip 11 onto their character. Why may you ask? Well, we don’t have two sets of hands, do we? Oh, the player characters are indeed human. Items in a single slot can also be stackable, meaning that while a single sword item may take up a single slot, coins will continue stacking up in a single slot.

Skills: The game world is plastered with interactable objects that help a player level up their skills. As an example, there are trees that players can cut down to get experience points in that skill and eventually level up that skill – this skill is known as woodcutting. You will not be capturing these interactable gaming world objects in this database. However, you should have a way of recording that there are 23 usable skills and each player has a certain level and experience points for each of those skills. Skills also play one more crucial role in our game; they place level requirements onto items. This means that some items may not be able to be used by a player unless they have a required skill level. The player would still be able to have these items in their bank or inventory regardless of the skill level requirement.

Inventory and Banking: A player can carry a number of items on their character, up to 28 to be specific. These 28 spaces are what we are going to be calling slots. Are these slots relevant to our database? No. We simply care up to 28 items can be held in a player’s inventory at any given moment in time. Player’s may want to store their items away for later use on their adventures, not always wanting to hold them on their character. That’s why each character is going to be permitted a single bank account with a maximum of 816 slots.

Quests: Quests allow us to tell the story of our game to the player characters, they can range from collecting the ingredients of a cake for a master baker, to slaying an almighty dragon that has been terrorising individuals. To keep things clear, I am going to list specifics as to what you must include in your design. A quest must have a name and a quest point requirement as well as a way note that a player has completed that quest. Quests have NPCs attached to them, such as this master baker or dragon. You do not need to include items that are related to a quest, however doing so will make this component more complete – plus a bonus mark for if you mess up anywhere else in the assignment!

Design Requirements

In order to achieve the desired level of flexibility in data collection, the design will be expected to accommodate the following features:

1. Draw a traditional UML diagram i.e. ER diagram for the application to show entity classes and relationships, indicate CKs and multiplicities whenever appropriate. This diagram is critical and if it does not capture all the requirements and constraints of the application, the final database designed will not be complete.


2. Convert the UML Class Diagram i.e. ER diagram into a relational database schema; primary keys and foreign keys have to be clearly identified.


3. Create these tables in the SQL Server. Make sure that the constraints obtained from Step (2) are all implemented.

Submission Requirements

To complete the assignment, you must submit a report (i.e. doc/docx file) including each of the followings:

1. 
   

   Atraditional UML Class diagramorER Diagram using UML standards(no Foreign Keys in entity classes. Use association classes, weak entities, inheritance etc where appropriate). Each entity class should contain a list of the applicable attributes that meet the design requirements. The diagram should highlight ALLassociationsbetween entity classes including theirmultiplicitiesand include appropriate roles/names to describe the purpose of the association. Specifically

   
   
   
   
   1. The UML diagram should be drawn up in UMLet or some other UML design tool.
   
   
   2. Multiplicitiesmust be included.
   
   
   3. AnyCandidate Keysshould be depicted using (CK) etc.
   
   
   4. Save the UML as an image to be included in your final word document (make sure it is still readable!)
   
   
   5. You need to provide anywritten assumptionsthat may justify why you chose certain attributes/association types over another. These should be short descriptive dot points. No need for an eassy.
   
   
   6. It should adhere to all the design rules taught in this course.
   
   
   
   


2. 
   

   ARelational Diagramdrawn using UMLet. Save the relational diagram as an image to be included in your final word document and make sure it is still readable.

   
   

   Alternatively, aRelational Modelwritten in text. Each table schema in the logical design is put in a separate line and the primary key (PK), alternative key (AK), and the foreign key (FK) specifications of the table must follow the table schema immediately, be indented and be presented in different lines. The logical design in text should be written in your report using the following format:

   
   

   Table1(attr11, attr12, attr13)      PK = (attr11)      AK = (attr12)      FK = (attr13) ~> Table2(attr21) Table2(attr21, attr22)      PK = (attr21)

   
   

   If your logical design does not agree with your UML design, for example, an attribute is in the logical design but not in the UML diagram, heavy deduction up to 50% will apply unless otherwise you justify.

   
   


3. 
   

   Table Creation statementsfor your design. The table creation statements should be tested in SQL Server Management Studio. No marks will be awarded if syntax errors are identified. At the same time, the statements must be based on the tables of your own logical design. In other words, the creation of tables that are not in your logical design will get no marks. The table creation statements should have:

   
   
   
   
   1. AnyPrimary KeysandForeign Keyswith appropriate and consistent naming,
   
   
   2. Appropriatedata types, and
   
   
   3. Appropriateconstraints(if there is any).
   
   
   
   

Marking Schemes

The assignment will be marked based on whether the requirements and the constraints of the application are fulfilled. The total marks for the assignment are 25 and are distributed in the components as the following.