Foreign Key

Relationship Between Collections/Tables: MongoDB

In this video tutorial we shall learn about normalization, and the way mongoDB handles relationship between two collections(Primary Key and Foreign Key concept in RDBMS)

Normalization: It refers to the process of organizing the data to minimize redundancy and dependency.

There is no 1 way of doing this in mongoDB. You could implement it according to the needs of your application.

For Example, Normalization is good in the application wherein we do more of write operation. De-normalization is good, wherein we only read.

In today’s tutorial, we shall see how we can implement relationship between two collections/tables and query the database using this relationship.

Database name: company
Collections: info, ceo

info collection

>use company
switched to db company
 
>db.info.find().forEach(printjson)
{
"_id" : ObjectId("517e829d005b19f1f0d96b25"),
"name" : "Apple",
"product": "iPhone5S",
"emp_no" : 100
}
 
{
"_id" : ObjectId("517e8377005b19f1f0d96b26"),
"name" : "Technotip",
"product": "Video Tutorials - Educational",
"emp" : [
"Satish",
"Kiran"
],
"Videos" : {
"mongo" : "MongoDB Videos",
"php": "PHP Video Tutorials"
}
}

Create another collection ceo and insert a document

>db.ceo.insert({
      name      : "SATISH",
      company_id: db.info.find()[1]._id
});
>db.ceo.find().forEach(printjson)
{
        "_id" : ObjectId("51822e2c60e925795355fd26"),
        "name" : "SATISH",
        "company_id" : ObjectId("517e8377005b19f1f0d96b26")
}

Here we store the _id value present in the second record of info collection inside first record’s company_id field of ceo collection.
This way we build a relation between two collections info and ceo.

Next, we query mongoDB to get some results based on this relationship.

> db.info.find({ _id: db.ceo.find()[0].company_id }).forEach(printjson)
{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "name" : "Technotip",
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}
>

This command(in first line) retrieves the document in the ‘info’ collection which matches the company_id value present in ceo collection and _id of info collection.

Primary Key and Foreign Key Concept in MongoDB

[youtube https://www.youtube.com/watch?v=7cYT0Qbu9tU]

YouTube Link: https://www.youtube.com/watch?v=7cYT0Qbu9tU [Watch the Video In Full Screen.]

Note: In traditional database systems(RDBMS) we accomplish the same by using primary key and foreign key concept. And make use of JOINS (Equi JOIN, RIGHT JOIN, LEFT JOIN) to query the database, which actually consumes more time and resources. In mongoDB, there is no need of JOIN’s and hence is faster than RDBMS.

Primary Key & Foreign Key Implementation: MySQL

This video tutorial illustrates the implementation of Primary key and Foreign key, using MySQL.

You can run the same commands / Queries shown in the video on both phpmyadmin as well as on MySQL console.

You can also look at: Primary Foreign Unique Keys, AUTO_INCREMENT: MySQL

Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys have unique values.
Primary Keys are NOT NULL by default.

Creating student table:

CREATE TABLE student(
 stud_id  int AUTO_INCREMENT,
 name     varchar(30) NOT NULL,
 age      int NOT NULL,
 PRIMARY KEY(stud_id)
);

Foreign Key
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Creating enrol table:

CREATE TABLE enrol(
 rol_no  int AUTO_INCREMENT,
 stud_id int NOT NULL,
 PRIMARY KEY(rol_no),
 FOREIGN KEY(stud_id) REFERENCES student(stud_id)
);

Primary Key & Foreign Key Implementation: MySQL

[youtube https://www.youtube.com/watch?v=eTK9TLciRpU]

YouTube Link: https://www.youtube.com/watch?v=eTK9TLciRpU [Watch the Video In Full Screen.]

Note:
1. You can have only 1 primary key per table. But can have zero or more foreign keys in a table.
2. You can not delete primary key row directly, without deleting all the rows referring to that row.

DBMS Basics: Getting Started Guide

Getting started with Database Management System.

In this video tutorial we’re trying our best to keep everything to the minimum, and make sure not to make it look complicated for beginners.

The tables and the normalization shown in the video are just for the purpose of demonstration.

DBMS is a software system which helps in managing incoming data, organizes it and provides certain ways for the data to be modified and/or extracted by the users or other programs.

Some examples of Database Management System Software include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.
Also: Some Native database systems that can be connected with PHP ?

Tables Relational databases are made up of relations, commonly known as TABLES.
Relationships exists between the tables and the data are inter-related by making use of Primary and Foreign keys.

Table Columns
It has unique names.
Each column has an associated datatype.
Columns are sometimes called as fields or attributes.

Table Rows
Each row in the table represents individual data.
Rows are also called as records or tuples.

Values
Every value must have the same data type, as specified by it’s column.

Key
To identify each row uniquely.
The identifying column in a table is called as key or primary key.

Schema
Complete set of table design for a database.
It’s like a blueprint for the database.

A Schema should show the tables along with their columns, and the primary and foreign keys. Usually primary key’s are underlines and foreign keys are italicized.

Database Management System: Basics

[youtube https://www.youtube.com/watch?v=aR44FbeeFH8]

YouTube Link: https://www.youtube.com/watch?v=aR44FbeeFH8 [Watch the Video In Full Screen.]

Anomalies
Assume that we’re running an online store.
We’ve a order table.

If a person called Satish orders Apple iPad, Mac Book and a iPhone from our site.
We store his name and address and quantity of his order.

Next, Satish moves to a different place before we process the order, now we will need to update his address at 3 places!

Doing 3 times as much work. We may miss updating Satish’s address in some place, making the data inconsistent. This is called modification anomaly.

If we design our database table in this way, we’ll need to take the address of Satish each time he orders something from our online store. This way, we need to always make sure the address(data) is consistent across all the rows. If we do not take care, we may end up with conflicting data.
Ex: One row may indicate Satish to be living in Bangalore and another row may indicated Satish to be living in New York!
This scenario is called Insertion anomaly.

Once all the orders of Satish has been processed, we delete the records. This way, we no longer have Satish’s address. So we can’t send any promotional offers etc to Satish in the future. If he want’s to order something again from our online store, he need to enter his address again. This scenario is called deletion anomaly.

We could solve these anomaly problems by making use of Primary and Foreign key’s and by developing the skill/art of normalization.

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.

Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

Foreign Key
A foreign key is a field in a relational table that matches a candidate key of another table. The foreign key can be used to cross-reference tables.

Relationships
We’ll cover
one-one
one-many
many-many
relationships in coming video tutorials, with some real time examples.

Primary Foreign Unique Keys, AUTO_INCREMENT: MySQL

Video tutorial illustrates the use of Primary Key, Foreign Key, Unique Key, AUTO_INCREMENT and NOT NULL in MySQL.

Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys have unique values.
Primary Keys are NOT NULL by default.

CREATE TABLE Apple
(
 id int NOT NULL,
 name varchar(255) NOT NULL,
 PRIMARY KEY(id)
);

Unique Key
The UNIQUE constraint uniquely identifies each record in a database table.

UNIQUE keys are not Primary Keys. but all Primary keys are Unique keys.

CREATE TABLE Apple
(
 id int NOT NULL,
 name varchar(255) NOT NULL,
 UNIQUE(id)
);

Foreign Key
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

CREATE TABLE Orders
(
 id int NOT NULL,
 O_No int NOT NULL,
 PRIMARY KEY(O_No),
 FOREIGN KEY(id) REFERENCES Apple(id)
);

If your MySQL uses InnoDB engine, then use the following query:

CREATE TABLE Orders
(
 id int NOT NULL,
 O_No int NOT NULL,
 PRIMARY KEY(O_No),
 FOREIGN KEY(id) REFERENCES Apple(id)
)ENGINE=InnoDB;

Video Tutorial: Primary Foreign Unique Keys, AUTO_INCREMENT: MySQL

[youtube https://www.youtube.com/watch?v=01Y1T44D9JU]

YouTube Link: https://www.youtube.com/watch?v=01Y1T44D9JU [Watch the Video In Full Screen.]

phpMyAdmin UI Interface Options
Options for assigning Primary key, unique key to the attributes..also the edit and delete keys.

AUTO_INCREMENT and NOT NULL

CREATE TABLE Apple
(
 id int NOT NULL AUTO_INCREMENT,
 name varchar(255) NOT NULL,
 PRIMARY KEY(id)
);

AUTO_INCREMENT is used for integer type data.. the advantage being, we need not explicitly send the data to this field. MySQL automatically inserts the value to this field.