How to convert a table into second normal form

Question

I am really struggling to convert the table into second normal form. I've tried examples which are much easier, however I can't seem to be able to identify the keys for this one and work my way forward. I have converted that to 1st normal form by duplicating the module information.

Answer 1

1NF: Only the key
    Remove repeated data/data groups 
    i.e. having a person and address table instead of single mega-table
2NF: The whole key
    Ensure that all nonkey columns are dependent on the whole key
    So with a table where employee name and location could be a primary key, 
    and there is a skill attribute the skill attribute isn’t dependent 
    on the whole of the key as 
    it isn’t dependent on location therefore must be split into two tables
3NF And nothing but the key
    Should not have any dependencies on things other than the primary key

Now this seems like a bit of a homework question so I won't do it for you but make a module table, make a room table. If you want third normal form making a booking table with module code and room number as a compound primary key, and the fields relevant to a booking.

Remember normalisation is to make all the data related and remove repetition.

Answer 2

Normalization has to do with avoiding to repeat the same data. As you can surely see in the image above, module data is (unnecessarily) repeated for some of the rows. The same goes for the extension number and the capacity.

Try to think about it like this. A room is booked at some time of some day for some module. A room has a capacity. A module has a title and a leader. When you want to call the Leader of a module you dial his extension.

From this I gather that there are 4 concepts in play which need a table:

Room
Leader (or employee)
Module
Booking

I will leave it up to you to find out which columns go where.

Hope it helps.

Answer 3

This is almost exactly the example used for 'factless fact tables' in analysis services, which illustrates the point. The thing represented by the table is a 'booking', which has no natural primary key. The true key would be a composite key of location-timeslot. You need to create an artificial primary key if you don't want to do that or the DBMS does not support that.

You need at least these tables- Location(room #, Capacity) Timeslot(day,starttime), module(code,name).

It's not clear if the person is tied to the module or tied to the booking. It's not clear if the extension is tied to the person or the location.