Can you help me?

[iProgram]

Hello, I'm a college student and I've been given a pretty tricky homework problem. I'm great with SQL since I love code, but normalizing is not my forte. My professor said there is an issue with this relation aswell, but I beleive it has to do with the StationAdress, I'm still not sure though... Can you help me normalize the following relation and point out if another attribute is needed or what the tricky part is, I'd really appreciate it!

FIRE_REPORT(FireID, Alarms, Address, FiremanID, FiremanName, FiremanPhone, FiermanHomeStation, StationAdress, StationPhone, TruckID, License, TruckHomeStation)

Context: 
1.Each fire can have several firefighters assigned to it. 
2.Each fire can have several fire trucks assigned to it. 
3.Each firefighter can be assigned to several fires
4.Each fire truck can be assigned to several fires
5.Each firefighter has a single home station
6.Each fire truck has a single home station
7.Each station name is unique
8.Each station has a single phone number

Adress= Adress of the fire
FiremanPhone= personal phone number
Alarms= severity of the fire, example; 1 alarm fire, 2 alarm fire...

Example of a Truck Home Station/Fireman Home Station: LittleTown Station 5

 

[xenou]

The biggest problem right off the bat is you have everything in one table. The foreign keys to other tables (Fireman ID, Station ID, Truck ID) means you should have other tables - Firemen, Stations, Trucks.

Actually strictly speaking if you want to follow the rules of normalization the whole point is to create the other tables where needed - so that's what you need to do.

 

[iProgram]

The biggest problem right off the bat is you have everything in one table. The foreign keys to other tables (Fireman ID, Station ID, Truck ID) means you should have other tables - Firemen, Stations, Trucks.

Actually strictly speaking if you want to follow the rules of normalization the whole point is to create the other tables where needed - so that's what you need to do.

Yeah I think thats part of the problem, their is no StationID! I understand the process of normalization, i'm just not very good at it. I was hoping for some help breaking down this relation FIRE_REPORT into other relations.

 

[xenou]

Other relations (tables) are:
Firemen
Trucks
Stations

This looks like a somewhat advanced problem (with many to many relationships involved) and I'm afraid you don't even know the basics yet. What textbook are you using? What class are you in?

 

[iProgram]

Other relations (tables) are:
Firemen
Trucks
Stations

This looks like a somewhat advanced problem (with many to many relationships involved) and I'm afraid you don't even know the basics yet. What textbook are you using? What class are you in?

I'm in an introductory database management class, currently using Database Concepts KROENKE 5th Edition.

I've normalized numerous relations before, i'm familiar with foreign keys, primary keys, functional dependencies etc...it's just the only thing stopping me from doing this problem myself is that my professor stated several times how there's CLEARLY something wrong with this relation. What he meant by that I'm not sure, I was guessing that StationID and StationName are two missing attributes needed but I don't know if that's what he meant. I am somewhat confident though that it has something to do with some sort of missing attribute or attributes, since we went over an example similar to this problem in class.

 

[xenou]

As I stated already, you have fields in the FIRE_REPORT table that belong in *other* tables. For example, the Fireman Name and Fireman Phone Number don't belong in the FIRE_REPORT table. Those attributes belong in the FIREMEN table.

Does that make sense? You are using more than one table here. What are the tables in your database?

 

[iProgram]

As I stated already, you have fields in the FIRE_REPORT table that belong in *other* tables. For example, the Fireman Name and Fireman Phone Number don't belong in the FIRE_REPORT table. Those attributes belong in the FIREMEN table.

Does that make sense? You are using more than one table here. What are the tables in your database?

So do you want me to type out what I have so far so you can check it? I guess I should have thought of that earlier

I'll be back in 1-2 days with my current work, hopefully you can look over it and tell me what I did wrong

 

[iProgram]

OK, so here's what I came up with. I added the attribute StationName to the original relation.


FIRE_REPORT{FireID, FiremanID, StationName, TruckID)

FIREMAN_INFO(FireManID,FiremanName,FiremanPhone,FiremanHomeStation)

TRUCK_INFO(TruckID,License,TruckHomeStation)

STATION_INFO(StationName,StationPhone,StationAdress)

FIRE_INFO(FireID,Alarms,Adress)


Referential Integrity Constraints

FiremanID in FIRE_REPORT must exist in FiremanID in FIREMAN_INFO

TruckID in FIRE_REPORT must exist in TruckID in TRUCK_INFO

StationName in FIRE_REPORT must exist in StationName in STATION_INFO

FireID in FIRE_REPORT must exist in FIREID in FIRE_INFO

 

[xenou]

Yes, that looks like a database now. For consistency (though it's not necessary) you could use a StationID too, as the primary key in the stations table.

My only question is this: Is Fire_Report supposed to be an actual table, or is this really a "report" that is being generated?

The next step is to consider the relationships between the tables - are they one to many, one to one, or many to many. If you have many to many relationships, you'll need junction tables too (i.e., a fireman relates to many fire reports since he fights different fires; a fire report relates to many fireman since more than one fireman fights a fire). Using junction tables (aka linking tables, or associative tables) is a very important topic when you cover the basics of DB design.

 

[iProgram]

Yes, that looks like a database now. For consistency (though it's not necessary) you could use a StationID too, as the primary key in the stations table.

My only question is this: Is Fire_Report supposed to be an actual table, or is this really a "report" that is being generated?

The next step is to consider the relationships between the tables - are they one to many, one to one, or many to many. If you have many to many relationships, you'll need junction tables too (i.e., a fireman relates to many fire reports since he fights different fires; a fire report relates to many fireman since more than one fireman fights a fire). Using junction tables (aka linking tables, or associative tables) is a very important topic when you cover the basics of DB design.

Fire_Report is simply a table.

As far as the junction tables I'm not sure we're going to divulge that deep at least as of now. I figured since the Station Name is unique (as listed in the context) I wouldn't need the StationID. However, I am a little shaky on the foreign keys though, is it okay that all my foreign keys are in the Fire_Report table? I actually researched this problem and I saw someone do it like this;

STATION(Phone, Name, Address)
TRUCK(ID, License, HomeStation)
FIREMAN(ID, Name, Phone, HomeStation)
FIRE(ID, Alarms, Address)
FIRE_REPORT(FireID, FiremanID, StationPhone, TruckID)

StationPhone in FIRE_REPORT must exist in Phone in STATION.
HomeStation in TRUCK must exist in Name in STATION.
HomeStation in FIREMAN must exist in Name in STATION.

http://pastebin.com/s560S4GN