-1

enter image description here

Hoping to get some assistance on how this would be represented in a database table (3rd normal form). I feel what I have done is incorrect. I understand that with a one to many association, the primary key from the "1" table (in this instance Alert) should be included in it's child classes however I am unsure what the Primary Key should be for Text Response & VoiceResponse?

What I have does not look correct but I do know that each object requires a uniqueId. Note: The ObservationId attribute is from a 1 to many class that has been cut out of the pic.

The tables I have so far are:

Alert: AlertId (PK), AlertType, ObservationId

TextResponse: TextResponseId (PK), AlertID (FK), TextResponse

VoiceResponse: VoiceResponseId (PK), AlertId (FK), SoundClip

Appreciate any help.

philipxy
  • 14,867
  • 6
  • 39
  • 83
LukeJ
  • 55
  • 1
  • 5
  • What are the foreign keys in the two response tables? This is not clear to me. – Tim Biegeleisen Apr 06 '18 at 03:39
  • AlertId is used for both I would imagine - the textbook I am using states "one to many associations - add the primary key attribute(s) of the "one"class to the table that represents the "many" class. In this instance it would be AlertId – LukeJ Apr 06 '18 at 03:44
  • May I ask, are you required to have separate tables for voice and text responses? – Tim Biegeleisen Apr 06 '18 at 03:50
  • Hi. Please read my comment on your other recent question. How can you ask about normalization yet not give FDs? Your textbook will tell you how to map models to tables, where are you stuck? (What is your textbook?) Does your textbook address modeling subtypes? PS Please clarify via edits, not comments. – philipxy Apr 08 '18 at 09:25
  • Re comments on your (now-deleted) other recent question, also relevant to this one: It is strange that you call things like "Please google 'stackexchange homework' & show your reasoning [for] your work" & "The post should be telling us what is going on" to not be "constructive feedback". You don't act on my comments here either. As is, your questions require (good) answers to reproduce & apply your textbook, from scratch. What does *your text* say to do & where are you stuck? PS Neither your post nor the accepted answer shows any understanding of "normalization". – philipxy Apr 09 '18 at 20:54

1 Answers1

1

I can see a possible dependency here which perhaps you overlooked. Your current design would only be normalized if there were no relationship between an AlertId and the text/sound content. But, in practice, you would probably see the same alerts happening multiple times. Under your current design, you would be storing the same text/voice content multiple times. To fix this, you can refactor by changing the two response tables into pure bridge tables:

TextAlert:  TextResponseId,  AlertID (primary key is both columns)
VoiceAlert: VoiceResponseId, AlertId (PK both columns)

Then, create two new tables to store the actual text/voice content:

TextResponse:  TextResponseId  (PK), TextResponse
VoiceResponse: VoiceResponseId (PK), SoundClip

Now your design is robust to the same alert content appearing more than once.

Another comment I can make is that in practice you might want to have just one response table, which can store either text or sounds, perhaps as a blob.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • @LukeJ There may be other issues which they want you to find, but I just tried to point out what was obvious to me. Maybe stare at the actual data for a while and try to find something else. – Tim Biegeleisen Apr 06 '18 at 03:59