Normalising (1 Viewer)

taker0

Member
Joined
Nov 24, 2007
Messages
60
Gender
Male
HSC
2009
I read the textbook but it was too complicated...it kept confusing me.

so how do you normalise a database?? cause no matter how many examples i try... either my tables are wrong or im connecting the wrong things.

also how do you identify and label primary keys/foreign keys etc.

any help would be great..
 

bazookie

Member
Joined
May 28, 2008
Messages
45
Gender
Male
HSC
2008
a primary key is the unique identifier for a table. for example, in a database of the computers in a school library, the unique identifier will be the computer number.

a foreign key is the unique identifier of something which COMES FROM ANOTHER TABLE. for example, in the computer users table in a school library, the foreign key will be the computer number which comes from the computer table, whilst the primary key could be student numbers.

to normalise a database, you must first decide how many tables you should split the database into. this is different depending on each question. After you have decided upon the number, you bassically just split the different fields from the main table into the seperate tables, with each table for a different purpose e.g

a table for computer information
a table for student information
and finally, a related table named computer users showing:
- the foreign keys computer number and student number
- the primary key (which you will make up for this table)
- other details which come from the main table (dependning on the question)

lol hope that helps.i don't think i explained it very well. If you don't undersand, find me a question and i will help you normalise it.
 
Last edited:

seremify007

Junior Member
Joined
Apr 29, 2004
Messages
9,997
Gender
Male
HSC
2005
Uni Grad
2009
If you have access to past papers or even the textbooks, look up the relevant chapters and physically draw out the tables with sample data inside. Then look for data which seems repetitive as well as data which is unique and that'll get you started on the correct train of thought as to which data is redundant and which data should form your primary key. Try to visualise the one-to-many relationships, etc... or if not, use Microsoft Access to do it.
 

Makro

Porcupine
Joined
May 16, 2006
Messages
415
Location
In between.
Gender
Male
HSC
2009
I found that one to be really hard (I had it in my trial or mid-course). They're usually not that confusing/vague. So just look at the answer?
 

bazookie

Member
Joined
May 28, 2008
Messages
45
Gender
Male
HSC
2008
ok the question says normalise into 3 tables... so:

table 1 will be called ITEMS and it will have:
-Item Number (primary Key)
-Item

table 2 is STUDENTS:
- Student ID (You must make this one up. This is this tables primary key)
- FirstName
- LastName
- Faculty

Table 3 is LOANS:
- Item Number (foreign key: relate this to table 1)
- Student Number (foreign key: relate this to table 2)
- Date Borrowed
- Date Returned
 
Last edited:

grendel

day dreamer
Joined
Sep 10, 2002
Messages
103
Gender
Male
HSC
N/A
bazookie said:
ok the question says normalise into 3 tables... so:

table 1 will be called ITEMS and it will have:
-Item Number (primary Key)
-Item

table 2 is STUDENTS:
- Student ID (You must make this one up. This is this tables primary key)
- FirstName
- LastName
- Faculty

Table 2 is LOANS:
- Item Number (relate this to table 1)
- Student Number (relate this to table 2)
- Date Borrowed
- Date Returned
that looks good. in the LOANS Table Item Number and Student Number are referred to as foreign keys. While primary keys are unique, foreign keys can be repeated within a table. for example if a student has 4 loans then that students id will appear four times in the LOANS Table
 

noturningback

Member
Joined
Nov 25, 2007
Messages
116
Gender
Undisclosed
HSC
2009
I have a problem with relationships when normalizing (ie. many to many, one to many, one to one)
I understand what each one is but I never get them right
 

Makro

Porcupine
Joined
May 16, 2006
Messages
415
Location
In between.
Gender
Male
HSC
2009
It's almost always one-to-many. I'm pretty sure a Primary key makes it one and then the foreign key that it's linked to, makes it many.

So if you have a primary key linked to a foreign key, by logic it's always one-to-many?
 

bazookie

Member
Joined
May 28, 2008
Messages
45
Gender
Male
HSC
2008
Makro said:
It's almost always one-to-many. I'm pretty sure a Primary key makes it one and then the foreign key that it's linked to, makes it many.

So if you have a primary key linked to a foreign key, by logic it's always one-to-many?
oh is that how it works? lol thats much simpler. my idea of it was much more complicated. Thanks for that =D
 

mijoe

Member
Joined
May 18, 2008
Messages
72
Location
Behind you
Gender
Male
HSC
2010
Not always one to many, you are seriously mistaken. It all depends on what's linked to what, you have to decide for yourself, it's not very hard.
 

Makro

Porcupine
Joined
May 16, 2006
Messages
415
Location
In between.
Gender
Male
HSC
2009
given the condition, primary to foreign (foreign means it's a primary key in another table, so this can be repeated, as said by someone else in this thread). So it wouldn't be one-to-one.

And for many-to-many, You can't use a primary key as one of the linked fields, isn't this right? Because then it's not unique.
 

grendel

day dreamer
Joined
Sep 10, 2002
Messages
103
Gender
Male
HSC
N/A
Makro said:
given the condition, primary to foreign (foreign means it's a primary key in another table, so this can be repeated, as said by someone else in this thread). So it wouldn't be one-to-one.

And for many-to-many, You can't use a primary key as one of the linked fields, isn't this right? Because then it's not unique.
i said a foreign keycan be repeated but it does not neccessarily have to be. it depends on the database. the only restriction is on the primary key that must be unique as its function is to identify a specific record.

consider the rta database. the drivers license number in a "Driver Table" would be a primary key. each driver has their own unique number. if you then had another table, say "Car Table" that connected drivers to vehicles then the drivers license number would be a foreign key in this table.

now consider if only one driver could be matched to one car in the "Car Table" then this is a one to one relationship as each Drivers License number appears only once(maximum) in the Car Table.

however, if a driver could be matched up to more than one car (you know i drive my corolla to work but take out the Ferrari on weekends :D ) then that would be a one to many relationship as my driver license number would appear twice in the Car Table.

note that it is not neccessary for a driver to be matched up to any car (you know, you drive your parents tarago to go out)
 

Takuto

Member
Joined
May 14, 2006
Messages
454
Location
abo town
Gender
Male
HSC
2008
yep that teacher is right

a question - can someone describe what a 'many-to-many' relationship entails as well as provide an example of its practical use?

i havent seen it yet
 

Makro

Porcupine
Joined
May 16, 2006
Messages
415
Location
In between.
Gender
Male
HSC
2009
* A one-to-one relationship occurs when each record in the first entity is related to exactly one record in the second entity. For example, in primary school each class has one teacher and each teacher relates to only one class.
* A one-to-many relationship occurs when one record in the first entity is related to many records in the second entity, but any record in the second entity only relates to one record in the first entity. For example, one student can take out many books from the library, but any one book can only be taken out by one student at a time.
* A many-to-many relationship occurs when each record in the first entity is related to many records in the second entity, and each record in the second entity is related to many records in the first entity. For example, each student studies many subjects, and each subject is studied by many students.
End of each description
 

Lebstr

Member
Joined
Feb 10, 2007
Messages
103
Gender
Male
HSC
2008
all u hve to do, is organise data in a series of tables to reduce data redundancy, thts when u use ur primrary keys and foeign keys
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top