CSC 350

Database Systems

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.


Spirit Animals Header

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:

Animal Design


Life Stats


  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.




To submit this assignment, do the following:

pg_dump <the database name> > assignment02_ddl.sql