[piouson]

OK so we were given a table to normalize and I did just that, only to get a 'B'
Guys please take a look and tell me how this can be better normalized.

A student information systems table.
Table: 1NF
StdNo || Fname || Lname || CourseID || EntryYr || URI || Address || Town || County || PCDistrict || PCArea || Phone || Email
(PC = postcode. e.g for PC = PM12 4BX, PCDistrict = PM12, PCArea = 4BX)

Rules
1. An individual person may have enrolled for more than one course.
2. Each time a person registers for a course a new student number (StdNo) is assigned to them.
3. More than one person may live at the same address.
4. Telephone number is associated with the address (i.e. the house) rather than individual persons.
5. Email addresses are associated with individual persons.
6. URI is unique to each person.

What I did
Table1: StdNo || URI || CourseID || EntryYr
Table2: URI || Fname || Lname
Table3: StdNo || Email
Table4: Phone || PCDistrict || PCArea || Town || County
Table5: Phone || Address

I have underlined my primary keys.
Thanks for your help guys

[Atli]

Hey.

Rules 1 and 2 don't make sense to me. Does a single person registered for two courses have two StdNo values, or does the person get a new StdNo when she/he registers for the first course and it then gets reused for the second course? Personally I would think that a StdNo would serve as a PK for the individual persons... but that doesn't seem to fit the rules


Anyways, let me point out a couple of things first.

• In Table1, you underline both StdNo and URI as PKs, which I take to mean a single joint PK, right? If that is the case, then the problem becomes that the StdNo values won't be unique. That is: because they are a part of a joint key, rather than being a unique PK, each URI can be joined with every possible StdNo value, even if another URI is already using that same value. - You rules don't specifically mention this, but one would assume the StdNo value should be unique. This would violate that.
  
  
• In Table3 you associate an Email with a StdNo. This would be fine if the StdNo were unique and each person only had one StdNo. But this design, as well as the ambiguity in the rules, seem to allow multiple StdNo to be associated with each person, which makes this a less then optimal thing to do. - Instead, as per rule 5, you should be associating the Email addresses with the URIs, which are clearly unique to a person.
  
  
• In Table4 you are associating a Phone number (as the PK) with an entire area, not a specific address. That you do in Table5, but there you do not associate the address with an area. It could be argue that if you moved the Address into Table4, that you could remove Table5 altogether and it would solve this. However, for a normalized design that doesn't really work either. (I'll explain further below when I talk about the addresses.)

OK, then. How can this be made "better"?

Looking at the data, there are only two base "objects" of sorts: the person, and the address.

The URI, the Fname and the Lname all belong to a single person, and should therefore be pulled out into a single table, like your Table2, ideally named "person". (Using "Table1", "Table2", etc... gets very confusing very fast.)

The Address, Town, County, PCDistrict, PCArea and Phone all belong to the address. However unlike the person, creating normalized tables for addresses is a little more complicated. If you read what I wrote about the 3NF in my Normalization tutorial, the second part specifically, you should see what I mean.

Once you have that sorted, there are only three things remaining:

• Associating each person with each course he/she is taking. This would involve setting up a N:M relationship. In this case, it seems you should use the StdNo as the PK of that table, with URI and CourseID as FKs to the person and course tables, respectively. I also assume EntryYr should be in this table.
  
  
• Associating each person with a single address. This would require a 1:N relationship, where the PK of the address would be placed in the person table.
  
  
• Associating the Emails with their persons. That you could do in a simple email table, where the Email could be the PK with the person's URI as a FK to the person table.

[piouson]

Atli, on 25 November 2011 - 08:56 PM, said:

Hey.

Rules 1 and 2 don't make sense to me. Does a single person registered for two courses have two StdNo values, or does the person get a new StdNo when she/he registers for the first course and it then gets reused for the second course? Personally I would think that a StdNo would serve as a PK for the individual persons... but that doesn't seem to fit the rules

Thank you so much, your response was not only fast, but damn no one never explained to me like this.

Well Rule 1 and 2 dint make sense to me as well, but apparently the question was to test how well we can stick to the rules. Many other tables have a unique StdNo, but not in this case, as only URI is truly unique because each student have only one URI.

A student registered for 3 courses will have 3 StdNo values, and 3 Email values to go with each StdNo value!!

Another bizarre rule is the Telephone number, this is not a mobile number but a home phone, and all students living in the same home, shares the same telephone number. This makes telephone unique to each address!

With these mentioned, may be you, and everyone else can find more tips to guide me
Thanks for the link, I'll go over there and see what I can make of it, then come back to this.

[piouson]

Atli, on 26 November 2011 - 03:56 AM, said:

OK, then. How can this be made "better"?

Looking at the data, there are only two base "objects" of sorts: the person, and the address.

The URI, the Fname and the Lname all belong to a single person, and should therefore be pulled out into a single table, like your Table2, ideally named "person". (Using "Table1", "Table2", etc... gets very confusing very fast.)

I understand this part, Student Table = URI || Fname || Lname

Atli, on 26 November 2011 - 03:56 AM, said:

The Address, Town, County, PCDistrict, PCArea and Phone all belong to the address. However unlike the person, creating normalized tables for addresses is a little more complicated. If you read what I wrote about the 3NF in my Normalization tutorial, the second part specifically, you should see what I mean.

Now this part I don't have a clue as to how best I should normalize this.

Atli, on 26 November 2011 - 03:56 AM, said:

Once you have that sorted, there are only three things remaining:

• Associating each person with each course he/she is taking. This would involve setting up a N:M relationship. In this case, it seems you should use the StdNo as the PK of that table, with URI and CourseID as FKs to the person and course tables, respectively. I also assume EntryYr should be in this table.

Course Table = StdNo || CourseID || EntryYr || Email

The 'EntryYr' is associated with the 'CourseID', the 'Email' is associated with the 'StdNo', are you suggesting CourseID as my PK in the course table, instead of StdNo?

Atli, on 26 November 2011 - 03:56 AM, said:

Associating each person with a single address. This would require a 1:N relationship, where the PK of the address would be placed in the person table.

Associating the Emails with their persons. That you could do in a simple email table, where the Email could be the PK with the person's URI as a FK to the person table.

Remember a URI may have more than one email, is it ok to have URI and Email on same table as this?

Please see attachment for table.

Attached image(s)

•