Both Oracle and MySQL provide a number of sample schemas so users can build different schema environments and load them with data. So I thought I'd get one of the Oracle sample schemas and go through the exercise of converting it to a MySQL schema. I could have gone in either direction, since this is more of a MySQL blog I made MySQL the destination. Before I start let me introduce some of the Oracle and MySQL sample schemas.
Oracle Sample Schemas:
- Human Resources: (HR) is a simple schema containing tables with basic primary and foreign key relationships such as: employees, departments, jobs, locations, regions, countrys, jobs, etc. This can be extended to support Oracle Internet Directory demos.
- Order Entry (OE) is a more complex set of tables demonstrating the different datatypes supported by Oracle. Tables dealing with products, sales, customers, etc. are used in this schema.
- Online Catalog (OC) is a supporting schema that demonstrates object-relational database objects built inside the OE schema.
- Product Media (PM) is a schema that supports multimedia datatypes. Audio, video, images, large text data are all used in this schema.
- Queued Shipping (QS) to demonstrate Oracle Advanced Queuing capabilities. Advanced Queuing and XML are used in this schema.
- Sales History (SH) is a schema supporting a large number of tables. Features like partitioning and Oracle analytics can be used with this schema.
MySQL Sample Schemas:
- World is a basic schema using three tables City, Country and CountryLanguages that use MyISAM tables.
- Sakila is a schema that adds additional complexity using an online DVD store to demonstrate more of the MySQL 5.1 features.
- Employees is a schema with a much larger data set containing employees, deparments, salaries, titles, etc that are very intuitive to work with. There are approximately 4 million records to work with. Different storage engines and partitioning are often benchmarked using this schema.
Here is a summary of changes made to convert the Oracle schema script to a MySQL schema script. The goal here is to provide a level of understanding of converting a schema.
- Comment out SQL*Plus specific commands.
- Change REM to --
- Change VARCHAR2 to VARCHAR
- Change NUMBER to INT
- Change NUMBER(x,x) to DECIMAL(x,x)
- Change CONSTRAINT keyword Syntax
- For InnoDB tables, define PRIMARY KEY as part of the CREATE TABLE command and not a separate CREATE INDEX command.
- Make sure tables are created as InnoDB. set storage_engine=innodb;
- Modify constraint syntax for FOREIGN KEY definitions.
- Fully quality PRIMARY KEY column in FOREIGN KEY constraint.
- Remove READ ONLY clause with CREATE VIEW.
Converting the Oracle HR sample schema to MySQL
I decided the Oracle HR schema would be a good sample schema to convert to a MySQL schema to demonstrate things to consider in creating schema scripts. I started with the cre_hr.sql script and converted it to a cre_hr_mysql.sql script. The Oracle cre_hr.sql script I started with can be found at:
- http://www.oracle.com/technology/obe/sqldev_obe/osdm/files/hr_cre.sql
MySQL equivalent of the Oracle HR schema script
Here is the corresponding MySQL create script. The only thing I did not address were the sequential incremental values used with Oracle sequences. I included the Oracle CREATE SEQUENCE commands in so it would show the adjustments I made to use autoincrement but also show that the incremental values would need to be addressed. A little more work needs to be done here to verify the data loads would create the same values in an Oracle environment. As I mentioned my main goal was to give someone a feel for the code differences in creating a schema between Oracle and MySQL.
-- hr_cre_mysql.sql
/*
Notes of changes:
Comment out SQL*Plus specific commands.
Change REM to --
Change VARCHAR to VARCHAR
Change INT to INT
Change INT(x,x) to DECIMAL(x,x)
Change CONSTRAINT keyword Syntax
For InnoDB tables, define PRIMARY KEY as part of the CREATE TABLE command and not a separate CREATE INDEX command.
Make sure tables are created as InnoDB.
set storage_engine=innodb;
Modify constraint syntax for FOREIGN KEY definitions.
Fully quality PRIMARY KEY column in FOREIGN KEY constraint.
Remove READ ONLY clause with CREATE VIEW.
*/
DROP DATABASE IF EXISTS hr;
CREATE DATABASE hr;
USE hr;
CREATE TABLE regions
( region_id INT NOT NULL
, region_name varchar(25),
primary key (region_id)
);
CREATE TABLE countries
( country_id CHAR(2) NOT NULL
, country_name varchar(40)
, region_id INT
, PRIMARY KEY (country_id)
) ;
ALTER TABLE countries
ADD (FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;
CREATE TABLE locations
( location_id INT(4)
, street_address varchar(40)
, postal_code varchar(12)
, city varchar(30) NOT NULL
, state_province varchar(25)
, country_id CHAR(2)
, PRIMARY KEY (location_id)
) auto_increment=3300;
/*
CREATE SEQUENCE locations_seq\
START WITH 3300\
INCREMENT BY 100\
MAXVALUE 9900\
NOCACHE\
NOCYCLE;\
*/
ALTER TABLE locations
ADD ( FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;
CREATE TABLE departments
( department_id INT(4)
, department_name varchar(30) NOT NULL
, manager_id INT(6)
, location_id INT(4)
, PRIMARY KEY (department_id)
) auto_increment=280;
ALTER TABLE departments
ADD ( FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;
/*
CREATE SEQUENCE departments_seq\
START WITH 280\
INCREMENT BY 10\
MAXVALUE 9990\
NOCACHE\
NOCYCLE;\
*/
CREATE TABLE jobs
( job_id VARCHAR(10)
, job_title VARCHAR(35) NOT NULL
, min_salary INT(6)
, max_salary INT(6)
, PRIMARY KEY (job_id)
) ;
CREATE TABLE employees
( employee_id INT(6)
, first_name varchar(20)
, last_name varchar(25) NOT NULL
, email varchar(25) NOT NULL
, phone_INT varchar(20)
, hire_date DATE NOT NULL
, job_id varchar(10) NOT NULL
, salary DECIMAL(8,2)
, commission_pct DECIMAL(2,2)
, manager_id INT(6)
, department_id INT(4)
, UNIQUE KEY (email)
, PRIMARY KEY (employee_id)
) auto_increment=207;
DELIMITER //
CREATE TRIGGER emp_sal_min BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF (salary <= 0 ) THEN call unknown(); END IF; END// DELIMITER ; ALTER TABLE employees ADD ( FOREIGN KEY (department_id) REFERENCES departments(department_id)); ALTER TABLE employees ADD ( FOREIGN KEY (job_id) REFERENCES jobs (job_id)); ALTER TABLE employees ADD ( FOREIGN KEY (manager_id) REFERENCES employees(employee_id)) ; ALTER TABLE departments ADD ( FOREIGN KEY (manager_id) REFERENCES employees (employee_id)) ; /* CREATE SEQUENCE employees_seq\ START WITH 207\ INCREMENT BY 1\ NOCACHE\ NOCYCLE;\ */ CREATE TABLE job_history ( employee_id INT(6) NOT NULL , start_date DATE NOT NULL , end_date DATE NOT NULL , job_id VARCHAR(10) NOT NULL , department_id INT(4) , PRIMARY KEY (employee_id, start_date) ) ; DELIMITER // CREATE TRIGGER jobhist_dates BEFORE INSERT ON job_history FOR EACH ROW BEGIN IF (end_date > start_date ) THEN call unknown();
END IF;
END//
DELIMITER ;
ALTER TABLE job_history
ADD (FOREIGN KEY (job_id)
REFERENCES jobs(job_id));
ALTER TABLE job_history
ADD ( FOREIGN KEY (employee_id)
REFERENCES employees(employee_id));
ALTER TABLE job_history
ADD (FOREIGN KEY (department_id)
REFERENCES departments(department_id)) ;
CREATE OR REPLACE VIEW emp_details_view
(employee_id, job_id, manager_id,
department_id, location_id, country_id,
first_name, last_name, salary,
commission_pct, department_name, job_title,
city, state_province, country_name, region_name)
AS SELECT
e.employee_id, e.job_id, e.manager_id,
e.department_id, d.location_id, l.country_id,
e.first_name, e.last_name, e.salary,
e.commission_pct, d.department_name, j.job_title,
l.city, l.state_province, c.country_name, r.region_name
FROM
employees e, departments d, jobs j,locations l,
countries c, regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id ;
