오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 6장

본 게시글은 오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 6장 풀이입니다. 제가 이 책을 공부하면서 풀었던 풀이의 흔적이므로 정답이 아닐 수 있습니다. 혹여나 틀린 부분이 있어 오류를 댓글로 지적해주신다면 감사하겠습니다.

1. 데이터베이스 설계 순서로 옳은 것은?

(1) 요구사항 분석 → 물리적 모델링 → 논리적 모델링 → 개념적 모델링 → 데이터베이스 구현

(2) 요구사항 분석 → 개념적 모델링 → 논리적 모델링 → 물리적 모델링 → 데이터베이스 구현

(3) 요구사항 분석 → 논리적 모델링 → 개념적 모델링 → 물리적 모델링 → 데이터베이스 구현

(4) 요구사항 분석 → 논리적 모델링 → 물리적 모델링 → 개념적 모델링 → 데이터베이스 구현

 

---

 

2. ER 모델의 표현 방법으로 옳지 않은 것은?

(1) 개체 집합-사각형

(2) 관계 집합-마름모

(3) 속성– 오각형 → 타원형

(4) 다중값 속성-이중타원형

 

---

 

3. ER 모델에 대한 설명으로 옳지 않은 것은?

(1) ER 다이어그램으로 표현하며 피터 첸이 제안하였다.

(2) 일대일(1:1) 관계 유형만 표현할 수 있다. → 1:N, N:M도 표현 가능

(3) 개체 타입과 이들 간의 관계 타입을 이용해 현실 세계를 개념적으로 표현한다.

(4) ER 다이어그램은 ER 모델을 그래프 방식으로 표현한 것이다.

 

---

 

4. ER 표기법에 대한 설명 중 옳지 않은 것은?

(1) 키(key) 속성

(2) 약한 개체의 식별자

(3) 복합 속성 → 다중 속성

(4) 유도 속성

---

5. IE 표기법에 대한 설명으로 옳지 않은 것은?

(1) 비식별자 관계

(2) 식별자 관계

(3) 1:N 관계

(4) 필수참여 → 부분참여

---

 

6. 다음 내용을 모두 포함하는 데이터베이스를 설계하시오. 필요한 경우 몇 가지 가정을 넣을 수 있다.

1. 회사는 네 개의 부서를 운영한다. 부서는 (부서번호, 부서이름)을 저장한다.
2. 부서는 1명 이상의 직원(직원번호, 직원이름, 직책)을 두고 있다. 각 직원은 하나의 부서에 소속된다.
3. 직원은 부양가족(이름, 나이)이 있을 수 있다.
4. 각 직원은 근무했던 부서에 대한 근무기록(기간, 직책)이 있다.

 

(1) ER 다이어그램을 그리시오.

 

(3) ER 다이어그램을 테이블로 변환하시오.

• 부서(Dept) 테이블
  • DeptID (PK)
  • DeptName
• 직원(Employee) 테이블
  • EmpID (PK)
  • EmpName
  • Position
  • DeptID (FK)
• 부양가족(Dependent) 테이블
  • Name
  • Age
  • EmpID (FK)
• 근무기록(WorkRecord) 테이블
  • Period
  • Position
  • EmpID (FK)

 

---

 

7. 다음 내용을 모두 포함하는 데이터베이스를 설계하시오. 필요한 경우 몇 가지 가정을 넣을 수 있다.

1. A 회사는 직원들이 사용하는 주차건물에 주차장이 여러 개 있다.
2. 각 주차장은 주차장 이름, 위치, 주차대수, 주차층 정보로 구성된다.
3. 각 주차장의 주차 공간은 일련번호가 붙은 주차 공간으로 구성되어 있다.
4. 직원은 지정 주차 공간을 요청할 수 있다. 직원은 직원번호, 이름, 구내전화번호, 운전면허번호 정보로 구성된다.

 

(1) ER 다이어그램을 그리시오.

 

(2) ER 다이어그램을 테이블로 변환하시오.

• 주차장(ParkingLot) 테이블
• 주차 공간(ParkingSpace) 테이블
• 직원(Employee) 테이블

 

---

 

8. 다음은 고객과 주문에 관한 ER 다이어그램이다. 개체는 고객(Customer), 제품(Product), 주문(Invoice)으로 구성된다. Place 관계는 ‘주문한다’를, LineItem은 ‘주문 항목’을 의미한다. 그림에 해당하는 테이블을 작성하시오(변환된 테이블의 기본키는 밑줄 실선, 외래키는 밑줄 점선으로 표시한다. 기본키인 동시에 외래키일 경우에는 밑줄 실선으로 표시한다. 테이블 변환을 위하여 필요한 사항 중 설명되지 않은 것은 임의로 정하여 설계한다.

• 고객(Customer) 테이블
  • Cust# (PK): 고객 번호
  • Cname: 고객 이름
  • Street: 거리
  • City: 도시
  • Phone: 전화번호
  • Zip: 우편번호
  • State: 주
• 제품(Product) 테이블
  • Prod# (PK): 제품 번호
  • Description: 설명
  • StandardPrice: 표준 가격
• 주문(Invoice) 테이블
  • Invoice# (PK): 주문 번호
  • TotalOrderAmt: 총 주문 금액
  • Date: 주문 날짜
  • Terms: 조건
  • ShipVia: 배송 방법
  • Cust# (FK): 고객 번호
• 주문 항목(LineItem) 테이블
  • Invoice# (PK, FK): 주문 번호
  • Prod# (PK, FK): 제품 번호
  • SellPrice: 판매 가격
  • Quantity: 수량

 

---

 

9. 학사에 관한 개체-관계(ER) 다이어그램을 관계형 데이터베이스 스키마로 바꾸시오.

• 교수(Professor) 테이블
  • 교번 (PK)
  • 이름
• 학생(Student) 테이블
  • 학번 (PK)
  • 이름
  • 교번 (FK)
• 과목(Course) 테이블
  • 과목번호 (PK)
  • 과목명
• 수강(Enrollment) 테이블 (학생:과목 - N:M)
  • 학번 (PK, FK)
  • 과목번호 (PK, FK)
  • 성적
• 강의(Teaching) 테이블 (교수:과목 - 1:N)
  • 교번 (FK)
  • 과목번호 (PK, FK)

 

---

 

10. 직원과 직원이 수행하는 프로젝트에 관한 ERD(Entity-Relationship Diagram)를 관계형 데이터베이스 스키마로 변환하시오.

• 직원(Employee) 테이블
  • 직원번호 (PK): 직원의 고유 번호
  • 직원이름: 직원의 이름
  • 상사직원번호 (FK, 옵셔널): 상사의 직원번호 (관리 관계를 나타내기 위함)
• 프로젝트(Project) 테이블
  • 프로젝트번호 (PK): 프로젝트의 고유 번호
  • 프로젝트이름: 프로젝트의 이름
• 참가(Participation) 테이블
  • 직원번호 (PK, FK): 직원의 고유 번호
  • 프로젝트번호 (PK, FK): 프로젝트의 고유 번호
  • 시간: 해당 프로젝트에 투입된 시간

 

---

 

11. [부품 데이터베이스] 글로벌 자동차 부품업체 A사는 자동차 회사에 부품을 공급하며 부품 공급에 대한 데이터베이스를 구축하고 있다. 요구사항은 다음과 같다. 이를 바탕으로 개념적 모델링 과정을 거쳐 ER 다이어그램을 작성하고 논리적 모델링 과정을 거쳐 관계 데이터 모델로 사상해 보시오.

 

1. 부품(part): 고유번호(id), 이름(name), 제작연도(year)를 저장한다.
2. 자동차회사(company): 회사마다 고유번호(id), 이름(name), 도시(city), 국가(nation)를 저장한다.
3. 자동차(car): 자동차 회사마다 하나 이상의 자동차 브랜드(brand)를 만든다. 각 자동차는 회사에서 붙여준 고유 번호와 이름을 갖는다.
4. 조립(Play): 부품은 여러 자동차에서 사용된다. 언제 어떤 자동차에 어떤 부품이 사용되었으며 얼마나 많이 사용되었는지에 대한 정보를 저장한다.

 

(1) ER 다이어그램을 그리시오.

 

(2) ER 다이어그램을 테이블로 변환하시오.

• 부품(Part) 테이블
• 회사(Company) 테이블
• 자동차(Car) 테이블
• 조립(Play) 테이블

 

---

 

12. [호텔 데이터베이스] 여러 개의 지점을 가진 호텔을 데이터베이스로 구축하려고 한다. 다음 내용을 모두 포함하는 데이터베이스를 설계하시오. 필요한 경우 몇 가지 가정을 넣을 수 있다.

 

1. 각 호텔 지점은 이름, 주소, 도시, 각 타입 방의 가격, 각 타입 방의 수 등의 정보가 있다.
2. 고객은 이름, 주소, 전화번호 정보가 있다. 고객은 하루 혹은 며칠 동안 호텔을 예약할 수 있다. 예약 시 투숙객의 수, 예약일, 출발일, 도착일을 저장한다.
3. 투숙 후 실제 투숙의 도착일, 출발일, 선급금, 지불방법을 저장한다.

 

(1) ER 다이어그램을 그리시오.

 

(2) ER 다이어그램을 테이블로 변환하시오.

• 호텔 지점(HotelBranch) 테이블
  • BranchName (PK)
  • Address
  • City
  • RoomTypePrice
  • RoomTypeCount
• 고객(Customer) 테이블
  • CustomerID (PK)
  • Name
  • Address
  • Phone
• 예약(Reservation) 테이블
  • ReservationID (PK)
  • NumberOfGuests
  • BookingDate
  • DepartureDate
  • ArrivalDate
  • CustomerID (FK)
  • BranchName (FK)
• 투숙(Stay) 테이블
  • StayID (PK)
  • ActualArrivalDate
  • ActualDepartureDate
  • AdvancePayment
  • PaymentMethod
  • ReservationID (FK)

 

---

 

14. [CD 데이터베이스] 다음 내용을 모두 포함하는 데이터베이스를 설계하시오. 필요한 경우 몇 가지 가정을 넣을 수 있다.

CD 정보를 데이터베이스에 저장하려고 한다. CD는 타이틀, 가격, 장르, 트랙 리스트 등의 정보를 가지고 있다. 각 CD는 아티스트가 있으며 아티스트는 여러 CD를 출반한다. 트랙은 타이틀, 러닝타임(초)이 있다.

 

(1) 개체와 관계를 열거하시오.

• 개체(Entity)
  • CD: 타이틀, 가격, 장르, 트랙 리스트
  • 아티스트: 이름, 국적, 데뷔 연도
  • 트랙: 타이틀, 러닝타임
• 관계(Relationship)
  • CD와 아티스트는 N:1 (하나의 아티스트는 여러 CD를 출반할 수 있음)
  • CD와 트랙은 1:N (하나의 CD에는 여러 트랙이 포함될 수 있음)

 

(2) ER 다이어그램을 그리시오.

 

(3) ER 다이어그램을 테이블로 변환하시오.

• CD 테이블
  • Title (PK)
  • Price
  • Genre
  • TrackList
  • ArtistName (FK)
• Artist 테이블
  • Name (PK)
  • Country
  • DebutYear
• Track 테이블
  • Title (PK)
  • RunningTime
  • CDTitle (FK)

 

(4) 테이블을 생성하는 DDL문을 작성하시오.

CREATE TABLE Artist (
  Name VARCHAR(255) PRIMARY KEY,
    Country VARCHAR(255),
    DebutYear Date
);

CREATE TABLE CD (
  Title VARCHAR(255) PRIMARY KEY,
  Price DECIMAL,
  Genre VARCHAR(100),
  ArtistName VARCHAR(255),
    TrackList VARCHAR(255),
  FOREIGN KEY (ArtistName) REFERENCES Artist(Name)
);

CREATE TABLE Track (
  Title VARCHAR(255) PRIMARY KEY,
  RunningTime INT,
  CDTitle VARCHAR(255),
  FOREIGN KEY (CDTitle) REFERENCES CD(Title)
);

 

(5) 생성된 테이블에 데이터를 세 개씩 삽입하는 DML문을 작성하시오.

INSERT INTO Artist (Name, Country, DebutYear) VALUES ('Artist1', 'USA', '2001-01-01');
INSERT INTO Artist (Name, Country, DebutYear) VALUES ('Artist2', 'UK', '2005-05-05');
INSERT INTO Artist (Name, Country, DebutYear) VALUES ('Artist3', 'Canada', '2010-10-10');

INSERT INTO CD (Title, Price, Genre, ArtistName) VALUES ('CD1', 19.99, 'Rock', 'Artist1', 'TrackList1');
INSERT INTO CD (Title, Price, Genre, ArtistName) VALUES ('CD2', 14.99, 'Pop', 'Artist2', 'TrackList2');
INSERT INTO CD (Title, Price, Genre, ArtistName) VALUES ('CD3', 17.99, 'Jazz', 'Artist3', 'TrackList3');

INSERT INTO Track (Title, RunningTime, CDTitle) VALUES ('Track1', 180, 'CD1');
INSERT INTO Track (Title, RunningTime, CDTitle) VALUES ('Track2', 210, 'CD1');
INSERT INTO Track (Title, RunningTime, CDTitle) VALUES ('Track3', 200, 'CD2');

 

(6) SELECT 문을 작성하여 다음 질의에 답하시오.

(a) 아티스트 정보를 보이시오.

SELECT * FROM Artist;

 

(b) CD 정보를 보이시오.

SELECT * FROM CD;

 

(c) 아티스트 이름과 타이틀을 보이시오.

SELECT Artist.Name, CD.Title
FROM CD
JOIN Artist ON CD.ArtistName = Artist.Name;

 

(d) 가장 많은 CD를 출반한 아티스트의 이름을 보이시오.

SELECT ArtistName
FROM CD
GROUP BY ArtistName
ORDER BY COUNT(*) DESC
LIMIT 1;