/* ************************************************** */
/* This script creates (heaped versions of) tables    */
/* mother and child for use in the exercises on       */
/* application design                                 */
/* distribution of data in the tables                 */
/*                                                    */
/*                      Bo Brunsgaard, October 2016   */
/*                             Revised October 2017   */
/* ************************************************** */


/*DROP INDEX IXMOTHERIDCHILD;
DROP INDEX IXHAIRCOLORMOTHER;
DROP INDEX IXCHILDYEARBORN;

DROP INDEX idxChildMotherId;*/

ALTER SYSTEM FLUSH SHARED_POOL;

DROP TABLE child purge
;
DROP TABLE mother purge
;
CREATE TABLE mother
(
    id       NUMBER(10,0)
             NOT NULL
             CONSTRAINT coPKMother
               PRIMARY KEY
               USING INDEX

  , firstName VARCHAR(50)
              NOT NULL

  , lastName  VARCHAR(50)
              NOT NULL

  , gender    CHAR(01)
              NOT NULL
              CONSTRAINT coCHMotherGender
                CHECK (gender = 'F')

  , dateBorn  DATE
              NOT NULL

 , hairColor   VARCHAR2(20)
               NOT NULL

 , meaninglessData CHAR(100)
                   NOT NULL
)
;

CREATE TABLE child
 (
     id      NUMBER(10,0)
             NOT NULL
             CONSTRAINT coPKChild
               PRIMARY KEY
               USING INDEX

  , firstName VARCHAR(50)
              NOT NULL

  , lastName  VARCHAR(50)
              NOT NULL

  , gender    CHAR(01)
              NOT NULL
              CONSTRAINT coCHChildGender
                CHECK (gender IN ('F','M'))

  , yearBorn  NUMBER(4,0)

  , motherId  NUMBER(10,0)
              NOT NULL

  , meaninglessData CHAR(100)
                    NOT NULL
   )
;
COMMIT
;
/
DECLARE

  noOfMothers           CONSTANT int := 50000;

BEGIN

  FOR m IN 1..noOfMothers LOOP

    IF MOD(m, 100) > 0 THEN
     INSERT
       INTO mother (id, firstname, lastname, gender, dateBorn, hairColor, meaninglessData)
       VALUES      (
                    m
                    , 'First name ' || TO_CHAR(m)
                    , 'Last Name ' || TO_CHAR(m)
                    , 'F'
                    , TO_DATE('1980-01-01 00:00:00','YYYY-MM-DD hh24:mi:ss')
                       + to_ymInterval(TO_CHAR(FLOOR(dbms_random.VALUE(1,30))) || '-' || TO_CHAR(FLOOR(dbms_random.VALUE(1,12))) )
                    ,  CASE MOD(m, 101)
                          WHEN 0 THEN 'blond'
                          ELSE        'brown'
                       END
                    , 'x');
    END IF;
  END LOOP;

  COMMIT;
END;
/
CREATE INDEX idxChildMotherId ON CHILD (motherID);
/
-- -------------------------------------------------------
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','MOTHER');
/
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','CHILD');
/

DELETE FROM child;
COMMIT; -- delete children and commit

DELETE FROM MOTHER WHERE HAIRCOLOR = 'blond'; -- drop blond mothers

ALTER TABLE child
ADD CONSTRAINT fk_ChildMother
FOREIGN KEY (motherID) REFERENCES mother(ID); -- add foreign key

SELECT c.ID, c.FIRSTNAME, c.LASTNAME -- get motherless children
FROM CHILD c WHERE c.MOTHERID IN
(SELECT CHILD.MOTHERID FROM CHILD
MINUS
SELECT m.ID FROM MOTHER m)
ORDER BY c.ID;

ROLLBACK; -- rollback, to add foreign key again