drmc99
Badges: 0
Rep:
?
#1
Report Thread starter 3 years ago
#1
Hello,

I was wondering if anybody could help me normalise this data:

Apprentice Number
Course Number
Apprentice Name
Apprentice Address
Course Name
Computers
Room Number

I need to break the data down into UNF,UNF Notes, 1NF,1NF Notes, 2NF,2NF Notes, 3NF,3NF Notes

Thanks.
0
reply
571122
Badges: 18
Rep:
?
#2
Report 3 years ago
#2
It's been a while since I've done normalization but I'll give it a shot. Take my proposed solution with a grain of salt.

UNF
PK Course Number
attr Course Name
attr Room Number
attr Computers
key Apprentice Number
attr Apprentice Name
attr Apprentice Address

I am not sure what the Computers field represents but I assume it's the number of computers in the room.

source:
http://www.sqa.org.uk/e-learning/DDFun01CD/page_10.htm

That's it for now. I'll try to do the rest on another day. Have you solved it yet?
1
reply
drmc99
Badges: 0
Rep:
?
#3
Report Thread starter 3 years ago
#3
Thanks for the response! I am still trying to work this out.
0
reply
571122
Badges: 18
Rep:
?
#4
Report 3 years ago
#4
(Original post by drmc99)
Thanks for the response! I am still trying to work this out.
1 NF

First normal form:
Each of your columns and each of your tables should contain one value - just one value - and there shoud be no repeating groups.

table APPRENTICE
Apprentice Number, Apprentice Name, Apprentice Address, Course Number
1, John, addr, 1
2, Jane, addr, 1

table COURSE
Course Number, Course Name, Room Number, Computers
1, pc, 5, 5

relationship: 1-to-Many between COURSE and APPRENTICE. 1 apprentice takes one course (because you can't have a repeating PK).

foreign key in COURSE table: Course Number

Note: a different, more flexible design might require a many-to-many linking table, with composite PK Course Number, Apprentice Number.

2NF

prerequisite: 1NF

Second normal form:
Is all about the relationship between your columns that are your keys and your other columns that aren't your keys.
"Any non-key field should be dependent on the entire primary key."


Scenario:
2NF is only a problem when we're using a composite PK, that is, a PK made of two or more columns.

table APPRENTICE
Apprentice Number, Apprentice Name, Apprentice Address, Course Number
1, John, addr, 1
2, Jane, addr, 1

table COURSE
Course Number, Course Name
1, pc, 5, 5

table ROOM
Room Number, Computers

Why a table ROOM?
We can figure out the number of computers by just looking at the room number. We now have one specific number of computers for one specific room. More reasons follow below:

What problem does 2NF solve?
-> If someone ever changed the Course Number but not the Course Name, we would have a conflict in our data: a wrong title for a particular course.
2NF is not needed if you're not using composite keys. You can skip right to 3NF.


3NF

prerequisite: 1NF, or 2NF with composite keys

Third normal form:
Similar to 2NF.
Can I figure out any values of this row from any of the other values in this row? I shouldn't be able to do that.
Look at your non-key fields.
"No non-key field is dependent on any other non-key field"


table APPRENTICE
Apprentice Number, Apprentice Name, Apprentice Address, Course Number
1, John, addr, 1
2, Jane, addr, 1

table COURSE
Course Number, Course Name
1, pc, 5, 5

table ROOM
Room Number, Computers

Note: this is ok for 3NF if we assume that a room will always have the same amount of computers. The reason we pull room out into a separate table for 3NF is because we don't want to repeat the number of available seats for each room every time in the COURSE table.

I'm not sure what 'Notes' means.

Why do we do normalization?
We want to minimize the redundancy of information, so that our database design becomes as flexible as possible and robust against future changes.

Note:
This normalization is based on lynda.com's Fundamentals of Programming: Databases by Simon Allardice.
I re-emphasize that this normalization should be taken with a grain of salt and might not be correct.

On a side note: I think the 1NF has to be a Many-to-Many table COURSE_APPRENTICE so that many students can take many courses. Right now it's just one student takes one course I think.
0
reply
drmc99
Badges: 0
Rep:
?
#5
Report Thread starter 3 years ago
#5
Cheers
0
reply
X

Quick Reply

Attached files
Write a reply...
Reply
new posts
Back
to top
Latest
My Feed

See more of what you like on
The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

Personalise

What factors affect your mental health the most right now? (select all that apply)

Lack of purpose or routine (72)
15.58%
Uncertainty around my education (79)
17.1%
Uncertainty around my future career prospects (42)
9.09%
Isolating with family (28)
6.06%
Lack of support system (eg. Teachers, counsellors) (19)
4.11%
Lack of exercise/ability to be outside (38)
8.23%
Loneliness (45)
9.74%
Financial worries (15)
3.25%
Concern about myself or my loved ones getting ill (44)
9.52%
Exposure to negative news/social media (33)
7.14%
Lack of real life entertainment (eg. cinema, gigs, restaurants) (47)
10.17%

Watched Threads

View All