Guest User

Untitled

a guest
Dec 11th, 2017
145
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* ************************************************** */
  2. /* This script creates (heaped versions of) tables    */
  3. /* mother and child for use in the exercises on       */
  4. /* application design                                 */
  5. /* distribution of data in the tables                 */
  6. /*                                                    */
  7. /*                      Bo Brunsgaard, October 2016   */
  8. /*                             Revised October 2017   */
  9. /* ************************************************** */
  10.  
  11.  
  12. /*DROP INDEX IXMOTHERIDCHILD;
  13. DROP INDEX IXHAIRCOLORMOTHER;
  14. DROP INDEX IXCHILDYEARBORN;
  15.  
  16. DROP INDEX idxChildMotherId;*/
  17.  
  18. ALTER SYSTEM FLUSH SHARED_POOL;
  19.  
  20. DROP TABLE child purge
  21. ;
  22. DROP TABLE mother purge
  23. ;
  24. CREATE TABLE mother
  25. (
  26.     id       NUMBER(10,0)
  27.              NOT NULL
  28.              CONSTRAINT coPKMother
  29.                PRIMARY KEY
  30.                USING INDEX
  31.                
  32.   , firstName VARCHAR(50)
  33.               NOT NULL
  34.              
  35.   , lastName  VARCHAR(50)
  36.               NOT NULL
  37.              
  38.   , gender    CHAR(01)
  39.               NOT NULL
  40.               CONSTRAINT coCHMotherGender
  41.                 CHECK (gender = 'F')
  42.                
  43.   , dateBorn  DATE
  44.               NOT NULL
  45.              
  46.  , hairColor   VARCHAR2(20)
  47.                NOT NULL
  48.                
  49.  , meaninglessData CHAR(100)
  50.                    NOT NULL
  51. )
  52. ;
  53.  
  54. CREATE TABLE child
  55.  (
  56.      id      NUMBER(10,0)
  57.              NOT NULL
  58.              CONSTRAINT coPKChild
  59.                PRIMARY KEY
  60.                USING INDEX
  61.                
  62.   , firstName VARCHAR(50)
  63.               NOT NULL
  64.              
  65.   , lastName  VARCHAR(50)
  66.               NOT NULL
  67.              
  68.   , gender    CHAR(01)
  69.               NOT NULL
  70.               CONSTRAINT coCHChildGender
  71.                 CHECK (gender IN ('F','M'))
  72.              
  73.   , yearBorn  NUMBER(4,0)
  74.              
  75.   , motherId  NUMBER(10,0)
  76.               NOT NULL
  77.              
  78.   , meaninglessData CHAR(100)
  79.                     NOT NULL
  80.    )
  81. ;
  82. COMMIT
  83. ;
  84. /
  85. DECLARE
  86.  
  87.   noOfMothers           CONSTANT int := 50000;
  88.  
  89. BEGIN
  90.  
  91.   FOR m IN 1..noOfMothers LOOP
  92.    
  93.     IF MOD(m, 100) > 0 THEN
  94.      INSERT
  95.        INTO mother (id, firstname, lastname, gender, dateBorn, hairColor, meaninglessData)
  96.        VALUES      (
  97.                     m
  98.                     , 'First name ' || TO_CHAR(m)
  99.                     , 'Last Name ' || TO_CHAR(m)
  100.                     , 'F'
  101.                     , TO_DATE('1980-01-01 00:00:00','YYYY-MM-DD hh24:mi:ss')
  102.                        + to_ymInterval(TO_CHAR(FLOOR(dbms_random.VALUE(1,30))) || '-' || TO_CHAR(FLOOR(dbms_random.VALUE(1,12))) )
  103.                     ,  CASE MOD(m, 101)
  104.                           WHEN 0 THEN 'blond'
  105.                           ELSE        'brown'
  106.                        END
  107.                     , 'x');
  108.     END IF;
  109.   END LOOP;
  110.  
  111.   COMMIT;
  112. END;
  113. /
  114. CREATE INDEX idxChildMotherId ON CHILD (motherID);
  115. /
  116. -- -------------------------------------------------------
  117. EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','MOTHER');
  118. /
  119. EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','CHILD');
  120. /
  121.  
  122. DELETE FROM child;
  123. COMMIT; -- delete children and commit
  124.  
  125. DELETE FROM MOTHER WHERE HAIRCOLOR = 'blond'; -- drop blond mothers
  126.  
  127. ALTER TABLE child
  128. ADD CONSTRAINT fk_ChildMother
  129. FOREIGN KEY (motherID) REFERENCES mother(ID); -- add foreign key
  130.  
  131. SELECT c.ID, c.FIRSTNAME, c.LASTNAME -- get motherless children
  132. FROM CHILD c WHERE c.MOTHERID IN
  133. (SELECT CHILD.MOTHERID FROM CHILD
  134. MINUS
  135. SELECT m.ID FROM MOTHER m)
  136. ORDER BY c.ID;
  137.  
  138. ROLLBACK; -- rollback, to add foreign key again
RAW Paste Data