I am trying to visualize how a skills database will be laid out and I am getting confused.
I want to store skills, certifications, and degrees earned by a member, where a member can have zero to N of each. I also want to store the dates the certifications were earned and dates attended at the school where the degree was attained.
I feel I should create a table that stores the skills for each member like this:
... is it normalized if I put the certification dates in a table as above? This is where I am having trouble.
-cert_date (date certification was earned)
For education, do I need use a table to track the various schools and degrees obtained? I doubt that there will be duplicates for schools, but I guess if I am creating the database from scratch, it probably won't hurt.
Is this the right way to look at it?
Okay, it's pretty obvious this is a homework assignment. You can't normalize a schema by staring at it. This is a mathematical problem and you have to take it step by step. There's no "feeling" or "intuition," it's either right or wrong.
Go back to your book, and read the section on normalization. (And you can't read by simply staring at it, either.)
It will show you a set of steps you have to work through, and the order is actually pretty obvious: you get it in 1NF, then 2NF, 3NF, BCNF and *then* you can go up to higher forms like 4NF or 5NF. Each higher form requires that the schema be in the lower form.
You're going to have to show the work to turn it in as an assignment anyway, or at least, I hope you will.
Sorry to confuse anyone. It is not a homework assignment. It is actually a project that I was pushed into by the officer in charge of the unit I serve with. I am not a database guy, but apparently I am the most qualified out of the shallow pool of people he had to choose from. They want me to design it using Access (which I am not very familiar with) and it is supposed to store members and their associated skills with the ability to filter the list to only members with desired skills for rapid deployment. It is a collateral duty - meaning that I am not being paid extra for this.
I am using the internet for research. If there are any good sites for examples, etc., I am open to advice. I was hoping to lean on this forum for roadblocks I run into.
Thanks in advance,
From what I read on the internet, I thought I had a case where I had multivalued dependencies.
The members in the database can have several skills, certs, and degrees. I thought I had three many-to-many relationships, one between members and skills, one between members and certifications, and one between members and degrees. Under fourth normal form, I read that these relationships should not be represented in a single record such as:
and should be instead represented in three records:
I also read that these tables were called join tables, and in Access, the PKs for the primary tables must also be FKs in the join tables. Each join table will have two FKs. I am still hazy on the concept of a superset, but I assumed that the superset is the [Member|Skills] key in the case of the first table.
I guess my initial question could be rephrased to "Is my database layout normalized?"
I can definitely dumb down the database to get something functional and add to it after the first milestone, but I wanted to get it right and complete the first time.