Normalize? help me pls
-
UNF
Customer_Order (Customer_ID, Customer_Name, Customer_Address, Customer_Phone, Order_ID, Order_Date, Order_QTY,{Hardware_ID, Hardware_Prices, Hardware_Description, Hardware_Name, Supplier_ID, Supplier_Name, Supplier_Phone, Supplier_Address, Shipments_ID, Shipment_QTY})
how to do?
-
I'm not too sure how each shipment is related to an order, so I've placed shipment_id under Orders.
Basically, what you're trying to achieve through database normalization is to reduce duplication and inconsistency of data. For example, if I created the database with only one table like the one stated in your question, and one particular customer places 42 item orders with you. So one day, he call up and says that his address has changed and this requires you to update the database. Without normalization, you will have to update every single row that contains his address, thus providing a room for inconsistency errors while updating. Normalization allows you to update just one entry (Customers table) which all of his orders, in a separate table (Orders table), refer to.
Anyway, why you learning DB normalization liao? I thought you just started in poly?
Customers
id
name
address
phoneOrders
id
customer_id
date
hardware_id
qty
shipment_idHardware
id
supplier_id
name
price
descriptionSuppliers
id
name
phone
addressShipments
id
qty -
Originally posted by LatecomerX:
I'm not too sure how each shipment is related to an order, so I've placed shipment_id under Orders.
Basically, what you're trying to achieve through database normalization is to reduce duplication and inconsistency of data. For example, if I created the database with only one table like the one stated in your question, and one particular customer places 42 item orders with you. So one day, he call up and says that his address has changed and this requires you to update the database. Without normalization, you will have to update every single row that contains his address, thus providing a room for inconsistency errors while updating. Normalization allows you to update just one entry (Customers table) which all of his orders, in a separate table (Orders table), refer to.
Anyway, why you learning DB normalization liao? I thought you just started in poly?
Customers
id
name
address
phoneOrders
id
customer_id
date
hardware_id
qty
shipment_idHardware
id
supplier_id
name
price
descriptionSuppliers
id
name
phone
addressShipments
id
qtymi 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?
-
Business rules stated that
A SUPPLIER may sent many Shipments.However each shipment must be sent by exactly one supplier
-
Originally posted by youyayu:
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_keyRegarding 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
phoneOrders
id
customer_id
date
hardware_id
qty
shipment_idHardware
id
supplier_id
name
price
descriptionSuppliers
id
name
phone
addressShipments
id
supplier_id
qtyNote:
Primary keys are labelled in bold.
Foreign keys are labelled in green. -
cos
Customer----Order from-----Company
Supplier ------Supply-------> Company
Supplier ------- Recieve OrderSupplier -------Shipment-----> Company
Company------Deliver-------> Customer
-
Originally posted by youyayu:
cos
Customer----Order from-----Company
Supplier ------Supply-------> Company
Supplier ------- Recieve OrderSupplier -------Shipment-----> Company
Company------Deliver-------> Customer
So you mean there's two types of orders and shipments, one is between the company and the suppliers, and the other is between the company and the customers? Then you may have to separate the tables for each relationship accordingly.
-
Originally posted by LatecomerX:
So you mean there's two types of orders and shipments, one is between the company and the suppliers, and the other is between the company and the customers? Then you may have to separate the tables for each relationship accordingly.
what u mean
-
Originally posted by youyayu:
what u mean
-
Originally posted by LatecomerX:
but isin'it the same... when customer sent the orders detail is the same with wad ever the company order from the supplier? the company act as the middle man
-
Originally posted by youyayu:
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?
Then why are U doing normalisation?1NF,2NF,3NF? -
Originally posted by Xcert:
Then why are U doing normalisation?1NF,2NF,3NF?er. i studying database...of cos must know la
diploma in IT
-
Originally posted by youyayu:
er. i studying database...of cos must know la
diploma in IT
I see...then I hope U have,by now, found out how to identify the primary key and foreign keys(if any)...cos if U dunno...then basically no need to do liao...in case U dunno...the very simplified explanation is
primary key - the unique field that differentiates every record
foreign key - the primary key of another table that U want to integrate into ur table.
-
Originally posted by youyayu:
but isin'it the same... when customer sent the orders detail is the same with wad ever the company order from the supplier? the company act as the middle man
No, you need separate tables since suppliers usually sell in bulk whereas customers would place orders with you for relatively small amounts of goods.