교육센터 운영프로그램

1. 프로젝트 개요

프로젝트명: 교육센터 운영프로그램
개발 기간: 2024/08/06 ~ 2024/08/21 (3주)
인력 구성: 5명
담당 범위: ERD 설계 및 DB설계,테이블,프로시저,뷰,더미데이터 생성
배포 현황: 미출시
기술 스택: Oracle,AWS

2. 프로젝트 목적 및 담당역할

프로젝트 목적

교육관리시스템의 데이터베이스제작 프로젝트로서 관리시스템 내 저장된 데이터 최적화,데이터베이스 자체 기능 구현 및 최적화 프로젝트이다

기획 의도 및 목표

데이터베이스 이해도 향상
실제 업무에 사용가능 수준의 데이터베이스 제작을 목표로함

담당 역할

장비대여,장비목록,지원자,지원자수강상담,근태상황,출결,입퇴실시간,훈련장려금 테이블 생성과 제약조건 정의
지원자관리,수강상담내역관리,훈련장려금 지급관리,코딩 지식인 게시판관리,명예의전당 관리,출결조회(년도,월,일) 뷰와 프로시저 생성

3. 기술 스택 및 아키텍처

분류	내용
운영체제	Windows 11
서버	AWS EC2
데이터베이스	Oracle 11g Express Edition
개발 툴	SQL Developer
협업/디자인/문서	Discord, ERD Cloud, Draw.io, Google Drive

4. 주요 기능

테이블

테이블: 44개 테이블
컬럼: 193개의 컬럼
테스트데이터: 262,568행

관리자

기초 정보 관리 기능, 교사 계정 관리, 개설 과정 관리, 개설 과목 관리, 교육생 관리, 시험 관리 및 성적 조회, 출결 관리 및 출결 조회, 취업자-수강생 멘토링, 지원자 수강상담, 훈련장려금, 교사평가 문항관리, 수강평가 문항관리, 취업자 특강, 장비 대여 관리, 강의실 예약 관리, 코딩 지식인 게시판 관리, 명예의 전당 관리

교사

강의 스케줄 조회, 배점 입출력, 성적 입출력, 출결 조회, 강사 평가 조회, 코딩테스트 문제 관리, 상담일지 작성 및 조회

학생

성적 조회, 출결 조회, 교사 및 강의 평가, 강의실 예약, 코딩 지식인, 교육생 취업 현황, 코딩 지식인 사용, 상담일지 조회, 취업자 특강 신청

5.구현

1.교육생 연도별 출결조회

스크린샷

쿼리문

-연도별 출결조회

create or replace view vwattendancestatusYEAR
as

select 
    to_char(to_date(year.연도, 'yyyy-mm'), 'yyyy') as 연도,
    sum(year.지각숫자) as 지각숫자,
    sum(year.조퇴숫자) as 조퇴숫자,
    sum(year.결석숫자) as 결석숫자,
    sum(year.결석전환숫자) as 결석전환숫자,
    sum(year.외출숫자) as 외출숫자,
    sum(year.병가숫자) as 병가숫자,
    sum(year.기타숫자) as 기타숫자,
    sum(year.출석일) as 출석일
from (
    select
        to_char(a.attendance_date, 'yyyy-mm') as 연도,
        count(case when s.status = '지각' then 1 end) as 지각숫자,
        count(case when s.status = '조퇴' then 1 end) as 조퇴숫자,
        count(case when s.status = '결석' then 1 end) as 결석숫자,
        trunc((count(case when s.status = '지각' then 1 end) +
               count(case when s.status = '조퇴' then 1 end)) / 3) as 결석전환숫자,
        count(case when s.status = '외출' then 1 end) as 외출숫자,
        count(case when s.status = '병가' then 1 end) as 병가숫자,
        count(case when s.status = '기타' then 1 end) as 기타숫자,
        (count(to_char(a.attendance_date, 'yyyy-mm-dd')) / 2) - 
        trunc((count(case when s.status = '지각' then 1 end) +
               count(case when s.status = '조퇴' then 1 end)) / 3 +
               count(case when s.status = '결석' then 1 end)) as 출석일
    from
        tblattendance a
    inner join
        tblattendancestatus s on a.attendance_id = s.attendance_id
    where
        a.student_id = 401
    group by
        to_char(a.attendance_date, 'yyyy-mm')
) year
group by
    to_char(to_date(year.연도, 'yyyy-mm'), 'yyyy')
order by
    연도 asc;

목적	출결 상태를 기준으로 지각, 조퇴, 결석, 결석 전환, 외출, 병가, 기타와 같은 세부 통계를 집계하고, 최종적으로 연도별 출석일수를 계산합니다.
기술스택	Oracle Database 11g
사용 기술	SQL 집계 함수:SUM, COUNT, TRUNC 등 집계 작업 CASE :조건에 따라 특정 값을 계산 TO_CHAR, TO_DATE 함수**:날짜 형식 변환 및 처리 GROUP BY:연도별로 데이터를 그룹화 JOIN:두 테이블 (tblattendance, tblattendancestatus) 간의 데이터 결합 SUBQUERY:내부 서브쿼리(year)를 사용해 연도별 데이터를 먼저 계산한 뒤, 외부 쿼리에서 최종 집계
구현 방식	1.데이터 원본 테이블: - tblattendance와 tblattendancestatus를 조인하여 출결 데이터 조회 2.서브쿼리: - 월별 출결 상태 통계를 계산: - COUNT로 지각, 조퇴, 결석 등 상태별 숫자 집계.지각과 조퇴를 기반으로 결석전환숫자 계산 3.연도별 집계: - 서브쿼리 결과를 연도로 그룹화(GROUP BY) - 각 상태별 합계를 SUM으로 계산 4.뷰 생성: - VIEW로 연도별 통계 뷰 저장
성과	- 복잡한 SQL 쿼리를 뷰로 만들어 재사용 가능성을 높임 - 연도별로 요약된 데이터를 바로 사용할 수 있다

2.명예의 전당 나의 순위 조회 기능

스크린샷 1

스크린샷 2

쿼리문

--2.나의 순위 조회
create or replace view vwcodingtestmyranking
as
SELECT 
    sub.순위,
    sub.교육생ID,
    sub.점수,
    sub.내가푼문제수,
    sub.티어
    
FROM (
    SELECT 
        RANK() OVER (ORDER BY r.score DESC, r.coding_test_ranking_id ASC) AS 순위,
        r.student_id AS 교육생ID,
        r.score AS 점수,
        r.solve_test_count AS 내가푼문제수,
        r.tier AS 티어
    FROM 
        tblcodingtestranking r
) sub
WHERE sub.교육생ID = 412
ORDER BY 
    sub.점수 DESC;

목적	코딩테스트를 푼 문제의수에 따라 점수가 정해지고 내 점수가 전체인원중 몇위 인지 확인하는기능
기술스택	Oracle Database 11g
사용 기술	RANK() 함수:RANK() 윈도우 함수를 사용하여 점수를 기준으로 순위를 계산 SUBQUERY: SUBQUERY를 사용해 전체 순위를 계산한 뒤, 특정 교육생의 데이터를 필터링 ORDER BY:점수를 기준으로 순위를 매기고 최종 결과를 정렬 뷰 정의:SQL 쿼리를 뷰로 저장하여 재사용 가능하게 설계
구현 방식	1. 데이터 원본 테이:tblcodingtestranking에서 데이터를 조회. - 컬럼: student_id(교육생 ID), score(점수), solve_test_count(푼문제 수),tier(티어). 2. 서브쿼리: - RANK() 함수와 OVER()절을 사용해 점수 순으로 순위 계산. - 점수가 높은 순서대로 정렬하고, 점수가같으면 coding_test_ranking_id로 정렬. 3. 조건필터링: WHERE sub.교육생ID= 412를사용하여 특정 교육생의 순위와 점수만 조회. 4. 최종정렬:ORDER BY sub.점수DESC를통해 최종 결과를 점수 기준으로 내림차순 정렬.
성과	- 특정 학생의 코딩 테스트 결과(점수, 순위, 푼 문제 수, 티어)를 간편하게 확인 가능. - RANK() 함수를 통해 동점자가 있을 경우에도 순위를 정확히 처리 가능. - 뷰(View)를 활용하여 데이터 재사용성과 유지보수성을 높임.