[방송통신대학교] 데이터베이스설계및구현 출석수업대체시험 실습 과제물

2019년도 2학기 데이터베이스설계및구현 출석수업대체시험 

---

과제 내용 

교재 1 장~5 장까지 학습한 후, MySQL 서버와 MySQL 워크벤치를 사용하여 다음 조건에 따라 데이터베이스를 설계하시오.

 

요구사항

• (주) 영화광은 국내에서 상영․유 통되는 영화 컨텐츠에 대한 종합적인 정보를 관리하기 위해 데이터베이스 시스템을 구축하고자 한다.
• 영화에 대하여 영화코드번호, 제목, 제작년도, 제 작국가, 상영시간, 개봉일자, 제작사, 배급사 정보를 저장한다.
  • 감독에 대해 서는 등록번호, 이름, 성별, 출생일, 출생지, 학력사항을 저장한다.
  • 배우에 대해서는 배우번호, 생년월일, 이름, 성별, 출생지, 키, 몸무게, 혈액형 정보를 저장한다.
    • 혈액형에는 'A', 'B', 'AB', 'O' , 성별에는 '남', '여' 값만 입력될 수 있다.
• 한 영화에 여러 명의 감독이 참여할 수 있으며, 감독 또한 여러 영화에 참여할 수 있다.
• 감독과 유사하게 한 영화에 여러 명의 배우가 출연할 수 있으며, 출연 시 배역이 정해진다.
• 한 배우 또한 여러 영화에 출연할 수 있다. 장르에 대해 장르코드와 장르명이 저장되며, 각 영화에는 한 장르코드만 부여되고, 한 장르코드는 여 러 영화에 부여될 수 있다.

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

문제

• 문1) MySQL 워크벤치를 사용하여 ER 다이어그램 을 작성한다. (모델링 화면 캡쳐) - 5점
• 문2) MySQL 워크벤치의 forward engineering 기 능을 이용하여 스키마를 생성하고 MySQL Server 에 적용한다. (review the SQL script, forward engineer process, 내비게이터 스키마 부분 화 면 캡쳐) - 3점
• 문3) 사용자 요구사항과 문5)를 참고하여 각 테 이블에 적용되어야할 한 개 이상의 인덱스를 생성한다. (인덱스 생성 SQL문 목록 및 SQL 쿼리 패널에 적용 화면 캡쳐 ) - 2점
• 문4) 각 테이블에 10개 이상의 예제 레코드를 삽입하는 SQL문을 작성한다. (SQL문 제출 및 SQL 쿼리 패널 적용 화면 캡쳐) - 5점
• 문5) 다음 정보를 출력하기 위한 SQL문을 작성 한다. (SQL문 제출 및 쿼리창 적용 화면 캡쳐하 여 제출) - 각 3점
  • 1) 배우의 이름, 성별, 출생지를 출력하시오.
  • 2) 2015년에 제작된 영화의 제목, 제작국가, 상 영시간, 제작사를 출력하시오.
  • 3) 현재 날짜를 기준으로 최근 3년 안에 제작 된 영화의 제목, 제작국가, 개봉일, 제작사, 배급사를 출력하시오.
  • 4) 이름이 ‘명감독’인 감독이 촬영한 영화의 제목을 출력하시오.
  • 5) 감독이 직접 출현한 영화의 제목과, 제작국 각, 상영시간을 출력하시오.

 

---

과제 풀이

1. MySQL 워크벤치를 사용하여 ER 다이어그램 작성

---

2. MySQL 워크벤치의 forward engineering 기능을 이용하여 스키마를 생성하고 MySQL Server에 적용

• review the SQL script

• forward engineer process

• 내비게이터 스키마

---

3. 테이블에 적용되어야 할 한 개 이상의 인덱스를 생성

• SQL문

ALTER TABLE 영화 DROP INDEX yearIndex;
ALTER TABLE 배우 DROP INDEX nameIndex;
ALTER TABLE 감독 DROP INDEX directorNameIndex;
ALTER TABLE 영화참여 DROP INDEX directorCodeIndex;
ALTER TABLE 영화출연 DROP INDEX actorNumIndex;
ALTER TABLE 장르 DROP INDEX jangreNameIndex;

• SQL 쿼리 패널

---

4. 각 테이블에 10개 이상의 예제 레코드 삽입하는 SQL문 작성

• SQL 문

insert into 감독 values(1, '명감독', '남', '2001-05-06', '대전', '고려대 불문과 졸업');
insert into 감독 values(2, '김감독', '여', '1990-07-01', '서울', '서울대 경영학과 졸업');
insert into 감독 values(3, '이감독', '남', '1980-09-06', '대구', '고려대 연극영화과 졸업');
insert into 감독 values(4, '박감독', '여', '1977-11-12', '부산', '서강대 고고학과 졸업');
insert into 감독 values(5, '최감독', '여', '1971-12-29', '대천', '대전 컴퓨터공학과 졸업');
insert into 감독 values(6, '나감독', '남', '1965-02-19', '서산', '충남대 사회복지학과 졸업');
insert into 감독 values(7, '마감독', '여', '1953-03-16', '홍천', '한남대 컴퓨터과학과 졸업');
insert into 감독 values(8, '차감독', '남', '1999-07-10', '강릉', '성균관대 유아교육과 졸업');
insert into 감독 values(9, '아감독', '남', '1996-10-28', '속초', '경희대 산업공학 졸업');
insert into 감독 values(10, '강감독', '여', '1977-06-09', '제천', '연세대 신문방송학과 졸업');

insert into 배우 values(1, '1999-05-06', '김배우', '남', '서울', 171.2, 76.1, 'A'); 
insert into 배우 values(2, '1953-05-06', '이배우', '여', '부산', 165.6, 59.4, 'B'); 
insert into 배우 values(3, '1977-05-06', '강배우', '남', '대구', 186.7, 86.7, 'AB'); 
insert into 배우 values(4, '1996-05-06', '나배우', '여', '속초', 157.9, 106.8, 'O'); 
insert into 배우 values(5, '1953-03-16', '마감독', '여', '홍천', 179.2, 96.2, 'A'); 
insert into 배우 values(6, '1969-05-06', '공배우', '여', '파주', 178.3, 46.4, 'B'); 
insert into 배우 values(7, '1999-05-06', '아배우', '여', '철원', 166.4, 62.3, 'AB'); 
insert into 배우 values(8, '1980-05-06', '박배우', '남', '김포', 185.7, 98.2, 'O'); 
insert into 배우 values(9, '1971-05-06', '최배우', '남', '인천', 181.2, 92.7, 'O'); 
insert into 배우 values(10, '1998-05-06', '명배우', '여', '대전', 183.1, 85.1, 'A'); 

insert into 장르 values('A', '멜로');
insert into 장르 values('B', '공포');
insert into 장르 values('C', '코미디');
insert into 장르 values('D', '재난');
insert into 장르 values('E', '교육');
insert into 장르 values('F', '판타지');
insert into 장르 values('G', '전쟁');
insert into 장르 values('H', '종교');
insert into 장르 values('I', '성인');
insert into 장르 values('J', '모헝');

insert into 영화 values(1, '나의 여행기', 2018, '한국', 123, '2018-05-11', '바른미래', 'CJ엔터테인먼트', 'J');
insert into 영화 values(2, '스타이즈본', 2015, '미국', 180, '2015-01-21', '마블스튜디오', '롯데엔터테인먼트', 'D');
insert into 영화 values(3, '스타워즈', 2002, '중국', 174, '2002-08-05', '월트디즈니스튜디오', '오드', 'A');
insert into 영화 values(4, '걸리버여행기', 2001, '일본', 152, '2001-06-02', '미라맥스', '소나무픽쳐스', 'B');
insert into 영화 values(5, '공포영화', 1999, '대만', 124, '1999-09-01', '우리집', '소니픽처스', 'E');
insert into 영화 values(6, '나를 돌아봐', 2009, '프랑스', 175, '2009-08-01', '대한건아', '월트디즈니', 'C');
insert into 영화 values(7, '추격자', 2007, '독일', 102, '2007-10-01', '영화사랑', '안나프루나', 'F');
insert into 영화 values(8, '공조', 2006, '러시아', 117, '2006-12-01', 'DC필름스', '유니버설픽처스', 'I');
insert into 영화 values(9, '공부', 2004, '한국', 147, '2004-06-01', '굿코리아', '대원미디어', 'G');
insert into 영화 values(10, '빈집', 2019, '일본', 130, '2019-07-01', '터치스톤', '영화사빛', 'H');

insert into 영화참여 values(1, 1);
insert into 영화참여 values(2, 2);
insert into 영화참여 values(3, 3);
insert into 영화참여 values(4, 4);
insert into 영화참여 values(5, 5);
insert into 영화참여 values(6, 6);
insert into 영화참여 values(7, 7);
insert into 영화참여 values(8, 8);
insert into 영화참여 values(9, 9);
insert into 영화참여 values(10, 10);

insert into 영화출연 values(1, 1);
insert into 영화출연 values(2, 2);
insert into 영화출연 values(3, 3);
insert into 영화출연 values(4, 4);
insert into 영화출연 values(5, 7);
insert into 영화출연 values(6, 6);
insert into 영화출연 values(7, 5);
insert into 영화출연 values(8, 8);
insert into 영화출연 values(9, 9);
insert into 영화출연 values(10, 10);

• SQL 쿼리 패널(생략)

---

5. 다음 정보를 출력하기 위한 SQL문 작성

• 배우의 이름 성별, 출생지를 출력

select 이름, 성별, 출생지 from 배우;

• 2015년에 제작된 영화의 제목, 제작국가, 상영시간, 제작사 출력

select 제목, 제작국가, 상영시간, 제작사 from 영화 where 제작년도 = 2015;

• 현재 날짜를 기준으로 최근 3년안에 제작된 영화의 제목, 제작국가, 개봉일, 제작사, 배급사를 출력

select 제목, 제작국가, 개봉일자, 제작사, 배급사 from 영화 where 제작년도 > (NOW() - INTERVAL 3 YEAR);

• 이름이 ‘명감독’인 감독이 촬영한 영화의 제목 출력

select 제목 from 영화 
inner join 영화참여 on 영화참여.영화_영화코드번호 = 영화.영화코드번호
inner join 감독 on 감독.등록번호 = 영화참여.감독_등록번호 and 감독.이름 = '명감독';

• 감독이 직접 출연한 영화의 제목과, 제작국가, 상영시간 출력

select 제목, 제작국가, 상영시간 from 영화
inner join 영화출연 on 영화출연.영화_영화코드번호 = 영화.영화코드번호
inner join 배우 on 배우.배우번호 = 영화출연.배우_배우번호
right outer join 감독 on 배우.이름 = 감독.이름 where 배우.이름 is not null;

---

끝으로

이 글이 도움이 되었다면, 하단의 Google 광고 👎👎👎 한번씩 클릭 부탁 드립니다. 🙏🙏🙏

광고 클릭은 많은 힘이 됩니다!