Creating Database with MySQL (Commands to create Sample database with five related tables)



Lets assume we want to create a database for the company named GROUP4, and  is in need of database that contains five tables, Which are Managers, Departments, Suppliers, Customers/Clients and Products.

 

1. Demonstration of ERD:

ENTITY RELATIONSHIP DIAGRAM

The ERD of GROUP4 Database

 

2. Demonstration of MySQL Commands/Statements:

(You can copy these commands and paste them in your MySQL console then execute; they will work fine. )

 

 

DROP DATABASE IF EXISTS GROUP4;

create database GROUP4;
use GROUP4;
create table Manager
(
MANID CHAR(4),
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,

GENDER enum('male','female'),
DOB date,
SALARY INT NOT NULL,
primary key(MANID)
);
create table Department
(
DEPTNO CHAR(4),
DEPT_NAME VARCHAR(50) NOT NULL,
MANID CHAR(4),
LOCATION VARCHAR(25),
primary key (DEPTNO),
FOREIGN KEY(MANID) REFERENCES MANAGER(MANID)
);
create table SUPPLIER(
DEPTNO CHAR(4),
SUPID CHAR(4) PRIMARY KEY,
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,
GENDER enum('male','female'),
SALARY INT NOT NULL,
COMMISSION INT(4),
FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO)
);
create table CUSTOMER (
CUSTOMERID  INT(3) AUTO_INCREMENT,
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,
COMPANY VARCHAR(20) NOT NULL,
PRIMARY KEY(CUSTOMERID)
);
create table PRODUCT (
PCODE INT(3) PRIMARY KEY,
DEPTNO CHAR(4),
SUPID CHAR(4),
CUSTOMERID INT(3),
PRODUCT_NAME VARCHAR(30) NOT NULL,
PRICE INT NOT NULL,
ITEM INT(3),
SALESDATE date,
FOREIGN KEY(SUPID) REFERENCES SUPPLIER(SUPID),
FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO),
FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID)
);
insert into Manager values('M/01','Steven', 'Washokera', 'male', '1991-06-16', 5300000),
('M/02','Cecilia', 'Mzole', 'female', '1992-06-16', 4300000),
('M/03','Yassin', 'Mpare', 'male', '1998-06-16', 3300000),
('M/04','Glady', 'Mtimbuka', 'female', '1991-09-19', 4100000),
('M/05','James', 'Kanyala', 'male', '1993-01-11', 3500000
);
insert into Department values('D/01','Consumable', 'M/01', 'Posta'),
('D/02','Cleaning Material', 'M/02', 'Posta'),
('D/03','Food Court', 'M/03', 'Kariakoo'),
('D/04','Stationary', 'M/04', 'Mnazi Mmoja'),
('D/05','Beverage', 'M/05', 'Mnazi Mmoja'
);
insert into Supplier values('D/01', 'S/01','David', 'Joseph', 'male', 700000, 23000),
('D/02', 'S/02','Bruce', 'Wills', 'male', 550000, 43000),
('D/03', 'S/03','Michael', 'David', 'male', 527000, 63000),
('D/04', 'S/04','Kelvin', 'Halt', 'male', 650000, 43000),
('D/05', 'S/05','Sweetlove', 'Steven', 'female', 700000, 53000
);
insert into Customer (fname,lname,company) values('Jacob', 'Denis', 'Bongosatcom'),
('Lameck', 'Goffrey', 'Bongosatcom'),
('Frank', 'Desdery', 'IPPmedia'),
('John', 'Justin', 'Airtel'),
('Vicent', 'Victor', 'Bayport Financila Services'
);
insert into Product values(101, 'D/01', 'S/01', 1,  'Computer', 500000, 5, '2014/03/06'),
(102, 'D/01', 'S/01', 1,  'Laptop', 900000, 15, '2014/03/06'),
(103, 'D/02', 'S/02', 2,  'Toilet Paper',800, 450, '2014/03/06'),
(104, 'D/03', 'S/03', 3,  'Stake Meat', 15000, 150, '2014/03/06'),
(105, 'D/04', 'S/03', 4,  'Poutly Meat', 20000, 200, '2014/03/06'
);