I have created an ERD for the provided scenario, however I am having some troubl

Question

I have created an ERD for the provided scenario, however I am having some trouble changing it into a Relational Model table, any help would be appreciated. The task is below, along with the ERD I came up with.

Derive a Relational Model from the conceptual schema you provided in Task 1 (it should match exactly).
For the purpose of this assignment, only derive the part of the database that stores the information about
players, quests, beacons and treasures (considering a sub-ERD), and leave aside the information relating
to stores and badges.
1. List all the relations derived from the entity types of the ERD, and their initial attributes.
2. For each relationship type in the sub-ERD justify whether it leads to a new relation, a new attribute
in a relation derived from an entity type, or to a merger of two relations derived from two entity
types.
3. List all the relations in the final relational model, and all their attributes
4. List all the constraints included in the conceptual schema in addition to the relevant primary keys
and foreign keys.

Task 1 answer shown below...

Explanation / Answer

Below is the relationship schema before going to build the data base. Underlineds are primary key.

Entity Sets are :

Players(Username, Name ( FIrstName,LastName),Phone No., DOB,email,...)

Badges(BadgeId, Name, Description)

stores(StoreId , Name, Open Time, Close Time)

Beacons(BeaconId, Story)

Quest(QuestID, Name, BeaconId, TreasureID, MiniquestID)

Treasure(TreasureID, Type,WebPageLink,...)

Relationship Schemas:

Purchase(Username, BadgeID, Purchase Date, Purchase Time, Cost)

GoOn(Username, QuestId,progress)

Sell(StoreId, BadgeId)

Contain(QuestId, BeaconID) here BeaconID is a foreign Key

LeadTo(QuestID, TreasureID) here TreasureID is a foreign Key

Find(UserName, BeaconID, TreasureID)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.