Question

IS 331 NJIT Assignment 5

Steps to complete in Microsoft Access

Use the sample solution to assignment 4 (below) as the conceptual model, develop a logical model (relational model) based on it. Specify all the integrity constraints in English.

-Acquires- Character Gamer PK character name Item PK username PK item name level -Creates- O-Owns--O password first name experience value health points status last_name mana points strength intelligence type email Skill dex skill id PK class name PvP Match MP cost level required description MatchID PK Winner Name Loser Name Prerequisite Kill Time Kill Score type Belongs to Guild Armor Accessory Weapon guild name PK time founded defense type speed status armor type special effects damage per second ASSUMPTIONS: A gamer has to create at least one character to be stored in the database Item name is unique for each item Characters do not always have to be carrying an item There could be items that do not belong to any character Characters do not always have skills A character does not have to be in a guild A character can be a member of many guilds

For each table in the Assignment 4 solution, write a relation in the form TableName(PK attribute, attribute 1, attribute 2, etc)

For each relationship connection between the tables, write a constraint in the form CONSTRAINT foreign_key REFERENCES reference_table_name

For any N:M relationships, create an associative or junction table that copy the primary keys from each of the two table and has constraints for each of the Primary Keys to be also Foreign Keys. Wikipedia actually has a good description for this at https://en.wikipedia.org/wiki/Associative_entity

Here's the first few tables from the assignment to help you get a good start:

GAMER (USERNAME, PASSWORD, FIRSTNAME, LASTNAME, EMAIL)

CHARACTER (CHARACTER_NAME, LEVEL, EXP, HP, MP, STRENGTH, INTELLIGENCE, DEXTERITY, CLASS, USERNAME)

FOREIGN KEY USERNAME REFERENCES GAMER

GUILD (GUILD_NAME, FOUNDING_TIME, STATUS)

CHARACTER_GUILD (GUILD_NAME, CHARACTER_NAME)

FOREIGN KEY CHARACTER_NAME REFERENCES CHARACTER

FOREIGN KEY GUILD_NAME REFERENCES GUILD

Answer 1

Two examples have been provided by you, here are the rest of the relationship and entities that have primary key.

CHARACTER(character_name, level, experience, health points, mana points, strength, intelligence, dex, class)
ITEM(item_name, value, status, type)
CHARACTER_ITEM(character_name, item_name)
FOREIGN KEY character_name REFERENCES character
FOREIGN KEY item_name REFERENCES item

CHARACTER(character_name, level, experience, health points, mana points, strength, intelligence, dex, class)
SKILL(skill_id, name, mp_cost, level_required, description)
CHARACTER_SKILL(character_name, skill_id)
FOREIGN KEY character_name REFERENCES character
FOREIGN KEY skill_id REFERENCES skill

PvP_Match(MatchId, winner_name, loser_name, kill_time, kill_score)