bionic25 Posted December 8, 2009 Share Posted December 8, 2009 i am trying to figure out how to turn the following into a 3NF database but am really stuck and going a bit crazy over it. the field names of the non-normalised table are: "deptcode","module1","title1","module2","title2","module3","title3","group1","group2","group3","group4","group5","type","dayno","start","finish","weeks","lecturer1","lecturer2","lecturer3","lecturer4","lecturer5","lecturer6","lecturer7","lecturer8","room1","room2","room3","room4","room5" its for a university timetable. if anyone could help me out with how to make this 3NF, like what new tables foreign keys etc I would be SOOOO grateful thanks!! Quote Link to comment Share on other sites More sharing options...
premiso Posted December 8, 2009 Share Posted December 8, 2009 Any column that is repeating can be it's own table and in that table you would have a common foreign key linking to your main table (the auto increment id of your main table should be used as the foreign key). Since this does seem like homework that is about as far as I will go. Quote Link to comment Share on other sites More sharing options...
bionic25 Posted December 8, 2009 Author Share Posted December 8, 2009 thanks for your quick reply its not homework lol just a project im using to try and learn sql, i did the same with php and am okay at that now. i dont want the work done for me just help figuring it out. the other problem is the raw data comes in a .csv file. when i input the data how do i get it to disperse to the other tables sensibly. for example if i had a 'modules' table to hold the modules, and it was linked from the main table like how do i get the right module_ids to show in the main table. obviously in the 'modules' table itself the data will just be id'd with auto_inc but i dont get how the refernce in the main table will know what it is Quote Link to comment Share on other sites More sharing options...
JustLikeIcarus Posted December 8, 2009 Share Posted December 8, 2009 Ok I have to ask. Why third normal form specifically? Why not Second, Fourth or even Fifth? Quote Link to comment Share on other sites More sharing options...
bionic25 Posted December 8, 2009 Author Share Posted December 8, 2009 ive already had some experience of 1st and thought id have a got at 3rd. ive also heard that u dont really need to go beyond 3rd until you're like doing really compliacted stuff Quote Link to comment Share on other sites More sharing options...
Mchl Posted December 8, 2009 Share Posted December 8, 2009 Unless you know that you need anything beyond 3rd NF, you shouldn't probably go further than that. Quote Link to comment Share on other sites More sharing options...
bionic25 Posted December 8, 2009 Author Share Posted December 8, 2009 I agree with you! the problem i'm having is getting the database from unnormalised into 3NF :-\ Quote Link to comment Share on other sites More sharing options...
premiso Posted December 8, 2009 Share Posted December 8, 2009 create table secondarytable (rename to what you want) sectblid - primary key / auto inc group lecturer title room mainid - foreign key (added this via an edit.) Which leaves us with: main_table -mainid (pri key / auto inc id for table.) -deptcode -type -dayno -start -finish -weeks Basically, you have a 1 to many relationship between Main table and the secondary table. The secondary table contains the room etc (since you had them labeled as lecturer 1, room 1 module 1, I just assumed that they went together so created a table with them. Other then that it is hard to provide any more structural information to you given that I do not know what type of data goes into the tables etc, but hopefully that gets you on your feet and able to try. I would highly suggest that when you post again, post your current SQL structure you have created, as most people will not want to do this all for you. Learning is trying and asking, so try it and ask if it looks right. EDIT: See comment in psuedo sql structure. Quote Link to comment Share on other sites More sharing options...
JustLikeIcarus Posted December 8, 2009 Share Posted December 8, 2009 Well I dont know exactly what your data is but... You need to identify the minimal columns needed to identify a unique record then remove any thing that is related to columns that make up the primary key but is not part of the key itself. Unless you will be using an auto increment id field then the normalization starts occurring a bit differently. Quote Link to comment Share on other sites More sharing options...
bionic25 Posted December 8, 2009 Author Share Posted December 8, 2009 Thanks premiso, thats very useful. The main thing confusing me is this: There can be more than one lecturer per row. I have, therefore, made a separate table called "Lecturers". However each ID in this table only holds one lecturer's details, to comply with atomicity. How can I have the details of 2 Lecturers in the main table listing, or do I have to accept using multiple numbered columns like "Lecturer1", "Lecturer2" etc? thanks again for your replies Quote Link to comment Share on other sites More sharing options...
JustLikeIcarus Posted December 8, 2009 Share Posted December 8, 2009 or do I have to accept using multiple numbered columns like "Lecturer1", "Lecturer2" etc? Multiple numbered columns like you mention violates NF1's rules Here i took the original list of columns you gave and seperated them into groups. deptcode, type, dayno, start, finish, weeks module, title group lecturer room Each of those groups should primarily make up a table. I beleive that meets all of the NF3 requirements. Ill have to check. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.