KNOU-DATABASE 2019: 실습 및 구현

6 분 소요

온라인 과제 관련

제출기한: 11월 4일 15시 까지

제출방법참조 - 링크클릭

FAQ

Q1. 다이어그램에서 한글안될시 어떻게 해야하는지 다시 보여주실수 있나요?

정재화교수님 유투브 참조하시면 됩니다.

Q2. 과제물 제출방법은 어디에 있나요?

위에 과제물 제출방법에 대한 링크 올려놓았습니다.

Q3. 2번 문제에서 forward engineer 과정을 캡쳐하라는 건 상단메뉴인 데이터베이스 누르고 넘어가는 화면 하나하나 다 캡쳐하라는 건가요?

마지막 장면 하나만 캡쳐해주시면 됩니다.

Q4. 안녕하세요. 제가 과제를 하고 있는데, mysql 버전8.0입니다. 다이어그램 그릴때, 참조키 붉은 열쇠 모양이 안나타납니다. 기본키 노란 열쇠는 표시가 됩니다. 보이게 할 수 있을까요?

상위버전에서는 안나오도록 업데이트 된 것 같습니다. 안나와도 점수에 영향 없습니다.

Q5. “SQL 에디터 적용 화면”캡처 중간고사 과제물에 sql 에디터가 뭐죠? ;;;

밑 그림에서 3번 9번 창 부분 캡쳐해주시면 됩입니다.

Q6. 과제 3번은 어떤식으로 스크린샷을 찍어서 저장해야 되나요?

INSERT 쿼리문을 스크린샷찍으시거나 INSERT 다된 이후의 테이블을 SELECT * FROM ~~~ 명령을 이용하여 출력하신 결과를 스크린샷 찍으시면 됩니다.

Q7. MySQL 대신 MariaDB로 해도 상관없나요?

상관없습니다.

Q8. NULL 설정도 평가에 들어가나요?

안들어갑니다.

Q9. 데이터 타입의 길의는 임의로 설정해도 상관없나요?

상관없습니다.

Online 질의응답 시스템 접속 방법

링크 클릭 후 COSE371 입력

Modeling: EERD

1. KNOU SQL SCRIPT: DDL

CREATE DATABASE knou;
USE knou;

2. KNOU SQL SCRIPT: DDL2

DROP TABLE IF EXISTS `수강신청`;
DROP TABLE IF EXISTS `강의`;
DROP TABLE IF EXISTS `교수`;
DROP TABLE IF EXISTS `학생`;
DROP TABLE IF EXISTS `졸업예정자`;

CREATE TABLE `학생` (
  `학생번호` char(9) NOT NULL,
  `소속학과` varchar(30) NOT NULL,
  `학생이름` char(20) NOT NULL,
  `학년` int(11) NOT NULL,
  `성별` char(4) DEFAULT NULL,
  `나이` int(11) DEFAULT NULL,
  `전화번호` varchar(13) NOT NULL,
  `주소` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`학생번호`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `교수` (
  `교수번호` char(4) NOT NULL,
  `소속학과` varchar(30) NOT NULL,
  `교수이름` char(20) NOT NULL,
  `직위` char(10) DEFAULT NULL,
  `전화번호` varchar(13) NOT NULL,
  `연구실위치` varchar(50) DEFAULT NULL,
  `주소` varchar(50) DEFAULT NULL,
  `입사일` datetime DEFAULT NULL,
  `퇴사일` datetime DEFAULT NULL,
  PRIMARY KEY (`교수번호`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `강의` (
  `강의번호` char(4) NOT NULL,
  `강의이름` varchar(50) NOT NULL,
  `강의실` varchar(40) NOT NULL,
  `강의시간` varchar(20) NOT NULL,
  `인원제한` int(11) NOT NULL,
  `이수구분` char(20) NOT NULL,
  `교수번호` char(4) NOT NULL,
  `선수과목` char(4) DEFAULT NULL,
  PRIMARY KEY (`강의번호`),
  KEY `fk_강의_교수_idx` (`교수번호`),
  KEY `fk_강의_강의1_idx` (`선수과목`),
  CONSTRAINT `fk_강의_강의1` FOREIGN KEY (`선수과목`) REFERENCES `강의` (`강의번호`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_강의_교수` FOREIGN KEY (`교수번호`) REFERENCES `교수` (`교수번호`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `수강신청` (
  `학생번호` char(9) NOT NULL,
  `강의번호` char(4) NOT NULL,
  `신청시각` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`학생번호`,`강의번호`),
  CONSTRAINT `fk_강의_has_학생_학생1_idx` FOREIGN KEY (`학생번호`) REFERENCES `학생` (`학생번호`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_강의_has_학생_강의1_idx` FOREIGN KEY (`강의번호`) REFERENCES `강의` (`강의번호`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `졸업예정자` (
  `학생번호` char(9) NOT NULL,
  `소속학과` varchar(30) NOT NULL,
  `학생이름` char(20) NOT NULL,
  `학년` int(11) NOT NULL,
  `성별` char(4) DEFAULT NULL,
  `나이` int(11) DEFAULT NULL,
  `전화번호` varchar(13) NOT NULL,
  `주소` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`학생번호`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. KNOU SQL SCRIPT: DML

INSERT INTO `knou`.`학생` (`학생번호`, `소속학과`, `학생이름`, `학년`, `성별`, `나이`, `전화번호`, `주소`) VALUES ('201258047', '국어국문학과', '최규성', '3', '남', '24', '020-1424-7272', '대전시 대덕구');
INSERT INTO `knou`.`학생` (`학생번호`, `소속학과`, `학생이름`, `학년`, `성별`, `나이`, `전화번호`, `주소`) VALUES ('201311001', '컴퓨터과학과', '최성우', '1', '남', '21', '020-4293-1924', '서울시 종로구');
INSERT INTO `knou`.`학생` (`학생번호`, `소속학과`, `학생이름`, `학년`, `성별`, `나이`, `전화번호`, `주소`) VALUES ('201323004', '법학과', '이민태', '2', '여', '22', '020-4293-1925', '서울시 송파구');
INSERT INTO `knou`.`학생` (`학생번호`, `소속학과`, `학생이름`, `학년`, `성별`, `나이`, `전화번호`, `주소`) VALUES ('201409081', '경영학과', '장준홍', '4', '남', '35', '020-4293-1926', '대전시 중구');
INSERT INTO `knou`.`학생` (`학생번호`, `소속학과`, `학생이름`, `학년`, `성별`, `나이`, `전화번호`, `주소`) VALUES ('201426005', '컴퓨터과학과', '현석경', '1', '여', '31', '020-4293-1927', '부산시 해운대구');
INSERT INTO `knou`.`학생` (`학생번호`, `소속학과`, `학생이름`, `학년`, `성별`, `나이`, `전화번호`, `주소`) VALUES ('201426007', '컴퓨터', '정제용', '2', '남', '27', '020-4293-1928', '서울시 성북구');
INSERT INTO `knou`.`학생` (`학생번호`, `소속학과`, `학생이름`, `학년`, `성별`, `나이`, `전화번호`, `주소`) VALUES ('201434027', '법학과', '정호경', '2', '여', '29', '020-4293-1929', '서울시 강남구');
INSERT INTO `knou`.`학생` (`학생번호`, `소속학과`, `학생이름`, `학년`, `성별`, `나이`, `전화번호`, `주소`) VALUES ('201481096', '통계학과', '정희영', '3', '남', '28', '020-4293-1930', '광주시 서구');

INSERT INTO `knou`.`교수` (`교수번호`, `소속학과`, `교수이름`, `직위`, `전화번호`, `연구실위치`, `주소`, `입사일`) VALUES ('CO12', '컴퓨터과학과', '강원상', '부교수', '020-8561-1354', '서울서부지역대학', '서울시 중구', '2004-03-21 00:00:00');
INSERT INTO `knou`.`교수` (`교수번호`, `소속학과`, `교수이름`, `직위`, `전화번호`, `연구실위치`, `주소`, `입사일`, `퇴사일`) VALUES ('CO17', '컴퓨터과학과', '이춘식', '정교수', '020-5461-5846', '서울본부', '서울시 강남구', '1984-02-28 00:00:00', '2014-08-30 00:00:00');
INSERT INTO `knou`.`교수` (`교수번호`, `소속학과`, `교수이름`, `직위`, `전화번호`, `연구실위치`, `주소`, `입사일`) VALUES ('CO29', '컴퓨터과학과', '길민준', '정교수', '020-5487-9134', '전북지역대학', '전주시 완산구', '1994-06-30 00:00:00');
INSERT INTO `knou`.`교수` (`교수번호`, `소속학과`, `교수이름`, `직위`, `전화번호`, `연구실위치`, `주소`, `입사일`) VALUES ('KO41', '국문학과', '공식기', '정교수', '020-6851-5149', '서울남부지역대학', '서울시 구로구', '1991-01-24 00:00:00');
INSERT INTO `knou`.`교수` (`교수번호`, `소속학과`, `교수이름`, `직위`, `전화번호`, `연구실위치`, `주소`, `입사일`) VALUES ('LA17', '법학과', '김완종', '부교수', '020-1685-5433', '인천지역대학', '인천시 남동구', '2016-08-30 00:00:00');
INSERT INTO `knou`.`교수` (`교수번호`, `소속학과`, `교수이름`, `직위`, `전화번호`, `연구실위치`, `주소`, `입사일`) VALUES ('MN23', '경영학과', '송배문', '조교수', '020-3548-4489', '강원지역대학', '춘천시 호자구', '2011-02-03 00:00:00');
INSERT INTO `knou`.`교수` (`교수번호`, `소속학과`, `교수이름`, `직위`, `전화번호`, `연구실위치`, `주소`, `입사일`) VALUES ('SS07', '통계학과', '황수지', '조교수', '020-1905-9994', '제주지역대학', '제주시 노형동', '2011-03-01 00:00:00');

INSERT INTO `knou`.`강의` (`강의번호`, `강의이름`, `강의실`, `강의시간`, `인원제한`, `이수구분`, `교수번호` ) VALUES ('CO21', '자료구조', '신관 492호', '월요일 1~2교시', '60', '전공선택', 'CO12');
INSERT INTO `knou`.`강의` (`강의번호`, `강의이름`, `강의실`, `강의시간`, `인원제한`, `이수구분`, `교수번호` ) VALUES ('KO14', '현대시론', '구관 009호', '화요일 6교시', '100', '교양필수', 'KO41');
INSERT INTO `knou`.`강의` (`강의번호`, `강의이름`, `강의실`, `강의시간`, `인원제한`, `이수구분`, `교수번호`)  VALUES ('LA01', '형법', '신관 333호', '월요일 7~9교시', '90', '전공필수', 'LA17');
INSERT INTO `knou`.`강의` (`강의번호`, `강의이름`, `강의실`, `강의시간`, `인원제한`, `이수구분`, `교수번호`)  VALUES ('MN09', '마케팅원론', '본관 042', '금요일 4~교시', '80', '전공필수', 'MN23');
INSERT INTO `knou`.`강의` (`강의번호`, `강의이름`, `강의실`, `강의시간`, `인원제한`, `이수구분`, `교수번호`, `선수과목`) VALUES ('CO19', '데이터베이스', '본관 123호', '수요일 2~3교시', '80', '전공필수', 'CO12', 'CO21');
INSERT INTO `knou`.`강의` (`강의번호`, `강의이름`, `강의실`, `강의시간`, `인원제한`, `이수구분`, `교수번호`, `선수과목`) VALUES ('KO31', '한국어 의미의 이해', '강당', '목요일 6~7교시', '200', '교양필수', 'KO41', 'KO14');
INSERT INTO `knou`.`강의` (`강의번호`, `강의이름`, `강의실`, `강의시간`, `인원제한`, `이수구분`, `교수번호`, `선수과목`) VALUES ('CO41', '컴파일러', '본관 232호', '수요일 4~6교시', '50', '전공선택', 'CO29', 'CO21');

INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201311001', 'CO21', '2014-02-28 05:32:36');
INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201323004', 'KO31', '2014-02-28 05:32:37');
INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201323004', 'LA01', '2014-02-28 05:32:38');
INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201409081', 'MN09', '2014-02-28 05:32:39');
INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201426005', 'CO19', '2014-02-28 05:32:40');
INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201426005', 'KO14', '2014-02-28 05:32:41');
INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201434027', 'CO21', '2014-02-28 05:32:42');
INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201434027', 'LA01', '2014-02-28 05:32:43');
INSERT INTO `knou`.`수강신청` (`학생번호`, `강의번호`, `신청시각`) VALUES ('201481096', 'CO21', '2014-02-28 05:32:44');

온라인과제물 공지

MySQL 서버와 MySQL 워크벤치를 사용하여 다음 조건에 따라 데이터베이스를 설계하시오.

온라인 과제물 제출 기한 및 방법

  • 기한: 11월 4일 15시까지
  • 방법: 방통대 전산을 제외하고 전무, 이메일 제출 불가
  • 파일 형식: 한글파일 또는 word

요구사항 명세서

자전거 대여점 “KNOU두발로”는 고객이 요구하는 사양의 자전거를 실시간으로 대여하기 위해 자전거 및 부품에 대한 데이터베이스를 구축하고자 한다. 각각의 자전거에 대해서 자전거번호, 제조회사, 최대승차인승, 색상, 상태, 대여요금을 관리한다. 제조회사에 대해서는 제조사코드, 제조사명, 대표번호, 주소를 관리한다. 고객은 한번에 한 자전거를 대여할 수 있으며, 각 자전거는 한 고객에게만 대여될 수 있다. 각 자전거는 반납후에 다른 고객에게 대여될 수 있다. 고객에 대하여 고객번호, 이름, 주소를 관리한다. 대여 시 대여시각, 반납시각이 기록된다.

※ 위 요구사항은 최소한의 의무사항을 나타내며 문제 해결을 위해 추가적인 엔티티, 속성, 관계 및 데이터 타입을 수험자가 직접 결정, 정의하여 사용할 수 있다.

문제

  1. MySQL 워크벤치를 사용하여 ER 다이어그램을 작성한다. (모델링 화면 캡처) - 5점

  2. MySQL 워크벤치의 forward engineering 기능을 이용하여 스키마를 생성하고 MySQL Server에 적용한다. (SQL script, forward engineer 과정, 스키마 부분 화면 캡처) - 5점

  3. 각 테이블에 4개 이상의 예제 레코드를 삽입하는 SQL문을 작성한다. (SQL문 제출 및 SQL 에디터에 적용 화면 캡처) - 5점

  4. 다음 정보를 출력하기 위한 SQL문을 작성한다. (SQL문 제출 및 SQL 에디터 적용 화면캡처) - 각 5점

    1. 모든 제조사코드, 제조사명, 대표번호를 출력하라
    2. 현재 ‘고장’상태인 자전거의 자전거번호, 제조회사, 색상 목록을 출력하라
    3. 자전거 대여 횟수가 3번 이상인 모든 고객 이름을 출력하라.

참고자료