[Project] STRC 러닝 데이터베이스: 러닝크루 기록 관리

 

[ Project ]

 

 

1. 프로젝트 개요

• 분류: 데이터베이스 설계
• 일시: 2019.10. ~ 2019.11.
• 스택: Oracle DB
• 주제: 러닝 동아리원의 정보를 관리하기 위한 데이터베이스 설계

 

 

2. 프로젝트 소개

STRC 로고

 STRC는 매주 2회 정기 러닝을 하는 서울과학기술대학교 중앙동아리입니다. STRC 동아리원들의 신상정보와 러닝정보를 체계적으로 관리하고 운용하기 위해 데이터베이스를 만들기로 생각했습니다.

 

 

● 프로젝트 목적

- 동아리원들의 신상정보를 체계적으로 관리
: 매학기 신규 지원자가 150명이 넘는 중앙동아리입니다.
다수의 신상정보를 체계적으로 관리하기 위한 데이터베이스를 설계해야 합니다.

- 동아리원들의 러닝정보를 체계적으로 관리
: 타 대학 러닝 동아리와 연합 활동이 많고, 각종 마라톤 대회나 행사참여, 번개런(불시에 모여서 뛰는 모임), 정기 러닝, 훈련 프로그램 등 다양한 활동을 운영하고 있습니다. 동아리원이 참여한 러닝정보를 체계적으로 관리하기 위해 데이터베이스를 설계해야 합니다.

 또한, 데이터 량도 적지 않을 것으로 예상되고 있습니다. (200명의 동아리원이 일주일에 4번씩만 달려도, 한 달에 약 3200개의 러닝기록이 생성됩니다)

 

 

cf) 사용자 요구사항 분석 (인터뷰)

운영진들 중심으로, STRC 러닝 데이터베이스의 요구사항을 분석했습니다. 카카오톡 메신저를 이용하기도 했고, 동아리 회식자리에서 의견을 받기도 했습니다. 이에 대한 내용을 아래에 정리해두었습니다.


 Q) STRC 중앙동아리는 어떤 활동을 하고 있나요?
 A) STRC는 Seoul Tech Running Crew 의 줄임말로, 서울과학기술대학교 재학생과 휴학생, 졸업생을 대상으로 정기적인 러닝모임을 갖는 운동 동아리입니다. 현재, 매주 2회(월, 수) 정기러닝을 하고 있으며, 번개런도 상시 운영 중입니다. (번개런은 특정 시간에 불시에 모여 뛰는 러닝모임으로, 원하는 시간대에 동아리원들이 모여 뛰는 상시모임입니다.) 또한, 마라톤 대회도 꾸준히 참가하고 있습니다. 이번 학기에는 서울달리기 대회, 춘천마라톤, JTBC마라톤, 손기정평화마라톤 참가가 예정되어있습니다. 이 외에도, 타 러닝 소모임(대학생, 사회인)과 콜라보 런을 하거나, 서울 동부연합 동아리끼리 주말에 연합러닝도 진행하고 있습니다.

Q) 그럼, STRC 동아리원은 얼마나 있습니까?
A) 저희 STRC 동아리원은 ‘정규 동아리원’과 ‘신입 동아리원’으로 나뉘어집니다. 일정기간 내에 3회 러닝을 하셔야 정규 동아리원으로 입부 가능합니다. 또, 정규 동아리원도 일정 점수를 채우지 못하면 다음 학기에 탈퇴되므로 굉장히 유동적으로 운영되고 있습니다. 정규 동아리원은 83명이며, 신입 동아리원은 134명으로 현재도 정규 동아리원 입부가 계속 이루어지고 있습니다.

Q) STRC에서 진행 중인 러닝활동을 좀 더 자세히 말해주실 수 있을까요?
A) 우선, 정기 런은 주 2회(월, 수)로 학교 운동장이나, 중량천에서 진행하고 있습니다. 거리는 4.5km, 7km로 나뉘어서 뛰고 있습니다. 또, 주말에는 서울 동부지역 대학끼리 연합해서 ‘동부연합 러닝’과, 타 러닝 동호회와 ‘콜라보 런’을 주로 진행하고 있습니다.
 ‘오픈 런’은 동아리 사람 외에도 같이 러닝을 참가할 수 있는 활동입니다. ‘번개 런’은 동아리원이 뛰고 싶은 시간에 동아리원들을 모아 불시에 뛰는 상시 모임입니다. ‘훈련 프로그램’은‘담당자(조교)’에 진행되며, 호흡법이나 러닝법 등을 가르쳐주거나 대회를 연습하는 프로그램입니다.
 ‘마라톤 대회’도 꾸준히 참가하고 있으며, 현재 학기에는 서울달리기 대회, 춘천마라톤, JTBC마라톤, 손기정평화마라톤 참가가 예정되어있습니다.

 

 

 

3. 개념 스키마 설계 [E-R 모델]

 동아리원과 활동 프로그램, 러닝기록 간의 관계를 표현하기 위해 관계형 데이터 모델인 E-R모델로 설계했습니다.

E-R 다이어그램

 

 

 

4. 논리 데이터베이스 설계

 타겟 DBMS가 관계형 데이터베이스(오라클)이므로 해당 E-R 모델을 실제 2차원 구조의 테이블로 구성합니다.

 

 

 

 

5. 테이블 명세서 작성

 앞서 구성한 2차원 구조의 테이블 13개의 명세서를 작성합니다.

 

 

 

 

6. 데이터베이스 활용 예시

1) SQL

1. STRC 크루원들 중에서, 정규 크루원들의 이름과 학번, 입부일자를 출력하시오.

 

2. STRC 크루원들중에서, 18년도 이후에 입부한 크루원들의 정보를 모두 출력하시오.

 

3. 4km이상 뛴 러닝기록의 일시, 러닝거리, 걸린 시간과 페이스, 그리고 해당 기록 소유자의 이름과 직책을 출력하시오.

 

4. 이번 19년도 2학기 신입크루훈련에 참가하고 있는 크루원들의 이름과 학번을 출력하시오.

 

5. 이번 2019학년도 훈련프로그램을 2개 이상 참여중인 크루원의 이름과 활동하는 훈련프로그램의 개수를 출력하시오.

 

6. 신입 크루원 중, 신입훈련 프로그램을 참여하지 않은 크루원들의 이름과 성별, 입부일자를 출력하시오.

 

7. 현재 진행 중인 훈련 프로그램들의 이름과 참여인원 수를 출력하시오.

 

8. STRC와 연합 러닝을 하고 있는 ‘동부연합’에 소속된 동아리 이름과 활동지역을 출력하시오.

 

9. 2019년도 2학기에 진행한 연합 프로그램을 2번 이상 참가한 크루원의 이름과 해당 크루원이 참가한 연합 프로그램의 명칭과 일시, 장소를 크루원의 이름으로 오름차순해서 출력하시오.

select distinct a.p_name 이름, c.ur_name 연합프로그램명, c.ur_date 일시, c.ur_place 장소 
from privacy a, crew_union b, union_running c
where a.p_no=b.cu_no and b.cu_name=c.ur_name and b.cu_no=some(
select b.cu_no
from privacy a, crew_union b, union_running c
where a.p_no=b.cu_no and b.cu_name=c.ur_name
group by b.cu_no
having count(*)>=2)
order by a.p_name;

 

10. 2019년 2학기 연합러닝에 참가한 신입크루원들의 이름과 학번, 학과, 성별을 출력하시오.

 

11. 2019년 1학기 신입크루훈련을 받은 크루원들 중에서, 가장 페이스가 빠른 크루원의 이름과 해당 기록의 러닝거리, 걸린시간, 일시, 페이스를 출력하시오.

 

12. 2019년 2학기에 번개런(host 크루원이 정한 시간대에 모여 불시에 러닝을 하는 이벤트)을 2회 이상 소집한 주관자의 이름과 소집 횟수를 출력하시오.

 

13. 2019년 2학기 동안, 2회 이상 번개런에 참가한 크루원의 이름과 성별, 직급을 출력하시오.

 

14. 2019년 2학기에 STRC 크루원들이 참여한 마라톤 대회의 명칭과 각 대회별 참여인원 수를 출력하시오.

 

15. 제11회 손기정마라톤과 제13회 서울어택에서 수상한 크루원들의 이름, 대회명칭, 상 이름, 등수, 상금을 출력하시오.

 

 

2) 프로시저

 러닝거리와 시간을 입력하면, 동일한 러닝거리의 기록들과 비교하여 총 몇 개의 기록 중에서 자신이 몇 등을 했는지를 알려주는 프로시저입니다.

 

create or replace procedure rank(
 dist in varchar2,
 runtime in number)
IS
 rank number(4);
 total number(4);
BEGIN
 select count(*)
 into rank
 from running
 where dist=r_distance and runtime>=r_runtime;

 select count(*)
 into total
 from running
 where dist=r_distance;

 DBMS_OUTPUT.PUT_LINE('총 '||total||'개의 '||dist||'km 달리기 기록 중에서');
 DBMS_OUTPUT.PUT_LINE('당신의 등수는 '||rank||'등 입니다.');

END;

 

 

3) 함수

러닝거리를 입력하면, 해당 러닝거리의 기록 중 가장 좋은 기록이 걸린 시간을 반환합니다.

 

create or replace function best(
dist in number)
return number
IS
best_time number;
BEGIN
select max(r_runtime)
into best_time
from running
where r_distance=dist;

return(best_time);
END best;

 

 

4) 패키지

 모든 크루의 정보를 출력하는 all_member_info, 모든 크루의 학번과 이름을 출력하는 no_name_info, 특정 학번의 크루 정보를 출력하는 dist_no_info(학번)를 정의합니다.

 

<선언부>

create or replace package m_info as
procedure all_member_info;
procedure no_name_info;
procedure dist_no_info(no in number);
end m_info;

 

<본문>

create or replace package body m_info as
procedure all_member_info
is
cursor mem_cursor is
Select *
from privacy
order by p_name;
begin
for a in mem_cursor loop
DBMS_OUTPUT.PUT_LINE('크루 학번 : '||a.p_no);
DBMS_OUTPUT.PUT_LINE('크루 P/N : '||a.p_phone);
DBMS_OUTPUT.PUT_LINE('크루 이름 : '||a.p_name);
DBMS_OUTPUT.PUT_LINE('크루 학과 : '||a.p_dept);
DBMS_OUTPUT.PUT_LINE('크루 성별 : '||a.p_sex);
DBMS_OUTPUT.PUT_LINE('크루 직책 : '||a.p_position);
DBMS_OUTPUT.PUT_LINE('크루 입부일자 : '||a.p_date);
END LOOP;
EXCEPTION
when others then
DBMS_OUTPUT.PUT_LINE('에러 발생 : '||SQLERRM);
END all_member_info;

procedure no_name_info
is
cursor mem_cursor is
Select p_no, p_name
from privacy
order by p_name;
begin
for a in mem_cursor loop
DBMS_OUTPUT.PUT_LINE('크루 학번 : '||a.p_no);
DBMS_OUTPUT.PUT_LINE('크루 이름 : '||a.p_name);
END LOOP;
EXCEPTION
when others then
DBMS_OUTPUT.PUT_LINE('에러 발생 : '||SQLERRM);
END no_name_info;

procedure dist_no_info(no in number)
is
cursor mem_cursor is
Select p_no, p_name
from privacy
where floor(p_no/1000000)=no
order by p_name;
begin
for a in mem_cursor loop
DBMS_OUTPUT.PUT_LINE('크루 학번 : '||a.p_no);
DBMS_OUTPUT.PUT_LINE('크루 이름 : '||a.p_name);
END LOOP;
EXCEPTION
when others then
DBMS_OUTPUT.PUT_LINE('에러 발생 : '||SQLERRM);
END dist_no_info;
END m_info;

 

 

 

5) 트리거

 새로운 러닝기록을 insert할 때, 자신의 러닝기록 중 최고치를 알려주는 트리거입니다.

 

create or replace trigger tri_run
after
insert on running
declare
best_run3 number;
best_run5 number;
BEGIN
select min(r_runtime)
into best_run3
from running
where r_distance=3;

select min(r_runtime)
into best_run5
from running
where r_distance=5;

DBMS_OUTPUT.PUT_LINE('현재 3km 최고기록 : '||best_run3);
DBMS_OUTPUT.PUT_LINE('현재 5km 최고기록 : '||best_run5);
end;

 

 

 

6) Pro*C를 이용한 프로그래밍

 1. STRC 러닝동아리 회원 중, 신입 동아리원의 신상정보(이름, 학번, 학과, 휴대전화번호, 성별)을 C언어를 이용하여 보기 좋게 출력합니다. (마지막 줄에는 총 신입 동아리원 수를 출력합니다.)

 

#include <stdio.h> 
#include <string.h> 
#include <sqlca.h>  

EXEC SQL BEGIN DECLARE SECTION; 
char connstr[30];
EXEC SQL END DECLARE SECTION; 

void SqlConnect() { 
  strcpy_s(connstr, 30, "CSDB120/3695@wow");
  EXEC SQL CONNECT :connstr;  

  if (sqlca.sqlcode != 0 && sqlca.sqlcode != -1405) { 
    printf("\n DB연결 에러메시지 = %s", sqlca.sqlerrm.sqlerrmc); 
  } else 
    printf("\n ********** DB 연결 성공 **********");
}
void main(){
EXEC SQL BEGIN DECLARE SECTION ; 
    char p_no[9], p_name[21], p_dept[31], p_phone[12], p_sex[3], p_position[11]; 
EXEC SQL END DECLARE SECTION ; 

   int count=0 ; 
    SqlConnect(); 
 
   EXEC SQL DECLARE crew_cursor CURSOR FOR SELECT p_no, p_name, p_dept, p_phone, p_sex FROM privacy WHERE p_position='신규'; 
  EXEC SQL OPEN crew_cursor ; 
   EXEC SQL FETCH crew_cursor INTO :p_no, :p_name, :p_dept, :p_phone, :p_sex ; 

   if (sqlca.sqlcode != 0 && sqlca.sqlcode != -1405) { 
      EXEC SQL CLOSE crew_cursor ; 
     return ;
    }
while(1) { 
      count++ ; 
      printf("\n\n %d번째 신입크루, %s의 신상정보", count, p_name);
      printf("\n 학번 : %s, 학과 : %s, 휴대폰번호 : %s, 성별 : %s", p_no, p_dept, p_phone, p_sex); 

     EXEC SQL FETCH crew_cursor INTO :p_no, :p_name, :p_dept, :p_phone, :p_sex ; 

     if (sqlca.sqlcode != 0 && sqlca.sqlcode != -1405) { 
     EXEC SQL CLOSE crew_cursor ; 
         break;
     } 
} 
   printf("\n\n 총 신입크루 수 =%d \n\n", count) ; 
   EXEC SQL CLOSE crew_cursor; 
}

 

 

2. STRC 러닝동아리 회원이 참여한 대회의 이름, 일시, 장소, 주최를 Pro*c를 이용해서 출력합니다.
(마지막 줄에는 동아리원들의 총 대회출전횟수를 출력합니다.)

 

#include <stdio.h> 
#include <string.h> 
#include <sqlca.h>  

EXEC SQL BEGIN DECLARE SECTION; 
char connstr[30];
EXEC SQL END DECLARE SECTION; 

void SqlConnect() { 
  strcpy_s(connstr, 30, "CSDB120/3695@wow");
  EXEC SQL CONNECT :connstr;  

  if (sqlca.sqlcode != 0 && sqlca.sqlcode != -1405) { 
    printf("\n DB연결 에러메시지 = %s", sqlca.sqlerrm.sqlerrmc); 
  } else 
    printf("\n ********** DB 연결 성공 **********");
}
void main(){
EXEC SQL BEGIN DECLARE SECTION ; 
    char c_name[41], cc_name[41], c_date[12], c_place[21], c_host[21], cc_no[9], p_no[9], p_name[21]; 
EXEC SQL END DECLARE SECTION ; 

   int count=0 ; 
    SqlConnect(); 
 
   EXEC SQL DECLARE crew_cursor CURSOR FOR SELECT a.c_name, a.c_date, a.c_place, a.c_host, c.p_name FROM crew_con b, contest a, privacy c  
WHERE a.c_name=b.cc_name and cc_no=p_no; 
  EXEC SQL OPEN crew_cursor ; 
   EXEC SQL FETCH crew_cursor INTO :c_name, :c_date, :c_place, :c_host, :p_name ; 

   if (sqlca.sqlcode != 0 && sqlca.sqlcode != -1405) { 
      EXEC SQL CLOSE crew_cursor ; 
     return ;
    }
while(1) { 
      count++ ; 
      printf("\n\n %d. %s 크루원의 대회참가정보", count, p_name);
      printf("\n 대회 : %s, 일시 : %s, 장소 : %s, 주최 : %s", c_name, c_date, c_place, c_host); 

     EXEC SQL FETCH crew_cursor INTO :c_name, :c_date, :c_place, :c_host, :p_name ; 

     if (sqlca.sqlcode != 0 && sqlca.sqlcode != -1405) { 
     EXEC SQL CLOSE crew_cursor ; 
         break;
     } 
} 
   printf("\n\n 총 대회참여횟수 수 =%d \n\n", count) ; 
   EXEC SQL CLOSE crew_cursor; 
}

 

 

7) JDBC를 이용한 프로그래밍

1. 기록을 알고 싶은 러닝거리를 입력하면, STRC 러닝동아리 회원기록 중에서 해당 러닝거리의 기록이 조회됩니다.

 

import java.sql.*;
import java.util.Scanner;

public class selectTable
{
public static void main(String[] argv)
{
Connection conn;
PreparedStatement pstmt;
ResultSet rs;
try{
//드라이버를 로딩합니다.
Class.forName("oracle.jdbc.driver.OracleDriver");
//연결을 설정합니다.
conn = DriverManager.getConnection
("jdbc:oracle:thin:@117.17.198.43:1531:ORCL", 
"CSDB120", "3695");
//SQL문을 실행합니다.
int num; // 러닝기록을 저장할 변수 선언
Scanner s = new Scanner(System.in);
System.out.println("알고싶은 러닝기록의 거리(km) : ");
num = s.nextInt(); // 러닝거리를 입력받습니다.
System.out.println("\n 해당 거리의 기록을 보여줍니다. (" + num +"km기록)\n");
pstmt = conn.prepareStatement("SELECT b.p_name, a.r_runtime, a.r_place FROM running a, privacy b WHERE a.r_no=b.p_no and a.r_distance= ? ");
pstmt.setInt(1, num); // 입력받은 러닝거리를 sql문에 대입합니다.
rs = pstmt.executeQuery(); // 쿼리 진행
int count = 0; // 조회된 레코드의 수를 저장할 변수 선언
while(rs.next())
{
count++;
//데이터를 받아옵니다.
String name = rs.getString(1); //컬럼위치 지정
String runtime = rs.getString(2); 
String place = rs.getString(3);

//결과를 출력합니다.
System.out.println(count+"등 "+name+"님의 걸린시간은 "+runtime+"이며");
System.out.println("러닝장소 : "+place+" 입니다.");
}
System.out.println("총 "+count+"의 기록을 탐색했습니다.");
//Result Set를 닫습니다.
rs.close();
//Statement를 닫습니다.
pstmt.close();
//연결을 닫습니다.
conn.close();
}catch(Exception e){e.printStackTrace();}
}
}

 

 2. 러닝거리와 거리를 입력하면, 동일한 러닝거리의 기록들과 비교하여, 총 몇 개의 기록 중에서 자신이 몇 등을 했는지 알려주는 프로시저를 CallableStatement를 이용해서 호출합니다.

- 등수를 알려주는 rank(러닝거리, 러닝시간) 프로시저

create or replace procedure rank(
dist in varchar2,
runtime in number)
IS
rank number(4);
total number(4);
BEGIN
select count(*)
into rank
from running
where dist=r_distance and runtime>=r_runtime;
select count(*)
into total
from running
where dist=r_distance;
DBMS_OUTPUT.PUT_LINE('총 '||total||'개의 '||dist||'km 달리기 기록 중에서');
DBMS_OUTPUT.PUT_LINE('당신의 등수는 '||rank||'등 입니다.');
END;

 

- CallableStatement를 이용한 호출

import java.sql.*;
import java.util.Scanner;

public class CallableStatementTest1
{
public static void main(String[] argv)
{
Connection conn;

try{
Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection(
"jdbc:oracle:thin:@117.17.198.43:1531:ORCL", 
"CSDB120", "3695");
CallableStatement cs;
String dist;
String time;

Scanner s = new Scanner(System.in);
System.out.println("당신의 기록을 입력하세요.");
System.out.printf("달린거리 : ");
dist = s.next();
System.out.printf("걸린시간 : ");
time = s.next();
cs = conn.prepareCall("{call rank(?, ?)}");
cs.setString(1, dist);
cs.setString(2, time);
cs.executeUpdate();
cs.close();
conn.close();
}catch(Exception e)
{e.printStackTrace();}
}
}

 

3. STRC 러닝동아리는 각종 타 대학교 및 사회인 동호회와 연합을 맺어 같이 러닝활동을 하고 있습니다. STRC와 연합을 맺고 있는 동아리 및 동호회를 추가하는 프로시저를 만들고 이를 CallableStatement를 이용해서 호출합니다.

- 연합 동아리/동호회를 추가하는 프로시저

create or replace procedure u_insert(
name in varchar2,
kind in varchar2,
place in varchar2)
as
BEGIN
insert into Union1(u_name, u_kind, u_place) values(name, kind, place);
END;

 

- CallableStatement를 이용한 호출

import java.sql.*;
import java.util.Scanner;

public class CallableStatementTest1
{
public static void main(String[] argv)
{
Connection conn;

try{
Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection(
"jdbc:oracle:thin:@117.17.198.43:1531:ORCL", 
"CSDB120", "3695");
CallableStatement cs;
String name; // 입력받을 변수 선언
String kind;
String place;
Scanner s = new Scanner(System.in);
System.out.println("새로운 연합단체의 정보를 입력합니다.");
System.out.printf("단체이름 : ");
name = s.next(); // 변수 입력
System.out.printf("단체종류 : ");
kind = s.next(); // 변수 입력
System.out.printf("활동지역 : ");
place = s.next(); // 변수 입력
cs = conn.prepareCall("{call u_insert (?, ?, ?)}");

cs.setString(1, name);
cs.setString(2, kind);
cs.setString(3, place);
cs.executeUpdate(); // 명령수행
System.out.println("입력이 완료되었습니다.");
System.out.println("새로운 연합단체 "+name+"은 "+place+"에서 활동하고 있는 "+kind+"입니다." );
cs.close();
conn.close();
}catch(Exception e)
{e.printStackTrace();}
}
}