CSC 350

Lab 13 - Database Design and Mockup

In this assignment you'll design a complete database system. Creating the schema, populating the database with mock data, providing queries for common use cases, analyze database perforam using query plans, and tune performance using indicies and materialized views.

Scenario

You're creating an MMORPG (Massively Multiplayer Online Role-Playing Game) set in the Spirit Animals universe created by Brandon Mull.

The game producers have outlined the following requirements that the database system must support:

• The ability for new customers to sign up for and manage their subscription
• The ability to automate billing
• The ability to suspend accounts for non-payment
• The ability to track total revenue during a given time period as well as revenue changes over time.
• The ability for customers to create multiple Spirit Animal avatars from a list of options, and to customize that avatar's appearance, name, and powers.

• The ability for each customer to switch between avatars during different game sessions.
• The ability to track which avatar types are used most often
• The ability for individual avatars to have stats, including level, health, mana, and xp.

• Healtha™️ and mana stats should have a maximum value determine by a combination of avatar type and level.
• Each level can be reached by a specific amount of xp. Those xp thresholds should be the same for every avatar type.

Assignment

1. Design a database schema that would support each of those scenarios. Make sure that your tables are all in 3NF. Spend adequate time on this part so you're not blindly trying to complete the subsequent parts. Remember, every minute of good design saves hours of bad coding.

2. Once you have the design nailed down, use Mockaroo to populate your database with test data. In order to adequately profile your database, your database should contain at least 5,000 customers, with an appropriate number of supporting records in other tables.

3. Make sure your tables have the correct primary and foreign key relationships.

4. Write the queries needed for the application layer to be able to achieve all of the specified cases. This will include various INSERT queries for creating new records, UPDATE queries for changing records, and SELECT queries for accessing records.

5. Profile your queries using EXPLAIN and look for places where performance could be increased by the use of indexes. Apply those indexes to your tables.

Hints

• Mockaroo can create tables for you, but it won't get the primary keys or data types correct. I suggest you create the structure yourself, then use Mockaroo to generate insert statements for your test data.

• You can use the instructions in Assignment 2 to have postgres import those SQL statements.

• Once you think you're done, go back through each of the requirements above and make sure that you can identify one or more queries that would be used to fulfill that requirement.

Grading

• Fulfilled all requirements (business requirements met, tables in 3NF, primary and foreign keys configured correctly, query performance tuned using indexes): 100%
• Met all business requirements, but database design is subpar: 70%
• Fulfilled some of the requirements: 50%
• Fulfilled none of the requirements: 0%:

Submission

To submit this assignment, do the following:

• Create a copy of your database using the pg_dump command:

pg_dump <the database name> > assignment02_ddl.sql

• Put this file and your sql file from step 3 into a zip file, and upload that zip file to mySVU.