COP-5725—Group-Project

Author

Michael Zeihen, David johansson, Melanie Ortiz

# loading packages 
library(rmarkdown)
library(knitr)

# INTRODUCTION

1 Introduction

This project was developed as part of COP 5725: Database Systems to showcase the complete process of designing and implementing a database, ie, Conceptual design, logical design, and Physical Design. We will develop a high-level viwee of the data and relationships. Translate that into a logicial model suitable for a relational database. Then finally convert the logical model into an actual database using MYSQL.

The focus of our team will be to build a relational database tailored to the operations of a Dungeon and Dragons ecology, encompassing areas like Player and Character records, inventory management, locations, and Bosses. We started by identifying relevant entities and their relationships, followed by crafting an Enhanced Entity-Relationship (EER) diagram that included cardinalities, strong and weak entities, and hierarchical structures like supertypes and subtypes. After applying necessary constraints and normalizing the schema to Third Normal Form (3NF), we implemented the database using MySQL, filled it with sample data, and developed advanced queries to derive valuable insights. The project concluded with a reverse-engineered ER diagram to confirm the alignment between the original design and its actual implementation.

2 PHASE 1

List of Entities

2.1 👥 Player

Field	Type	Description
PlayerID	INT (PK)	Unique player ID
Name	VARCHAR	Player character name
UserName	VARCHAR	Player’s login or nickname
Level	INT	Character level
ClassID	INT (FK)	Links to Class
XP	INT	Experience points
Race	VARCHAR	Elf, Human, etc.
HP	INT	Current HP
Mana	INT	Current mana or spell slots
Alignment	VARCHAR	Lawful Good, Chaotic Evil…

PlayerCharacter (one-to-many):
- PlayerID
- 2.2 characterID

2.3 📌 Character

Field	Type	Description
characterID	INT (PK)	Unique player ID
Name	VARCHAR	Player character name
UserName	VARCHAR	Player’s login or nickname
Level	INT	Character level
charClassID	INT (FK)	Links to Class
XP	INT	Experience points
Race	VARCHAR	Elf, Human, etc.
HP	INT	Current HP
Mana	INT	Current mana or spell slots
Alignment	VARCHAR	Lawful Good, Chaotic Evil…

CharacterClass (one-to-one):
- charClassID
- ClassID

2.4 🧙 Class

Field	Type	Description
ClassID	INT (PK)	Unique class ID
ClassName	VARCHAR	Wizard, Fighter, etc.
HitDie	INT	e.g., 6, 8, 10
PrimaryStat	VARCHAR	e.g., INT for Wizard
Description	TEXT	Class description

2.5 ✨ Spell

Field	Type	Description
SpellID	INT (PK)	Unique spell ID
Name	VARCHAR	Spell name
Level	INT	Spell level (0–9)
School	VARCHAR	Abjuration, Necromancy, etc.
Effect	TEXT	What it does
CastingTime	VARCHAR	Action, Bonus, etc.
Duration	VARCHAR	Instant, 1 hour, etc.

ClassSpell (many-to-many):
- ClassID
- SpellID

2.6 🧟 Monster

Field	Type	Description
MonsterID	INT (PK)	Unique monster ID
Name	VARCHAR	Name of the creature
CR	FLOAT	Challenge Rating
HP	INT	Hit Points
AC	INT	Armor Class
Type	VARCHAR	Beast, Fiend, Dragon, etc.
XP	INT	XP granted on defeat

2.7 👑 Boss

Field	Type	Description
BossID	INT (PK)	Unique boss ID
Name	VARCHAR	Boss name
MonsterID	INT (FK)	Inherits from Monster
PhaseCount	INT	Number of fight phases
Lair	VARCHAR	Location name

bossMonsters (many-to-many):
- MonsterID
- 2.8 MonsterID

2.9 🗡️ Equipment

Field	Type	Description
EquipmentID	INT (PK)	Unique ID
Name	VARCHAR	Item name
Type	VARCHAR	Weapon, Armor, Ring, etc.
Bonus	VARCHAR	“+1 STR”, “AC +2”, etc.
Rarity	VARCHAR	Common, Rare, Legendary…
Description	TEXT	Special effects, flavor text

CharacterEquipment (many-to-many):
- characterID
- EquipmentID

2.10 👥 NPC

Field	Type	Description
NPCID	INT (PK)	Unique NPC ID
Name	VARCHAR	Character name
Role	VARCHAR	Vendor, Questgiver, etc.
Faction	VARCHAR	Affiliation or group
Location	VARCHAR	Where they can be found

2.11 🎭 Event

Field	Type	Description
EventID	INT (PK)	Unique event ID
Name	VARCHAR	Event name
Date	DATE	In-game or real date
Description	TEXT	Summary of what occurred

characterEvent (many-to-many): - characterID - EventID

2.12 🏆 Achievement

Field	Type	Description
AchievementID	INT (PK)	Unique ID
Name	VARCHAR	“Dragon Slayer”, etc.
Description	TEXT	How it was earned
XPReward	INT	Optional XP bonus

PlayerAchievement (many-to-many): - PlayerID - AchievementID - DateEarned

others
- Sales
- Trades
- potions
- weapons
- Armor
- PVP
- locations
possible quires
- find what bosses character killed and get list of items from that boss he traded to another character
- find what event(quest) gets the most exp with a sword or wand as reward that can only be used my mages and paladins

3 Phase 2

erDiagram User{ int ID PK varchar FirstName varchar LastName varchar Email } Character{ int ID PK int userID FK varchar Name int Level int classID FK int XP int raceACE FK int HP int mana int allignmentID FK } Allignment{ int ID PK varchar(50) Name
} Class{ int ID PK varchar(50) Name int HitDie text Description } Spell{ int ID varchar Name int Level text Effect int CastingTime int Duration }

```{mermaid}
erDiagram
    Monsters{
        varchar ID PK
        varchar Name
        float CR
        int HP
        int AC
        varchar createType
        int XP
    }
    Bosses{
        int ID PK
        varchar Name
        int monsterID FK
        varchar Lair
    }
    NPC{
        int ID PK
        varchar Name
        varchar Role
        varchar Faction
        varchar Location
    }
    Equipment{
        int ID PK
        varchar Name
        varchar type
        varchar bonus
        varchar rarity
        text    Description
    }

erDiagram
    Events{
        int ID PK
        varchar Name
        varchar Description
    }
    Achievments{
        int ID PK
        varchar tName
        text Description
        int  xpReward
    }

4 PHASE 3

Entity Specification (Structured English)

Describe one key entity clearly using structured language.
Include its attributes, roles, and how it interacts within the system.

Relationships & Structure Analysis

List all relationships along with their cardinalities (e.g., one-to-many, many-to-many) and participation ratios (total vs. partial participation).
Identify and explain:
- Strong vs. weak entities
- Supertypes and subtypes with their distinguishing features.

Table & Column Constraints
- Define all constraints such as:
  - Primary and foreign keys
  - NOT NULL requirements
  - Unique constraints
  - Referential integrity rules
Normalization Goals
- Restructure tables and attributes to achieve Third Normal Form (3NF):
- Eliminate redundancy
- Ensure dependencies are logical and efficient
- Avoid update anomalies

5 PHASE 4

Build the Database in MySQL

Translate your EER diagram into actual database structure using SQL.

Create Tables Based on Your Design

Map each entity and relationship to proper tables.
Document the schema details in your report.

Insert Sample Data

Populate each table with at least five records.
Include a snapshot of the data entries in your submission.

Write & Execute Complex Queries

Formulate and run at least five meaningful SQL queries that involve 3–4 table joins.
Ensure all tables are actively used—none should be redundant.
For each query:

Describe the goal in plain English.

Show the SQL code.

Present the query results.

Show Reverse-Engineered Diagram

Use reverse engineering to visualize the implemented schema.

-Add the generated ER diagram to your report to confirm alignment between design and implementation.