Project Specification - 학사관리 시스템 과제 2번 2016. 11. 24. PM 2:30 수정

과제2: SQL 작성 Due date: 11/30 23:59 과제 2의 주제(수강신청)는 과제 1과 같으나 과제 1의 E-R Diagram을 사용하지 않음 과제 2는 수강신청 및 성적 입력이 완료된 시점의 테이블 구성을 사용해, 각 종 정보를 출력하기 위한 SQL 구문의 작성을 목표로 함 따라서 마일리지 개념은 과제 2에서는 사용되지 않음 (수강신청이 완료된 시 점이며, 다음 학기 과목에 대한 정보는 존재하지 않는다고 가정함) 테이블은 데이터가 기본 제공되는 것과 본인이 데이터를 명세에 맞게 생성해 야 하는 테이블로 나뉘어짐 데이터는 2010년부터 2015년 2학기 까지의 데이터가 존재한다고 가정함 데이터 생성 시 2010년 ~ 2015년 2학기 까지의 데이터를 임의 생성하면 됨 (Hint : 대량의 데이터 생성보다는 본인이 만드 SQL 구문을 검증하기에 좋은 데이터를 생 성할 것)

과제2: SQL 작성 Due date: 11/30 23:59 제공되는 Table들에 대해 요구되는 각각의 SQL 구문 작성 데이터가 제공되는 테이블 TIMETABLE, LECTUREROOM, BUILDING, COLLEGE, SEMESTER, DAY_OF_WEEK 데이터 추가 및 제거 불가하며, 주어진 데이터 그대로 사용할 것 테이블 구조 변경 불가 (컬럼 추가 및 제거 불가, Constraint 추가 등 모든 종류의 수정 불가) 데이터가 제공되지 않는 테이블 COURSE, COURSE_REGISTRATION, COURSE_TO_TIME, STUDENTS, FACULTY, GRADE, ATTENDANCE 데이터가 제공되지 않는 테이블들은 직접 데이터를 명세에 맞게 추가하여 사용할 것 제공된 명세에 맞지 않는 데이터는 고려하지 말 것 주어진 명세에 부합하는 데이터만 존재하는 것으로 가정하면 됨 SQL 구문 작성 시 PostgreSQL에서 제공하는 SQL 함수 사용 가능 추가적인 테이블 생성 및 사용 불가 View 사용 불가

과제2: SQL 작성 Due date: 11/30 23:59 채점기준 정답이 아닌 경우, 해당 문제에 대한 부분 점수는 없음 채점 시에는 조교가 생성한 데이터 셋으로 정답을 검증함 데이터가 존재하지 않는 테이블에 대해 정답체크를 위한 별도 데이터 생성 조교가 생성한 데이터 또한 명세를 벗어나지 않음 해당 데이터는 공개하지 않음 과제제출: 김 재형 (jaehyungkim@yonsei.ac.kr) No Hardcopy 메일 제목 : [2016DB][과제제출][과제2번] 학번_이름 반드시 위 형식을 지켜서 제출해야 함 제출 파일 이름: 학번.zip 압축 파일 내에 ＂문제번호.sql” 형식으로 문제별로 각각의 sql 파일을 생성할 것 채점 시 pgAdmin을 통해 sql 파일을 실행해서 결과를 확인함

과제2: SQL 작성 Due date: 11/30 23:59 PostgreSQL Schema 위 버전에서 실행이 안되는 기능은 사용 불가 PostgreSQL 설치 시 포함된 pgAdmin(version 4) 툴에서 실행가능한 sql 파일로 만들 것 참고자료 : https://www.pgadmin.org/docs4/1.x/index.html Schema Schema는 default를 사용하므로 SQL문에 명시하지 말 것 예) SELECT * FROM public.”test”; (X) 예) SELECT * FROM “test”; (o) 데이터 명세 (Data specification) 과제 2의 데이터는 명세서에서 설명한 규격에 맞는 데이터만 존재한다고 가정할 것 예) TIMETABLE의 NO 컬럼은 Integer 데이터 타입이므로 10을 초과하는 정수를 입력할 수 있지 만, Column Info에 나와있는 유형의 데이터(0~10)만 존재하도록 함.

과제2: SQL 작성 Due date: 11/30 23:59 과제 관련 질문은 과제 제출일 3일 전 까지만 받 습니다. 질문은 11월 28일 23:59분까지 보내주세요. 이후 질문은 확인하지 않습니다. 질문 확인 및 답변이 늦어 과제 진행에 영향을 미치 는 것을 방지하기 위함입니다. 질문 메일 제목 형식 [2016DB][질문][과제2번] 학번_이름 위 형식을 지키지 않은 메일은 확인되지 않을 수 있음 질의응답 과정에서 본 PPT 파일이 업데이트 될 수 있으므로 하루 1번은 공지사항을 확인하세요.

BUILDNO는 BUILDING 테이블의 ABBR_NAME 컬럼에 있는 값만 존재 과제2: SQL 작성 Due date: 11/30 23:59 1) 데이터가 제공되는 Table 아래 표에 명시된 조건에 맞게 테이블 생성 주어진 excel 파일에 있는 데이터만 삽입할 것 Table name Contents Column list Data Type Column Feature Column Info Reference (참조 관계) BUILDING 학교 내 건물명(NAME) 및 축약형(ABBR_NAME), 건물이 소속된 단과대학명(COLLEGE_NAME) ABBR_NAME varchar(10) PK, Not NULL 문자열 (특수문자 제외) NAME varchar(100) Unique, Not NULL COLLEGE_NAME LECTUREROOM 각 건물(BUILDNO) 내 강의실(ROOMNO) BUILDNO BUILDNO는 BUILDING 테이블의 ABBR_NAME 컬럼에 있는 값만 존재 ROOMNO TIMETABLE 시간표 정보 테이블로 각 교시(NO)에 대해, 시작 시간(START_TIME), 종료 시간(END_TIME)을 나타냄 NO Integer 0~10 사이의 정수 START_TIME Time 시간 예) 8:00 END_TIME 예) 8:50 COLLEGE 단과대학 계열(CATEGORY), 단과대학명(COLLEGE_NAME) 및 소속 전공명(MAJOR_NAME), 전공 이름 축약형(MAJOR_ID) MAJOR_ID char(3) 3자리 영문 문자열 MAJOR_NAME varchar(20) Not NULL CATEGORY

MON, TUE, WED, THU, FRI, SAT, SUN 과제2: SQL 작성 Due date: 11/30 23:59 1) 데이터가 제공되는 Table 각 학기는 16주 단위이며, 시험기간은 고려하지 않음 Table name Contents Column list Data Type Column Feature Column Info Reference (참조 관계) SEMESTER 학기 정보를 나타내며 연도(YEAR), 학기(SEMESTER), 학기 시작일(START_DATE), 학기 종료일(END_DATE) YEAR Integer PK, Not NULL 정수 e.g. 2016 정수 1 or 2 START_DATE DATE Not NULL 날짜 e.g. 03-01 END_DATE 날짜 e.g. 06-21 DAY_OF_WEEK 요일 정보를 나타냄 char(3) MON, TUE, WED, THU, FRI, SAT, SUN

과제2: SQL 작성 Due date: 11/30 23:59 2) 데이터가 제공되지 않는 Table 문자열 길이는 Data Type에 선언된 길이까지 사용 가능 단, 특정 컬럼의 경우 길이가 고정일 수 있으며, Column Info의 내용을 반드시 확인할 것 참고자료 : https://www.postgresql.org/docs/9.6/static/datatype-character.html Table name Contents Column list Data Type Column Feature Column Info Reference (참조 관계) COURSE 과목 정보 테이블이며, 컬럼 나열된 순서대로 연도, 학기, 전공코드, 과목번호, 분반, 과목명, 담당교수명, 요일, 시간, 건물명, 강의실, 학점 COURSE_ID integer PK, Not NULL 0 이상 정수 COURSE_ID_PREFIX는 전공과목의 경우 COLLEGE의 MAJOR_ID를 참조(교양과목은 임의의 3자리 영문 문자열임) PROF_ID는 FACULTY 테이블의 ID에 있는 값만 존재함 BUILDNO는 LECTUREROOM 테이블의 BUILDNO 컬럼을 참조함 ROOMNO는 LECTUREROOM 테이블의 ROOMNO 컬럼을 참조함 YEAR Not NULL 정수 e.g. 2016 SEMESTER 정수 1 or 2 COURSE_ID_PREFIX char(3) 3자리 영문 문자열 COURSE_ID_NO char(4) 임의의 4자리 숫자(0~9)로 구성된 문자열 e.g. 0100 DIVISION_NO 1~99 사이의 정수 COURSE_NAME varchar(100) 콤마 없는 문자열 PROF_ID char(10) 임의의 10자리 정수(0~9)로 구성된 문자열 e.g. 2016000111 BUILDNO varchar(10) 문자열 (특수문자 제외) ROOMNO CREDIT 1~3 사이의 정수 MAX_ENROLLEES 1~300 사이의 정수

과제2: SQL 작성 Due date: 11/30 23:59 2) 데이터가 제공되지 않는 Table COURSE Table COURSE_ID Primary Key로 학정번호(COURSE_ID_PREFIX와 COURSE_ID_NO의 조합)와 별도로 COURSE 테이블 내에서 Unique 한 Key를 별도로 생성함 0 이상의 정수를 가짐 (2016-11-22 PM 11:22 수정) 기존에 표기된 10자리 숫자값을 갖는 문자열 대신 0 이상의 정수를 사용하도록 변경함 다른 테이블의 컬럼들 중 COURSE 테이블의 COURSE_ID를 참조하는 모든 컬럼들은 타입이 char(10)에서 integer로 변경됨 단, {YEAR, SEMESTER, COURSE_ID_PREFIX, COURSE_ID_NO, DIVISION_NO} 컬럼들은 묶어서 Unique해야 함 예) COURSE_ID가 1이고, {2016, 1, CSI, 0000, 1}인 행(row)과 COURSE_ID가 2이고, {2016, 1, CSI, 0000, 1}인 행은 존재할 수 없음 예) COURSE_ID가 1이고, {2016, 1, CSI, 0000, 1}인 행(row)과 COURSE_ID가 2이고, {2016, 1, CSI, 0000, 2}인 행은 존재할 수 있음 COURSE_ID_PREFIX와 COURSE_ID_NO (학정번호) 이 두 개의 컬럼으로 학정번호를 구성하게 됨 COURSE_ID_PREFIX는 전공과목일 경우, COLLEGE의 MAJOR_ID 값 중 하나를 갖게 되며, 교양과목은 임의의 3자리 영문자가 됨 COURSE_ID_NO는 0~9 사이의 숫자 4개로 구성됨 P/NP 과목은 존재하지 않음 같은 과목이라도 학기에 따라 강의실은 바뀔 수 있음 하나의 수업에 강의실은 하나만 배정됨 수업 시간에 따라 강의실이 바뀌지 않음 모든 수업은 특정 전공에 속한 교수가 가르침 교양수업은 특정 전공에 속하지 않지만, 교양 수업을 가르치는 교수는 특정 전공에 속함 PROF_ID는 PK가 아닙니다. (2016-11-22 PM 9:35 수정) 기존에 PROF_ID도 PK로 표시되어 있었으나, 해당 내용을 수정하였습니다. COURSE 테이블에서 COURSE_ID 컬럼만 PK 입니다.

과제2: SQL 작성 Due date: 11/30 23:59 2) 데이터가 제공되지 않는 Table COURSE_TO_TIME 테이블 아래와 같은 형식으로 데이터를 가짐 예) COURSE_ID가 1인 과목이 화요일 0교시, 수요일 1,2교시에 수업이 있다면 다음과 같은 행을 가짐 1, TUE, 0 1, WED, 1 1, WED, 2 {COURSE_ID, DAY_OF_WEEK, NO} 세 컬럼을 묶어서 Unique해야 함 Table name Contents Column list Data Type Column Feature Column Info Reference (참조 관계) COURSE_TO_TIME 해당 과목의 시간표 정보를 갖는 테이블로 각 컬럼은 과목 ID, 요일, 시간을 나타냄 COURSE_ID integer Not NULL 0 이상 정수 COURSE_ID는 COURSE 테이블의 COURSE_ID에 있는 값만 존재 NO는 TIMETABLE 테이블의 NO에 있는 값만 존재 DAY_OF_WEEK는 DAY_OF_WEEK 테이블에 있는 값만 존재 DAY_OF_WEEK char(3) MON, TUE, WED, THU, FRI, SAT, SUN 중 하나를 가짐 NO Integer 0~10 사이의 정수

MAJOR_ID는 COLLEGE의 MAJOR_ID에 있는 값만 존재 과제2: SQL 작성 Due date: 11/30 23:59 2) 데이터가 제공되지 않는 Table STUDENTS 테이블 (11월 21일 업데이트됨) 휴학생은 존재하지 않으며, 모든 학과에 대해 4년제로 처리함. 현재 시점은 2016년 1학기 직전으로 가정하여, 2012년 입학생은 2016년 졸업으로 일괄 처리함. 즉, 2013년 입학생은 4학년인 상태이며, 2014년 입학생은 3학년, 2012년은 2016년 졸업자로 처리됨. GRADUATE_YEAR이 NULL일 경우, 재학생을 의미함. Table name Contents Column list Data Type Column Feature Column Info Reference (참조 관계) STUDENTS 학생 정보 테이블로서, 순서대로 입학연도, 졸업연도, 학년, 학번, 이름 ADMISSION_YEAR Integer Not NULL 연도 e.g. 2016 MAJOR_ID는 COLLEGE의 MAJOR_ID에 있는 값만 존재 GRADUATE_YEAR GRADE 정수 1~4 MAJOR_ID char(3) 3자리 영문 문자열 STUDENT_ID char(10) PK, Not NULL 임의의 10자리 정수(0~9)로 구성된 문자열 e.g. 2016000111 NAME varchar(20) 임의의 문자열 FACULTY 교수진 정보 테이블로 순서대로 교수번호, 이름, 소속 전공 ID, 직위 ID 콤마 없는 문자열 e.g. Sanghyun, Park (불가) e.g. Sanghyun Park (허용) POSITION varchar(4) “정교수”, “부교수”, “조교수” 중 1개 선택

STUDENT_ID는 STUDENTS 테이블의 STUDENT_ID에 있는 값만 존재 과제2: SQL 작성 Due date: 11/30 23:59 2) 데이터가 제공되지 않는 Table Table name Contents Column list Data Type Column Feature Column Info Reference (참조 관계) GRADE 성적 정보 테이블 COURSE_ID integer PK, Not NULL 0 이상 정수 COURSE_ID는 COURSE 테이블의 COURSE_ID에 있는 값만 존재 STUDENT_ID는 STUDENTS 테이블의 STUDENT_ID에 있는 값만 존재 numeric(2,1) Not NULL 아래에 해당하는 두 자리 실수 중 1가지 A+: 4.3, A0: 4.0 A-: 3.7 B+: 3.3, B0: 3.0 B-: 2.7 C+: 2.3, C0: 2.0 C-: 1.7 D+: 1.3, D0: 1.0 D-: 0.7 F: 0.0 STUDENT_ID char(10) 임의의 10자리 정수(0~9)로 구성된 문자열 e.g. 2016000111 ATTENDANCE 출결 현황 테이블 STUDENT_ID는 STUDNETS 테이블의 STUDENT_ID에 있는 값만 존재 ABSENCE_TIME 총 수업 시간에서 출석하지 않은 시간을 정수로 나타내며, 총 수업 시간을 넘을 수 없음. 즉, 결석과 지각의 개념 대신 수업시간에 참여하지 않은 시간으로 출석을 기록하며, 시간은 1시간 단위이며, 분초단위는 존재하지 않음. 0 ~ 총 수업 시간

과제2: SQL 작성 Due date: 11/30 23:59 2) 데이터가 제공되지 않는 Table GRADE 테이블 ATTENDANCE 테이블 휴강은 존재하지 않음 PK는 COURSE_ID와 STUDENT_ID 입니다. 11월 28일 PM 02:40 업데이트 GRADE 테이블 전체 강의 시간의 1/3시간 이상을 출석하지 않은 경우 입력한 성적과 무관하게 F학점 처리 1/3 시간이 소수점이 나올 경우 소수점 첫째자리에서 올림 처리 예) 22 / 3 = 7.33  8 재수강하는 학생의 경우 A0를 초과한 학점을 받을 수 없음 위 두 조건을 만족하는 데이터만 존재한다고 가정하면 됨 COURSE_ID와 STUDENT_ID를 합쳐서 PK를 구성하도록 변경 (2016-11-23 AM 00:07 업데이트됨) 재수강한 과목은 해당 과목의 최종 성적만 기록되는 것으로 가정함 (2016-11-23 AM 00:07 업데이트됨)

과제2: SQL 작성 Due date: 11/30 23:59 2) 데이터가 제공되지 않는 Table 학생 1인당 한 학기에 최대 19학점까지 등록되었다고 가정 Table name Contents Column list Data Type Column Feature Column Info Reference (참조 관계) COURSE_REGISTERATION 수강 신청 결과 테이블로 각 과목별로 수강 신청된 학생들의 정보가 저장됨 COURSE_ID integer PK, Not NULL 0 이상 정수 COURSE_ID는 COURSE 테이블의 COURSE_ID에 있는 값만 존재 COURSE_ID_PREFIX와 COURSE_ID_NO는 COURSE 테이블에 있는 값만 존재함 STUDENT_ID는 STUDNETS 테이블의 STUDENT_ID에 있는 값만 존재 COURSE_ID_PREFIX char(3) Not NULL 3자리 영문 문자열 COURSE_ID_NO char(4) 임의의 4자리 숫자(0~9)로 구성된 문자열 e.g. 0100 STUDENT_ID char(10) 임의의 10자리 정수(0~9)로 구성된 문자열 e.g. 2016000111

SQL 구문 문제 각 문제에 대한 하나의 SQL 구문을 작성하여 sql 파일로 저장하시오 VIEW 사용 금지 별도 TABLE 생성 금지 SQL 구문은 문제 하나 당 반드시 하나일 것 테이블 생성 시 테이블 명과 컬럼 명은 반드시 대문자로 할 것 (2016-11-22 PM 11:46 업데이트) 단, 이 경우에 SQL 구문에서 테이블 명과 컬럼 명을 “”로 묶어주어야 함 예) SELECT * FROM “BUILDING”; (O) 예) SELECT * FROM BUILDING; (X) 예) SELECT “YEAR” FROM “COURSE”; (O)

문제 1 – 전공수업 통계 각 연도별로 전공별 전공명과 수업의 개수를 출력하시오 교양과목은 전공에 속하지 않으므로 제외됨 연도를 오름차순으로 정렬할 것 그 다음으로 수업의 개수가 많은 전공순으로 정렬할 것

문제 2 – 난이도가 높은 과목 찾기 2010년 1학기부터 2015년 2학기까지의 모든 수업 중(모든 학과를 통 틀어)에서 가장 많은 학생들이 재수강을 많이 한 순서로 3개 과목의 이름을 출력하시오 한 학생이 같은 과목을 재수강을 두 번이상한 경우, 이를 모두 재수강 횟수에 포함시킬 것 예) 재수강을 1회해서 같은 과목을 두 번 듣게 된 경우, 재수강 횟수는 2로 인정 예) 재수강을 2회해서 같은 과목을 세 번 듣게 된 경우, 재수강 횟수는 3으로 인정 예) 어떤 과목에 대해 학생 A가 재수강 횟수가 2, 다른 학생 B가 재수강 횟수가 3일 경우, 해당 과목의 재수강 횟수는 5가 됨 Hint : COURSE_REGISTRATION 테이블을 활용하여 재수강 여부를 확인 COURSE_ID_PREFIX 컬럼과 COURSE_ID_NO 컬럼으로 구성된 학정번호로 과목을 구분 함 한 학생이 COURSE_ID_PREFIX 컬럼과 COURSE_ID_NO 컬럼 값이 같지만 COURSE_ID 가 다른 튜플을 갖는 경우 이를 재수강으로 판단할 수 있음 분반이 달라도 같은 과목으로 인정하므로 위와 같이 판단할 수 있음 Hint와 다른 방법으로 해결해도 무방함 과목의 재수강 횟수에 동률은 존재하지 않는 것으로 가정함 2016-11-23 AM 10:51 업데이트

문제 3 - 선호도 조사 2010년 1학기부터 2015년 2학기까지 개설된 과목 중 실제 수강인원 이 최대 수강인원의 80%를 초과하는 과목들을 개설한 교수명과 그 과목 들의 개수를 출력하시오 교양수업의 경우도 교수가 가르친 학생 수 계산에 포함할 것 (교양수업 자체 는 어느 전공에도 속하지 않지만, 해당 수업을 가르치는 교수는 특정 전공에 속함) 수강인원이 80%를 초과하는 과목들의 개수가 높은 교수부터 낮은 교수 순으 로 정렬할 것 2016-11-25 AM 08:00 업데이트 Hint : 과목별 수강인원은 COURSE_REGISTRATION 테이블을 이용해 획득 가 능 이 문제에서 과목은 오직 COURSE_ID로 구분하도록 함 이는 같은 과목이라도 매년 다른 교수가 수업을 진행할 수 있고, 혹은, 분반이 다른 수업 에서 다른 교수가 수업을 진행할 수 있기 때문임 예) 2016년에 개설된 과목의 학정번호가 CSI-0001이고, 분반이 1과 2인 수업이 있을 때, 이는 서로 다른 과목으로 처리함 예) 2015년, 2016년에 학정번호가 CSI-0002이고, 분반이 1인 과목이 개설되었다면, 이 는 서로 다른 과목으로 처리됨 2016-11-24 AM 00:00 업데이트

문제 4 – 성적 장학금 수혜자 2015년 2학기에 전공별 그리고 학년별로 평균 학점이 가장 높은 학 생의 평균 학점과 학번, 학년, 전공명, 소속 단과대학을 출력하시오 각 전공에서 1~4학년 학생 중 평균학점이 가장 높은 학생을 출력하는 문제임 결과 튜플의 개수는 {전공 수 X 4} 만큼 나오게 됨 평균 학점 계산 공식 (과목 평점 * 학점수)의 합계 / 총 학점 수 2016-11-24 PM 02:30 업데이트

문제 5 – 학생 간 공강 확인 학번이 2015111111과 2014222222인 두 학생의 2015년 2학기 공강 시간 중 서로 겹치는 시간을 출력하시오 STUDENTS에 위 학번을 갖는 학생에 대한 데이터를 생성할 것 단, 토, 일은 제외할 것 시간은 TIMETABLE 테이블의 NO를 이용해서 출력할 것 Hint : https://www.postgresql.org/docs/9.6/static/functions-conditional.html 예) MON, 8:00 ~ 8:50 MON, 9:00 ~ 9:50 TUE, 13:00 ~ 13:50 FRI, 14:00 ~ 14:50 FRI, 15:00 ~ 15:50

문제 6 – 학교 수석 졸업자 2015년 2학기 기준 4학년 학생 중 4년간 40학점 이상 취득한 학생들 에 대해서 평균 학점이 가장 높은 학생과 가장 낮은 학생의 이름과 학번, 전공명, 소속 단과대학명을 출력하시오 단, 이름은 앞 한글자만 출력하고 나머지는 *으로 대체하시오. *의 길이는 문자열의 길이와 정확히 일치해야 하며, 특수문자 및 띄워쓰기 등 은 모두 *로 치환되어야 함 예) 박상현  박** 예) Sanghyun, Park  S************* Hint : https://www.postgresql.org/docs/9.6/static/functions-string.html 데이터 생성에 시간이 오래 걸릴 것을 대비해서 학점을 낮게 설정합니다. 2016년 11월 22일 AM 11:49 변경