Hurtownie Danych:
”TRANSPORT DANYCH”

Tworzenie schematu bazy danych.

Na podstawie pliku tekstowego etl_pattern.txt stwórz skrypt elt_tables.sql, tworzący schemat bazy danych. Zachowaj następujące standardy:

1. Polecenia CREATE TABLE poprzedź poleceniami DROP TABLE dla umożliwienia powtarzalności skryptu.

2. Wszystkie atrybuty podlegające analizie uzupełnij kwalifikatorem NOT NULL.

3. Nie twórz kluczy głównych i obcych.

Stworzony schemat powinien wyglądać zgodnie z poniższym rysunkiem
(z pominięciem ograniczeń kluczy głównych i obcych):

Wczytywanie plików danych tekstowych.

1. Plik tekstowy z separatorem kolumn danych.

Przy pomocy programu SqlLoader wczytaj dane z pliku tekstowego etl_sales.dat do tabeli etl_sales. Fragmenty struktur danych zostały przedstawione na rysunkach:

Tabela:

prod_id NUMBER(6) 'FK to the etl_products table' , cust_id NUMBER 'FK to the etl_customers table' , time_id DATE 'FK to the etl_times table' , channel_id CHAR(1) 'FK to the etl_channels table' , promo_id NUMBER(6) 'promotion identifier' , quantity_sold NUMBER(3) 'product quantity sold with the transaction' , amount NUMBER(10,2) 'invoiced amount to the customer' , cost NUMBER(10,2) 'costs involved with the transaction'

Plik tekstowy:

9465|29700|1-01-1998|S||29|2291.00|835.20| 46360|3380|1-01-1998|S||6|114.00| 54.00| 4105|67830|1-01-1998|C||7|553.00|252.00| 85|179330|1-01-1998|T|950068|0| 0.00| 0.00| 7145|127520|1-01-1998|P||3|195.00| 55.80|

2. Plik tekstowy z formatem kolumnowym.

Przy pomocy programu SqlLoader wczytaj dane z pliku tekstowego etl_customers.dat do tabeli etl_customers. Fragmenty struktur danych zostały przedstawione na rysunkach:

Tabela:

cust_id NUMBER 'primary key' , cust_first_name VARCHAR2(20) , cust_last_name VARCHAR2(40) , cust_gender CHAR(1) 'low cardinality attribute' , cust_year_of_birth NUMBER(4) , cust_marital_status VARCHAR2(20) 'low cardinality attribute' , cust_street_address VARCHAR2(40) , cust_postal_code VARCHAR2(10) , cust_city VARCHAR2(30) , cust_state_province VARCHAR2(40) , country_id CHAR(2) 'foreign key to the etl_countries table , cust_main_phone_number VARCHAR2(25) , cust_income_level VARCHAR2(30) , cust_credit_limit NUMBER ,cust_email VARCHAR2(30)

Plik tekstowy:

Tabela zewnętrzna typu SqlLoader.

1. Stwórz tabelę zewnętrzną etl_prod_src , połączoną z plikiem tekstowym etl_products.dat, który znajduje się w katalogu logicznym etl_ext_source serwera SOIL_FESTIVITIES. Katalog jest widoczny w sieci za pośrednictwem serwera samba pod nazwą \\soil_festivitie\oracle.

2. Do tabeli etl_products skopiuj dane z tabeli zewnętrznej etl_prod_src.

Tabela:

prod_id NUMBER(6) , prod_name VARCHAR2(50) , prod_desc VARCHAR2(4000) , prod_subcategory VARCHAR2(50) , prod_subcat_desc VARCHAR2(2000) , prod_category VARCHAR2(50) , prod_cat_desc VARCHAR2(2000) , prod_weight_class NUMBER(2) , prod_unit_of_measure VARCHAR2(20) , prod_pack_size VARCHAR2(30) , supplier_id NUMBER(6) , prod_status VARCHAR2(20) , prod_list_price NUMBER(8,2) , prod_min_price NUMBER(8,2)

Plik tekstowy:

Tabela Zewnętrzna typu DataPump.

1. Przy pomocy klienta SQL połącz się z serwerem o nazwie IMEHEI.EARTHSEA.COM, widocznym w sieci Internet pod adresem (IP: 89.67.9.216), na którym znajduje się instancja bazy ORCL, a w niej schemat wsisiz z hasłem bartek.

2. Stwórz tabelę zewnętrzną etl_login_src typu DataPump i wyeksportuj dane z tabeli countries do pliku etl_login_src.dmp w katalogu o nazwie logicznej etl_ext_source.

3. Połącz się do serwera FTP pod adresem (IP: 89.67.9.216) wykorzystując login wsisiz z hasłem bartek i skopiuj plik etl_login_src.dmp do katalogu logicznego etl_ext_source serwera SOIL_FESTIVITIES, który jest widoczny w sieci za pośrednictwem serwera samba pod nazwą \\soil-festivitie\oracle.

4. Stwórz tabelę zewnętrzną etl_countries_ext, połączoną ze skopiowanym właśnie plikiem etl_login_src.dmp i przenieś dane do tabeli etl_countries.

Brama Homogeniczna - rozproszona transakcja.

5. W swoim schemacie na serwerze SOIL_FESTIVITIES stwórz połączenie homogeniczne (DATABASE_LINK) do serwera o nazwie VETCH.EARTHSEA.COM, widocznego w sieci Internet pod adresem(IP: 89.67.9.216), na którym znajduje się instancja bazy ORCL, a w niej schemat wsisiz z hasłem bartek.

6. Wypełnij swoją tabelę etl_promotions zawartością tabeli promotions na serwerze VETCH.

Tabela etl_promotions:

promo_id NUMBER(6) , promo_name VARCHAR2(20) , promo_subcategory VARCHAR2(30) , promo_category VARCHAR2(30) , promo_cost NUMBER(10,2) , promo_begin_date DATE , promo_end_date DATE

Brama Heterogenicza - ODBC - Ms Access.

1. Na własnym komputerze stwórz źródło danych ODBC, oparte na pliku etl_odbc_msacc.mdb.

2. Skonfiguruj bramę heterogeniczną lokalnej instancji Oracle własnego komputera.

3. Skonfiguruj lokalny program listenera Oracle własnego komputera do nasłuchu na bramie heterogenicznej.

4. Przyłącz zewnętrzne źródło danych (lokalny komputer) do swojego schematu na serwerze SOIL_FESTIVITIES.

5. Wypełnij tabelę etl_channels danymi ze źródła etl_odbc_msacc.mdb:

Tabela etl_channels:

channel_id CHAR(1) , channel_desc VARCHAR2(20) , channel_class VARCHAR2(20)

Uzupełnienie kluczy i wiązań tabel.

Stwórz skrypt elt_tables_constraints.sql do generacji kluczy głównych i obcych zbudowanego schematu.

Przykład skryptu generacji klucza głównego:

ALTER TABLE etl_countries DROP CONSTRAINT countries_pk CASCADE ; DROP INDEX countries_pk ; CREATE UNIQUE INDEX countries_pk ON etl_countries (country_id) ; ALTER TABLE etl_countries ADD CONSTRAINT countries_pk PRIMARY KEY (country_id) ; * * * ALTER TABLE etl_customers DROP CONSTRAINT cust_coun_fk ; ALTER TABLE etl_customers ADD CONSTRAINT cust_coun_fk FOREIGN KEY (country_id) REFERENCES etl_countries ;

laboratorium: Hurtownie Danych Transport Danych

Rafał Kowalski Strona 9 2014-03-02

Brama Heterogeniczna

- ODBC - Ms Access.

Plik tekstowy z

separatorem

Plik tekstowy z formatem kolumnowym

Tabela zewnętrzna

typu SqlLoader

Tabela Zewnętrzna typu DataPump

Brama Homogeniczna

- rozproszona transakcja

---