SHARE
TWEET

Untitled

a guest Dec 11th, 2017 99 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top