SQL8

INSTRUCTOR
VINAY NOAH

More Than One Tables

Our example so far involves only one table “products“. A practical database contains many related tables.+

Products have suppliers. If each product has one supplier, and each supplier supplies only one product (known as one-to-one relationship), we can simply add the supplier’s data (name, address, phone number) into the products table. Suppose that each product has one supplier, and a supplier may supply zero or more products (known as one-to-many relationship). Putting the supplier’s data into theproducts table results in duplication of data. This is because one supplier may supply many products, hence, the same supplier’s data appear in many rows. This not only wastes the storage but also easily leads to inconsistency (as all duplicate data must be updated simultaneously). The situation is even more complicated if one product has many suppliers, and each supplier can supply many products, in amany-to-many relationship.+

One-To-Many Relationship

Suppose that each product has one supplier, and each supplier supplies one or more products. We could create a table called suppliers to store suppliers’ data (e.g., name, address and phone number). We create a column with unique value called supplierID to identify every suppliers. We set supplierID as the primary key for the table suppliers (to ensure uniqueness and facilitate fast search).+

To relate the suppliers table to the products table, we add a new column into the products table – the supplierID. We then set the supplierID column of the products table as a foreign key references the supplierID column of the suppliers table to ensure the so-called referential integrity.+

Database: felightlabs Table: suppliers
supplierID INT	name VARCHAR(30)	phone CHAR(8)
501	ABC Traders	88881111
502	XYZ Company	88882222
503	QQ Corp	88883333

Database: felightlabs Table: products
productID INT	productCode CHAR(3)	name VARCHAR(30)	quantity INT	price DECIMAL(10,2)	supplierID INT (Foreign Key)
2001	PEC	Pencil 3B	500	0.52	501
2002	PEC	Pencil 4B	200	0.62	501
2003	PEC	Pencil 5B	100	0.73	501
2004	PEC	Pencil 6B	500	0.47	502

We need to first create the suppliers table, because the products table references the suppliers table. The suppliers table is known as the parent table; while the products table is known as the childtable in this relationship.+

+

PREVIOUS UNIT