관계형DB와 그래프DB의 비교를 위한 기초자료

은행 계좌의 예입니다. 

고객이 있고, 고객들은 수신 계좌와 여신계좌는 공유 할 수 있고, 카드는 1명의 고객만 가질 수 있다는 구조에서 먼저 관계형DB의 생성 및 조회 예제입니다.

 

오라클용 생성용 쿼리 입니다.

CREATE TABLE Customers (
    customer_id VARCHAR2(50),
    name VARCHAR2(100),
    PRIMARY KEY (customer_id)
);

INSERT INTO Customers (customer_id, name) VALUES
('customer_0', 'Michael');
INSERT INTO Customers (customer_id, name) VALUES
('customer_1', 'Maria');
INSERT INTO Customers (customer_id, name) VALUES
('customer_2', 'Rashika');
INSERT INTO Customers (customer_id, name) VALUES
('customer_3', 'Jamie');
INSERT INTO Customers (customer_id, name) VALUES
('customer_4', 'Aalyah');

SELECT * FROM Customers;

CREATE TABLE Accounts (
    acct_id VARCHAR2(50),
    created_date DATE DEFAULT SYSDATE,
    PRIMARY KEY (acct_id)
);

CREATE TABLE Loans (
    loan_id VARCHAR2(50),
    created_date DATE DEFAULT SYSDATE,
    PRIMARY KEY (loan_id)
);

INSERT INTO Accounts (acct_id) VALUES ('acct_0');
INSERT INTO Accounts (acct_id) VALUES ('acct_5');
INSERT INTO Accounts (acct_id) VALUES ('acct_14');

INSERT INTO Loans (loan_id) VALUES ('loan_18');
INSERT INTO Loans (loan_id) VALUES ('loan_32');
INSERT INTO Loans (loan_id) VALUES ('loan_80');

CREATE TABLE CreditCards (
    cc_num VARCHAR2(50),
    customer_id VARCHAR2(50) NOT NULL,
    created_at DATE DEFAULT SYSDATE,
    PRIMARY KEY (cc_num),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

INSERT INTO CreditCards (cc_num, customer_id) VALUES ('cc_17', 'customer_0');
INSERT INTO CreditCards (cc_num, customer_id) VALUES ('cc_32', 'customer_2');

CREATE TABLE Owns (
    customer_id VARCHAR2(50) NOT NULL,
    acct_id VARCHAR2(50) NOT NULL,
    created_date DATE DEFAULT SYSDATE,
    PRIMARY KEY (customer_id, acct_id),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (acct_id) REFERENCES Accounts(acct_id)
);

INSERT INTO Owns (customer_id, acct_id) VALUES ('customer_0', 'acct_14');
INSERT INTO Owns (customer_id, acct_id) VALUES ('customer_1', 'acct_14');
INSERT INTO Owns (customer_id, acct_id) VALUES ('customer_2', 'acct_5');
INSERT INTO Owns (customer_id, acct_id) VALUES ('customer_3', 'acct_0');
INSERT INTO Owns (customer_id, acct_id) VALUES ('customer_4', 'acct_0');

CREATE TABLE Owes (
    customer_id VARCHAR2(50) NOT NULL,
    loan_id VARCHAR2(50) NOT NULL,
    created_date DATE DEFAULT SYSDATE,
    PRIMARY KEY (customer_id, loan_id),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (loan_id) REFERENCES Loans(loan_id)
);

INSERT INTO Owes (customer_id, loan_id) VALUES ('customer_0', 'loan_32');
INSERT INTO Owes (customer_id, loan_id) VALUES ('customer_3', 'loan_18');
INSERT INTO Owes (customer_id, loan_id) VALUES ('customer_4', 'loan_18');
INSERT INTO Owes (customer_id, loan_id) VALUES ('customer_4', 'loan_80');

 

질의와 질의에 따른 쿼리들입니다.

 

1. 고객은 어떤 신용카드를 사용하는가?

신용카드 테이블에서 고객ID로 조회 하면 됩니다.

SELECT * FROM CREDITCARDS c 
WHERE c.CUSTOMER_ID ='customer_0';

 

고객의 정보도 같이 알고 싶다면, 고객테이블과 조인해서 보여줘야 합니다.

SELECT * 
FROM CUSTOMERS c 
LEFT JOIN CREDITCARDS c2 ON c.CUSTOMER_ID  = c2.CUSTOMER_ID 
WHERE c.CUSTOMER_ID ='customer_0';

 

2. 고객은 어느 계좌를 소유하는가?

owns 테이블을 중심으로 고객과 계좌 테이블을 조회합니다.

SELECT *
FROM CUSTOMERS c 
LEFT JOIN OWNS o ON o.CUSTOMER_ID = c.CUSTOMER_ID 
LEFT JOIN ACCOUNTS a ON a.ACCT_ID = o.ACCT_ID 
WHERE c.CUSTOMER_ID ='customer_0'
;

 

3. 고객은 어떤 대출을 받았는가?

SELECT *
FROM CUSTOMERS c 
LEFT JOIN OWES o ON o.CUSTOMER_ID = c.CUSTOMER_ID 
LEFT JOIN LOANS l ON l.LOAN_ID  = o.LOAN_ID 
WHERE c.CUSTOMER_ID ='customer_4'
;

 

4. 고객의 어떤 정보를 알고 있는가?

고객의 모든 수신계좌, 여신계좌, 카드 조회 하는 쿼리입니다.

엮이는 테이블이 늘어나면서 쿼리가 점점 길어지는걸 알수 있습니다.

SELECT *
FROM CUSTOMERS c 
LEFT JOIN OWNS o ON o.CUSTOMER_ID = c.CUSTOMER_ID 
LEFT JOIN ACCOUNTS a ON a.ACCT_ID = o.ACCT_ID 
LEFT JOIN OWES o2 ON o2.CUSTOMER_ID = c.CUSTOMER_ID 
LEFT JOIN LOANS l ON l.LOAN_ID = o2.LOAN_ID 
LEFT JOIN CREDITCARDS c2 ON c2.CUSTOMER_ID = c.CUSTOMER_ID 
WHERE c.CUSTOMER_ID = 'customer_0'
;

 

Gremlin 언어 설명에서 같은 형태의 데이터를 그래프db로 구축하고 조회 예제를 보겠습니다.