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...

Answer 1

The derived Relational Model is as follows:

1.

The relations derived from the entity types of the ERD, and their initial attributes are as follows:

• Player(loginName,Sex,Address)
• Badge(badge_name,description,condition)
• Quests(number,text)

2.

The explanation of the relation is as follows:

• With the help of the join_date as a foreign key, the Player makes its relationship with the Team in the relational model.
• With the attribute name, Quests makes their relationship with WayPoint and have has as a relation.

3.

The relations in the final relational model, and all their attributes are as follows:

• Made by(login_name,badge_name,review_id)
• Member of(login_name,creation_time,join_date)
• Paticipates in(title,team_name,creation_date)

4.

The constraints included in the conceptual scheme relevant to primary keys are as follows:

• login_name
• title
• team_name
• review_id
• number

The constraints included in the conceptual scheme relevant to foreign keys are as follows:

• join_date, its depends upon date
• start_time, its depends upon creation_time.