I need some help confirming the accuracy of my work. QUESTION: List all the rela

Question

I need some help confirming the accuracy of my work.

QUESTION:

List all the relations derived from the entity types of the ERD (Seen below), and their initial attributes.

MY ANSWER:

Player (Username, FirstName, LastName, Email, DOB, StreetNumber, StreetName, Suburb, City, Postcode, TotalPoints, SignUpDate, SignUpTime)

Treasure (TreasureID, Description, PointsAwarded, WebsiteLink)

Beacons (BeaconID, Story)

Quests (QuestID, Name, Beacon, TreasureID)

Account (Username, Password)

Gender (Username, GenderType)

PhoneNumbers (PhoneNmber, Username)

TreasureTypes (TreasureID, TreasureType)

I'm fairy confident it is incorrect and just need to know what I need to add, change or remove. Thanks

Explanation / Answer

Consider following things when you try to add tables or attributes from ERD :

- Make sure you know all possible relationships between entity sets.

- create one table for each entity set.

- identify relationship cardinality between two entity sets.

- Take relationships one by one and follow given steps :

-- if cardinality between two relationship is many to many then only you need to add new table for that relationship.

--- Create a table with name of relationship or any suitable name. here i have used table1_table2 to show relationship between two tables table1 and table 2.

--- add key of both tables in the new relationship table. Also add relationship attributes.

-- if cardinality is not many to many :

--- Take a table with many cardinality (i.e if two tables table1 and table2 has many to 1 cardinality then take table1 , in case of one-to-one cardinality you can choose any table. )

--- add key attribute of other table in the table selected in first step.

--- add relationship attribute to the table selected table in first step.

These are the only things you need to take care of while you convert ERD to Database tables.

Answer for your question :

Account (Username, Password)

Player (Username, FirstName, LastName, Phone1, Phone2, Phone3, Email, DOB, Gender, StreetNumber, StreetName, Suburb, City, Postcode, TotalPoints, SignUpDate, SignUpTime, BadgeID, Badge_purchaseDate, Badge_PurchaseTime, Badge_Cost, QuestID, Progress)

Account_Player(player_Username, account_username)

Badges(BadgeID, Name, Description, StoreID)

Store(StoreID, Name, OpenTime, CloseTime)

Beacons(BeaconID, story)

Treasure(TreasureID, Type, Description, PointsAwarded, WebsiteLink, QuestID)

Player_Beacon_Treasure(Plyer_Username, BeaconID, TreasureID)

Quests(QuestID, Name, BeaconID, AdvancedQuestID)

It may look too complicated but you will have a database that has least amount of redundancy.

You can try creating one table for each relationship too like we are doing in case of many-to-many cardinality. But it is not needed. It will create redundancy when primary key is made up of many attributes.

If you have any doubts then you can ask in comment section.

EDIT : (Answer to first step) :

Account (Username, Password)
Player (Username, FirstName, LastName, Phone1, Phone2, Phone3, Email, DOB, Gender, StreetNumber, StreetName, Suburb, City, Postcode, TotalPoints, SignUpDate, SignUpTime)
Account_Player(player_Username, account_username)
Badges(BadgeID, Name, Description)
Player_Purchase_Badges(BadgeID, Player_Username, PurchaseDate, PurchaseTime, Cost)
Stores_Sell_Badges(BadgeID, StoreID)
Store(StoreID, Name, OpenTime, CloseTime)
Treasure(TreasureID, Type, Description, PointsAwarded, WebsiteLink)
Quests(QuestID, Name)
AdvancedQuests(QuestID, AdvancedQuestID)
Beacons(BeaconID, story)
Player_Find_Treasure(Player_Username, TreasureID)
Player_Find_Beacons(Player_Username, BeaconID)
Treasure_Find_Beacons(TreasureID, BeaconID)
Player_Quests(Player_Username, QuestID, progress)
Player_Treasure(Player_Username, TreasureID, progress)
Tresure_Leadsto_Quests(TresureID, QuestID)
Beacons_Assignedto_Quests(QuestID, BeaconID)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects