Computer Science Homework Help

A new company has developed a breakthrough Massive Multiplayer Online Role Playing Game (MMORPG). An MMORPG is a computer game that is played over the Internet, connecting to a server where other players from around the world are also playing the same game. It is possible to ’see’ and communicate with these other players when online.

This company has recruited you to be their database designer. The database will manage the accounts of all players registered for this online game.

• Each account must have a unique name. This will be the name that players use to log on to the game server.
• Players need to pay a fee every month for having an account and, when their time runs out, the account must be blocked. It must only be unblocked when they have purchased more time.
• Each account has a number of characters that have been created by the user. All characters have names and belong to a team and also have a skill level.
• A table in the database logs any errors that occur. Make sure that certain errors have a certain type so that they are easier to find.
• When playing the game with one of characters within the account, a player may find and pick up items.

These items are then stored in the character’s inventory. No item is unique unfortunately, so other characters may have the same item. The inventory has eight open slots for different types of items to go into. Items that are the same can stack on the same slot; therefore, a record must be kept of the quantity.

– Create an ER Diagram for the scenario above

Create a database.

Create the tables for the scenario above. (Ensure to use constraints such as CHECK and other validation constraints in your tables to avoid losing marks.)

Insert sample data into the tables you created above.

Create the following views:

• vwBlockedAccounts – All the accounts that are currently blocked.
• vwTopSkill – The top 20 characters with the highest skill levels and their account details.
• vwTopStackedItems – A view of the 20 items that are stacked most frequently. Include the characters whose inventory they are stacked in.

vwPopItems – The five most popular items that are contained inside the character’s inventory, including the number of characters that have the item.

Create the following stored procedures:

• spRegister – Registers a new account. All the account details must be the arguments. Make sure that the name does not already exist in the table.
• spAddTime – Adds the specified time to the specified account. Makes sure the account exists beforehand. Arguments: Account name, Length of time in days.
• spAddItem – Adds an item to a character’s inventory. Make sure there is space before adding the item.
• spAddChar – Adds a specified character to a specified account.
• spSendLetter – Prints out a letter for each account, indicating how much time they have left. An optional argument that can be used to send latest news as well must also be included.
• Ensure to use constraints such as CHECK and other validation constraints in your tables to avoid losing marks.

Create 2 (two) after triggers on the tables you have created.

I forgot to add this but it’s just extra details not really extra work:Complete the following:Use the given initial ER diagramCreate a script file that includes the code for creating the database and all of the tables required in the project. PRIMARY KEY, FOREIGN KEY, UNIQUE and CHECK constraints must be included. Make use of the IDENTITY property, DEFAULT values and constraints. Remember that master/parent tables must be created first.Create a script file to insert data into your user-created tables. Add at least four records for each table without foreign keys and seven records for each table that does contain a foreign key.Create a script file to create the views as specified.Create a script file to create the stored procedures as specified.Create a script file to create at least two triggers for your project. There are no specifications for what types of triggers you must create. You should apply your knowledge and create triggers that you think would be appropriate.Create a script file to delete the database that you create.Create appropriate indexes on your tables.You should validate all applicable arguments in the stored procedures where arguments are received.Make use of iteration where appropriate.Using the GUI (Graphical User Interface), create a full backup of your database and send it to a zipped folder.Each script file must have a header section (using comments) that contains the following information:Script file nameProgrammer name (Clara Hammann)DateA short description of what the script file doesWhen commenting your code, both styles of commenting (– or /* */) may be used. Just remember to maintain consistency throughout your project.Save all of the script files together with the backup onto a disk and hand it in with printouts of the script files. Provide clear instructions in your user documentation on how to execute the views and stored procedures, including the arguments that need to be passed to the stored procedures. In your user documentation you should include author and date details, together with the purpose and description of the project

Create Indexes on the tables you created.

Create a zipped backup of your database and hand it in with your script files. 

NB: I have completed some of it but need it to be corrected and fully completed. It is a zipped file.