5.8.1 Setting Up Sample Data in the Database

In order to create the SQL property graph, students_graph, shown in Creating a SQL Property Graph, the following sample tables with data need to be set up in the database.

1. Connect to the database as the schema user.
2. Run the following SQL script to create the university, persons, students, and friendships tables with sample data in the database.

   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'));