mi not studying in poly..
can list out the steps? i still dont' know how to do the
normalization steps by steps.. can teach me? thanks alot. and about
foregin/primary key. how to identify?
I still don't see how a shipment is linked to an order. Are they
even related in this case?
Anyway, a primary key is a unique number for each data row that
identifies the entry, and a foreign key is basically a reference
column to the primary key of another table.
A good example of a primary key in the database of, say,
Singaporeans would be our NRIC numbers (call it sgAns). Each of
these number is unique and only assigned once to one person in the
entire population, thus you can identify any Singaporeans just by
the NRIC number alone. Such values would then serve as a primary
key in a database table.
On the other hand, we have foreign keys. When they're in a database
table, they act as a constraint every time there's a new or changed
entry by checking the new value in the foreign key column is found
in the primary key column of another table. An example, extending
the NRIC one above, would be a database table of Singaporean-owned
cars and their owners (let's call it sgCars). Their owner's NRIC
number can be a foreign key in sgCars, and if there's a new entry
to sgCars containing a NRIC number not found in the sgAns, it would
be rejected and an error will be thrown.
Note that a database table can only have one primary key, but may
contain multiple foreign keys (eg. Orders table, see below). Just
remember, a primary key is for identification of each data row, and
a foreign key is used to filter invalid entries.
You may want to read more about these at Wikipedia:
http://en.wikipedia.org/wiki/Primary_key
http://en.wikipedia.org/wiki/Foreign_key
Regarding database normalization, in the simplest sense, is to
breakup a table with too many columns into small, little tables to
reduce data duplication,
such that data of same value don't appear twice or more. For
example, if you use the original database table in your first post,
if the same customer places 10 orders, you would have 10 entries
with his name, address and phone number appearing 10 times in
total. But if you split it up to two tables, Customers and Orders,
then even if that customer places 1000 orders, there would only be
1 row in the Customers table containing his name, address and phone
number, with each of his orders in the Orders table referring to
the customer via a foreign key "customer_id". Then just follow
through and continue breaking down the tables until they can't be
broken down further. Think of it as saving disk space, if that
helps. Read more about it here:
http://en.wikipedia.org/wiki/Database_normalization
Database:
Customers
id
name
address
phone
Orders
id
customer_id
date
hardware_id
qty
shipment_id
Hardware
id
supplier_id
name
price
description
Suppliers
id
name
phone
address
Shipments
id
supplier_id
qty
Note:
Primary keys are labelled in bold.
Foreign keys are labelled in green.
