Instalar BD de Prueba: ClassicModels

Seguiremos los siguientes pasos para instalarnos la base de datos de prueba ClassicModels:

1. Descarga la base de datos haciendo clic aquí.
2. Descomprime el fichero
3. Entra en la carpeta ClassicModels/mysql donde encontrarás un par de scripts y una carpeta con los ficheros de datos
4. Estos dos ficheros y esta carpeta debes moverla temporalmente al directorio en el que esté instalado MySQL, más en concreto al mysql/bin
5. Entra en la consola de MySQL
6. Crea la base de datos utilizando la siguiente orden: create database ClassicModels;
7. Utilízala: use ClassicModels;
8. Ejecuta el script de creación de tablas: source create_classicmodels.sql;
9. Gracias al anterior script, tendremos creadas todas las tablas. Ahora ejecutaremos el script de carga de datos: source load_classicmodels.sql; Gracias al cual tendremos todas las tablas rellenas.

Script de creación de tablas: CREATE_CLASSICMODELS.SQL

/******************************************************************************
  * Copyright (c) 2005 Actuate Corporation.
  * All rights reserved. This file and the accompanying materials
  * are made available under the terms of the Eclipse Public License v1.0
  * which accompanies this distribution, and is available at
  * http://www.eclipse.org/legal/epl-v10.html  *
  * Contributors:
  *  Actuate CorporatioN  - initial implementation  *
  * Classic Models Inc. sample database developed as part of the
  * Eclipse BIRT Project. For more information, see http:\\www.eclipse.org\birt  *
  *******************************************************************************/
/* Recommended DATABASE name is classicmodels. */
/* CREATE DATABASE classicmodels; */ /* USE classicmodels; */
/* DROP the existing tables. Comment this out if it is not needed. */
DROP TABLE Customers;
 DROP TABLE Employees;
 DROP TABLE Offices;
 DROP TABLE OrderDetails;
 DROP TABLE Orders;
 DROP TABLE Payments;
 DROP TABLE Products;
 DROP TABLE ProductLines;
/* Create the full set of Classic Models Tables */
CREATE TABLE Customers (
   customerNumber INTEGER NOT NULL,
   customerName VARCHAR(50) NOT NULL,
   contactLastName VARCHAR(50) NOT NULL,
   contactFirstName VARCHAR(50) NOT NULL,
   phone VARCHAR(50) NOT NULL,
   addressLine1 VARCHAR(50) NOT NULL,
   addressLine2 VARCHAR(50) NULL,
   city VARCHAR(50) NOT NULL,
   state VARCHAR(50) NULL,
   postalCode VARCHAR(15) NULL,
   country VARCHAR(50) NOT NULL,
   salesRepEmployeeNumber INTEGER NULL,
   creditLimit DOUBLE NULL,
   PRIMARY KEY (customerNumber) );
CREATE TABLE Employees (
   employeeNumber INTEGER NOT NULL,
   lastName VARCHAR(50) NOT NULL,
   firstName VARCHAR(50) NOT NULL,
   extension VARCHAR(10) NOT NULL,
   email VARCHAR(100) NOT NULL,
   officeCode VARCHAR(10) NOT NULL,
   reportsTo INTEGER NULL,
   jobTitle VARCHAR(50) NOT NULL,
   PRIMARY KEY (employeeNumber) );
CREATE TABLE Offices (
   officeCode VARCHAR(10) NOT NULL,
   city VARCHAR(50) NOT NULL,
   phone VARCHAR(50) NOT NULL,
   addressLine1 VARCHAR(50) NOT NULL,
   addressLine2 VARCHAR(50) NULL,
   state VARCHAR(50) NULL,
   country VARCHAR(50) NOT NULL,
   postalCode VARCHAR(15) NOT NULL,
   territory VARCHAR(10) NOT NULL,
   PRIMARY KEY (officeCode) );
CREATE TABLE OrderDetails (
   orderNumber INTEGER NOT NULL,
   productCode VARCHAR(15) NOT NULL,
   quantityOrdered INTEGER NOT NULL,
   priceEach DOUBLE NOT NULL,
   orderLineNumber SMALLINT NOT NULL,
   PRIMARY KEY (orderNumber, productCode) );
CREATE TABLE Orders (
   orderNumber INTEGER NOT NULL,
   orderDate DATETIME NOT NULL,
   requiredDate DATETIME NOT NULL,
   shippedDate DATETIME NULL,
   status VARCHAR(15) NOT NULL,
   comments TEXT NULL,
   customerNumber INTEGER NOT NULL,
   PRIMARY KEY (orderNumber) );
CREATE TABLE Payments (
   customerNumber INTEGER NOT NULL,
    checkNumber VARCHAR(50) NOT NULL,
   paymentDate DATETIME NOT NULL,
   amount DOUBLE NOT NULL,
   PRIMARY KEY (customerNumber, checkNumber) );
CREATE TABLE Products (
   productCode VARCHAR(15) NOT NULL,
   productName VARCHAR(70) NOT NULL,
   productLine VARCHAR(50) NOT NULL,
   productScale VARCHAR(10) NOT NULL,
   productVendor VARCHAR(50) NOT NULL,
   productDescription TEXT NOT NULL,
   quantityInStock SMALLINT NOT NULL,
   buyPrice DOUBLE NOT NULL,
   MSRP DOUBLE NOT NULL,
   PRIMARY KEY (productCode) );
CREATE TABLE ProductLines(
   productLine VARCHAR(50) NOT NULL,
   textDescription VARCHAR(4000) NULL,
   htmlDescription MEDIUMTEXT NULL,
   image MEDIUMBLOB NULL,
   PRIMARY KEY (productLine) );

 

Script de carga de datos: LOAD_CLASSICMODELS.SQL

 

/******************************************************************************
  * Copyright (c) 2005 Actuate Corporation.
  * All rights reserved. This file and the accompanying materials
  * are made available under the terms of the Eclipse Public License v1.0
  * which accompanies this distribution, and is available at
  * http://www.eclipse.org/legal/epl-v10.html  *
  * Contributors:  *  Actuate Corporation  - initial implementation  *
  * Classic Models Inc. sample database developed as part of the
  * Eclipse BIRT Project. For more information, see http:\\www.eclipse.org\birt  *
  *******************************************************************************/
/* Loads the Classic Models tables using the MySQL LOAD command */
/* Preparing the load files for importing. Input file requirements:
      - Column order in the file must be the same as the columns in the table
      - Columns are Comma delimited
      - Text is quoted (")
      - NULL columns must be ,NULL,  ( ,, is not acceptable)
      - Dates must be in YYYY-MM-DDD format
   Input files expected in the datafiles direcory, parallel to this script. */
/* First make sure all the tables are empty */
DELETE FROM Customers;
 DELETE FROM Employees;
 DELETE FROM Offices;
 DELETE FROM OrderDetails;
 DELETE FROM Orders;
 DELETE FROM Payments;
 DELETE FROM Products;
 DELETE FROM ProductLines;
# Load records into the tables. There should be no warnings.
LOAD DATA LOCAL INFILE '.\\datafiles\\customers.txt' INTO TABLE Customers
           FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
SHOW WARNINGS LIMIT 10;
LOAD DATA LOCAL INFILE '.\\datafiles\\employees.txt' INTO TABLE Employees
           FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
SHOW WARNINGS LIMIT 10;
LOAD DATA LOCAL INFILE '.\\datafiles\\offices.txt' INTO TABLE Offices
           FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
SHOW WARNINGS LIMIT 10;
LOAD DATA LOCAL INFILE '.\\datafiles\\orderdetails.txt' INTO TABLE OrderDetails
           FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
SHOW WARNINGS LIMIT 10;
LOAD DATA LOCAL INFILE '.\\datafiles\\orders.txt' INTO TABLE Orders
           FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
SHOW WARNINGS LIMIT 10;
LOAD DATA LOCAL INFILE '.\\datafiles\\payments.txt' INTO TABLE Payments
           FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
SHOW WARNINGS LIMIT 10;
LOAD DATA LOCAL INFILE '.\\datafiles\\products.txt' INTO TABLE Products
           FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
SHOW WARNINGS LIMIT 10;
LOAD DATA LOCAL INFILE '.\\datafiles\\ProductLines.txt' INTO TABLE ProductLines
           FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
           (productLine , textDescription);
SHOW WARNINGS LIMIT 10;