Seguimos con esta serie de tutoriales, y en esta parte vamos a crear la conexión del usuario atpc_user de nuestra base de datos autónoma al SQL Developer local.
En el primer tutorial aprendimos como conectarnos con el usuario ADMIN de la base de datos autónoma desde el SQL Developer local.
Ver el tutorial 1 AQUI
Ingresamos al SQL Developer local:
Ingresamos los datos de la conexión del mismo modo como lo hicimos en el tutorial 1, y en este caso el usuario será: atpc_user y la password que ingresamos cuando creamos el usuario.
Crear Tablas SH en nuestra base de datos autónoma
Ahora que estamos conectados con nuestra base de datos autónoma usando el SQL Developer podemos definir la declaración CREATE TABLE para crear las tablas SH (las tablas de ventas del esquema de ejemplo de Oracle).
Copiamos el siguiente código en la consola del SQL Developer.
Debajo también ingreso el código, por si el link se rompe.
CREATE TABLE atpc_user.sales (
prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id NUMBER NOT NULL,
promo_id NUMBER NOT NULL,
quantity_sold NUMBER(10,2) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL);
CREATE TABLE atpc_user.salestemp (
prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id NUMBER NOT NULL,
promo_id NUMBER NOT NULL,
quantity_sold NUMBER(10,2) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL,
unit_cost NUMBER(10,2) ,
unit_price NUMBER(10,2) );
CREATE TABLE atpc_user.costs (
prod_id NUMBER NOT NULL,
time_id DATE NOT NULL,
promo_id NUMBER NOT NULL,
channel_id NUMBER NOT NULL,
unit_cost NUMBER(10,2) NOT NULL,
unit_price NUMBER(10,2) NOT NULL);
CREATE TABLE atpc_user.times (
time_id DATE NOT NULL,
day_name VARCHAR2(9) NOT NULL,
day_number_in_week NUMBER(1) NOT NULL,
day_number_in_month NUMBER(2) NOT NULL,
calendar_week_number NUMBER(2) NOT NULL,
fiscal_week_number NUMBER(2) NOT NULL,
week_ending_day DATE NOT NULL,
week_ending_day_id NUMBER NOT NULL,
calendar_month_number NUMBER(2) NOT NULL,
fiscal_month_number NUMBER(2) NOT NULL,
calendar_month_desc VARCHAR2(8) NOT NULL,
calendar_month_id NUMBER NOT NULL,
fiscal_month_desc VARCHAR2(8) NOT NULL,
fiscal_month_id NUMBER NOT NULL,
days_in_cal_month NUMBER NOT NULL,
days_in_fis_month NUMBER NOT NULL,
end_of_cal_month DATE NOT NULL,
end_of_fis_month DATE NOT NULL,
calendar_month_name VARCHAR2(9) NOT NULL,
fiscal_month_name VARCHAR2(9) NOT NULL,
calendar_quarter_desc CHAR(7) NOT NULL,
calendar_quarter_id NUMBER NOT NULL,
fiscal_quarter_desc CHAR(7) NOT NULL,
fiscal_quarter_id NUMBER NOT NULL,
days_in_cal_quarter NUMBER NOT NULL,
days_in_fis_quarter NUMBER NOT NULL,
end_of_cal_quarter DATE NOT NULL,
end_of_fis_quarter DATE NOT NULL,
calendar_quarter_number NUMBER(1) NOT NULL,
fiscal_quarter_number NUMBER(1) NOT NULL,
calendar_year NUMBER(4) NOT NULL,
calendar_year_id NUMBER NOT NULL,
fiscal_year NUMBER(4) NOT NULL,
fiscal_year_id NUMBER NOT NULL,
days_in_cal_year NUMBER NOT NULL,
days_in_fis_year NUMBER NOT NULL,
end_of_cal_year DATE NOT NULL,
end_of_fis_year DATE NOT NULL );
CREATE TABLE atpc_user.products (
prod_id NUMBER(6) NOT NULL,
prod_name VARCHAR2(50) NOT NULL,
prod_desc VARCHAR2(4000) NOT NULL,
prod_subcategory VARCHAR2(50) NOT NULL,
prod_subcategory_id NUMBER NOT NULL,
prod_subcategory_desc VARCHAR2(2000) NOT NULL,
prod_category VARCHAR2(50) NOT NULL,
prod_category_id NUMBER NOT NULL,
prod_category_desc VARCHAR2(2000) NOT NULL,
prod_weight_class NUMBER(3) NOT NULL,
prod_unit_of_measure VARCHAR2(20) ,
prod_pack_size VARCHAR2(30) NOT NULL,
supplier_id NUMBER(6) NOT NULL,
prod_status VARCHAR2(20) NOT NULL,
prod_list_price NUMBER(8,2) NOT NULL,
prod_min_price NUMBER(8,2) NOT NULL,
prod_total VARCHAR2(13) NOT NULL,
prod_total_id NUMBER NOT NULL,
prod_src_id NUMBER ,
prod_eff_from DATE ,
prod_eff_to DATE ,
prod_valid VARCHAR2(1) );
CREATE TABLE atpc_user.channels (
channel_id NUMBER NOT NULL,
channel_desc VARCHAR2(20) NOT NULL,
channel_class VARCHAR2(20) NOT NULL,
channel_class_id NUMBER NOT NULL,
channel_total VARCHAR2(13) NOT NULL,
channel_total_id NUMBER NOT NULL);
CREATE TABLE atpc_user.promotions (
promo_id NUMBER(6) NOT NULL,
promo_name VARCHAR2(30) NOT NULL,
promo_subcategory VARCHAR2(30) NOT NULL,
promo_subcategory_id NUMBER NOT NULL,
promo_category VARCHAR2(30) NOT NULL,
promo_category_id NUMBER NOT NULL,
promo_cost NUMBER(10,2) NOT NULL,
promo_begin_date DATE NOT NULL,
promo_end_date DATE NOT NULL,
promo_total VARCHAR2(15) NOT NULL,
promo_total_id NUMBER NOT NULL);
CREATE TABLE atpc_user.customers (
cust_id NUMBER NOT NULL,
cust_first_name VARCHAR2(20) NOT NULL,
cust_last_name VARCHAR2(40) NOT NULL,
cust_gender CHAR(1) NOT NULL,
cust_year_of_birth NUMBER(4) NOT NULL,
cust_marital_status VARCHAR2(20) ,
cust_street_address VARCHAR2(40) NOT NULL,
cust_postal_code VARCHAR2(10) NOT NULL,
cust_city VARCHAR2(30) NOT NULL,
cust_city_id NUMBER NOT NULL,
cust_state_province VARCHAR2(40) NOT NULL,
cust_state_province_id NUMBER NOT NULL,
country_id NUMBER NOT NULL,
cust_main_phone_number VARCHAR2(25) NOT NULL,
cust_income_level VARCHAR2(30) ,
cust_credit_limit NUMBER ,
cust_email VARCHAR2(50) ,
cust_total VARCHAR2(14) NOT NULL,
cust_total_id NUMBER NOT NULL,
cust_src_id NUMBER ,
cust_eff_from DATE ,
cust_eff_to DATE ,
cust_valid VARCHAR2(1) );
CREATE TABLE atpc_user.countries (
country_id NUMBER NOT NULL,
country_iso_code CHAR(2) NOT NULL,
country_name VARCHAR2(40) NOT NULL,
country_subregion VARCHAR2(30) NOT NULL,
country_subregion_id NUMBER NOT NULL,
country_region VARCHAR2(20) NOT NULL,
country_region_id NUMBER NOT NULL,
country_total VARCHAR2(11) NOT NULL,
country_total_id NUMBER NOT NULL,
country_name_hist VARCHAR2(40));
CREATE TABLE atpc_user.supplementary_demographics
( CUST_ID NUMBER not null,
EDUCATION VARCHAR2(21),
OCCUPATION VARCHAR2(21),
HOUSEHOLD_SIZE VARCHAR2(21),
YRS_RESIDENCE NUMBER,
AFFINITY_CARD NUMBER(10),
bulk_pack_diskettes NUMBER(10),
flat_panel_monitor NUMBER(10),
home_theater_package NUMBER(10),
bookkeeping_application NUMBER(10),
printer_supplies NUMBER(10),
y_box_games NUMBER(10),
os_doc_set_kanji NUMBER(10),
COMMENTS VARCHAR2(4000));
ALTER TABLE atpc_user.promotions
ADD CONSTRAINT promo_pk
PRIMARY KEY (promo_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.sales
ADD CONSTRAINT sales_promo_fk
FOREIGN KEY (promo_id) REFERENCES atpc_user.promotions (promo_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.costs
ADD CONSTRAINT costs_promo_fk
FOREIGN KEY (promo_id) REFERENCES atpc_user.promotions (promo_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.customers
ADD CONSTRAINT customers_pk
PRIMARY KEY (cust_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.sales
ADD CONSTRAINT sales_customer_fk
FOREIGN KEY (cust_id) REFERENCES atpc_user.customers (cust_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.products
ADD CONSTRAINT products_pk
PRIMARY KEY (prod_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.sales
ADD CONSTRAINT sales_product_fk
FOREIGN KEY (prod_id) REFERENCES atpc_user.products (prod_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.costs
ADD CONSTRAINT costs_product_fk
FOREIGN KEY (prod_id) REFERENCES atpc_user.products (prod_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.times
ADD CONSTRAINT times_pk
PRIMARY KEY (time_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.sales
ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES atpc_user.times (time_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.costs
ADD CONSTRAINT costs_time_fk
FOREIGN KEY (time_id) REFERENCES atpc_user.times (time_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.channels
ADD CONSTRAINT channels_pk
PRIMARY KEY (channel_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.sales
ADD CONSTRAINT sales_channel_fk
FOREIGN KEY (channel_id) REFERENCES atpc_user.channels (channel_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.costs
ADD CONSTRAINT costs_channel_fk
FOREIGN KEY (channel_id) REFERENCES atpc_user.channels (channel_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.countries
ADD CONSTRAINT countries_pk
PRIMARY KEY (country_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.customers
ADD CONSTRAINT customers_country_fk
FOREIGN KEY (country_id) REFERENCES atpc_user.countries (country_id)
RELY DISABLE NOVALIDATE;
ALTER TABLE atpc_user.supplementary_demographics
ADD CONSTRAINT supp_demo_pk
PRIMARY KEY (cust_id)
RELY DISABLE NOVALIDATE;
Hacemos clic en el icono para ejecutar el script.
Examinamos las tablas SH que hemos creado
El panel Salida de SQL Developer Script muestra las tablas que se han creado en el usuario llamado atpc_user.
También podemos ver las tablas creadas desde la ventana de conexión del SQL Developer.
Examinemos las columnas de la tabla CHANNELS.
Si hacemos clic en la ficha Data, podemos ver que no hay datos cargados en la tabla.
En el siguiente tutorial vamos a cargar los datos en las tablas SH.
Te espero!