Create a normalized Entity-Relationship Model
nancyj
GamesDatabase_E-R_Example1.pptxGenres
Genre_ID (PK)
Genre_Name
PCs
Account_ID (PK)(FK)
PC_Serial_No
PC Registration_Date
Currency
Currency_ID (PK)
Currency_Description
Pricing
Currency_ID (PK) (FK)
Account_Type_ID (PK) (FK)
Price
Account_Types
Account_Type_ID (PK)
Account_Type_Description
Games
Game_ID (PK)
Game_Name
Game_Description
Min_Number_of_Players
Max_Number_of_Players
Rating_Category (FK)
Typical_Time_to_Play
Game_Size_in Bytes
Digital_Signatures
Digital_Signature_ID (PK)
Account_ID (FK)
Game_ID (FK)
PC_Serial_No
Signature_Time_Date
Activated
Bytes_Downloaded
Time_to_Download
Gamer GamerID(PK)
Active
Password
E-mail_ID
First Name
Last_Name
Street_Address1
Street_Address2
City
Postal Code
Country (FK)
Home_Phone
Mobile_Phone
Games_Genres
Game_ID (PK) (FK)
Genre_ID (PK) (FK)
Rating
Rating_Category (PK)
Rating_Description
State_Province
State_Province (PK)
State_Province_Desc
Country(FK)
Country
Country (PK)
Country_Description
Account
AccountID(PK)
PayPal_Account
Currency_ID (FK)
Account_Type_ID (FK)
GamerID(FK)
1
The next two pages describe how to read the relationships. Use the small circled letter on the diagram to reference the relationship description on the last page.
Genres
Genre_ID (PK)
Genre_Name
PCs
Account_ID (PK)(FK)
PC_Serial_No
PC Registration_Date
Currency
Currency_ID (PK)
Currency_Description
Pricing
Currency_ID (PK) (FK)
Account_Type_ID (PK) (FK)
Price
Account_Types
Account_Type_ID (PK)
Account_Type_Description
Games
Game_ID (PK)
Game_Name
Game_Description
Min_Number_of_Players
Max_Number_of_Players
Rating_Category (FK)
Typical_Time_to_Play
Game_Size_in Bytes
Digital_Signatures
Digital_Signature_ID (PK)
Account_ID (FK)
Game_ID (FK)
PC_Serial_No
Signature_Time_Date
Activated
Bytes_Downloaded
Time_to_Download
Gamer GamerID(PK)
Active
Password
E-mail_ID
First Name
Last_Name
Street_Address1
Street_Address2
City
Postal Code
Country (FK)
Home_Phone
Mobile_Phone
Games_Genres
Game_ID (PK) (FK)
Genre_ID (PK) (FK)
Rating
Rating_Category (PK)
Rating_Description
State_Province
State_Province (PK)
State_Province_Desc
Country(FK)
Country
Country (PK)
Country_Description
P
O
D
C
B
A
M
N
L
K
J
I
T
S
U
V
Q
R
F
E
H
G
Account
AccountID(PK)
PayPal_Account
Currency_ID (FK)
Account_Type_ID (FK)
GamerID(FK)
W
X
3
The relationships between the entities:
An Account has zero or one PC – zero means a customer has created an account but not linked it to a PC yet
A PC / Account combination has one and only one Account
A Digital Signature is associated with one and only one Account
An Account has zero to many Digital Signatures – zero means the customer has yet to download a game
A Country can have zero to many Accounts
An Account have one and only Country
A State-Province can have one and only one Country
A Country can have zero to many State-Provinces
A Pricing record has zero to many Accounts – zero means no customer is using that Pricing
An Account has one and only Pricing record
A Pricing record has one and only Account Type
An Account Type has one to many Pricing records
A Currency has one to many Pricing records
A Pricing record has one and only Currency
A Game has zero to many Digital Signatures – zero means no customers have downloaded that game
A Digital Signature is associated with one and only one Game
A Rating can be associated with zero to many Games
A Game can have one and only one Rating
A Game belongs to one or more Genres
A Genre has zero to many Games – zero means a new genre without games yet
A Game_Genre has one and only one Game
A Game_Genre has one and only one Genre
An Account has one and only one Gamer
A Gamer has zero to many Accounts
