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. (1 mark)

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. (1 marks)

3. List all the relations in the final relational model, and all their attributes (1 mark)

4. List all the constraints included in the conceptual schema in addition to the relevant primary keys and foreign keys. (2 marks)

ANSWER NEEDS TO BE IN THIS FORMAT

Deriving Relationship Types

RELATIONSHIP NAME was derived by adding RELATION with attribute as foreign keys to link with RELATION and RELATION because of MULTIPLICITY and/or PARENT/CHILD

RELATIONSHIP NAME was derived by merging RELATION and RELATION because of MULTIPLICITY and/or PARENT/CHILD

RELATIONSHIP NAME was derived by adding attribute to RELATION as a foreign key to link with RELATION because of MULTIPLICITY and/or PARENT/CHILD

Relations in the Final Relational Model

RELATION (attribute, attribute, attribute)

RELATION (attribute, attribute, attribute)

RELATION (attribute, attribute, attribute)

RELATION (attribute, attribute, attribute)

RELATION (attribute, attribute, attribute)

Constraints of the Final Relational Model

Primary Keys

The primary keys are the attributes underlined in relations listed above 3.

Foreign Keys

Relation (attribute) is dependent on Relation (attribute).

Relation (attribute) is dependent on Relation (attribute).

Other Constraints

The X attribute of the Y relation can only be from 1000 to 9999.

Player userlD (PK) Name Badges Beacon 1..n badgelD (PK) Decription .n lastName Gender DOB phoneNo. [O..3] Address Finds beaconlD (PK) Story 1..1 0..n 1..n 1 1.1 streetNo. streetName Suburb Quests treasurelD (PK) treasureType [Common, Uncommon, Rare, Ultra Rare, ElieObjectifies treasurelD quesuD (PK) Cost purchaseDate purchaseTime Stores 1..n 1..1 beaconID storelD PK! storeName openTime closeTime City Points signupDate sighupTime questName Contains > Website miniQuests [1..5] Description advanced quests Progress [Active, Inactive Complete] Player Quest