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...

TITLE
GENRE(genreID, genre_name)
TITLE-GENRE(titleID*, genreID*)...

Would this be correct?

Sorry if you dont understand any of this

Many thanks in advance

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.

pootle flump ur codings are working excelent.

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??

MAny thanks

Many-to-many relationships are broken down with what is called an interim table, which is a table with a composite key made of the primary keys from each table.

Example:
One employee can be assigned to many projects.
One project can have many employees assigned to it.

Employees(employeeID, Fname, Sname, ...)
Projects(projectID, title, descript, ...)
EmployeeProjects(empliyeeID, projectID)

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.

many thanks

Where did the employee table come from?

This article is well worth a read!

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?

Imagine:
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

thanks mate

Q1 - yes. Q2 - no

pootle flump ur codings are working excelent.

Can all employees access this system?
Again, the answer is contextual - it depends on the business requirements.

Here's a tricky one for you... What if an employee leaves?
added_byID would still equal 1, but the record where employeeID has been removed - see a problem?

But it may be a business requirement to not delete employee acounts/details.

You're not going to be wrong with your answer, you just have to justify why you chose this method (or in fact, why you cannot justify this method).

Look at the first definition you had of TITLE-GENRE. Then look at the link George gave you paying particular attention to 2NF. Then go back to the definition. Then 2NF. Notice owt?

pootle flump ur codings are working excelent.

Yep, this is known as a "natural key"
Poots rasies a good point. I did not pick up on that!

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??

Put 2NF in your own words. What does it mean?
Also please repost your table definition for that table as it was in your first post (to make ure we are on the same page).

pootle flump ur codings are working excelent.