Hi. I am a neewbie to this forum, and need a little help in designing my database for a an assignment. I am in the process of normalising the sample data that was given to me, and am having a little trouble getting my head around relationships.
My case study is to create a database for a video shop.
In normalising to 2NF, do we only create a new table where their is a many-to-many relationship? Example from my design...
I have a TITLE entity, and a TITLE-GENRE entitiy which has the following attributes (titleID*, genreID*, genre_name). Is this a many-to-many relationship? Im really struggling to get my head around which is a many-to-many and which are one-to-many. Do I create a new table so I have the following...
To establish if a relationship is many-to-many or one-to-many you can ask yourself a series of questions:
Can one genre apply to one or many titles?
Can one title belong to one or many genres?
Join the two answers up with "-to-" and there ya go
BTW - your first definition of TITLE-GENRE is wrong.
thanks mate.. i think i have it worked out now..its a many-to-many.
could you please tell me what was wrong about my definition?
and also... i am having trouble moving to 3NF... i have learnt that to go to 3NF, you ask yourself "if one column changes, will another column require a change". I have looked at my 2NF, and it doesnt really seem that any changes would require a change from another column... does this mean that 3NF is just the same as 2NF??
Yeh thanks guys i understand now and think I have it done. I was wandering.. if its not too much to ask, if someone could look at the stages I have taken to normalise, and perhaps point out any problems they see, before i create the logical and schema just so i dont run into any problems later.
i added the employee table because in the TITLE table, I have an attribute called "added_byID", which is a foreign key (i think thats the correct terminology) to the corresponding employee that added that title to the database so i created a new table for the empoyee details. Probably should add a few more details such as address, phone etc....
Ahh, I did not notice that, well done!
Ask yourself this - do you need to know the employees address/phone/etc if you are simply using this for audit purposes?
employeeID = 1
employeeName = 'georgev'
If you know that I added the record, what more would you like to know about me?
If the answer is nothing, then this raises a question of whether added_byId should be added_byUserName instead? The arguement could go either way...
But then again, maybe I'm just playing devil's advocate.
thanks mate.. yeh i was thinking that exact thing to be honest.. i mean im sure (making assumptions) that this theoretical video store would have employee records somewhere else, rather than in the same database as the movies are in....
in this case.. how should i approach this? just keep it the way it is? because if i got rid of the employee table, then sometimes when a user enters the employeeID, some might enter it in different ways, such as JohnSmith or John Smith or just John....
when you say add my username.. i just realised thats a much better idea.. a username can be a Primary Key as long as its unique right? I cant remember if thery have to be integers or not...
i think to fix that i should just leave it the way it is
i dont seem to see any problems myself with the rest of it so i think im gonna go ahead and make my logical design
ok cheers boys.. had a goood 20 minute read of that whole document (very good document i might add!) but still cannot understand where my problem is... ive gone back and forth, any chance of a little hint??