Database Design & SQL

misterdeedsInformation Systems, View All

Database Design & SQL

During my time at Cal Poly Pomona, i was a part of a database design project that involved both designing a database from scratch, and then implementing it into a Microsoft SQL Server. Our group chose to do a database about Pokemon battles. This database is able to track each Pokemon trainer and their battle record. It also tracks the Pokemon they own and the moves their Pokemon can perform.

Check out the database below:

  • ER Diagram
    This Entity Relationship (ER) diagram explains the relationship between data elements, based on the business rules. It not only optimizes storage and querying, but ensures data integrity among dependent entities. Using this diagram as an example, a Trainer has exactly one Battle Record and vice versa. Whenever a new Trainer is entered, one Battle Record must also be created for that trainer
  • Relational Schema
    This Relational Schema diagram, which explains what data elements every entity needs, as well as which primary or foreign keys are present in other entities. This is important for maintaining data integrity, and ensuring each entry for an entity has all the required information. In this example, every battle needs two Trainer IDs and one Stadium ID, which are all primary keys taken from other entities.
  • Microsoft SQL Server Diagram
    This diagram shows the database implemented in Microsoft's SQL Server. Each entity is shown with all relevant attributes and primary/foreign keys. This diagram highlights the important relationships between entities and how foreign keys are exchanged between them . Using this example, the Pokemon entity lists which trainer owns them using the primary key "Trainer ID" from the Trainer entity.
  • SQL Queries
    This is one example of the SQL queries we ran on the database to prove our understanding of SQL and to answer how any trainers owned female Pokemon. Our query was designed so that for every Trainer with at least 1 female Pokemon, it would return a count of how many female Pokemon they owned, along with their name, by matching the Trainer ID in both the Trainer and Pokemon tables.
Click below to download our report
Download Report
Click below to download our powerpoint presentation
Download presentation
Click below to download our data spreadsheet
Download Data