Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hi!

 

This is not a question as such, I am mainly looking for somebody to tell me whether I am on the right track or way off the mark.

 

I am currently working through the MySQL chapters, in particular Normalization. i just want to check if I have understood everything corrctly so far.

Say I am creating a "language skills database" for an office, where all of the staff speak one or more foreign languages.

 

Staff_id (PRIMARY KEY)

first_name

last_name

email

telephone number

start_date

language

 

The way I understand normalization, I would need to first create table for the staff, removing the language column as each member of staff my speak more than one foreign language (French, German and Spanish for instance) which would be in violation of 1NF (i.e. atomicity) ie.:

 

Staff_id (PRIMARY KEY)

first_name

last_name

start_date

 

This table would be in 1NF, as each entry has a Primary key, each field is atomic and each record will appear only once.

 

I would then have to create a "Language Skills" , i.e.

 

lang_id (PRIMARY KEY)

language

 

Again, I understand that this table would be 1NF.

 

The Relationship between the two tables is currently M:M i.e. one memeber of stafff can speak mutiple languages, and each language (French for instance) may be spoken by many members of staff.

 

I would therefore require a thrid table (a link / intermediary table) along the following lines:

 

Staff_id (FOREIGN KEY)

lang_id (FOREIGN KEY)

 

 

whereby the link from the staff table to the link table is 1:M and the link from the Languages table to the link table is also 1:M

 

So then by now my database is 1NF (no listed fields, and each table has a primary key) and 2NF (any columns that would contain duplicated data - ie. the languages spoken by the staff - have been slipt off into a new table - i.e. the languages table)

 

Does this sound about right?

 

 

All comments and tips will be very much appreciated.

Thanks

LF

Link to post
Share on other sites

Sounds good to me. While I have studied the normal forms, at the end of the day, I don't remember them well, and really, common sense plays a big part in how I decided what's "right".

I think you're right to have the intermediary table with the foreign keys for staff ID and language ID, though, and your general logic seems sound.

  • Upvote 1
Link to post
Share on other sites
  • 2 weeks later...

This is the right way to do it. But your revised tables are not INF1. They are boyce-codd or INF3.

 

The number indicates what kind of normalization level you are on. INF1 is almost useless for tables as they are then redundant and lack integrity. INF2 means there is an unwanted dependency like postal code / postal address in the same table. The table is INF3/Boyce-codd if you remove the dependency. This is what we are looking for.

 

Hope this makes sense. Read up on normalization definition. You seem to have figured out how it works though.

Link to post
Share on other sites
  • 2 weeks later...

OK....next question. I am struggling with INNER and OUTER JOINS. How would I write a query to tell me what member of staff speaks which langauge?

 

So far I have come up with:

 

SELECT s.first_name, s.last_name

FROM staff AS s

INNER JOIN staff_lang

USING ( staff_id );

 

This will just link the "staff" table with the link table "staff_lang" containing the staff_id and lang_id foreign keys. Could anybody give me a tip on how to link the "lang"-->"staff_lang"<---"staff" tables to display the results?

 

Sorry for posting such a basic question, but I am really struggling with this.

 

Thanks,

LF

Link to post
Share on other sites

if I understand your question correctly, to display all members of staff who speak a particular language, you need to join the 3 tables and use a WHERE clause e.g.

SELECT s.first_name, s.last_name, s.start_date, l.language FROM staff AS s INNER JOIN staff_languages AS sl
USING (staff_id) INNER JOIN languages AS l USING (lang_id) WHERE l.lang_id = 02

the column names in the different tables must have exactly the same names to use the USING clause - staff_id must exist in both the staff table and the staff_languages table and lang_id must exist in both the staff_languages and languages tables. If the columns have different names you will need to use the ON clause.

  • Upvote 1
Link to post
Share on other sites

Outer joins are inclusive. It allows null's, like if a staff dosen't speak another/any languages, while Inner Joins are exclusive and will only show those who have a language in the staff_language table.

 

I don't know your structure, but this is should be done with three tables. One for staff, one for languages, and one for staff_languages.

 

SELECT s.first_name, s.last_name
FROM staff AS staff[/font]
OUTER JOIN staff_lang AS staff_lang ON ( staff.id = staff_land.staff_id )
INNER JOIN languages AS lang ON ( lang.lang_id = staff_lang.lang_id )

 

 

Table structures:

staff ( id, firstname, lastname, other data)

staff_lang ( lang_id*, staff_id* )

languages ( lang_id, name, other data )

 

As you can see, staff_lang should be only foreign keys to the staff and languages tables. A combined lang_id/staff_id in staff_lang will allow a staff to speak several languages.

 

I would then do an outer join for the staff_lang on the staff table to get all staff and their languages spoken. A Inner join from staff_lang to languages ensure all information about the language is found.

  • Upvote 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...