5.8.1 データベースでのサンプル・データの設定

「SQLプロパティ・グラフの作成」に示されているSQLプロパティ・グラフstudents_graphを作成するには、データベースに次のサンプル表とデータを設定する必要があります。

1. スキーマ・ユーザーとしてデータベースに接続します。
2. 次のSQLスクリプトを実行して、データベースにサンプル・データを含むuniversity、persons、studentsおよびfriendships表を作成します。

   CREATE TABLE university (
       id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
       name VARCHAR2(10),
       CONSTRAINT u_pk PRIMARY KEY (id));
   
   INSERT INTO university (name) VALUES ('ABC');
   INSERT INTO university (name) VALUES ('XYZ');
   
   CREATE TABLE persons (
        person_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT
        BY 1),
        name VARCHAR2(10),
        birthdate DATE,
        height FLOAT DEFAULT ON NULL 0,
        hr_data JSON,
        CONSTRAINT person_pk PRIMARY KEY (person_id)
      );
   
   INSERT INTO persons (name, height, birthdate, hr_data)
          VALUES ('John', 1.80, to_date('13/06/1963', 'DD/MM/YYYY'), '{"department":"IT","role":"Software Developer"}');
   
   INSERT INTO persons (name, height, birthdate, hr_data)
          VALUES ('Mary', 1.65, to_date('25/09/1982', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Manager"}');
   
   INSERT INTO persons (name, height, birthdate, hr_data)
          VALUES ('Bob', 1.75, to_date('11/03/1966', 'DD/MM/YYYY'), '{"department":"IT","role":"Technical Consultant"}');
   
   INSERT INTO persons (name, height, birthdate, hr_data)
          VALUES ('Alice', 1.70, to_date('01/02/1987', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Assistant"}');
   
   CREATE TABLE student_of (
         s_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
         s_univ_id NUMBER,
         s_person_id NUMBER,
         subject VARCHAR2(10),
         CONSTRAINT stud_pk PRIMARY KEY (s_id),
         CONSTRAINT stud_fk_person FOREIGN KEY (s_person_id) REFERENCES persons(person_id),
         CONSTRAINT stud_fk_univ FOREIGN KEY (s_univ_id) REFERENCES university(id)
       );
   
   INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (1,1,'Arts');
   INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (1,3,'Music');
   INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (2,2,'Math');
   INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (2,4,'Science');
   
   CREATE TABLE friends (
       friendship_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
       person_a NUMBER,
       person_b NUMBER,
       meeting_date DATE,
       CONSTRAINT fk_person_a_id FOREIGN KEY (person_a) REFERENCES persons(person_id),
       CONSTRAINT fk_person_b_id FOREIGN KEY (person_b) REFERENCES persons(person_id),
       CONSTRAINT fs_pk PRIMARY KEY (friendship_id)
   );
   
   INSERT INTO friends (person_a, person_b, meeting_date) VALUES (1, 3, to_date('01/09/2000', 'DD/MM/YYYY'));
   INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 4, to_date('19/09/2000', 'DD/MM/YYYY'));
   INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 1, to_date('19/09/2000', 'DD/MM/YYYY'));
   INSERT INTO friends (person_a, person_b, meeting_date) VALUES (3, 2, to_date('10/07/2001', 'DD/MM/YYYY'));