EMP and DEPT datasets for MongoDB

Since Oracle’s “EMP” and “DEPT” datasets are useful for practice, I’ve provided converted scripts for SQL Server and MySQL in previous articles. This tutorial will guide you through the process of creating an example dataset and practice adding entries into MongoDB.

The table definition in SQL is as given below:

–Table definition – Dept
CREATE TABLE Dept(
DeptNo INT,
DName VARCHAR(14),
Loc VARCHAR(13),
CONSTRAINT pk_Dept PRIMARY KEY (DeptNo) );

— Tabe Definition – Emp:
CREATE TABLE Emp(
EmpNo INT,
EName VARCHAR(10),
Job VARCHAR(9),
Mgr INT,
HireDate date,
Sal INT,
Comm INT,
DeptNo INT,
CONSTRAINT pk_Emp PRIMARY KEY (EmpNo),
CONSTRAINT fk_DeptNo FOREIGN KEY (DeptNo) REFERENCES Dept (DeptNo) );

Let’s implement it in MongoDB.

–Create the “Emp” and “Dept” collections
> db.createCollection(“emp”, { capped : true, size : 5242880, max : 5000 } )
> db.createCollection(“dept”, { capped : true, size : 5242880, max : 5000 } )

–Inserting records into “Dept” collection
db.dept.insert({“deptno” : 10,”dname” : “ACCOUNTING”,”loc” : “NEW YORK”})
db.dept.insert({“deptno” : 20,”dname” : “RESEARCH”,”loc” : “DALLAS”})
db.dept.insert({“deptno” : 30,”dname” : “SALES”,”loc” : “CHICAGO”})
db.dept.insert({“deptno” : 40,”dname” : “OPERATIONS”,”loc” : “BOSTON”})

–Inserting records into “Emp” collection
db.emp.insert({“empno” : 7839,”ename” : “King”,”job” : “PRESIDENT”,”mgr” : null,”hiredate” : “1981-11-17″,”sal” : 5000,”comm” : null,”deptno” : 10})
db.emp.insert({“empno” : 7698,”ename” : “Blake”,”job” : “MANAGER”,”mgr” : 7839,”hiredate” : “1981-05-01″,”sal” : 2850,”comm” : null,”deptno” : 30})
db.emp.insert({“empno” : 7782,”ename” : “Clark”,”job” : “MANAGER”,”mgr” : 7839,”hiredate” : “1981-06-09″,”sal” : 2450,”comm” : null,”deptno” : 10})
db.emp.insert({“empno” : 7566,”ename” : “Jones”,”job” : “MANAGER”,”mgr” : 7839,”hiredate” : “1981-04-02″,”sal” : 2975,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7788,”ename” : “Scott”,”job” : “ANALYST”,”mgr” : 7566,”hiredate” : “1981-04-19″,”sal” : 3000,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7902,”ename” : “Ford”,”job” : “ANALYST”,”mgr” : 7566,”hiredate” : “1981-03-12″,”sal” : 3000,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7369,”ename” : “Smith”,”job” : “CLERK”,”mgr” : 7902,”hiredate” : “1980-12-17″,”sal” : 800,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7499,”ename” : “Allen”,”job” : “SALESMAN”,”mgr” : 7698,”hiredate” : “1981-02-20″,”sal” : 1600,”comm” : 300,”deptno” : 30})
db.emp.insert({“empno” : 7521,”ename” : “Ward”,”job” : “SALESMAN”,”mgr” : 7698,”hiredate” : “1981-02-22″,”sal” : 1250,”comm” : 500,”deptno” : 30})
db.emp.insert({“empno” : 7654,”ename” : “Martin”,”job” : “SALESMAN”,”mgr” : 7698,”hiredate” : “1981-09-28″,”sal” : 1250,”comm” : 1400,”deptno” : 30})
db.emp.insert({“empno” : 7844,”ename” : “Turner”,”job” : “SALESMAN”,”mgr” : 7698,”hiredate” : “1981-09-08″,”sal” : 1500,”comm” : 0,”deptno” : 30})
db.emp.insert({“empno” : 7876,”ename” : “Adams”,”job” : “CLERK”,”mgr” : 7788,”hiredate” : “1987-05-23″,”sal” : 1100,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7900,”ename” : “James”,”job” : “CLERK”,”mgr” : 7698,”hiredate” : “1981-03-12″,”sal” : 950,”comm” : null,”deptno” : 30})
db.emp.insert({“empno” : 7934,”ename” : “Miller”,”job” : “CLERK”,”mgr” : 7782,”hiredate” : “1982-01-23″,”sal” : 1300,”comm” : null,”deptno” : 10})