Normalising (1 Viewer)

[taker0]

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]

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.

 

[seremify007]

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.

 

[taker0]

http://www.boardofstudies.nsw.edu.au/hsc_exams/hsc2005exams/pdf_doc/info_process_tech_05.pdf

there is a question about normalising/data redunancy which is question 22.

can anyone help me out??

 

[Makro]

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?

 

[grendel]

Go here http://web1.fairfield-h.schools.nsw.edu.au/moodle/course/view.php?id=16 and scroll down to the word document called "normalisation".

You will have to log in as guest.

this is a limited time offer!

Also you can check out this link http://en.wikibooks.org/wiki/Information_Processing_and_Technology/Information_Systems_and_Databases

 

[taker0]

grendel, thanks for those links. realy helpful.

 

[bazookie]

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

 

[grendel]

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

 

[bazookie]

woops.... forgot to add that in. lol

fixed it =D

 

[noturningback]

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]

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]

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]

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]

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]

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 ) 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]

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]

* 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]

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