Question

ERDQuestion A group of students attend an end of semester party. The party was described by one attendee as‘very exciting’. Students played computer gamestill almost midnight! Each of the students has a name and a Player Name e.g. Fred Smith "The Slayer". Each of the games played has a name and a description. Each console has a unique serial no, name and the year of manufacture. The games can all be played on any ofthe various consolesthat have been broughtto the party. The consoles include: A Dell PC, A Playstation 3 and an X Box 360 When a player finishes a game, a judge recordsthe score obtained by the player and which console the games was played on. Assume that only the best score by a player for a console / game is recorded. a) Dothe following: o Draw theERD ▪ When converted to a relation schema, show all PKs and FKs. b) Assume that we now want to record every score recorded by a player for a console / game. o What changes are required? ▪ At 8pm Scottscore 8630 ptsfor Tetris on the PC. At 8:15 he scored 9100 pointsforthe combination.

Answer 1

Hi,

Please find the answer below:

------------------------------------------

Identify the entities.

• Student
• Game
• Console
• GameResult

-----------------

Identify the relationships among the entities.

• Player may play one or more Games.
• Player may play on one or more Consoles.
• Games may be played by one or more Players.
• Console may be played by one or more Players.
• Game may be installed one or more Consoles.

-------------------------------------

List the primary keys for the tables.

Student table

Columns:

• sid
• studentName
• playerName

Game table

Columns:

• gid
• name
• description

Console table

Columns:

• serialNo
• name
• yearOfManufacture

GameResult table

Columns:

• sid
• gid
• serialNo
• points

----------------------------------

Primary Keys

sid for Student

gid for Game

serialNo for Console

Combination of (sid, gid, serialNo) for GameResult table.

Changes to the database:

Modify the GameResult table to hold:

GameResult table

Columns:

resultID

• sid
• gid
• serialNo
• points
• dateTime

resultID is the primary key.

sid,gid, serialNO are foreign keys

--------------------------

Let me know if more information is required.

Hope this helps.