Advertisement
ASiwon

DBMaster_ZD_T7_1

Apr 18th, 2020
795
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Wymiar z danymi dat
  2. CREATE TABLE DBMASTER.DIM_DATES
  3. (
  4.   date_id      NUMBER(6) NOT NULL,
  5.   date_value   DATE NOT NULL,
  6.   day_of_week  NUMBER(1) NOT NULL,
  7.   day_of_month NUMBER(2) NOT NULL,
  8.   day_of_year  NUMBER(3) NOT NULL,
  9.   week         NUMBER(2) NOT NULL,
  10.   MONTH        NUMBER(2) NOT NULL,
  11.   YEAR         NUMBER(4) NOT NULL
  12. );
  13. /
  14. -- Add comments to the columns
  15. COMMENT ON COLUMN DBMASTER.DIM_DATES.date_id IS 'Data - klucz';
  16. /
  17. COMMENT ON COLUMN DBMASTER.DIM_DATES.day_of_week IS 'Numer dnia w tygodniu';
  18. /
  19. COMMENT ON COLUMN DBMASTER.DIM_DATES.day_of_month IS 'Numer dnia w miesiącu';
  20. /
  21. COMMENT ON COLUMN DBMASTER.DIM_DATES.day_of_year IS 'Numer dnia w roku';
  22. /
  23. COMMENT ON COLUMN DBMASTER.DIM_DATES.week IS 'Numer tygodnia w roku';
  24. /
  25. COMMENT ON COLUMN DBMASTER.DIM_DATES.MONTH IS 'Numer miesiąca w roku';
  26. /
  27. COMMENT ON COLUMN DBMASTER.DIM_DATES.YEAR IS 'Rok';
  28. /
  29. -- Create/Recreate primary, unique and foreign key constraints
  30. ALTER TABLE DBMASTER.DIM_DATES ADD CONSTRAINT PK_DIM_DATES PRIMARY KEY (DATE_ID);
  31. /
  32. ALTER TABLE DBMASTER.DIM_DATES ADD CONSTRAINT UQ_DIM_DATE_VALUE UNIQUE (DATE_VALUE)
  33. /
  34.  
  35. -- Wymian z danymi pracowników działu sprzedaży
  36. CREATE TABLE DBMASTER.DIM_SALES_PERSONS (
  37.   person_id         NUMBER(6) NOT NULL,
  38.   person_first_name VARCHAR2(20) NOT NULL,
  39.   person_last_name  VARCHAR2(40) NOT NULL);
  40. /
  41. -- Add comments to the columns
  42. COMMENT ON COLUMN DBMASTER.DIM_SALES_PERSONS.person_id IS 'Identyfikator pracownika działu sprzedaży'
  43. /
  44. COMMENT ON COLUMN DBMASTER.DIM_SALES_PERSONS.person_first_name IS 'Imię'
  45. /
  46. COMMENT ON COLUMN DBMASTER.DIM_SALES_PERSONS.person_last_name IS 'Nazwisko'
  47. /
  48. ALTER TABLE DBMASTER.DIM_SALES_PERSONS ADD CONSTRAINT PK_DIM_SALES_PERSONS PRIMARY KEY (PERSON_ID);
  49. /
  50.  
  51. -- Wymiar z danymi klientów
  52. CREATE TABLE DBMASTER.DIM_CUSTOMERS (
  53.   customer_id   INTEGER NOT NULL,
  54.   customer_name VARCHAR2(100) NOT NULL,
  55.   country_id    NUMBER(3) NOT NULL);
  56. -- Add comments to the columns
  57. COMMENT ON COLUMN DBMASTER.DIM_CUSTOMERS.customer_id IS 'Identyfikator klienta'
  58. /
  59. COMMENT ON COLUMN DBMASTER.DIM_CUSTOMERS.customer_name IS 'Nazwa klienta'
  60. /
  61. COMMENT ON COLUMN DBMASTER.DIM_CUSTOMERS.country_id IS 'Identyfikator kraju klienta'
  62. /
  63. ALTER TABLE DBMASTER.DIM_CUSTOMERS ADD CONSTRAINT PK_DIM_CUSTOMERS PRIMARY KEY (CUSTOMER_ID);
  64. /
  65. ALTER TABLE DBMASTER.DIM_CUSTOMERS
  66.   ADD CONSTRAINT FK_DC_COUNTRY foreign KEY (COUNTRY_ID)
  67.   references DBMASTER.DIM_COUNTRIES (COUNTRY_ID);
  68. /
  69.  
  70. -- Wymiar z danymi krajów
  71. CREATE TABLE DBMASTER.DIM_COUNTRIES (
  72.   country_id   NUMBER(3) NOT NULL,
  73.   iso_code     VARCHAR2(3) NOT NULL,
  74.   name         VARCHAR2(100) NOT NULL,
  75.   continent_id NUMBER(1) NOT NULL);
  76. /
  77. COMMENT ON COLUMN DBMASTER.DIM_COUNTRIES.country_id IS 'Identyfikator kraju'
  78. /
  79. COMMENT ON COLUMN DBMASTER.DIM_COUNTRIES.iso_code IS 'Kod kraju wg normy ISO'
  80. /
  81. COMMENT ON COLUMN DBMASTER.DIM_COUNTRIES.name IS 'Nazwa kraju'
  82. /
  83. COMMENT ON COLUMN DBMASTER.DIM_COUNTRIES.continent_id IS 'Identyfikator kontynentu'
  84. /
  85.  
  86. ALTER TABLE DBMASTER.DIM_COUNTRIES ADD CONSTRAINT PK_DIM_COUNTRIES PRIMARY KEY (COUNTRY_ID)
  87. /
  88. ALTER TABLE DBMASTER.DIM_COUNTRIES
  89.   ADD CONSTRAINT FK_DCN_DIM_CONTINENTS foreign KEY (CONTINENT_ID)
  90.   references DBMASTER.DIM_CONTINENTS (CONTINENT_ID);
  91. /
  92.  
  93. -- Wymiar z danymi kontynentów, do których są przypisane państwa
  94. CREATE TABLE DBMASTER.DIM_CONTINENTS (
  95.   continent_id   NUMBER(1) NOT NULL,
  96.   continent_name VARCHAR2(20) NOT NULL);
  97. /
  98. COMMENT ON COLUMN DBMASTER.DIM_CONTINENTS.continent_id IS 'Identyfikator kontynentu'
  99. /
  100. COMMENT ON COLUMN DBMASTER.DIM_CONTINENTS.continent_name IS 'Nazwa kontynentu'
  101. /
  102. ALTER TABLE DBMASTER.DIM_CONTINENTS ADD CONSTRAINT PK_DIM_CONTINENTS PRIMARY KEY (CONTINENT_ID);
  103. /
  104.  
  105. -- Tabela faktów - zamówienia
  106. CREATE TABLE DBMASTER.FACT_ORDERS(
  107.   id                    INTEGER NOT NULL,
  108.   order_source_id       INTEGER NOT NULL,
  109.   order_date            NUMBER(6),
  110.   ship_date             NUMBER(6),
  111.   delivery_date         NUMBER(6),
  112.   customer_id           INTEGER NOT NULL,
  113.   sales_person_id       NUMBER(6) NOT NULL,
  114.   total_price           NUMBER(8,2) NOT NULL,
  115.   customer_country_id   NUMBER(3) NOT NULL,
  116.   customer_continent_id NUMBER(1) NOT NULL)
  117. /
  118.  
  119. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.id IS 'Klucz wiersza'
  120. /
  121. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.order_source_id IS 'Identyfikator zamówienia w systemie źródłowym'
  122. /
  123. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.order_date IS 'Klucz daty zamówienia'
  124. /
  125. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.ship_date IS 'Klucz daty wysyłki'
  126. /
  127. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.delivery_date IS 'Klucz daty dostawy'
  128. /
  129. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.customer_id IS 'Identyfikator klienta'
  130. /
  131. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.sales_person_id IS 'Identyfikator pracownika działu sprzedaży'
  132. /
  133. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.total_price IS 'Kwota zamówienia'
  134. /
  135. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.customer_country_id IS 'Identyfikator kraju klienta'
  136. /
  137. COMMENT ON COLUMN DBMASTER.FACT_ORDERS.customer_continent_id IS 'Identyfikator kontynentu klienta'
  138. /
  139.  
  140. ALTER TABLE DBMASTER.FACT_ORDERS ADD CONSTRAINT PK_FACT_ORDERS PRIMARY KEY (ID);
  141. /
  142. ALTER TABLE DBMASTER.FACT_ORDERS
  143.   ADD CONSTRAINT FK_CONTINENT_ID foreign KEY (CUSTOMER_CONTINENT_ID)
  144.   references DBMASTER.DIM_CONTINENTS (CONTINENT_ID);
  145. /
  146. ALTER TABLE DBMASTER.FACT_ORDERS
  147.   ADD CONSTRAINT FK_COUNTRY_ID foreign KEY (CUSTOMER_COUNTRY_ID)
  148.   references DBMASTER.DIM_COUNTRIES (COUNTRY_ID);
  149. /
  150. ALTER TABLE DBMASTER.FACT_ORDERS
  151.   ADD CONSTRAINT FK_CUSTOMER_ID foreign KEY (CUSTOMER_ID)
  152.   references DBMASTER.DIM_CUSTOMERS (CUSTOMER_ID);
  153. /
  154. ALTER TABLE DBMASTER.FACT_ORDERS
  155.   ADD CONSTRAINT FK_DELIVERY_DATE foreign KEY (DELIVERY_DATE)
  156.   references DBMASTER.DIM_DATES (DATE_ID);
  157. /
  158. ALTER TABLE DBMASTER.FACT_ORDERS
  159.   ADD CONSTRAINT FK_ORDER_DATE foreign KEY (ORDER_DATE)
  160.   references DBMASTER.DIM_DATES (DATE_ID);
  161. /
  162. ALTER TABLE DBMASTER.FACT_ORDERS
  163.   ADD CONSTRAINT FK_SALES_PERSON_ID foreign KEY (SALES_PERSON_ID)
  164.   references DBMASTER.DIM_SALES_PERSONS (PERSON_ID);
  165. /
  166. ALTER TABLE DBMASTER.FACT_ORDERS
  167.   ADD CONSTRAINT FK_SHIP_DATE foreign KEY (SHIP_DATE)
  168.   references DBMASTER.DIM_DATES (DATE_ID);
  169. /
Advertisement
RAW Paste Data Copied
Advertisement